1 / 13

IBM i – SQL0901 logger

IBM i – SQL0901 logger. August 3, 2009. SQL0901 logger – the problem . SQL0901 is the message that is sent when SQLCODE = -901, indicating that an unexpected DB2 for IBM i error has been encountered. If the joblog is not saved, we ask customers to recreate the failure.

gabe
Download Presentation

IBM i – SQL0901 logger

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IBM i – SQL0901 logger August 3, 2009

  2. SQL0901 logger – the problem • SQL0901 is the message that is sent when SQLCODE = -901, indicating that an unexpected DB2 for IBM i error has been encountered. • If the joblog is not saved, we ask customers to recreate the failure. • SQL0901’s encountered by Client based applications can be hard to isolate because its hard to find the related joblog

  3. SQL0901 logger – improved failure collection • A 901 logger table has been implemented for V5R4, IBM i 6.1 and future releases. • When an SQL0901 occurs, just prior to sending the SQL0901 message, we collect details regarding the failure and insert a new row in the QRECOVERY.QSQ901S table. • A unique instance of a job will log the first three SQL0901 failures. Any subsequent 901 failures are most likely uninteresting and therefore are not logged. • The QIBM_NO_901_LOGGING environment variable can be used to disable SQL0901 logging.

  4. SQL0901 logger – enabling PTFs • IBM i 5.4 PTFs: SI35568 and SI35570The 5.4 PTFs are included with DB Group PTF SF99504 (Version #22), which became available on 8/7/2009. • IBM i 6.1 PTFs: SI35437 and SI35667 The 6.1 PTFs are included with DB Group PTF SF99601 (Version #10), which has a target availability of 8/28/2009.

  5. SQL0901 logger – table definition (V5R4) • QRECOVERY.QSQ901S columns: • SERVERNAME VARCHAR(18)The database name being used at the point of failure. • FAILTIME TIMESTAMPThe timestamp when the SQL0901 occurred. • FAILRSN INTEGERThe unique failure reason that appeared in the SQL0901 message. Note, this reason code is useful to IBM service but is not documented externally. • 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.

  6. SQL0901 logger – table definition (V5R4) • QRECOVERY.QSQ901S columns: (continued) • 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 • Note that the format of the message text makes it easy to cut and paste the point of failure detail into the System i support search facilities, making it possible for customers to find solutions to problems. • The MSGS column will most likely contain many messages. If a message was removed, it won’t appear in the MSGS column.

  7. SQL0901 logger – table definition (IBM i 6.1) • QRECOVERY.QSQ901S additional columns: • 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.

  8. SQL0901 logger – table definition (IBM i 6.1) • QRECOVERY.QSQ901S additional columns: • 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

  9. SQL0901 logger – V5R4 example • MESSAGE: MCH3601 F/QSQRUN2-MODULE/QSQFETCH-PROCEDURE/F_GETNEXTL-STMT/9550 T/QSQRUN2-MODULE/QSQFETCH-PROCEDURE/F_GETNEXTL-STMT/9550 • MESSAGE: SQL0508 F/QSQRUN3-MODULE/QSQUPDAT-PROCEDURE/CLEANUP-STMT/25204 T/QSQRUN3-MODULE/QSQUPDAT-PROCEDURE/CLEANUP-STMT/25204 • MESSAGE: CPF501B F/QDBSIGEX-INS/01EA T/QSQRUN3-MODULE/QSQUPDAT-PROCEDURE/SQL_Update-STMT/12590 • MESSAGE: CPI434B F/QQQQUERY-MODULE/QQQQUERY-PROCEDURE/CLEANUP-STMT/27382 T/QSQRUN3-MODULE/QSQOPEN-PROCEDURE/FULL_OPEN-STMT/23178 • MESSAGE: CPF9898 F/QQQOOODBOP-MODULE/QQQOOOINV-PROCEDURE/CALLDBMAINTFOROPENOROPTIMIZE-STMT/4125 T/QQQOOODBOP-MODULE/QQQOOOINV-PROCEDURE/QDBOPINVOKE-STMT/3993 • MESSAGE: CPI434A F/QQQQUERY-MODULE/QQQQUERY-PROCEDURE/QQQQUERY-STMT/14253 T/QSQRUN3-MODULE/QSQOPEN-PROCEDURE/FULL_OPEN-STMT/23178 • MESSAGE: CPI434B F/QQQQUERY-MODULE/QQQQUERY-PROCEDURE/CLEANUP-STMT/27382 T/QSQRUN3-MODULE/QSQOPEN-PROCEDURE/FULL_OPEN-STMT/23178 • MESSAGE: CPF9898 F/QQQOOODBOP-MODULE/QQQOOOINV-PROCEDURE/CALLDBMAINTFOROPENOROPTIMIZE-STMT/4125 T/QQQOOODBOP-MODULE/QQQOOOINV-PROCEDURE/QDBOPINVOKE-STMT/3993 • MESSAGE: CPI434A F/QQQQUERY-MODULE/QQQQUERY-PROCEDURE/QQQQUERY-STMT/14253 T/QSQRUN3-MODULE/QSQOPEN-PROCEDURE/FULL_OPEN-STMT/23178 • MESSAGE: CPI434B F/QQQQUERY-MODULE/QQQQUERY-PROCEDURE/CLEANUP-STMT/27382 T/QSQRUN3-MODULE/QSQOPEN-PROCEDURE/FULL_OPEN-STMT/23178 • MESSAGE: CPF9898 F/QQQOOODBOP-MODULE/QQQOOOINV-PROCEDURE/CALLDBMAINTFOROPENOROPTIMIZE-STMT/4125 T/QQQOOODBOP-MODULE/QQQOOOINV-PROCEDURE/QDBOPINVOKE-STMT/3993 • MESSAGE: CPI434A F/QQQQUERY-MODULE/QQQQUERY-PROCEDURE/QQQQUERY-STMT/14253 T/QSQRUN3-MODULE/QSQOPEN-PROCEDURE/FULL_OPEN-STMT/23178

  10. SQL0901 logger – IBM i 6.1 example SQL0901’s hit when using Interactive SQL (STRSQL) or Run SQL Statement (RUNSQLSTM) commands will notice that the Client Special register values contain default values.

  11. SQL0901 logger – Database Group PTF Use the WRKGRPPTF command to see the Database Group PTF level. IBM i 6.1 - WRKPTFGRP PTFGRP(SF99601) IBM i 5.4 - WRKPTFGRP PTFGRP(SF99504)

  12. SQL0901 logger – FAQ How is the QRECOVERY.QSQ901S table managed?Users can delete the table or specific rows in the table as they wish. The table is not protected like a catalog. When a -901 occurs, we will recreate the table, if needed. How should the table be used?SQL0901’s are an indication of a DB2 for IBM i internal failure. You may observe logging of internal failures that are not causing significant problems or pain to the users. The logger is similar to First Failure Data Capture (FFDC) and STRDST VLOG’s, in that you may observe failures being logged that are less important. The high value use case for the SQL0901 logger is when a severe error is observed, the failure symptom includes an SQL0901 and the joblog can’t be found. How can the customer collect this data and send it to IBM? Do the following:CRTSAVF QGPL/LOG901SAVOBJ OBJ(QSQ901S) LIB(QRECOVERY) DEV(*SAVF) SAVF(QGPL/LOG901)

  13. Questions or ideas for improvement? Contact Scott Forstie (forstie@us.ibm.com)

More Related