A Followup on Snapshot Scheduling

 

A colleague recently pointed out that while my Snapshot Scheduling blog entry was on target, it was missing a few details for a portion of the IBM i install base. Specifically, those IBM i clients that are using Independent Disk Pools  - also known as Independent Auxiliary Storage Pools (ASPs) or IASPs for short. IASP usage is on the rise by IBM i customers since they provide the foundation for IBM PowerHA.

In the prior blog entry, I advocated the use of the DUMP_PLAN_CACHE service for creating Plan Cache snapshots on your production system on a regular basis. The detail that I did not include regarding this service and IASPs is this - by default, the DUMP_PLAN_CACHE service only copies Plan Cache entries for those queries referencing tables in the System ASP.  That means if you had a critical query like the following and DBLIB resided in an IASP, then the Plan Cache snapshot would contain no record of this query. 

SELECT COUNT(*) FROM dblib.orders WHERE custid = 123

The snapshot would only contain queries that reference objects that reside in libraries and schemas that are housed in the System ASP. This default behavior of the DUMP_PLAN_CACHE service exactly matches the default behavior when you view the live Plan Cache entries with the IBM i Access Client Solutions (ACS) SQL Performance Center. 

The SQL Performance Center Database pull-down menu highlighted in the following figure defaults to the System ASP. Thus, clicking on the Show Statements button to analyze Plan Cache entries will result in a list of those queries that only reference objects that reside in libraries that are stored in the System ASP. If you want to view Plan Cache entries for queries referencing tables that reside in an IASP, then you’ll first need to select the IASP name from the highlighted Database pull-down menu.

On a side note, the Plan Cache Properties shown in this figure are summary metrics that include all queries regardless of which ASP the queried objects resides in.

Obviously, there’s no graphical interface for the DUMP_PLAN_CACHE service, so you’re probably wondering how that service can be used to capture all of the entries in the Plan Cache. One way to ensure that all Plan Cache entries are written to the snapshot is to use the Set ASP Group (SETASPGRP) CL command featured in the following CL script. 

RUNSQL SQL('CALL QSYS2.DUMP_PLAN_CACHE(''SOMELIB'',''SNAP1'')')

SETASPGRP ASPGRP(IASPNN)

RUNSQL SQL('CALL QSYS2.DUMP_PLAN_CACHE (''SOMELIB'',''SNAP1'')')

The first call to the DUMP_PLAN_CACHE service will dump the plan cache entries related to System ASP queries. After that dump has completed, the SETASPGRP command is run to reset the job to the independent ASP named IASPNN.  This ASP group reset causes the second DUMP_PLAN_CACHE service invocation to dump the plan cache entries related to the specified IASP. 

Take note in this CL script that the second DUMP_PLAN_CACHE request also uses the same snapshot name to ensure that all of the plan cache entries end up in a single Plan Cache Snapshot object. 

Now, you have all the details needed to guarantee that even environments utilizing IASPs can have scheduled Plan Cache snapshots that capture ALL of your queries.