IBM Mainframe Forum

I'm running Rexx Db2 to fetch the count of all table in sysibm.systables.

it is like this i'm declaring a cursor and fetching the count dynamically, for every table i'm using a different cursor,
cursor name is automatically incremented like s1,s2,s3........s100,s101...


this is my cursor name and from s101 cursor it shows sqlcode =-104.

is there any problem in declaring cursor, and i dont have any close cursor in my program.

Hello,

Suggest you post the code. . .

If would be a good idea to open and close each cursor as you use them.

Posting the complete diagnostic info may help someone help you.

What happens if you start the process at s101?

If you want the number of rows for each table in sysibm.systables then you can get this from the CARD column of sysibm.systables. You can run a RUNSTATS job which will update the DB catalog tables, then you will have an accurate count of all the tables populated in the CARD column of sysibm.systables, so you shouldn't even need to read any other table.

If you can't run a RUNSTATS job before running this query then you'll have to find a resolution to your problem, so post some more info, as Dick suggests.

Cheers
Steve.

Thanks guys for our reply....

I got my answer ....

Now i'm using only one cursor, instead of looping 100 of cursor,

BUT I WILL TRY THE CARD COLUMN ,
IT SURE TO HELP ME, BUT WAT IF I'M NOT RUNNING RUNSTATS,
AND WHEN WILL THAT SYSIBM.SYSTABLE BE UPDATED EVERYTIME? COULD YOU PLS HELP ME......
WITH THE RUNSTATS

To use the CARD column you need to run the DB2 RUNSTATS before, otherwise the column contents will not be accurate. Do you have a DBA on-site that you can talk to about setting up a RUNSTATS job for you. It's a standard DB2 utility so there should be no problem in setting one up.

select creator, name from sysibm.systables

open the cursor above
do until done...
fetch cursor into...
select count(*)
from &creator.&name
end do

Each select count(*) will be the number of rows in the given table.

Although this post is old, i like to mention what is the reason behind db2 throwing -104, in Rexx Cursor names and Statement names are pre defined as C1...C100 and s1...s100..So if you are trying to use Cursor names or statement names other than predefined names you will be getting a syntax error.

So try correcting the names and rerun it, it will go fine.