sql -129 during a simple select statement

[Former Member]

I've got a problem on a system running 4.6c where a statement

SELECT * FROM afvv

         INTO TABLE afvv_tab

         FOR ALL ENTRIES IN aufpl_pre_tab

         WHERE aufpl EQ aufpl_pre_tab-aufpl.

returns an error code SQL -129  (which means sql statement contains too many tables).

It clearly does not contain too many tables in the SQL statement, and there is only one entry in the internal table aufpl_pre_tab.

If I use strsql to execute the statement

select * from afvv where aufpl='<the value from aufpl_pre_tab>'

it returns correctly the 3 entries that it should.

The system has recently been recovered from a backup after a series of disk failures occurred. I'm told the restore was successful and clean.

Does anyone have any idea what could cause this?

[and yes, I know 4.6c is not supported any longer...]

[christian_bartels · Accepted solution]

Did you restore the SQL packages along with the rest of system after the disk failures? If so, I would recommend to delete the SQL packages as described in SAP Note 63037. There may be a bad pointer in an SQL package, and that can have unpredictable results, such as unexpected SQL errors.

If that does not help, you should check the developer trace and the joblog for further information, such as the message(s) preceeding the SQL0129 error code. You may have to report the problem to IBM then (hoping that you are running a supported level of IBM i).

Kind regards,

Christian Bartels.

[Former Member · Accepted solution]

Thanks Christian, yes, I thought I had deleted the SQL packages using R3DLTPKG; however, it looked like some did not delete, so I stopped the system again and ran it once more. This prevented the SQL -129 errors as you suggested. I did then have poor performance on numerous statements (basically table scanning) for which I found that the only solution was to recreate the indexes (/constraints) which I did in SE11 when I spotted table scanning going on. I had to do this for many tables. There were no reported bad indexes showing on ipl when they should have been rebuilt as far as I can tell. However, this seemed to resolve it in the end.

Thanks for the help.

Andy.