The MODIFY procedure must be compilable. Consult Static SQL Requirements and your FOCUS documentation on maintaining databases for more information on compiled MODIFY requirements.

The following chart lists the DDNAMEs you must allocate before compiling a FOCEXEC to use static SQL. You can add these allocations to your FOCUS CLIST, batch JCL, or PROFILE FOCEXEC. Note that these allocations are additions to the normal allocations for running FOCUS with DB2:

ASMSQL

DSORG(PO)
RECFM(FB)
LRECL(80)

DBRMLIB

DSORG(PO)
RECFM(FB)
LRECL(80)

DB2LOAD

STUBLIB

DSORG(PO)
RECFM(U)

SQLERR1

DSORG(PS)
RECFM(FB)
LRECL(130)

SQLERR2

DSORG(PS)
RECFM(FM)
LRECL(121)

SQLERR3

DSORG(PS)
RECFM(FA)
LRECL(81)

FOCCOMP

DSORG(PO)
RECFM(VB)
LRECL(32756)
BLKSIZE(32760)

The syntax for the SET STATIC command is

{ENGINE|SQL} [DB2] SET STATIC process 

where:

process

Indicates the command that invokes static processing. Valid values are:

OFF

Is the default setting. Reverses ON or NOBIND settings. Does not invoke static processing for any subsequent COMPILE commands issued. For information about the FOCUS COMPILE command, see your FOCUS documentation on maintaining databases.

ON

invokes static processing with automatic bind. This option does not support extended plan management (see Plan Management in DB2) or modification of any of the default bind parameters. If you require more flexibility, use the NOBIND option.

NOBIND

invokes static processing without bind. Use this option if you need extended plan management, to change the default bind parameters, or to bind your programs at another time (for example, during an off peak period).

Note:

• If FOCUS performs the bind, the isolation level is always RR for MODIFY requests. To change the isolation level, you must issue the BIND outside of FOCUS, as described in Optionally BIND the Plan for the FOCEXEC on page 13-9.
• Before requesting an automatic bind, make sure the DB2 subsystem is operational. In addition, the bind operation itself requires DB2 privileges that you may not possess. Your DB2 database administrator can tell you if you are authorized to use BIND.
• Omit the DB2 qualifier if you previously issued the SET SQLENGINE command for DB2.
• Batch considerations:

  You can create or run static procedures in batch. The DSN command processor cannot be invoked in batch. Therefore, you must compile your programs using the NOBIND option and bind the programs separately.

If you request the automatic bind option and the DB2 subsystem in which you want the bind to occur is different from your installation default, you must issue the SET SSID command (see Adapter Commands). You can obtain the current setting for the DB2 subsystem by issuing the SQL DB2 ? command.

Note: The DB2 subsystem referenced by the SET SSID command should use the DB2 load library allocated to DDNAME DB2LOAD.

To compile the MODIFY procedure, use the FOCUS COMPILE facility.

COMPILE focexec 

This step is only necessary if you issued the SET STATIC command with the NOBIND option.

You may choose to bind one application plan for each FOCEXEC (basic plan management) or to combine several FOCEXECs into one application plan (extended plan management). Also, use this option to supply bind parameters (such as isolation level) other than the default. Plan Management in DB2 discusses plan management, and Introduction to Adapters for Relational Data Sources introduces bind concepts.

Issue the BIND command outside the FOCUS environment, using one of the methods supplied by IBM. Your BIND may include one or more FOCEXEC DBRMs.

For example:

BIND PLAN (BIGPLAN) MEM(FEX1 FEX2 FEX3 FEX4) ACTION(ADD) ISOLATION(CS)

For a full explanation of bind methods and parameters, consult the IBM DB2 Command and Utility Reference.

Issue the SQL GRANT EXECUTE command to authorize users to execute the application plan created with the previous steps. This example shows how to issue the command from within a FOCUS session:

SQL DB2 GRANT EXECUTE ON PLAN BIGPLAN TO USER1, USER2

For more information on GRANT, consult the IBM DB2 SQL Reference.

In addition to the allocations required for FOCUS and the adapter outlined in Invoking Relational Adapters, you must include allocations for the FOCCOMP and STUBLIB libraries allocated to DDNAMEs FOCCOMP and STUBLIB.

If you use extended plan management (see Extended Plan Management), you must issue the adapter SET PLAN command before running any of the procedures included in the DB2 application plan (see Maintaining Tables With FOCUS):

SQL DB2 SET PLAN BIGPLAN

This setting overrides the plan for the FOCUS dynamic adapter. Extended Plan Management explains options for resetting the plan at the conclusion of your procedures.

You can use the SQL DB2 ? command to view all current adapter plan settings, for example:

   SQL DB2 SET PLAN BIGPLAN
   SQL DB2 ?
   >
       .
       .
       .
   (FOC1448) ACTIVE PLAN FOR CALL ATTACH IS     - :
   (FOC1459) USER SET PLAN FOR CALL ATTACH IS   - : BIGPLAN
   (FOC1460) INSTALLATION DEFAULT PLAN IS       - : P7029910
       .
       .
       .

The most recently issued request to DB2 sets the Active Plan. If the thread is closed or marked inactive, no value displays for Active Plan. Your SET PLAN command determines the User Set Plan.

Note: To execute a static MODIFY procedure, you do not need the Access File library (allocated to DDNAME FOCSQL) at run time if you will not be accessing DB2 dynamically during the FOCUS session. The Master File library (allocated to DDNAME MASTER) is required for all static and dynamic access.

When you run a compiled FOCEXEC procedure, the adapter searches the STUBLIB load library for a load module of the same name and loads it, if it exists.

When you run a compiled MODIFY procedure, FOCUS loads the FOCCOMP library member into virtual memory. If the flag setting in this member indicates that this is a static procedure, the adapter searches the STUBLIB load library for a load module of the same name. The adapter compares timestamps in the FOCCOMP and STUBLIB members for consistency.

Next, FOCUS connects to DB2 and opens a thread to the application plan of the same name as the static procedure (you can use the SET PLAN command to override this). DB2 compares the owner, program name, and timestamps in the STUBLIB member and the DB2 application plan for consistency.

This process verifies that the compiled MODIFY (FOCCOMP member), the program load module (STUBLIB member), and the DB2 application plan are valid and that no substitutions have been made.

The following annotated example illustrates the creation and automatic bind of a static MODIFY procedure named MOD1. The numbers 1 through 7 refer to the explanatory notes that follow the example.

Also included for your information are the options FOCUS uses for each step (for example, Assembler options). Please refer to the appropriate IBM manual for an explanation of these parameters and their possible settings.

    SQL DB2 SET SSID DB2P
    SQL DB2 SET STATIC ON 
1.  COMPILE MOD1
    EMPLOYEE    ON 02/27/91 at 14.26.57 
2.  (FOC1472) STATIC SQL PROGRAM CREATED SUCCESSFULLY: 
3.  (FOC1473) STATIC SQL PROGRAM PREPROCESSED. RETURN CODE IS: 4 
4.  (FOC1474) STATIC SQL PROGRAM ASSEMBLED   . RETURN CODE IS: 0 
5.  (FOC1476) STATIC SQL PROGRAM LINKED      . RETURN CODE IS: 0
    DSN SYSTEM(DB2P) 
6.  BIND PLAN(MOD1) MEM(MOD1) ACTION(ADD) ISOLATION(RR)
    DSNT252I - BIND OPTIONS FOR PLAN MOD1
               ACTION    ADD
               OWNER     PMSEF
               VALIDATE  RUN
               ISOLATION RR
               ACQUIRE   USE
               RELEASE   COMMIT
               EXPLAIN   NO
    DSNT253I - BIND OPTIONS FOR PLAN MOD1
               NODEFER         PREPARE
    DSNT200I -  BIND FOR PLAN MOD1     SUCCESSFUL
    END

7.  COMPILED...

The steps in the process are:

1. Issue the COMPILE command.

   The user invokes the FOCUS COMPILE facility for MODIFY procedures.

2. Create the Assembler program.

   In this initial step, FOCUS reads the MODIFY procedure and creates an Assembler program with embedded SQL statements representing all SQL statements the MODIFY will execute. This program is the input file for the next step.

3. Precompile the Assembler program.

   FOCUS invokes the IBM precompiler for the Assembler program created in Step 2. The precompiler comments out the embedded SQL statements and replaces them with Assembler calls to DB2. It places the SQL statements in a Database Request Module (DBRM) created as a member in the data set allocated to DBRMLIB. This member is part of the input to the bind process. Introduction to Adapters for Relational Data Sources, introduces bind concepts. FOCUS places the modified source program into a temporary data set. It writes the IBM precompiler listing to the data set allocated to DDNAME SQLERR1 or to the terminal.

   This step uses the Assembler precompiler included in the DB2 load library allocated to DDNAME STEPLIB. The adapter SET SSID command has no influence on the precompiler used. If you have more than one DB2 subsystem, be sure that the DB2 load library used in the precompilation step is the same one used by the DB2 subsystem in which the program will run. The DB2 subsystem does not have to be active during this step.

   The precompiler options for this step are HOST(ASM), DATE(ISO).

   Note: The precompilation process generates the statement WARNINGS HAVE BEEN SUPPRESSED DUE TO LACK OF TABLE DECLARATIONS. This message is normal and may be ignored. The precompilation should complete with a return code of four (4).

4. Assemble the program.

   FOCUS assembles the modified source program using IBM's Assembler H and places the output into a temporary data set. It writes the resulting listing to the data set allocated to DDNAME SQLERR2 or to the terminal.

   The assembler options are DECK, NOOBJECT, NOALIGN and TERM. This step should complete with a return code of zero (0).

5. Link-edit the program.

   FOCUS links the assembled source program using IBM's linkage editor, placing the run-time module into the data set allocated to DDNAME STUBLIB. It writes the linkage editor output to the data set allocated to DDNAME SQLERR3 or to the terminal.

   The linkage editor options are TERM, RENT, AMODE(31), RMODE(ANY), and LIST. This step should complete with a return code of zero (0).

6. Optional automatic BIND.

   Since the SET STATIC ON command requests an automatic BIND, FOCUS invokes the DB2 DSN command processor and submits the following bind request:

   DSN SYSTEM(DB2P)
   BIND PLAN (MOD1) MEM(MOD1) ACTION(REPLACE) ISOLATION(RR)

   Note:

   • The SSID of the DB2 subsystem in which this plan will be bound is DB2P. The target subsystem for the bind was established by the SET SSID command.
   • The plan name will be the same as that of the FOCEXEC (MOD1). The MEM parameter means that application plan MOD1 will include member MOD1 from the data set allocated to DDNAME DBRMLIB. Member MOD1 was created during the precompilation step. The plan owner is the DB2 primary authorization ID in effect at the time of the bind.
7. Compile the FOCEXEC.

   FOCUS compiles the FOCEXEC and creates member MOD1 in the data set allocated to DDNAME FOCCOMP. It sets a flag in the FOCCOMP member to indicate that this is a static procedure.

   FOCEXECs compiled with the Static SQL facility do not support the use of the LOAD command. To execute them you must use the RUN command.