Specifying Shared and Private Memory Pools in the Memory_Pool_Preference QAQQINI Option

Abstract:

            This article introduces an enhancement to the Memory_Pool_Preference QAQQINI option that enables the user to specify a shared or private memory pool.  The motivation for this enhancement is explained and several examples of its use are provided.

Introduction

            The Memory_Pool_Preference option in the QAQQINI file provides the user with some influence over the memory pool that DB2 will use to execute database operations.  Up until now the user has been able to specify a system pool ID in addition to a few special values in the option.  This functionality worked well unless a user preferred to have the query data paged into a shared or private memory pool.  In that case, the user had to know the system pool ID associated with that named memory pool and if that pool ID ever changed, the user would have to update QAQQINI with the new system pool ID.  The next section gives an example of how this can be difficult to manage.

A Motivating Example: Changing System Pool IDs

            As an example of how difficult it can be to keep track of the system pool IDs associated with particular shared and private pools, consider the following four subsystem requirements:

1. A subsystem with with one private pool.
2. A subsystem with three private pools.
3. A subsystem with a shared memory pool.
4. A subsystem with one shared and one private pool.

            Each private pool should have a storage size of 2000 KB and should allow no more than 1 thread active at the same time in the pool.  To create these subsystems, we could enter the following commands at a command interface[1]:

            CRTSBSD SBSD(MYLIB/MYSBS1) POOLS((1 2000 1))

            CRTSBSD SBSD(MYLIB/MYSBS2) POOLS((1 2000 1) (2 2000 1) (3 2000 1))

            CRTSBSD SBSD(MYLIB/MYSBS3) POOLS((1 *SHRPOOL1))

            CRTSBSD SBSD(MYLIB/MYSBS4) POOLS((1 *SHRPOOL1) (2 2000 1))

            Figure 1 shows how we could have used IBM Navigator for i to create the first subsystem.  From the side panel we would select System->Run Command and enter 'CRTSBSD' in the text box.  Next we would click on 'Prompt' below the text box which would open up a page where we could enter the parameters.

   Figure 1: CRTSBSD using IBM Navigator for i.

            Next we can start the subsystems in ascending order. Figure 2 below shows how this can be done for the first subsystem.  The other subsystems could be started similarly.

            Figure 2: Starting a subsystem.

            After the subsystems have been started, we can navigate to Work Management->Active Memory Pools in Navigator and see the information for the active memory pools[2] as in Figure 3 below.

            Figure 3: Active Memory Pools.

            What we want to notice in the preceding figure is the System Pool Identifier in the first column and the corresponding Pool ID in the second column for the subsystems we have just created.

            Suppose the subsystems were ended and restarted in a different order, say 3-1-2-4.  Figure 4 shows how to stop a subsystem in IBM Navigator for i.   In the figure, we have selected Mysbs1 and clicked on Actions->Stop in the toolbar.

     Figure 4: Stopping a subsystem.

            After we have restarted the subsystems in the new order, we could navigate back to Active Memory Pools to view the pools again as shown in Figure 5 below.

            Figure 5: Active Memory Pools after restarting subsystems in a different order.

                Notice how the system pool IDs associated with the shared and private pools have changed.  If a system pool ID were specified in the QAQQINI Memory_Pool_Preference option, the database would be directed to page the query data into a different pool from what the user expected.  For example, if System Pool 5 had been specified in the memory pool preference, DB2 would have tried to page data into pool number 1 in subsystem MYLIB/MYSBS1 the first time, but after the subsystems were ended and restarted in a different order, the memory pool preference would have directed DB2 to page data into Shared 1.  The only way to ensure this didn't happen would be to update the option in QAQQINI before running the query.  This would have to be done every time the pool IDs changed.  This could be very frustrating for the user who has expended significant effort tuning memory pools for optimized database performance[3].

The Solution: Specifying Shared and Private Pools in QAQQINI

            With this new enhancement, the user can simply specify the shared pool name or the private pool name along with its subsystem and library and DB2 will keep track of these changes and make certain that the preferred memory pool for the database operation is really what the user intended.

            In order to specify a shared memory pool in the INI option, the user must preface the pool name with the keyword *NAME and include one space between the name and the keyword as in the following example:

                        *NAME <shared pool name>

            In order to specify a private pool name, the user must specify the name in the following format:

                        *PRIVATE <subsystem library>/<subsystem> <private pool name> 

The *PRIVATE keyword must be followed by one space and there should be no spaces between the subsystem library and the forward slash, nor between the forward slash and the subsystem.  There must be one space between the subsystem and the private pool name.

            The following SQL examples illustrate the syntax for both shared and private pools:

update MYLIB/QAQQINI                

set QQVAL='*NAME *SHRPOOL1'           

where QQPARM = 'MEMORY_POOL_PREFERENCE'

            update MYLIB/QAQQINI                   

set QQVAL='*NAME *INTERACT'           

where QQPARM = 'MEMORY_POOL_PREFERENCE'

update MYLIB/QAQQINI                    

set QQVAL='*PRIVATE MYLIB/MYSBS1 1'    

where QQPARM = 'MEMORY_POOL_PREFERENCE'

            If we run the first update followed by the following query:

SELECT * FROM MYLIB.MYTABLE

in Visual Explain we can see that the Preferred Memory Pool is indeed System Pool ID 5, which corresponds to memory pool Shared 1 in Figure 5.  Figure 6 below shows the Visual Explain output.

                Figure 6: Visual Explain of the query showing Preferred Memory Pool.

            A few additional facts to keep in mind when using this enhacement are that DB2 does not, in the case of private pools, make any attempt to validate whether the subsystem or subsystem library exist, nor does it attempt to validate whether the subsystem is active.  There are many cases where the user wants to specify the pool preference but the library or subsystem has not yet been created.  Thus it is up to the user to ensure that the subsystem and library are spelled correctly, for example.  If a query is run and the private pool was not specified correctly or the subsystem is not active, DB2 will use the *BASE pool as the preferred memory pool.