SQL/MP Programming Manual for COBOL

Error and Status Reporting
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
9-18
Returning Information From the SQLCA
Suppose that TABLEX and its index XTABLX are created:
CREATE TABLE TABLEX (
ERRDATE NUMERIC (6) NO DEFAULT ,
ERRTIME NUMERIC (6) NO DEFAULT ,
TERMID NUMERIC (6) NO DEFAULT ,
SQLCODE NUMERIC (4) NO DEFAULT ,
FSCODE NUMERIC (4) SYSTEM DEFAULT ,
TEXT1 CHARACTER (240) SYSTEM DEFAULT ,
TEXT2 CHARACTER (240) SYSTEM DEFAULT ,
TEXT3 CHARACTER (240) SYSTEM DEFAULT ,
TEXT4 CHARACTER (240) SYSTEM DEFAULT ,
PRIMARY KEY ( ERRDATE ASC, ERRTIME ASC ) )
NO AUDIT
SECURE "NNNN"
CREATE INDEX XTABLX ON TABLEX (
TERMID,
SQLCODE,
FSCODE )
The timestamp (ERRDATE, ERRTIME) is the primary key of the table. The terminal ID,
the SQL code, and any file system code are the composite alternate index for the
table. For each error, you write one row to the table. To collect the information for this
table, the program can perform these steps:
1. Retrieve the timestamp, terminal ID, and SQLCODE for the first four columns.
2. Call SQLCAFSCODE to retrieve FSCODE (or zero) for the fifth column.
3. Call SQLCA_TOBUFFER2_ to retrieve the error message text for the last four
columns.
If you send error information to an SQL table, you might consider setting up a help
desk to provide end users with more information about errors returned by the
application. (A help desk is a person or group of people responsible for helping end
users with their problems.) When individual users require more explicit information
about errors, the help desk staff can query the error table with SQLCI.
A query from TABLEX could specify the user terminal ID and the date and approximate
time of the error (for example, between 2:00 p.m. and 3:00 p.m. June 8, 1996) as
shown:
SELECT * FROM TABLEX
WHERE ERRDATE = "960608"
AND TERMID = "012300"
AND ERRTIME BETWEEN "140000" AND "150000"
Other queries could look for specific SQL errors (such as -8300) using the SQLCODE
column, or specific file-system errors (such as 11) using the FSCODE column.
On a periodic basis, you can archive the SQL error table with the COPY or DUP
utilities available through SQLCI, and you can clear the current table with the
PURGEDATA utility. In this way, you can restrict the table to contain errors for a
specific period of time, such as one week.