Plan Management in DB2

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 Relational Data Adapters, introduces application plans, packages, and bind concepts.

In this section: Basic Plan Management Extended Plan Management

Basic Plan Management

With basic plan management, you create a separate DB2 application plan for every FOCEXEC or MODIFY procedure. You do not have to issue the SET PLAN command, and the data 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, that FEX1 and FEX2 are static FOCEXEC procedures, and that a corresponding DB2 application plan exists for each of them:

Step	MODIFY	FOCEXEC
1.	RUN MOD1	SQL DB2 RUN FEX1 END
2.	RUN MOD2	SQL DB2 RUN FEX2 END
3.	RUN MOD2	SQL DB2 RUN FEX3 END
4.	EX RPT1	EX RPT1

The data 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 or FEX1. If no prior connection existed, it first issues a CAF CONNECT. It establishes the thread to MOD1 or FEX1 with a CAF OPEN.

2. It opens a thread to plan MOD2 or FEX2. First it deallocates the thread to MOD1 or FEX1 with a CAF CLOSE, then issues a CAF OPEN to establish the thread to MOD2 or FEX2.

3. No action. The data adapter recalls that it already has a thread to MOD2 or FEX2 and takes no action.

4. It opens a thread to the FOCUS dynamic plan. First it deallocates the thread to MOD2 or FEX2 with a CAF CLOSE. Then it issues a CAF OPEN to establish the thread to the installation default application plan for the dynamic SQL data 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.

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, or FEX1 and FEX2, have been bound into a plan called BIGPLAN:

Step	MODIFY	FOCEXEC
1.	SQL DB2 SET PLAN BIGPLAN	SQL DB2 SET PLAN BIGPLAN
2.	RUN MOD1	SQL DB2 RUN FEX1 END
3.	RUN MOD2	SQL DB2 RUN FEX2 END
4.	RUN MOD2	SQL DB2 RUN FEX3 END
5.	SQL DB2 SET PLAN	SQL DB2 SET PLAN
6.	EX RPT1	EX RPT1

The data adapter takes the following actions:

1. It sets the application plan. The SET PLAN command tells the data 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 data adapter recalls that the user-set plan is BIGPLAN and that the thread has already been established.

4. No action. The data adapter recalls that the user-set plan is BIGPLAN and that the thread has already been established.

5. It returns control to the dynamic data adapter. Setting the plan to blank instructs the data 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 data 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.