Force db2 statistics cardinality

Look at this scenario (kindly provided to me by my collegue Lisa).

You have a session table that contains all the user sessions, so one row for each login session.
If you want to execute runstats on this table you should run when there is the peek of users logged, otherwise if you do when there are a few of users logged then you get a low cardinality number in the statistics catalog. The problem is that runstats are generally run when the system is quiet, during the night because it is a CPU and disk intensive operation, so it is not possibile to get the maximun cardinality because there are not many users in the session table at the night.

Then when the cardinality is low DB2 will prepare an access plan with a table scan even if there is an index, because if it would be faster. This is true in the case of a small table when all the data fits on one data page only in DB2, then table scanning is faster than using an index.

update sysibm.sysindexes set indcard=10 where name='MYINDEX'

Instead if you want to force the access plan to use the index change the cardinality of the table, execute an update statement to set the maximum number of rows (of the peek hours) on the statistics without the need to run runstats.

update sysibm.sysindexes set indcard=10000 where name='MYINDEX'

DB2 basic performance error: missing indexes

How many times during testing of a new service or web interface, you get angry because low performance with large amount of data? Stop the panic, revise your checklist and in particular,
ask to this question, are you 100% sure all the queries are the "where" and "order by" clauses using columns that have a defined index? When if you are not sure, first check it.
On DB2 you can run a simple command:

db2 connect to MYDB
db2 SELECT INDNAME,COLNAMES FROM SYSCAT.INDEXES WHERE TABNAME='mytable' AND TABSCHEMA='myschema'

Check the results on the COLNAMES looking for yours. If any column is not present, then evaluate to add a new index. But before do this make sure your application does not perform very large UPDATE statements on that table, otherwise you will have slower UPDATE statements because there is this additional index to maintain up-to-date. So maintaining a new index has a CPU cost, but also a footprint cost. On OLTP systems you should have less indexes respect to a data warehouse system.

To create a simple index on a single column for example run:

db2 "CREATE INDEX myschema.myindex ON myschema.mytable (mycolname DESC) COLLECT STATISTICS COMPRESS NO ALLOW REVERSE SCANS"

To get the benefit of this new index you need to update the statistics with the new index:

db2 "RUNSTATS ON TABLE myschema.mytable WITH DISTRIBUTION AND DETAILED INDEX myschema.myindex"

Now verify if your queries and the entire system got an improvement by this new index.

An administrative activity is sometime necessary to reorganize physically the pages of the table and of its indexes to not-fragmented and continuous pages:

db2 "REORG TABLE myschema.mytable"
db2 "REORG INDEXES ALL FOR TABLE myschema.mytable"

or for the single index:

db2 "REORG INDEX myschema.myindex FOR TABLE myschema.mytable"


In this post I've not talked of indexes defined for multiple column. More details in the DB2 reference. Look also at this IBM article for suggestions when creating indexes:

http://www.ibm.com/developerworks/data/library/dmmag/DMMag_2010_Issue4/DataArchitect/



DB2 references below.

CREATE INDEX command:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020190.html

RUNSTATS command:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001980.html?cp=SSEPGG_9.7.0%2F3-6-2-4-108&lang=en

REORG command:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001966.html?cp=SSEPGG_9.7.0%2F3-6-2-4-96

DB2 create explain tables and check access plan of a query

DB2 provides some commands to visualize the access plan of the queries. To run these commands it needs to creates the explain and and advise tables (working tables) on the database:

db2 connect to DB_NAME

db2 "call SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))"

This command above create a set of tables under SYSTOOLS schema this is the target default place, but you can change it. Note, the option to delete these tables is 'D' instead of  'C'.    

After creating the tables you can run the command with your statement to analyze:

db2expln -database DB_NAME -schema SCHEMA_NAME -package % -statement "your query" -terminal -graph

or put your statement on a file, put the terminator ";" in the statement:

db2expln -database DB_NAME -schema SCHEMA_NAME -package % -stmtfile query.sql -terminator ";" -terminal -graph

A graph plan is displayed to visualize the access plan of the specified query.

If you want to get advise from DB2 run the command:

db2advis -d DB_NAME -q SCHEMA_NAME -s "thequery" -noxml

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008441.html?cp=SSEPGG_10.1.0%2F2-9-13