Modeling a production environment on a test subsystem
You can improve the accuracy of access path testing by modeling the configuration and settings of a production environment in a test subsystem. The test system uses values that you specify for processor configuration, RID pool, sort pool, and buffer pool settings.
Before you begin
- A complete set of EXPLAIN tables must exist with the SYSIBM qualifier
on the production subsystem and in the test subsystem.
You can find a sample CREATE TABLE statement for each EXPLAIN table in member DSNTESC of the prefix.SDSNSAMP library. You can call the ADMIN_EXPLAIN_MAINT stored procedure to create EXPLAIN tables, upgrade them to the format for the current Db2 release, or complete other maintenance tasks. See ADMIN_EXPLAIN_MAINT stored procedure for information about using the action input parameter to request each of these tasks.
- A complete set of profile tables must exist on the test subsystem. For more information, see Profile tables.
About this task
Application testing is often conducted on test subsystems that have different parameters and configurations than the production subsystems that actually run the applications. Such differences can result in different access paths between the test and production subsystems. Such differences can cause performance problems to remain undetected on the test system, to be discovered only in the production environment.
- Processor speed
- Number of processors
- Maximum number of RID blocks
- Sort pool size
- Buffer pool sizes
Procedure
To model your production environment in a test subsystem:
- Gather the values to model in the test system from your
production system. The information that you might gather includes the processor speed and the number of processors, RID pool, sort pool, and buffer pool settings.
- Capture EXPLAIN information about the production system
to make the needed values available. You can execute the EXPLAIN statement for any SQL statement, but you must specify a unique QUERYNO value.For example, you might execute the following statements:
SET CURRENT DEGREE='ANY'; EXPLAIN ALL SET QUERYNO=2647 FOR SELECT * FROM SYSIBM.SYSDUMMY1;
- Execute a query to gather the required values from the
EXPLAIN data in the PLAN_TABLE. For example, you might use the following statements to gather the following information from the IBM_SERVICE_DATA column:
- Processor speed
- Number of processors
- Maximum number of RID blocks
- Sort pool size
SELECT (CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,69,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,69,1)),1,1)) END * POWER (16, 7) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,69,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,69,1)),2,1)) END * POWER (16, 6) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,70,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,70,1)),1,1)) END * POWER (16, 5) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,70,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,70,1)),2,1)) END * POWER (16, 4) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,71,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,71,1)),1,1)) END * POWER (16, 3) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,71,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,71,1)),2,1)) END * POWER (16, 2) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,72,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,72,1)),1,1)) END * POWER (16, 1) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,72,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,72,1)),2,1)) END * POWER (16, 0)) AS CPUSPEED_EXP, (CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,25,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,25,1)),1,1)) END * POWER (16, 3) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,25,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,25,1)),2,1)) END * POWER (16, 2) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,26,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,26,1)),1,1)) END * POWER (16, 1) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,26,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,26,1)),2,1)) END * POWER (16, 0)) AS NUMCP_EXP, (CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,13,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,13,1)),1,1)) END * POWER (16, 7) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,13,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,13,1)),2,1)) END * POWER (16, 6) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,14,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,14,1)),1,1)) END * POWER (16, 5) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,14,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,14,1)),2,1)) END * POWER (16, 4) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,15,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,15,1)),1,1)) END * POWER (16, 3) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,15,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,15,1)),2,1)) END * POWER (16, 2) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,16,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,16,1)),1,1)) END * POWER (16, 1) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,16,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,16,1)),2,1)) END * POWER (16, 0)) AS RIDPOOL_EXP, (CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,9,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,9,1)),1,1)) END * POWER (16, 7) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,9,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,9,1)),2,1)) END * POWER (16, 6) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,10,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,10,1)),1,1)) END * POWER (16, 5) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,10,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,10,1)),2,1)) END * POWER (16, 4) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,11,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,11,1)),1,1)) END * POWER (16, 3) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,11,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,11,1)),2,1)) END * POWER (16, 2) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,12,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,12,1)),1,1)) END * POWER (16, 1) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,12,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,12,1)),2,1)) END * POWER (16, 0)) AS SORTPOOL_EXP FROM PLAN_TABLE WHERE QUERYNO=2647;
- Use the DISPLAY BUFFERPOOL command to gather information about the buffer pools that are defined in the production environment.
- Gather information about the values of the following
optimization subsystem parameters in the test environment:
- NPGTHRSH
- PARAMDEG
- STARJOIN
You can use either the DSNWZP stored procedure or the ADMIN_INFO_SYSPARM stored procedure to obtain the current settings of most documented Db2 subsystem parameters. The DSNTEJ6Z sample job contains a sample call to the ADMIN_INFO_SYSPARM stored procedure.
- Capture EXPLAIN information about the production system
to make the needed values available.
- Set subsystem parameters on the test subsystem to simulate
the processor speed and number of processors in the production environment.
- Modify a copy of the DSNTIJUZ job that you use to create
the subsystem parameter module for the test subsystem, and add the
following keyword parameters:
- SIMULATED_CPU_SPEED
- Specify the value that was captured from the production subsystem as CPUSPEED_EXP.
- SIMULATED_CPU_COUNT
- Specify the value that was captured from the production subsystem as NUMCP_EXP. This subsystem parameter value is applicable only for queries that use that use parallelism.
- NPGTHRSH
- Specify the same value that is used in the production environment.
- PARAMDEG
- Specify the same value that is used in the production environment.
- STARJOIN
- Specify the same value that is used in the production environment.
- Modify a copy of the DSNTIJUZ job that you use to create
the subsystem parameter module for the test subsystem, and add the
following keyword parameters:
- Create a profile on the test system to specify RID pool,
sort pool, and buffer pool settings. The single profile that you create has a global scope for the single subsystem that it applies to.
- Create the profile. You can specify any unique value for PROFILEID.
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID) VALUES (4713);
- Insert values into the DSN_PROFILE_ATTRIBUTES table
to model production buffer pools in the test environments.
For example, the following statements mean that Db2 uses a value of 25000 for BP0 and for a value of 2500 for BP8K. The values override the actual buffer pool sizes only when Db2 when determines access paths:
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2) VALUES (4713, 'BP0',NULL, 25000); INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2) VALUES (4713, 'BP8K0',NULL, 2500);
The actual buffer pool sizes are not changed on the test subsystem. The buffer pool assignment for each table in your test system must match the buffer pool assignment for the corresponding table in the production environment. However, it is unnecessary that the buffer pools of the same name be assigned to corresponding table in each environment. However, each table must be assigned to a buffer pool of the same size in each environment. - Insert values into the DSN_PROFILE_ATTRIBUTES table
on the test to model the size of the sort pool on the production subsystem:
Specify the value that was captured from the production subsystem as SORTPOOL_EXP.
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2) VALUES (4713, 'SORT_POOL_SIZE',NULL, 307200);
- Insert values into the DSN_PROFILE_ATTRIBUTES table
on the test subsystem to model the maximum number of RID blocks on
the production subsystem: Specify the value that was captured from the production subsystem as RIDPOOL_EXP.
INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2) VALUES (4713, 'MAX_RIDBLOCKS',NULL, 300);
- Create the profile.
- Issue a START PROFILE command to start the profile on the
test subsystem. You must have SYSOPR, SYSCTRL, or SYSADM authority to issue the command.After you start the profile, you might execute the following statements to verify that the profile is started:
SELECT PROFILE_ENABLED FROM SYSIBM.DSN_PROFILE_TABLE WHERE PROFILEID=4713; SELECT SUBSTR(KEYWORDS,1,14) KEYWORDS,ATTRIBUTE2, SUBSTR(STATUS,1,51) STATUS FROM SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY PAH WHERE PAH.ATTRIBUTE_TIMESTAMP = (SELECT MAX(ATTRIBUTE_TIMESTAMP) FROM SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY PAH2 WHERE PAH2.PROFILEID = PAH.PROFILEID) AND PROFILEID = 4713;
If the profile is started, the preceding statements return the following result:Table 1. Sample result from the DSN_PROFILE_ATTRIBUTES_HISTORY table KEYWORDS ATTRIBUTE2 STATUS MAX_RIDBLOCKS 300 ACCEPTED SORT_POOL_SIZE 307200 ACCEPTED BP8K0 2500 ACCEPTED BP0 25000 ACCEPTED - Validate that the correct parameters settings are used
in the test system.
- Capture EXPLAIN information in the test system to gather
values that you can compare to the production environment. You might use either of the following methods to capture the information.
- Issue an EXPLAIN ALL statement, such as the statement shown in
the following example:
SET CURRENT DEGREE='ANY'; EXPLAIN ALL SET QUERYNO = 1 FOR SELECT COUNT(*) FROM SYSIBM.DSN_PROFILE_TABLE WHERE PROFILEID > 0;
- Issue a BIND or REBIND command and specify EXPLAIN(YES).
- Issue an EXPLAIN ALL statement, such as the statement shown in
the following example:
- Query the EXPLAIN data on the test subsystem to compare
values to those values that you captured from the production system.
For example, you might issue the following series of statements:
SELECT CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,69,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,69,1)),1,1)) END * POWER (16, 7) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,69,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,69,1)),2,1)) END * POWER (16, 6) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,70,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,70,1)),1,1)) END * POWER (16, 5) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,70,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,70,1)),2,1)) END * POWER (16, 4) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,71,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,71,1)),1,1)) END * POWER (16, 3) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,71,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,71,1)),2,1)) END * POWER (16, 2) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,72,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,72,1)),1,1)) END * POWER (16, 1) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,72,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,72,1)),2,1)) END * POWER (16, 0) AS CPUSPEED_EXP, FROM PLAN_TABLE WHERE QUERYNO = 1; SELECT CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,25,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,25,1)),1,1)) END * POWER (16, 3) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,25,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,25,1)),2,1)) END * POWER (16, 2) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,26,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,26,1)),1,1)) END * POWER (16, 1) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,26,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,26,1)),2,1)) END * POWER (16, 0) AS NUMCP_EXP, FROM PLAN_TABLE WHERE QUERYNO = 1; SELECT CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,13,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,13,1)),1,1)) END * POWER (16, 7) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,13,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,13,1)),2,1)) END * POWER (16, 6) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,14,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,14,1)),1,1)) END * POWER (16, 5) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,14,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,14,1)),2,1)) END * POWER (16, 4) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,15,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,15,1)),1,1)) END * POWER (16, 3) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,15,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,15,1)),2,1)) END * POWER (16, 2) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,16,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,16,1)),1,1)) END * POWER (16, 1) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,16,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,16,1)),2,1)) END * POWER (16, 0) AS RIDPOOL_EXP, ATTRIBUTE2 AS RIDPOOL_PROFILE FROM PLAN_TABLE, SYSIBM.DSN_PROFILE_ATTRIBUTES WHERE KEYWORDS= 'MAX_RIDBLOCKS' AND QUERYNO = 1; SELECT CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,9,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,9,1)),1,1)) END * POWER (16, 7) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,9,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,9,1)),2,1)) END * POWER (16, 6) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,10,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,10,1)),1,1)) END * POWER (16, 5) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,10,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,10,1)),2,1)) END * POWER (16, 4) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,11,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,11,1)),1,1)) END * POWER (16, 3) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,11,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,11,1)),2,1)) END * POWER (16, 2) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,12,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,12,1)),1,1)) END * POWER (16, 1) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,12,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,12,1)),2,1)) END * POWER (16, 0) AS SORTPOOL_EXP, ATTRIBUTE2 AS SORTPOOL_PROFILE FROM PLAN_TABLE, SYSIBM.DSN_PROFILE_ATTRIBUTES WHERE KEYWORDS= 'SORT_POOL_SIZE' AND QUERYNO = 1; SELECT QUERYNO,TBL.CREATOR,TBL.NAME,TS.NAME, BPOOL,ATTRIBUTE2 AS BP_PROFILE, CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,1,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,1,1)),1,1)) END * POWER (16, 7) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,1,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,1,1)),2,1)) END * POWER (16, 6) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,2,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,2,1)),1,1)) END * POWER (16, 5) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,2,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,2,1)),2,1)) END * POWER (16, 4) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,3,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,3,1)),1,1)) END * POWER (16, 3) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,3,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,3,1)),2,1)) END * POWER (16, 2) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,4,1)),1,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,4,1)),1,1)) END * POWER (16, 1) + CASE SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,4,1)),2,1) WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15 ELSE INTEGER(SUBSTR(HEX(SUBSTR(IBM_SERVICE_DATA,4,1)),2,1)) END * POWER (16, 0) AS BP_EXP FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLES TBL, SYSIBM.DSN_PROFILE_ATTRIBUTES,PLAN_TABLE WHERE KEYWORDS = BPOOL AND TS.NAME = TSNAME AND TBL.CREATOR = PLAN_TABLE.CREATOR AND TBL.NAME = PLAN_TABLE.TNAME AND QUERYNO = 1; SELECT QUERYNO,REASON FROM DSN_STATEMNT_TABLE WHERE QUERYNO = 1;
The following tables show example results from the preceding statements:CPUSPEED_EXP 380 NUMCP_EXP 1 RIDPOOL_EXP RIDPOOL_PROFILE 300 300 SORTPOOL_EXP SORTPOOL_PROFILE 307200 307200 BPOOL BP_PROFILE BP_EXP BP0 25000 25000
- Capture EXPLAIN information in the test system to gather
values that you can compare to the production environment.