Problem description (Cause)

The problem arise from an IBM change on a PTF that changes a value SQLMONITOR uses to filter SQI messages. It only applied to V7R1 or V6R1 and iSeries Modules v6.08 (will be fixed on future releases). Until then, please verify the Symptoms are exactly the same and then apply the FIX to solve it.

Context information

IBM confirmed that a change was done to iSeries Database Monitoring changing Column QQC21 Value 'SI' to 'OP' for a all SELECT operations but they were not able to say on which PTF the change was introduced.

Symptoms

Only CONNECT Messages are written to SQI table B_DETECTOR/SILOG01X

Verify doing:

  • STRSQL
  • SELECT * FROM B_DETECTOR/SILOG01X ORDER BY init_tms desc 

Actions to fully verify the Problem

  • Check keys are correctly inserted and it is not a Key Problem
    No KEY errors shown on DSPMSG QSYSOPR for SQLMONITOR
  • KRNSUPERV and SQLMONITOR are running on T4NICELINK SBS
    run: WRKACTJOB SBS(T4NICELINK)
  • SQI is enabled
    verify with DSPDTAARA DTAARA(B_DETECTOR/T4LOG) OUTFMT(*HEX)
    Should return 'Y'
  • The user login SQI is on a Subsystem that is not filtered out (ex: QINTER)  and have signed on after starting KRNSUPERV.
    check subsystem filtering doing: GO T4NICELINK/T4NICELINK -> Opt 6 -> Opt 60 -> Opt 1 (Manteinance)
  • iSeries DBMON Trace is working but Changed by IBM:
    STRSQL and then:
       SELECT QQTIME, QQRID, QQC12, QQC21, QQC11, QQ1000 FROM b_detector/sldbm01x order by QQTIME desc
      (run it twice so that the command appears on the query result)
      Column QQC21 displaying 'OP' for a QQ1000 Select... (used to be 'SI'. changed by IBM)
  • SQI Messages are filtered incorrectly to SQL Table:
    check for SELECT queries on SLLOG01X by doing:
    SELECT * FROM B_DETECTOR/SLLOG01X ORDER BY init_tms desc
    (Use PF20 to move right)

Solution

Apply Fix by doing:

  1. Upload SLKRN609.SAVF and SLDDS.SAVF to the iSeries (ftp usage explained in http://kb.tango04.com/content/280)
    (Zip files attached at the bottom of this entry)
  2. Stop SQLMONITOR and KRNSUPERV
    B_DETECTOR/ENDSQLMON
    B_DETECTOR/ENDKRNSUP
  3. Rename files to be modified:
    B_DETECTOR/SLDBM02LB to SLDBM02LB_
    B_DETECTOR/SLKRN01CB to SLDKRN01CB_
    B_DETECTOR/SLDDS to SLDDS_
  4. Restore objects:
    RSTOBJ OBJ(SLKRN609) SAVLIB(B_DETECTOR) DEV(*SAVF) OBJTYPE(*ALL) SAVF(WORKLIB/SLKRN609) RSTLIB(B_DETECTOR)
    RSTOBJ OBJ(SLDDS) SAVLIB(B_DETECTOR) DEV(*SAVF) OBJTYPE(*ALL) SAVF(WORKLIB/SLDDS) RSTLIB(B_DETECTOR)
  5. Start SQLMONITOR and KRNSUPERV
    B_DETECTOR/STRKRNSUP
    B_DETECTOR/STRSQLMON
  6. Signoff and Sign back on to start logging SQI
  7. Test the solution by performing:
    STRSQL
    SELECT * FROM B_DETECTOR/SILOG01X ORDER BY init_tms desc
    you should see SELECT queries after several attempts (it may take 15 seconds to refresh)
     

Rollback instructions

To roll back if the FIX doesn't work or causes another problem is to simply rename back the objects renamed on step 3.


Additional information

Additional information

Related information to QQC21 Values on iSeries Database Monitoring:
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzajq/t1000.htm

Case related: 2-11991469 (internal information)

 


Attachments

Files needed to apply the FIX:

  • SLKRN609_2.zip with SLKRN01CB.SAVf
  • SLDDS.zip with SLDDS.SAVF


 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: October 22, 2018