Client Connection Attributes and WLM

Client Connection Attributes

When you establish connection to DB2 using Java programs, you need 5 minimum attributes

DB2 server name or IP address
Port Number (socket connection)
Database name
User ID
Password

This could be defined as a URL in the Java program such as:

String url = "jdbc:db2://192.168.100.107:50000/testdb";

Then one defines the user id and password usually through connection properties. For example:

Properties connProp = new Properties();
connProp.setProperty("user", userid);
connProp.setProperty("password", passord);

And, then a connection is made using DriverManager.

connection = DriverManager.getConnection(url, connProp);

So, what’s new here? Nothing – This is what people do.

What is important – Java programmers have very little idea that they can also use client connection properties, which then can be used for the purpose of Work Load Management. This is the area least understood but very powerful.

This example illustrates this.

Use clientProgramName to identify who you are:

connProp.setProperty("clientProgramName", "APP4260");

This is a non-changeable property and once set – it can’t be changed during the life time of the connection.

Whatever name you set, you would see the same name when you run db2 command:

db2 list applications

This unchangeable name can become one of the attribute to do WLM in DB2.

The other client connection attributes are:

```
clientUser
```
```
clientWorkstation
```
```
clientAccountingInformation
```
```
clientApplicationInformation
```

It is not necessary that you have to use all four. Just use one that makes sense to you to uniquely identify an application for the purpose of WLM.

The great thing about above 4 properties are that they can be set after a connection has been made or they can be changed during anytime during the life of an application.

For example, they can be set using connection properties that we used before to set user id, password and client program name.

  connProp.setProperty("clientUser", "DB2POWER");
  connProp.setProperty("clientWorkstation", "Pegasus");
  connProp.setProperty("clientAccountingInformation", "Sales");
  connProp.setProperty("clientApplicationInformation", "Any_name");

Or, once connection has been established, they can be set anywhere in your application code.

connection = DriverManager.getConnection(url, connProp);
connection.setAutoCommit(false);
((DB2Connection) connection).setDB2ClientUser("DB2POWER");
((DB2Connection) connection).setDB2ClientAccountingInformation("Sales");
((DB2Connection) connection).setDB2ClientWorkstation("Pegasus");
((DB2Connection) connection).setDB2ClientApplicationInformation("Any_name");

The difference may not sound big to you but it really matters if you set them at the time of connection or after the connection. There is a difference between two and it can make a huge difference as when you want WLM to kick in.

Assuming, that you now know the difference. Your DBA friends can now do WLM magic to do things that were impossible to do before.

For example:

Just basic WLM monitoring to know how your Java application is performing. For example, to know the performance of just your application including all connections that it has opened.

DBAs may not know Java programming but they can write scripts.

Create WLM Workload

CONNECT TO STORE;
SET WORKLOAD TO SYSDEFAULTADMWORKLOAD;
CREATE WORKLOAD WL_DB2POWER CURRENT CLIENT_USERID ('DB2POWER') APPLNAME ('APP4260')
    ENABLE COLLECT AGGREGATE UNIT OF WORK DATA;
SET WORKLOAD TO AUTOMATIC;

WLM Monitoring

#!/bin/bash
#
# Author : Vikram Khatri
#
# Purpose: Run WLM monitoring for CLIENT_USERID 
#
# The SQL script to monitor a client
#
# SELECT ACT_COMPLETED_TOTAL + ACT_ABORTED_TOTAL AS TOTAL_TRANS,
# CAST(TOTAL_ACT_TIME/1000.0 as DECIMAL(8,2)) ACT_TIME_SECS
# FROM TABLE(MON_GET_WORKLOAD('workload_name'),-2)) as T
#

if [ $# -lt 1 ] ; then
 echo "Usage $0 username" 1>&2
 exit 1
fi

oldtotal=0.0
oldtps=0

db2 connect to store

CLIENTUSER=`echo $1 | awk '{print toupper($0)}'`
WORKLOAD=WL_${CLIENTUSER}
COUNT=1

printf "%-15s %9s %12s\n" "WORKLOAD" "TRANSACTIONS" "ELAPSED"
 
while true
do
 ARRAYS=(`db2 -x "
 select act_completed_total + act_aborted_total AS TOTAL_TRANS,
 cast(total_act_time/1000.0 as decimal(8,2)) ACT_TIME_SECS 
 from table(mon_get_workload('${WORKLOAD}',-2)) as t"`)
 total="${ARRAYS[0]}"
 tps="${ARRAYS[1]}"
 a=$(echo "scale=2;($total-$oldtotal)" | bc)
 b=$(echo "scale=2;($tps-$oldtps)" | bc)
 printf "%10s %11.2f %10.2f\n" "$WORKLOAD" "$a" "$b"
 sleep 1
 oldtotal="$total"
 oldtps="$tps"
 COUNT=$[$COUNT + 1]
 if [ $(($COUNT % 20)) -eq 0 ] ; then
 printf "%-15s %9s %12s\n" "WORKLOAD" "TRANSACTIONS" "ELAPSED"
 fi 
done

Now, with the help of above script, you are now monitoring the designated application (and not everything in your database). This script can be a life saver for you. How?

Use above script for a period of time (say 24 hours by controlling the sleep) and draw the time series graph to have a base line.
The WLM monitoring is very light weight and will not have any noticeable impact on your database and application.
When you are in slowdown situation, run this script again and see how it differs from your baseline.

The other things that can be done using WLM.

Concurrency controls
Assign dedicated and guaranteed CPU resources

Let’s see how concurrency controls can protect an application.

But, first what is concurrency control?

An ability to queue the workload if number of concurrent activities within same definition exceeds a given limit.

For example:

If you have 16 cores in your DB2 server, you know for sure that you will not stress the system if all 16 cores are doing the work simultaneously. But what if, you have 100 connections open for the same application. It will work – but the context switching will be more for 16 cores to do the work for 100 connections.

In order to protect your system from overloading and to have a guaranteed service level agreement, you can define the concurrency control for your application in which you say :-

If my application is identified by clientProgramName as APP4260 and the clientUser as DB2POWER, I will then enforce a concurrency control of 12 (16 – 4 for doing OS and other DB2 works) on this application
The application can still open 100 connections and I will not limit it.
But, I will only allow 12 concurrent connections to do the work and if there are more than 12, the others will wait in a queue and when their turn comes, then only they will be allowed to do the work.

The example of concurrency control.

CONNECT TO STORE;

SET WORKLOAD TO SYSDEFAULTADMWORKLOAD;

CREATE SERVICE CLASS SC_DB2POWER;

CREATE THRESHOLD TH_DB2POWER FOR SERVICE CLASS SC_DB2POWER 
 ACTIVITIES ENFORCEMENT DATABASE ENABLE 
 WHEN CONCURRENTDBCOORDACTIVITIES > 12 
 AND QUEUEDACTIVITIES UNBOUNDED CONTINUE;

CREATE WORKLOAD WL_DB2POWER CURRENT CLIENT_USERID ('DB2POWER') APPLNAME ('APP4260')
 ENABLE SERVICE CLASS SC_DB2POWER
 COLLECT AGGREGATE UNIT OF WORK DATA;

SET WORKLOAD TO AUTOMATIC;

The key here to have concurrency control is the threshold CONCURRENTDBCOORDACTIVITIES, which is an aggregate threshold having the capability of queuing. Check with db2pd -d <dbname> -thresholds output.

Threshold Name = TH_DB2POWER
Threshold ID = 1 
Domain = 30 
Domain ID = 14 
Predicate ID = 90 
Maximum Value = 1 
Enforcement = D 
Queueing = Y 
Queue Size = -1 
Collect Flags = N 
Partition Flags = C 
Execute Flags = C 
Enabled = Y 
Check Interval (seconds) = -1 
Remap Target Serv. Subclass = 0 
Log Violation Evmon Record = Y

But, how would you know if concurrency controls are working or not?

Again assuming that you have more connections than the number of cores in DB2, you can create a view and monitor the performance from this view to know if queuing is happening or not.

---------------------------------------------------------------
-- Create a view to see queue activity for concurrency control
---------------------------------------------------------------
CONNECT TO STORE;

DROP VIEW WLM_QUEUE_INFO;

CREATE VIEW WLM_QUEUE_INFO (APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID, 
 THRESHOLD_NAME, QUEUE_ENTRY_TIME, DBPARTITIONNUM) 
 AS SELECT APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID, 
 VARCHAR(EVENT_OBJECT_NAME, 128), AGENT_STATE_LAST_UPDATE_TIME, 
 DBPARTITIONNUM FROM TABLE(WLM_GET_SERVICE_CLASS_AGENTS(NULL,NULL,NULL,-2)) 
 AS T WHERE EVENT_OBJECT = 'WLM_QUEUE';

When your Java application is running, you can run this SQL as shown below to know how the queuing is going on.

db2 -x "select count(*) from db2psc.wlm_queue_info where threshold_name = 'TH_DB2POWER'

What have you learned so far?

It is very important for Java developers to set clientProgramName and one of the client attributes.
Do basic WLM monitoring
Implement concurrency controls to protect your database from ever getting down from increased workload

For example: If you are running a very critical 24×7 web application and you don’t have a clue about the workload that you may experience. Then, this is the right case for implementing WLM but that can only be done if your Java folks do the right thing but they will not have a clue unless you tell them so.

Client Connection Attributes and WLM