IBM Support

SQL Logger Enhancement

Troubleshooting


Problem

This document describes the SQL Logger enhancement which can assist problem determination.

Diagnosing The Problem

About Message SQL0901

The message SQL0901 normally appears in the joblog when an SQLCODE -901 is issued. The error indicates that an unexpected DB2 for IBM i error was encountered. This message is generic and normally does not contain the necessary details to find the root cause of the problem. A joblog is usually needed in order to identify the source of the problem. In some cases such as prestart jobs (QZDASOINIT, QRWTSRVR, and QSQSRVR), the joblog is not available after the job ends. Additionally, it is sometimes difficult to locate the joblog for the job that issues a -901 SQL code. This requires additional steps to be performed in order to obtain the joblog and identify the message identifier that is descriptive of the problem.

Resolving The Problem


The SQL Logger Table

When an SQL0901 error occurs, prior to sending the message to the user, we collect details about the failures and update the QSQ901S table which is located in the QRECOVERY library.

QSQ901S Table Definition

The QSQ901S table contains the following columns for r540:

§ SERVERNAME VARCHAR(18)
The database name being used at the point of failure.
§ FAILTIME TIMESTAMP
The timestamp when the SQL0901 occurred.
§ FAILRSN INTEGER
The unique failure reason that appeared in the SQL0901 message.
§ CURUSER VARCHAR(18)
The user name of the user who encountered the SQL0901.
§ JOBNAME CHAR(28)
The qualified job name of the job that encountered the SQL0901.
§ MSGS VARCHAR(3000)
The messages, in order from most recent to least recent, which precede the SQL0901 message. Each message is prefixed with the text ‘MESSAGE: ‘.
Example message text: MESSAGE: MCH3601 F/QSQRUN2-MODULE/QSQFETCH-PROCEDURE/F_GETNEXTL-STMT/9550 T/QSQRUN2-MODULE/QSQFETCH-PROCEDURE/F_GETNEXTL-STMT/9550

QSQ901S Additional Columns Available for r610:

§ VRM CHAR(10)
The version, release and modification level at the point of failure.
For example: ‘V6R1M0 ’
§ DBGROUP CHAR(10)
The Database Group PTF level at the point of failure.
For example: ‘SF99601 04’
§ CLIENTUSER VARCHAR(255)
The value contained within the CURRENT CLIENT_USERID special register at the point of failure.
§ CLIENTAPP VARCHAR(255)
The value contained within the CURRENT CLIENT_APPLNAME special register at the point of failure.
§ CLIENTPGM VARCHAR(255)
The value contained within the CURRENT CLIENT_PROGRAMID special register at the point of failure.
§ CLIENTWRK VARCHAR(255)
The value contained within the CURRENT CLIENT_WRKSTNNAME special register at the point of failure.
§ CLIENTACCT VARCHAR(255)
The value contained within the CURRENT CLIENT_ACCTNG special register at the point of failure.
§ CONNUSER VARCHAR(128)
The value contained within the SYSTEM_USER special register at the point of failure.
§ The SQL Reference explains how the Client Special registers can be set:

List current available fields with :

DSPFFD FILE(QRECOVERY/QSQ901S)


Permissible QSQ901S Operations

Because the object QSQ901S is an SQL table, users can manipulate the data. Select, insert, delete, and update statements are allowed against the table. Furthermore, the file can be saved, cleared, deleted, and so on. There is currently no automatic clearing or resetting of the records in the table. The table is automatically re-created if it i deleted by a user.

SQL Script to View Most Recent 100 Records in the QSQ901S File

If the SQS901S table is large and you want to retrieve the last 100 records into another file, you can execute the SQL script below:

CL: CRTSAVF QGPL/LOG901;
Create table qgpl.recent901s as (select * from qrecovery.qsq901s order by failtime desc fetch first 100 rows only) with data;
CL: SAVOBJ OBJ(RECENT901S) LIB(QGPL) DEV(*SAVF) SAVF(QGPL/LOG901);

SQL statement to return entries from a specific date:

SELECT * FROM qrecovery.qsq901s WHERE DATE(FAILTIME) = '2021-02-21' 

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"DB2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

533964248

Document Information

Modified date:
06 January 2022

UID

nas8N1012785