Working as a DB2 UDB Support Analyst, I frequently hear the following question from customers who are dealing with an optimizer or query planning problem:

"How do I recreate the same query access plan on my test environment that I am getting on my production environment?"

Many times, there is a need to replicate a production environment to a test environment, including recreating the same access plan for query analysis purposes.

For example, in production, you could experience performance problems caused by a query that is using a poor access plan and want to replicate the access plan on a test system in order try some different strategies, such as manipulating the statistics, changing the optimization level, trying different settings for DB2 registry variables, and so on, in order to improve performance.

In an ideal world, you would want to have the test environment as closely matched to production as possible. That is, you would want to use exactly the same hardware, operating system maintenance level and configuration, DB2 level, and configuration in both environments, as well as using the same data in test as in production. However, this ideal cannot always be achieved. If the production environment has a very large amount of data, you may simply not have the capacity to keep a test copy of the production system.

The db2look

This article will explain how you can mimic a production system on a test system without the need for actual data in order to recreate a query planning problem. This ability will help you to debug queries and understand access plan issues without interrupting work in the production environment. Note, however, that if you want to test the execution of the resulting access plan, you will still need to load data from production onto test (as much data as possible). And there is always the possibility that the differences between the test and production systems are still enough that the execution characteristics on test do not match those on production. This part of analysis (performance tuning) is as much an art as science.

Other problems in the optimizer or query compiler area, such as SQL0901N errors or instance crashes, can also be recreated using the methods explained in this article. You can try various strategies, such as testing the most recent fix pack (if the system is at an older fix level), different optimization levels, different registry variables, and so on, in order to see if these changes will correct the problem.

Let's look at the options to use with db2look

The db2look

Here are the commands you use to capture the needed information from your production system:

Listing 1. Commands to recreate an optimizer problem

`db2look -d <dbname> -l -o storage.out db2look -d <dbname> -f -fd -o config.outdb2look -d <dbname> -e -a -m -o db2look.out db2look -d <dbname> -e -a -m -t table1 table2 .... tableX -o table.ddl`

Now let's look at these db2look

Generate buffer pool, tablespace, and database partition group information

`db2look -d <dbname> -l -o storage.out`

Here is a description of the options used in the db2look-d

: Database name -- This must be specified.

-l

: Generates database layout. This is the layout for database partition groups, buffer pools and tablespaces.

-o

: Redirects the output to the given file name. If the

-oThe -l option is important to mimicking your production environment. Ideally, you want to have the same buffer pools, database partition groups (if you're in a multi-partition environment), and tablespace information (including temporary tablespaces). However, if you are constrained by memory and cannot allocate the large buffer pools that you have in production, then use thedb2fopt

It is not always possible to have the same tablespaces set up in test that you have in production. For example, you may have devices set up with large sizes, and you may not have the flexibility to create the same device sizes in test. Or, you may not have a separate tablespace device available at all in the test environment. In addition, you might not have the same paths set up in test that you have in production. You would need to alter the paths, devices, and files appropriately to fit your test environment.

The important information used by the optimizer for a tablespace is the following. This is what you would want to make sure are the same on both test and production. (Note: The numbers shown here are an example. You should use the same settings on test as you do on production.)

`PREFETCHSIZE 16EXTENTSIZE 16OVERHEAD 12.670000TRANSFERRATE 0.180000`

If a tablespace is "managed by database" on production, it should also be "managed by database" on test. If it is "managed by system" on production, it should also be that way on test.

Note: If this is a system with multiple physical partitions (MPP), the number of partitions in the database partition group must be the same on test. However, the number of physical machines does not have to be the same. The number of logical partitions in the whole MPP environment must be the same on both test and production.

Generate configuration parameters and registry variables

`db2look -d <dbname> -f -fd -o config.out`

Here, I've used the following parameters:

-f

: Extracts configuration parameters and registry variables. If this option is specified,

-wrapper

-server-fd

: Generates db2fopt statements for

opt_buffpage

opt_sortheap

, along with other configuration and registry settings.

The output of the command looks like this:

Listing 2. Sample output from db2look command

$ db2look -d sample -f -fd-- No userid was specified, db2look tries to use Environment variable USER-- USER is: SKAPOOR-- This CLP file was created using DB2LOOK Version 8.2-- Timestamp: Sat Mar 26 00:13:36 EST 2005-- Database Name: SAMPLE-- Database Manager Version: DB2/6000 Version 8.2.2-- Database Codepage: 819-- Database Collating Sequence is: UNIQUECONNECT TO SAMPLE; -------------------------------------------------------- -- Database and Database Manager configuration parameters -------------------------------------------------------- UPDATE DBM CFG USING cpuspeed 6.523521e-07; UPDATE DBM CFG USING intra_parallel NO; UPDATE DBM CFG USING federated NO; UPDATE DBM CFG USING fed_noauth NO; !db2fopt SAMPLE update opt_buffpage 50000; !db2fopt SAMPLE update opt_sortheap 10000; UPDATE DB CFG FOR SAMPLE USING locklist 1000; UPDATE DB CFG FOR SAMPLE USING dft_degree 1;UPDATE DB CFG FOR SAMPLE USING maxlocks 10; UPDATE DB CFG FOR SAMPLE USING avg_appls 1; UPDATE DB CFG FOR SAMPLE USING stmtheap 2048; UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5; --------------------------------- -- Environment Variables settings --------------------------------- !db2set DB2_ANTIJOIN=yes; !db2set DB2_INLIST_TO_NLJN=yes; COMMIT WORK;CONNECT RESET; TERMINATE;

$ db2look -d sample -f -fd-- No userid was specified, db2look tries to use Environment variable USER-- USER is: SKAPOOR-- This CLP file was created using DB2LOOK Version 8.2-- Timestamp: Sat Mar 26 00:13:36 EST 2005-- Database Name: SAMPLE-- Database Manager Version: DB2/6000 Version 8.2.2-- Database Codepage: 819-- Database Collating Sequence is: UNIQUECONNECT TO SAMPLE; -------------------------------------------------------- -- Database and Database Manager configuration parameters -------------------------------------------------------- UPDATE DBM CFG USING cpuspeed 6.523521e-07; UPDATE DBM CFG USING intra_parallel NO; UPDATE DBM CFG USING federated NO; UPDATE DBM CFG USING fed_noauth NO; !db2fopt SAMPLE update opt_buffpage 50000; !db2fopt SAMPLE update opt_sortheap 10000; UPDATE DB CFG FOR SAMPLE USING locklist 1000; UPDATE DB CFG FOR SAMPLE USING dft_degree 1;UPDATE DB CFG FOR SAMPLE USING maxlocks 10; UPDATE DB CFG FOR SAMPLE USING avg_appls 1; UPDATE DB CFG FOR SAMPLE USING stmtheap 2048; UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5; --------------------------------- -- Environment Variables settings --------------------------------- !db2set DB2_ANTIJOIN=yes; !db2set DB2_INLIST_TO_NLJN=yes; COMMIT WORK;CONNECT RESET; TERMINATE;

The -f and -fd options are key options to use in order to extract configuration parameter and environment variables, which the optimizer uses during access plan phase. In Listing 2, above, note the following output that resulted from the -fd

`!db2fopt SAMPLE update opt_buffpage 50000; !db2fopt SAMPLE update opt_sortheap 10000;`

The db2fopt command tells the optimizer to use the specified value for "Buffer pool size," rather than adding up the pages of the buffer pools available. (Buffer pool size in the db2exfmt output is discussed further in the buffer pool size section below.). For example, say that you cannot afford to have large buffer pools due to memory constraints on the test system and would like to configure the size the same without actually having them in reality. Use the -fd option, which would generate the db2foptcommands required to tell the optimizer to use the specified size rather than calculating based on the buffer pools available for this database.

It works the same way for sort heap, as we'll see in the sort heap section below.

Here is the usage for the db2fopt command. Note, that the -fd option with db2look

`C:\>db2foptUsage: db2fopt <database-alias> update [opt_buffpage <value>] [opt_sortheap <value>f] or db2fopt <database-alias> get [opt_buffpage] [opt_sortheap]]`

If you would like to set the values for opt_buffpage and opt_sortheap, issue:

`db2fopt <dbname> update opt_buffpage <value> opt_sortheap <value>`

For example:

`C:\>db2fopt sample update opt_buffpage 50000 opt_sortheap 10000Update succeeded`

Make sure to terminate and reconnect to the database.

If you would like to view the values, issue:

C:\>db2fopt sample get opt_buffpage opt_sortheap opt_buffpage value is 50000 opt_sortheap value is 10000

And if you would like to reset the values so that we do not use these two parameters, and go back to using syscat.bufferpools for bufferpool pages estimate, and database configuration for sortheap size, issue:

`C:\>db2fopt sample update opt_buffpage -1 opt_sortheap -1Update succeeded`

Make sure to terminate and reconnect to the database.

To make sure that they are reset, use the get option in db2fopt

`C:\>db2fopt sample get opt_buffpage opt_sortheapopt_buffpage value is -1opt_sortheap value is -1`

If you're a DBA, you will probably be using the DB2 SQL Explain Tool (db2exfmt) to gain an understanding of your SQL access plan. The db2exfmt tool is used to format the contents of the explain tables. If you look at the output of one of the access plans using db2exfmt from production, you will notice the following at the top of the plan. (Note: For the most part, these parameters are picked up by the -f and -fd option in the db2look output, with the exception of the dbheap

Listing 3. Sample output from db2exfmt

Database Context:---------------- Parallelism: None CPU Speed: 6.523521e-07 Comm Speed: 100 Buffer Pool size: 50000 Sort Heap size: 10000 Database Heap size: 5120 Lock List size: 1000 Maximum Lock List: 10 Average Applications: 1 Locks Available: 7849 Package Context: --------------- SQL Type: Dynamic Optimization Level: 5 Blocking: Block All Cursors Isolation Level: Cursor Stability ---------------- STATEMENT 1 SECTION 201 ---------------- QUERYNO: 1 QUERYTAG: CLP Statement Type: Select Updatable: No Deletable: No Query Degree: 1

Database Context:----------------         Parallelism:            None				        CPU Speed:              6.523521e-07              Comm Speed:             100         Buffer Pool size:       50000         Sort Heap size:         10000         Database Heap size:     5120         Lock List size:         1000         Maximum Lock List:      10         Average Applications:   1         Locks Available:        7849 Package Context: ---------------         SQL Type:               Dynamic         Optimization Level:     5         Blocking:               Block All Cursors         Isolation Level:        Cursor Stability ---------------- STATEMENT 1  SECTION 201 ----------------         QUERYNO:                1         QUERYTAG:               CLP         Statement Type:         Select         Updatable:              No         Deletable:              No         Query Degree:           1

If you go a bit further down in the db2exfmtNote: Again, unfortunately not all the relevant registry variables are listed by db2look -f. You will need to add the ones that are missing. In general, your registry variable settings on the test system should be identical, or as close as possible, to the settings on production.

Listing 4. Registry settings that affect access plan

1) RETURN: (Return Result) Cumulative Total Cost: 57.6764 Cumulative CPU Cost: 191909 Cumulative I/O Cost: 2 Cumulative Re-Total Cost: 5.37264 Cumulative Re-CPU Cost: 134316 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 26.9726 Estimated Buffer pool Buffers: 2 Arguments: --------- BLDLEVEL: (Build level) DB2 v8.1.0.80 : s041221 ENVVAR : (Environment Variable) DB2_ANTIJOIN=yes DB2_INLIST_TO_NLJN = yes STMTHEAP: (Statement heap size) 2048

1) RETURN: (Return Result)         Cumulative Total Cost:          57.6764         Cumulative CPU Cost:            191909         Cumulative I/O Cost:            2         Cumulative Re-Total Cost:       5.37264         Cumulative Re-CPU Cost:         134316         Cumulative Re-I/O Cost:         0         Cumulative First Row Cost:      26.9726         Estimated Buffer pool Buffers:   2         Arguments:         ---------         BLDLEVEL: (Build level)                 DB2 v8.1.0.80 : s041221         ENVVAR  : (Environment Variable) 	    	DB2_ANTIJOIN=yes                 DB2_INLIST_TO_NLJN = yes         STMTHEAP: (Statement heap size)                 2048

Create data definition language (DDL)

The following db2look

`db2look -d <dbname> -e -a -m -o db2look.out`

Here we've used the following parameters:

-a

: Generate statistics for all creators. If this option is specified, then the

-u-e

: Extract DDL file needed to duplicate database. This option generates a script containing DDL statements. The script can be run against another database to recreate database objects.

-m

: Run the

db2look

utility in mimic mode. This option generates a script containing SQL UPDATE statements. These

SQL UPDATE

statements capture all the statistics. This script can be run against another database to replicate the original one. When the

-m

option is specified, the

-p

-g

, and

-s

Gather statistics and DDL for a database subset

To gather statistics and ddl for only certain tables and related objects, use the following command:

`db2look -d <dbname> -e -a -m -t <table1> <table2> .. <tableX> -o table.ddl`

Here, I've used the following additional parameter:

Note: The -m -t

: Generate statistics for the specified tables. The maximum number of tables that can be specified is 30.

In addition, if you do not use the -a option, you could use the -zoption:-z

: Schema name. If

-z

-a

are both specified, then

-z

More details db2exfmt output

Database manager level configuration parameters

Note: Use the command db2 "get dbm cfg", in order to look at these parameters and db2 "update dbm cfg using <parameter> <value>"

Parallelism:
This parameter indicates whether inter- or intra-partition parallelism is enabled. If this is DPF with multiple partitions, then you will see Inter Partition Parallelism. If this is just SMP (intra_parallel enabled) single node environment, then you will see Intra Partition Parallelism. If both intra_parallel enabled and multiple partition environment, you will see both Inter and Intra partitions parallelismfor this parameter. And finally, if there is no inter- or intra-parallelism, this parameter will show NONE.

CPU Speed (cpuspeed):
The CPU speed (in milliseconds per instruction) is used by the SQL optimizer to estimate the cost of performing certain operations.

Communications speed: (comm_bandwidth)
The value specified for the communications bandwidth (in megabytes per second) is used by the SQL optimizer to estimate the cost of performing certain operations between partition servers of a partitioned database system.

Database level configuration parameters

Note: Use the command db2 "get db cfg for <dbname>" in order to look at these parameters and db2 "update db cfg for <dbname> using <parameter> <value>")

Buffer pool size:
The buffer pool size shown in db2exfmt output is determined by the buffpage parameter, if using buffpage as default for one buffer pool, or a calculation based on the contents of syscat.bufferpools. The number shown is the total number of buffer pool pages that are allocated for the database. For example, let's say we have the following buffer pools:

Table 1. Buffer pool setup

BUFFERPOOLNAME	SIZE
IBMDEFAULTBP	1000
BP1	1000
BP2	4000
BPIND1	1000
BPIND2	1000
BPLONG	1000
BPTEMP	1000
Total:	10,000

The db2exfmt output would show the total size as the sum of the number of pages in all the bufferpools. In our example above, it is 10,000. Note: Pagesize does not matter, just the number of pages.

You could use the -fd option in db2look to use the db2foptIn MPP, the opt_buffpage

Sort heap
This parameter defines the maximum number of private memory pages to be used for private sorts or the maximum number of shared memory pages to be used for shared sorts.

You should set this to the same value as in production. Again, using the -fd option in db2look, you will notice the following:

`!db2fopt SAMPLE update opt_sortheap 256;`

This will override the sortheap configuration parameter, and this is what the optimizer will use as the sortheap value. Again, in reality, the actual sortheap allocated at runtime will be determined by the sortheap setting in the database configuration. As withopt_buffpage, you can use opt_sortheap

Database heap size: (DBHEAP)
There is one database heap per database, and the database manager uses it on behalf of all applications connected to the database. It contains control block information for tables, indexes, table spaces, and buffer pools.

Lock list size: (LOCKLIST)
This parameter indicates the amount of storage that is allocated to the lock list.

Maximum lock list: (MAXLOCKS)
This parameter defines a percentage of the lock list held by an application that must be filled before the database manager performs escalation.

The locklist and maxlocks would help determine the type of locks that will be held during a certain scan (index scan or table scan), along with the isolation level. For example, in the plan you will notice, say, an index scan operation:

`IXSCAN: (Index Scan) TABLOCK : (Table Lock intent) INTENT SHARE`

Note: Do not be concerned if Locks Available differs in your db2exfmt

Average applications: (AVG_APPLS)
This parameter is used by the SQL optimizer to help estimate how much buffer pool will be available at run-time for the access plan chosen (since the buffer pool is shared by all active applications connected to the database).

Optimization Level: (DFT_QUERYOPT)
The query optimization class is used to direct the optimizer to use different degrees of optimization when compiling SQL queries

Query Degree: (DFT_DEGREE)

The degree of intra-partition parallelism for an SQL statement. if set to ANY, the optimizer is sensitive to the actual number of cpus that are online. if you use ANY, then the number of cpus on test and production should be configured the same, unless intra_parallel is disabled.

In addition to the above changes, there are some others that you must make sure are the same.

Number of frequent values retained: (NUM_FREQVALUES)

This parameter allows you to specify the number of "most frequent values" that will be collected when the WITH DISTRIBUTIONoption is specified on the RUNSTATSNumber of quantiles retained: (NUM_QUANTILES)

This parameter controls the number of quantiles that will be collected when the WITH DISTRIBUTION option is specified on theRUNSTATSThe above two, NUM_FREQVALUES and NUM_QUANTILES

SQL statement heap (4KB): (STMTHEAP)
The statement heap is used as a workspace for the SQL compiler during compilation of an SQL statement. This parameter specifies the size of this workspace. If this parameter is smaller on test than that in production, you may start seeing SQL0101N message due to lack of space in statement heap needed to compile the query. As well, you may see SQL0437W RC=1 , dropping down to greedy join enumeration if there is not enough statement heap for dynamic join enumeration.