Plan Management in DB2

In this section:

Using extended plan management, you can create application plans that contain multiple procedures. This section discusses both the basic and extended plan management options. Introduction to Adapters for Relational Data Sources introduces application plans, packages, and bind concepts.
Top of page
x
Basic Plan Management

With basic plan management, you create a separate DB2 application plan for every MODIFY procedure. You do not have to issue the SET PLAN command, and the adapter automatically establishes and terminates the necessary DB2 threads for each procedure invoked.

The following examples assume that MOD1 and MOD2 are static MODIFY procedures and that a corresponding DB2 application plan exists for each of them:

Step

MODIFY

1. 

RUN MOD1

2. 

RUN MOD2

3. 

RUN MOD2

4. 

EX RPT1

The adapter takes the following actions:

  1. First, it closes the thread to a prior application plan, if one exists. It deallocates that plan with a Call Attachment Facility (CAF) CLOSE.

    Then it opens a thread to plan MOD1. If no prior connection existed, it first issues a CAF CONNECT. It establishes the thread to MOD1 with a CAF OPEN.

  2. It opens a thread to plan MOD2. First it deallocates the thread to MOD1 with a CAF CLOSE, then issues a CAF OPEN to establish the thread to MOD2.
  3. No action. The adapter recalls that it already has a thread to MOD2 and takes no action.
  4. It opens a thread to the FOCUS dynamic plan. First it deallocates the thread to MOD2 with a CAF CLOSE. Then it issues a CAF OPEN to establish the thread to the installation default application plan for the dynamic SQL adapter.

Note: The settings for AUTOCLOSE and AUTODISCONNECT affect thread retention across invocations of the same procedure, as in items 2 and 3. Some settings sever a thread and/or connection to DB2, either at the conclusion of any procedure, or within the procedure itself. More information is available in Controlling Connection Scope.
Top of page
x
Extended Plan Management

With extended plan management, you can create a DB2 application plan that contains more than one procedure. You must issue the SET PLAN command to establish an umbrella plan that includes each FOCEXEC to be invoked.

The primary purpose of extended plan management is to limit the amount of overhead involved in plan switching. Therefore, this method of binding is not consistent with the AUTOCLOSE or AUTODISCONNECT options, both of which terminate threads and/or connections to DB2.

The following example assumes that MOD1 and MOD2 have been bound into a plan called BIGPLAN:

Step

MODIFY

1. 

SQL DB2 SET PLAN BIGPLAN

2. 

RUN MOD1

3. 

RUN MOD2

4. 

RUN MOD2

5. 

SQL DB2 SET PLAN

6. 

EX RPT1

The adapter takes the following actions:

  1. It sets the application plan. The SET PLAN command tells the adapter that all required SQL statements for subsequent FOCEXECs are contained in BIGPLAN.
  2. It opens a thread to plan BIGPLAN. It establishes the thread to BIGPLAN with a CAF OPEN. If there is no prior connection to DB2, it executes a CONNECT before the OPEN.
  3. No action. The adapter recalls that the user-set plan is BIGPLAN and that the thread has already been established.
  4. No action. The adapter recalls that the user-set plan is BIGPLAN and that the thread has already been established.
  5. It returns control to the dynamic adapter. Setting the plan to blank instructs the adapter to return control to the installation default application plan. If you wish to return control to a plan other than the default plan, specify that plan name.

    Another option would be to include the dynamic adapter in BIGPLAN by including the DBRM for RSQL as part of the member list when binding BIGPLAN. This procedure would eliminate the need to reset the plan for dynamic access to DB2. If using this option, skip steps 5 and 6.

  6. It opens a thread to the FOCUS dynamic plan. It executes a CAF OPEN to establish the thread to the installation default application plan.
Information Builders