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)

JeffCoachman

So, just now many indexes DOES the table have?

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.

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
ConnectionIndexError2.jpg

You could always go to the green-screen interface and issue the command:

DspDbr YourSchema/YourTable

The output would show you all the indexes.

HTH.
DaveSlash

Again, if it were me, I would initiate a conversation with the data provider...

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

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?

Here is what I referred to, see attachement.
ConnectionIndexList.jpg

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.

Tom

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.

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.

Glad you got it resolved.

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.

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.