A DB2 Server for VSE & VM application program contains three main parts: the prolog, the body, and the epilog. Certain SQL statements must appear at the beginning and end of the program to handle the transition from the host language to the embedded SQL statements.
The prolog is at the beginning of every program and must contain:
The body contains the SQL statements that will enable you to access and manage data. Among the statements included in this section are:
The epilog is at the end of the application program, and contains SQL statements that:
All host program variables that interact with the database manager must be declared in an SQL declare section. A program may contain multiple SQL declare sections. An SQL declare section is a group of host program variable declarations that are preceded by the SQL statement BEGIN DECLARE SECTION and followed by the SQL statement END DECLARE SECTION. Host program variables declared in an SQL declare section are host variables and can be used in host-variable references in SQL statements.
The attributes of each host variable depend on how the variable is used in the SQL statement. For example, variables that receive data from or store data in DB2 Server for VSE & VM tables must have data type and length attributes compatible with the column being accessed. To determine the data type for each variable, you must be familiar with DB2 Server for VSE & VM data types, shown in Figure 25. Each column of every table is assigned a data type when the table is created.
Host variables can be used to receive data from the database manager or to transfer data from the host program to the database manager. Host variables that receive data from the database manager are output host variables. Host variables that transfer data from the host program to the database manager are input host variables.
Consider the following SELECT INTO statement:
SELECT HIREDATE, EDLEVEL INTO :HDATE, :LVL FROM EMPLOYEE WHERE EMPNO = :IDNO
It contains two output host variables, HDATE and LVL, and one input host variable, IDNO. The database manager uses the data stored in the host variable IDNO to determine the EMPNO of the row that is retrieved from the EMPLOYEE table If a row that meets the search criteria is found, HDATE and LVL receive the data stored in the columns HIREDATE and EDLEVEL respectively. This statement illustrates an interaction between the host program and the database manager using columns of the EMPLOYEE table.
Each column of a table is assigned a data type and each data type can be related to a host language data type. For example, the INTEGER data type is a 31-bit binary integer. This is equivalent to the following data description entries in each of the host languages, respectively:
COBOL:
01 variable-name PICTURE S9(9) COMPUTATIONAL.
Assembler:
variable-name DS F
C:
long variable-name;
FORTRAN
INTEGER variable-name
PL/I:
DCL variable-name BINARY FIXED(31);
All the host language equivalents for a particular DB2 Server for VSE & VM data type are listed at the end of each host language appendix.
After you determine which column a host variable interacts with, you need to find out what DB2 Server for VSE & VM data type that column has. Do this by querying the DB2 Server for VSE & VM catalog, which is a set of tables containing information about all tables created in the database. This catalog is described in the DB2 Server for VSE & VM SQL Reference manual.
After you have determined the data types, you can refer to the conversion charts at the end of the host language appendixes, and code the appropriate declarations. Figure 4 shows the declarations in each host language.
Figure 4. Examples of Declarations and Embedded SQL Statements
| Assembler |
Col. 1 Col. 16 Col. 72
| | |
EXEC SQL BEGIN DECLARE SECTION
HDATE DS CL10
LVL DS H
IDNO DS CL6
EXEC SQL END DECLARE SECTION
EXEC SQL INCLUDE SQLCA
EXEC SQL WHENEVER SQLERROR GOTO ERRCHK
EXEC SQL SELECT HIREDATE, EDLEVEL *
INTO :HDATE, :LVL *
FROM EMPLOYEE *
WHERE EMPNO = :IDNO
.
.
.
ERRCHK
|
| C |
EXEC SQL BEGIN DECLARE SECTION;
char HDATE[11];
short LVL;
char IDNO[7];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL WHENEVER SQLERROR GOTO ERRCHK;
EXEC SQL SELECT HIREDATE, EDLEVEL
INTO :HDATE, :LVL
FROM EMPLOYEE
WHERE EMPNO = :IDNO;
.
.
.
ERRCHK: errout();
|
| COBOL |
Cols. 8 12
| |
DATA DIVISION.
FILE SECTION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 HDATE PICTURE X(10).
01 LVL PICTURE S9(4) COMPUTATIONAL.
01 IDNO PICTURE X(6).
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
EXEC SQL WHENEVER SQLERROR GOTO ERRCHK END-EXEC.
EXEC SQL SELECT HIREDATE, EDLEVEL
INTO :HDATE, :LVL
FROM EMPLOYEE
WHERE EMPNO = :IDNO END-EXEC.
.
.
.
ERRCHK.
|
| FORTRAN |
Col. 7
|
EXEC SQL BEGIN DECLARE SECTION
CHARACTER*10 HDATE
INTEGER*2 LVL
CHARACTER*6 IDNO
EXEC SQL END DECLARE SECTION
EXEC SQL INCLUDE SQLCA
EXEC SQL WHENEVER SQLERROR GOTO 4000
EXEC SQL SELECT HIREDATE, EDLEVEL
* INTO :HDATE, :LVL
* FROM EMPLOYEE
* WHERE EMPNO = :IDNO
.
.
.
4000 CONTINUE
|
| PL/I |
Col. 2
|
EXEC SQL BEGIN DECLARE SECTION;
DCL HDATE CHARACTER(10);
DCL LVL BINARY FIXED(15);
DCL IDNO CHARACTER(6);
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL WHENEVER SQLERROR GOTO ERRCHK;
EXEC SQL SELECT HIREDATE, EDLEVEL
INTO :HDATE, :LVL
FROM EMPLOYEE
WHERE EMPNO = :IDNO;
.
.
.
ERRCHK:
|
Figure 4 also shows the BEGIN and END DECLARE SECTION statements for DB2 Server for VSE. Observe how the delimiters for SQL statements differ for each language. For the exact rules of placement, continuation, and delimiting of these statements, see the appendixes of this book.
The SQL Communications Area (SQLCA) is discussed in detail in Using the Automatic Error-Handling Facilities. This section presents an overview. To declare the SQLCA, code this statement in your program:
INCLUDE SQLCA
When you preprocess your program, the database manager inserts host language variable declarations in place of the INCLUDE SQLCA statement. The system communicates with your program using the variables for warning flags, error codes, and diagnostic information.
The system returns a return code in SQLCODE after executing each SQL statement. The SQLCODE is an integer value that summarizes the execution of the statement. Refer to the DB2 Server for VSE & VM SQL Reference manual for a detailed description of the SQLCODE field. Refer to the DB2 Server for VM Messages and Codes or the DB2 Server for VSE Messages and Codes manuals for information about specific SQLCODEs.
A return code is also returned in SQLSTATE after each SQL statement is executed. SQLSTATE is a character field that provides common error codes across IBM's relational database products. SQLSTATE values comply with the SQL92 standard. For a discussion of SQLSTATE, refer to the DB2 Server for VSE & VM SQL Reference manual. For more information about specific SQLSTATEs, refer to the DB2 Server for VM Messages and Codes or the DB2 Server for VSE Messages and Codes manuals.
When a statement is executed successfully, SQLCODE is set to 0 (SQLSTATE is '00000'). A negative SQLCODE indicates an error condition. Positive SQLCODES indicate that a statement has executed successfully but a warning code may be issued which means that you must verify whether the SQL statement was executed without unexpected results.
The system supports the use of a stand-alone SQLCODE. If you request this support, do not include the SQLCA definition in your program. However, you must provide the integer variable SQLCODE (SQLCOD in FORTRAN). For a detailed discussion, see Using the Automatic Error-Handling Facilities.
If you want the system to control error checking after each SQL statement, use the WHENEVER statement. The following WHENEVER statement indicates to the system what to do when it encounters a negative SQLCODE:
WHENEVER SQLERROR GO TO errchk
That is, whenever an SQL error (SQLERROR) occurs, program control is transferred to code that follows a specific label, such as ERRCHK. This code should include logic to analyze the error indicators in the SQLCA. Depending upon the ERRCHK definition, action may be taken to execute the next sequential program instruction, to perform some special functions, or, as in most situations, to roll back the current logical unit of work (LUW) and terminate the program. See Using Logical Units of Work for more information on LUWs.
Generally, other nonexecutable SQL statements are also part of the prolog. These are discussed later in this manual, and in the DB2 Server for VSE & VM SQL Reference manual. Examples of other nonexecutable statements are:
Your program must establish a connection to the application server before it can run any executable SQL statements. This connection identifies the authorization ID of the user who is running the program, and the name of the application server on which the program will be run.
| DB2 Server for VM |
|---|
|
The program can establish the connection in two ways:
|
The authorization ID established by the connection must have been granted both the privilege to execute the program's package and CONNECT authority for the target application server. For DB2 Server for VM, the package has authority to perform the actions specified in the statements in the program if the owner of the package has the authority. For DB2 Server for VSE, the package has the authority to access database resources specified in the SQL statements in the program if the owner of the package has the authority.
| DB2 Server for VSE |
|---|
|
After the connection has been established, your program can issue SQL statements that manipulate data, define and maintain database objects, and begin control operations, such as, granting user authority, and committing changes to the database. See the DB2 Server for VSE & VM SQL Reference manual for a more detailed discussion of the CONNECT statement. |
The following are some of the statements that you can use to create and drop database objects such as tables, indexes, and synonyms. (These statements are discussed in Chapter 9, Maintaining Objects Used by a Program.)
The following are some of the statements that you can use to manipulate database objects:
These statements are discussed in detail in Chapter 3, Coding the Body of a Program.
| Note: | Refer to the DB2 Server for VSE & VM SQL Reference manual for a description of select-statements. |
The following are some of the statements that you can use to manage logical units of work, dbspaces, and locks:
There are two statements to use to assign and withdraw privileges on objects or authorities to user IDs:
They are discussed in detail in Chapter 10, Assigning Authority and Privileges.
The application epilog is the logical end of your DB2 Server for VSE & VM application program. To properly end your program:
| DB2 Server for VSE |
|---|
|
The two tasks are accomplished differently for VSE batch or ICCF applications, and for CICS/VSE transactions. |
| DB2 Server for VM |
|---|
|
Although an implicit COMMIT or ROLLBACK statement is automatic for any application that accesses an application server, you should still issue an explicit COMMIT or ROLLBACK statement. For DB2 Server for VM application programs that are not executed through an EXEC, implicit COMMIT or ROLLBACK processing occurs when the application program is completed. For those that are executed through an EXEC, this processing does not occur until the EXEC is completed. To sever the connection and cause the COMMIT or ROLLBACK to take effect from an EXEC, the SQLRMEND EXEC must be invoked. See Invoking Applications in CMS SUBSET for limitations on the use of SQLRMEND, and the DB2 Server for VSE & VM Database Administration manual for more information on this EXEC. When an implicit COMMIT or ROLLBACK is invoked, the logical unit of work will be committed if the termination was normal, or rolled back if the termination was abnormal. An application is terminated normally when it returns to CMS or, in single virtual machine mode, to the DB2 Server for VM calling routine. Any other kind of termination, such as HX, CMS abend, program check, or any user machine termination, is abnormal. In the VM environment, user-written interactive SQL applications are provided with an inherent facility to cancel an SQL statement without terminating the running application. This cancelation facility is invoked with the SQLHX immediate command established by the DB2 application requester. The only special processing ability required of the application is that it be sensitive to the -914 SQLCODE (SQLSTATE '57014'). If the user ID and password were established with an explicit SQL CONNECT, you must reissue the CONNECT statement. If you do not, the user ID password and application server revert to the value established by the implicit CONNECT. The application can modify the basic cancel facility by defining additional names for the DB2 Server for VM-defined SQLHX command or by requesting the system to remove the SQLHX command and the exit it invokes. Use the ARIRCAN macro to do these modifications. For more details on the ARIRCAN macro interface (RMXC) and the SQLHX command, see the DB2 Server for VM System Administration manual. For more information on CMS, consult the VM/ESA: CMS Command Reference or the VM/ESA: CMS User's Guide manuals. |
You can enter either
COMMIT RELEASE
to end the current logical unit of work and commit the changes to the database, or
ROLLBACK RELEASE
to end the current logical unit of work and restore the changes made to the database. The RELEASE keyword is optional; it releases your connection to the application server. You should always explicitly end your logical unit of work; however, you should release the connection only when ending the last logical unit of work (if your program has more than one) or when changing your authorization ID or the connected application server. If you release the connection in any logical unit of work other than the last logical unit of work, enter a new CONNECT statement to reestablish the connection. You should not release and reestablish the connection unnecessarily because this may degrade the performance of your program. Begin subsequent logical units of work with an explicit CONNECT statement if the previous logical unit of work was terminated using the RELEASE option.
If you do not code a RELEASE as described above, the system issues one implicitly for you upon task/program termination. Not coding the RELEASE when ending the last logical unit of work is inefficient, however; DB2 Server for VSE resources are held until the application terminates even though you may not be using them.
| Note: | If you forget to end your logical unit of work, the system interrogates a VSE flag to determine whether the program connection (to the application server) terminated normally or abnormally. If the program terminated normally, the system issues a COMMIT statement on behalf of the program. If the program terminated abnormally, the system issues a ROLLBACK statement. |
Once again, to avoid confusion, always explicitly end your logical units of work.
You can enter
COMMIT
to end the current logical unit of work and commit the changes to the database, or
ROLLBACK
to end the current logical unit of work and restore the changes made to the database. You do not have to explicitly release your connection to the application server (although you can, if you wish). DB2 Server for VSE online support automatically releases the connection for use by other CICS/VSE transactions when the current logical unit of work is committed or rolled back.
If your transaction contains more than one logical unit of work, however, it is not necessary to re-CONNECT to the application server every time you want to start a logical unit of work. When the connection to the database manager is implicitly dropped, DB2 Server for VSE online support remembers the user ID, password, and server-name established in the transaction's original CONNECT. The next time a logical unit of work is begun in that same transaction, online support implicitly issues a CONNECT for you. The re-connection is transparent to the transaction.
You do not have to explicitly issue a COMMIT if that is how you want to end the logical unit of work. A normal transaction termination causes a COMMIT statement to be issued on behalf of the transaction.
A CICS/VSE syncpoint or syncpoint rollback also causes the system to issue a COMMIT or ROLLBACK on behalf of the transaction. Conversely, a DB2 Server for VSE COMMIT or ROLLBACK statement causes a CICS/VSE syncpoint to be taken. If your application is using multiple resources, however, you should issue the SYNCPOINT statement or SYNCPOINT ROLLBACK statement instead of the DB2 Server for VSE COMMIT statement or ROLLBACK statement. Internally, SYNCPOINT statements are always more efficient than the corresponding SQL statements.
Under the CICS/VSE system, an interactive transaction can establish a user exit that will get control at points where an SQL program might be canceled. Control is transferred when the online resource manager is about to wait either for an SQL statement to complete 2 or for a cross partition link to become available. The user exit can be used to cause the current SQL statement to be canceled. The cancel will cause a -914 SQLCODE (SQLSTATE '57014') to be returned to the transaction and a ROLLBACK to be performed on the logical unit of work. A macro (ARIRCAN) is available to establish the user exit. (The ARIRCAN macro can also be used to set user data for the CIRD transaction.) For more details on the ARIRCAN macro interface and the coding of the exit, see CANCEL Exit in the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.