If you have installed PTFs recently, you may have noticed that transactions DBACockpit or ST04 are showing no SQL statements at all, or that they are showing zero for the number of rows returned in all statements. When trying to force a new plan cache dump, you may have seen an error message SQL0407 ("Null values not allowed in column or variable QQARSS") in the work process.
With DB2 for IBM i, you have two options for database monitoring: You can use the classic ABAP performance collector, which was shipped with ABAP or dual-stack systems only, or you can use the central SAP Database Performance Collector for IBM i, which is shipped with SAPHOSTAGENT, release 7.20 patch level 75 or later and needs to be activated through an entry in file /usr/sap/sapservices (see SAP Note 1622665).
Parameter as4/dbmon/central_collector is used to decide whether the classic ABAP performance collector or the central database performance collector for IBM i is used by transaction DBACockpit. Valid values are:
0 or OFF = Use the classic ABAP performance collector
1 or ON = Use the central SAP Database Performance Collector for IBM i
The default value is 0. The value can be changed dynamically, but if it is changed to 1 or ON, regularly scheduled batch jobs for setting 0 or OFF will no longer be executed. If you switch the parameter back to 0 or OFF at a later time, no performance data will be available for the time while the parameter was set to 1 or ON.
To fix the problem with the missing or incorrect data, you need to perform the following activities:
- If you use as4/dbmon/central_collector=0, you need to apply the PTFs associated for your IBM i release level - SI60610 and SI60970 for IBM i 7.1, SI60421 and SI60424 for IBM i 7.2 or SI60611 and SI60964 for IBM i 7.3. If your SAP system is installed on an independent ASP (IASP), you need to vary off and on the IASP in order to activate the changes for the IASP. Alternatively you can execute the following commands:
SETASPGRP ASPGRP(<IASP>)
CALL PGM(QSYS/QSQSYSIBM) - If you use as4/dbmon/central_collector=1, you need to apply the correction instructions that are assigned to SAP Note 2321172.
- If using Visual Explain functionality through the SAP interfaces, you need to apply SAPHOSTAGENT release 7.21 patch level 16 or higher (see SAP Note 2315775) to get the correct number of rows returned for the individual statement that you analyze.
