-802 error for count(*) query for a DB2 table

[srilata83]

Hi,
I am trying to query a big table which contains huge data,I just tried to check the no of records of table, i have run the query for COUNT(*) in QMF, But i am not getting the result , i am getting an error -802 ,saying commit frequency must be greater than zero.
Is there any way of getting the count?
I tried in platinum tool also, but its N/A over there in 'No. of rows' field.

Please help me in this regard.

[vijay2082]

If you use COUNT(*) with a table that has more than 2,147,483,647 rows, you will receive the following message:
SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=22003

If you look up the message, you will find out that received this particular error because your query result was greater than the :MAX_LONGINT_IN T SQL limit of 2,147,483,647 rows.

The solution is to simply replace COUNT(*) with COUNT_BIG(*).

Cheers, Vijay

[srilata83]

Thank you, Vijay.

I came to know a new thing from u....

Thanks a lot.... :)

[docdiesel]

Hi,

if there's a primary key on your table (e.g. column "id") or another indexed field, then use this by COUNT(ID) instead of COUNT(*).

The latter one means that DB2 has to do a complete table scan, resulting in lots of i/o and consuming much more time that using an indexed column.

Regards


Doc

[vijay2082]

Hi Doc,

Shouldn't be an issue with the table scan. Count(*) or count_big(*) has inetrnal working and the UDB optimizer choose the least possible resources(inclu ding any defined key etc) to produce the result.

Cheers, Vijay

[vijay2082]

C:\Users\vijay> db2 describe indexes for table db2admin.column s

Index Index Unique Number of
schema name rule columns
-------------- ------------------- -------------- --------------

0 record(s) selected.

explain plan without any indexes :

C:\Program Files\IBM\SQLLI B\MISC>db2expln -d paulb001 -i -t -g -q "select count(*) from db2admin.column s" -noenv

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

*************** ***** DYNAMIC *************** *************** *********

=============== ===== STATEMENT =============== =============== ============

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM" ,
"VIJAY"


Statement:

select count(*)
from db2admin.column s


Section Code Page = 1252

Estimated Cost = 71.966728
Estimated Cardinality = 1.000000

( 3) Access Table Name = DB2ADMIN.COLUMN S ID = 2,4
| #Columns = 0
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
( 3) | | Predicate Aggregation
| | | Column Function(s)
( 2) Aggregation Completion
| Column Function(s)
( 1) Return Data to Application
| #Columns = 1

End of section


Optimizer Plan:

RETURN
( 1)
|
GRPBY
( 2)
|
TBSCAN
( 3)
|
Table:
DB2ADMIN
COLUMNS

well as expected its a table scan, should be. :)

C:\vijay>db2 create index idx01 on db2admin.column s(length)
DB20000I The SQL command completed successfully.

C:\vijay>db2 runstats on table db2admin.column s with distribution and detailed indexes all
DB20000I The RUNSTATS command completed successfully.

Explain plan after creating the index :

C:\vijay>db2exp ln -d paulb001 -i -t -g -q "select count(*) from db2admin.column s" -noenv

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

*************** ***** DYNAMIC *************** *************** *********

=============== ===== STATEMENT =============== =============== ============

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM" ,
"VIJAY"


Statement:

select count(*)
from db2admin.column s


Section Code Page = 1252

Estimated Cost = 23.113621
Estimated Cardinality = 1.000000

( 3) Access Table Name = DB2ADMIN.COLUMN S ID = 2,4
| Index Scan: Name = VIJAY.IDX01 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: LENGTH (Ascending)
| #Columns = 0
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
( 3) | | Predicate Aggregation
| | | Column Function(s)
( 2) Aggregation Completion
| Column Function(s)
( 1) Return Data to Application
| #Columns = 1

End of section


Optimizer Plan:

RETURN
( 1)
|
GRPBY
( 2)
|
IXSCAN
( 3)
/ \
Index: Table:
VIJAY DB2ADMIN
IDX01 COLUMNS

You can see udb optimizer is smart enough to pick up the desired index. To explain further :

C:\vijay>db2exp ln -d paulb001 -i -t -g -q "select count(length) from db2admin.column s" -noenv

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

*************** ***** DYNAMIC *************** *************** *********

=============== ===== STATEMENT =============== =============== ============

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM" ,
"VIJAY"


Statement:

select count(length)
from db2admin.column s


Section Code Page = 1252

Estimated Cost = 23.113621
Estimated Cardinality = 1.000000

( 3) Access Table Name = DB2ADMIN.COLUMN S ID = 2,4
| Index Scan: Name = VIJAY.IDX01 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: LENGTH (Ascending)
| #Columns = 0
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
( 3) | | Predicate Aggregation
| | | Column Function(s)
( 2) Aggregation Completion
| Column Function(s)
( 1) Return Data to Application
| #Columns = 1

End of section


Optimizer Plan:

RETURN
( 1)
|
GRPBY
( 2)
|
IXSCAN
( 3)
/ \
Index: Table:
VIJAY DB2ADMIN
IDX01 COLUMNS



C:\vijay>db2exp ln -d paulb001 -i -t -g -q "select count(scale) from db2admin.column s" -noenv

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

*************** ***** DYNAMIC *************** *************** *********

=============== ===== STATEMENT =============== =============== ============

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM" ,
"VIJAY"


Statement:

select count(scale)
from db2admin.column s


Section Code Page = 1252

Estimated Cost = 23.113621
Estimated Cardinality = 1.000000

( 3) Access Table Name = DB2ADMIN.COLUMN S ID = 2,4
| Index Scan: Name = VIJAY.IDX01 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: LENGTH (Ascending)
| #Columns = 0
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
( 3) | | Predicate Aggregation
| | | Column Function(s)
( 2) Aggregation Completion
| Column Function(s)
( 1) Return Data to Application
| #Columns = 1

End of section


Optimizer Plan:

RETURN
( 1)
|
GRPBY
( 2)
|
IXSCAN
( 3)
/ \
Index: Table:
VIJAY DB2ADMIN
IDX01 COLUMNS

In all case it will use the index that best suits the query.

Cheers, Vijay