Recreate optimizer access plans using db2look

A tool to aid in tuning queries

Return to article

Table 2. Comparing configurations: prod_sample_exfmt.txt (Production SAMPLE database access plan)
Database ContextValue
ParallelismNone
CPU speed9.446886e-007
Comm speed:0
Buffer pool size:250
Sort Heap size:256
Database Heap size:600
Lock List size:50
Maximum Lock List:22
Average applications:1
Locks available:1122
Package ContextValue
SQL Type:Dynamic
Optimization level:5
Blocking:Block all cursors
Isolation Level:Cursor Stability
Table 3. Comparing configurations: test_dummy_exfmt.txt (Test DUMMYDB database access plan)
Database ContextValue
ParallelismNone
CPU speed9.446886e-007
Comm speed:0
Buffer pool size:250
Sort Heap size:256
Database Heap size:600
Lock List size:50
Maximum Lock List:22
Average applications:1
Locks available:1122
Package ContextValue
SQL Type:Dynamic
Optimization level:3
Blocking:Block all cursors
Isolation Level:Cursor Stability
------- STATEMENT 1  SECTION 201 ---------
	QUERYNO: 		3
	QUERYTAG: 		CLP                 
	Statement Type: 	Select
	Updatable: 		No
	Deletable: 		No
	Query Degree: 		1


1) RETURN: (Return Result)
	Cumulative Total Cost: 	25.8823
	Cumulative CPU Cost: 	193000
	Cumulative I/O Cost: 	2
	Cumulative Re-Total Cost: 	25.8823
	Cumulative Re-CPU Cost: 	193000
	Cumulative Re-I/O Cost: 	2
	Cumulative First Row Cost: 	25.8823
	Estimated Bufferpool Buffers: 2

	Arguments:
	---------
	BLDLEVEL: (Build level)
		DB2 v8.1.8.852 
	ENVVAR  : (Environment Variable)
		DB2_HASH_JOIN = yes
	ENVVAR  : (Environment Variable)
		DB2_INLIST_TO_NLJN = yes
	STMTHEAP: (Statement heap size)
		2048
------- STATEMENT 1  SECTION 201 ---------
	QUERYNO: 		1
	QUERYTAG: 		CLP                 
	Statement Type: 	Select
	Updatable: 		No
	Deletable: 		No
	Query Degree: 		1


1) RETURN: (Return Result)
	Cumulative Total Cost: 	25.8843
	Cumulative CPU Cost: 	195076
	Cumulative I/O Cost:       2
	Cumulative Re-Total Cost: 	25.8843
	Cumulative Re-CPU Cost: 	195076
	Cumulative Re-I/O Cost: 	2
	Cumulative First Row Cost: 	25.8445
	Estimated Bufferpool Buffers: 2

	Arguments:
	---------
	BLDLEVEL: (Build level)
		DB2 v8.1.8.852 
	ENVVAR  : (Environment Variable)
		DB2_HASH_JOIN = yes
	ENVVAR  : (Environment Variable)
		DB2_INLIST_TO_NLJN = yes
	STMTHEAP: (Statement heap size)
		2048

Return to article

Note: To print this page, use landscape printing option.