Stabilizing your dynamic world

Stabilizing your dynamic world

For many years we have known that executing BIND over packages and plans sets up the required structures inside the Db2 catalog and directory. We have known about it because it involves a considerable portion of any DBAs efforts to maintain such structures: Executing RUNSTATS, BIND, learning about EXPLAIN output, evaluating access paths, observing application’s trends over time, making decisions on object maintenance, query tuning, and much more; we’re basically describing a full job description here, or at least the portion that will keep a lot of DBAs busy every day. It also holds true for the dynamic world since we have also evolved over time, so we also have to become familiar with the Dynamic Statement Cache (DSC), local prepares, full prepares, some subsystem parameters and much more.

These days, however, we’re also learning about management strategies for the NULLID collections (those collections that serve as the bridge for remote connections) and their application compatibility levels, and we also have a new concept that deserves further play for our dynamic applications: Dynamic SQL plan stability. You might have heard about this feature already, but before you decide to activate it you should study it first and this article is going to help on your research.

If you wish to read more about this feature and new enhancements that came with Db2 12 for z/OS, IDUG prepared a document with the title “DB2 12 for z/OS. An IDUG User Technical Perspective”, located in the following link:

https://www.idug.org/p/do/sd/sid=9321

What Dynamic SQL plan stability is

As of Db2 12 for z/OS with a function level of 500 or above we can now use this new feature. If it’s activated, Db2 will save the prepared runtime structures into the following new catalog tables that can be further queried and explored:

  • SYSIBM.SYSDYNQRY. Will contain general information about stabilized statements.
  • SYSIBM.SYSDYNQRYDEP. As the name implies, will contain information about dependencies for packages and dependencies on authorities.

There’s also another group of tables for this feature, however, we will not be able to investigate them for diagnostic purposes:

  • SYSIBM.SYSDYNQRY_EXPL.
  • SYSIBM.SYSDYNQRY_OPL.
  • SYSIBM.SYSDYNQRY_SHTEL.
  • SYSIBM.SYSDYNQRY_SPAL.
  • SYSIBM.SYSDYNQRY_TXTL.

The source of this information, that is, the statements that will be stabilized, will be located first at the Dynamic Statement Cache and then saved into the tables previously mentioned if they apply to the conditions specified by the activation command. More on this later.

Next, at the time of a prepare request, Db2 will look into the Dynamic Statement Cache, this time however, if there’s a cache miss, Db2 will search within SYSIBM.SYSDYNQRY and if it’s located in the catalog, the information will be copied into the DSC and used from there. The same applies when the entire contents of the DSC are lost (due to Db2 restarts, for example), in which case Db2 will look into SYSIBM.SYSDYNQRY and load stabilized statements from the catalog into the DSC. Now you have this strategy to save existing access paths for your dynamic queries.

That’s the process of loading a stabilized dynamic statement.

What it could be

By using this new feature, you will have more tricks and techniques into workload management, and it can also offer insight into application trends if you want to exploit the contents of SYSIBM.SYSDYNQRY. This depends on how you want to use it since there are a few alternatives to start the collection of stabilized statements. You could use this feature on a simplified collection of statements or stabilizing a set of very specific statements previously located inside the Dynamic Statement Cache.

What it is not

It is by no means a shortcut to managing all your distributed environment. The statement is very simple: You do not want to stabilize all your statements because they will likely include poorly performing queries.

Why or when does it make sense?

Especially in a high-volume environment, we might suffer from unnoticed access path changes and the cost of full preparing our thousands of dynamic statements. As we also know, the contents of the Dynamic Statement Cache are lost on Db2 restarts and every time the contents are lost, statements must be prepared once again.

The purpose, as indicated by its name, is to stabilize dynamic statements as a subsequent PREPARE might result in a different form for a same statement.

This could be a point for further debate, as stabilizing a statement can also mean losing the potential benefit of a better access path that might have been found in the future. Therefore, you will want to be careful considering the options.

How can I use it?

We’ll begin by the START and DISPLAY commands. To activate the collection of stabilized statements, we have a START command that requires identifying a stabilization group, which is an ID we will provide, and this is how we will locate a unit of stabilization. We also have a threshold option that will serve as a counter: If a statement has been executed at least the number of times this parameter dictates, then it can be a candidate for stabilization, and it has a default number of 2 executions. It is important to note that the use of this parameter requires the activation of IFCID 0316 and 0318 or the trace of class 29, type MONITOR.

From here on, the rest of the options will offer a few choices or strategies, which we can briefly describe.

Strategy 1: Stabilize all dynamic activity

On the START command we have the option to filter by SQLID and its default is *, which means that by omitting this parameter (called CURSQLID), all dynamic activity that qualifies for the given threshold will be stabilized.

-START DYNQUERYCAPTURE STBLGRP(ALLIN) THRESHOLD(50)

In this example all dynamic statements in the DSC that have been executed 50 times or more will be stabilized. You will want to be careful with this option since it’s likely that it will contain statements you’re not comfortable with, that is, poorly executing statements that you might have identified previously.

Strategy 2: Stabilize applications

If you separate applications by user id, then you can also narrow your scope to create a stabilization group that represents an individual application, and in case you have concentrated all access to your critical tables to a very specific user, then you can also stabilize a group that identifies this user and its activity to watch it closer.

-START DYNQUERYCAPTURE STBLGRP(APP1) THRESHOLD(50) CURSQLID(<user-from-APP1>)

 

Strategy 3: Stabilization by looking at the DSC contents

You can also create stabilization groups that point to statement IDs from the Dynamic Statement Cache or by token. This means that you have previously done your homework of running EXPLAIN STATEMENT CACHE and studied the contents of the table DSN_STATEMENT_CACHE_TABLE.

As usual, it all depends on the approach we wish to take. If you want to explore this path you might want to jump straight into this last option so you can observe in detail before taking aggressive decisions of stabilizing all statements from a user, or even worse, stabilizing everything.

-START DYNQUERYCAPTURE STBLGRP(STMT1) STMTID(<id-of-statement>)

 

Dynamic stabilization

We also have the option to use this feature in a more dynamic way. All previous statements have one thing in common: They capture statements that have qualified for a certain threshold, but what happens if another statement will qualify in the future? We can also address this issue by using the MONITOR option, which will continuously look into the DSC, and if there is a statement that matches an existing stabilization group and has reach its defined threshold, it will be added into SYSIBM.SYSDYNQRY.

Consideration for Data Sharing Groups

For Data Sharing Groups, we have the following notes:

  • By stabilizing with an ID or token, Db2 will look into the DSC of the member where the command was run.
  • We can also run the START command with the option SCOPE. This has an additional note: If at a later point a new member is added to the group, the stabilization monitor will not start automatically for this new member. For example, if we start active monitoring on the activity of a specific user ID on a whole data sharing group using SCOPE(GROUP), and later in time we add another member into the data sharing group, the active monitoring will not automatically start on this new member.

Other considerations

Stabilizing dynamic statements is possible with a few limitations, where queries with the following conditions will not qualify for stabilization:

  • Queries that reference archive tables and thus are affected by the variable GET_ARCHIVE.
  • Queries that are transformed by System Temporal tables, Application Temporal tables and the use of registers such as CURRENT SYSTEM TEMPORAL TIME or CURRENT BUSINESS TEMPORTAL TIMME.
  • Queries prepared with CONCENTRATE STATEMENT WITH LITERALS.
  • Queries that are prepared with REOPT(AUTO).

Special note here: As you have seen the last point, you might be wondering how this applies to your NULLID collections. If you executed the DB2Binder utility without specifying the option reopt, then your packages will be bound as REOPT(NONE).

Usage and contents of SYSIBM.SYSDYNQRY

Now we will use an example to look into this feature. For this, I have created a copy of the sample database and started the monitor to capture all the activity under my user id.

-START DYNQUERYCAPTURE STBLGRP(JAVIER1) CURSQLID(<my-user>) MONITOR(YES) 

As you’re experimenting, you will also want to specify MONITOR(YES) so that you can track how your stabilization group changes. Upon activation, you will obtain a confirmation message that gives you in return a capture monitor number if you decide to monitor it.

DSNX221I <SSID> DSNXESTC DYNAMIC QUERY CAPTURE FOR COMMAND NUMBER 2 STARTED SUCCESSFULLY.

 

At the moment I don’t have any activity run on my sample database, and so the collection has stabilized no statements.

DSNX222I <SSID> DSNXESC1 DYNAMIC QUERY CAPTURE COMPLETED FOR COMMAND NUMBER 2, WITH 0 STATEMENTS SCHEDULED, 0 STATEMENTS STABILIZED, AND 0 STATEMENTS ALREADY STABILIZED. 

 

In this example, my capture monitor is number 2 and so we will identify this with the DISPLAY DYNQUERYCAPTURE command. Note that if we started the stabilization with MONITOR(NO), then we won’t be able to see information for such stabilization group.

-DIS DYNQUERYCAPTURE CNO(*)
DSNX250I  <SSID> DSNXEDQC                                             
*** BEGIN DISPLAY DYNAMIC QUERY CAPTURE CNO(*)                       
====================================================================
CNO        : 2                                                       
STBLGRP    : JAVIER1                                                 
SQLID      : <my-user>                                                 
THRESHOLD  : 2                                                       
STABILIZED : 0                                                       
====================================================================
*** END DISPLAY DYNAMIC QUERY CAPTURE                                

As we execute activity on this database, we will see that the number of stabilized statements will increase. We’ll begin with a simple SELECT to see this reflected.

-DIS DYNQUERYCAPTURE CNO(*)
DSNX250I <SSID> DSNXEDQC
*** BEGIN DISPLAY DYNAMIC QUERY CAPTURE CNO(*)
====================================================================
CNO : 2
STBLGRP : JAVIER1
SQLID : <my-user>
THRESHOLD : 2
STABILIZED : 1
====================================================================
*** END DISPLAY DYNAMIC QUERY CAPTURE

At this point, if we look at the contents of SYSIBM.SYSDYNQRY, we’ll see a few generated queries to the catalog itself, and my SELECT.

Look at the values in these columns, you can already see valuable information inside.

SDQ_STMT_ID STBLGRP COPYID CURAPPLCOMPAT VALID LASTUSED
---------------------------------------------------------
5 JAVIER1 0 V12R1M500 Y 2018-12-17

 

The columns LASTUSED, COPYID and VALID will give insight as you can also leverage the contents of this catalog table to analyze your application trends.

Of course, we also have the text of the stabilized statement under the column STMTTEXT.

SELECT F.WORKDEPT, F.EMPNO, F.LASTNAME, F.SALARY 
FROM EMP AS F
WHERE F.WORKDEPT IN
(SELECT X.DEPTNO FROM
(SELECT D.DEPTNO, D.DEPTNAME,
COALESCE(SUM(E.SALARY),0) AS "TOTAL SALARY",
RANK() OVER(ORDER BY COALESCE(SUM(E.SALARY),0))
AS "RANK BY SALARY"
FROM DEPT D
LEFT OUTER JOIN
EMP E
ON D.DEPTNO = E.WORKDEPT
GROUP BY D.DEPTNO, D.DEPTNAME
HAVING COALESCE(SUM(E.SALARY),0) > 0) AS X
WHERE "RANK BY SALARY" <= 3)
ORDER BY F.SALARY, F.LASTNAME
DSNHATTR FOR READ ONLY

 

During your exercise, as we issued the command over the activity of a user ID, you will also see that your SELECT * FROM SYSIBM.SYSDYNQRY will also appear as a stabilized statement and this serves as a reminder to be careful with how you want to approach query stabilization.

Now, to continue with the experiment, look at the contents of SYSDYNQRYDEP for the stabilized statement. This table will contain information on the dependencies for these statements. Columns BNAME and BTYPE will indicate the names of the objects they’re dependent on and what kind of objects they are, while columns AUTHID, BAUTH, PUBLICAUTH, and BADMINAUTH will contain information for dependencies on privileges. Also look at the column CLASS, which will differentiate if a column belongs to an authorization or DDL dependency. What does it tell us? It confirms that DROPs, ALTERs and REVOKEs will invalidate our stabilized statements.

How does invalidation happen?

In table SYSIBM.SYSDYNQRY, columns COPYID and VALID will report on this. The column VALID contains some options that will specify why a statement is no longer valid: A DROP will set a value of ‘N’, while ALTERs will set it to either ‘A’ or ‘H’. Column COPYID will only have two options, 0 for all current copies and 4 if this is an invalid copy. Therefore, upon executing a DROP we will see the following combination:

COPYID VALID
------------
0 N

When do I see a value of 4 in COPYID?

Next time the same statement is prepared, you will see the new copy in SYSIBM.SYSDYNQRY with the same value in SDQ_STMT_ID, however, you will have the current copy with value 0 in COPYID and now the previously invalidated copy will contain an indicator of 4.

How can I delete records from my stabilized statements?

After some time when you decide to delete your invalidated copies, you can execute the FREE STABILIZED DYNAMIC QUERY command, which will also offer several options depending on our scope:

  • Delete a whole stabilization group (or all groups).
  • Delete copies for a particular statement ID.

And from the both options above, we have the following filters:

  • Delete all copies.
  • Delete only invalidated copies. We have the options INVALIDONLY and PLANMGMTSCOPE that filter on the values of columns VALID and COPYID respectively.

Be careful, as the default is set to delete all copies regardless of their validity.

In the output of the command we can also see how many copies were deleted.

DSN
FREE STABILIZED DYNAMIC QUERY STBLGRP(JAVIER1) INVALIDONLY(YES)
DSNT340I <SSID> FREE STABILIZED DYNAMIC QUERY COMPLETED SUCCESSFULLY
FOR 1 STATEMENTS.
DSN

Do I have to look into my subsystem parameters?

Yes. Now you have subsystem parameter CACHEDYN_STABILIZATION that will control how this feature behaves, with these options:

  • Completely disable this feature (value NONE).
  • Enable capture into catalog but disable loading into DSC (value CAPTURE).
  • Enable loading into DSC but disable statement capturing (value LOAD).
  • Enable both capture and loading (this is the default, value BOTH).

Closing thoughts

What’s the right approach for you? That’s the big question. Stabilizing everything will impact the space on your catalog storage group and might include statements that you don’t wish to stabilize, however, on the most basic START command the defaults will look for all dynamic statements that have been executed more than twice, so I have to remind you to be careful. One approach, though it requires more study at your particular site, is to execute EXPLAIN STATEMENT CACHE and carefully look at the statistics of the statements, or perhaps you already have a few particular statements in mind and you want to confirm by looking at this.

However you decide to experiment with this feature, it requires a good knowledge of your applications and constant monitoring, so you might want to explore with this first to consider its options, how much it will impact on your daily tasks and how much effort you can afford to invest in managing your stabilized statements.

 

 

3 Likes
Recent Stories
Scalable Predictive Machine Learning for Invoking Utilities for Db2 for z/OS

Feature Engineering for initial insights

April Sum up at IDUG