How to control the SAP retention period of the statistical data of the SQE plan cache for DB2 on IBM i

[former_member270360]

Dears,

Please consider the question in the .txt attached file, as I am encountering a problem that prevents me from editing the question directly into the editor.

Thanks.

Reda

SCN forum thread - How to control the SAP retention period of the statistical data of the SQE plan cache for DB2 on IBM i.txt.zip

[christian_bartels · Accepted solution]

Okay, the question was:

Dears,

We are implementing SAP NW 7.3 AS ABAP on IBM Power System i – IBM i V7R1

The following are the values used for some relevant instance profile parameters :

as4/dbmon/enable = 1

as4/dbmon/classic = 0

as4/dbmon/central_collector = 0

Could someone please guide on how to control the retention period of the statistical data of the SQE plan cache for DB2 on IBM i , which is copied to SAP system tables by SAP system jobs.

The following are SAP links, together with their relevant excerpts :

http://help.sap.com/saphelp_snc70/helpdata/en/0d/ba08f8af7b11d28e1e0000e816c2ff/content.htm

‘The statistics are not deleted automatically. However, you can delete weekly data from the display (second screen). To schedule automatic deletion of the statistics, configure table SAPWLREORG, setting ID to D4 for DB2 UDB for iSeries.’

http://www.stechno.net/sap-programs.html?view=sapprog&id=RSDB4UPD

‘You can delete old data online using transaction ST04, if you have authorization.  Alternatively, you can do this in the background by submitting RSDB4UPD using variant SAP&_RSDB4UPD1 (deletes all data older than a week).’

It is really not so clear how exactly is the deletion controlled.

Thank you in advance for your cooperation.

Reda

-------------------------

Hi Reda,

if you use the traditional monitoring (as4/dbmon/central_collector = 0), the SQE plan cache data is kept from one IPL to another, i.e. after each IPL the plan cache data is cleared. The statistical data may hang around for longer periods of time, but this is probably not relevant at all. The second link that you provided is definitely outdated information (was only valid for as4/dbmon/classic = 0, but that setting is not supported with IBM i 7.1).

If you would like to keep historical data from the plan cache, you should consider using the new database performance collector for IBM i (see SAP Notes 1622665 and 1634283) as well as the new DBACockpit that was shipped with 7.02 SP15, 7.30 SP11, 7.31 SP11 or 7.40 SP06.

Kind regards,

Christian Bartels.

[former_member270360 · Accepted solution]

Hello Christian,

Please consider my reply in the attached file.

Thanks.

Reda

SCN forum thread - How to control the SAP retention period of the statistical data of the SQE plan cache for DB2 on IBM i (2).txt.zip

[christian_bartels · Accepted solution]

Again, here is the question in plain text:

Hello Christian,

First of all thank you for your reply, and sorry for being late in replying.

We intend to switch to the new database performance collector for IBM i at the appropriate time.

Let me clarify the current situation more :

I’ve guided our ABAPers to use the System i Navigator of the  IBM System i Access for Windows, option : ‘Databases’, to exploit the ‘Run SQL Scripts’, ‘Visual Explain’ & ‘Index Advisor’ features,  and all these features use the statistical data existing in the SQE plan cache, which is cleared with every IPL.

Also, our ABAPers will be using SAP transaction DB4COCKPIT, to exploit its different features, and also the capability to export to Excel the list of advised indexes,…etc.

DB4COCKPIT uses the SAP database performance tables, which are filled by SAP system jobs, which copy the memory-resident statistical data of the DB2 for IBM i SQE plan cache to those tables.

(Program SAPMSSY6 runs periodically (the period is controlled by profile parameter : rdisp/autoabaptime) , it submits program RSDB4DMP, which calls periodically program RSDB4UPD (the period is controlled by : DB4COCKPIT , Performance , Database Monitor Configuration), and RSDB4UPD – which runs by default every 10 hours – conducts the necessary update / merge of the SAP database performance tables – Please do correct me if this summary is inaccurate.)

The problem now is with the inconsistency between the statistical data of the SQE plan cache (through the IBM i Navigator) and that which is copied to the SAP database performance tables : already a delay of 10 hours exists (I wouldn’t decrease that time interval, as program RSDB4UPD already takes a long running time), and also if we conduct an IPL, the residing data presented through DB4COCKPIT would be more or less misleading.

My goal is to maintain the consistency between the two sides, as much as possible.

If I understood you correctly, I guess I should be cleaning up the SAP database performance data manually (by calling the relevant CL program in note 727078 – iSeries: Cleanup of the database monitor data)  after each IPL, and just tolerate the 10 hours delay of the scheduling of RSDB4UPD.

Thanks.

Reda

-----------------

Hi Reda,

I guess it is really difficult to compare values from the plan cache seen through the System i Navigator to values seen through DBACockpit. Besides the 10 hours delay that you pointed out, there are also other differences. First of all, the plan cache view of the Navigator is looking at all SQL statements in the system, while the DBACockpit limits itself to statements that belong to the selected SAP system. If you only have one SAP system in your partition, that may not make a difference, but as soon as you have more than one SAP system, you cannot get the same results. In addition, the analysis function of the System i Navigator is looking at the current content of the plan cache ("old" statements get pruned as new statements are added), while the DBACockpit is keeping an merging all statements since the last IPL. Finally, as you pointed out, the data in DBACockpit can be up to 10 hours old (see the "Last Analysis" timestamp in the DBACockpit screen Performance -> Overview).

You do not need to cleanup the DBACockpit data after an IPL. When the first data is collected after the IPL, all data from before the IPL is cleared (or should be cleared) by the code that creates the new plan cache dump. We have to do this, because the SQL statements in the dump output contain counters that are not unique across IPLs. If you want to clear the table contents later, you can follow SAP Note 727078 partially (it appears to be a bit outdated): It is no longer necessary to run the CL program listed in the SAP Note if you run on IBM i 6.1 or later, or if you run with as4/dbmon/classic=0.

Kind regards,
Christian Bartels.

[former_member270360 · Accepted solution]

Hello Christian,

Thank you very much for your cooperation.

Best regards.

Reda