Creating a Static Procedure for DB2

Creating a static TABLE request requires the data adapter SQL COMPILE facility; its sole function is to create static SQL code for TABLE requests. Creating a static MODIFY is an extension to the usual procedure for compiling a FOCEXEC with the FOCUS COMPILE facility. Invoke the static creation process after you complete the FOCEXEC or MODIFY procedure.

In this section: Write the FOCEXEC Allocate the Required DDNAMEs Optionally Issue the SET STATIC Command Optionally Issue the SET SSID Command Compile the FOCEXEC Optionally BIND the Plan for the FOCEXEC Authorize Users to Run the Plan Run-time Requirements Processing and Security Overview DB2 Static TABLE Example DB2 Static MODIFY Example

You must allocate some additional DDNAMEs, you may need to issue the data adapter SET STATIC command, and you must execute either the FOCUS COMPILE command (for MODIFY) or the data adapter SQL COMPILE command (for TABLE). In response, the data adapter automatically creates an Assembler program with the embedded SQL required by the procedure. It then precompiles, assembles, link-edits, and, optionally, binds the program. It accomplishes all steps, including the automatic bind, from within FOCUS.

You must give some thought to the issue of plan management discussed in Plan Management in DB2 as it will affect your choice of bind option.

This section outlines the steps required to create a static procedure, and provides a description of FOCUS processing for each step. DB2 Static TABLE Example and DB2 Static MODIFY Example provide annotated examples.

Creating a static module for DB2 consists of the following steps:

1. Write the procedure.

2. Allocate the required DDNAMEs.

3. Optionally issue the SET STATIC command.

4. Optionally issue the SET SSID command.

5. Compile the FOCEXEC.

6. Optionally bind the plan for the FOCEXEC.

7. Authorize users to run the plan.

Write the FOCEXEC

Virtually any TABLE or MATCH FILE request is valid. A MODIFY procedure, however, must be compilable. Consult Static SQL Requirements and your FOCUS documentation on maintaining databases for more information on compiled MODIFY requirements.

Allocate the Required DDNAMEs

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:

DDNAME	DCB Parameters	Description
ASMSQL	DSORG(PO) RECFM(FB) LRECL(80)	Target data set for the assembler source code generated by the compilation. The size of each member varies depending on the size of the TABLE requests in the FOCEXEC or the size of the MODIFY procedure and the number of columns in each table the MODIFY procedure references.
DBRMLIB	DSORG(PO) RECFM(FB) LRECL(80)	Target data set for the database request module generated by the compilation.
DB2LOAD	n/a	DB2 load library. The name for this library is site-specific, but usually follows the form DSNxy0.SDSNLOAD (where xy0 is DB2 Version x Release y). The DB2 load library should be the same one used by the DB2 subsystem where the bind will take place. Note: The STEPLIB allocation of the DB2 load library is still necessary.
STUBLIB	DSORG(PO) RECFM(U)	Contains the load module created for the procedure. This data set is also required at run time.
SQLERR1	DSORG(PS) RECFM(FB) LRECL(130)	Contains the output of the IBM precompiler. DCB parameters are set by the precompiler. To route output to the terminal, use DA(*).
SQLERR2	DSORG(PS) RECFM(FM) LRECL(121)	Contains the output of the IBM assemble operation. DCB parameters are set by the assembler. To route output to the terminal, use DA(*).
SQLERR3	DSORG(PS) RECFM(FA) LRECL(81)	Contains the output of the IBM linkage editor. DCB parameters are set by the linkage editor. To route output to the terminal, use DA(*).
FOCCOMP	DSORG(PO) RECFM(VB) LRECL(32756) BLKSIZE(32760)	For MODIFY only. Contains the compiled MODIFY procedure. This data set is also required at run time. Note: The FOCCOMP library member for a MODIFY procedure contains a record of the fact that the procedure uses static SQL. If you have both static and dynamic versions of the compiled procedure, you need two FOCCOMP libraries, one compiled with STATIC ON or NOBIND, and the other compiled with STATIC OFF.

Optionally Issue the SET STATIC Command

The syntax for the SET STATIC command for DB2 is

SQL [DB2] SET STATIC process

where:

process

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

OFF is the default setting.

• For MODIFY, 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.
• For TABLE, invokes static processing with automatic bind, which 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.

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:

• For TABLE requests, you do not have to issue the SET STATIC command unless you require the NOBIND option.
• If FOCUS performs the bind, the isolation level is always CS for TABLE requests and 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.
• 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.

• FOCUS Multi-Session Option (MSO) Considerations:

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

Optionally Issue the SET SSID Command

If you request the automatic bind option and the DB2 subsystem in which you want the bind to occur is different from your site's installation default, you must issue the SET SSID command (see Environmental 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.

Compile the FOCEXEC

To compile a TABLE request, use the data adapter SQL COMPILE facility; you must then use the data adapter SQL RUN facility to run it.

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

Reference: The SQL COMPILE and SQL RUN Commands for TABLE

The data adapter provides facilities for creating and running static TABLE procedures. See Using the SQL COMPILE and SQL RUN Facilities for a complete description of the SQL COMPILE command, the SQL RUN command, SQL COMPILE and SQL RUN processing, host variable length considerations, and host variable placement.

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

Syntax: How to Compile a Static MODIFY Procedure on MVS

Before compiling the request, be sure to allocate all input and output files, such as transaction files or log files, that the MODIFY will use at run time. Any COMBINE structures must be in effect both before compiling the MODIFY and at run time.

Issue the COMPILE command

COMPILE focexec

where:

focexec

Is the name of the MODIFY procedure to compile.

You do not have to recompile or rebind FOCUS applications for different releases of a FOCUS Version. This saves time and effort.

For example, a FOCUS static MODIFY procedure compiled and bound in Version 7.0 Release 8, does not have to be recompiled or rebound for FOCUS Version 7.0 Release 9, or vice versa. However, the procedure must be recompiled and rebound for a different FOCUS Version (for example, FOCUS Version 7.2).

Note: The 'AS module' extension to the COMPILE command is not supported for static SQL procedures. The module name must be identical to the FOCEXEC name.

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.

Optionally BIND the Plan for the 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 Relational Data Adapters, 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.

Authorize Users to Run the Plan

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.

Run-time Requirements

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

If you use extended plan management (see Extended Plan Management), you must issue the data 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 data 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 data 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 Access File library is always required when executing a static TABLE FOCEXEC. The Master File library (allocated to DDNAME MASTER) is required for all static and dynamic access.

Processing and Security Overview

When you run a compiled FOCEXEC procedure, the data 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 data adapter searches the STUBLIB load library for a load module of the same name. The data 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.

DB2 Static TABLE Example

The following annotated example illustrates the creation and automatic bind of a static FOCEXEC procedure named FEX1. It uses the following multi-table Master and Access File, EMPLOYEE, that relates two tables, PAYROLL and COURSES.

EMPLOYEE Master File:

FILENAME=EMPLOYEE,    SUFFIX=SQLDS,$
SEGNAME= PAYROLL,     SEGTYPE=S0,$
 FIELD=  EMP_ID,      EMP_ID,     A5,    A5, MISSING=OFF,$
 FIELD=  SALARY,      SALARY,     P13.2, P6, MISSING=OFF,$
SEGNAME= COURSES,     SEGTYPE=S0,$
 FIELD=  EMP_NUM,     EMP_NUM,    A5,    A5, MISSING=OFF,$
 FIELD=  COURSE_NUM,  COURSE_NUM, A5,    A5, MISSING=OFF,$
 FIELD=  POINTS,      POINTS,     I3,    I2, MISSING=OFF,$

EMPLOYEE Access File:

SEGNAME=PAYROLL, TABLENAME="USER1"."PAYROLL",
KEYS=1, WRITE=YES, KEYORDER=LOW,$
SEGNAME=COURSES, TABLENAME="USER1"."COURSES",
KEYS=2, WRITE=YES, KEYORDER=LOW,
KEYFLD=EMP_ID, IXFLD=EMP_NUM,$

Notice that the FEX1 FOCEXEC explicitly sets OPTIMIZATION, even though it requires the default value, ON:

SQL DB2 SET OPTIMIZATION ON
TABLE FILE EMPLOYEE
PRINT EMP_ID SALARY POINTS
WHERE SALARY GE &SALARY
END

The numbers 1 through 6 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
 
1.  SQL DB2 COMPILE FEX1 SALARY=50000
      END
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
6.  BIND PLAN(FEX1) MEM(FEX1) ACTION(REPLACE) ISOLATION(CS)
      DSNT252I -  BIND OPTIONS FOR PLAN FEX1
                 ACTION        ADD
                 OWNER         USER1
                 VALIDATE      RUN
                 ISOLATION     CS
                 ACQUIRE       USE
                 RELEASE       COMMIT
                 EXPLAIN       NO
      DSNT253I -  BIND OPTIONS FOR PLAN FEX1
                 NODEFER       PREPARE
                 CACHESIZE     1024
                 QUALIFIER     USER1
                 CURRENTSERVER
                 CURRENTDATA   NO
                 DEGREE        1
                 SQLRULES      DB2
                 DISCONNECT    EXPLICIT
      DSNT200I -  BIND FOR PLAN FEX1     SUCCESSFUL
      DSN
      END

The steps in the process are:

1. Issue the SQL COMPILE command.

The user invokes the Static SQL for TABLE facility. The FOCEXEC includes a variable (&SALARY) to the right of a comparison operator in a screening condition. As described in Using the SQL COMPILE and SQL RUN Facilities, when the SQL COMPILE command supplies a literal for a variable value, FOCUS substitutes an SQL host variable in that location. The value &SALARY=50000 in the SQL COMPILE command functions as a placeholder for the duration of the SQL COMPILE. The user must supply the actual value for the variable at SQL RUN time.

2. Create the Assembler program.

In this step, FOCUS reads the FOCEXEC procedure and creates an Assembler program with embedded SQL statements representing all SQL statements the FOCEXEC 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 Relational Data Adapters, 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 data 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 in the Assembler program. 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 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 request does not include the SET STATIC NOBIND command, FOCUS automatically invokes the DB2 DSN command processor and submits the following bind request:

DSN SYSTEM(DB2P)
BIND PLAN (FEX1) MEM(FEX1) ACTION(REPLACE) ISOLATION(CS)

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 (FEX1). The MEM parameter means that application plan FEX1 will include member FEX1 from the data set allocated to DDNAME DBRMLIB. Member FEX1 was created during the precompilation step. The plan owner is the DB2 authorization ID in effect at the time of the bind.

FOCEXECs compiled with the Static SQL for TABLE facility do not support the use of the LOAD command; to execute them you must use the SQL DB2 RUN command.

DB2 Static MODIFY Example

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 Relational Data Adapters, 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 data 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.