Runstats on data change percentage
As always I like to be in control of stuff, just to know what is happening and thereby preventing unexpected behavior. Same goes for maintenance tasks. Collecting statistics on your tables for example. I wanted to have a procedure so that I can kick it off at a time of my liking. And not only at a specific time, but I also wanted to determine which tables I’m going to collect statistics of. I would like to trigger the collection of statistics on a (configurable) data change percentage of the table evaluated.
Get the metrics
Having said that I was looking into the data change percentage and quickly trying things with the monitoring SQL functions that were introduced with Version 9.7 of DB2 for Linux, UNIX, and Windows. These new table functions are a more efficient and light-weight alternative to the snapshot monitor table functions that existed prior to Version 9.7. Monitoring using SQL table functions provides a number of significant advantages over other DB2 monitoring interfaces such as the snapshot commands (for example, GET SNAPSHOT) or the snapshot APIs (for example, db2GetSnapshot). An SQL interface can be used in any programming language that supports SQL, and because the monitoring data is returned as rows in a table, the interface supports the rich query capabilities of the SQL language. For example, you can easily search for the applications consuming the highest amount of CPU by simply adding a WHERE clause to a query that lists application data from a monitoring table function. Isn’t that’s great? I think so, I’m going to use those!
The idea was to look at the data change percentage of the table to inspect for statistics collection. And if the data change percentage of that table exceeded a certain threshold, I would collect statistics on that table. Great idea, but immediately bumped into some specific details of the monitoring functions. The monitoring data they report is always relative to the activation of the database. That is, counters start at 0 on database activation at each database partition and are strictly increasing until deactivation.
Hmmm… so when I determine the data change percentage it will never be reset. And a reset is what I needed. If I collected statistics on a table because the threshold was exceeded I want to reset the data change percentage to zero. After searching the Internet for a while I found a great article on emulating data reset of which my solution to control the statistics collection is based. (See the References at the bottom of this page)
The way to achieve a reset and get the data change percentage every time the runstats procedure is executed is by storing table metric data in a temporary table. Data of which I can calculate the data change percentage from. And when the threshold is exceeded and statistics collection is executed the baseline is being reset. That way it’s in the right state for the next time the runstats procedure is being executed. Well, that’s enough talk for now I would say. Let’s go and get into the coding stuff…
First a table is needed to store the table metrics in. We will create the table with columns for all the metrics we want to store. Note that I extended the table with more data than is strictly needed to determine the data change percentage. I might have plans for other triggers in my maintenance plan than the one discussed here. For example the overflow access and creates to set the PCTFREE of a table. But for now we will focus on data change percentage.
Tracking baseline table
CREATE TABLE DBA.MON_TABLE_BASELINE( BASELINE_TIMESTAMP TIMESTAMP NOT NULL, TABSCHEMA VARCHAR(128) NOT NULL, TABNAME VARCHAR(128) NOT NULL, TABLE_SCANS BIGINT , ROWS_READ BIGINT , ROWS_INSERTED BIGINT , ROWS_UPDATED BIGINT , ROWS_DELETED BIGINT , OVERFLOW_ACCESSES BIGINT , OVERFLOW_CREATES BIGINT , PAGE_REORGS BIGINT ); CREATE UNIQUE INDEX UN_MTB_PK ON DBA.MON_TABLE_BASELINE (TABSCHEMA, TABNAME) ALLOW REVERSE SCANS; ALTER TABLE DBA.MON_TABLE_BASELINE ADD CONSTRAINT PK_MTB PRIMARY KEY (TABSCHEMA, TABNAME); COMMIT;
Having the table to store the metrics in place, we need to initially fill it with the metrics of all tables we want to evaluate. For this I created the next procedure. The procedure is collecting all data for all tables in all schema’s in the database with some exceptions. I don’t want to include the system tables (SYS%), nor the explain plan tables (ADMIN%) or the tables that I use in my maintenance plan (the DBA schema).
Procedure initially filling the baseline table
------------------------------------------------------------------------------- -- Update complete baseline (all tables) ------------------------------------------------------------------------------- CREATE PROCEDURE DBA.MON_UPDATE_TABLE_BASELINE() LANGUAGE SQL BEGIN DELETE FROM DBA.MON_TABLE_BASELINE; INSERT INTO DBA.MON_TABLE_BASELINE SELECT CURRENT TIMESTAMP, TABSCHEMA, TABNAME, SUM(TABLE_SCANS) AS TABLE_SCANS, SUM(ROWS_READ) AS ROWS_READ, SUM(ROWS_INSERTED) AS ROWS_INSERTED, SUM(ROWS_UPDATED) AS ROWS_UPDATED, SUM(ROWS_DELETED) AS ROWS_DELETED, SUM(OVERFLOW_ACCESSES) AS OVERFLOW_ACCESSES, SUM(OVERFLOW_CREATES) AS OVERFLOW_CREATES, SUM(PAGE_REORGS) AS PAGE_REORGS FROM TABLE(MON_GET_TABLE('','',-2)) AS T WHERE T.TABSCHEMA NOT LIKE ('SYS%') AND T.TABSCHEMA NOT LIKE ('ADMIN%') AND T.TABSCHEMA != ('DBA') GROUP BY T.TABSCHEMA, T.TABNAME; END~
Now we have a baseline of all tables. Next up is a procedure to determine if a certain data change threshold for a specific table has been exceeded. The final function that determines the data change percentage is at the end of this chapter. It uses a helper function to get to the data change percentage,
DBA.MON_GET_DATA_CHANGE_PERCENTAGE. That procedure uses another helper procedure,
DBA.MON_GET_TABLE_DELTA that actually calculates the data change percentage (and the other metrics which I may be using in the future) and returns all these in the form of a table (just like the SQL functions we’re using to collect the metrics). This function is a little different from the example mentioned in the reference material. The function in this post is supporting partitioned tables too. The main difference is the SUM on the metrics. We need this because otherwise you’ll get duplicate rows for a partitioned table, one row for every partition. That’s not what we want, hence the SUM to sum the metrics over all partitions of a partitioned table. In contrary of the REORG command, RUNSTATS can’t be executed on a single partition.
Function calculating the data change for a schema/table combo and returning a table
CREATE FUNCTION DBA.MON_GET_TABLE_DELTA( TABSCHEMA_IN VARCHAR(128), TABNAME_IN VARCHAR(128) ) RETURNS TABLE ( BASELINE_TIMESTAMP TIMESTAMP, TABSCHEMA VARCHAR(128), TABNAME VARCHAR(128), TABLE_SCANS BIGINT, ROWS_READ BIGINT, ROWS_INSERTED BIGINT, ROWS_UPDATED BIGINT, ROWS_DELETED BIGINT, OVERFLOW_ACCESSES BIGINT, OVERFLOW_CREATES BIGINT, PAGE_REORGS BIGINT) LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA RETURN SELECT (SELECT B.BASELINE_TIMESTAMP FROM DBA.MON_TABLE_BASELINE B WHERE A.TABSCHEMA = B.TABSCHEMA AND A.TABNAME = B.TABNAME) AS BASELINE_TIMESTAMP, A.TABSCHEMA, A.TABNAME, DBA.MON_METRIC_DELTA(SUM(A.TABLE_SCANS), (SELECT B.TABLE_SCANS FROM DBA.MON_TABLE_BASELINE B WHERE A.TABSCHEMA = B.TABSCHEMA AND A.TABNAME = B.TABNAME)), DBA.MON_METRIC_DELTA(SUM(A.ROWS_READ), (SELECT B.ROWS_READ FROM DBA.MON_TABLE_BASELINE B WHERE A.TABSCHEMA = B.TABSCHEMA AND A.TABNAME = B.TABNAME)), DBA.MON_METRIC_DELTA(SUM(A.ROWS_INSERTED), (SELECT B.ROWS_INSERTED FROM DBA.MON_TABLE_BASELINE B WHERE A.TABSCHEMA = B.TABSCHEMA AND A.TABNAME = B.TABNAME)), DBA.MON_METRIC_DELTA(SUM(A.ROWS_UPDATED), (SELECT B.ROWS_UPDATED FROM DBA.MON_TABLE_BASELINE B WHERE A.TABSCHEMA = B.TABSCHEMA AND A.TABNAME = B.TABNAME)), DBA.MON_METRIC_DELTA(SUM(A.ROWS_DELETED), (SELECT B.ROWS_DELETED FROM DBA.MON_TABLE_BASELINE B WHERE A.TABSCHEMA = B.TABSCHEMA AND A.TABNAME = B.TABNAME)), DBA.MON_METRIC_DELTA(SUM(A.OVERFLOW_ACCESSES), (SELECT B.OVERFLOW_ACCESSES FROM DBA.MON_TABLE_BASELINE B WHERE A.TABSCHEMA = B.TABSCHEMA AND A.TABNAME = B.TABNAME)), DBA.MON_METRIC_DELTA(SUM(A.OVERFLOW_CREATES), (SELECT B.OVERFLOW_CREATES FROM DBA.MON_TABLE_BASELINE B WHERE A.TABSCHEMA = B.TABSCHEMA AND A.TABNAME = B.TABNAME)), DBA.MON_METRIC_DELTA(SUM(A.PAGE_REORGS), (SELECT B.PAGE_REORGS FROM DBA.MON_TABLE_BASELINE B WHERE A.TABSCHEMA = B.TABSCHEMA AND A.TABNAME = B.TABNAME)) FROM TABLE(MON_GET_TABLE(TABSCHEMA_IN,TABNAME_IN,-2)) A GROUP BY A.TABSCHEMA, A.TABNAME~
Please note the function that determines the data change percentage (see following code fragment) can return a percentage which is greater than 100%. Reason for this, it calculates the ratio between rows updated, deleted and inserted and the total number of rows in the table (CARD). When for instance all rows in a table are updated twice, the data change percentage will be 200%.
Function returning the data change percentage
CREATE FUNCTION DBA.MON_GET_DATA_CHANGE_PERCENTAGE( TABSCHEMA_IN VARCHAR(128), TABNAME_IN VARCHAR(128), TABCARD BIGINT DEFAULT 0 ) RETURNS DECIMAL(17,2) LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA BEGIN DECLARE #PCT_ROWS_CHANGED DECIMAL(17,2) DEFAULT 0; SET #PCT_ROWS_CHANGED = ( SELECT CASE WHEN (T.ROWS_INSERTED + T.ROWS_UPDATED + T.ROWS_DELETED) > 0 AND TABCARD > 0 THEN DECIMAL( ( DECIMAL(T.ROWS_INSERTED + T.ROWS_UPDATED + T.ROWS_DELETED) / TABCARD * 100 ) ,17,2 ) ELSE DECIMAL(0,17,2) END AS ROWS_CHANGED FROM TABLE(DBA.MON_GET_TABLE_DELTA(TABSCHEMA_IN, TABNAME_IN)) T); RETURN #PCT_ROWS_CHANGED; END~
The function that determines if the given threshold (as a parameter) is exceeded, is a simple one. It calls the
MON_GET_DATA_CHANGE_PERCENTAGE function to get the data change percentage of the table and determines if it’s more than the threshold.
Function determining if the threshold has been exceeded
CREATE FUNCTION DBA.MON_DATA_CHANGE_THRESHOLD_EXCEEDED( TABSCHEMA_IN VARCHAR(128), TABNAME_IN VARCHAR(128), TABCARD BIGINT DEFAULT 0, DATA_CHANGE_PCT_IN DECIMAL(5,2) DEFAULT 0 ) RETURNS BOOLEAN LANGUAGE SQL NOT DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA BEGIN DECLARE #PCT_ROWS_CHANGED DECIMAL(17,2) DEFAULT 0; SET #PCT_ROWS_CHANGED = DBA.MON_GET_DATA_CHANGE_PERCENTAGE(TABSCHEMA_IN, TABNAME_IN, TABCARD); IF (#PCT_ROWS_CHANGED > DATA_CHANGE_PCT_IN) THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END~
Did you notice the procedure
DBA.MON_GET_TABLE_DELTA using a helper function too? Sure you did, I knew that. The helper function it is using,
DBA.MON_METRIC_DELTA, calculates the delta of a metric and supports a possible counter rollover and NULL baseline values resulting from objects that didn’t exist when baseline was taken. A counter rollover will probably never occur, but you’ll never know. The maximum value of a BIGINT is 9223372036854775807 and that’s quite a large number to rollover. Nevertheless, it’s supported.
Function calculating a metric delta
CREATE FUNCTION DBA.MON_METRIC_DELTA( CURR BIGINT, BASELINE BIGINT ) RETURNS BIGINT LANGUAGE SQL CONTAINS SQL DETERMINISTIC RETURN CASE WHEN BASELINE IS NULL THEN CURR WHEN BASELINE <= CURR THEN CURR - BASELINE ELSE -- Handle counter rollover CURR + (9223372036854775807 - BASELINE) END~
Having all that were almost there. We now can determine if a certain data change percentage for a schema/table has exceeded it’s threshold. And if so, we can collect statistics on that table. But afterwards, when we collected statistics on the table, the metrics need to be reset for the next evaluation. This is archived with the following procedure.
Procedure resetting the metrics after statistics are collected
CREATE PROCEDURE DBA.MON_UPDATE_TABLE_BASELINE( IN TABSCHEMA_IN VARCHAR(128), IN TABNAME_IN VARCHAR(128) ) LANGUAGE SQL BEGIN DELETE FROM DBA.MON_TABLE_BASELINE BL WHERE BL.TABSCHEMA = TABSCHEMA_IN AND BL.TABNAME = TABNAME_IN; INSERT INTO DBA.MON_TABLE_BASELINE SELECT CURRENT TIMESTAMP, TABSCHEMA, TABNAME, SUM(TABLE_SCANS) AS TABLE_SCANS, SUM(ROWS_READ) AS ROWS_READ, SUM(ROWS_INSERTED) AS ROWS_INSERTED, SUM(ROWS_UPDATED) AS ROWS_UPDATED, SUM(ROWS_DELETED) AS ROWS_DELETED, SUM(OVERFLOW_ACCESSES) AS OVERFLOW_ACCESSES, SUM(OVERFLOW_CREATES) AS OVERFLOW_CREATES, SUM(PAGE_REORGS) AS PAGE_REORGS FROM TABLE(MON_GET_TABLE(TABSCHEMA_IN,TABNAME_IN,-2)) AS T GROUP BY T.TABSCHEMA, T.TABNAME; END~
Now where getting somewhere. The last two procedures is a cleanup process and a support (reset metrics) for when the database is re-activated. First the cleanup process; when a table is no longer in the system catalog (it’s been dropped or so) we want to clean that table from the baseline. Otherwise it would be sticking in there for eternity.
Procedure that cleans up the baseline with non-existing tables
CREATE PROCEDURE DBA.MON_CLEANUP_TABLE_BASELINE() LANGUAGE SQL BEGIN DELETE FROM DBA.MON_TABLE_BASELINE BL WHERE NOT EXISTS ( SELECT 1 FROM SYSCAT.TABLES T WHERE T.TABSCHEMA = BL.TABSCHEMA AND T.TABNAME = BL.TABNAME ); END~
And the last procedure supporting re-activation of the database. When the database is re-activated all counters are reset to zero. Because the method described above is using the counters to determine the data change percentage we also need to reset the metrics when the database is re-activated.
Procedure that resets the metrics after a re-activation of the database
CREATE PROCEDURE DBA.MON_ASSURE_PROPER_INITIALIZATION() LANGUAGE SQL BEGIN DECLARE #MAX_BASELINE_TS TIMESTAMP; DECLARE #DATABASE_ACTIVATION_TS TIMESTAMP; SET #MAX_BASELINE_TS = (SELECT MAX(BASELINE_TIMESTAMP) FROM DBA.MON_TABLE_BASELINE); SET #DATABASE_ACTIVATION_TS = (SELECT DB_CONN_TIME FROM SYSIBMADM.SNAPDB); IF (#MAX_BASELINE_TS IS NULL -- Not initialized OR #MAX_BASELINE_TS <= #DATABASE_ACTIVATION_TS) -- Database has been re-activated THEN -- Re-initialize metrics CALL DBA.MON_UPDATE_TABLE_BASELINE(); END IF; END~
Howto construct an overarching procedure
You can now construct your own overarching procedure that you can kick of at a time of your liking to evaluate all tables on data change percentage and based on the outcome collect statistics or not. One can choose to log all the evaluations, executions and more. I myself created a procedure that does the evaluation, possible statistics collection execution (based on data change percentage) and stores the results in a housekeeping table. The housekeeping table is storing the number of tables evaluated, the data change percentage of every table at the date/time the main procedure is called and a status indicating if statistics collection has been executed.
The overall flow of my procedure is like this:
- Assure proper initialization by checking if the database has been re-activated
- Cleanup the baseline table, removing non-existing tables by calling
- Declare a GLOBAL TEMPORARY TABLE for the processing in the procedure with at least one boolean (CHAR) indicating if the data change threshold has been exceeded. That will be the trigger for executing statistics collection or not
- Load all tables with there statistics profile (or statistics command) and the boolean to indicate is the data change threshold has been exceeded into the temporary table
CASE WHEN (DBA.MON_DATA_CHANGE_THRESHOLD_EXCEEDED(a.tabschema, a.tabname, a.card, #runstats_data_change_pct_threshold) = TRUE) THEN 'T' ELSE 'F' END
- Then evaluate the data change percentage boolean and execute statistics collection or not. If the threshold has been exceeded and statistics is collected, reset the baseline for that table.
CALL sysproc.admin_cmd (#db2_runstats_line); CALL DBA.MON_UPDATE_TABLE_BASELINE(#schema, #table); -- Resets changed data counters
HINT: You could extend the where clause for selecting the tables to more than just checking on data change percentage. I added for instance also to execute statistics collection when the table at hand was altered, but no statistics has been collected for that table after the alteration. You can derive this easily from the SYSCAT.TABLES.
... FROM SYSCAT.TABLES a WHERE (a.stats_time IS NOT NULL AND (a.alter_time > a.stats_time))
Using data change percentage is a good way to determine if you want to collect statistics on a table or not. The nicest feature of the method described above is that you can set the threshold yourself. You can choose whatever fits your business best. I’ve left out an example of a procedure that is using the described method. I think you’d best create one of your own. With your own specifications. You could also have a look at a blog I’ve written about controlling your own table maintenance; Automatic Table Maintenance Self-controlled. This article contains such a procedure that’s also using the data change percentage I’ve described here.