15.00 - Diagnostic Handling - Teradata Tools and Utilities

Teradata Preprocessor2 (SPP Prefix) Messages

Teradata Tools and Utilities
Programming Reference

Diagnostic Handling

The preprocessor returns error information to the application using multiple mechanisms.

The primary communication method for applications not using ANSI mode is via the SQLCA structure discussed in Teradata Preprocessor2 for Embedded SQL Programmer Guide.

ANSI mode applications report errors via the SQLCODE and SQLSTATE variables.

The SQLCA provides the following to the application:

  • Result reporting
  • Warning condition reporting
  • Support for DSNTIAR

The application may also retrieve the full diagnostic text by using a supplied routine, as described in the following passages.

SQLCODE Checking

Include a test of SQLCODE after each execution of an embedded SQL statement to ensure that the statement completes successfully. Also, take appropriate action to resolve unacceptable SQLCODE values. For more information on SQLCODE checking, see Teradata Preprocessor2 for Embedded SQL Programmer Guide.

Result Reporting

The results of all SQL requests sent from an application are reported in the SQLCODE field of the SQLCA structure.

WHEN SQLCODE is . . . THEN . . .
negative an error occurred during processing.
the application may determine the source of the error using the SQLCODE in conjunction with the first SQLERRD element.
SQLERRD shows the following:
- Error conditions detected by the precompiler execution environment
- Error conditions reported by CLI/TDP/Teradata Database
The first SQLERRD element is zero when the error is detected directly by the preprocessor.
These items are listed below:
- A list of error codes
- The text associated with the code
- A possible explanation
- A possible solution for these errors
When an error condition is detected by CLI, TDP, or the Teradata Database, the SQLCODE is set to the negative of the sum of the error code plus 10000.
The application can look at the SQLCODE without interrogating the first SQLERRD element.
Teradata Database error conditions are reported in the following distinct styles:
1. Teradata Database codes which have similar or equivalent DB2 values are mapped to the DB2 value in the SQLCODE field.
See the table of retryable error codes below.
2. Teradata Database codes which have no similar or equivalent code have the SQLCODE value set to the negative of the Teradata Database code.
In either case, the first SQLERRD element contains the actual value returned by the Teradata Database.
Use caution in distinguishing between a precompiler execution time error and a Teradata Database error.
Some SQLCODE values are found in both categories of error.
zero processing was successful, though there might be warnings.
positive termination was normal.
Positive values other than 0 or +100 indicate Teradata Database-issued warnings, such as the end-of-data reached for a request.

An application may obtain additional diagnostic help for a non-zero SQLCODE by invoking PPRTEXT.

PPRTEXT returns the error code (normally the same value as in the first SQLERRD element) and the textual message associated with the condition.

The following four parameters are required to execute PPRTEXT:

  • The address of the runtime context area, SQL-RDTRTCON for COBOL and SQL_RDTRTCON for C and PL/I.

  • A 4-byte integer field to contain the actual error code.

  • A varying character field up to 255 characters long to contain the error text.

  • A 2-byte integer field which contains the maximum size of the error text to be returned.

Examples of PPRTEXT usage appear in the language-dependant chapters.

Warning Condition Reporting

Warning conditions are reported to the application via the SQLWARNx fields in the SQLCA structure.

SQLWARN0 contains the value W if any warning condition is detected during execution.

The warning fields are used to signal such conditions as implicit rollbacks and data truncation.

See SQL Stored Procedures and Embedded SQL for details of the exact values and conditions of each warning field.

Error codes for retryable events also are indicated via the SQLWARNx fields.

SQLWARN6 is set to R when such an error is detected. The application may then take the appropriate action.

The following is a list of current retryable errors.

These codes are found in the first SQLERRD element of the SQLCA structure.

  • 2631
  • 2639
  • 2641
  • 2659
  • 2825
  • 2826
  • 2827
  • 2828
  • 2834
  • 2835
  • 3111
  • 3120
  • 3598
  • 3603
  • 3897

Support for DSNTIAR

Whenever possible, the SQLERRM field of the SQLCA structure will contain message inserts usable by the IBM-supplied routine DSNTIAR. Details of the SQLERRM field are found in Teradata Preprocessor2 for Embedded SQL Programmer Guide.

DSNTIAR operation is discussed in the vendor documentation.