DB2 v10.1 for Z/OS. SELECT COUNT(*) ended with error: SQLCODE=-802, SQLSTATE=22003

Hi!

I have trouble to calculate count of rows for big tables.

For example:

select cast((count(*)) as BIGINT) as count_of_rows from OWNER.MYTABLE with UR;

Toad output:

Lookup Error - DB2 Database Error: ERROR [22003] [IBM][DB2] SQL0802N  Arithmetic overflow or other arithmetic exception occurred.

ZOS DB2 output:

Rollback done
SQLCODE : -802 DSNTIAR CODE : 0
DSNT408I SQLCODE = -802, ERROR: EXCEPTION ERROR FIXED POINT OVERFLOW HAS
OCCURRED DURING COLUMN FUNCTION OPERATION ON INTEGER DATA, POSITION
DSNT418I SQLSTATE = 22003 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRRC SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 103 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000067' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

If perform runstat, it count more than 2.6 billions of lines.

 

Any help would be appreciated!

 

Use COUNT_BIG() function

Isaac Yassin
Sent from my Galaxy Note 5

בתאריך יום ב׳, 8 באוק׳ 2018, 16:03, מאת Oleg Dayneko ‏<[login to unmask email]
>:

> Hi!
>
> I have trouble to calculate count of rows for big tables.
>
> For example:
>
> select cast((count(*)) as BIGINT) as count_of_rows from OWNER.MYTABLE with
> UR;
>
> Toad output:
>
> Lookup Error - DB2 Database Error: ERROR [22003] [IBM][DB2] SQL0802N
> Arithmetic overflow or other arithmetic exception occurred.
>
> ZOS DB2 output:
>
> Rollback done
> SQLCODE : -802 DSNTIAR CODE : 0
> DSNT408I SQLCODE = -802, ERROR: EXCEPTION ERROR FIXED POINT OVERFLOW HAS
> OCCURRED DURING COLUMN FUNCTION OPERATION ON INTEGER DATA, POSITION
> DSNT418I SQLSTATE = 22003 SQLSTATE RETURN CODE
> DSNT415I SQLERRP = DSNXRRC SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD = 103 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD = X'00000067' X'00000000' X'00000000' X'FFFFFFFF'
> X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
>
> If perform runstat, it count more than 2.6 billions of lines.
>
>
>
> Any help would be appreciated!
>
>
>
> -----End Original Message-----
>

Thank you! 

This function solved my trouble.