Session 31: Error Handling
Learning Objectives Learning Objectives
After completing the session, you will be able to:
Debug errors in a COBOL-DB2 application program through several ways in addition to SQLCODE check
Error Handling - Introduction
To facilitate error processing, DB2 provides:
SQL Communication Area (SQLCA)
A Subprogram DSNTIAR
WHENEVER statement
SQLCA
SQLCA holds the information about the execution of the embedded SQL statements. SQLCODE in SQLCA contains the return code which provides information about the execution of the last SQL statement. In addition to SQLCODE, you have some other fields in SQLCA which are also useful while processing the COBOL-DB2 application programs.
01 SQLCA.
10 SQLSTATE PIC X(5).
The following table gives the description about the useful fields which are highlighted:
Field Description
SQLCODE SQL Return Code indicating the status of the most recent SQL statement. This field is unique to DB2 for MVS.
SQLERRD(3) Number of rows affected by an INSERT, UPDATE or DELETE statement SQLWARN0 Contains W if any other SQLWARN field contains W
SQLWARN1 Contains W if a string was truncated when stored in a host variable
SQLWARN2 Contains W if null values were excluded during the processing of a column function SQLWARN3 Contains W if the number of columns and host variables do not match
SQLWARN6 Contains W if an arithmetic operation produces an unusual date or timestamp. For example, if 4 months are added to 1997-01-31, the result is 1997-04-31. As April does not have 31 days, the results are converted to 1997-04-30.
SQLSTATE Contains a return code indicating the status of the most recent SQL statement. This field can be used across DB2 platforms (DB2 for MVS, OS/2, Windows NT)
DSNTIAR
DSNTIAR is an “Error Reporting Routine” supplied by IBM for DB2. This program takes error data, adds explanatory text and presents it in a more user friendly format. You can call DSNTIAR from a COBOL-DB2 application program to display a formatted error message.
You have to pass three parameters while calling DSNTIAR routine.
Name of the SQL Communication Area
Name of the working storage area where the routine will put the formatted error message text
Length of the text lines (must be between 72 and 240)
Example:
WORKING-STORAGE SECTION.
01 WS-DB2-ERR-MESSAGE.
05 WS-DB2-ERR-MESG-LEN PIC S9(04) COMP VALUE +800.
05 WS-DB2-ERR-MESG-TEXT PIC X(80) OCCURS 10 TIMES
INDEXED BY WS-DB2-ERRMSG-IDX.
01 WS-DB2-ERRMESG-LINE-LEN PIC S9(09) COMP VALUE +80.
As DSNTIAR returns a variable length message, the second parameter should contain:
Length Component
Logic behind the numbers:
Length of each line as set by the third parameter Î 80
Number of lines for the message Î 10
So the total length of the data component Î 800
Call statement in the Procedure Division DSNTIAR routine should be called as follows:
CALL ‘DSNTIAR’ USING SQLCA
WS-DB2-ERR-MESSAGE
WS-DB2-ERRMESG-LINE-LEN.
DSNTIAR return codes
Following is the list of return codes after calling DSTIAR:
Code Meaning
0 Successful execution
4 More data was available than could fit into the provided message area 8 The logical record length was not between 72 and 240 inclusive
12 The message area was not large enough, or the message length was 240 or greater
16 Error in TSO message routine
20 Module DSNTIA1 could not be loaded 24 SQLCA data error
WHENEVER
SQL has an error trapping statement called WHENEVER that you can embed in an application program. When the WHENEVER statement is processed, it applies to all subsequent SQL statements issued by the application program in which it is embedded. WHENEVER directs
processing to continue or to branch to an error handling routine based on the SQLCODE returned for the statement. The syntax is as follows:
EXEC SQL
WHENEVER {SQLERROR | SQLWARNING | NOT FOUND}
{ {GOTO | GO TO} paragraph-name | CONTINUE}
END-EXEC.
The following table gives the meaning of the keywords used in WHENEVER:
Keyword Meaning
SQLERROR The SQLCODE is negative
SQLWARNING The SQLCODE is positive but not +100 or SQLWARN0 equal to W NOT FOUND The SQLCODE is equal to +100
GOTO or GO TO Branch to the paragraph name that follows
CONTINUE Continue with the statement that follows the SQL statement that caused the error condition
Avoid using the WHENEVER statement. It is almost always safer to code specific SQLCODE checks after each SQL statement and process accordingly. Additionally, you should avoid coding the GO TO verb as used by the WHENEVER statement. The GO TO construct is generally avoided in structured application programming methodologies.
Example:
EXEC SQL
WHENEVER NOT FOUND CONTINUE END-EXEC.
EXEC SQL
WHENEVER SQLWARNING
GO TO ERROR-PARAGRAPH END-EXEC.
Try It Out
Problem Statement:
Create a program which tries to insert the following row to TB_RATE table. As this row already exists in TB_RATE, the INSERT should give error. This error message needs to be formatted through DSNTIAR.
RATE_CATE RATE_PER_HOUR
D 50
Code:
IDENTIFICATION DIVISION.
PROGRAM-ID. ERRORHAN.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
******************************************************************
10 FILLER PIC X(12) VALUE 'SQLCODE IS: '.
END-EXEC.
Refer File Name: ErrorHandling_Program_ERRORHAN_Session#31_Slide#7 to obtain soft copy of the program code
Summary
To facilitate error processing, DB2 provides the following:
SQL Communication Area (SQLCA)
A Subprogram DSNTIAR
WHENEVER statement Test Your Understanding
1. Explain about the useful fields in SQLCA.
2. Explain about DSNTIAR.
3. Explain about WHENEVER statement.
Exercises
Try to delete the patient number “A113” from the patient table and capture the formatted error message using DSNTIAR.
Session 33: Commit and Rollback
Learning Objectives
After completing the session, you will be able to:
Store the updated data of the database in the disk COMMIT
When a program issues an INSERT, UPDATE, or DELETE statement, DB2 does not immediately write the table modification to disk. It logs the changes in a dataset and keeps track of the changes in virtual storage buffers. When it reaches the commit point it writes the logged changes to the disk.
Types of Commit:
Implicit Commit
Explicit Commit
Implicit Commit:
This occurs when a program terminates normally by reaching a STOP or GOBACK statement.
Explicit Commit
This occurs when a program issues an SQL COMMIT statement.
COMMIT statement should be issued in a program as follows:
EXEC SQL
COMMIT END-EXEC
Unit of Work or Unit of Recovery:
This consists of the transactions that are logged between two commit points:
If the program does not include any COMMIT statements, none of the transactions are committed until the program terminates normally. This can seriously degrade the performance. So a program which deals with huge volumes of data should issue a COMMIT statement whenever a unit of recovery reaches an appropriate size.
You need to issue explicit commit when a resubmit of the failing program causes reprocessing of all the records.
You need to issue explicit commit in such a way that to achieve data integrity:
o Issuing a DB2 COMMIT saves the updates done during a unit of work.
o For Example, suppose you have an application that involved update of two tables that are related to each other. Suppose the update of table is committed and the program abends before committing the UPDATE of the other. The data in the two
Check point – Restart Logic:
COBOL-DB2 application program which deals with large volumes of data should include check point - restart logic which will minimize the impact to DB2 resources as well as the allotted batch processing window in the event of an application failure.
Check point – Restart logic in the program will basically stores the information about the last committed values so that during the application failure, you can restart the processing from the point of last committed row and not from the beginning.
Rollback
DB2 rolls back (or reverses) all of the transactions in the unit of recovery when a program terminates abnormally or when a program issues a ROLLBACK statement.
A ROLLBACK can be issued in the program as follows:
EXEC SQL
ROLLBACK END-EXEC.
Try It Out
Problem Statement:
Create a program which inserts the following row to TB_RATE table. Once the insert is successful the inserted row should be explicitly committed.
RATE_CATE RATE_PER_HOUR
01 WS-DB2-ERR-MESSAGE.
1000-EXIT.
Refer File Name: CommitRollback_Program_COMTRLBK_Session#33_Slide#7 to obtain
Summary
Commit saves the updates done during the unit of work.
Rollback reverses the updates done during the unit of work.
Test Your Understanding
1. Explain about the COMMIT statement.
2. Explain about the ROLLBACK statement.