Too many indexes on an AS400 Table and can no longer be 'linked' to MS Access

I have been linking to AS400 tables for the past 7 years using MS Access and back coding. All the tables that are needed, which are many, are programmed to open and close when accessing a Form or Report. I know that MS Access has a limitation as to how many indexes can be opened at one time.

Up until this weekend several of my MS Access databases have been running without connection linking error. That is until today, and have now received the following error message:

There are too many indexes on table WFALREP. Delete some of the indexes on the table and try the operation again.

I have had the IBM AS40 admnistrator look into the table to see what indexes are running or IF they can be modified. He is unfamiliar with the indexes and or if they can be modified.
At the present time we are dead-in-the-water.

Does anyone know a workaroudn, if any, or how/where to modify the AS400 table indexes?

I have also tried opening a 'clean' Access db with no success. I am using MS Office 2010.
My SQL 2008 server seems to be connecting and pulling the same table without errors. I cannot use the SQL as a link because the data is static and must be imported daily.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
I have had the IBM AS40 administrator look into the table to see what indexes are running or IF they can be modified. He is unfamiliar with the indexes and or if they can be modified.

It is interesting the the AS400 admin is at a loss about the indexing...?

All I can say is that youi make sure you are using the most up-to-date drivers for connecting the as400 to Access.

You may ultimately have to contact your data vendor for the AS 400 (IBM, ...etc)

Dave FordSoftware Developer / Database AdministratorCommented:
So, just now many indexes DOES the table have?
edrz01Author Commented:
I have managed to figure out how to look into the AS400 table structures via the Schemas. I opened up the table in question WFALREP. I see no Key Constraints, Foreign Key Constraints or Materialized Query. However, I did attempt to assign a Primary Key for the field ALAANB (Request Number), but was unsuccessful. A message was displayed that the table is in use and the request cannot be Added. To try to Add the request when not in use.

That's about where I am at this time.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

edrz01Author Commented:
I attempted to open the Index Folder and received the following error message about SQL query exceeds specified time limit or storage limit. See the attachment in this blog post
Dave FordSoftware Developer / Database AdministratorCommented:
You could always go to the green-screen interface and issue the command:

DspDbr YourSchema/YourTable

The output would show you all the indexes.

Jeffrey CoachmanMIS LiasonCommented:
Again, if it were me, I would initiate a conversation with the data provider...
edrz01Author Commented:
I checked the Green Screen and there are 39 dependent files.

In System i Navigator there are 34 Indexes and of those 12 are not Valid. There is no selection to delete the invalid indexes. All the others are valid. The Owner for those are all QPGMR
Dave FordSoftware Developer / Database AdministratorCommented:
That's interesting. I just tried the same thing with two different tables. One table has over 100 indexes, and the other has 36 indexes. The one with 100 indexes threw the same error message as you mention, but the one with 36 worked beautifully.

When you say "12 are not Valid", what exactly do yo mean by that?
edrz01Author Commented:
Here is what I referred to, see attachement.
You should check {Help} text to ensure we know what Valid(Yes/No) means. My current iNav version doesn't show that column. Normally I'd expect it to mean that it's being generated (or updated with perhaps many entries) and DB2 hasn't finished with it yet.

If you use APIs to retrieve the descriptions, there is a 'Valid' indicator that I usually see being set to 'No' after a restore when the rebuilds are still going on. I use the flag to decide if database restores have completed to a point where files are useful. But I don't know if this is the same indicator value.

I'd guess it's a HTE database from the schema name. If so, it's not likely that anything can be done about the sheer (apparently excessive) number of indexes. If it's from HTE, I'd ask their Support group about it.

It looks as if a number of the indexes are 'S'elect/'O'mit versions of other indexes. I don't know what the actual Access problem is. I suspect it's just from its built in limitations. But it might be sensitive to shared access paths.

If so, it could be possible for HTE Support to recreate the indexes in a sequence that ensures no duplication of indexes. (Long shot, but maybe.)

Best choice is probably not to use Access to link to a complex database.

Gary PattersonVP Technology / Senior Consultant Commented:
Access has an internal limit of 32 indexes.  Db2 counts indexes differently, so the counts may notnot line up perfectly.  

One trick you may be,able to use is to create a db2 alias or view, and attach the alias or view as a table in access.

like Tom said, Better approach is to avoid using a desktop database tool like access with large production databases.  Not what it is designed for.
edrz01Author Commented:
Guys, I fully understand that Access is truly not the way to go for this type of DB. And I use SQL 2008 and Oracle 11g, however, due to the data in SQL and Oracle being static, it is only used for certain reports. And I am aware that Access has the 32 index limitations. However, the Access db's were written many years before by previous programmers and the company wants to keep it that way, Restrictive yes, but, when the db's in question have worked for years and then an upgrade occurred...that is a the problem.

Rewriting the dozens of Access databases that were previously written is almost unfathomable, especially since I'm the only one that works databases and Excel formula spreadsheets.

I have submitted a work request to SunGard and am trying to understand what occurred and what can be done to remedy the issue. I would rather have a full understanding than go off blind and accept a solution without comprehending the issue(s).

I have always trusted the experts here on EE and their experience too. Majority of my best solutions have come from you experts...

So far, I have gained a lot of experience in this blog on using AS400 inner workings. I’ve stepped into the ‘backdoor’ of the AS400 and finding how it can be customized or views, etc. I know I'll probably create a View to facilitate a solution. Ironically another table GM310AP is having the same problem. And I have asked our AS400 Administrator to advise SunGard of the issue I am having. With Tom's explanation, I will be better off explaining and understanding what is or has happened.

Please continue to submit your blog entries, maybe and hopefully another user, as myself, can learn much from you guys. We may not become experts, but will be better off with more knowledge.
edrz01Author Commented:
Ok, here is what my AS400 Administrator and I were able to figure out.

-First, we sent a command to the Green Screen “strHostSvr *Database to refresh the connections.

-Second, Next we opened the System i Navigator and went to the Connection and Table
--Located the Table that we are having Index issues
---Selected “Show Indexes”
----Located the “Created Date” and sorted descending order
-----Identified any dates that were on the most recently created and noted the SQL Name

-Third, again sent a command to the Green Screen “StrPdm
---Selected option 1 and enter
---Selected the Connection (in our case HTEDTA)
----Selected option 12
------Scrolled down to the SQL Name(s) noted above
--------Selected #1 to DELETE the SQL Name(s)

-Verified in the System i Navigator and noted the Index change

-Lastly, open Access and performed an ODBC Link to AS400 table that was affected. Link was successful.

We will note these procedures in future AS400 updates. The System i Navigator is for Viewing Indexes and the SQL Name creation dates and the Green Screen allows direct modifications to the Indexes that were created.

We did speak with SunGuard and they are still scratching their heads and didn't know who or why the extra Indexes were created, but they were created and now deleted nonetheless.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary PattersonVP Technology / Senior Consultant Commented:
Glad you got it resolved.
edrz01Author Commented:
Finally got an answer from SunGuard. The two indexes deleted were created to speed up SQL connection. Those were not required and were authorized for deletion.
edrz01Author Commented:
Internal research and testing resulted in the procedures that I posted and this the solution that I came up with. The AS400 Administrator agreed with the procedures along with SunGuard. This may not be a total solution for everyone, but one that is working for our company. Thank you all for your valuable inputs.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.