IBM Support

New SQL Logger Enhancement

Troubleshooting


Problem

This document describes a new SQL Logger enhancement that was added to the operating system to assist with debugging problems.

Resolving 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.

The SQL Logger Table

A new logger table has been implemented for v5r4, v6r1, and future releases. When an SQL0901 error occurs, prior to sending the message to the user, we collect details about the failures and sent them into 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:

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/rzahg/icmain.htm

How to Enable the SQL0901 Logger

The following PTFs are required to enable the function:

IBM i 5.4 PTFs: DB Group PTF SF99504 (Version #22 available as of 8/6).
IBM i 6.1 PTFs: DB Group PTF SF99601 (Version #10 available as of 8/28).

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);

[{"Product":{"code":"SGYQGH","label":"IBM i"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":"DB2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.2.0;7.1.0;6.1.0;5.4.5;5.4.0","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSC5L9","label":"IBM i 7.2"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSC52E","label":"IBM i 7.1"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSC3X7","label":"IBM i 6.1"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]

Historical Number

533964248

Document Information

Modified date:
17 June 2018

UID

nas8N1012785