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.

However, you can specify that Db2 models the configuration and parameters of your production environment in your test subsystem. You can specify the following parameters and configuration details for Db2 to use for access path selection in your test subsystem:

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.

Set subsystem parameters on the test subsystem to simulate the processor speed and number of processors in the production environment.
1. 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.
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.
1. Create the profile.
  You can specify any unique value for PROFILEID.
```
INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID)
 VALUES (4713);
```
2. 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.
3. 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);
```
4. 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);
```

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).

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