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 FOCUS procedure for compiling a FOCEXEC. Invoke the static creation process after you complete the FOCEXEC or MODIFY procedure. |
In this section: Issue the SET STATIC Command for MODIFY Procedures Authorize Users to Run the FOCEXEC Processing and Security Overview |
To begin the static TABLE creation process, execute the data adapter SQL COMPILE command (described in Using the SQL COMPILE and SQL RUN Facilities). You begin the static MODIFY creation process by issuing the data adapter SET STATIC command and executing the FOCUS COMPILE command.
In response, the data adapter automatically creates an Assembler program with the embedded SQL required by the procedure. Complete the process by exiting FOCUS and running an Information Builders-supplied EXEC that preprocesses, assembles, and links the program and creates a package in the DB2 for VM database.
This section outlines the steps required to create a static procedure, and provides a description of FOCUS processing for each step. DB2 for VM Static TABLE Example and DB2 for VM Static MODIFY Example provide annotated examples.
Creating a static procedure for DB2 for VM consists of the following steps:
Note: Execute all steps in this procedure after logging on to a user ID authorized to connect to DB2 for VM and after meeting the run-time requirements documented in Invoking Relational Data Adapters.
You can create a static procedure from virtually any TABLE or MATCH FILE request. The default isolation level is CS and cannot be changed.
A MODIFY procedure must be compilable. Consult Static SQL Requirements and your FOCUS documentation on maintaining databases for more information on compiled MODIFY requirements. The default isolation level is Cursor Stability (CS). If the MODIFY procedure updates any tables, include the SQL SET ISOLATION RR command in the MODIFY itself, immediately following the MODIFY FILE command.
To create a static MODIFY procedure, you may need to issue the SET STATIC command. Omit this step to create a static TABLE procedure.
The syntax for the static SET command for DB2 for VM is
SQL [SQLDS] SET STATIC {OFF|ON}
where:
Omit the SQLDS target database qualifier if you previously issued the SET SQLENGINE command for DB2 for VM.
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.
The data adapter provides facilities for creating and running static TABLE procedures. Refer to 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.
Before compiling the request, issue FILEDEF commands for 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:
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).
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.
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.
Issue the FIN command to exit from FOCUS before running the GENUSQL EXEC.
The GENUSQL EXEC preprocesses, assembles, links, and creates an application package for your static procedure. GENUSQL requires input parameters for execution; they acquire default values within the EXEC. To change the defaults at run time, provide overriding values on the command line in the form PARM=value. Use blanks as delimiters between parameter values; do not insert spaces on either side of the assignment symbol (=)
EX GENUSQL program [(parm=value parm=value] [DBLIST (db1 db2...dbn)]
where:
Note:
Note:
If you ran GENUSQL with GRANT=NO (the default), issue the SQL GRANT RUN command to authorize users to execute the packages created with the previous steps. The following example shows how to issue the command from within a FOCUS session:
SQL SQLDS GRANT RUN ON PLAN FEX1 TO USER1, USER2
For more information on GRANT, consult the IBM DB2 for VM SQL Reference.
In addition to the usual run-time requirements for the FOCUS DB2 for VM Data Adapter outlined in Invoking Relational Data Adapters, make sure that the FOCCOMP files for any compiled MODIFY procedures are on a minidisk that you accessed prior to invoking FOCUS.
The STUBLIB LOADLIB containing the run-time modules must reside on a minidisk that you accessed prior to invoking FOCUS. It is not necessary to GLOBAL the STUBLIB LOADLIB, as the FOCUS EXEC automatically does this. However, you must access the minidisk before invoking FOCUS.
When you run a compiled FOCEXEC procedure, FOCUS searches for a module of the same name in the STUBLIB LOADLIB file.
When you run a compiled MODIFY procedure, FOCUS searches for a FOCCOMP file on one of the minidisks you accessed prior to invoking FOCUS. If the flag in the FOCCOMP file indicates that it is a static procedure, FOCUS searches the STUBLIB LOADLIB for a module of the same name. The data adapter compares consistency tokens in the FOCCOMP and STUBLIB files.
FOCUS then invokes the static FOCEXEC or MODIFY procedure, connects to DB2 for VM, and loads the package (which always has the same name as the FOCEXEC). DB2 for VM verifies that the STUBLIB load module for the program and the DB2 for VM package have the same owner, program name, and consistency token.
This process verifies that the compiled MODIFY (FOCCOMP file), the program load module (STUBLIB LOADLIB member), and the DB2 for VM application package are valid and that no substitutions have been made.
Note: There is no equivalent in DB2 for VM for the plan management techniques available for DB2. There must be a separate package for every static procedure, and one for the dynamic DB2 for VM Data Adapter. DB2 for VM switches packages automatically in response to program execution.
The following annotated example illustrates the creation of a static FOCEXEC procedure named FEX1. This example uses a multi-table Master and Access File, EMPLOYEE, that relates two tables, PAYROLL and COURSES. DB2 Static TABLE Example provides the Master and Access Files.
The FEX1 FOCEXEC follows:
SQL SQLDS SET OPTIMIZATION ON
TABLE FILE EMPLOYEE
PRINT EMP_ID SALARY POINTS
WHERE SALARY GE &SALARY
END
The numbers 1 through 9 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 options and their possible settings.
1. SQL SQLDS COMPILE FEX1 SALARY=50000
END
2. (FOC1472) STATIC SQL PROGRAM CREATED SUCCESSFULLY
3. FIN
Ready;
4. genusql fex1 (sqluid=user1 sqlpswd=myword grant=yes
Following installation parameters assumed:
Pgm = FEX1
Sqluid = USER1
Sqlpswd = MYWORD
Grant = YES
Indvar = YES
Collid =
5. ARI0717I START SQLPREP EXEC: 11/07/94 15:22:25 EST
ARI0320I THE DEFAULT DATABASE NAME IS SQLDBA.
ARI0663I FILEDEFS IN EFFECT ARE:
SYSIN DISK FEX1 ASMSQL *
SYSPRINT DISK FEX1 LISTPREP A1
SYSPUNCH DISK FEX1 ASSEMBLE A1
ARISQLLD DISK ARISQLLD LOADLIB G1
ARI0713I PREPROCESSOR ARIPRPA CALLED WITH THE FOLLOWING PARAMETERS:
........PREP=FEX1,USER=USER1/********,ISOL(USER),BLK,NOPR,DATE(ISO)
ARI0708I ALL SQLPREP EXEC PROCESSING COMPLETED SUCCESSFULLY.
ARI0796I END SQLPREP EXEC: 11/07/94 15:22:28 EST
6. Assembling of FEX1 completed successfully.
7. ARI0717I START SQLDBSU EXEC: 11/07/94 16:21:00 EST
ARI0659I LINE-EDIT SYMBOLS RESET:
LINEND=# LINEDEL=OFF CHARDEL=OFF ESCAPE=OFF TABCHAR=OFF
ARI0656I MESSAGE FILE (SYSPRINT): TERMINAL
ARI0655I INPUT FILE (SYSIN): TERMINAL
ARI0320I THE DEFAULT DATABASE NAME IS SQLDBA.
ARI0663I FILEDEFS IN EFFECT ARE:
ARISQLLD DISK ARISQLLD LOADLIB G1
SYSIN TERMINAL
SYSPRINT TERMINAL
ARI0801I DBS Utility started: 11/07/94 16:21:05.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
ARI0828I ...LINEWIDTH reset to 80.
ARI0827I ...Begin command execution: ERRORMODE = CONTINUE
ARI0870I Enter the command terminated by semicolon or enter
EXIT to end.
------>
------> CONNECT "USER1 " IDENTIFIED BY ********;
ARI8004I User USER1 connected to server SQLDBA.
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
ARI0870I Enter the command terminated by semicolon or enter
EXIT to end.
------> GRANT RUN ON USER1.FEX1 TO PUBLIC;
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
ARI0870I Enter the command terminated by semicolon or enter
EXIT to end.
------> COMMIT WORK;
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
ARI0870I Enter the command terminated by semicolon or enter
EXIT to end.
------> EXIT
ARI8997I ...Begin COMMIT processing.
ARI0811I ...COMMIT of any database changes successful.
ARI0809I ...No errors occurred during command processing.
ARI0808I DBS processing completed: 11/07/94 16:23:39.
ARI0660I LINE-EDIT SYMBOLS RESTORED:
LINEND=# LINEDEL=¢ CHARDEL=@ ESCAPE=" TABCHAR=ON
ARI0796I END SQLDBSU EXEC: 11/07/94 16:23:39 EST
8. DMSCPY721I Copy ARIRVSTC TEXT G1 APPEND FEX1 TEXT A1 (old file)
IEW0000 INCLUDE OBJ
IEW0000 ENTRY ADDRESS
9. IEW0000 NAME FEX1(R)
Ready;
The steps in the process are:
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 the 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.
In this step, FOCUS reads the FOCEXEC program and creates an Assembler program with all the embedded SQL statements required by the FOCEXEC.
The Assembler program has the same filename as the FOCEXEC and a file type of ASMSQL. It is placed on the minidisk accessed as file mode A. In this example, the Assembler program is called FEX1 ASMSQL A.
The user issues the FIN command to exit FOCUS.
The user invokes the GENUSQL EXEC.
The GENUSQL EXEC invokes the SQLPREP EXEC supplied by IBM on the DB2 for VM production disk. SQLPREP precompiles the Assembler program (FEX1 ASMSQL A) and creates a package for it in the DB2 for VM database. The package naming convention is owner.program_name, where the owner is the COLLID (if one was provided), SQLUID (if one was provided and COLLID was not provided), or the VM user ID invoking GENUSQL (if neither COLLID nor SQLUID was provided). In this example, the package is named USER1.FEX1.
The SQLPREP EXEC invokes the IBM preprocessor for Assembler programs (ARIPRPA, residing on the DB2 for VM production disk) with the following parameters, provided by GENUSQL: ISOL(USER), BLK, NOPR, DATE(ISO). If the user provides the SQLUID and SQLPSWD parameters, USER=SQLUID/SQLPSWD. Output from the preprocessor is FEX1 LISTPREP A (the preprocessor output listing) and FEX1 ASSEMBLE A (the modified source program). The LISTPREP file contains a summary count of errors and warnings generated by the preprocessor, while the actual text of errors and warnings is in the modified source program.
Successful completion of this step is indicated by the ARI0708I and ARI0796I messages generated by DB2 for VM.
The GENUSQL EXEC assembles the modified source program (FEX1 ASSEMBLE A) using IBM's Assembler H, if available. If it does not find Assembler H, it uses the installed version. This results in object FEX1 TEXT A (a temporary file) and FEX1 LISTING A (the output from the assembly). The Assembler options are LIST and NOALIGN.
Successful completion of this step is indicated by the message displayed at item 6 in the example.
GENUSQL issues the CONNECT command to obtain the proper authorization to proceed.
Since GENUSQL was invoked with GRANT=YES, it starts the DB2 for VM Database Services Utility program (DBSU). It grants run privileges on USER1.FEX1 to PUBLIC (all users) and issues a COMMIT WORK.
Successful completion of this step is indicated by the ARI0811I and ARI0809I messages generated by DB2 for VM.
The GENUSQL EXEC appends the Resource Manager Stub (ARIRVSTC TEXT, residing on the DB2 for VM production disk) to FEX1 TEXT A.
A non-zero return code for this step results in an error message.
The GENUSQL EXEC links FEX1 TEXT A into STUBLIB LOADLIB A. It then erases FEX1 TEXT A. The linkage editor options are LIST, MAP, XREF, and NCAL.
A non-zero return code for this step results in an error message.
The following annotated example illustrates the creation of a static MODIFY procedure named MOD1. The numbers 1 through 10 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 options and their possible settings.
>
sql sqlds set static on
>
1. compile mod1
>
SAMPLE SQLDS ON 07/30/91 AT 12.23.48
2. (FOC1472) STATIC SQL PROGRAM CREATED SUCCESSFULLY
3. COMPILED...
>
4. fin
Ready; T=0.16/0.27 12:23:54
5. genusql mod1 (sqluid=admin sqlpswd=minnie grant=yes
Following installation parameters assumed:
Pgm = MOD1
Sqluid = ADMIN
Sqlpswd = MINNIE
Grant = YES
Indvar = YES
Collid =
6. ARI0717I START SQLPREP EXEC: 07/30/91 12:24:40 EDT
ARI0320I THE DEFAULT DATABASE NAME IS SQLDBA.
ARI0663I FILEDEFS IN EFFECT ARE:
OFFLINE PRINTER
SYSIN DISK MOD1 ASMSQL *
SYSPRINT DISK MOD1 LISTPREP A1
SYSPUNCH DISK MOD1 ASSEMBLE A1
ARISQLLD DISK ARISQLLD LOADLIB W1
ARI0713I PREPROCESSOR ARIPRPA CALLED WITH THE FOLLOWING PARAMETERS:
.. PREP=MOD1,USER=ADMIN/********,ISOL(USER),BLK,NOPR,DATE(ISO)
ARI0708I ALL SQLPREP EXEC PROCESSING COMPLETED SUCCESSFULLY.
ARI0796I END SQLPREP EXEC: 07/30/91 12:24:45 EDT
7. Assembling of MOD1 completed successfully.
8. ARI0717I START SQLDBSU EXEC: 07/30/91 12:24:52 EDT
ARI0659I LINE-EDIT SYMBOLS RESET:
LINEND=# LINEDEL=OFF CHARDEL=OFF ESCAPE=OFF TABCHAR=OFF
ARI0656I MESSAGE FILE (SYSPRINT): TERMINAL
ARI0655I INPUT FILE (SYSIN): TERMINAL
ARI0320I THE DEFAULT DATABASE NAME IS SQLDBA.
ARI0663I FILEDEFS IN EFFECT ARE:
ARISQLLD DISK ARISQLLD LOADLIB G1
SYSIN TERMINAL
SYSPRINT TERMINAL
ARI0801I DBS Utility started: 11/07/94 16:21:05.
AUTOCOMMIT = OFF ERRORMODE = OFF
ISOLATION LEVEL = REPEATABLE READ
ARI0828I ...LINEWIDTH reset to 80.
ARI0827I ...Begin command execution: ERRORMODE = CONTINUE
ARI0870I Enter the command terminated by semicolon or enter
EXIT to end.
------>
------> CONNECT "ADMIN " IDENTIFIED BY ********;
ARI8004I User ADMIN connected to server SQLDBA.
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
ARI0870I Enter the command terminated by semicolon or enter
EXIT to end.
------> GRANT RUN ON ADMIN.MOD1 TO PUBLIC;
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
ARI0870I Enter the command terminated by semicolon or enter
EXIT to end.
------> COMMIT WORK;
ARI0500I SQL processing was successful.
ARI0505I SQLCODE = 0 SQLSTATE = 00000 ROWCOUNT = 0
ARI0870I Enter the command terminated by semicolon or enter
EXIT to end.
------> EXIT
ARI8997I ...Begin COMMIT processing.
ARI0811I ...COMMIT of any database changes successful.
ARI0809I ...No errors occurred during command processing.
ARI0808I DBS processing completed: 07/30/91 12:24:56.
ARI0660I LINE-EDIT SYMBOLS RESTORED:
LINEND=# LINEDEL=¢ CHARDEL=@ ESCAPE=" TABCHAR=ON
ARI0796I END SQLDBSU EXEC: 07/30/91 12:24:56 EDT
9. DMSCPY721I Copy ARIRVSTC TEXT W1 APPEND MOD1 TEXT A1 (old file)
IEW0000 INCLUDE OBJ
IEW0000 ENTRY STATICSQ
10. IEW0000 NAME MOD1(R)
Ready; T=0.70/0.93 12:25:05
The steps in the process are:
The user invokes the FOCUS COMPILE facility for MODIFY procedures.
In this step, FOCUS reads the MODIFY program and creates an ASSEMBLER program with all the embedded SQL statements required by the MODIFY.
The ASSEMBLER program has the same filename as the MODIFY and a file type of ASMSQL. It is placed on the minidisk accessed as file mode A. In this example, the ASSEMBLER program is called MOD1 ASMSQL A.
FOCUS compiles the MODIFY procedure, creating MOD1 FOCCOMP A.
The user issues the FIN command to exit FOCUS.
The user invokes the GENUSQL EXEC.
The GENUSQL EXEC invokes the SQLPREP EXEC supplied by IBM on the DB2 for VM production disk. SQLPREP precompiles the ASSEMBLER program (MOD1 ASMSQL A) and creates a package for it in the DB2 for VM database. The package naming convention is owner.program_name, where the owner is the COLLID (if one was provided), SQLUID (if one was provided and COLLID was not provided), or the VM user ID invoking GENUSQL (if neither COLLID nor SQLUID was provided). In this example, the package is named ADMIN.MOD1.
The SQLPREP EXEC invokes the IBM preprocessor for ASSEMBLER programs (ARIPRPA, residing on the DB2 for VM production disk) with the following parameters, provided by GENUSQL: ISOL(USER), BLK, NOPR, DATE(ISO). If the user provides the SQLUID and SQLPSWD parameters, USER=SQLUID/SQLPSWD. Output from the preprocessor is MOD1 LISTPREP A (the preprocessor output listing) and MOD1 ASSEMBLE A (the modified source program). The LISTPREP file contains a summary count of errors and warnings generated by the preprocessor, while the actual text of errors and warnings is in the modified source program.
Successful completion of this step is indicated by the ARI0708I and ARI0796I messages generated by DB2 for VM.
The GENUSQL EXEC assembles the modified source program (MOD1 ASSEMBLE A) using IBM's ASSEMBLER H, if available. If it does not find ASSEMBLER H, it uses the installed version. This results in object MOD1 TEXT A (a temporary file) and MOD1 LISTING A (the output from the assembly). The ASSEMBLER options are LIST and NOALIGN.
Successful completion of this step is illustrated by the message displayed at item 6 in the example.
GENUSQL issues the CONNECT command to obtain the proper authorization to proceed.
Since GENUSQL was invoked with GRANT=YES, it starts the DB2 for VM Database Services Utility program (DBSU). It grants run privileges on ADMIN.MOD1 to public (all users) and issues a COMMIT WORK.
Successful completion of this step is indicated by the ARI0811I and ARI0809I messages generated by DB2 for VM.
The GENUSQL EXEC appends the Resource Manager Stub (ARIRVSTC TEXT, residing on the DB2 for VM production disk) to MOD1 TEXT A.
A non-zero return code for this step results in an error message.
The GENUSQL EXEC links MOD1 TEXT A into STUBLIB LOADLIB A. It then erases MOD1 TEXT A. The linkage editor options are LIST, MAP, XREF, and NCAL.
A non-zero return code for this step results in an error message.
| Information Builders |