This month I begin a two-part topic because it is just too large to do in one blog entry!
In the Beginning
The DSN_PROFILE_TABLE was introduced sometime in DB2 V8, but it was not until DB2 9 that it started to be used for system profiling when IBM introduced three new commands: DISPLAY PROFILE, START PROFILE and STOP PROFILE. This first appearance of PROFILES was a bit limited and could control only a few ZPARMs – and four of those just for EXPLAIN purposes.
How does/did it look?
To get it working, you must first create all the required tables and indexes. (The DDL is in the db2hlq.SDSNSAMP member DSNTIJSG.) In bold and italics are the DB2 10 and higher versions:
SYSIBM.DSN_PROFILE_TABLE
CREATE TABLE SYSIBM.DSN_PROFILE_TABLE
( "AUTHID" VARCHAR(128)
,"PLANNAME" VARCHAR(24)
,"COLLID" VARCHAR(128)
,"PKGNAME" VARCHAR(128)
,"LOCATION" "IPADDR" VARCHAR(254)
,"PROFILEID" INTEGER NOT NULL
PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
,"PROFILE_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT
,"PROFILE_ENABLED" CHAR(1) NOT NULL DEFAULT 'Y'
,"GROUP_MEMBER" VARCHAR(24)
,"REMARKS" VARCHAR(762)
,"ROLE" VARCHAR(128)
,"PRDID" CHAR(8)
,"CLIENT_APPLNAME" VARCHAR(255)
,"CLIENT_USERID" VARCHAR(255)
,"CLIENT_WRKSTNNAME" VARCHAR(255)
);
CREATE UNIQUE INDEX SYSIBM.DSN_PROFILE_TABLE_IX_ALL
ON SYSIBM.DSN_PROFILE_TABLE
( "PROFILEID"
);
CREATE INDEX SYSIBM.DSN_PROFILE_TABLE_IX2_ALL
ON SYSIBM.DSN_PROFILE_TABLE
( "PROFILE_ENABLED"
,"AUTHID"
,"PLANNAME"
,"COLLID"
,"PKGNAME"
,"LOCATION" "IPADDR"
,"PRDID"
,"ROLE"
,"CLIENT_APPLNAME"
,"CLIENT_USERID"
,"CLIENT_WRKSTNNAME"
,"GROUP_MEMBER"
,"PROFILE_TIMESTAMP" DESC
);
SYSIBM.DSN_PROFILE_HISTORY – Same columns as DSN_PROFILE_TABLE apart from
REMARKS -> STATUS VARCHAR(254) and no index.
SYSIBM.DSN_PROFILE_ATTRIBUTES
CREATE TABLE SYSIBM.DSN_PROFILE_ATTRIBUTES
( "PROFILEID" INTEGER NOT NULL
REFERENCES SYSIBM.DSN_PROFILE_TABLE ON DELETE CASCADE
,"KEYWORDS" VARCHAR(128) NOT NULL
,"ATTRIBUTE1" VARCHAR(1024)
,"ATTRIBUTE2" INTEGER
,"ATTRIBUTE3" FLOAT
,"ATTRIBUTE_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT
,"REMARKS" VARCHAR(762)
);
CREATE UNIQUE INDEX SYSIBM.DSN_PROFILE_ATTRIBUTES_IX_ALL
ON SYSIBM.DSN_PROFILE_ATTRIBUTES
( "PROFILEID"
,"ATTRIBUTE_TIMESTAMP" DESC
,"KEYWORDS"
,"ATTRIBUTE1"
,"ATTRIBUTE2"
,"ATTRIBUTE3"
);
SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY same columns as DSN_PROFILE_ATTRIBUTES apart from
REMARKS -> STATUS VARCHAR(254) and no index.
Notice the RI between the DSN_PROFILE_ATTRIBUTES and DSN_PROFILE_TABLE keyed on PROFILEID. Also notice that there are no indexes on the HISTORY tables and also no RI.
So What Could You Do?
With this new functionality you could use a profile to override four ZPARMs, namely NPGTHRSH, OPTIOWGT, STARJOIN and SJTABLES. To do so, you first inserted a row into the DSN_PROFILE_TABLE with some sort of filter, at this time only COLLID and PKGNAME, and then one or more inserts in the DSN_PROFILE_ATTRIBUTES table using the PROFILEID that you either just used, or got generated for you, in the DSN_PROFILE_TABLE using the KEYWORDS column and ATTRIBUTEn column(s).
Always on?
The column PROFILE_ENABLED in the DSN_PROFILE_TABLE informs Db2 whether or not to consider this profile when the START PROFILE command is issued. Setting it to N puts all of this profile’s records “to sleep”.
Not Just ZPARMs
It also enabled three global changes (no filters allowed) for BPname, MAX_RIDBLOCKS and SORT_POOL_SIZE. All of these are just for modelling production systems in test to then get a better, more accurate, EXPLAIN result and have *no* effect on the actual system at all.
Finally, IBM added some Accelerator-only support which had to be done with IBM involved.
Interestingly enough, there was a complete chapter about using profiles to monitor and report on SQL but there was also an update to the docu:
Important: The use of profile tables to monitor and capture information about the performance of SQL statements is deprecated, and not recommended.
So, I will not even bother going into detail about the monitor settings.
What Was the Difference?
The major difference between the SQL and ZPARM settings, was the ability to use different filter column values like AUTHID or IPADDR/LOCATION.
The DSN_PROFILE_HISTORY has the same columns as the DSN_PROFILE_TABLE, except that REMARKS is called STATUS and gets a value set by the START PROFILE command. Basically, a string that starts with REJECTED – or ACCEPTED – and then a text string describing why the profile was, or was not, accepted for use.
What’s in an ATTRIBUTE?
The DSN_PROFILE_ATTRIBUTES table contains the option that should be overridden when the Profile is active and the filtering allows it. The columns of interest are KEYWORDS and the three ATTRIBUTEn columns.
BUFFERPOOL Modelling
BPname (where name is any of the valid names like 0 through 49 or 32K1 through 32K9 etc.) in the KEYWORDS column. ATTRIBUTE1 and ATRIBUTE3 are set to NULL and ATTRIBUTE2 contains a positive integer value for the size of the BUFFERPOOL (for production modelling).
RIDPOOL Modelling
MAX_RIDBLOCKS in the KEYWORDS column, ATTRIBUTE1 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE2 contains a value from 0 to the maximum value that you can set MAXRBLK in that subsystem (for production modelling).
STARJOIN Control
STAR JOIN in the KEYWORDS column, ATTRIBUTE2 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE1 set to DISABLE or ENABLE.
MIN STAR JOIN TABLES in the KEYWORDS column, ATTRIBUTE1 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE2 contains a value from 3 to 225.
INDEX ACCESS Control
NPAGES THRESHOLD in the KEYWORDS column, ATTRIBUTE1 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE2 contains one of the following values:
-1 use index access if possible
0 access path based on cost, the normal way Db2 works
1 to nnnn Db2 should use index access on tables for which the total number of pages (NPAGES) is less than nnnn. Make sure that your Db2 Catalog statistics are up to date before you specify a value of 1 or greater.
IO Control
IO WEIGHTING in the KEYWORDS column, ATTRIBUTE2 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE1 is set to DISABLE or ENABLE (deprecated in DB2 10).
SRTPOOL Modelling
SORT_POOL_SIZE in the KEYWORDS column, ATTRIBUTE1 and ATTRIBUTE3 are set to NULL, and ATTRIBUTE2 set to a positive integer up to the maximum value of SRTPOOL. That is the new SRTPOOL (for production modelling).
Production Modelling
In this case, the EXPLAIN output got changed to output which PROFILE value was active at the time of the EXPLAIN. The REASON column in the DSN_STATEMNT_TABLE gets set to “PROFILEID nnnn” for the profile number that was active at the time of the EXPLAIN.
When Was this Done?
The DSN_PROFILE_ATTRIBUTES_HISTORY has the same columns as the DSN_PROFILE_ATTRIBUTES_TABLE except that REMARKS is called STATUS and gets a value set by the START PROFILE command. Basically, a string that starts with REJECTED – or ACCEPTED – and then a text string describing why the profile was, or was not, accepted for use.
So that was it for DB2 9 – not that much but a very good start if you ask me!
System Profile Monitoring
Then in DB2 10 came “system profile monitoring”, which is where this system got very useful indeed! It then got the ability to Monitor Connections, Monitor Threads and Monitor Idle Threads.
New Keywords for Connections and Threads!
MONITOR CONNECTIONS in the KEYWORDS column, ATTRIBUTE1 is a “two part” column value. The first part is either WARNING or EXCEPTION. A warning causes a console message every five minutes depending on the diagnosis level. An exception issues the diagnosis level and rejects any new incoming connection requests. The second part is either not there or it is _DIAGLEVEL1 which issues a DSNT771I console message and _DIAGLEVEL2 which issues a DSNT772I console message with more details. ATTRIBUTE2 is a positive integer to indicate the threshold for the maximum number of remote connections. It must be less than or equal to CONDBAT. ATTRIBUTE3 is NULL. Filtering is only by the LOCATION column.
MONITOR THREADS in the KEYWORDS column, ATTRIBUTE1 is a “two part” column value. The first part is either WARNING or EXCEPTION. A warning causes a console message every five minutes depending on the diagnosis level. An exception issues the diagnosis level and can cancel the thread depending on the filtering criteria otherwise the thread is queued. The second part is either not there or it is _DIAGLEVEL1 which issues a DSNT771I console message and _DIAGLEVEL2 which issues a DSNT772I console message with more details. ATTRIBUTE2 is a positive integer to indicate the threshold for the maximum number of server threads. It must be less than or equal to MAXDBAT. ATTRIBUTE3 is NULL. Filtering on nearly all columns is allowed.
Db2 11 Docu Update
In Db2 11, an extra bit of documentation was added when filtering by Collection identifier, package name, client user name, client application name or client workstation name. When the total number of queued and suspended threads exceeds the threshold, Db2 fails subsequent SQL statements and returns SQLCODE -30041 to the client.
For example, suppose that a profile for a package is started. That profile uses ATTRIBUTE2=2. If five threads request to run the package, two threads run concurrently, two threads are queued and suspended, and Db2 fails the SQL statements for the fifth thread.
And Finally IDLE?
MONITOR IDLE THREADS in the KEYWORDS column, ATTRIBUTE1 is a “two part” column value. The first part is either WARNING or EXCEPTION. A warning causes a console message every five minutes, depending on the diagnosis level. An exception issues the diagnosis level and cancels the idle thread. The second part is either not there or it is _DIAGLEVEL1 which issues a DSNT771I console message or _DIAGLEVEL2 which issues a DSNT772I console message with more details or WARNING_MESSAGE_FOR_IDLE_TIMEOUT (only for WARNING) which issues DSNT771I and/or DSNT773I. ATTRIBUTE2 is a positive integer to indicate the threshold for the maximum number of seconds an active server thread can stay idle.
That’s all for this month, next month I will go into detail about the Filters, the new stuff In Db2 11, 12 and 13 as well as examples of different things you can do nowadays.
As always I would love to hear any comments or criticism about this topic!
TTFN
Roy Boxwell