MVSFORUMS.com

[jim haire]

One of our developers is running a DSNTEP2 job to determine the number of rows on a production table.

He is getting a -802 error, which leads me to believe that it is because the number of rows on the table exceeds the highest allowable number for an integer type value (I believe a COUNT(*) returns an integer).

I know from past RUNSTATS that there were over 2 billion rows on this table on an earlier date. What type of SQL can be written to determine
the count of rows on this table?

Note: I am trying SELECT DECIMAL(COUNT(*)) from THE_TABLE, but I'm not sure it won't return an error again.

[jsharon1248]

I don't think that will work. Try COUNT_BIG(*). COUNT_BIG returns a DECIMAL(31,0).

[kolusu]

jim haire,

Count indeed returns the result as an Integer of 4 bytes which can contain a max value of 2,147,483,647.

However you can use COUNT_BIG which gives you the result in decimal number with precision 31 and scale 0.

Check this link which explains the COUNT_BIG function

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQJ12/3.1.3?DT=20050325102208

Hope this helps...

Cheers
_________________
Kolusu
www.linkedin.com/in/kolusu

[jim haire]

Thanks. COUNT_BIG is what I was looking for.