Mainframe Forum: What is SQLCA? | SQL Communication Area | SQLCA in DB2 | DB2 SQLCA Codes

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 SQL
INCLUDE SQLCA
END-EXEC.

EXEC SQL TABLE
INCLUDE EMPLY
END-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	Contains the length of the SQLCA: 136.
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

► Follow us on Twitter

► Facebook

► Linkedin

► Reddit

► Mainframe Blog

► Medium Blog

Thank you for your support.

Mainframe Forum™