 |
| DB2 SQL Communication Area. |
Welcome back to SQLCA in DB2 session. In this article, you’ll learn what is SQLCA? You'll also learn how to use SQLCA in COBOL+DB2 applications. Additionally, you'll learn the significance of each variable that is defined in the DB2 SQLCA copybook. In the end, we’ll discuss some of the important DB2 SQL codes that you should be aware of while designing COBOL+DB2-based applications. Let's get started with today's agenda.
Agenda.
- Introduction.
- What is SQLCA?
- What are the SQLCODE and SQLSTATE fields?
- How to include SQLCA in COBOL-DB2 program?
- SQLCA fields description.
- Important DB2 SQLCODE.
- Conclusion.
Introduction.
DB2 and COBOL are two separate entities, but they work together when the business logic and data access technique are combined into a COBOL+DB2 program. A COBOL-DB2 program uses SQL statements to process data stored in DB2 tables.
A COBOL program can either use the SQL Communication area (i.e. SQLCA in DB2) or SQL diagnostics area to validate the return code (SQLCODE or SQLSTATE) of the recently executed SQL statement. The SQL return codes indicate whether the recently executed SQL statement succeeded or failed.
What is SQLCA (i.e. SQL Communication Area)?
The term SQLCA stands for SQL Communication Area. It allows DB2 to communicate with a COBOL program. Typically, COBOL-DB2 programs use SQL statements to access data from the DB2 database. When DB2 processes an embedded SQL statement, it places return codes in the SQLCODE and SQLSTATE host variables or corresponding fields of the SQL Communication Area.
Thus, the SQL Communication area or simply SQLCA in DB2 is used to inform COBOL programs about the status of recently executed SQL statements. The SQL return codes indicate whether the recently executed SQL statement succeeded or failed.
 |
| SQLCA in DB2 |
What are the SQLCODE and SQLSTATE fields?
SQLCODE and SQLSTATE are two important fields defined in the SQLCA copybook. The SQLCODE and SQLSTATE fields of the SQLCA are updated whenever an SQL statement executes. Despite the fact that both fields indicate whether the statement was successful, they differ in some respects.
DB2 returns the following codes in SQLCODE:
- If SQLCODE = 0, execution was successful.
- If SQLCODE > 0, the execution was successful with a warning.
- If SQLCODE < 0, execution was not successful.
- If SQLCODE = 100, no data was found.
In addition to 0 and 100, SQLCODEs can have different meanings depending on the product implementing SQL.
The database manager sets SQLCODE and SQLSTATE values after each SQL statement has been run. Application programs can determine whether a previous SQL statement succeeded by checking SQLCODE or SQLSTATE values.
The advantage of using the SQLCODE field is that it can provide more specific information than SQLSTATE. There are several SQLCODES in the SQLCA that have associated tokens that provide information such as which DB2 object is at fault.
One of the advantages of SQLSTATE is its cross-vendor compatibility.
How to include SQLCA in COBOL+DB2 program?
In COBOL, the DB2 SQLCA can be written directly or with the SQL INCLUDE statement. If you are coding it directly, make sure it is initialized. In order to include a standard declaration with an SQL INCLUDE statement, use the following syntax:
IDENTIFICATION DIVISION.... . .WORKING-STORAGE SECTION....EXEC SQLINCLUDE SQLCAEND-EXEC.EXEC SQL TABLEINCLUDE EMPLYEND-EXEC....
You should declare SQLCODE, SQLSTATE, and SQLCA variables in the WORKING-STORAGE SECTION and in the LINKAGE SECTION of your program. The COBOL precompiler includes the following SQLCA copybook when you use the INCLUDE statement:
DB2 SQLCA Copybook Structure.
01 SQLCA.
05 SQLCAID PIC X(8).
05 SQLCABC PIC S9(9) COMP-4.
05 SQLCODE PIC S9(9) COMP-4.
05 SQLERRM.
49 SQLERRML PIC S9(4) COMP-4.
49 SQLERRMC PIC X(70).
05 SQLERRP PIC X(8).
05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMP-4.
05 SQLWARN.
10 SQLWARN0 PIC X(1).
10 SQLWARN1 PIC X(1).
10 SQLWARN2 PIC X(1).
10 SQLWARN3 PIC X(1).
10 SQLWARN4 PIC X(1).
10 SQLWARN5 PIC X(1).
10 SQLWARN6 PIC X(1).
10 SQLWARN7 PIC X(1).
05 SQLEXT.
10 SQLWARN8 PIC X(1).
10 SQLWARN9 PIC X(1).
10 SQLWARNA PIC X(1).
10 SQLSTATE PIC X(5).
The following table explain the significance of each variable defined in COBOL SQLCA Copybook. The table also outline the datatype and brief description of each variable.
Name
|
Data Type
|
Purpose
|
SQLCAID
|
CHAR(8)
|
An "eye-catcher" for storage dumps containing the text 'SQLCA'.
|
SQLCABC
|
INTEGER
|
|
SQLCODE
|
INTEGER
|
Contains the
SQL return code: 0 (successful execution, although there might have been
warning messages); positive (successful execution, with an exception
condition); negative (error condition).
|
SQLERRML
|
SMALLINT
|
Length indicator for SQLERRMC, in
the range 0 through 70: 0 means that the value of SQLERRMC is not pertinent.
|
SQLERRMC
|
VARCHAR(70)
|
Contains one
or more tokens, separated by X'FF', that are substituted for variables in the
descriptions of error conditions.
|
SQLERRP
|
CHAR(8)
|
Provides a product signature and,
in the case of an error, diagnostic information, such as the name of the module that detected the error. In all cases, the first three characters are DSN
for DB2 for z/OS.
|
SQLERRD(1)
|
INTEGER
|
Contains an
internal error code.
|
SQLERRD(2)
|
INTEGER
|
Contains an internal error code.
|
SQLERRD(3)
|
INTEGER
|
Contains the
number of rows affected after INSERT, UPDATE, and DELETE but not rows deleted
as of a result of CASCADE delete. Set to 0 if the SQL statement fails,
indicating that all changes made in executing the statement were cancelled.
Set to 1 for a mass delete from a table in a segmented tablespace. SQLERRD(3)
can also contain the reason code of a timeout or deadlock for SQLCODES 911
and 913.
|
SQLERRD(4)
|
INTEGER
|
Generally contains a timer-on, a
short floating-point value that indicates a rough relative estimate of resources required. It does not reflect an estimate of the time required.
|
SQLERRD(5)
|
INTEGER
|
Contains the
position or column of a syntax error for a PREPARE or EXECUTE IMMEDIATE
statement.
|
SQLERRD(6)
|
INTEGER
|
Contains an internal error code.
|
SQLWARN0
|
CHAR(1)
|
Contains a W
if at least one other indicator also contains a W; otherwise, contains a
blank.
|
SQLWARN1
|
CHAR(1)
|
Contains a W if the value of a
string column was truncated when assigned to a host variable.
|
SQLWARN2
|
CHAR(1)
|
Contains a W
if null values were eliminated from the argument of a column function; not
necessarily set to W for the MIN function, because its results are not
dependent on the elimination of null values.
|
SQLWARN3
|
CHAR(1)
|
Contains a W if the number of
result columns is larger than the number of host variables. Contains a Z if
fewer locators were provided in the ASSOCIATE LOCATORS statement than the
stored procedure returned.
|
SQLWARN4
|
CHAR(1)
|
Contains a W
if a prepared UPDATE or DELETE statement does not include a WHERE clause.
|
SQLWARN5
|
CHAR(1)
|
Contains a W if the SQL statement
was not executed because it is not a valid SQL statement in DB2 for z/OS.
|
SQLWARN6
|
CHAR(1)
|
Contains a W
if the addition of a month or year duration to a DATE or TIMESTAMP value
results in an invalid day, such as June 31. This indicates that the value of the day was changed to the last day of the month to make the result valid.
|
SQLWARN7
|
CHAR(1)
|
Contains a W if one or more
nonzero digits were eliminated from the fractional part of a number used as
the operand of a decimal multiply or divide operation.
|
SQLWARN8
|
CHAR(1)
|
Contains a W
if a character that could not be converted was replaced with a substitute
character.
|
SQLWARN9
|
CHAR(1)
|
Contains a W if arithmetic
exceptions were ignored during COUNT DISTINCT processing. Contains a Z if the
stored procedure returned multiple result sets.
|
SQLWARNA
|
CHAR(1)
|
Contains a W
if at least one character field of the SQLCA or the SQLDA names or labels is
invalid because of a character-conversion error.
|
SQLSTATE
|
CHAR(5)
|
Contains a return code for the outcome of the most recent execution of an SQL statement.
|
Important SQLCODE list.
SQLCA in DB2 is a useful tool for diagnosing a problem. You must include SQLCA in COBOL-DB2 program, and displaying useful information from the SQLCA in applications. You should always consider the following SQL code when writing a COBOL+DB2 program. Depending on the return code, you know whether the statement was successfully executed.
- SQLCODE = +0 Successful.
- SQLCODE = +100 No More Rows.
- SQLCODE = -805 Package or DBRM issue.
- SQLCODE = -811 Return Multiple Rows.
- SQLCODE = -818 Timestamp Mismatch.
- SQLCODE = -904 Unavailable Resource.
- SQLCODE = -911 Deadlock or timeout.
- SQLCODE = -922 Authorization failure.
- SQLCODE = -501 DB2 Cursor not open.
- SQLCODE = -305 Missing Null indicator variable.
- SQLCODE = -805 Duplicate Index.
Note: This is not complete list of SQL Error code. We would recommend that you should go through complete list of SQL error code and based on program logic you should handle negative error code in your COBOL-DB2 Program to avoid program abend.
Youtube: What is SQLCA in DB2?
Conclusion.
Finally, this mark an end to SQLCA in DB2 tutorial. In this article, you learn what is SQL communication area and how you can use it in your COBOL-DB2 program. You also learn what are the difference between SQLCODE and SQLSTATE and what are different SQL code's that you should consider while writing COBOL+DB2 program. Do check out COBOL+DB2 Compilation Process.
►Subscribe to Topictrick & Don't forget to press THE BELL ICON to never miss any updates. Also, Please visit mention the link below to stay connected with Topictrick and the Mainframe forum on -
► Youtube
► Facebook
► Linkedin
► Reddit
Thank you for your support.
Mainframe Forum™
No comments:
Post a Comment