February 21, 2012

Managing a Virtual Environment

IBM has a facility that establishes performance-monitoring parameters through a set of profile tables. These parameters are activated and deactivated through the START PROFILE command. I covered the profile tables and commands in a previous blog entry.

More recently, I learned about an extension to these tables that allow you to setup a virtual environment to model the access path that DB2 selects, based on a target system on which the SQL will run. I discussed this new facility last week.

I want to follow up on last week's post because I've since done some testing. After inserting the rows into the profile tables based on APAR PM26575 and then starting the profile, I did an EXPLAIN so I could examine the access path. However, when I checked the REASON column found in the user.DSN_STATEMENT_TABLE table to see if the optimizer used the profile setup to model this environment, I found it was blank. The virtual environment defined in the profile tables wasn't being used. I didn't know why this was the case, so I decided to check on the profile status, which is set when the START PROFILE command is issued. In the STATUS column found in SYSIBM.DSN_PROFILE_HISTORY, I discovered that the status was set to "REJECTED - INVALID SCOPE SPECIFIED." Huh? I went back to the APAR to see if I missed something. Then I noticed a separate APAR listed for DB2 10.

Upon examining the DB2 10 APAR, I found two DSNZPARMs that weren't commented on in the DB2 9 version APAR. They are: DSN6SPRM.SIMULATED_CPU_SPEED and DSN6SPRM.SIMULATED_CPU_COUNT. I figured these parameters had to be the same in both DB2 9 and DB2 10. I changed the parameter settings from the default of OFF to the values from the target, reassembled the DSNZPARMS then used the SET SYSPARM command to load them into memory. Finally I issued the START PROFILE command and found the status was ACCEPTED.

Now I have a virtual environment set up with the CPU information along with the memory information (RID POOL size, SORT POOL size, buffer pool size) and the catalog statistics about the objects taken from the target system. I issued the EXPLAIN statement again and found the reason contained the text "PROFILEID 6475." I was happy to see this because it confirmed that the optimizer is using this profile to define the access path.

Remember that you can define multiple profiles and change the bufferpool- and memory-related values in the profile tables. However, CPU Speed and CPU Count must be defined in the zparms. If you want to play what-if games (e.g., go from 5 to 10 CPUs), you'll need to modify the zparms, assemble and then reload. Monitoring the memory size of buffer or rid pools is more dynamic because you can change the information in the profile table and restart the profile to take effect.

If you're using this service I'd be interested in knowing more. Please post a comment so everyone can benefit from your experiences.