SQL Statements › CREATE PROCEDURE › CREATE PROCEDURE Syntax and Description › Diagnostics Area

Diagnostics Area

A Diagnostics Area is a data structure that represents a list of errors, warnings, and user-defined conditions that have occurred during the execution of a single SQL statement.

SQL maintains an array of Diagnostics Areas, known as a Diagnostics Area Stack, for each logical unit of work (LUW). At any given time, the stack represents the error status of both the currently-active user request and any related condition handler executions (see following) or of the previous request if a request is not currently active.

Each Diagnostics Area contains a header containing information such as a description of the SQL statement that was executed, and an array of condition information areas (CIAs), each area representing a single error, warning, or user-defined condition that occurred during execution of the statement. The number of CIAs that are maintained in each Diagnostics Area is determined by the SQL_COND_INFO_AREAS Multi-User startup option. The amount of memory used by a Diagnostics Area can be computed as follows, where active LUWs is the number of concurrently active LUWs and CIAs is the number of CIAs as specified by SQL_COND_INFO_AREAS:

9k * (active LUWs) * (CIAs)

You can obtain an estimate of the number of concurrent SQL LUWs by querying the LUWS column in the SQL_STATUS (SQS) Dynamic System Table. Assuming you have 50 SQL applications running concurrently, and SQL_COND_INFO_AREAS is 2, then additional memory usage would then become:

9k * 50 * 2 = 900k

There are four ways to populate a Diagnostics Area. The first way is to simply experience an error or warning in an SQL statement. The second, third, and fourth ways are to execute the statements SIGNAL (see SIGNAL Statement), RESIGNAL (see RESIGNAL Statement), and RAISE ERROR (see RAISE ERROR Statement). To retrieve diagnostics information in your application, execute the GET DIAGNOSTICS statement (see GET DIAGNOSTICS Statement).