Dynamic plan selection

It is beneficial to use dynamic plan selection and packages together. You can convert individual programs in an application that contains many programs and plans, one at a time, to use a combination of plans and packages. This process reduces the number of plans per application; having fewer plans reduces the effort that is needed to maintain the dynamic plan exit routine.

CICS®You can use packages and dynamic plan selection together, but when you dynamically switch plans, the following conditions must exist:

  • All special registers, including CURRENT PACKAGESET, must contain their initial values.
  • The value in the CURRENT DEGREE special register cannot have changed during the current transaction.
Assume that you develop the following programs and DBRMs:
Table 1. Example programs and DBRMs
Program Name DBRM Name
MAIN MAIN
PROGA PLANA
PROGB PKGB
PROGC PLANC

You could create packages using the following bind statement: 

BIND PACKAGE(PKGB) MEMBER(PKGB)
The following scenario illustrates thread association for a task that runs program MAIN. Suppose that you execute the following SQL statements in the indicated order. For each SQL statement, the resulting event is described.
  1. EXEC CICS START TRANSID(MAIN)

    TRANSID(MAIN) executes program MAIN.

  2. EXEC SQL SELECT…

    Program MAIN issues an SQL SELECT statement. The default dynamic plan exit routine selects plan MAIN.

  3. EXEC CICS LINK PROGRAM(PROGA)

    Program PROGA is invoked.

  4. EXEC SQL SELECT…

    Db2 does not call the default dynamic plan exit routine, because the program does not issue a sync point. The plan is MAIN.

  5. EXEC CICS LINK PROGRAM(PROGB)

    Program PROGB is invoked.

  6. EXEC SQL SELECT…

    Db2 does not call the default dynamic plan exit routine, because the program does not issue a sync point. The plan is MAIN and the program uses package PKGB.

  7. EXEC CICS SYNCPOINT

    Db2 calls the dynamic plan exit routine when the next SQL statement executes.

  8. EXEC CICS LINK PROGRAM(PROGC)

    Program PROGC is invoked.

  9. EXEC SQL SELECT…

    Db2 calls the default dynamic plan exit routine and selects PLANC.

  10. EXEC SQL SET CURRENT SQLID = 'ABC'

    The CURRENT SQLID special register is assigned the value 'ABC.'

  11. EXEC CICS SYNCPOINT

    Db2 does not call the dynamic plan exit routine when the next SQL statement executes because the previous statement modifies the special register CURRENT SQLID.

  12. EXEC CICS RETURN

    Control returns to program PROGB.

  13. EXEC SQL SELECT…

CICS With packages, you probably do not need dynamic plan selection and its accompanying exit routine. A package that is listed within a plan is not accessed until it is executed. However, you can use dynamic plan selection and packages together, which can reduce the number of plans in an application and the effort to maintain the dynamic plan exit routine.