One of the new features of DB2 10.5 is BLU Acceleration. In introduces a couple of default Workload Management objects that are intended to control heavy queries running against column-organized tables. The objects are automatically created with every database, independent of the product edition. They are only enabled when DB2_WORKLOAD has been set to ANALYTICS before creating the database, i.e., a database for in-memory analytics is set up. But what is available for the regular guy like myself? What can be used for free and as foundation for some monitoring and understanding the system workload? Let's take a look.
Typically I use a DB2 Developer Edition which includes all features including WLM. So I removed the db2de license and organized (being IBMer has some benefits!) a Workgroup Server Edition (db2wse) which I added to the system using db2licm. I also turned on hard license enforcement, so that any attempts of using an unlicensend feature are directly blocked. Here is what db2licm returned thereafter:
mymachine> db2licm -l
Product name: "DB2 Workgroup Server Edition"
License type: "Authorized User Single Install"
Expiry date: "Permanent"
Product identifier: "db2wse"
Version information: "10.5"
Max amount of memory (GB): "128"
Enforcement policy: "Hard Stop"
Number of licensed authorized users: "25"
With that in place I created a new database named WLMTEST and connected to it. My first test was to create a workload object which should not be possible given my DB2 edition:
DB: WLMTEST => create workload freeride applname('xploit')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL8029N A valid license key was not found for the requested functionality.
Reference numbers: "".
Ok, this look right. I don't have a license to use DB2 WLM (Workload Manager). My next query was intended to check what service classes are present in my system.
DB: WLMTEST => select varchar(serviceclassname,30), varchar(parentserviceclassname,30), enabled from syscat.serviceclasses
1 2 ENABLED
------------------------------ ------------------------------ -------
SYSDEFAULTSUBCLASS SYSDEFAULTSYSTEMCLASS Y
SYSDEFAULTSUBCLASS SYSDEFAULTMAINTENANCECLASS Y
SYSDEFAULTSUBCLASS SYSDEFAULTUSERCLASS Y
SYSDEFAULTMANAGEDSUBCLASS SYSDEFAULTUSERCLASS Y
SYSDEFAULTSYSTEMCLASS - Y
SYSDEFAULTMAINTENANCECLASS - Y
SYSDEFAULTUSERCLASS - Y
7 record(s) selected.
The DB2 Knowledge Center has an overview of related default WLM objects and which parts can be modified with DBADM or WLMADM authority. Having the names of the system objects I tried my luck altering a work class set to reduce the cost barrier for the managed heavy queries (SYSMANAGEDQUERIES):
DB: WLMTEST => alter work class set sysdefaultuserwcs alter work class SYSMANAGEDQUERIES for timeroncost from 1000
DB20000I The SQL command completed successfully.
The threshold SYSDEFAULTCONCURRENT defines how many of those queries can run concurrently in the system. Why not change that threshold definition?
DB: WLMTEST => alter threshold SYSDEFAULTCONCURRENT when sqlrowsreturned > 20 stop execution
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL4721N The threshold "SYSDEFAULTCONCURRENT" cannot be created or altered
(reason code = "7"). SQLSTATE=5U037
Well, it seems that you cannot modify the entire threshold to your liking. However, following the documentation on what can be done, I successfully reduced the number of parallel activities.
DB: WLMTEST => alter threshold SYSDEFAULTCONCURRENT when CONCURRENTDBCOORDACTIVITIES > 3 stop execution
DB20000I The SQL command completed successfully.
To test the impact of my changes, I opened 4 different shells, connected to DB2 in each window, and more or less simultaneously executed the following query:
select * from syscat.tables,syscat.columns
I have to restate that I tried to execute it in all four windows. It only ran in three of them. Why? Because the threshold kicked in for this heavy query and stopped the execution for the 4th session ("concurrentdbcoordactivities> 3 stop execution"). So some basic workload management seems to work even without a license.
Can I change the threshold to force the application off, i.e., to not allow running the query?
DB: WLMTEST => alter threshold SYSDEFAULTCONCURRENT when CONCURRENTDBCOORDACTIVITIES > 2 force application
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL4721N The threshold "SYSDEFAULTCONCURRENT" cannot be created or altered
(reason code = "13"). SQLSTATE=5U037
No, changing the entire definition of the threshold is not possible, but at least parts of it can be modified. You can then use the adapted default WLM objects to better understand what work is running on your system, e.g., testing what would fall into the category of "heavy queries". As a last step, I used a monitoring function to return the CPU time spent by service subclass. Most was in the managed subclass into which my queries from above were mapped:
DB: WLMTEST => SELECT varchar(service_superclass_name,30) as service_superclass, varchar(service_subclass_name,30) as service_subclass, sum(total_cpu_time) as total_cpu, sum(app_rqsts_completed_total) as total_rqsts FROM TABLE(MON_GET_SERVICE_SUBCLASS('','',-2)) AS t GROUP BY service_superclass_name, service_subclass_name ORDER BY total_cpu desc
SERVICE_SUPERCLASS SERVICE_SUBCLASS TOTAL_CPU TOTAL_RQSTS
------------------------------ ------------------------------ -------------------- --------------------
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 1207794 552
SYSDEFAULTUSERCLASS SYSDEFAULTMANAGEDSUBCLASS 852547 0
SYSDEFAULTMAINTENANCECLASS SYSDEFAULTSUBCLASS 466436 1374
SYSDEFAULTSYSTEMCLASS SYSDEFAULTSUBCLASS 0 0
4 record(s) selected.
With that I leave more testing to you. Happy monitoring!
BTW: The same tests can also be done on the SQL DB service on IBM Bluemix. That service is a DB2 Enterprise Server Edition.