RExx Db2 cursor issue...Help me



IBM's flagship relational database management system

RExx Db2 cursor issue...Help me

Postby manojlives » Thu May 14, 2009 11:06 pm

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.
manojlives
 
Posts: 37
Joined: Wed Dec 24, 2008 5:15 pm
Has thanked: 0 time
Been thanked: 0 time

Re: RExx Db2 cursor issue...Help me

 

Re: RExx Db2 cursor issue...Help me

Postby dick scherrer » Fri May 15, 2009 12:02 am

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?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: RExx Db2 cursor issue...Help me

Postby swd » Fri May 15, 2009 12:41 pm

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.
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Re: RExx Db2 cursor issue...Help me

Postby manojlives » Thu Jun 11, 2009 6:24 am

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
manojlives
 
Posts: 37
Joined: Wed Dec 24, 2008 5:15 pm
Has thanked: 0 time
Been thanked: 0 time

Re: RExx Db2 cursor issue...Help me

Postby swd » Thu Jun 11, 2009 6:20 pm

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.
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Re: RExx Db2 cursor issue...Help me

Postby mikerexx » Fri Aug 07, 2009 8:10 am

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.
mikerexx
 
Posts: 5
Joined: Wed Aug 05, 2009 7:54 am
Has thanked: 0 time
Been thanked: 0 time

Re: RExx Db2 cursor issue...Help me

Postby mainframedasan » Fri Aug 05, 2011 4:48 pm

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.
mainframedasan
 
Posts: 3
Joined: Fri Oct 24, 2008 11:32 am
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post