Preprocessing performs the following actions :
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, created either by the preprocessor or by CBND, 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 preprocessors supplied with the database manager have the following program names:
You preprocess your program to prepare it to use the system. To preprocess your program, you invoke the appropriate preprocessor through VSE job control statements. A job control ASSGN SYSIPT statement should point to your source program. Other job control statements must point to where the preprocessor should place the modified source-program output (ASSGN SYSPCH) and printed output (ASSGN SYSLST).
You can suppress SYSPCH and SYSLST output through NOPRINT and NOPUNCH parameters to the preprocessor.
The preprocessor requires the job control statement DLBL SQLGLOB. In addition, when you want the preprocessor to generate a bind file, job control statements DLBL SQLBIND and DLBL BINDWKF are required. These DLBL statements must be provided in either the preprocess job control or the system standard label subarea. For more information see Creating a Package Using CBND.
You can supply preprocessor parameters through the VSE EXEC statement PARM keyword. The preprocessor parameters are described later in this chapter.
If the preprocessor encounters an SQL error, it inserts statements in the modified source code that cause a subsequent compile (or assemble) to fail.
If the preprocessor encounters a severe error in an SQL statement, all processing stops. Syntactic checking is only performed on subsequent SQL statements if the error is not severe. The preprocessor also puts statements in the preprocessed program which will cause a subsequent compile to fail. If successful, the preprocessor places an entry in the SYSTEM.SYSACCESS catalog to record the newly created package.
Different job control is required to invoke the preprocessors depending on whether the system is running in multiple user or single user mode.
Prior to invoking any of the preprocessors, data sets for
input, output, and work files must be assigned. Data sets used by each
of the preprocessors are shown in Figure 42 and Figure 43. Many VSE systems may already have
the logical units assigned and data set labels defined during the IPL
procedure.
Figure 42. Data Sets Required by All Preprocessors
| Data Set | Used For |
|---|---|
| SYSIPT | Source input is read from here |
| SYSPCH | Modified source output is written here |
| SYSLST | Print (report) output is written here |
| SQLGLOB | CHARNAME and DBCS options are obtained from this VSAM file |
| SQLBIND | Bind files are written to this VSAM file (needed only if the bind preprocessor option is specified). |
| BINDWKF | A VSAM work file used when performing VSAM I/O against the bind file (needed only if the bind preprocessor option is specified). |
Figure 43. Work Files Required for Each Preprocessor
| Preprocessor | Data Set | Requirement |
|---|---|---|
| Assembler | SYS001 | The file must be the same size as the source input file. |
| C | SYS001 | The file must be the same size as the source input file. |
| COBOL | SYS001 | One logical record for each line of source code plus about 10 records for each SQL statement. Also, add an allowance for diagnostic messages. |
| SYS002 | Approximately 20 to 60 logical records for each SQL statement. The number you should reserve depends on the complexity of the SQL statement -- particularly the number of host variables referenced. | |
| SYS003 | Approximately 10 to 40 logical records for each SQL statement. (This number also depends on host variable references.) | |
| SYS004 | The file must be the same size as the source input file. | |
| FORTRAN | SYS001 | One logical record for each line of source code plus about 10 records for each SQL statement. Also, add an allowance for diagnostic messages. |
| SYS002 | Approximately 20 to 60 logical records for each SQL statement. The number you should reserve depends on the complexity of the SQL statement -- particularly the number of host variables referenced. | |
| SYS003 | The file must be the same size as the source input file. | |
| PL/I | SYS001 | The file must be the same size as the source input file. |
When invoking any preprocessor in multiple user mode, it is recommended that you specify SIZE=AUTO.
Figure 44 shows generic job control that invokes a preprocessor in multiple user mode.
Figure 44. Invoking the DB2 Server for VSE Preprocessor in Multiple User Mode Refer to the DB2 Server for VSE Program Directory for more information on coding LIBDEF statements. Refer to the DB2 Server for VSE Program Directory for more information on the READ MEMBER statement.
// JOB jobname
// ASSGN SYSIPT,cuu *-- } Optional - may be
// ASSGN SYSPCH,cuu *-- } assigned by
// ASSGN SYSLST,cuu *-- } standard label
// ASSGN SYSxxx,cuu *-- Preprocessor work files
// DLBL SQLGLOB,....,DISP=(OLD,KEEP) *-- } Optional - may be
// DLBL SQLBIND,....,DISP=(OLD,KEEP) *-- } provided by
// DLBL BINDWKF,.... *-- } standard label
*
// EXEC PGM=ARIPRPx,SIZE=AUTO,PARM='prepparms'
.
. Input card stream if SYSRDR and SYSIPT are
. assigned to the same device or file
.
/*
/&
Note:
definitions
the source file has been cataloged as membertype A.
When invoking any preprocessor in single user mode, you must specify SIZE=AUTO. The job control for single user mode initializes the system with the preprocessor name and desired parameters. Figure 45 shows generic job control that invokes a preprocessor in single user mode:
The job control starts the system and invokes the preprocessor. The EXEC statement initializes the system in single user mode and passes to it (as a parameter) the name of the preprocessor (ARIPRPx). Note that the slash (/) is actually written in the EXEC statement. It separates the general DB2 Server for VSE options (such as SYSMODE and PROGNAME) from the preprocessor options (such as PREPNAME and USERID). For prepparms, specify desired preprocessor parameters; the parameters are in the following section.
The DB2 Server for VSE & VM Operation manual lists all the DB2 Server for VSE initialization parameters for single user mode. However, the person who installs the system should determine what the best initialization parameters for your installation are, and pass these on to you.
| Note: | COBOL programs invoking COBOL SORT cannot be run in single user mode. |
Figure 45. Invoking the DB2 Server for VSE Preprocessor in Single User Mode
// JOB jobname
// EXEC PROC=ARIS71DB *-- DB2 for VSE Starter database
// EXEC PROC=ARIS71PL *-- Library definition
// ASSGN SYSIPT,cuu *-- } Optional - may be
// ASSGN SYSPCH,cuu *-- } assigned by
// ASSGN SYSLST,cuu *-- } standard label
// ASSGN SYSxxx,cuu *-- Preprocessor workfiles
// DLBL SQLGLOB,....,DISP=(OLD,KEEP) *-- } Optional - may be
// DLBL SQLBIND,....,DISP=(OLD,KEEP) *-- } provided by
// DLBL BINDWKF,.... *-- } standard label
*
// EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,LOGMODE=A, *
PROGNAME=ARIPRPx/prepparms'
.
. Input card stream if SYSRDR and SYSIPT are
. assigned to the same device or file
.
/*
/&
Note:
|
The following are parameters for all DB2 Server for VSE preprocessors unless otherwise noted.
Program Preparation Parameters
>>-PARM=--'--PREPname=--+----------------+--package_id---------->
'-collection_id.-'
>----,USERid=--authorization_name/password-+------------------------+>
'-,DBNAME=--server_name--'
>-----+------------------------------------------------+--'----><
+-,PrepFile=--(--membername--+-------------+--)--+
| '-.membertype-' |
'-| prepparms |----------------------------------'
prepparms
.-,APOST------. .-,NOBIND----. .-,NOBLocK--.
|--+-------------+---+------------+---+-----------+------------->
| (1) | | (3) | '-,BLocK----'
'-,Quote------' '-,BIND------'
>-----+---------------------------+----------------------------->
'-,CCSIDGraphic--(integer)--'
>-----+-------------------------+---+------------------------+-->
'-,CCSIDMixed--(integer)--' '-,CCSIDSbcs--(integer)--'
.-,NOCHECK--.
>-----+------------------------------+---+-----------+---------->
'-,CHARSUB--(--+-Sbcs--+---)---' +-,CHECK----+
+-Mixed-+ '-,ERROR----'
'-Bit---'
(1) (1)
>----+-------+--------+--------+-------------------------------->
'-,COB2-' '-,COBRC-'
.-,CTOKEN--(NO)---------.
>-----+-----------------------+---+--------------------------+-->
'-,CTOKEN--+-(---+---)--' '-,DATE--(--+-EUR---+---)--'
+-NO--+ +-ISO---+
'-YES-' +-JIS---+
+-LOCAL-+
'-USA---'
.-,NOEXIST--. .-,EXPLAIN--(NO)------------.
>-----+-----------+---+---------------------------+------------->
'-,EXIST----' '-,EXPLAIN--(--+-NO--+---)--'
'-YES-'
(4)
>----+-------------+---+---------+------------------------------|
| (2) | '-,DYNALC-'
'-,NOFOR------'
Notes:
prepparms (continued)
.-,NOGRaphic----. .-,ISOLation--(RR)-------------.
|--+---------------+---+------------------------------+--------->
| (1) | '-,ISOLation--(--+-CS---+---)--'
'-,GRaphic------' +-RR---+
'-USER-'
.-,KEEP---.
>-----+---------+---+-----------------------+------------------->
'-,REVOKE-' '-,LABEL--(label_text)--'
.-,LineCount--(60)------. .-,PACKAGE---. .-,PRint---.
>-----+-----------------------+---+------------+---+----------+->
'-,LineCount--(integer)-' '-,NOPACKAGE-' '-,NOPRint-'
.-,PUnch---. .-,REPLACE--. .-,SEQuence--------.
>-----+----------+---+-----------+---+------------------+------->
'-,NOPUnch-' '-,NEW------' | (2) |
'-,NOSEQuence------'
(3)
>----+----------+----------------------------------------------->
'-,NOSQLCA-'
>-----+--------------------------------------------+------------>
'-,SQLFLAG--(--+-SAA-----------------+---)---'
'-89--+------------+--'
'-(COMPLETE)-'
.-,STDSQL--(NO)----------------.
>-----+------------------------------+-------------------------->
'-,STDSQL--(--+-NO------+---)--'
| (4) |
'-89------'
>-----+---------------------------+-----------------------------|
'-,TIME--(--+-EUR---+---)---'
+-ISO---+
+-JIS---+
+-LOCAL-+
'-USA---'
Notes:
Specify these parameters using the PARM keyword of the VSE job control EXEC statement. The order in which you specify them is unimportant. You must separate all preprocessor parameters by a comma or by one or more blanks. (See Figure 46.)
| Note: | The maximum number of bytes that can be included within the quotation marks after the PARM keyword is 100. Therefore, you should take advantage of the abbreviations and defaults for the preprocessor parameters. |
Figure 46. Specifying Preprocessor Parameters
Multiple User Mode
// EXEC PGM=ARIPRPx,SIZE=AUTO,PARM='PREP=MYJOB,USERID=SAM/SECRET' |
Single User Mode
Col. 72 -----------------
|
// EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,LOGMODE=A, *
PROGNAME=ARIPRPx/PREPNAME=MYJOB,USERID=SAM/SECRET'
In single user mode, the first / separates the preprocessor parameters from the DB2 Server for VSE initialization parameters. The second / separates the authorization-name from the password in the USERID preprocessor parameter (described below). |
If you want to keep the authorization-name and password secret, you must suppress the printout of the job control EXEC statement that contains the preprocessor parameters. To do this, surround the preprocessor job control with the statements shown in Figure 47.
Figure 47. Suppressing Job Control Display of Authorization-Name and Password
NOLOG <--- Suppress display of JCL on the system
// OPTION NOLOG <--- Suppress output to SYSLST
(c)
(c)
(c)
LOG <--- Resume output to the CONSOLE
// OPTION LOG <--- Resume output to SYSLST
|
If the program is preprocessed successfully and PACKAGE was specified, an entry is made in the DB2 Server for VSE catalog table SYSTEM.SYSACCESS. The CREATOR column is set to the value specified for authorization-name; the TNAME column is set to the value specified for PREPNAME. For more information about the DB2 Server for VSE catalog, refer to the DB2 Server for VSE & VM SQL Reference manual.
If collection_id is not specified, it defaults to the authorization_name specified on the USERid parameter. If it is specified, it must equal the authorization_name specified on the USERid parameter.
Refer to the DB2 Server for VSE System Administration manual for a discussion of the default application server.
The following parameters can be specified in the PrepFile or in the preprocessor parameters. For a more detailed discussion of the options file, see Using the Preprocessor Option Member.
Quote causes the preprocessor to use double quotation marks (") as constant delimiters in the VALUE clauses of the declarations it generates.
The use of a single quotation mark (') or double quotation marks (") in SQL statements is not affected by this parameter.
APOST/Quote is stored in the bind file header if BIND is specified and a bind file is successfully created after preprocessing.
create a bind file; NOBIND is the default.
If you specify the BIND parameter, the preprocessor creates a
bind file. A bind file will not be created if NOCHECK is in effect and there was an error found during SQL statement validation. BIND is ignored if CHECK is specified. For a more detailed discussion of the bind file, see Creating a Package Using CBND.
| Note: | The Fortran preprocessor ignores the BIND parameter, if specified. |
When NOBLocK is specified, rows are not grouped.
BLock/NOBLock is stored in the bind file header if BIND is specified and a bind file is successfully created after preprocessing.
If you want to change the BLocK option, you must recompile (or reassemble) and relink your program after preprocessing it. You must also use CBND to rebuild the package if BIND is specified. Preprocessing alone does not change the BLocK setting. You must also use CBND to rebuild the package if BIND is specified.
not performed and so NOCHECK is ignored in this case. NOCHECK will be stored in the BIND file header if BIND is specified and a bind file is successfully created after preprocessing; NOCHECK is the default.
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 bind file. PACKAGE and BIND are ignored if CHECK is specified.
If you specify ERROR, the preprocessor executes normally except that most statement-parsing errors are tolerated. 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. If NOPACKAGE is specified, package functions are not performed and so ERROR is ignored in this case. ERROR will be stored in the bind file header if BIND is specified and a bind file is successfully created after preprocessing.
You should use the ERROR option when you are also generating a bind file and intend to bind it against a remote application server, where at least one statement in the program is specific to an unlike application server.
information, see Using the COBRC Parameter
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. NOEXIST/EXIST is stored in the bind file header if BIND is specified and a bind file is successfully created after preprocessing.
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. This option is stored in the bind file header if BIND is specified and a bind file is successfully created after preprocessing.
| 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 value in the GLOBAL SQLGLOB parameters is set to YES, the graphic option is not used and preprocessing occurs as though GRaphic had been specified. In addition, the default graphic option becomes GRaphic. |
See Selecting the Isolation Level to Lock Data for guidelines on choosing the isolation level for your program. This option is stored in the bind file header if BIND is specified and a bind file is successfully created after preprocessing.
| Note: | If you want to change the ISOLation option, you must recompile (or reassemble) and relink your program after preprocessing it. You must also use CBND to rebuild the package if BIND is specified. Preprocessing alone does not change the ISOLation setting. |
KEEP is the default. KEEP/REVOKE is stored in the bind file header if BIND is specified and a bind file is successfully created after preprocessing.
package functions and creates a package against a local database. PACKAGE is ignored if CHECK is specified; PACKAGE is the default.
If you specify the NOPACKAGE parameter, the preprocessor does not
perform package functions and will not create a package. If you specify NOCHECK as well as NOPACKAGE, NOCHECK is ignored. If you specify ERROR as well as NOPACKAGE, ERROR is ignored.
source listing output is produced. The NOPRint parameter specifies that the preprocessor listing output is suppressed, except for the summary messages that are normally printed at the end. PRint is the default.
| Note: | In the latter case, you must use the NOSEQ and MARGINS (1,80) C compiler options when compiling the modified source. |
| 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). |
Instead of specifying all the preprocessing parameters in the preprocessor job you can use an options member. This has several advantages. You can maintain a set of standard options members. They can save time, and ensure consistent use of preprocessing parameters.
You can use a preprocessor options member by including the PrepFile parameter when you are preprocessing. The member can contain only one preprocessor parameter per line. If more are found an error message is returned. Blank lines are ignored, and the parameters may be in either upper- or lowercase. You can insert comments 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 is a VSE source member.
Figure 48. The preprocessor option file example.
* 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 VSE 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.
CICS/VSE provides translators for C,COBOL, PL/I, and assembler language programs to convert CICS/VSE statements to CICS/VSE calls, similar to the function performed by the DB2 Server for VSE preprocessors. When program modules include both SQL and CICS/VSE statements, the appropriate CICS/VSE translator and the appropriate DB2 Server for VSE preprocessor must be run before compiling the language. This replaces the CICS/VSE and SQL statements with appropriate host language statements that invoke the CICS/VSE or database manager.
SQL statements may contain certain encoding, in quoted strings, that would not be properly bypassed by the CICS/VSE translators when scanning for CICS/VSE statements. For example,
"...EXEC CICS ..." "...'..."
When using the QUOTE option in COBOL, the same problem may occur for single-quoted strings. Also, when SQL statements contain DBCS constants, a similar problem may arise because DBCS constants may contain single quotation marks as part of the double-byte character set.
If a CICS/VSE translator is run before the DB2 Server for VSE preprocessor, these problems may occur in the form of unmatched quotation marks from a CICS/VSE standpoint, because a translator does not allow for SQL statements embedded in programs. Therefore, you should run the DB2 Server for VSE preprocessor before running a CICS/VSE translator. This will ensure that the SQL statements are commented out before a CICS/VSE translator processes the program.
Currently, there is a problem with running the DB2 Server for VSE preprocessor before a CICS translator. The preprocessor's output goes to SYSPCH as 81-byte records; a CICS/VSE translator, however, accepts only 80-byte input. For COBOL and PL/I programs this is not a severe problem, because these preprocessors append the stacker select and punch control character as the eighty-first byte. While SYSPCH is unblocked, CICS ignores the extra byte.
For assembler and C, however, the card punch control character is appended as the first byte. In this case it is necessary to process the DB2 Server for VSE assembler preprocessor output with a utility (OBJMAINT) to eliminate the leading byte before processing by a CICS translator. See Figure 53.
These problems can also be avoided by placing the SQL statements in a separate module from the one containing CICS statements.
When preprocessing your program, you can specify two performance parameters, the BLocK/ NOBLocK option and the ISOLation level option. discussed under Preprocessing the Program. These options are specified under the PARM keyword of the job control EXEC statement. The format and use of these parameters was discussed earlier in this chapter. The next section discusses when you would want to specify each of these options.
(Other performance considerations are discussed in the afid" 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 how long the system holds the lock on 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.
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 49 shows the isolation level variable name for each of the host
languages.
Figure 49. 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. |
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 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. When you specify the blocking option, improves performance for DB2 Server for VSE application programs that:
You can specify the blocking option as a DB2 Server for VSE 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. The block size for inserts and SELECTs is automatically fixed.
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 system sometimes overrides blocking for a particular cursor because of storage limitations in the database partition, or because of SQL statement ineligibility.
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. |
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.
If the DB2 Server for VSE database manager is acting as an application server to a non-DB2 Server for VSE application requester during a FETCH operation, the application server stores reply messages in blocking buffers. The size of the buffer is less than or equal to the block size, except when the data rows are longer than the block size.
The inclusion of external source members is indicated to the DB2 Server for VSE 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 identifies the external source member. The text_file_name is a 1 to 8 character identifier and 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, as these are special INCLUDE keywords.
The statements contained in the external source specified by may be host language statements or SQL statements (except for another INCLUDE statement). INCLUDE statements may not be nested, but the external source may contain INCLUDE SQLDA or INCLUDE SQLCA statements.
| Note: | The INCLUDE statement can be in an SQL DECLARE section, or the entire SQL DECLARE section can be within external source members. |
The INCLUDE statement may be used to obtain secondary input from a VSE source member.
INCLUDE causes input to be read from the specified source member until the end of that source member is read. At this time, SYSIPT input resumes. (File records representing the source statements must be unblocked, fixed-length, 80-character records.) The source member must be cataloged as the following source types:
If the INCLUDE statement specifies a source member that is not cataloged for the appropriate source member type, an error results.
Source member input must not contain preprocessor INCLUDE statements other than INCLUDE SQLDA or INCLUDE SQLCA, although it may contain both host language and DB2 Server for VSE statements. If an INCLUDE statement is encountered, an error will result.
Within the INCLUDE statement, text_file_name specifies a "source member" in the DB2 Server for VSE library. The source member type is determined by the preprocessor that is invoked.
The text_file_name specification must not contain the source member type identifier; the source member type will be based on the aforementioned preprocessor.