db2pd -

A new DB2 UDB utility for monitoring DB2 instances and databases

ref

How can I make an SQL INSERT faster?

DB2 has added a new keyword to the CREATE and ALTER TABLE SQL statements: APPEND. This new keyword enables a pretty simple concept. If you CREATE or ALTER a TABLE to APPEND YES, DB2 simply sticks the new row at the end of the table*, makes no attempt at searching for available space, and makes no effort to preserve any kind of clustering order. Because you can ALTER this attribute on and off, you can switch it on (YES) for that massive insert batch job you run once a month and always follow with REORG/RUNSTATS anyway, then switch it back off (NO) for your day to day online insert processing. REORG is unaffected by the APPEND option so you can use it in conjunction with a tables clustering options allowing the object to take advantage of a faster insert and still maintaining a clustered sequence by the REORG and LOAD utilities.

The APPEND will work for all tables except those created in LOB, XML, and work files table spaces. BTW, this process is for insert and online LOAD operations. There is also a new column, APPEND, in SYSIBM.SYSTABLES so you can track when this feature has been turned on or off. In addition, you are going to see some index relief for inserts in DB2 9. But I'll save that for another post.



Ref

Revision: Runstats vs Reorg

In case you are new, we run REORG & then RUNSTAT when we do mass inserts/updates/deletes on DB or individual tables,so that the query runs faster . REORG and RUNSTAT are DB command.

RUNSTATS

1) Updates datadictionary stats. When a query is fired it reads the data dictionary table (dictionary managed) and calculates the cost,etc.

2) Gathers summary information about the characteristics of the data in table spaces and indexes. This information is recorded in the DB2 catalog, and is used by DB2 to select access paths to data during the bind process. It is available to the database administrator for evaluating database design, and determining when table spaces or indexes should be reorganized .

REORG

1) Put all the contiginous blocks together,so data reads / inserts will happen faster as it need not scan the pages. (example is defragmentation in windows)

2) Reorganized a table space to improve access performance and reclaim fragmented space. In addition, the utility can reorganize a single partition of either a partitioned index ora partitioned table space. If you specify REORG UNLOAD ONLY or REORG UNLOAD PAUSE, the REORG utility unloads data in a format acceptable to the LOAD utility of the same DB2 table space.

REORGCHK

do a reorgchk on a certain schema, which will runstat all tables in the schema, whereas doing runstats alone,
then you need to do it table by table.


TIPS

1) Run REORG & then RUNSTAT.

2) It is not necessary to run reorg for all tables :use REORGCHK ON TABLE ALL find which tables require reoganization and run REORG & then RUNSTAT for that specified tables.


EXAMPLE
REORG INDEXES ALL FOR TABLE MYSCHEMA.TABLEA ALLOW WRITE ACCESS;
RUNSTATS ON TABLE MYSCHEMA.TABLEA WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE MYSCHEMA.TABLEA ON ALL COLUMNS ALLOW WRITE ACCESS;


ref

Where exactly is a DB2 plan stored?

Catalog contains information about plans in the following tables:

• SYSIBM.SYSDBRM
• SYSIBM.SYSPLAN
• SYSIBM.SYSPLANAUTH
• SYSIBM.SYSPLANDEP
• SYSIBM.SYSSTMT
  

And, the DB2 Catalog contains information about packages in the following tables:

• SYSIBM.SYSPACKAGE
• SYSIBM.SYSPACKAUTH
• SYSIBM.SYSPACKDEP
• SYSIBM.SYSPACKLIST
• SYSIBM.SYSPACKSTMT
• SYSIBM.SYSPKSYSTEM
• SYSIBM.SYSPLSYSTEM

ref

Step SQL Optimize

db2expln -d tibsdb -f xx/sql -o xx.log

db2expln - SQL Explain Command
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0005736.htm

The Two Biggest DB2 Performance Things
ref

Check index
====
db2 "describe indexes for table tibsadmin.fundtransferhistory"
db2 "SELECT colnames,tbname FROM SYSIBM.SYSINDEXES where upper(tbname) =upper('fundtransferhistory') ";

db2expln -d tibsdb -f 1.sql -o 1.log


drop index ;
CREATE INDEX {index_name} ON {table} ( {column1} ASC, {column2} ASC) ;
CREATE INDEX {index_name} ON {table} ( {column1} ASC) CLUSTER ;
**cluster index is physical index. Only 1 cluster index per table

Setup JMeter for load test
====================
pre requisite-put the db2 drirver.jar to jmeter/lib

1)Test Plan -> Add Thread Group
2)Thread Group->Add->Config Element ->JDBC Connection Configuration
3)Max number of connection = {> Number of thread}
Database url = jdbc:db2://{host}:{port}/{dbname}
Jdbc driver classess = com.ibm.db2.jcc.DB2Driver
username=
password=
**package for "com.ibm.db2.jcc" is ibm jdbc driver type4
**package for "COM.ibm.db2.jdbc" is ibm jdbc driver type2 which use by websphere server
**"DB2Driver" class is normal driver
**"DB2ConnectionPoolDataSource" class is connection pool data source.
****jdbc:db2://10.100.101.30:61099/tibsdb


4)Thread Group->Add->Sampler->JDBC Request
**For insert sql, u can create Non Duplicate ID using this function
CHAR( ${__counter(FALSE,100)} )

example:
INSERT INTO Schema.Mytable VALUES ( CHAR( ${__counter(FALSE,100)} )
, 'AbcValue')

5)ThreadGroup ->Add->Listner
i)Summary report - to view summary statistic
ii)View Results Tree - to check the request and response data
iii) Aggregate Report - Have 90% line











ref 1 : How to easily populate a table with random data