Preprocessing does two things:
The preprocessor replaces all the SQL statements in the program with host language code that invokes the new package. The new version of the program also contains the SQL statements in comment form. The package contains information to carry out the SQL requests made by the program. The database manager follows the best access path to the data for each SQL statement in the program, using available indexes and data statistics of which the system keeps track.
When the program is run, the new code calls the system to handle each SQL statement. It also links the program to the application server and translates messages and statements between the two.
The SQLPREP EXEC is used in both single and multiple user mode to preprocess application programs.
The preprocessors supplied with the database manager have the following program names:
The preprocessor takes source program input from SYSIN, and produces a modified source program, a source listing, and a package in the database. The modified source program output is sent to SYSPUNCH, and the source listing to SYSPRINT. Using the SQLPREP EXEC, you can direct SYSIN, SYSPUNCH, and SYSPRINT to various virtual devices and CMS files.
The syntax diagram on page DB2 Server for VM Program Preparation Parameters lists all the parameters for the SQLPREP EXEC. An explanation of each parameter follows the figure.
In single user mode, the SQLPREP EXEC is executed on the database machine. (The DBname parameter indicates that you are in single user mode, and identifies the application server that you want to access.) The SQLPREP EXEC then issues an SQLSTART and passes the DBname parameter. If the preprocessor encounters no errors (warnings are permissible), a package is created or replaced on the specified application server.
Use the SQLPREP EXEC in multiple user mode to preprocess an application program on one or more application servers. Use the SQLINIT EXEC to establish the default application server. If you want to preprocess your application program on other application servers, use the DBList or DBFile parameter to specify the other application servers on which you want to preprocess your application. Either of these parameters temporarily overrides the application server specified by the SQLINIT EXEC.
For each application server specified, the SQLPREP EXEC:
A package is created for each application server on which the program was successfully preprocessed. If an error is encountered during preprocessing on one of these application servers, and the ERROR parameter was not specified, a package is not created for that application server. See page *** for a discussion of the ERROR option.
When the SQLPREP EXEC is used for more than one application server, only one copy of the modified source program output is retained (the PUNCH parameter), but all the source listings (the PRINT parameter) are appended to produce a single source listing. The NOPUNCH and NOPRINT parameters may be used to suppress modified source program output and source listings, respectively.
The following are parameters for all DB2 Server for VM preprocessors unless otherwise noted.
>>-SQLPREP----+-ASM-----+--PrepParm----------------------------->
+-C-------+
+-COBol---+
+-FORTran-+
'-PLI-----'
>----(--PREPname=--+----------------+--package_id--------------->
'-collection_id.-'
>-----+----------------------------------+---------------------->
+-,PrepFile=--(--| fileparms |--)--+
'-| prepparms |--------------------'
>-----+----------------------------------------+--)------------->
'-,USERid=--authorization_name/password--'
>-----+----------------------------------+---------------------->
'-sysIN--(--+-| fileparms |-+---)--'
'-Reader--------'
>-----+-------------------------------------+------------------->
'-sysPRint--(--+-| fileparms |-+---)--'
+-Printer-------+
'-Terminal------'
>-----+-------------------------------------+------------------->
'-sysPUnch--(--+-| fileparms |-+---)--'
'-Punch---------'
(2) (1)
>-----+-| multiple-user-mode-parms |-----------+---------------><
| (1) |
'-| single-user-mode-parms |-------------'
Notes:
fileparms
|--filename----+-------------------------+----------------------|
'-filetype--+----------+--'
'-filemode-'
prepparms
.-,APOST------. .-,NOBLocK-----.
|--+-------------+---+--------------+--------------------------->
| (1) | +-,BLocK-------+
'-,Quote------' | (2) |
'-,SBLocK------'
>-----+---------------------------+----------------------------->
'-,CCSIDGraphic--(integer)--'
>-----+-------------------------+---+------------------------+-->
'-,CCSIDMixed--(integer)--' '-,CCSIDSbcs--(integer)--'
.-,NOCHECK--.
>-----+------------------------------+---+-----------+---------->
'-,CHARSUB--(--+-Sbcs--+---)---' +-,CHECK----+
+-Mixed-+ '-,ERROR----'
'-Bit---'
(1)
>----+------------+---+--------+-------------------------------->
| (1) | '-,COBRC-'
'-,COB2------'
.-,CTOKEN--(NO)------------.
>-----+--------------------------+------------------------------>
'-,CTOKEN--(--+-NO--+---)--'
'-YES-'
.-,NOEXIST--.
>-----+--------------------------+---+-----------+-------------->
'-,DATE--(--+-EUR---+---)--' '-,EXIST----'
+-ISO---+
+-JIS---+
+-LOCAL-+
'-USA---'
.-,EXPLAIN--(NO)------------.
>-----+---------------------------+--+-------------+------------>
'-,EXPLAIN--(--+-NO--+---)--' | (3) |
'-YES-' '-,NOFOR------'
>----+--------------+-------------------------------------------|
| (4) |
'-,DYNALC------'
Notes:
prepparms (continued)
.-,NOGRaphic----. .-,ISOLation--(RR)----------------.
|--+---------------+---+---------------------------------+------>
| (1) | '-,ISOLation--(--+-CS------+---)--'
'-,GRaphic------' +-RR------+
| (2) |
+-RS------+
+-UR------+
'-USER----'
.-,KEEP---.
>-----+---------+---+-----------------------+------------------->
'-,REVOKE-' '-,LABEL--(label_text)--'
.-,LineCount--(60)------.
>-----+-----------------------+--------------------------------->
'-,LineCount--(integer)-'
.-,PERiod-----.
>-----+------------------------------------+---+-------------+-->
| (2) | | (2) |
'-,OWner--(authorization_name-----)--' '-,COMma------'
.-,PRint---. .-,PUnch---.
>-----+----------+---+----------+------------------------------->
'-,NOPRint-' '-,NOPUnch-'
>-----+-----------------------------------+--------------------->
| (2) |
'-,QUALifier--(collection_id-----)--'
.-,RELease--(COMMIT)--------------------. .-,REPLACE--.
>-----+---------------------------------------+---+-----------+->
'-,RELease--(--+-COMMIT----------+---)--' '-,NEW------'
| (2) |
'-DEALLOCATE------'
.-,SEQuence--------. .-,SQLApost-----------.
>-----+------------------+---+---------------------+------------>
| (3) | | (2) (4) |
'-,NOSEQuence------' '-,SQLQuote-----------'
>----+---------------+------------------------------------------>
| (5) |
'-,NOSQLCA------'
>-----+--------------------------------------------+------------>
'-,SQLFLAG--(--+-SAA-----------------+---)---'
'-89--+------------+--'
'-(COMPLETE)-'
.-,STDSQL--(NO)----------------.
>-----+------------------------------+-------------------------->
'-,STDSQL--(--+-NO------+---)--'
| (6) |
'-89------'
>-----+---------------------------+-----------------------------|
'-,TIME--(--+-EUR---+---)---'
+-ISO---+
+-JIS---+
+-LOCAL-+
'-USA---'
Notes:
multiple-user-mode-parms
|--+------------------------------------+-----------------------|
+-DBFile--(--| fileparms |--)--------+
| .----------------. |
| V | |
'-DBList--(-----server_name---+---)--'
single-user-mode-parms
|---Dbname----(server_name)---+--------------------+------------>
'-dcssID--(dcss_id)--'
>-----+---------------------------------------------------+-----|
'-LOGmode--(--+-A-+---)----+---------------------+--'
+-L-+ '-PARMID--(filename)--'
+-N-+
'-Y-'
The parameters for the SQLPREP EXEC that apply to both single and multiple user mode are described below. When choosing names within any of these parameters, avoid whatever line-end-delimiter character (normally #) is being used in your installation.
If collection_id is not specified, it defaults to the user's authorization ID at the application requester site. In the database manager, however, an object's collection_id must be the same as the user's authorization ID at the application server site. If the collection_id does not match the application server authorization ID, a preprocessing error results. This restriction does not apply if the application server authorization ID has DBA authority.
The authorization ID at the application requester and application server sites is the authorization_name specified on the USERid parameter. If the USERid parameter is not specified, the authorization ID is the VM logon ID at the application requester site. In some situations, the VM logon ID is converted before it is received at the application server site. If the authorization ID is the VM logon ID, the conversion can cause the authorization IDs at each site to differ.
To avoid a situation in which the collection_id does not match the application server authorization ID, explicitly state the collection_id equal to the application server authorization ID.
For information on how to determine the authorization ID at the application server site, refer to the DB2 Server for VM System Administration and the Distributed Relational Database Connectivity Guide manuals.
If the USERid option is not specified, the VM logon user ID will be implicitly connected to the application server. All the static SQL statements in the program will have their authorization checked against the implicitly connected userid.
The USERid parameter is not supported when you are using DRDA protocol.
The following parameters can be specified in the PrepFile or on the command line.
Quote causes the preprocessor to use double quotation marks (") as constant delimiters in the VALUE clauses of the declarations it generates. If you do not specify this parameter, the COBOL preprocessor defaults to APOST and generates single quotation mark (') delimiters for its internal source declarations.
The use of a single or double quotation marks in SQL statements is not affected by this parameter.
When NOBLocK is specified, rows are not grouped.
SBLocK is primarily for use with application servers that support the FOR FETCH ONLY clause on the DECLARE CURSOR statement. Application servers do not support this clause, but application requesters can connect to application servers that do support FOR FETCH ONLY.
Following is a comparison of the BLocK and SBLocK options as they apply to the DB2 Server for VM preprocessors:
| Note: | Only the DB2 Server for VM preprocessors turn off SBLocK blocking because of the presence of COMMIT and ROLLBACK statements. In non-DB2 Server for VM preprocessors, only the presence of dynamically defined statements has this effect. |
If you want to change the BLocK option, you must recompile (or reassemble) and relink your program after preprocessing it. Preprocessing alone does not change the BLocK setting.
The blocking of FETCH statements is supported when you use the DRDA protocol, but blocking of INSERT statements is not. The blocking of INSERT statements is only supported when you use SQLDS protocol. See Using the Blocking Option to Process Rows in Groups for guidelines on deciding the programs for which to specify blocking.
If you specify the CHECK parameter, the preprocessor checks all SQL statements for validity and generates error messages if necessary, but does not generate a package or modified source code.
If you specify ERROR, the preprocessor executes normally except that most statement-parsing errors are tolerated; that is, it generates modified source code and performs package functions. When one of these errors is detected, the preprocessor generates an error message in the output listing and the modified source code in commented form, and continues processing. The program can be compiled and executed, but the erroneous statement cannot be executed. Use this option when you are preprocessing against multiple application servers, where at least one statement in the program is specific to an unlike application server. This option lets you successfully preprocess on each application server regardless of the presence of statements which that application server does not allow. In some situations, the ERROR option is overridden and a severe error condition results. Refer to Checking Warnings and Errors at Preprocessor Time for a discussion on debugging your SQL statements when using the ERROR option.
If the NOEXIST parameter is specified, object and authorization existence is not required, and if not found, a warning will be issued. NOEXIST is the default.
If EXPLAIN(YES) is specified, each explainable SQL statement in the program is explained during preprocessing. If you specify EXPLAIN(YES), an EXPLAIN ALL is executed. The complete set of explanation tables must, therefore, be available. If they are not available, you receive an SQLCODE -649 (SQLSTATE = 42704) and preprocessing is not successful. To interpret the explanation tables, refer to the DB2 Server for VSE & VM Performance Tuning Handbook manual.
| Note: | This option is also implied if the STDSQL (89) or STDSQL (86) parameter is specified. |
| Note: | This option is only accepted by the COBOL, PL/I, C, and Assembler preprocessors. |
be validated. NOGRaphic is the default.
If GRaphic is specified, the preprocessor accepts SQL statements containing graphic constants, and checks that all strings of DBCS characters are correctly formatted.
If NOGRaphic is specified, the preprocessor does not allow graphic constants in SQL statements, and does not verify the format of strings of DBCS characters.
| Note: | If the DBCS parameter of the SQLINIT EXEC is specified as YES, the graphic option is not used and preprocessing occurs as though GRaphic had been specified. Refer to Initializing the User Machine for a discussion of the SQLINIT EXEC. |
You cannot specify the USER option when you are using DRDA protocol (if you do, it is ignored and the isolation level defaults to CS).
See Selecting the Isolation Level to Lock Data for guidelines on choosing the isolation level for your program.
| Note: | If you want to change the ISOLation option, you must recompile (or reassemble) and relink your program after preprocessing it. Preprocessing alone does not change the ISOLation setting. |
See the section on PREPname on page *** for a discussion on application server and application requester authorization IDs.
For an application server, the only acceptable decimal delimiter is a period.
The QUALifier parameter is meant to be used when preprocessing against a non-DB2 Server for VM application server. If you specify this parameter when preprocessing against an application server, the collection_id must be the same as the application server authorization ID. If you do not specify this parameter, the default is selected by the application server.
For an application server, the only acceptable action is RELEASE(COMMIT), which releases resources at the end of a logical unit of work.
| Note: | If NEW is specified along with KEEP or REVOKE, an error will occur. |
| Note: | In the latter case, you must use the NOSEQ and MARGINS (1,80) C compiler options when compiling the modified source. |
For an application server, the only acceptable string delimiter is a single quotation mark.
If you request NOSQLCA support, it is your responsibility to make sure that there are no explicit declarations of the SQLCA in your application program. For more information on using SQLCODE without the SQLCA, refer to Using the Automatic Error-Handling Facilities.
| Note: | This option is also implied if the STDSQL(89) or STDSQL (86) parameter is specified. |
See Using the Flagger at Preprocessor Time for more details on this facility, including an explanation of the SQL-89 standard.
| Note: | STDSQL(86) is a synonym for STDSQL(89). |
sysIN( filename filetype )
sysIN( filename filetype filemode )
This optional parameter identifies the filename (fn), and optionally the filetype (ft) and filemode (fm), of the CMS file containing the preprocessor source input. The filetype specification defaults to the following:
The file mode specification will default to A.
The following CMS FILEDEF command is issued for the preprocessor source input file:
FILEDEF SYSIN DISK fn ft fm (RECFM FB LRECL 80 BLOCK 800)
This specification of the sysIN optional parameter identifies that the preprocessor source input file is a virtual reader file. The following CMS FILEDEF command is issued for the preprocessor source input file:
FILEDEF SYSIN READER (RECFM F LRECL 80)
| Note: | If the sysIN parameter is not specified, you must enter a CMS FILEDEF command for the preprocessor source input (ddname=SYSIN) before issuing the SQLPREP EXEC. |
sysPRint( filename filetype )
sysPRint( filename filetype filemode )
This optional parameter identifies the filename (fn) and optionally the filetype (ft) and filemode (fm) of the CMS file containing the preprocessor source output listing. The filetype specification defaults to LISTPREP, and the filemode specification to A.
If this form of the sysPRint parameter is supplied, the following CMS FILEDEF command is issued for the preprocessor source output listing file:
FILEDEF SYSPRINT DISK fn ft fm . . .
(RECFM FBA LRECL 121 BLOCK 1210 DISP MOD)
This specification of the sysPRint optional parameter identifies that the preprocessor source output listing file is directed to a virtual printer file. If sysPRint(Printer) is specified, the following CMS FILEDEF command is issued for the preprocessor source output listing file:
FILEDEF SYSPRINT PRINTER (RECFM FA LRECL 121)
This specification of the sysPRint optional parameter identifies that the preprocessor source output listing file is directed to the console terminal. If sysPRint(Terminal) is specified, the following CMS FILEDEF command is issued for the preprocessor source output listing file:
FILEDEF SYSPRINT TERM (RECFM FA LRECL 121)
FILEDEF SYSPRINT DISK fn LISTPREP A . . .
(RECFM FBA LRECL 121 BLOCK 1210 DISP MOD)
In this example, fn is the file name specification used for the preprocessor SYSIN file, and file mode is defaulted to A.
| Note: | If sysPRint and sysIN information is not specified, then the user must issue a CMS FILEDEF command for the preprocessor source output listing file (ddname=SYSPRINT) before issuing the SQLPREP EXEC. |
sysPUnch( filename filetype )
sysPUnch( filename filetype filemode )
This optional parameter identifies the filename (fn) and optionally the filetype (ft) and filemode (fm) of the CMS file containing the preprocessor modified source output. The file type specification will default to a value based on the preprocessor invoked as follows:
The file mode specification will default to A.
If this form of the sysPUnch parameter is supplied, the following CMS FILEDEF command is issued for the preprocessor modified source output file:
FILEDEF SYSPUNCH DISK fn ft fm . . .
(RECFM FB LRECL 80 BLOCK 800)
This specification of the sysPUnch optional parameter identifies that the preprocessor modified source output file is directed to a virtual punch file. If sysPUnch(Punch) is specified, the following CMS FILEDEF command is issued for the preprocessor modified source output file:
FILEDEF SYSPUNCH PUNCH (RECFM F LRECL 80)
FILEDEF SYSPUNCH DISK fn ft A . . .
(RECFM FB LRECL 80 BLOCK 800)
In this example, fn is the file name specification used for the preprocessor source input file, and file mode is defaulted to A. ft is the default file type as determined by the previously mentioned method.
| Note: | If sysPUnch and sysIN information is not specified, then the user must issue a CMS FILEDEF command for the preprocessor modified source output file (ddname=SYSPUNCH) before issuing the SQLPREP EXEC. |
The parameters for the SQLPREP EXEC that apply only to single user mode are:
This parameter is used as the DBname parameter for the SQLSTART EXEC that is executed when the database manager is started in single user mode. The system initialization parameters SYSMODE=S and PROGNAME=progname (where progname varies according to which preprocessor is being invoked) will also be supplied in the PARM parameter of the SQLSTART EXEC.
Refer to the DB2 Server for VM System Administration manual for more information.
Refer to the DB2 Server for VM System Administration manual for more information.
Refer to the DB2 Server for VM System Administration manual for more information.
The parameters for the SQLPREP EXEC that apply only to multiple user mode are:
The rules governing the format of the CMS file are as follows:
The SQLPREP EXEC accepts only those parameters and options which are listed in this manual. Some of those options are only meaningful to one or more of the other IBM relational database server or servers. The SQLPREP EXEC does not filter out options that are not applicable to an application server before sending them to that application server.
Equivalent parameters and options for IBM relational database products are given in the IBM SQL Reference manual. For example, the VALIDATE(BIND) parameter in the DB2 product for MVS and the EXIST parameter for the DB2 Server for VM product are equivalent preprocessing parameters.
When the DB2 Server for VM system acts as an application server and receives an unsupported preprocessing parameter value, it returns an error message to the application requester.
Instead of specifying all the preprocessing parameters (found in PrepParm) in the SQLPREP EXEC you can use an options file. Maintaining a set of standard options files has several advantages: they can save you time; they can ensure consistent use of preprocessing parameters; and the number of parameters that you can use is not limited by the number of positions on the command line.
You can use a preprocessor options file by including the PrepFile parameter when you issue the PREP command. The file itself can contain only one preprocessor parameter per line. If more are found an error message is returned. Blank lines are ignored, and parameters may be in either upper or lower case. Comments may be inserted into the options file by placing an asterisk (*) to the left of the comment. Everything to the right of the asterisk is ignored. The file must be fixed blocked and must have a record length of 80 bytes. Figure 40 is an example of a preprocessor option file.
Figure 40. An Example of a Preprocessor Option File
* prep parameters for program SAMPLE
ISOL(CS) *cursor stability isolation level
TIME(ISO)
BLOCK *indicate inserts and retrieves in groups
|
The Flagger is invoked at preprocessor time by the optional parameter SQLFLAG It provides an auditing function on the static SQL statements in the host program. This function is independent of the other preprocessor functions, and has no bearing on whether the preprocessor run will complete satisfactorily.
The audit compares the static SQL statements with the SAA standard or the SQL-89 standard. SQL-89 is a collective term that implies support of SQL as defined by the Federal Information Processing Standards (FIPS) 127-1. It includes:
In addition to basic syntax checking against SQL-89, Flagger optionally performs semantics checking against SQL-89. This includes some integrity checking between the SQL statements and the database. For example, it checks:
Any statements that do not conform to the standards are flagged in the form of information messages in the preprocessor output listing. Flagger, however, does not force you to comply with the standards. The purpose of Flagger is to provide guidance for those users who want to conform to these standards, so that they can have SQL consistency across operating environments.
| Note: | The DB2 Server for VM product is a superset of the SQL-89 standard without the Integrity Enhancement feature. For example, the datetime data types are not part of SQL-89 and the CONNECT statement is not part of SQL-89. The use of extensions such as these will generate information messages for deviations from the standard specified in the SQLFLAG parameter. |
The Flagger messages generated at preprocessor time range from ARI5500 to ARI5599, and are further classified as follows:
In this event, SQL-89 semantics checking will be turned off and its syntax checking may or may not be turned off, depending on the nature of the failure. However, the preprocessor run itself will continue, and any inconsistencies discovered by Flagger prior to the failure will be included in the output listing of the run.
When preprocessing your program, you can specify two performance parameters, the SBLocK/BLocK/NOBLocK option, and the ISOLation level option. The format and use of these options within the SQLPREP EXEC was discussed under Preprocessing the Program. The next section discusses when you would want to specify each of these options.
(Other performance considerations are discussed in the DB2 Server for VSE & VM Database Administration manual.)
The database manager puts locks on data that your program works with, to keep other users from reading or changing that data. You can specify either to lock all the data that the current logical unit of work (LUW) has read, to lock just the row or page of data that a cursor is currently pointing to, or to not lock any data being read. This is called specifying the isolation level of the lock.
The isolation level used by an application is set using the ISOLation preprocessing parameter. On SELECT, SELECT INTO, INSERT, searched UPDATE, and DELETE statements, the WITH clause may be specified to override the value specified on the preprocessing parameter.
If you choose to put a lock on all the data that your program's current LUW has read, this is called specifying isolation level repeatable read.
Repeatable read locks are held until the end of the LUW. If you choose to put a lock on just the row or page of data that your cursor is pointing to, then you are specifying isolation level cursor stability. With cursor stability locking, when the cursor moves, the system frees all the data previously read by the program that was held by the lock. If you choose not to lock the data that your program will read, this is called specifying isolation level uncommitted read. With uncommitted read, no locks are held on the data being read, and as a result, the data can be changed by other applications.
Both repeatable read and cursor stability provide you with the following data isolation from other concurrent users:
In addition to the above, repeatable read locking provides you with the following data isolation from other concurrent users:
This extra isolation has its drawbacks, however. When you specify repeatable read for data in public dbspaces with PAGE or ROW level locking, you reduce the concurrency of the data. This means that other users may be locked out from the data for a long time, causing delays in their programs' executions.
If you specify cursor stability instead, you reduce these locking problems by making the data more available. With this isolation level, the system does not hold the locks as long. After a cursor has moved past a row or page of data, the lock on that data is dropped. This increases concurrency so that other users can access data faster.
Cursor stability can, however, cause some data inconsistencies. For instance:
HOST_EMPNO = '000250'
EXEC SQL SELECT SALARY /* HOST_SALARY is 19180 */
INTO :HOST_SALARY
FROM EMPLOYEE
WHERE EMPNO = :HOST_EMPNO;
HOST_SALARY = HOST_SALARY + 1000; /* HOST_SALARY increased to 20180 */
EXEC SQL UPDATE EMPLOYEE /* UPDATE SALARY in EMPLOYEE */
SET SALARY = :HOST_SALARY; /* TABLE with HOST_SALARY */
WHERE EMPNO = :HOST_EMPNO;
EXEC SQL SELECT SALARY /* HOST_SALARY may not be 20180 */
INTO :HOST_SALARY /* because lock was not held for*/
FROM EMPLOYEE /* the duration of the LUW */
WHERE EMPNO = :HOST_EMPNO;
COMMIT WORK;
In the previous example, it is possible that two or more users could read the salary column with the same value at approximately the same time. They would then each increment the number and issue the UPDATE statement. The second user would wait for the first user's update to finish, and then overwrite it with the same number.
Unlike RR or CS, uncommitted read does not provide any data isolation from other concurrent users. Like CS though, concurrency is improved, although at the risk of data inconsistency. UR can cause similar data inconsistencies as those described for CS and should only be used when it is not necessary that the data you are reading be committed.
An application using isolation level UR is still restricted to access only
data for which it has authorization. However, because it will be able
to read uncommited changes, it will be able to read additional rows which an
application, with the same authorization but using RR or CS, could not.
This is illustrated by the following example.
|
Rows of table: A B C <---D E |
| ||
|
|
| ||
|
|
| ||
|
|
|
When should each of these options be chosen for your program? Usually, you should specify repeatable read locking. Only use cursor stability if your program causes or will cause locking problems. For instance, you would probably want to use cursor stability for transactions that perform terminal reads without performing a COMMIT or ROLLBACK, or programs that do bulk reading, because it is handy for programs that browse through large amounts of data. For programs that perform commits or rollbacks before issuing terminal reads, you should use repeatable read locking, because they probably will not cause locking problems. Also, any application that needs to protect itself against updates should also use repeatable read locking. For programs where concurrency is wanted, for example, data being queried simultaneous to being updated, you would use uncommitted read locking. Of course, this would be for applications where data integrity was not important because the data being read may not necessarily have been committed. For single row processing (UPDATE and DELETE, for example) by way of unique indexes, cursor stability performs no better, and may perform worse, than repeatable read isolation.
One additional isolation level exists in DRDA protocol: Read Stability (RS). RS is not supported by a DB2 Server for VSE & VM application server, but it is recognized as a valid preprocessing option by the database manager. For more information on this option, refer to the DB2 Server for VSE & VM SQL Reference manual.
Upon receiving a request for the RS isolation level, an application server escalates it to RR and proceeds without indicating the escalation to the application requester.
You can also mix isolation levels,
to have your program set, change, and control its own isolation level as it is running.
You can specify mixed isolation level with the USER option of the ISOLation preprocessor parameter, as detailed under Preprocessing the Program.
If you choose this option, your program must pass the isolation level value to the application server by a program variable. It must declare a one-character program variable and must set this variable to the desired isolation level value before executing SQL statements. For repeatable read, your program should set this variable to R; for cursor stability, the variable should be set to C; and for uncommitted read, the variable should be set to U. The program can change the variable at any time so that subsequent SQL statements are executed at the new isolation level value. However, if your program changes the isolation level while a cursor is OPEN, the change does not take effect for operations on that cursor until it has been closed and opened again. That is, until the cursor is closed all operations on that cursor are executed at the isolation level value that was in effect when the cursor was opened. Note that the changed isolation level will be used (without error) for SQL statements not referencing the opened cursor.
If the program sets the isolation level variable to a value other than C, R or U, or if it fails to initialize the variable, the system stops execution and returns an error code in the SQLCA.
Figure 41 shows the isolation level variable name for each of the host
languages.
Figure 41. Variable Names for Specifying Mixed Isolation Levels
| Host Language | Variable Name | Example |
|---|---|---|
| assembler | SQLISL | SQLISL DS CL1 |
| C | SQLISL | char SQLISL; |
| COBOL | SQL-ISL | 01 SQL-ISL PIC X(1). |
| FORTRAN | SQLISL | CHARACTER SQLISL |
| PL/I | SQLISL | DCL SQLISL CHAR(1); |
| Note: | If you forget to declare the isolation level variable in a PL/I program, the PL/I compiler issues an informational message which can, in some environments, be suppressed. |
If you preprocess using DRDA protocol, the USER isolation level option is not supported. In DRDA protocol, the application requester changes any USER isolation level request to CS. If you preprocess using SQLDS protocol but later invoke the package using DRDA protocol, the application server defaults to the CS isolation level at run time. If a package is preprocessed and invoked using SQLDS protocol, the isolation level setting is not affected.
Isolation level cursor stability or uncommitted read only has meaning for data in public dbspaces with ROW or PAGE level locking. Data in private dbspaces or in public dbspaces with DBSPACE level locking always uses repeatable read isolation. However, programs which access such data and do not require repeatable read should be preprocessed with cursor stability or uncommitted read. The data concurrency requirements might change and cause the data to be moved to a public dbspace with PAGE or ROW level locking. In this case, the program would not need to be repreprocessed to run at isolation level cursor stability or uncommitted read.
To use the features of CS or UR, data must reside in public dbspaces with PAGE or ROW level locking. DML statements against private dbspaces or public dbspaces with PAGE or ROW level locking under isolation level CS or UR are handled the same as if isolation level RR were used.
When the system uses a dbspace scan (that is, does not use an index) to access a table in a dbspace with ROW level locking using isolation level cursor stability, the effect is the same as repeatable read. That is, no other LUW can update the table until the logical unit of work performing the dbspace scan ends. Also, if an LUW is updating a table, another LUW (using cursor stability) cannot access that table with a dbspace scan until the updating LUW ends. This reduced concurrency for dbspace scans does not apply to tables in dbspaces with PAGE level locking, or to accessing tables through indexes. Because most database accesses will typically use indexes, the reduced concurrency caused by dbspace scans should not occur frequently.
The isolation level specification affects UPDATE and DELETE processing as well as SELECT processing. For UPDATE and DELETE processing, the system acquires UPDATE locks. UPDATE locks can be acquired for both cursor stability and repeatable read isolation level settings. If the user actually wants to update or delete the data, the UPDATE lock is changed to an EXCLUSIVE lock; otherwise, the UPDATE lock is changed to a SHARE lock.
Note the following about UPDATE LOCKS:
Internally generated SELECT, UPDATE, or DELETE statements use cursor stability locking no matter what the isolation level is set to. (See Enforcing Referential Integrity for information on these statements). Conversely, data definition statements such as CREATE, ACQUIRE, or GRANT, use repeatable read locking no matter what the isolation level is set to. These statements, therefore, should not play a role in your choice of isolation level.
| Note: | Catalog access for SQL statement preprocessing is also always done with repeatable read locking. |
You can insert and retrieve rows in groups or blocks, instead of one at a time. This is called specifying the blocking option. Specifying one of the blocking options (SBLock or BLock), improves performance for DB2 Server for VM application programs that:
You can specify the blocking option as a DB2 Server for VM preprocessor parameter, or as an option on the CREATE PACKAGE statement. After a program has been preprocessed with the blocking option, all eligible cursor SELECTs and all eligible cursor INSERTs within the program are blocked. You do not have to specify a block size or block factor.
When using DRDA protocol, you can specify the block size by using the SQLINIT EXEC. Performance is closely related to block size when using DRDA protocol.
The programs that would benefit the most from blocking are those that do multiple-row inserts (with PUT statements) or multiple-row SELECTs (with FETCH statements). In both cases, a cursor must be defined. (See Retrieving or Inserting Multiple Rows; for more information on cursors.) Thus, a general rule for blocking is USE BLOCKING FOR PROGRAMS THAT DECLARE CURSORS.
A program can use either PUT or FETCH statements without being sensitive to whether the system is blocking. These statements work regardless of whether you specified the blocking option. What information is returned in the SQLCA after each PUT or FETCH, however, depends on whether blocking is in effect or not.
Remember that when you preprocess a program with the blocking option, all eligible INSERT and SELECT cursors are blocked. You cannot specify blocking for just INSERTs or for just SELECTs. If you specify the blocking option, it automatically applies to both.
When are INSERT or SELECT statements not eligible for blocking? The database manager sometimes overrides blocking for a particular cursor because of storage limitations in the virtual machine, or because of SQL statement ineligibility. The following SQL statements are ineligible for blocking and cause blocking to be overridden automatically for the cursors they refer to:
The system also disqualifies blocking if it cannot fit at least two rows into a block. (The number of rows that fit into a block may differ from one PUT/FETCH statement to the next, even when such statements operate on the same table.)
The system does not halt the program when it overrides blocking. Instead, in each of the above cases, it sets a warning flag in the SQLCA. The warning can be detected by using WHENEVER SQLWARNING in the program. See Using the Automatic Error-Handling Facilities for more information on the SQLCA and the SQL WHENEVER declarative statement.
| Note: | The DECLARE CURSOR... statement can also be written without the FOR UPDATE OF clause, even though positioned updating is subsequently done. (This is allowed when NOFOR support is invoked at preprocessor time.) In this case, blocking is also ineligible. |
The system also overrides blocking for all programs running in single user mode. In this instance, the system does not usually return a warning to the SQLCA. A warning is returned to the SQLCA for programs running in single user mode if:
The DBS Utility may get blocking ineligible warnings when it is run in single user mode because it is preprocessed with the BLocK option, but uses PREPARE to process SELECT statements.
| Note: | Always CLOSE a cursor before issuing a COMMIT statement, especially when blocking. If you commit changes before closing an insert cursor that is being blocked, you receive an error. If you are using DRDA protocol and if the HOLD option is in effect, your application does not have to close the cursor before committing the LUW. |
When blocking is active, a single SQLCA is returned with each block of rows. This SQLCA is returned to the application program with the last row in the block. However, for the final block of rows, the FETCH that returns the "not found" condition (SQLCODE = +100 and SQLSTATE='02000') will return the SQLCA. (For more information on SQLCA refer to Using the SQLCA). This has the following implications for application programming:
For example, if SQLWARN3 is set (to indicate that the application has fewer target variables in the INTO clause than the number of items in the SELECT list), the application will not be notified until either the last row in a block or the "not found" condition is returned.
When the database manager is acting as an application requester in DRDA protocol, no blocking is provided on a PUT statement. Each PUT statement results in the execution of an INSERT statement. Blocking of inserts is not supported when your application is accessing a remote application server using DRDA protocol. If you are loading a large amount of data while using DRDA protocol, transfer the data through some other means, and then use the local utility to load it into the application server.
In DRDA protocol, the block size for FETCH statements is determined by the QRYBLKSIZE parameter in SQLINIT. For information on SQLINIT, refer to the DB2 Server for VSE & VM Database Administration manual.
The inclusion of external source files is indicated to the DB2 Server for VM preprocessor by an embedded SQL statement, the INCLUDE statement, in the user's source code. This statement can appear anywhere that an SQL statement can appear, and indicates within the source code where the external source is to be placed. The syntax for the INCLUDE statement is as follows:
>>-INCLUDE--text_file_name------------------------------------->< |
where text_file_name is a 1- to 8-character identifier that identifies the file name of the external source file. Text_file_name cannot be delimited by double quotation marks. The first character must be a letter (A-Z), $, #, or @; the remaining characters must be letters, digits (0-9), $, #, @, or underscore (_), unless further restricted by the operating system. Also, text_file_name cannot be SQLCA or SQLDA, because these are special INCLUDE keywords.
You can use the INCLUDE statement to obtain secondary input from a CMS file. If a source program input to a DB2 Server for VM preprocessor uses the INCLUDE facility, any files to be used as secondary input must be accessed by the user. A search of all accessed CMS mini-disks for the file name and file type is conducted in standard CMS search order and the first match determines the file mode. This filename, filetype, and filemode are used as the secondary input or external source. The CMS file containing the secondary input statements must be fixed-length, 80-character records.
The INCLUDE statement causes input to be read from the specified file name until the end of the file, at which time the SYSIN input resumes. The file to be included must have an appropriate file type:
The file mode is determined by the search of the virtual machine's accessed minidisks. If the INCLUDE statement specifies a file name that is not located on any user-accessed CMS mini-disk, an error will result.
Secondary input must not contain preprocessor INCLUDE statements other than INCLUDE SQLDA or INCLUDE SQLCA, although it may contain both host language and SQL statements. If an INCLUDE statement is encountered, an error will result.