SQL error while querying an AS400 file from VB (ODBC)

Hi everyone,

Kinda puzzled here, hoping for a little input and/or ideas.

Every month I run a query from VB on a file created monthly for the previous month's data.  Everything has been working fine up until this month, but something changed and now I'm getting an error (and the as400 admin doesn't know what caused it).

The error I'm getting is:
Run-time error '-2147467259 (80004005)':
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - SQL query exceeds specified time limit or storage limit


I first thought maybe the data for November was larger than usual, but I got the same error when re-running it on October's data (which worked fine for me last month).

My query is actually querying a similar file in 5 libraries (ie. LIB01/FL200711, LIB02/FL200711, LIB03/FL200711, etc) with a UNION ALL between each lib's query, so I thought maybe that was creating the error. Breaking it down to 5 individual queries still causes the error.

The admin of the machine ran the CHGQRYA CL command (at least I think that is what she ran, that is what IBM's website says specifies the query time limit and temp storage limit) but said there are no limits ("nomax" i think she said).

I ran the same query in NGS (through the iSeries Access emulator) and it worked fine and without error.

My ODBC driver isn't the issue; I tried querying a different file in the same libraries and it worked fine.

I tried putting an OPTIMIZE FOR 2000 ROWS statement in there as well (the max one of my individual queries would be), no luck.

Because I'm running this query via ODBC, the admin says nothing is logging in my job log (my connection string does have my username) for it.



Some (sanitized) details, should you want to know:

My connection string:
ODBC;DSN=MYDSN;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=THESYSTEM.DOMAIN.COM;COMMANDTIMEOUT=0;Uid=MVIDAS;Pwd=********;

The SQL statement for one of these non-working queries:
SELECT digits(FIELD1)||digits(FIELD2), FIELD3, COUNT(*)
FROM LIB01.FL200711
WHERE (FIELD4 In (123,456,789)) AND (FIELD3<>'00')
GROUP BY FIELD1, FIELD2, FIELD3

Number of records in LIB01.FL200711: 1,780,319

Back in August the machine was upgraded to a "570"


Thanks for reading this far. I welcome any suggestions/advice/questions/etc!
Thanks
MattStart Free Trial