$PLAN Function

$PLAN returns the application plan name most recently set in an application. You can use the SET $PLAN statement to specify a logical identifier for the next plan to use. This is a logical identifier, not necessarily the actual plan name. This level of indirection lets you avoid hard coding actual plan names in your programs (which requires compilation for any change to plan names).

This function has the following format:

$PLAN

As the source of a SET statement or in a condition, $PLAN returns the plan name most recently set in a CA Ideal application.

As the target of a SET statement, you can use $PLAN to select a plan name that can instruct DB2 to change to a new plan. You can set $PLAN to any one‑ to eight‑character plan identification or to the name of a field that contains a plan identification.

If $PLAN is set to an alphanumeric literal, you must surround the value with double or single quotes (“ or '). For example:

SET $PLAN = 'PAYPLAN'

Online, CA Ideal applications can switch plan names only if DB2 Version 2 is installed and the RCT entry for the current transaction ID specifies PLNEXIT=YES and PLNPGME=@IADRCTX, the RCT exit CA Ideal supplies.

In batch, CA Ideal applications can switch plans under either release of DB2.

For more information about selecting plans in CA Ideal and setting up the RCT, see the Administration Guide.

You can set a session plan name using a SET RUN PLAN command. The SET $PLAN statement overrides this setting for the current application. If a plan name was not set in the application, the function returns the name set for the session by SET RUN PLAN.
If a plan name was not set in the application and a name was not set for the session, the function returns IDPLANDV. For example, the following tests true if the name PAYPLAN was selected in the most recent SET $PLAN statement or if a SET $PLAN statement was not executed in a SET RUN PLAN command.
```
	IF $PLAN = 'PAYPLAN' …
```
You can execute the SET $PLAN statement only before the first SQL statement in a logical unit of work. That is, executing SET $PLAN at any point except before the first SQL statement at the beginning of a CICS transaction or before the first SQL statement following a database commit causes a runtime error.
The first SQL statement can be embedded SQL, SQL generated by a FOR construct for a DB2 dataview, or SQL in a non‑ideal subprogram. A commit can be a PDL TRANSMIT, CHECKPOINT or BACKOUT statement, or an SQL COMMIT or ROLLBACK statement.
If an application calls a non‑ideal subprogram that executes SQL statements and if that SQL can be the first in a logical unit of work, then you must specify Y (yes) for the Access DB2 field on the non‑ideal program IDE panel.
You can use the $ERROR‑DB2‑PLAN function in an error procedure to return the actual plan name currently in effect.
Changes made to the plan name by a plan name exit do not affect the value of $PLAN. $PLAN reflects the value set by statements in the current application.

Examples

This code selects PAYPLAN as the CA Ideal application's plan name for the embedded SQL that follows it.

CHECKPOINT
SET $PLAN = 'PAYPLAN'
EXEC SQL …END‑EXEC
…

The following procedure saves the plan name that was selected in a previous procedure in a working data field SAV‑PLAN. It sets GETPLAN as the plan name used with the FOR construct that follows it, commits its database modifications, and resets the plan name before returning.

<<GET>> PROCEDURE
SET SAV‑PLAN = $PLAN
SET $PLAN = GETPLAN
FOR EACH DB2‑DVW  
…ENDFOR
CHECKPOINT
SET $PLAN = SAV‑PLAN
ENDPROC