Connectivity and administration routines
DB2 11 sees further improvements of universal drivers for accessing data on any local or remote server. In addition, DB2 11 for z/OS provides a number of enhancements to improve the availability and performance of distributed applications.
This chapter describes these topics:
9.1 Client information enhancements
The DB2 client information fields are available on each distributed connection to a DB2 for z/OS database server. These fields are also available in other members of the DB2 family of databases. They enable a distributed application to provide additional information to DB2 that can be used to classify the workload within WLM, or to filter accounting reports, for example.
Up to DB2 10 for z/OS, client information lengths are arbitrary and are restrictive compared to other platforms of the DB2 product family.
Changes introduced in DB2 11 help to address the following needs:
ā€¢To be able to use longer fields to store business meaningful information in the client application registers
ā€¢To be able to override the current DRDA correlation token with a business value to correlate application work across the enterprise
At a glance, the improvements introduced by DB2 11 for z/OS in this area can be summarized as follows:
ā€¢Expansion of the length of some Client information fields
ā€¢Introduction of a new Client information field: Client Correlation Token
ā€¢Introduction of a new built-in session global variable: SYSIBM.CLIENT_IPADDR
These changes, how to take advantage of them, and their practical considerations are described in the following sections.
9.1.1 Expansion of the length of some Client information fields
DB2 11 enhances Client information in DB2 for z/OS by expanding the lengths of these fields:
ā€¢Client User ID
ā€¢Client Application Name
ā€¢Client Workstation Name
ā€¢Client Accounting Information
Table 9-1 list the Client information field length DB2 11 for z/OS in contrast with the lengths of these fields in DB2 10.
Table 9-1 Client information fields length changes with DB2 11 for z/OS
Field name
Max length in DB2 10
Max length in DB2 11
Client User ID
16 bytes
128 bytes
Client Application Name
32 bytes
255 bytes
Client Accounting Information
200 bytes
255 bytes
Client Workstation Name
18 bytes
255 bytes
Client Correlation Token
N/A
255 bytes
The longer client information length provide better granularity in the exploitation of this information, and compatibility with other databases member of the DB2 family of products. The longer client info strings are exploited in:
ā€¢WLM enclave classification
ā€¢DB2 supplied SYSPROC.WLM_SET_CLIENT_INFO stored procedure
ā€¢RRSAF DSNRLI SET_CLIENT_ID function
ā€¢Rollup accounting
ā€¢Profile Monitoring for remote threads and connections
ā€¢Resource Limit Facility (RLF)
ā€¢Client information special registers
ā€¢DISPLAY THREAD command output
ā€¢Various trace records, such as IFCIDs 172, 196, 313, and 316
ā€¢Various messages that present thread-info
The 9.1.4, ā€œUsing the client information fieldsā€ on page 178 provides examples and guidelines to exploit the longer client information fields.
9.1.2 Introduction the new client information field Client Correlation Token
DB2 11 introduces a the client correlation token client information field, which is an unique token that allows the application to correlate application work across the distributed transaction. The default value of the client correlation token is the DRDA correlation token, The data type is VARCHAR(255).
Example 9-1 shows the results of the -DIS THD(*) DETAIL command. Note the DSNV442I message.
Example 9-1 -DIS THD(*) DETAIL
DSNV401I -DB1A DISPLAY THREAD REPORT FOLLOWS -
DSNV402I -DB1A ACTIVE THREADS -
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER RA * 6 db2jcc_appli DB2R1 DISTSERV 0131 395
V437-WORKSTATION=9.55.137.33
USERID=DB2R1
APPLICATION NAME=db2jcc_application
V441-ACCOUNTING=JCC036609.55.137.33
V442-CRTKN=::9.55.137.33.52646.CBBF468B1B73
V482-WLM-INFO=DDFBAT:1:3:1
V445-G9378921.CDA6.CBBF468B1B73=395 ACCESSING DATA FOR
( 1)::9.55.137.33
V447--INDEX SESSID A ST TIME
V448--( 1) 38420:52646 W R2 1321311013868
The DB2 DSNV442I message contains detail output from the DISPLAY THREAD command. It provides details about the correlation token after the V442-CRTKN keyword.
The CURRENT CLIENT_CORR_TOKEN special register
The CURRENT CLIENT_CORR_TOKEN special register contains the value of the client correlation token from the client information that is specified for the connection. The data type is VARCHAR(255).
Example 9-2 shows a simple query example that can be used to retrieve the value of this special register using SQL.
Example 9-2 Retrieve the CURRENT CLIENT_CORR_TOKEN value using SQL
SELECT CURRENT CLIENT_CORR_TOKEN
FROM SYSIBM.SYSDUMMY1;
Example 9-3 shows the output of the execution of this query in this environment.
Example 9-3 Value of CURRENT CLIENT_CORR_TOKEN
::9.55.137.33.54132.CBBF794F9C68
The correlation token is made up of three components separated by periods. Its structure is shown in Example 9-4.
Example 9-4 Client correlation token components
ip-address.port-address.unique-id
The the correlation token includes the following components:
ip-address The IP address of the originating requester, which is 3 to 39 characters in length
port-address The port address, which is 1 to 8 characters in length
unique-id An unique logical unit of work identifier, which is 12 characters in length
You can change the value of this special register to a more meaningful value, for example by using one of the following application programming interfaces:
ā€¢SQLE_CLIENT_INFO_PROGRAMID (sqleseti)
ā€¢java.sql.Connection.setClientInfo (JDBC)
ā€¢The RRS DSNRLI SIGNON, AUTH SIGNON, CONTEXT SIGNON, or SET_CLIENT_ID function
Example 9-5 shows how to extract and how to override this special register in a Java program.
Example 9-5 Java and CURRENT CLIENT_CORR_TOKEN
import com.ibm.db2.jcc.DB2Connection;
import java.sql.*;
 
public class DB211NewDriverCorrToken {
 
public static Connection con = null;
public static CallableStatement cstmt;
public static ResultSet results;
public static boolean debug = true;
public static void main(String args[]) throws Exception {
Statement stmt; ResultSet rs; String corr_token;
String url = "jdbc:db2://redbook8:38420/DB1A" +
":user=db2r1;password=******;";
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
} catch (java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
con = DriverManager.getConnection(url);
DB2Connection db2con = (DB2Connection) con;
con.setAutoCommit(false);
// Extract original Correlation Token
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT CURRENT CLIENT_CORR_TOKEN FROM SYSIBM.SYSDUMMY1;");
rs.next(); corr_token = rs.getString(1);
System.out.println("CORR TOKEN = " + corr_token);
rs.close(); stmt.close();
// Override original Correlation Token
db2con.setClientInfo("ClientCorrelationToken","BRXLS_APPCRIS");
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT CURRENT CLIENT_CORR_TOKEN FROM SYSIBM.SYSDUMMY1;");
rs.next();
corr_token = rs.getString(1);
System.out.println("CORR TOKEN = " + corr_token);
rs.close(); stmt.close();
}
}
This program connect to DB2, selects the CURRENT CLIENT_CORR_TOKEN value in a string variable, overrides this information, and prints the new value. Example 9-6 shows the execution results.
Example 9-6 Java program output, overriding the correlation token
CORR TOKEN = ::9.55.137.134.62461.CBC82DBC97F5
CORR TOKEN = BRXLS_APPCRIS
Example 9-7 shows how the -DIS THD(*) DETAIL command provides the correlation token information in DSNV442I message.
Example 9-7 -DIS THD(*) DETAIL and the client correlation token value
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER RA * 8 db2jcc_appli DB2R1 DISTSERV 0133 139
V437-WORKSTATION=192.168.150.1
USERID=db2r1
APPLICATION NAME=db2jcc_application
V441-ACCOUNTING=JCC04170192.168.150.1
V442-CRTKN=BRXLS_APPCRIS
V482-WLM-INFO=DDFBAT:1:3:1
V445-G9378986.F843.CBC840370D33=139 ACCESSING DATA FOR
( 1)::9.55.137.134
V447--INDEX SESSID A ST TIME
V448--( 1) 38420:63555 W R2 1322014211550
In DB2 11, you can override the client correlation token. This option allows you to use this register for application purposes. You can, for example, feedback a program with different data depending on the value of this register.
As an example, consider the simple table created and populated with the information shown in Example 9-8.
Example 9-8 DDL and Insert for example table
CREATE TABLE COD_TAB (BUS_COD INTEGER, CORR_ID VARCHAR(255));
INSERT INTO COD_TAB (BUS_COD, CORR_ID) VALUES (1000,'BRXLS_APPCRIS'),
INSERT INTO COD_TAB (BUS_COD, CORR_ID) VALUES (2000,'ROME_APPCRIS'),
INSERT INTO COD_TAB (BUS_COD, CORR_ID) VALUES (3000,'MILANO_APPCRIS'),
INSERT INTO COD_TAB (BUS_COD, CORR_ID) VALUES (4000,'PARIS_APPCRIS'),
INSERT INTO COD_TAB (BUS_COD, CORR_ID) VALUES (5000,'SJOSE_APPCRIS'),
INSERT INTO COD_TAB (BUS_COD, CORR_ID) VALUES (5000,'MADRID_APPCRIS'),
Example 9-9 shows the contents of the table after the execution of the SQL.
Example 9-9 Contents of example table
---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM COD_TAB;
---------+---------+---------+---------+---------+---------+---------+
BUS_COD CORR_ID
---------+---------+---------+---------+---------+---------+---------+
1000 BRXLS_APPCRIS
2000 ROME_APPCRIS
3000 MILANO_APPCRIS
4000 PARIS_APPCRIS
5000 SJOSE_APPCRIS
5000 MADRID_APPCRIS
DSNE610I NUMBER OF ROWS DISPLAYED IS 6
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+
In this table, the BUS_COD column represents any kind of business information, such as a business code. The CORR_ID column contains the information to be matched with the value of the correlation token. Example 9-10 shows a SQL example where the BUS_COD value is returned based on the information in the special register CURRENT CLIENT_CORR_TOKEN.
Example 9-10 Using the CURRENT CLIENT_CORR_TOKEN in SQL
SELECT BUS_COD
FROM COD_TAB
WHERE CORR_ID = CURRENT CLIENT_CORR_TOKEN
Example 9-11 is a Java implementation of this technique. When invoked, this program receives a certain value as input parameter. This value is used in the program to set the client correlation token information. The value of the CURRENT CLIENT_CORR_TOKEN in the exploited in the embedded SQL.
Example 9-11 Java and SQL exploiting CURRENT CLIENT_CORR_TOKEN
import com.ibm.db2.jcc.DB2Connection;
import java.sql.*;
 
public class DB211NewDriverCorrToken {
 
public static Connection con = null;
public static CallableStatement cstmt;
public static ResultSet results;
public static boolean debug = true;
 
public static void main(String args[]) throws Exception {
System.out.println("Input correlation token = " + args[0]);
Statement stmt;
ResultSet rs;
String corr_token;
String url = "jdbc:db2://redbook8:38420/DB1A"
+ ":user=db2r1;password=******;";
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
} catch (java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
con = DriverManager.getConnection(url);
DB2Connection db2con = (DB2Connection) con;
con.setAutoCommit(false);
// Override original Correlation Token
db2con.setClientInfo("ClientCorrelationToken", args[0]);
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT CURRENT CLIENT_CORR_TOKEN FROM SYSIBM.SYSDUMMY1;");
rs.next();
corr_token = rs.getString(1);
System.out.println("CORR TOKEN = " + corr_token);
rs.close();
stmt.close();
// Use new Correlation Token value
db2con.setClientInfo("ClientCorrelationToken", args[0]);
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT BUS_COD FROM COD_TAB "
+ " WHERE CORR_ID = CURRENT CLIENT_CORR_TOKEN;");
rs.next();
corr_token = rs.getString(1);
System.out.println("BUS CODE = " + corr_token);
rs.close();
stmt.close();
 
}
}
These tests executed the program by passing the BRXLS_APPCRIS value as the parameter. Example 9-12 shows the execution output when running this program.
Example 9-12 Java application execution output
Input correlation token = BRXLS_APPCRIS
CORR TOKEN = BRXLS_APPCRIS
BUS CODE = 1000
This client information field cannot be used for classifying DDF work within WLM.
9.1.3 Introduction of a new built-in session global variable
DB2 11 provides a new built-in session global variable named SYSIBM.CLIENT_IPADDR. This global variable contains the value of the client IP address for the connection, as follows:
ā€¢For remote client connections, the value is the host IP address the application that is used to establish the connection.
ā€¢For local host applications, the value is NULL.
ā€¢For remote host applications, the value is the IP address that is associated with the DB2 subsystem used to establish the connection.
The data type is CHAR(39). SYSIBM.CLIENT_IPADDR displays the TCP/IP IPv6 colon hexadecimal format. For example:
IPv6 : 1111:2222:3333:4444:5555:6666:7777:8888
IPv4 : (9.30.115.135) mapped as IPv6 : 0000:0000:0000:FFFF:9:30:115:135
DB2 obtains TCP/IP IPv6 address value from network, the client does not provide it or set it. DB2 sets this value only if client is using TCP/IP or SSL protocol.
 
Note: The value of SYSIBM.CLIENT_IPADDR is NULL if the client did not connect to TCP/IP or SSL protocol.
SYSIBM.CLIENT_IPADDR can be used for classifying DDF work with WLM using the Client IP Address (CIP) WLM workload qualifier. For DDF workload type, the CIP is the source client IPv6 address associated with the DDF server thread. The maximum length is 39 bytes.
Example 9-13 shows how you can query the DB2 SYSIBM.SYSVARIABLES table to get details about CLIENT_IPADDR.
Example 9-13 Query on SYSIBM.SYSVARIABLES
---------+---------+---------+---------+---------+---------+---------+
SELECT
CAST(SCHEMA AS CHAR(10)) AS SCHEMA,
CAST(NAME AS CHAR(20)) AS NAME,
CAST(TYPENAME AS CHAR(10)) AS TYPE , LENGTH , DEFAULT
FROM SYSIBM.SYSVARIABLES
WHERE NAME = 'CLIENT_IPADDR' WITH UR;
---------+---------+---------+---------+---------+---------+---------+
SCHEMA NAME TYPE LENGTH DEFAULT
---------+---------+---------+---------+---------+---------+---------+
SYSIBM CLIENT_IPADDR CHAR 39 N
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+
9.1.4 Using the client information fields
This section describes how the changes in client information fields are used.
DB2 messages
The following DB2 messages are updated in DB2 11 to take advantage of longer field lengths:
DSNB260I A long-running reader has reached the maximum permitted time without issuing either a COMMIT or ROLLBACK statement.
DSNI031I Lock escalation has occurred for the specified object.
DSNR048I This message is produced periodically during the backout process of an in-abort unit of recovery.
DSNR035I This message indicates that during checkpoint processing, DB2 encountered an uncommitted unit of recovery (UR) that has an inflight or indoubt status.
DSNJ031I A UR has reached the threshold number of log records that were written without a commit or rollback operation.
DSNT318I A plan cannot get an internal resource lock manager (IRLM) lock because the resource is held by a P-lock in the data sharing group, and the maximum amount of time to wait for the locked resource was exceeded.
DSNT375I A plan has been denied an IRLM lock because of a detected deadlock.
DSNT376I A plan has been denied an IRLM lock because of a timeout.
DSNT377I A plan cannot gain an IRLM lock because a required resource is currently undergoing recovery.
DSNT378I A plan cannot get an IRLM lock because the resource is held by a retained lock on behalf of another member in the data sharing group, and the amount of time to wait for the locked resource was exceeded.
Accounting with OMPE1
The START TRACE command starts DB2 traces. You can limit the collection of trace data to particular applications or users and to limit the data collected to particular traces and trace events. You can use trace filters to exclude the collection of trace data from specific contexts and to exclude the collection of specific traces and trace events. The following types of trace filters are available:
ā€¢USERID or XUSERID
Specifies the user ID. Use USERID to constrain the trace to the specified user IDs or XUSERID to exclude the specified user IDs. You can specify multiple values and wildcard values. The value can be up to 16 characters.
ā€¢APPNAME or XAPPNAME
Specifies the application name. Use APPNAME to constrain the trace to the specified applications or XAPPNAME to exclude the specified applications. You can specify multiple values and wildcard values.
ā€¢WRKSTN or XWRKSTN
Specifies the workstation name. Use WRKSTN to constrain the trace to the specified workstations or XWRKSTN to exclude the specified workstations. You can specify multiple values and wildcard values.
 
Note: The START TRACE command filtering parameters, USERID and XUSERID, APPNAME and XAPPNAME, and WRKSTN and XWRKSTN are not be enhanced to support the new longer lengths for the client information fields.
Example 9-14 shows an OMPE JCL example as used in this environment for the creation of the reports exposed in this section.
Example 9-14 OMPE command JCL example
//PE EXEC PGM=FPECMAIN
//STEPLIB DD DISP=SHR,DSN=OMPE.V520.D130306.V11DRP5.TKANMOD
//INPUTDD DD DISP=SHR,DSN=SMFDATA.DB2RECS.G4829V00
//JOBSUMDD DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//ACRPTDD DD SYSOUT=*
//UTTRCDD1 DD SYSOUT=*
//SYSIN DD *
ACCOUNTING
REPORT
LAYOUT(LONG)
INCLUDE(SUBSYSTEM(DB1A))
TRACE
LAYOUT(LONG)
INCLUDE(SUBSYSTEM(DB1A))
EXEC
/*
Example 9-15 shows part of a OMPE Accounting Trace Long report. The report was created using records produced by an application using a supported version of JDBC driver. Being the driver 10.1 fix pack 0 if it is supported by DB2 11. However, this driver cannot use the longer client information fields.
Example 9-15 OMPE Accounting Trace Long - JDBC driver 10.1 fix pack 0
1 LOCATION: DB1A OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R2M0) PAGE: 1-159
GROUP: N/P ACCOUNTING TRACE - LONG REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DB1A ACTUAL FROM: 08/01/13 20:56:51.31
DB2 VERSION: V11
 
---- IDENTIFICATION --------------------------------------------------------------------------------------------------------------
ACCT TSTAMP: 08/01/13 22:38:35.36 PLANNAME: DB211Jav WLM SCL: DDFBAT CICS NET: N/A
BEGIN TIME : 08/01/13 22:38:13.08 PROD TYP: JDBC DRIVER CICS LUN: N/A
END TIME : 08/01/13 22:38:35.36 PROD VER: V4 R13M0 LUW NET: G9378921 CICS INS: N/A
REQUESTER : ::9.55.137.33 CORRNAME: db2jcc_a LUW LUN: D8DE
MAINPACK : DB211Jav CORRNMBR: ppli LUW INS: CBBFAC8093E3 ENDUSER : ClientUser_01234
PRIMAUTH : DB2R1 CONNTYPE: DRDA LUW SEQ: 2 TRANSACT: DB211JavaNewDriver_0123456789012
ORIGAUTH : DB2R1 CONNECT : SERVER WSNAME : WorkstationName_01
Example 9-16 show a report that belongs to the same application but running with the DB2 10.5 fix pack 2 of the driver. In this case, the application is able to exploit the longer fields. In these reports, note the ENDUSER, TRANSACT, and WSNAME fields differences.
Example 9-16 OMPE Accounting Trace Long - JDBC driver 10.5 fix pack 2
1 LOCATION: DB1A OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R2M0) PAGE: 1-148
GROUP: N/P ACCOUNTING TRACE - LONG REQUESTED FROM: NOT SPECIFIED
MEMBER: N/P TO: NOT SPECIFIED
SUBSYSTEM: DB1A ACTUAL FROM: 08/01/13 20:56:51.31
DB2 VERSION: V11
 
---- IDENTIFICATION --------------------------------------------------------------------------------------------------------------
ACCT TSTAMP: 08/01/13 22:36:37.38 PLANNAME: DB211Jav WLM SCL: DDFBAT CICS NET: N/A
BEGIN TIME : 08/01/13 22:36:15.07 PROD TYP: JDBC DRIVER CICS LUN: N/A
END TIME : 08/01/13 22:36:37.38 PROD VER: V4 R17M0 LUW NET: G9378921 CICS INS: N/A
REQUESTER : ::9.55.137.33 CORRNAME: db2jcc_a LUW LUN: D8D9
MAINPACK : DB211Jav CORRNMBR: ppli LUW INS: CBBFAC100F80 ENDUSER : ClientUser_012#1
PRIMAUTH : DB2R1 CONNTYPE: DRDA LUW SEQ: 2 TRANSACT: DB211JavaNewDriver_01234567890#1
ORIGAUTH : DB2R1 CONNECT : SERVER WSNAME : WorkstationName_#1
Example 9-17 shows the JDBC driver correlation section as produced with the SMF records created by the application using the old Java driver.
Example 9-17 JDBC driver correlation: Old Java driver
---- INITIAL DB2 COMMON SERVER OR UNIVERSAL JDBC DRIVER CORRELATION ----------------------------------------------------------------
PRODUCT ID : JDBC DRIVER
PRODUCT VERSION: V4 R13M0
CLIENT PLATFORM: WorkstationName_01
CLIENT APPLNAME: DB211JavaNewDriver_0
CLIENT AUTHID : ClientUs
DDCS ACC.SUFFIX: ClientAccountingInformation_01234567890123456789012345678901234567890123456789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678901234567890123456789012345678901
Example 9-18 shows the same section of the report for the records created using the new Java driver. There is no difference in both cases.
Example 9-18 JDBC driver correlation: New Java driver
---- INITIAL DB2 COMMON SERVER OR UNIVERSAL JDBC DRIVER CORRELATION ----------------------------------------------------------------
PRODUCT ID : JDBC DRIVER
PRODUCT VERSION: V4 R17M0
CLIENT PLATFORM: WorkstationName_01
CLIENT APPLNAME: DB211JavaNewDriver_0
CLIENT AUTHID : ClientUs
DDCS ACC.SUFFIX: ClientAccountingInformation_01234567890123456789012345678901234567890123456789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678901234567890123456789012345678901
However, using the new Java driver results in an expended identification section, as shown in Example 9-19. This example shows how the longer client field information fields are truncated in the OMPE report. This section is not available if the application was executed with the old Java driver.
Example 9-19 OMPE Accounting Trace report, identification section
---- IDENTIFICATION --------------------------------------------------------------------------------------------------------------
ACCT TSTAMP: 08/01/13 22:36:37.38 PLANNAME: DB211Jav WLM SCL: DDFBAT CICS NET: N/A
BEGIN TIME : 08/01/13 22:36:15.07 PROD TYP: JDBC DRIVER CICS LUN: N/A
END TIME : 08/01/13 22:36:37.38 PROD VER: V4 R17M0 LUW NET: G9378921 CICS INS: N/A
REQUESTER : ::9.55.137.33 CORRNAME: db2jcc_a LUW LUN: D8D9
MAINPACK : DB211Jav CORRNMBR: ppli LUW INS: CBBFAC100F80 ENDUSER : ClientUser_012#1
PRIMAUTH : DB2R1 CONNTYPE: DRDA LUW SEQ: 2 TRANSACT: DB211JavaNewDriver_01234567890#1
ORIGAUTH : DB2R1 CONNECT : SERVER WSNAME : WorkstationName_#1
 
--------------------------------------------------------------------------------------------------------------------------------
|TRUNCATED VALUE FULL VALUE |
|ClientUser_012#1 ClientUser_0123456789012345678901234567890123456789012345678901234567890123456789012345678901|
| 23456789012345678901234567890123456 |
|DB211JavaNewDriver_01234567890#1 DB211JavaNewDriver_01234567890123456789012345678901234567890123456789012345678901234567890123|
| 456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456|
| 7890123456789012345678901234567890123456789 |
|WorkstationName_#1 WorkstationName_01234567890123456789012345678901234567890123456789012345678901234567890123456|
| 789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789|
| 0123456789012345678901234567890123456789 |
--------------------------------------------------------------------------------------------------------------------------------
Application and SQL release incompatibility considerations
This section covers Application and SQL release incompatibility considerations that apply to the DB2 11 changes related to client information fields changes.
Truncated client information values
In previous versions of DB2, client information values were truncated and padded to the maximum length. In DB2 11, trailing blanks are removed.
 
Attention: In DB2 11 New Function Mode (NFM), special registers for client information fields might return different length values
When the application compatibility of a package is set to V11R1, the application might receive a different length client information value than in previous versions of DB2. The value is no longer padded to the supported maximum length and trailing blanks are removed.
You can find more information about application compatibility in 6.6, ā€œSET CURRENT APPLICATION COMPATIBILITYā€ on page 114.
SYSPROC.ADMIN_COMMAND_DB2 stored procedure
The DB2 provided SYSPROC.ADMIN_COMMAND_DB2 stored procedure executes DB2 commands on a connected DB2 subsystem, or on a DB2 data sharing group member. This stored procedure also returns the command output messages.
Starting in DB2 11 conversion mode (CM), the ADMIN_COMMAND_DB2 result set row returned in the created SYSIBM.DB2_THREAD_STATUS global temporary table when processing-type = ā€œTHDā€ has changed. The column data type and maximum lengths for WORKSTATION, USERID, APPLICATION, and ACCOUNTING has changed. Existing applications now receive a VARCHAR data type and possibly a different length client information value. The length is no longer padded to the supported maximum length.
Client information special registers length
In DB2 11 NFM, special registers for client information fields might return different length values. The values in these special registers change:
ā€¢CURRENT CLIENT_USERID
ā€¢CURRENT CLIENT_WRKSTNAME
ā€¢CURRENT CLIENT_APPLNAME
ā€¢CURRENT CLIENT_ACCTNG
In addition, the value of these special register change based on the application compatibility level. Whereas in previous version of DB2 special register values were truncated and padded, trailing blanks are removed in DB2 11. In consequence, when the application compatibility for a package is set to V11R1, the application might receive a different length client information value than they did previously.
Special registers
A special register is a storage area that is defined for an application process by DB2 and is used to store information that can be referenced in SQL statements. A reference to a special register is a reference to a value provided by the current server.
The following special registers related to the client information are available:
ā€¢CURRENT CLIENT_ACCTNG contains the value of the accounting string from the client information that is specified for the connection.
ā€¢CURRENT CLIENT_APPLNAME contains the value of the application name from the client information that is specified for the connection.
ā€¢CURRENT CLIENT_CORR_TOKEN contains the value of the client correlation token from the client information that is specified for the connection.
ā€¢CURRENT CLIENT_USERID contains the value of the client user ID from the client information that is specified for the connection.
ā€¢CURRENT CLIENT_WRKSTNNAME contains the value of the workstation name from the client information that is specified for the connection.
Resource Limit Facility
Resource limit tables can be used to limit the amount of resources used by SQL statements that run on middleware servers. Statements can be limited based on this client information:
ā€¢Application name
ā€¢User ID
ā€¢Workstation ID
ā€¢IP address
Resource limits apply only to dynamic SQL statements. The resource limit facility does not control static SQL statements regardless of whether they are issued locally or remotely.
The RLF table DSNRLMTxx columns are changed to support the longer lengths for client information fields, as summarized in Table 9-2.
Table 9-2 DSNRLMTxx longer columns in DB2 11
Column name
DB2 11
Comment
RLFEUAN
VARCHAR(255)
Specifies an application name
RLFEUID
VARCHAR(128)
Specifies a user ID
RLFEUWN
VARCHAR(255)
Specifies a userā€™s workstation name
RLFIP
CHAR(254)
The IP address of the location where the request originated
The DDL use to create the RLMT table (DSNRLMTxx) are provided in the DB2 DSNTIJSG installation job. DB2 11 provides the long field version of the table in DSNTIJSG.
 
Note: The DB2 11 DSNTIJSG installation job provides the DDL for the long field version of the RLMT table, but long fields can be used in NFM only
Example 9-20 shows the long field version DDL as provided in DSNTIJSG in DB2 11. The DDL is commented out in the job.
Example 9-20 DDL to create the RLMT table DSNRLMT01, DB2 11 version
//**********************************************************************
//* USE THE FOLLOWING DDL TO CREATE AN OPTIONAL RLST AND INDEX FOR
//* RLF GOVERNING BASED ON END-USER ID, APPLICATION NAME, WORKSTATION
//* ID, AND IP ADDRESS. SEE THE DB2 PERFORMANCE MONITORING AND TUNING
//* GUIDE FOR MORE INFORMATION ABOUT THIS TABLE.
//*
//* CREATE TABLE DSNRLMT01
//* (RLFFUNC CHAR(1) NOT NULL WITH DEFAULT,
//* RLFEUAN VARCHAR(255) NOT NULL WITH DEFAULT,
//* RLFEUID VARCHAR(128) NOT NULL WITH DEFAULT,
//* RLFEUWN VARCHAR(255) NOT NULL WITH DEFAULT,
//* RLFIP CHAR(254) NOT NULL WITH DEFAULT,
//* ASUTIME INTEGER,
//* RLFASUERR INTEGER,
//* RLFASUWARN INTEGER,
//* RLF_CATEGORY_B CHAR(1) NOT NULL WITH DEFAULT)
//* IN DSNRLST.DSNRLS01;
//*
//* CREATE UNIQUE INDEX DSNMRL01
//* ON DSNRLMT01
//* (RLFFUNC, RLFEUAN DESC, RLFEUID DESC,
//* RLFEUWN DESC, RLFIP DESC)
//* CLUSTER CLOSE NO;
//**********************************************************************
For comparison, Example 9-21 shows the DSNRLMT01 DDL as provided in DSNTIJSG in DB2 10. The DDL is commented out in the job.
Example 9-21 DDL to create the RLMT table DSNRLMT01, DB2 10 version
//**********************************************************************
//* USE THE FOLLOWING DDL TO CREATE AN OPTIONAL RLST AND INDEX FOR
//* RLF GOVERNING BASED ON END-USER ID, APPLICATION NAME, WORKSTATION
//* ID, AND IP ADDRESS. SEE THE DB2 PERFORMANCE MONITORING AND TUNING
//* GUIDE FOR MORE INFORMATION ABOUT THIS TABLE.
//*
//* CREATE TABLE DSNRLMT01
//* (RLFFUNC CHAR(1) NOT NULL WITH DEFAULT,
//* RLFEUAN CHAR(32) NOT NULL WITH DEFAULT,
//* RLFEUID CHAR(16) NOT NULL WITH DEFAULT,
//* RLFEUWN CHAR(18) NOT NULL WITH DEFAULT,
//* RLFIP CHAR(254) NOT NULL WITH DEFAULT,
//* ASUTIME INTEGER,
//* RLFASUERR INTEGER,
//* RLFASUWARN INTEGER,
//* RLF_CATEGORY_B CHAR(1) NOT NULL WITH DEFAULT)
//* IN DSNRLST.DSNRLS01;
//*
//* CREATE UNIQUE INDEX DSNMRL01
//* ON DSNRLMT01
//* (RLFFUNC, RLFEUAN DESC, RLFEUID DESC,
//* RLFEUWN DESC, RLFIP DESC)
//* CLUSTER CLOSE NO;
//**********************************************************************
 
Tip: Verify that the value in RLFAUTH results as the table creator when running these DDL statements. This system parameter defines the authorization ID of the DB2 governor RLF
Table 9-3 summarizes the column definition differences between the DB2 10 and DB2 11 versions of the DSNRLMTxx tables.
Table 9-3 DSNRLMTxx column difference summary DB2 10 versus DB2 11
Column name
DB2 10
DB2 11
Comment
RLFEUAN
CHAR(32)
VARCHAR(255)
Specifies an application name
RLFEUID
CHAR(16)
VARCHAR(128)
Specifies a user ID
RLFEUWN
CHAR(18)
VARCHAR(255)
Specifies a userā€™s workstation name
RLFIP
CHAR(254)
CHAR(254)
The IP address of the location where the request originated
Special considerations with DSNRLMT01 and DB2 versions
Special considerations apply for different combinations of RLMT table and DB2 versions. This section describes the different behaviors observed for different scenarios.
Example 9-22 shows the command used for starting the resource limit facility using the set of RLF tables 01.
Example 9-22 Start Resource Limit Facility command
-STA RLIMIT ID=01
For DB2 11 NFM with DB2 10 version of DSNRLMT01, Example 9-23 shows the output of the START RLIMIT command.
Example 9-23 Successful start of RLF
DSNT704I -DB1A SYSIBM.DSNRLST01 HAS BEEN STARTED FOR THE RESOURCE
LIMIT FACILITY
DSNT704I -DB1A SYSIBM.DSNRLMT01 HAS BEEN STARTED FOR THE RESOURCE
LIMIT FACILITY
DSN9022I -DB1A DSNTCSTR 'START RLIMIT' NORMAL COMPLETION
***
RLF starts correctly when running DB2 11 and the DB2 10 version of the RLF tables.
For DB2 10 NFM with DB2 11 version of DSNRLMT01, Example 9-24 shows the output of the START RLIMIT command.
Example 9-24 Starting RLIMIT in DB2 10 with DSNRLMT01 version DB2 11
DSNT707I -DB0A COLUMN RLFEUAN IN TABLE SYSIBM.DSNRLMT01 IS
INCORRECTLY DEFINED. THE COLUMN DEFINITION IS VARCHAR WITH LENGTH 255
AND NULL ATTRIBUTE N. THE COLUMN DEFINITION SHOULD BE CHAR WITH
LENGTH 32 AND NULL ATTRIBUTE N. THE START RLIMIT COMMAND WILL NOT USE
THIS TABLE.
DSNT707I -DB0A COLUMN RLFEUID IN TABLE SYSIBM.DSNRLMT01 IS
INCORRECTLY DEFINED. THE COLUMN DEFINITION IS VARCHAR WITH LENGTH 128
AND NULL ATTRIBUTE N. THE COLUMN DEFINITION SHOULD BE CHAR WITH
LENGTH 16 AND NULL ATTRIBUTE N. THE START RLIMIT COMMAND WILL NOT USE
THIS TABLE.
DSNT707I -DB0A COLUMN RLFEUWN IN TABLE SYSIBM.DSNRLMT01 IS
INCORRECTLY DEFINED. THE COLUMN DEFINITION IS VARCHAR WITH LENGTH 255
AND NULL ATTRIBUTE N. THE COLUMN DEFINITION SHOULD BE CHAR WITH
LENGTH 18 AND NULL ATTRIBUTE N. THE START RLIMIT COMMAND WILL NOT USE
THIS TABLE.
DSNT704I -DB0A SYSIBM.DSNRLST01 HAS BEEN STARTED FOR THE RESOURCE
LIMIT FACILITY
DSNT727I -DB0A TABLE SYSIBM.DSNRLMT01 WILL NOT BE USED BY THE
RESOURCE LIMIT FACILITY
DSN9022I -DB0A DSNTCSTR 'START RLIMIT' NORMAL COMPLETION
***
This example shows that the DB2 11 version of the RLMT table is not usable in a DB2 10 subsystem. As a result, RLF is partially started and there is no support for the RLMT table in this case. Example 9-25 shows this evidence, this is the output of the -DISPLAY RLIMIT command.
Example 9-25 -DIS RLIMIT: RLF partially started
DSNT700I -DB0A SYSIBM.DSNRLST01 IS THE ACTIVE RESOURCE LIMIT
SPECIFICATION TABLE
DSN9022I -DB0A DSNTCDIS 'DISPLAY RLIMIT' NORMAL COMPLETION
***
For DB2 11 CM with DB2 11 version of DSNRLMT01, Example 9-26 shows the output of the START RLIMIT command.
Example 9-26 Starting RLIMIT in DB2 11 CM with DSNRLMT01 version DB2 11
DSNT728I -DB0B THE FORMAT OF TABLE SYSIBM.DSNRLMT01 IS NOT SUPPORTED
IN COMPATIBILITY MODE. THE START RLIMIT COMMAND WILL NOT USE THIS
TABLE.
DSNT704I -DB0B SYSIBM.DSNRLST01 HAS BEEN STARTED FOR THE RESOURCE
LIMIT FACILITY
DSNT727I -DB0B TABLE SYSIBM.DSNRLMT01 WILL NOT BE USED BY THE
RESOURCE LIMIT FACILITY
DSN9022I -DB0B DSNTCSTR 'START RLIMIT' NORMAL COMPLETION
***
The DB2 11 of the RLMT table is not compatible with DB2 11 CM. There is no support for RLMT in this scenario. Example 9-27 shows that RLF is partially active.
Example 9-27 -DIS RLIMIT: RLF partially started
DSNT700I -DB0B SYSIBM.DSNRLST01 IS THE ACTIVE RESOURCE LIMIT
SPECIFICATION TABLE
DSN9022I -DB0B DSNTCDIS 'DISPLAY RLIMIT' NORMAL COMPLETION
***
Converting resource limit facility (RLF) tables
When migrated to DB2 11 NFM, you have to convert the resource limit facility (RLF) tables to take advantage of the longer client information fields. Follow these steps to alter and existing RLF table. Verify the status of RLF by executing a -DIS RLIMIT command. An output example is shown in Example 9-28.
Example 9-28 -DIS RLIMIT output example
DSNT700I -DB1A SYSIBM.DSNRLST01 IS THE ACTIVE RESOURCE LIMIT
SPECIFICATION TABLE
DSNT700I -DB1A SYSIBM.DSNRLMT01 IS THE ACTIVE RESOURCE LIMIT
SPECIFICATION TABLE
DSN9022I -DB1A DSNTCDIS 'DISPLAY RLIMIT' NORMAL COMPLETION
***
After stopping RLF you can alter the RLMT table to the new format. Example 9-29 shows the SQL used for altering the RLMT table to the DB2 11 NFM supported format.
Example 9-29 ALTER TABLE SYSIBM.DSNRLMT01
ALTER TABLE SYSIBM.DSNRLMT01 ALTER COLUMN RLFEUAN
SET DATA TYPE VARCHAR(255);
ALTER TABLE SYSIBM.DSNRLMT01 ALTER COLUMN RLFEUID
SET DATA TYPE VARCHAR(128);
ALTER TABLE SYSIBM.DSNRLMT01 ALTER COLUMN RLFEUWN
SET DATA TYPE VARCHAR(255);
Converting resource limit facility (RLF) tables, no RLF outage
If your installation cannot afford to run without RLF active, this alternative method allows for a RLMT migration without RLF outage. Using the DB2 11 DDL in DSNTIJSG, create a new RMT table with a non -used ID. For example, create DSNRLMT02 where the current RLMT table is DSNRLMT01. If needed, create an ID=02 of the table DSNRLSTxx as well.
You can copy the RLF definitions from the ID=01 to the ID=02 tables using SQL. Example 9-30 documents the SQL used to copy data from the old to the new version of the RLMT table.
Example 9-30 Copying RLMT data to a DB2 11 version of the table
INSERT INTO SYSIBM.DSNRLMT02
( RLFFUNC, RLFEUAN, RLFEUID, RLFEUWN, RLFIP,
ASUTIME, RLFASUERR, RLFASUWARN, RLF_CATEGORY_B )
SELECT
RLFFUNC, RLFEUAN, RLFEUID, RLFEUWN, RLFIP,
ASUTIME, RLFASUERR, RLFASUWARN, RLF_CATEGORY_B
FROM SYSIBM.DSNRLMT01;
Example 9-31 shows the SQL used to copy data between RLST tables.
Example 9-31 Copying RLST data to a DB2 11 version of the table
INSERT INTO SYSIBM.DSNRLST02
( AUTHID, PLANNAME, ASUTIME, LUNAME, RLFFUNC,
RLFBIND, RLFCOLLN, RLFPKG, RLFASUERR,
RLFASUWARN, RLF_CATEGORY_B )
SELECT
AUTHID, PLANNAME, ASUTIME, LUNAME, RLFFUNC,
RLFBIND, RLFCOLLN, RLFPKG, RLFASUERR,
RLFASUWARN, RLF_CATEGORY_B
FROM SYSIBM.DSNRLST01;
After the new RLST and RLMT tables are populated with the original data, you can switch the RLF definitions without outage. In this example, you can issue the START RLIMIT command using ID=02, as shown in Example 9-32, even if RLF is active with ID=01.
Example 9-32 -STA RLIMIT command
-STA RLIMIT ID=02
 
Tip: While RLF is active, you can switch RLF tables by executing the START RLIMIT command using a new ID.
Example 9-33 shows the output of this command. Note the DSNT709I message. This message informs that a START RLIMIT command was entered and that the facility was already active. The facility remains active and switches from using the old table name to the new one.
Example 9-33 Starting RLIMIT on a new set of RLF tables
DSNT709I -DB1A SYSIBM.DSNRLST02 NOW ACTIVE. SYSIBM.DSNRLST01 WAS OLD
RESOURCE LIMIT SPECIFICATION TABLE
DSNT709I -DB1A SYSIBM.DSNRLMT02 NOW ACTIVE. SYSIBM.DSNRLMT01 WAS OLD
RESOURCE LIMIT SPECIFICATION TABLE
DSN9022I -DB1A DSNTCSTR 'START RLIMIT' NORMAL COMPLETION
***
Profile monitoring for remote threads and connections
Profile tables identify contexts in which DB2 takes particular actions such resource monitoring, subsystem parameter customization, and dynamic SQL stabilization. The contexts might identify statements, threads, or connections based on information about the originating application, system, or user.
A profile is a set of criteria that identifies a particular context on a DB2 subsystem. A profile is defined by a record in the SYSIBM.DSN_PROFILE_TABLE table. The profile tables and related indexes are created by the DSNTIJSG job during DB2 installation or migration.
A complete set of profile tables and related indexes includes the following objects:
ā€¢SYSIBM.DSN_PROFILE_TABLE
ā€¢SYSIBM.DSN_PROFILE_HISTORY
ā€¢SYSIBM.DSN_PROFILE_ATTRIBUTES
ā€¢SYSIBM.DSN_PROFILE_ATTRIBUTES_HISTORY
ā€¢SYSIBM.DSN_PROFILE_TABLE_IX_ALL
ā€¢SYSIBM.DSN_PROFILE_TABLE_IX2_ALL
ā€¢SYSIBM.DSN_PROFILE_ATTRIBUTES_IX_ALL
 
Tip: Refer toDB2 11 for z/OS Managing Performance, SC19-4060 for details about the profile tables
The monitoring functions are defined by inserting rows in the DSN_PROFILE_ATTRIBUTES table. You can specify the following values in the KEYWORD column of the DSN_PROFILE_ATTRIBUTES table:
ā€¢MONITOR THREADS
ā€¢MONITOR CONNECTIONS
ā€¢MONITOR IDLE THREADS
MONITOR THREADS indicates that the profile monitors the total number of concurrent active remote threads according to the following filter criteria defined on SYSIBM.DSN_PROFILE_TABLE:
ā€¢LOCATION
ā€¢IPADDR
ā€¢PRDID
ā€¢ROLE AUTHID
ā€¢COLLID
ā€¢PKGNAME
The system-wide threshold that is defined by the value of the MAXDBAT subsystem parameter continues to apply.
MONITOR CONNECTIONS indicates that the profile monitors the total number of remote connections from TCP/IP requesters. The only filtering criteria is the LOCATION column in the SYSIBM. DSN_PROFILE_TABLE. Nevertheless, you can specify either an IP address or a domain name for its value. The system-wide threshold that is defined by the value of the CONDBAT subsystem parameter continues to apply.
MONITOR IDLE THREADS indicates that the profile monitors the approximate time (in seconds) that an active server thread is allowed to remain idle. A zero value means that matching threads are allowed to remain idle indefinitely.
At a glance, system profile monitoring allows to tailor the values of the following otherwise subsystem-level parameters to the need of any application:
ā€¢MAXDBAT
ā€¢CONDBAT
ā€¢IDTHTOIN
 
Important: By making client information fields longer, DB2 11 provides greater granularity for managing DDF connections, threads, and idle thread timeout.
After profiling is correctly defined, use the -START PROFILE DB2 command to start profile monitoring. Issue a -STOP PROFILE command. to disable all profile functions.
The following DSN_PROFILE_TABLE columns are defined as VARCHAR(255) in DB2 10 for z/OS, but their values are truncated as follows:
ā€¢CLIENT_APPLNAME: truncated to 32 bytes
ā€¢CLIENT_USERID: truncated to 16 bytes
ā€¢CLIENT_WRKSTNNAME: truncated to 18 bytes
There is no change in behavior in DB2 11 conversion mode. The -START PROFILE command continues to truncate the client information fieldā€™s information in the DSN_PROFILE_TABLE, as in DB2 10.
In DB2 11 New Function Mode (NFM), the complete value of columns CLIENT_APPLNAME and CLIENT_WRKSTNNAME, up to 255 bytes, is honoured. The value in column CLIENT_USERID is considered up to 128 bytes.
 
Important: IBM Data Server Driver or Client level DB2 10.5 Fix Pack 2 is required to exploit the enhanced client information fields. Previous levels do not take advantage, even if the DB2 server is running DB2 11 for z/OS NFM
Workload management
For mixed workloads, the general recommendation is to use multiple WLM service classes to differentiate users and applications that have different levels of importance for the business.
A WLM Service class describes a group of work within a workload with similar performance characteristics. A service class is a key construct for WLM. Each service class has at least one period, and each period has one goal. Address spaces and transactions are assigned to service classes using classification rules. Within a workload, a group of work with similar performance requirements can share the same service class.
WLM Report Classes refers to an aggregate set of work for reporting purposes. You can use report classes to analyze the performances of individual workloads running in the same or different service classes. Work is classified into report classes using the same classification rules that are used for classification into service classes. A useful way to contrast report classes to service classes is that report classes are used for monitoring work; service classes are primarily to be used for managing work.
As an example, the following general considerations might apply to your workload environment:
ā€¢Use WLM service classes with percentile response time goals for early periods that have frequent completions of short consumption work.
ā€¢Use WLM service classes with velocity goals for later periods containing work having less-frequent completions and larger, perhaps more varying, resource consumption characteristics.
ā€¢Potentially use a discretionary goal for the last period, which might not be applicable in OLTP environments with high CPU utilization, because it can result in severe DB2 locking issues.
The design of a WLM strategy must match the workload characteristics. For example, operational BI queries are typically numerous and small CPU consumers. Therefore, they have WLM service classes with response time goals and fall into early periods. Alternatively, data mining activity might be less frequent, long-running, and have wide variability in resource consumption. It is therefore likely to be targeted for WLM service classes with velocity goals and later periods.
WLM Classification rules are the filters that WLM uses to associate a transactionā€™s external properties (also called work qualifiers, such as LU name or user ID) with a WLM service class. As a preferred practice, classify each distributed request within WLM. If you do not classify your DDF transactions into specific WLM service classes, they are assigned to the default service class for the DDF workload.
Optionally, you can assign incoming work to a report class. Report classes can be used to report on a subset of transactions running in a single service class but also to combine transactions running in different service classes within one report class.
Figure 9-1 shows a LPAR level CPU utilization report for a given DB2 workload. This report is based on the RMF Workload (type 72) records. In this example, it is not possible to identify how the CPU utilization is distributed by application.
Figure 9-1 RMF Workload, LPAR CPU utilization
For the same workload, it is possible to obtain a more detailed overview of the CPU utilization by using WLM Report Class. Figure 9-2 shows a graph of the same scenario when the CPU utilization report is plotted by Report Class. In this case it is possible to identify which part of the application is active a different periods.
Figure 9-2 RMF Workload, LPAR CPU utilization per WLM Report Class
WLM Classification Rules are the rules you define to categorize work into service classes, and optionally report classes, based on work qualifiers. A work qualifier is what identifies a work request to the system. The first qualifier is the subsystem type that receives the work request.
Table 9-4 list the WLM classification attributes, or qualifiers, that pertain to DB2 DDF threads in z/OS 1.13.
Table 9-4 DDF - WLM classification attributes in z/OS 1.13
Attribute
Classify workload based on
AI
Accounting information
CAI
Client accounting information
CI
DB2 correlation ID of the DDF thread
CN
Collection name
LU
IBM VTAMĀ® LUNAME
NET
VTAM NETID
PK
Name of the first package accessed
PN
Plan name
PR
Stored procedure name
SI
Subsystem instance
SSC
Subsystem collection name
UI
User id
z/OS 2.1 introduces a new WLM classify work request macro service, IWM4CLSY. this new macro supports the new longer lengths client information fields to classify DDF server threads. There are new WLM classification attributes to support the longer lengths client information fields. These new attributes are listed in Table 9-5.
Table 9-5 New DDF - WLM classification attributes in z/OS 2.1
Attribute
Classify workload based on
Maximum length
CUI
Client user ID
128 bytes
CWN
Client workstation name
255 bytes
CTN
Client transaction (application) name
255 bytes
CIP
Client IP address
39 bytes
CAI
Client accounting information
512 bytes
PC
Process name
32 bytes
SPM
Subsystem parameter
255 bytes
 
Note: Refer to the IBM publication ā€œz/OS MVS Planning Workload Managementā€ for more details about WLM and its components.
The SPM qualifier has a maximum length of 255 bytes. The first 16 bytes contain the client's user ID. The next 18 bytes contain the client's workstation name. The remaining 221 bytes are reserved. If the length of the client's user ID is less than 16 bytes, SPM uses blanks after the user ID to pad the length. If the length of the client's workstation name is less than 18 bytes, SPM uses blanks after the workstation name to pad the length. The values of the client user ID and client workstation name are the truncated to 16 bytes and 18 bytes respectively. The full lengths, 128 bytes and 255 bytes respectively, are specified in the CUI and CWN attributes, as listed in Table 9-5.
Example 9-34 shows an example of the WLM ISPF panel Modify Rules for the Subsystem Type in a z/OS 2.1 LPAR.
Example 9-34 WLM Modify Rules for the Subsystem Type panel
. . . . . . . . . . . . . . . . . . . . . . . . .
Subsystem-Type Xref Notes Options Help
--------------------------------------------------------------------------
Modify Rules for the Subsystem Type Row 1 to 2 of 2
Command ===> ___________________________________________ Scroll ===> PAGE
Subsystem Type . : DDF Fold qualifier names? N (Y or N)
Description . . . DDF clasificatoin rules
Action codes: A=After C=Copy M=Move I=Insert rule
B=Before D=Delete row R=Repeat IS=Insert Sub-rule
More ===>
--------Qualifier-------- -------Class--------
Action Type Name Start Service Report
DEFAULTS: DDFUNKWN REPUNKWN
____ 1 SI DB1D* ___ DDFDEFLT REPDEFLT
____ 2 AI Bxls* 56 DDFHI REPBXLS
In this example, if a DDF work request comes in from an DB2 subsystem other than DB1D, then it is assigned to the default Service and Report Class DDFUNKWN and REPUNKWN. A request coming from DB1D with Accounting Information starting with the string Bxls gets assigned to DDFHI and REPBXLS. The order of the nesting and the order of the level 1 qualifiers, determine the hierarchy of the classification rules.
Example 9-35 uses a Java application to drive the distributed workload. In the program, the accounting information is set.
Example 9-35 Setting accounting information in a Java program
con = DriverManager.getConnection(url);
DB2Connection db2con = (DB2Connection) con;
con.setAutoCommit(false);
db2con.setClientInfo("ClientAccountingInformation", "BxlsAPP_Choco01");
 
Important: The start of the Client Accounting Information string has to be 56 in the WLM ISPF panel to match the beginning of the string used in the program
In the ā€œFold qualifier namesā€ option, set to the default Y, means that the qualifier names is folded to uppercase as soon as you type them and press Enter. If you set this option to N, the qualifier names remains in the case they are typed in. Leave this option set to Y unless you know that you need mixed case qualifier names in your classification rules.
You can use masking and wild card notation to group qualifiers that share a common substring. For work qualifiers that run longer than eight characters, you can use a start position to indicate how far to index into the character string. The name field for work qualifiers is 8 characters long. You can use nesting for the work qualifiers that run longer than 8 characters. Use the following fields:
ā€¢Accounting information
ā€¢Client accounting information
ā€¢Client IP address
ā€¢Client transaction name
ā€¢Client user ID
ā€¢Client workstation name
ā€¢Collection name
ā€¢Correlation information
ā€¢Package name
ā€¢Procedure name
ā€¢Process name
ā€¢Scheduling environment
ā€¢Subsystem parameter
ā€¢zEnterprise service class name
From this list, the fields Scheduling environment and zEnterprise service class name are not applicable for DDF workloads.
For example, for DDF workload, Accounting Information is the value of the DB2 accounting string associated with the DDF server thread. Because DB2 supports more than 8 characters in accounting information, and the WLM ISPF application allows only 8 characters per rule, the application allows ā€œnestingā€ for accounting information.
By nesting work qualifiers you can exploit the longer client information fields introduced in DB2 11. Example 9-36 shows an example.
Example 9-36 WLM classification rules: nesting accounting information
Subsystem-Type Xref Notes Options Help
--------------------------------------------------------------------------
Modify Rules for the Subsystem Type Row 1 to 3 of 3
Command ===> ___________________________________________ Scroll ===> PAGE
Subsystem Type . : DDF Fold qualifier names? N (Y or N)
Description . . . DDF clasificatoin rules
Action codes: A=After C=Copy M=Move I=Insert rule
B=Before D=Delete row R=Repeat IS=Insert Sub-rule
More ===>
--------Qualifier-------- -------Class--------
Action Type Name Start Service Report
DEFAULTS: DDFUNKWN REPUNKWN
____ 1 SI DB1D* ___ DDFDEFLT REPDEFLT
____ 2 AI BxlsAPP* 56 DDFHI REPBXLS
____ 3 AI Choco* 63 DDFHI REPCHOCO
In this configuration, and application with accounting information starting with the string BxlsAPP is classified in the DDFHI service class and REPBXLS report class. If the application has an accounting information field starting with BxlsAPP_Choco uses the REPCHOCO report class instead.
To activate the WLM changes use the Utilities menu entry, and then option 1 Install definition, as shown in Example 9-37.
Example 9-37 installing WLM definitions
File Utilities Notes Options Help
----- +-------------------------------------------------+ ----------------
Funct | 1 1. Install definition | Appl LEVEL029
Comma | 2. Extract definition | _______________
| 3. Activate service policy |
Defin | 4. Allocate couple data set |
| 5. Allocate couple data set using CDS values |
Defin | 6. Validate definition |
Descr +-------------------------------------------------+ _
Select one of the
following options. . . . . ___ 1. Policies
2. Workloads
3. Resource Groups
When installation is successful, the system provide the feedback shown in Example 9-38
Example 9-38 WLM Service definition installation successful
Service definition was installed. (IWMAM038)
Changes to the WLM policy have to be activated to be made effective.
Example 9-39 shows how the WLM policy activation can be done using the WLM ISPF panels.
Example 9-39 Activating WLM definitions
File Utilities Notes Options Help
----- +-------------------------------------------------+ ----------------
Funct | 3 1. Install definition | Appl LEVEL029
Comma | 2. Extract definition | _______________
| 3. Activate service policy |
Defin | 4. Allocate couple data set |
| 5. Allocate couple data set using CDS values |
Defin | 6. Validate definition |
Descr +-------------------------------------------------+ _
Select one of the
following options. . . . . ___ 1. Policies
2. Workloads
3. Resource Groups
As confirmation, the IWM001I system message is written in to system console, as shown in Example 9-40.
Example 9-40 WLM Policy activated
IWM001I WORKLOAD MANAGEMENT POLICY DB211TO NOW IN EFFECT
There are many ways of verifying of the WLM classification is working as expected. A simple and quick way is to explore the DDF activity in the RMF Enclave Report panel. The Enclave report provides detailed information about the activities of enclaves.
Example 9-41 shows an example of the RMF Enclave Report panel in z/OS 2.1.
Example 9-41 RMF Enclave Report panel
RMF V2R1 Enclave Report Line 1 of 5
Command ===> Scroll ===> CSR
Samples: 100 System: SC76 Date: 08/07/13 Time: 19.36.40 Range: 100 Sec
Current options: Subsystem Type: ALL -- CPU Util --
Enclave Owner: Appl% EAppl%
Class/Group: 0.2 1.9
Enclave Attribute CLS/GRP P Goal % D X EAppl% TCPU USG DLY IDL
*SUMMARY 0.811
ENC00002 SYSSTC 1 N/A Y 0.736 12.62 34 3.0 0.0
ENC00004 DDFHI 2 50 0.040 0.100 100 0.0 0.0
ENC00003 DDFHI 2 50 W 0.036 0.083 0.0 0.0 0.0
ENC00001 SYSSTC 1 N/A Y 0.000 0.025 0.0 0.0 0.0
This example shows the ENC00004 and ENC00003 enclaves running on the DDFHI service class. Selecting one of the enclaves provide access to the Enclave Classification Attributes panel where you can obtain more details, as shown in Example 9-42.
Example 9-42 Enclave details in RMF Enclave report
RMF V2R1 Enclave Report Line 1 of 5
Command ===> Scroll ===> CSR
Samples: +-------------------------------------------------------------------+
| RMF Enclave Classification Attributes |
Current o | |
| The following details are available for enclave ENC00004 |
| Press Enter to return to the Report panel. |
| |
Enclave | More: + |
| Subsystem Type: DDF Owner: DB1DDIST System: SC76 |
*SUMMARY | Accounting Information . . . : |
ENC00002 | JCC04170192.168.150.1 BxlsA |
ENC00004 | PP_Choco01 |
ENC00003 | |
ENC00001 | Collection Name . . . . . . : NULLID |
| Connection Type . . . . . . : SERVER |
| Correlation Information . . : db2jcc_appli |
| LU Name . . . . . . . . . . : |
| Net ID . . . . . . . . . . . : |
| Plan Name . . . . . . . . . : DISTSERV |
| Priority . . . . . . . . . . : |
| Process Name . . . . . . . . : db2jcc_application |
| Transaction/Job Class . . . : |
| Transaction/Job Name . . . . : |
| User ID . . . . . . . . . . : DB2RS1 |
Navigating down this panels provide access to the Client Information Field values. A partial example is shown in Example 9-43.
Example 9-43 RMF Enclave Classification Attributes
RMF V2R1 Enclave Report Line 1 of 5
Command ===> Scroll ===> CSR
Samples: +-------------------------------------------------------------------+
| RMF Enclave Classification Attributes |
Current o | |
| The following details are available for enclave ENC00004 |
| Press Enter to return to the Report panel. |
| |
Enclave | More: - + |
| |
*SUMMARY | |
ENC00002 | Client IP Address . . . . . : |
ENC00004 | 0000:0000:0000:0000:0000:0000:9.55.137. |
ENC00003 | Client User ID . . . . . . . : |
ENC00001 | db2rs1 |
| |
| |
| Client Transaction Name . . : |
| db2jcc_application |
| |
| |
| |
| |
| Client Workstation/Host Name : |
| 192.168.150.1 |
As discussed in this chapter, the driver version can influence the length of the client information field that is sent to the DB2 server. For example, a Java program setting the client accounting information with a string of 255 characters sends the complete string when using a DB2 driver 10.5 fix pack 2. The same application executed with a lower version of the driver sends the string truncated to 200 characters. WLM classification rules taking advantage of the longer client information fields introduced in DB2 11 behave different if the driver used by the applications is not the one required to fully exploit the DRDA changes in DB2 11 (9.5 fix pack 2).
 
Important: With DB2 11 longer client information fields, WLM classification rules might behave inconsistently, depending on the driver version used by the applications.
Setting Client info fields
This section discusses these topics related to how to change the Client info values:
ā€¢WLM_SET_CLIENT_INFO stored procedure
ā€¢Setting Client info in Java applications
ā€¢Setting Client info in DB2 command line processor scripts
WLM_SET_CLIENT_INFO stored procedure
WLM_SET_CLIENT_INFO is a DB2 provided, WLM established, stored procedure. It allows the caller to set client information that is associated with the current connection at the DB2 for z/OS server. It is of particular use for connections where DB2 for z/OS is the requester because in such situations there is no other way of setting the client information values. These DB2 for z/OS client special registers can be changed by calling WLM_SET_CLIENT_INFO:
ā€¢CURRENT CLIENT_ACCTNG
ā€¢CURRENT CLIENT_USERID
ā€¢CURRENT CLIENT_WRKSTNNAME
ā€¢CURRENT CLIENT_APPLNAME
 
Important: The WLM_SET_CLIENT_INFO stored procedure that is shipped with DB2 11 for z/OS does not allow to change the value of the new client information register CURRENT CLIENT_CORR_TOKEN, nor to update the system built-in session global variable SYSIBM.CLIENT_IPADDR.
Example 9-44 shows the WLM_SET_CLIENT_INFO call syntax.
Example 9-44 WLM_SET_CLIENT_INFO syntax
>>-WLM_SET_CLIENT_INFO--(--+-client_userid-+--,--+-client_wrkstnname-+--,-->
'-NULL----------' '-NULL--------------'
 
>--+-client_applname-+--,--+-client_acctstr-+--)---------------><
'-NULL------------' '-NULL-----------'
The WLM_SET_CLIENT_INFO procedure uses the following parameters:
client_userid A VARCHAR(255) input parameter that specifies the user ID for the client. If NULL is specified, the value remains unchanged. If an empty string is specified, the user ID for the client is reset to the default value. If the value specified exceeds 128 bytes, it is truncated to 128 bytes.
client_wrkstnname A VARCHAR(255) input parameter that specifies the workstation name for the client. If NULL is specified, the value remains unchanged. If an empty string is specified, the workstation name for the client is reset to the default value.
client_applname A VARCHAR(255) input parameter that specifies the application name for the client. If NULL is specified, the value remains unchanged. If an empty string is specified, the application name for the client is reset to the default value.
client_acctstr A VARCHAR(255) input parameter that specifies the accounting string for the client. If NULL is specified, the value remains unchanged. If an empty string is specified, the accounting string for the client is reset to the default value.
Setting Client info in Java applications
Table 9-6 summarizes the client information properties values for Java type 4 connectivity to DB2 for z/OS. It also shows the default values and the DB2 special register that can be used to read this information at run time.
Table 9-6 Client information property values for type 4 connectivity to DB2 for z/OS
Name
Max length
Default value
Special register
ApplicationName
255 bytes
The string ā€œdb2jcc_applicationā€
CURRENT CLIENT_APPLNAME
ClientAccountingInformation
255
JCCversionclient-ip
CURRENT CLIENT_ACCTNG
ClientCorrelationToken
255
Data server generated LUWID
CURRENT CLIENT_CORR_TOKEN
ClientHostname
255
The string ā€œdb2jcc_localā€
CURRENT CLIENT_WRKSTNNAME
ClientUser
128
The user ID that was specified when the connection was established
CURRENT CLIENT_USERID
These properties can be modified using the setClientInfo Java method. Example 9-45 shows how to setup the client user ID using this method. In this example, db2con is an established connection to DB2 for z/OS.
Example 9-45 Using the setClientInfo Java method
db2con.setClientInfo("ClientUser", "ClientUser_012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789");
Example 9-46 shows a fully functional Java program that can be used to test the new lengths provided by DB2 11. This example sets 4 client information fields and execute some SQL code.
Example 9-46 Java program: setting client information fields
import com.ibm.db2.jcc.DB2Connection;
import java.sql.*;
 
public class DB211NewDriver {
 
public static Connection con = null;
public static CallableStatement cstmt;
public static ResultSet results;
public static boolean debug = true;
 
public static void main(String args[]) throws Exception {
String url = "jdbc:db2://redbook8:38420/DB1A"
+ ":user=db2r1;password=********;"
+ "traceLevel="
+ (com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL) + ";"
+ "traceFile=c:\work\Author\Redbook#8\DRDA_traces\DB211NewDriver.trace;";
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
} catch (java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
con = DriverManager.getConnection(url);
DB2Connection db2con = (DB2Connection) con;
con.setAutoCommit(false);
 
db2con.setClientInfo("ClientUser", "ClientUser_012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789");
db2con.setClientInfo("ClientAccountingInformation", "ClientAccountingInformation_012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789");
db2con.setClientInfo("ClientHostname", "WorkstationName_012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789");
db2con.setClientInfo("ApplicationName", "DB211JavaNewDriver_012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789");
db2con.setClientInfo("ClientCorrelationToken", "BXLS001");
 
query1(con);
query2(con);
}
 
public static void query1(Connection con) {
System.out.println("Starting DB211NewDriver V1");
Statement stmt;
ResultSet rs;
String planname;
try {
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT NAME FROM SYSIBM.SYSPLAN WHERE PROGAUTH = 'D'");
System.out.println("--> Query executed. resultset follows");
while (rs.next()) {
planname = rs.getString(1);
System.out.println("PLAN NAME = " + planname);
// Force 1 second sleep for easier monitoring
try {
Thread.sleep(1000);
} catch (InterruptedException ex) {
Thread.currentThread().interrupt();
}
}
System.out.println("--> Resultset exhausted");
rs.close();
stmt.close();
} catch (SQLException e) {
System.out.println("==> SQLException = " + e);
System.out.println("==> SQLCODE = " + e.getErrorCode());
System.out.println("==> SQLSTATE = " + e.getSQLState());
System.out.println("==> Text of Error Message = " + e.getMessage());
}
}
 
public static void query2(Connection con) {
System.out.println("Starting query2");
Statement stmt;
ResultSet rs;
String currclntacctng;
try {
stmt = con.createStatement();
rs = stmt.executeQuery("select CURRENT CLIENT_ACCTNG from sysibm.sysdummy1;");
System.out.println("--> Query executed. resultset follows");
while (rs.next()) {
currclntacctng = rs.getString(1);
System.out.println("CURRENT CLIENT_ACCTNG = " + currclntacctng);
// Force 1 second sleep for easier monitoring
try {
Thread.sleep(1000);
} catch (InterruptedException ex) {
Thread.currentThread().interrupt();
}
}
System.out.println("--> Resultset exhausted");
rs.close();
stmt.close();
} catch (SQLException e) {
System.out.println("==> SQLException = " + e);
System.out.println("==> SQLCODE = " + e.getErrorCode());
System.out.println("==> SQLSTATE = " + e.getSQLState());
System.out.println("==> Text of Error Message = " + e.getMessage());
}
}
}
 
Tip: To keep an active connection with DB2 during the complete execution of the program and to make easier to monitor it online using commands, disable Autocommit by modifying the connection with con.setAutoCommit(false);
Example 9-47 shows the output of the execution of this program in this example test environment.
Example 9-47 Java sample program output
Starting DB211NewDriver V1
--> Query executed. resultset follows
PLAN NAME = DSNTIA11
PLAN NAME = DSNREXX
PLAN NAME = DSNESPCS
....
PLAN NAME = ADB2RIP
PLAN NAME = ADB2WCL
PLAN NAME = ADB27SPC
--> Resultset exhausted
Starting query2
--> Query executed. resultset follows
CURRENT CLIENT_ACCTNG = ClientAccountingInformation_012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
--> Resultset exhausted
Example 9-48 shows the result of the DB2 command -DIS THD(*) DETAIL when the type 4 driver is DB2 10.5 fix pack 2.
Example 9-48 -DIS THD(*) DETAIL
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER RA * 2 db2jcc_appli DB2R1 DISTSERV 0131 621
V437-WORKSTATION=WorkstationName_012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678901234567
89012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789
USERID=ClientUser_0123456789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678901234567
890123456
APPLICATION NAME=DB211JavaNewDriver_0123456789012345678901234567
89012345678901234567890123456789012345678901234567890123456789
01234567890123456789012345678901234567890123456789012345678901
2345678901234567890123456789012345678901234567890123456789
V441-ACCOUNTING=ClientAccountingInformation_0123456789012345678901234
5678901234567890123456789012345678901234567890123456789012345678
9012345678901234567890123456789012345678901234567890123456789012
345678901234567890123456789012345678901234567890123456789
V442-CRTKN=BXLS001
V482-WLM-INFO=DDFBAT:1:3:1
V445-G9378921.D8D9.CBBFAC100F80=621 ACCESSING DATA FOR
( 1)::9.55.137.33
V447--INDEX SESSID A ST TIME
V448--( 1) 38420:55513 W R2 1321318355007
DB2 DSNV436I message contains detail output from the DISPLAY THREAD command, which is part of the DSNV401I message. If a thread is processing an SQL statement, the output includes the following information about the SQL statement and the program that contains the statement.
Example 9-49 illustrates the output of the -DIS THD(*) DETAIL command while a Java application was executing a SQL statement. This results in the inclusion of message V436 in the output.
Example 9-49 DIS THD(*) DETAIL and message V436
-DB1A DIS THD(*) DET
DSNV401I -DB1A DISPLAY THREAD REPORT FOLLOWS - DSNV402I -DB1A ACTIVE THREADS - 717
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER RA * 2 db2jcc_appli DB2R6 DISTSERV 00A2 839
V437-WORKSTATION=9.55.137.146
USERID=db2r6
APPLICATION NAME=db2jcc_application
V441-ACCOUNTING=JCC041709.55.137.146
V436-PGM=NULLID.SYSLH200, SEC=1, STMNT=0, THREAD-INFO=DB2R6:9.55.137.
146:db2r6:db2jcc_application:DYNAMIC:59:*:<BRXLS_APPCRIS>
V442-CRTKN=BRXLS_APPCRIS
V482-WLM-INFO=DDFDEF:2:4:*
V445-G9378992.CB66.CBF19D605478=839 ACCESSING DATA FOR
( 1)::9.55.137.146
V447--INDEX SESSID A ST TIME
V448--( 1) 38420:52070 S2 1325311562154
In V436 message, the section THREAD-INFO provides information about the thread presented in a colon-delimited list that contains the following segments:
ā€¢The primary authorization ID that is associated with the thread.
ā€¢The name of the user's workstation.
ā€¢The ID of the user.
ā€¢The name of the application.
ā€¢The statement type for the currently executing statement: dynamic or static.
ā€¢The statement identifier for the currently executing statement, if available. The statement identifier can be used to identify the particular SQL statement.
ā€‰ā€“ For static statements, the statement identifier correlates to the STMT_ID column in the SYSIBM.SYSPACKSTMT table.
ā€‰ā€“ For dynamic statements, the statement identifier correlates to the STMT_ID column in the DSN_STATEMENT_CACHE_TABLE table
ā€¢The name of the role that is associated with the thread.
ā€¢The correlation token that can be used to correlate work at the remote system with work that runs at the DB2 subsystem. The default correlation token, if available, is enclosed in < and > characters, and contains three components, which are separated by periods:
ā€‰ā€“ A 3 - 39 character IP address
ā€‰ā€“ A 1 - 8 character port address
ā€‰ā€“ A 12 character unique identifier
Example 9-50 shows the output of -DIS THD(*) DETAIL for the same scenario, with the original application still running but not executing a query in DB2. This results in V436 not be presented in the output.
Example 9-50 DIS THD(*) DETAIL and message V436 missing
-DB1A DIS THD(*) DET DSNV401I -DB1A DISPLAY THREAD REPORT FOLLOWS - DSNV402I -DB1A ACTIVE THREADS - 721
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER RA * 3 db2jcc_appli DB2R6 DISTSERV 00A2 839
V437-WORKSTATION=9.55.137.146
USERID=db2r6
APPLICATION NAME=db2jcc_application
V441-ACCOUNTING=JCC041709.55.137.146
V442-CRTKN=BRXLS_APPCRIS
V482-WLM-INFO=DDFDEF:2:4:20
V445-G9378992.CB66.CBF19D605478=839 ACCESSING DATA FOR
( 1)::9.55.137.146
V447--INDEX SESSID A ST TIME
V448--( 1) 38420:52070 N R2 1325311562639
9.2 Cancel thread and cancel SQL statement improvements
This section discusses the following improvements for distributed application accessing DB2 11 for z/OS:
9.2.1 Changes in Cancel DDF thread
The DB2 CANCEL THREAD command cancels processing for specific local or distributed threads. The DDF option is used to identify distributed threads for which you want to cancel processing.
In previous versions of DB2 for z/OS, the DDF cancel command and the SQL Cancel command might not work to cancel hung threads or interrupt long running SQL statements. DB2 11 for z/OS introduces the following improvements:
ā€¢Enhance the DDF cancel command to use a new z/OS function to terminate a preemptable SRB
ā€¢Remove the restrictions preventing the SQL Cancel from interrupting any long running SQL statement
The DB2 for z/OS CANCEL THREAD command is a reactive command by design. A DB2 thread is flagged only as being canceled. The thread processing continues until it reaches a cancel detection point where the thread reacts by abnormally terminating itself. These cancel detection points are numerous and strategically distributed in the DB2 code.
This reactive cancel behavior is usually sufficient and successful, but there are cases where the continued processing of the thread might be such that a cancel detection point might not be encountered in a timely manner, or not at all, including in the following cases:
ā€¢Relatively tight loops in DB2 processing due to the extensive processing nature of the a SQL statement
ā€¢Relatively tight loops in DB2 processing due to a DB2 logic error
In these cases, the reactive nature of the DB2 CANCEL THREAD command is ineffective and a more proactive cancel behavior would be more reliable.
To satisfy this requirement, a new z/OS function allows to terminate DBAT related SRB and Enclave processing in a way that allows for DB2 functional recovery. A new z/OS CALLRTM TYPE=SRBTERM service is provided in z/OS 1.13 to allow the DB2 CANCEL THREAD command processing to proactively cancel the thread when it is executing under an SRB.
 
Important: z/OS CALLRTM TYPE=SRBTERM service is provided in z/OS 2.1 or z/OS 1.13 retrofitted through APAR OA39392.
To allow for this, the CANCEL THREAD command in DB2 11 includes a new FORCE option. Example 9-51 shows the syntax of the CANCEL THREAD command in DB2 11. Note the addition of the new FORCE option.
Example 9-51 CANCEL THREAD command in DB2 11
>>-CANCEL--+-THREAD(token)-----------+--+------+--+-------+----->
'-DDF THREAD(-+-luwid-+-)-' '-DUMP-' '-LOCAL-'
'-token-'
 
>--+-----------+--+-------+------------------------------------><
'-NOBACKOUT-' '-FORCE-'
Use the FORCE option to instruct DB2 to attempt to purge the thread of a remote connection in the DB2 server. The FORCE option is accepted only after a request to CANCEL THREAD is issued without the FORCE option.
 
Attention: The FORCE option can potentially affect theDB2 subsystem. Use it to cancel threads that impact the DB2 subsystem and cannot be canceled without FORCE.
 
Important: Even with the FORCE option, sensitive processing is still shielded to protect the subsystem. In these situations, FORCE might not work as expected.
You can use the DISPLAY THREAD command to display information by location. Example 9-52 shows the results of the DISPLAY THREAD(*) LOCATION(*) command.
Example 9-52 -DISPLAY THREAD(*) LOCATION(*) command
DSNV401I -DB1A DISPLAY THREAD REPORT FOLLOWS -
DSNV402I -DB1A ACTIVE THREADS -
NAME ST A REQ ID AUTHID PLAN ASID TOKEN
SERVER RA * 4 db2jcc_appli DB2R1 DISTSERV 0130 60
V437-WORKSTATION=9.55.137.139
USERID=db2r1
APPLICATION NAME=db2jcc_application
V442-CRTKN=::9.55.137.139.52107.CBC10606A950
V445-G937898B.CB8B.CBC10606A950=60 ACCESSING DATA FOR
::9.55.137.139
DISPLAY ACTIVE REPORT COMPLETE
DSN9022I -DB1A DSNVDT '-DISPLAY THREAD' NORMAL COMPLETION
***
In this example, the thread token that is assigned to the thread is 60, as shown under the TOKEN keyword. The CANCEL DDF THREAD command syntax accepts either a thread token or a thread luwid as input, as shown in Example 9-53.
Example 9-53 CANCEL DDF THREAD command syntax
-CANCEL DDF THREAD (token or luwid)
As from DB2 11, the CANCEL DDF THREAD commands accepts the FORCE option. For this example, when using the thread token as input parameter, this command can be written as shown in Example 9-54.
Example 9-54 CANCEL DDF THD FORCE example
-CAN DDF THD(60) FORCE
 
Note: The CANCEL THREAD command has no effect if the thread is not active or suspended in DB2.
Example 9-55 shows the DB2 feedback after the execution of this command.
Example 9-55 DB2 11 new message DSNV519I
DSNV519I -DB1A DSNLCNCL CANCEL THREAD COMMAND WITH FORCE OPTION FOR
'60' HAS COMPLETED WITH RETURN CODE X'0001'
***
DB2 11 introduces the DSNV519I message. This message is provided when the CANCEL THREAD FORCE command is issued and indicates the success or failure of the command through a return code. That is: to determine the successful or failure of this command, you have to interpret the return code provided by DSNV519I.
Example 9-56 shows the structure of the message DSNV519I.
Example 9-56 Structure of DB2 message DSNV519I
CANCEL THREAD COMMAND WITH FORCE OPTION FOR token-id HAS COMPLETED WITH RETURN CODE return-code
The DSNV519I message provides this information:
token-id Either a thread identifier or a logical unit of work identifier (luwid) returned from the DISPLAY THREAD command
return-code A numeric value that indicates the success or failure of the CANCEL THREAD command
The return-code can use the following possible values:
Xā€™0000ā€™ The CANCEL THREAD command successfully completed.
Xā€™0001ā€™ The CANCEL THREAD command was not accepted. The FORCE option is not allowed until a CANCEL THREAD without the FORCE option is first attempted.
Xā€™0002ā€™ The CANCEL THREAD command was not accepted. The CANCEL THREAD command with the FORCE option for the same token-id cannot be repeated.
Xā€™0003ā€™ The CANCEL THREAD command was not accepted. The token-id cannot be found.
Xā€™0004ā€™ The CANCEL THREAD command was not accepted. The token-id is associated with a DDF disconnected DBAT on the DB2 server.
Example 9-55 receives an Xā€™0001ā€™ return code when executing the CANCEL THD command in Example 9-54. This return code is a consequence of using the FORCE option before executing a non-FORCE command. At this point, no action against the target thread has been performed by DB2. In this scenario the only next option to cancel this tread is then to execute a non-FORCE CANCEL THD command as shown in Example 9-57.
Example 9-57 CANCEL DDF THREAD command
-CAN DDF THD(60)
Example 9-58 shows the DB2 feedback on the execution of this command.
Example 9-58 CANCEL THREAD command output example
DSNL010I -DB1A DDF THREAD '60' HAS BEEN CANCELLED
***
In this example, the thread was effectively and immediately cancelled. This can be confirmed by a DISPLAY THREAD command, or by inspecting the DB2 MSTR address space feedback. Example 9-59 shows how the termination of the thread is reported in the MSTR address space of this example DB2 subsystem.
Example 9-59 Cancelled thread: DB2 MSTR feedback
20.24.21 STC06973 DSNL027I -DB1A SERVER DISTRIBUTED AGENT WITH 220
220 LUWID=G937898B.CB8B.CBC10606A950=60
220
220 THREAD-INFO=DB2R1:9.55.137.139:db2r1:db2jcc_application:*:*:*:<::9.55
220 .137.139.52107.CBC10606A950>
220 RECEIVED ABEND=04E
220 FOR REASON=00D3001A
20.24.21 STC06973 DSNL028I -DB1A G937898B.CB8B.CBC10606A950=60 221
221 ACCESSING DATA FOR
221 LOCATION ::9.55.137.139
221 IPADDR ::9.55.137.139
20.24.21 STC06973 DSNL511I -DB1A DSNLIENO TCP/IP CONVERSATION FAILED 222
222 TO LOCATION ::9.55.137.139
222 IPADDR=::9.55.137.139 PORT=52107
222 SOCKET=SENDMSG RETURN CODE=3448 REASON CODE=00000000
The 00D3001A reason code indicates that a CANCEL DDF THREAD command naming a distributed thread caused the thread to be terminated. If the thread not being cancelled because of the reason discussed previously in this section, a CANCEL DDF THREAD option FORCE would be accepted by DB2.
9.2.2 Changes in SQL statement interruption processing
Prior to DB2 for z/OS Version 8, the only way to interrupt SQL statement processing that was executing on behalf of a remote application was for the remote application to terminate its connection to the DB2 for z/OS server. This interrupted the SQL statement by terminating the entire DB2 for z/OS server thread (DBAT) and all SQL in the transaction were also aborted.
To allow applications to remain connected to the DB2 for z/OS sever, DB2 for z/OS V8 introduced the ability to interrupt the operation of individual SQL statements. DB2 returns an SQLCODE indicating that the specific SQL statement was canceled, while maintaining the connection with the remote application and the effects of all previous SQL in the transaction.
Application driver environments typically have the following property settings that determine which form of SQL Interruption are used:
ā€¢To interrupt the SQL statement
ā€¢To interrupt the entire connection
The default client driver behavior is the more granular approach to interrupt just the SQL statement, as opposed to terminating the connection.
For example, the interruptProcessingMode property specifies the behavior of the IBM Data Server Driver for JDBC and SQLJ when an application executes the Statement.cancel method. Possible values are:
ā€¢DB2BaseDataSource.INTERRUPT_PROCESSING_MODE_DISABLED (0)
Interrupt processing is disabled. When an application executes Statement.cancel, the IBM Data Server Driver for JDBC and SQLJ does nothing
ā€¢DB2BaseDataSource.INTERRUPT_PROCESSING_MODE_STATEMENT_CANCEL (1)
This is the default value. When an application executes Statement.cancel, the IBM Data Server Driver for JDBC and SQLJ cancels the currently executing statement. If the data server does not support interrupt processing, the driver throws an SQLException.
ā€¢DB2BaseDataSource.INTERRUPT_PROCESSING_MODE_CLOSE_SOCKET (2)
When an application executes Statement.cancel, the driver drops the underlying socket
Example 9-60 shows a portion of a JCC trace for a Java application connecting to a DB2 11 for z/OS server.This example highlights the default queryTimeoutInterruptProcessingMode=1 property value. This value indicates that the driver is working with interrupt processing.
Example 9-60 JCC trace and the default interrupt processing mode
[jcc] pureQuery present = false
[jcc] END TRACE_DRIVER_CONFIGURATION
[jcc] BEGIN TRACE_CONNECTS
[jcc] Attempting connection to redbook8:38420/DB1A
[jcc] Using properties: { maxStatements=0, currentPackagePath=null, currentLockTimeout=-2147483647, timerLevelForQueryTimeOut=0, optimizationProfileToFlush=null, timeFormat=1, monitorPort=0, sendCharInputsUTF8=0,
...
currentSchema=null, CR_LOCKBLOB=null, traceLevel=-1, enableRowsetSupport=0, clientDebugInfo=null, dataSourceName=null, enableAlternateServerListFirstConnect=0, maxRetriesForClientReroute=-1, fetchSize=-1, queryDataSize=0, queryTimeoutInterruptProcessingMode=1, alternateGroupServerName=null, clientRerouteAlternateServerName=null, DBTEMP=/tmp, enableT2zosLBF=0, SUBQCACHESZ=10, ssid=null, maxConnCachedParamBufferSize=1048576, fullyMaterializeInputStreamsOnBatchExecution=0, alternateGroupPortNumber=null,
...
defaultIsolationLevel=2, deferPrepares=true, currentDegree=null, DUMPMEM=null, memberConnectTimeout=0 }
[jcc] END TRACE_CONNECTS
The SQL statement Interruption technique is the more granular and preferred operation with respect to remote applications. Nevertheless, the statement interruption processing is not completely reliable in some scenarios. As a consequence, there is a strong recommendation for users to use the more drastic, but more effective, INTERRUPT_PROCESSING_MODE_CLOSE_SOCKET method that terminates the connection.
However, it is often difficult for users to modify the client driver property to use the more reliable form. As a consequence, until the DB2 SQL statement Interrupt processing can be made more reliable, DB2 compensates by changing its SQL Interruption processing to behave as though the connection had been terminated.
This way, SQL statement interruption is more reliable at a DB2 for z/OS server system, but at the expense of terminating the connection with the remote application. This condition should be handled by the application.
In DB2 11, when DB2 receives a DRDA SQL Interrupt from a remote client, it closes the connection and terminate the thread under which the statement is running, instead of interrupting just the statement and returning an SQLCODE=-952.
Depending on whether the remote client has enabled Sysplex Workload Balancing (sysplexWLB) and if the application has resources that need to persist across transactions preventing the connection from being reused by a different application at the end of a transaction, the remote application might receive the following SQLCODEs:
-30081 An application gets this SQLCODE if the client does not support sysplexWLB or the connection cannot be reused. The client has to reconnect to DB2 before executing the application again
-30108 An application gets this SQLCODE if the client supports sysplexWLB and the connection can be reused but the client cannot retry the failed statement seamless to the application. The client, however, reconnects to DB2 before returning the SQLCODE to the application so that the application can retry the failed transaction immediately
0 An application can get this SQLCODE if the client supports sysplexWLB, the connection can be reused and the client seamlessly retried the failed statement which completed successfully
9.3 Continuous block fetching
DB2 11 introduces package-based continuous block fetch. It can improve performance for retrieval of large, read-only result sets from a remote DB2 for z/OS server.
 
Important: DB2 11for z/OS provides improved performance for distributed applications that return large result sets
Like the previously existing SQL-based continuous block fetch, package-based continuous block fetch causes fewer messages to be transmitted from the requester to retrieve the entire result set. However, package-based continuous block fetch is easier to configure. It requires only that you bind your applications with the new DBPROTOCOL(DRDACBF) option. You do not need to modify your applications or set subsystem parameters to indicate the maximum number of blocks to be returned for a remote request. This change is available in NFM and requires APPLCOMPAT = V11R1 to be set.
The new package-based continuous block fetch is more efficient than SQL-based continuous block fetch. With package-based continuous block fetch, the requester opens a secondary connection to the DB2 server for each read-only cursor. The DB2 server returns extra query blocks until the all rows for the cursor have been retrieved. When the cursor is closed, the secondary connection is implicitly closed.
Figure 9-3 shows a representation of how SQL Based Continuous Block Fetch works. Using this technique, DB2 can send numerous query blocks per request. A single connection is used for all SQL. The implication for the single connection is that other SQL, outside of the cursors, cannot use the connection while the cursor driven blocks are using the connection.
Figure 9-3 SQL based continuous block fetch
Figure 9-4 shows a representation of the Package Based Continuous Block Fetch, introduced in DB2 11. With this method, query blocks flow on a secondary connection until the cursor is exhausted. As a consequence, the network latency is significantly improved. When the result set or cursor is exhausted, the DB2 server terminates the connection and the thread is immediately pooled.
Figure 9-4 Package based continuous block fetch
Example 9-61 shows the changes to the SPUFI REBIND panels to indicate the DRDACBF option.
Example 9-61 SPUFI panel DSNEBP11, defaults for REBIND PACKAGE
DSNEBP11 DEFAULTS FOR REBIND PACKAGE SSID: DB1A
COMMAND ===>
Change default options as necessary.
----------------- Use the UP/DOWN keys to access all options ------------------
More: -
UNICODE, or ccsid)
11 OPTIMIZATION HINT ....... ===> SAME > (SAME or 'hint-id')
12 IMMEDIATE WRITE.......... ===> SAME (SAME, NO, YES,
or INHERITFROMPLAN)
13 DBPROTOCOL .............. ===> SAME (SAME, DRDA, DRDACBF)
14 DYNAMIC RULES ........... ===> SAME (SAME, RUN, BIND,
DEFINERUN, DEFINEBIND,
INVOKERUN or INVOKEBIND)
15 PLAN MANAGEMENT ......... ===> DEFAULT (DEFAULT, BASIC, EXTENDED, OFF)
16 ACCESS PATH REUSE ....... ===> DEFAULT (DEFAULT, ERROR, NONE, or WARN)
17 ACCESS PATH COMPARISON .. ===> DEFAULT (DEFAULT, ERROR, NONE, or WARN)
18 ACCESS PATH RETAIN DUPS . ===> DEFAULT (DEFAULT, NO, or YES)
19 SYSTEM_TIME SENSITIVE ... ===> SAME (SAME, NO, or YES)
20 BUSINESS_TIME SENSITIVE . ===> SAME (SAME, NO, or YES)
21 ARCHIVE SENSITIVE ....... ===> SAME (SAME, NO, or YES)
22 APPLICATION COMPATIBILITY ===> SAME (SAME, V10R1, or V11R1)
-------------------------------------------------------------------------------
PRESS: ENTER to continue UP/DOWN to scroll RETURN to EXIT
Example 9-62 shows an example of REBIND PACKAGE command using the DBPROTOCOL(DRDACBF) option.
Example 9-62 REBIND PACKAGE with DBPROTOCOL(DRDACBF) option
//DRDACBF1 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//STEPLIB DD DSN=DB1AT.SDSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB1A)
REBIND PACKAGE(DSN8BH11.DSN8BC3) DBPROTOCOL(DRDACBF)
/*
Example 9-63 shows the output of the execution of this REBIND command.
Example 9-63 REBIND output
1READY
DSN SYSTEM(DB1A)
DSN
REBIND PACKAGE(DSN8BH11.DSN8BC3) DBPROTOCOL(DRDACBF)
WARNING, ONLY IBM-SUPPLIED COLLECTION-IDS SHOULD BEGIN WITH "DSN"
WARNING, ONLY IBM-SUPPLIED PACKAGE-IDS SHOULD BEGIN WITH "DSN"
DSNT254I -DB1A DSNTBRB2 REBIND OPTIONS FOR
PACKAGE = DB1A.DSN8BH11.DSN8BC3.()
ACTION
OWNER SYSADM
QUALIFIER SYSADM
VALIDATE RUN
EXPLAIN NO
ISOLATION CS
RELEASE
COPY
APREUSE
APCOMPARE
APRETAINDUP YES
BUSTIMESENSITIVE YES
SYSTIMESENSITIVE YES
ARCHIVESENSITIVE YES
APPLCOMPAT V11R1
DSNT255I -DB1A DSNTBRB2 REBIND OPTIONS FOR
PACKAGE = DB1A.DSN8BH11.DSN8BC3.()
SQLERROR NOPACKAGE
CURRENTDATA YES
DEGREE 1
DYNAMICRULES
DEFER
REOPT NONE
KEEPDYNAMIC NO
IMMEDWRITE INHERITFROMPLAN
DBPROTOCOL DRDACBF
OPTHINT
ENCODING EBCDIC(00037)
PLANMGMT EXTENDED
PLANMGMTSCOPE STATIC
CONCURRENTACCESSRESOLUTION
EXTENDEDINDICATOR
PATH
DSNT232I -DB1A SUCCESSFUL REBIND FOR
PACKAGE = DB1A.DSN8BH11.DSN8BC3.()
DSN
END
READY
END
Package-based continuous block fetch provides a performance advantage for a DB2 for z/OS application with the following characteristics:
ā€¢The application queries only remote sites
ā€¢The application does not contain INSERT, UPDATE, DELETE or MERGE statements
ā€¢No statement in the application creates a unit of recovery on the remote site. This situation results in an SQL error when the application package is bound for package-based continuous block fetch
Table 9-7 shows the results observed during IBM conducted preliminary internal lab performance tests. In this table, results are expressed as delta %.
Table 9-7 Preliminary internal lab performance results
Delta %
Class 1 Elapsed Time
Class 2 Elapsed Time
Class 1 CPU Time
Class 2 CPU Time
Server
-29,5
-8,3
-20
-5,8
Requester
-31,1
-31,1
-13
-13
9.4 Support for global variables
This section describes the use of global variables.
DB2 supports the following types of distributed protocols an application can use to execute a statement on a remote system:
ā€¢When the application explicitly connects to the remote server, application-directed access
ā€¢When the application implicitly connects to the remote server by using three-part name references, system-directed access
Application-directed access
When using application-directed access, the location and platform of the system executing the statement is known to the application. The application, which had connected to DB2 and possibly run some statements, now connects to another location. In doing so, the application must then establish the session environment it needs to run any of its statements while connected to this new location. Thus, any session information, special registers and user-defined session global variables, are maintained between the application itself and the current serving location. If the application decides to temporarily move away from this location to another, the session information would be preserved at this serving location until the connection between the requesting and serving locations was terminated.
System-directed access
When using system-directed access, the location and platform of the system executing the statement is transparent to the application. The application connects to DB2 and runs the statement as though executing on the local system. You can create aliases for remote objects which allows you to reference remote objects without any change to your application. Users access them with the same syntax and application environment as local objects. When DB2 parses the statement and determines the object is on a remote system, DB2 implicitly connects to the system and forwards the statement to the other system.
For example, an application connects to the EAST location and queries the DEPT table. If the DEPT table is moved to the WEST location, you can create an alias on the EAST location for the DEPT table on the WEST location, the application can continue to issue the query without change. Even if the application directly references the DEPT table at the WEST location by using an explicit 3-part name reference, the application is still just referencing a table/view object without regard to the fact that communications have to be established to the WEST location to access the DEPT table
To maintain location transparency, DB2 ensures the application execution environment is maintained across systems. Special registers used to store information that can be referenced in SQL statements and set by the application are maintained by DB2 on both the local and remote systems transparent to the application. For example, if an application issues the SET CURRENT PRECISION statement to assign a value to the CURRENT PRECISION special register, The CURRENT PRECISION register value is propagated to the remote system prior to executing the statement. When the statement is executed, special register settings set by the application are used to process the statement on the other system.
To maintain location transparency for user-defined session global variables, DB2 ensures the global variables and their values set by the application context are maintained and persist across systems. Because any statement executed locally can reference or alter any user-defined session global variable, an instance of any global variable with its last updated value must be created on the remote system prior to executing the statement. Thus, global variables values are maintained by DB2 on both the local and remote systems for the application process.
For example, if an application sets some global variables and then executes a system-directed SQL statement, the global variable settings are propagated to the remote system. Prior to the execution of the statement, the remote system uses the global variable definitions and values sent from the requesting system to create instances of the user-defined global variables. The statement is then executed on the remote system.
If any changes are made to user-defined session global variables on the remote system by the just executed statement, the definitions and updated values of the changed user-defined session global variables are returned to the requesting system. The requesting system then updates any already instantiated user-defined session global variables or creates instances of newly set user-defined session global variables. For this all to work correctly, the definitions of the global variables, that is CREATE VARIABLE statements, must be identical on both the requesting and serving systems or SQLCODE -30045 is issued.
 
Important: The global variable definitions must exist at every remote location that is accessed by an application, and the definitions must be identical. Otherwise, the application receives SQLCODE -30045.
Example 9-64 shows the structure of the SQL error code -30045.
Example 9-64 Structure of SQLCODE -30045
EXECUTION FAILED BECAUSE THE DEFINITION OF OBJECT object-name OF TYPE object-type BEING ACCESSED AT server-name-1 DIFFERS FROM THE DEFINITION OF THE OBJECT AT server-name-2
As a result of the error reported by the SQL error code -30045, the statement cannot be processed. Refer to the IBM documentation ā€œDB2 for z/OS Codesā€ for more details about this SQL error code.
Finally, an application can intermix application-directed and system-directed statements on the same connection. How session information is maintained is dependent on the persistence of any connection created between the two locations. Mixing protocols that utilize user-defined session global variables can result in unexpected behavior and is prevented. If an application intermixes distributed protocols where a statement first used application-directed protocols and was then followed by a statement using system-directed protocols, an SQLCODE -30047 exception is generated.
For example, an application issues a statement that uses an ALIAS to refer to a table at another location and then calls a procedure which issues a CONNECT statement and issues statements to the same location, the connection has executed statements using both protocols.
Persistence of connections is governed by the SQL RELEASE statement and how the plan was bound as follows:
ā€¢For z/OS applications, the DISCONNECT bind plan option determines when connections are dropped during commit operations. The default value is EXPLICIT. If EXPLICIT is used, the application must issue a RELEASE statement prior to a COMMIT to have a connection dropped during commit processing.
If no RELEASE statements are issued, the connections persist until the application ends. Another possible option is AUTOMATIC. When the AUTOMATIC bind option is in control, all connections to remote servers from the requester is dropped when a COMMIT is processed. The final value of the DISCONNECT bind option is CONDITIONAL. It behaves similarly to AUTOMATIC with one exception. If a WITH HOLD cursor is still open against a location, the connection is not dropped when a COMMIT is processed. If the application eventually closes the cursor, a subsequent COMMIT then causes the connection to be dropped.
ā€¢For IBM DB2 Connectā„¢ applications, the EXPLICIT behavior is used and cannot be changed.
ā€¢The above connection persistence rules apply whether or not the application was prepared (not bound) under connect type-1 or connect type-2 rules.
Based on the above connection persistence rules, SQL statements that are processed at a location under application-directed protocols behave as follows:
ā€¢All statement references to user-defined session global variables, both input and output, refer to the user-defined session global variables at the serving location. User-defined session global variables at the requesting location are neither updated nor referenced as a result of either dynamic or static SQL statements in this scenario.
ā€¢The content of the user-defined session global variables at the serving location persists until the connection is dropped. Whether connect type-1 or connect type-2 rules are used, the connection type is not be a factor and doesn't affect when a connection is dropped. Any subsequent references to user defined session global variables at the location where the connection had been dropped cause the user defined session global variables to be instantiated with default values again; otherwise, subsequent references to the user-defined session global variables use the values last updated.
Based on these connection persistence rules, SQL statements that are processed using system-directed protocols behave as follows:
ā€¢Contents of the user-defined session global variables that are instantiated at the requesting location are sent to the serving location, such that the same user-defined session global variable values are used during the processing of the SQL statement at both locations.
ā€¢User-defined session global variables that are the target of the output process from the SQL statement are made to both the user-defined session global variables at both locations.
ā€¢For static SQL statements referencing objects and user-defined session global variables that use system-directed access, a package must be bound at both locations. If at the time of the static bind, different definition exists on the requester location and server location for the same user defined session global variable, the executable runtime structures are generated differently. At execution time, DB2 issues an -30045 exception to indicate a mismatch of user-defined session global variable definitions if referenced on both sites.
ā€¢For dynamic statements referencing objects and user-defined session global variables at remote locations through system-directed distributed processing, there is no corresponding restriction as static statements. However, if the SQL statement uses a user-defined session global variable and DB2 determines there is a definition mismatch, DB2 issues the -30045 error.
If the application connected to DB2 issues statements that use both system-directed protocols and application directed protocols to the same location, they share the same connection. For example, an application connects to the HDQ location and issues a statement that uses an ALIAS to query a table at the MFG location. The next statement calls a procedure on the HDQ location. If procedure issues a CONNECT statement to the MFG location, the same connection is used. Mixing protocols which use user-defined global variables can cause nondeterministic results. Mixing distributed protocol statements on the same connection that use user-defined session variables is prevented by DB2 issuing an SQLCODE -30047 exception.
For DB2 Connect clients that are sysplexWLB enabled which performs transaction level load balancing across a data sharing group, connections persist across different members of the data sharing group. To support user defined session global variables an upgrade of the client is needed with this feature enabled. DB2 returns changed user-defined session global variables to the client driver to allow the client to replay them when the application connection is transparently moved to a different member of the data sharing group.
Global variables in SQL statements referencing remote servers
In the case of static statements referencing a 3-part remote object, or a statically bound statement executed when the CURRENT SERVER is a remote server, DB2 marks the current section as a distributed-section at the requester site, and all SQL processing occurs at the target server site.
This means, the created global variables at the local requester site are not used in the processing of the SQL statement, because the processing occurs at the server.
DB2 requires the package to exist at both the requester and the server sites for DRDA communication protocol. Thus, the packages on all sites need to be created first using some form of BIND PACKAGE command. However, because the bind occurs on different sites, the global variables might not be all created, or if created might not share the same definition nor DEFAULT expressions. Incongruous definitions or instantiation of global variables can result in different outcomes when the same SQL statement is executed locally versus remotely
Global variables scope with Thread-Reuse
DB2 Distributed Data Facility (DDF) can employ thread-reuse to enhance performance when multiple connections are made to the DB2 server. If a connection (or thread) in DB2 qualifies for reuse, then it is returned to the reusable thread pool at COMMIT or ROLLBACK, waiting for the next connection request. The next connection request can be from a different application, or it can be the continuation of the previous application. Because global variables are not affected by COMMIT nor ROLLBACK, the content must persist across COMMIT and ROLLBACK, and therefore, across reusable threads.
When a thread is reused for an application that referenced global variables, all instantiated variables are ā€œreplayedā€ for the reused thread such that all values recorded from the previous thread are copied over to the current reused thread. This ensures the persistence of the instantiated global variable across reusable threads.
9.5 Local stored procedure execution improvement
DB2 11 delivers performance optimization for processing stored procedure calls from local ODBC and JDBC applications by improving stored procedure result set processing. This is beneficial for customers who call stored procedures from a local JDBC or ODBC environment, such as WebSphere on z/OS or MessageBroker on z/OS, encapsulating SQL in stored procedures. The enhancements do not require changes to the application and are available in CM.
The enhancements are in the following areas:
ā€¢The communication between the ODBC or JDBC/SQLJ driver and DB2 to execute the CALL statement.
Bundling CALL and DESCRIBE PROCEDURE and bundling ALLOCATE CURSOR and DESCRIBE CURSOR to reduce trips from ODBC/JDBC driver to DBM1.
ā€¢The communication between the ODBC or JDBC/SQLJ driver and DB2 to return the result set metadata.
ā€¢The processing of the result sets returned from the called stored procedure using limited block fetch and progressive streaming (which is better performing than multi-row fetch).
ā€¢The communication between ODBC or JDBC/SQLJ driver and DB2 by implicitly closing the result sets at their termination (SQLCODE +100).
ā€¢Support of 64bit private variables area for in/out parameters.
Allows the exchange of parameter larger than 32 KB, such as parameter of data type LOB (with usage of 64-bit DB2VAR for input/output parameters).
ā€¢More efficient way to describe stored procedure parameters.
Similar enhancements had been introduced with DB2 10 for local ODBC/JDBC, but not for stored procedures.
Figure 9-5 summarizes the enhancements.
Figure 9-5 Summary of local stored procedure improvements
The ODBC driver supports the optimization of stored procedure result set processing by enabling block fetch through the LIMITEDBLOCK, QUERYDATASIZE, and STREAMBUFFERSIZE keywords in the initialization file data source section. The keyword values are read from the data source stanza following a successful connect.
The LIMITEDBLOCK keyword specifies if the driver is to attempt a block fetch when fetching a result set at the connected data source (server). The acceptable keyword values are 0 and 1:
0 No block fetch.
1 (default) The driver would attempt a block fetch and return as many rows as can fit in a data block in a single fetch provided that blocking is supported at the server for the result set being fetched. The driver currently does not perform block fetch if any of the columns in the result set is a LOB, XML, or file reference.
QUERYDATASIZE specifies the size of the data block in bytes. The default for QUERYDATASIZE is 32 KB (32767). The maximum data block size is 1048575 in 32 KB increments.
STREAMBUFFERSIZE is the threshold value, in bytes (default 1 MB) to return LOB or XML as inline data or as internal token:
ā€¢If size of LOB or XML object <= STREAMBUFFERSIZE, data returned inline
ā€¢If size of LOB or XML object >= STREAMBUFFERSIZE, progressive reference returned
The JDBC driver supports stored procedure optimization transparently.
ā€¢The queryDataSize property also used for stored procedure result sets
32 KB, up to 1 MB in 32 KB increments
ā€¢FET_BUF_SIZE (64 KB) keyword can be used to limit rows per buffer
The tests have shown better performance for local ODBC and Type 2 applications that call local stored procedures because of:
ā€¢More efficient blocking of data in returned result sets
ā€¢More efficient retrieval of LOB and XML result sets
ā€¢Reduced traffic for implicit close
9.6 Multi-threaded Java stored procedure environment
DB2 11 adds support for running Java stored procedures in a 64-bit Java virtual machines (JVM). Earlier versions of DB2 run Java stored procedures in 31-bit JVM only, and each JVM can run only one Java stored procedure at a time.
With DB2 10, the behavior is single threaded JVM for Java stored procedures can be summarized as follows:
ā€¢WLM stored procedure address space (WLM-SPAS)
ā€¢1 JVM per TCB in WLM-SPAS
ā€‰ā€“ Large storage footprint per TCB
ā€‰ā€“ Overhead on starting JVMs
ā€¢The recommended NUMTCB is 8 or less per WLM application environment
ā€‰ā€“ NUMTCB used is typically 2-5
ā€¢There are performance and scalability implications for Java stored procedures
ā€¢They use a 31-bit JVM
With DB2 11, see Figure 9-6, Java stored procedures use multi-threaded JVMs. DB2 11 can concurrently run multiple Java stored procedures in 64-bit JVMs. Therefore, more Java stored procedures can run in a single stored procedure address space than in earlier DB2 versions.
ā€¢One 64-bit JVM per WLM-SPAS
ā€‰ā€“ Less overhead to start JVM
ā€‰ā€“ Smaller JVM storage footprint
ā€¢NUMTCB of 25 or more per WLM application environment
ā€‰ā€“ Better scalability
ā€¢It requires JDK 1.6
ā€‰ā€“ 64-bit JDK
ā€‰ā€“ IBM Data Server Driver for JDBC and SQLJ
The multi-threaded JVM executes a new DSNX9WJM module in the WLM application environment which is specified on the start-up JCL.
The existing application environments need to be modified or new application environments defined to take advantage of more TCBs (larger NUMTCB).
Figure 9-6 Moving to multi-threaded JVM environment
Most existing Java stored procedures can be altered to run in new multi-threaded environment. If the Java stored procedure invokes a native method through JNI calls, the dynamic link library (DLL) for native functions must be compiled and linked in 64-bit mode.
 
Note: Native non-Java code must be rebuilt and tested for the 64-bit environment.
While in data sharing coexistence, multi-threaded Java stored procedures can be used in CM mode but all members must use new DSNX9WJM module for that WLM environment if stored procedures uses native JNI calls.
9.7 ADMIN_COMMAND_MVS stored procedure
DB2 provides stored procedures that you can call in your application programs to perform administrative functions.
You can use the DB2 provided SYSPROC.ADMIN_COMMAND_MVS stored procedure to issue the following z/OS commands:
ā€¢QUERY COPYPOOL
ā€¢LIST COPYPOOL
ā€¢DB2 START
ā€¢DB2 STOP
ā€¢DUMP
ā€¢DISPLAY WLM
SYSPROC.ADMIN_COMMAND_MVS (also retrofitted to DB2 10 by APAR PM93773) extends the list to the following available command related stored procedures:
ā€¢ADMIN_COMMAND_DB2
ā€¢ADMIN_COMMAND_DSN
ā€¢ADMIN_COMMAND_UNIX
This stored procedure runs in a WLM-established stored procedures address space, and all of the libraries that are specified in the STEPLIB DD statement must be APF-authorized. Example 9-65 shows the syntax for calling ADMIN_COMMAND_MVS.
Example 9-65 Syntax CALL ADMIN_COMMAND_MVS
>>-CALL--ADMIN_COMMAND_MVS--(----type---,----------------------->
 
>--+-command_prefix-+-,--+-remote_system-+-,--+-jobname-+-,----->
'-NULL-----------' '-NULL----------' '-NULL----'
 
>----command---,--+-parameters-+-,--+-subparameters-+-,--------->
'-NULL-------' '-NULL----------'
 
>--+-wait_timeout-+--return-code,--command_completion_code,----->
'-NULL---------'
 
>--+-message-+--)----------------------------------------------><
'-NULL----'
The call parameter type cannot be NULL, and it accepts these values:
ā€¢HSM
ā€¢DB2
ā€¢DUMP
ā€¢WLM
This stored procedure returns the following output parameters:
return-code Provides the return code from the stored procedure. Possible values are 0,4,8, and 12.
command_completion_code Indicates the completion status of the command. Possible values are 0,4,8,12 and 16.
message Contains messages that describe the error that was encountered by the stored procedure.
 
Tip: Refer to DB2 11 for z/OS Administration Guide, SC19-4050 for details about ADMIN_COMMAND_MVS parameters.
Execution example: display WLM
To display the WLM application environments using ADMIN_COMMAND_MVS you have to specify these parameters:
ā€¢Type: Use the value WLM
ā€¢Command: Use the value DISPLAY
ā€¢Parameters: Specify either APPLENV=name or APPLENV=*
All the other parameters have to be defined as NULL. Figure 9-7 shows an example of calling ADMIN_COMMAND_MVS from Data Studio.
Figure 9-7 Calling ADMIN_COMMAND_MVS from Data Studio
For parameter that are not filled in, use the Set to Null button in this dialog box. Otherwise, the execution of the stored procedure fails with return code 12 and DSNA601I message.
Example 9-66 is an illustration of the feedback received when using an invalid COMMAND_PREFIX parameter.
Example 9-66 Message DSNA601I
DSNA601I DSNADMCM THE PARAMETER COMMAND_PREFIX IS NOT VALID. INVALID REASON CODE=5
DSNA601I indicates that the parameter specified in the message is not valid. The cause of the invalid error is identified by the INVALID REASON CODE value in the message text. The specified parameter is not valid for the indicated reason, as follows:
ā€¢REASON CODE=1: Value is not an acceptable value
ā€¢REASON CODE=2: Value is not unique
ā€¢REASON CODE=3: Value is null
ā€¢REASON CODE=4: Value is blank
ā€¢REASON CODE=5: Value is not null
ā€¢REASON CODE=6: Value is too long
ā€¢REASON CODE=7: Named parameter is not known
ā€¢REASON CODE=8: Named parameter is missing
Figure 9-8 shows the parameters panel in Data Studio after successful execution. Note the parameters RETURN_CODE = 0 and COMMAND_COMPLETION_CODE = 0. MSG is Null.
Figure 9-8 ADMIN_COMMAND_MVS parameters in Data Studio
The actual WLM DISPLAY results are reported in the Result1 panel, as shown in Figure 9-9.
Figure 9-9 Calling ADMIN_COMMAND_MVS: Result1 panel
In addition, the WLM DISPLAY output command is listed in the system log, as shown in Example 9-67.
Example 9-67 ADMIN_COMMAND_MVS and WLM DISPLAY: system log messages
15:52:51.67 STC06093 00000090 ICH70001I DB2R1 LAST ACCESS AT 15:39:32 ON FRIDAY, JULY 26, 2013
15:52:51.70 STC06093 00000290 IEA630I OPERATOR DSNADMCM NOW ACTIVE, SYSTEM=SC63 , LU=DSNADMCM
15:52:51.70 DSNADMCM 00000290 DISPLAY WLM,APPLENV=*
15:52:51.73 DSNADMCM 00000090 IWM029I 15.52.51 WLM DISPLAY 337
337 00000090 APPLICATION ENVIRONMENT NAME STATE STATE DATA
337 00000090 BARTSRV AVAILABLE
337 00000090 BBOASR1 AVAILABLE
337 00000090 BBOASR2 AVAILABLE
337 00000090 CBINTFRP AVAILABLE
337 00000090 CBNAMING AVAILABLE
...
In this example, STC06093 is the WLM address space that supports the execution of the stored procedure. DSNADMCM is the name of the extended MCS console that issue the requested command.
Security considerations
To execute the CALL statement, the owner of the package or plan that contains the CALL statement must have one or more of the following privileges:
ā€¢The EXECUTE privilege on the stored procedure
ā€¢Ownership of the stored procedure
ā€¢SYSADM authority
The load module for ADMIN_COMMAND_MVS is named DSNADMCM. This name can be used in the definition of some specific RACF resources as a way of increasing security.
The caller of ADMIN_COMMAND_MVS must have READ access to the RACF MVS.MCSOPER.* or to the MVS.MCSOPER.DSNADMCM resource profile of the OPERCMDS class. RACF perform access checks starting by the most restrictive resource profile. If MVS.MCSOPER.DSNADMCM is not defined, RACF checks for MVS.MCSOPER.*.
You can use the RACF ISPF panels to investigate the access defined on the resource MVS.MCSOPER.* following these steps:
1. Open the RACF ISPF main menu to receive the SERVICES OPTION MENU.
2. Select option 2. GENERAL RESOURCE PROFILES.
3. You are now in the GENERAL RESOURCE PROFILE SERVICES panel. Select option S. SEARCH.
4. Press Enter to access to the GENERAL RESOURCE SERVICES - SEARCH panel.
5. Enter OPERCMDS in the CLASS option, as shown in Example 9-68.
Example 9-68 RACF - GENERAL RESOURCE SERVICES panel
RACF - GENERAL RESOURCE SERVICES - SEARCH
OPTION ===>
ENTER THE FOLLOWING PROFILE INFORMATION:
CLASS ===> OPERCMDS
PROFILE ===>
6. Press Enter to access the SEARCH FOR GENERAL RESOURCE PROFILES panel. Fill-in MASK1 with MVS, and MASK2 with MCSOPER, as shown in Example 9-69. Press Enter to continue.
Example 9-69 RACF SEARCH FOR GENERAL RESOURCE PROFILES panel
RACF - SEARCH FOR GENERAL RESOURCE PROFILES
COMMAND ===>
ENTER MASK(S) OR FILTER (OPTIONAL):
MASK1 ===> MVS
<= end of data
MASK2 ===> MCSOPER
<= end of data
7. In next panel, SEARCH FOR GENERAL RESOURCE PROFILES, just press Enter to continue.
8. Example 9-70 shows the RACF command output.
Example 9-70 RACF COMMAND OUTPUT, MVS.MCSOPER
BROWSE - RACF COMMAND OUTPUT------------------------ LINE 00000000 COL 001 080
COMMAND ===> SCROLL ===> CSR
********************************* Top of Data **********************************
MVS.MCSOPER.ABC
MVS.MCSOPER.AOPAOP1C
MVS.MCSOPER.PLUGH
MVS.MCSOPER.* (G)
******************************** Bottom of Data ********************************
The MVS.MCSOPER.DSNADMCM profile is not defined in this environment. Access is controlled by the profile MVS.MCSOPER.*, a RACF generic profile. Using the RACF ISPF panel GENERAL RESOURCE SERVICES - DISPLAY you can browse the access definitions on this resource. Example 9-71 illustrates the RACF command output obtained when using this panel.
Example 9-71 RACF COMMAND OUTPUT, resource MVS.MCSOPER.*
BROWSE - RACF COMMAND OUTPUT------------------------ LINE 00000000 COL 001 080
COMMAND ===> SCROLL ===> CSR
********************************* Top of Data **********************************
CLASS NAME
----- ----
OPERCMDS MVS.MCSOPER.* (G)
LEVEL OWNER UNIVERSAL ACCESS YOUR ACCESS WARNING
----- -------- ---------------- ----------- -------
00 TRAUNER CONTROL CONTROL NO
...
******************************** Bottom of Data ********************************
The RACF Universal Access Authority (UACC) is assigned by default to a user id unless there is a more restrictive access definition. In this example, every user receives CONTROL access on the resource MVS.MCSOPER.* and, in consequence, there is no RACF restriction on the execution of the ADMIN_COMMAND_MVS stored procedure.
A way to increase the security control is to create the MVS.MCSOPER.DSNADMCM resource and to administer granular access to it. Example 9-72 shows the RACF command. Note that the access by default is NONE, that is, initially no user ID has access to the resource.
Example 9-72 RACF define resource MVS.MCSOPER.DSNADMCM
RDEFINE OPERCMDS MVS.MCSOPER.DSNADMCM UACC(NONE)
Example 9-73 shows the resulting output at execution.
Example 9-73 RACF define resource output example
RACLISTED PROFILES FOR OPERCMDS WILL NOT REFLECT THE ADDITION(S) UNTIL A SETROPTS REFRESH IS ISSUED.
***
The RACF class has to be refreshed to activate the changes. Example 9-74 shows the command to execute a RACF SETROPTS REFRESH command.
Example 9-74 RACF SETROPTS REFRESH command
SETROPTS RACLIST(OPERCMDS) REFRESH
Example 9-75 shows the resulting resource definitions after the execution of these RACF commands.
Example 9-75 RACF resources search result
BROWSE - RACF COMMAND OUTPUT------------------------ LINE 00000000 COL 001 080
COMMAND ===> SCROLL ===> CSR
********************************* Top of Data **********************************
MVS.MCSOPER.ABC
MVS.MCSOPER.AOPAOP1C
MVS.MCSOPER.DSNADMCM
MVS.MCSOPER.PLUGH
MVS.MCSOPER.* (G)
******************************** Bottom of Data ********************************
Example 9-76 shows the details of the MVS.MCSOPER.DSNADMCM RACF resource.
Example 9-76 RACF resource MVS.MCSOPER.DSNADMCM
BROWSE - RACF COMMAND OUTPUT------------------------ LINE 00000000 COL 001 080
COMMAND ===> SCROLL ===> CSR
********************************* Top of Data **********************************
CLASS NAME
----- ----
OPERCMDS MVS.MCSOPER.DSNADMCM
LEVEL OWNER UNIVERSAL ACCESS YOUR ACCESS WARNING
----- -------- ---------------- ----------- -------
00 DB2R1 NONE NONE NO
...
******************************** Bottom of Data ********************************
This example shows that both universal access and user id DB2R1ā€™s access is NONE. As a result, any attempt to execute the ADMIN_COMMAND_MVS by the user id DB2R1, or any other user not explicitly authorized in RACF, fails. Under these settings, the calling application receives Return Code 12 at call. Example 9-77 shows the accompanying error message.
Example 9-77 Error message DSNA628I
DSNA628I DSNADMCM THE STORED PROCEDURE SYSPROC.ADMIN_COMMAND_MVS ENCOUNTERED AN ERROR WHILE USING THE EXTENDED MCS CONSOLE TO ISSUE THE MVS SYSTEM COMMAND DISPLAY WLM,APPLENV=*. EMCS activation failed. Macro MCSOPER: RC=0C,RSN=00
At failure, RACF writes a ICH408I error message in the system console, as shown in Example 9-78.
Example 9-78 RACF message ICH408I
IEA631I OPERATOR DSNADMCM NOW INACTIVE, SYSTEM=SC63 , LU=DSNADMCM
ICH70001I DB2R1 LAST ACCESS AT 17:53:40 ON FRIDAY, JULY 26, 2013
ICH408I USER(DB2R1 ) GROUP(SYS1 ) NAME(PAOLO BRUNI ) 481
MVS.MCSOPER.DSNADMCM CL(OPERCMDS)
INSUFFICIENT ACCESS AUTHORITY
ACCESS INTENT(READ ) ACCESS ALLOWED(NONE )
This message shows that RACF is not allowing the user id DB2R1 to access the RACF resource MVS.MCSOPER.DSNADMCM. Because this is a requirement for the execution of the ADMIN_COMMAND_MVS stored procedure, the call fails. To provide access to this resource, you can use the RACF PERMIT command as shown in Example 9-79.
Example 9-79 RACF PERMIT MVS.MCSOPER.DSNADMCM
PERMIT MVS.MCSOPER.DSNADMCM CLASS(OPERCMDS) ACC(READ) ID(DB2R1)
The execution of this command has to be followed by a RACF SETROPTS command to activate changes, as shown in Example 9-80.
Example 9-80 RACF SETROPTS RACLIST(OPERCMDS) REFRESH command
SETROPTS RACLIST(OPERCMDS) REFRESH
Example 9-81 shows that DB2R1 has READ access on the resource MVS.MCSOPER.DSNADMCM. The call of the ADMIN_COMMAND_MVS stored procedure by DB2R1 is now allowed by RACF.
Example 9-81 RACF MVS.MCSOPER.DSNADMCM resource details
BROWSE - RACF COMMAND OUTPUT------------------------ LINE 00000000 COL 001 080
COMMAND ===> SCROLL ===> CSR
********************************* Top of Data **********************************
CLASS NAME
----- ----
OPERCMDS MVS.MCSOPER.DSNADMCM
LEVEL OWNER UNIVERSAL ACCESS YOUR ACCESS WARNING
----- -------- ---------------- ----------- -------
00 DB2R1 NONE READ NO
...
******************************** Bottom of Data ********************************
9.8 Drivers, clients, and connectivity requirements
Distributed clients communicate to DB2 11 for z/OS using the IBM Distributed Relational Database Architectureā„¢ (DRDA) protocol. DRDA is an open, vendor-independent architecture that supports the connectivity between a client and database servers. It was initially developed by IBM and then adopted by The Open Group as an industry standard interoperability protocol.
In DRDA terms, the Application Requester function accepts SQL requests from an application and redirect them to an Application Server for processing. The Application Server function receives requests from Application Requesters and processes them. The Application Server can process part of the request and forwards the applicable portion of the request to a database server.
In a distributed application environment accessing DB2 for z/OS, the Application Requester function is supported by a DB2 Client, by a DB2 driver, or by a DB2 Connect server. The Application Server function is integrated in DB2 for z/OS.
Figure 9-10 shows a schematic representation of the AR and AS DRDA components involved in a client to DB2 communication.
Figure 9-10 AR and AS DRDA components
Improvements related to the distributed access to DB2 11 to z/OS might require changes at the Client, driver or DB2 Connect side.
DB2 Clients, Drivers and DB2 Connect DB2 10.5 FP2 are required to fully take advantage of DB2 11 for z/OS distributed features, such as:
ā€¢CALL with array type arguments
ā€¢Larger CLIENT INFO properties (including new correlation token)
ā€¢Implicit COMMIT for stored procedures
ā€¢Sysplex support for Global Session Variables
The DRDA protocol implements DRDA levels to group improvements and features. A down level DRDA Client works with DB2 11, but it cannot use all the benefits of DB2 11. Any in-service level of DB2 Client, DB2 Drivers, or DB2 Connect server should work with DB2 11 CM and DB2 11 NFM. At the moment of this writing, versions 9.5 and later are in-service. DB2 Connect V9.5 is planned to be out of service at April-2014.
DB2 Connect drivers seamlessly handle the migration path from DB2 10 for z/OS to DB2 11 CM, and from them to DB2 11 NFM. In a data sharing environment, applications continue to function as members are migrated one by one.
During migration, these considerations apply:
ā€¢While in DB2 11 CM, applications continue to see DB2 10 function level.
ā€¢After migrating to DB2 11 NFM, new connections see DB2 11 function level when using APPLCOMPAT set to V11R1.
Verifying the level of DB2 Clients and DB2 Drivers
To exploit the latest DB2 11 distributed access improvements, you have to work with a DB2 driver or client DB2 10.5 fix pack 2.
 
Note: At the moment of this writing, the latest drivers and clients are available at the web page ā€œDownload initial DB2 10.5 clients and driversā€ at http://www.ibm.com/support/docview.wss?uid=swg21385217
Use the db2level command to show the current version and service level of a DB2 client or DB2 Connect server. Example 9-82 shows the execution of db2level on a Windows machine. Among other details, this example shows that this Client in DB2 10.5 with Fix Pack 0.
Example 9-82 Using the db2level command
C:Program FilesIBMSQLLIB_03BIN>db2level
DB21085I This instance or install (instance name, where applicable: "DB2_03")
uses "64" bits and DB2 code release "SQL10050" with level identifier
"0601010E".
Informational tokens are "DB2 v10.5.0.420", "s130528", "NTX64105", and Fix Pack
"0".
Product is installed at "C:PROGRA~1IBMSQLLIB~3" with DB2 Copy Name
"DB2V10R5".
 
C:Program FilesIBMSQLLIB_03BIN>
The db2level command is not available for Java drivers. For JDBC or SQLJ applications, if you are using the IBM DB2 driver for SQLJ and JDBC, you can determine the level of the driver by running the db2jcc utility, as shown in Example 9-83.
Example 9-83 Running the db2jcc utility
java com.ibm.db2.jcc.DB2Jcc -version
Example 9-84 shows the db2jcc output in this example test environment. There is no information about the driver version nor Fix Pack level. This can be an inconvenient because the DB2 11 for z/OS requirements for Clients and Drivers are expressed on these terms.
Example 9-84 Db2jcc utility output
C:Program FilesIBMSQLLIB_03BIN>java com.ibm.db2.jcc.DB2Jcc -version
IBM DB2 JDBC Universal Driver Architecture 3.66.46
 
C:Program FilesIBMSQLLIB_03BIN>
There is no direct way to discern which JDBC driver (JCC) version corresponds with each DB2 release and Fix Pack level. Nevertheless, there is a way to map the driver architecture, provided by db2jcc -version, with that information.
 
Note: To find the correlation between the Java driver architecture and the driver version visit the page ā€œDB2 JDBC driver Versionsā€ at http://www.ibm.com/support/docview.wss?rs=71&uid=swg21363866
Figure 9-11 shows a partial view of the DB2 JDBC driver Versions web page. It highlights how to match the driverā€™s architecture to the DB2 version and Fix Pack level.
Figure 9-11 DB2 JDBC driver Versions web page
The IBM DB2 JDBC Universal Driver Architecture 3.66.46, as shown in Example 9-84, correspond to DB2 DB2 10.5 FP0 (GA).
Example 9-85 shows how the JDBC connection string can be used to activate a JDBC trace.
Example 9-85 JDBC connection url String with TRACE_ALL
String url = "jdbc:db2://redbook8:38420/DB1A" +
":user=db2r1;password=******;" +
"traceLevel=" +
(com.ibm.db2.jcc.DB2BaseDataSource.TRACE_ALL) + ";" +
"traceFile=c:\work\Author\Redbook#8\DRDA_traces\DB211OldDriver.trace;";
Example 9-86 shows a partial example of the JDBC trace output as a result of the syntax in Example 9-85.
Example 9-86 JDBC trace output
[jcc] BEGIN TRACE_XML_CONFIGURATION_FILE
[jcc] dsdriverConfigFile=null
[jcc] END TRACE_XML_CONFIGURATION_FILE
 
[jcc] BEGIN TRACE_DRIVER_CONFIGURATION
[jcc] Driver: IBM Data Server driver for JDBC and SQLJ 4.13.127
[jcc] Compatible JRE versions: { 1.6, 1.7 }
[jcc] Target server licensing restrictions: { z/OS: enabled; SQLDS: enabled
[jcc] License editions: { O: not found; ZS: not found; IS: not found; AS: n
[jcc] Range checking enabled: true
[jcc] Bug check level: 0xff
[jcc] Default fetch size: 64
[jcc] Default isolation: 2
[jcc] Collect performance statistics: false
[jcc] No security manager detected.
[jcc] Detected local client host: x1/9.55.137.33
Figure 9-12 shows a portion of the ā€œDB2 JDBC driver Versionsā€ at:
This figure highlights the link between the driver version and the DB2 level of the driver.
Figure 9-12 db2 JDBC driver versions web page
Data Studio
IBM Data Studio provides database developers and database administrators with an integrated, modular environment for development and productive administration of DB2 databases. IBM Data Studio is a fully licensed product available at no charge and with no time restrictions.
 
Important: IBM Data Studio supports DB2 11 for z/OS with V4.1 or later, which can be downloaded at no additional charge from:
Figure 9-13 shows that IBM Data Studio V3.2 identifies a DB2 11 NFM database as a DB2 10 NFM subsystem.
Figure 9-13 IBM Data Studio 3.2 and DB2 11
Figure 9-14 shows that IBM Data Studio 4.1 correctly identifies the server as a DB2 11 NFM.
Figure 9-14 IBM Data Studio 4.1 and DB2 11
Using the Data Studio application menu, select Help ā†’ About IBM Data Studio to verify the Data Studio version. Figure 9-15 shows the About IBM Data Studio window with the version information.
Figure 9-15 How to identify the Data Studio version
You can use Data Studio 4.1 with a DB2 10 for z/OS database. In that case, the PTF UK91146 must be applied to the DB2 10 for z/OS data server that you want to connect to prevent connectivity problems. Refer to the technote (troubleshooting) ā€œConnecting to DB2 z/OS 10 with Data Studio V4.1 or InfoSphere Data Architect V9.1 results in SQL error code -4499 or -1224ā€ available at:
How to catalog a DB2 for z/OS database using commands
To access a DB2 for z/OS server using a Client, you have to catalog it. The DB2 Client version 10 does not comes with the Configuration Assistant. In previous versions, the Configuration Assistance, a GUI tool, can be used to catalog a DB2 for z/OS database as a ODBC data source in a Windows system machine. With DB2 10, the configuration has to be done using commands. This section describes the steps involved on the process.
Start by getting the host database configuration information by issuing the -DIS DDF DETAIL command. Example 9-87 shows the output of this command.
Example 9-87 -DIS DDF output example
DSNL080I -DB1A DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STARTD
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I DB1A USIBMSC.SCPDB1A -NONE
DSNL084I TCPPORT=38420 SECPORT=38422 RESPORT=38421 IPNAME=-NONE
DSNL085I IPADDR=::9.12.6.70
DSNL086I SQL DOMAIN=wtsc63.itso.ibm.com
DSNL090I DT=I CONDBAT= 10000 MDBAT= 200
DSNL092I ADBAT= 1 QUEDBAT= 0 INADBAT= 0 CONQUED= 0
DSNL093I DSCDBAT= 1 INACONN= 2
DSNL105I CURRENT DDF OPTIONS ARE:
DSNL106I PKGREL = COMMIT
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
Note the following information:
ā€¢Location, provided in message DSNL083I. DB1A in this example.
ā€¢TCP/IP port, provided in message DSNL084I. 38420 in this example.
The IP address provided in message DSNL085I might not work as a target IP from your workstation, depending from where it was obtained. Refer to the documentation of message DSNL085I for more details. In this example environment, the DB2 server can be reached by using the 9.12.6.70 IP address. This information is used in commands within the DB2 CLP. The CLP is part of the DB2 client, and it is included in other DB2 offerings as well.
If you have installed a DB2 client, you can use a DB2 CLP command window to catalog a DB2 for z/OS database in a windows server or workstation. Example 9-88 shows the initial contents when opening a DB2 Client 10.5.0 CLP window.
Example 9-88 DB2 Command Line Processor initial contents
Command Line Processor for DB2 Client 10.5.0
 
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
 
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
 
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
 
For more detailed help, refer to the Online Reference Manual.
 
db2 =>
The first step is to catalog a TCP/IP node using the CLP. Cataloging a TCP/IP node adds an entry to the Data Server Client node directory that describes the remote node. This entry specifies the chosen alias (node_name), the host name (or ip_address), and the svcename (or port_number) that the client uses to access the remote host.
Example 9-89 shows the commands that can be used.
Example 9-89 DB2 catalog TCP/IP node example
catalog tcpip node SC63 remote 9.12.6.70 server 38420 ostype mvs
In general, it is a preferred practice to use machine names instead if IP addresses when cataloging a remote server. Using names means fewer configuration points to maintain in case of an IP address change. You can use a DNS (domain name server) for mapping a server name to an IP address to make this information available to your network. If the scope is your own workstation, it is in general simpler to just maintain a hosts file. In Windows 7, the hosts files are located at C:WindowsSystem32driversetchosts.
Example 9-90 shows a sample Windows hosts file that is customized with the information about the System z server that runs the target DB2 11 for z/OS.
Example 9-90 Sample Windows hosts file
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# localhost name resolution is handled within DNS itself.
# 127.0.0.1 localhost
# ::1 localhost
9.12.6.70 redbook8
After updating the hosts file as shown in this example, a reference to redbook8 is redirected to the IP address 9.12.6.70. In consequence, the DB2 catalog TCP/IP node can be simplified as shown in Example 9-91.
Example 9-91 DB2 catalog TCP/IP node example using an hosts file entry
catalog tcpip node SC63 remote redbook8 server 38420 ostype mvs
Example 9-92 illustrates the output of this command.
Example 9-92 DB2 catalog TCP/IP node output example
db2 => catalog tcpip node SC63 remote redbook8 server 38420 ostype mvs
DB20000I The CATALOG TCPIP NODE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
db2 =>
The DB21056W message indicates that changes might not be effective immediately, and that a directory refresh might be required to make the updates effective. Execute the terminate command, as illustrated in Example 9-93.
Example 9-93 DB2 terminate example
db2 => terminate
DB20000I The TERMINATE command completed successfully.
C:Program FilesIBMSQLLIB_03BIN>
 
Tip: To refresh the CLP directory cache, issue a db2 terminate command. To refresh the directory information for another application, stop and restart that application. To refresh the directory information for the database, stop (db2stop) and restart (db2start) the database
The node directory is created and maintained on each database client. The directory contains an entry for each remote database partition server having one or more databases that the client can access. The DB2 client uses the communication endpoint information in the node directory whenever a database connection or instance attachment is requested. The entries in the directory also contain information about the type of communication protocol to be used to communicate from the client to the remote database partition. Cataloging a local database partition creates an alias for an instance that resides on the same computer.
Example 9-94 illustrates the execution of a list node directory command in this example test environment. Use this command to verify the successful addition of the node.
Example 9-94 DB2 list node directory command example
C:Program FilesIBMSQLLIB_03BIN>db2 list node directory
 
Node Directory
 
Number of entries in the directory = 1
 
Node 1 entry:
 
Node name = SC63
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = redbook8
Service name = 38420
 
C:Program FilesIBMSQLLIB_03BIN>
The catalog database command stores database location information in the system database directory. The database can be located either on the local workstation or on a remote database partition server. Example 9-95 illustrates the command used in this example test environment to catalog the DB2 11 target database.
Example 9-95 DB2 catalog database command example
catalog database DB1A as DB1A at node SC63 authentication SERVER_ENCRYPT
Example 9-96 shows the results of executing this command.
Example 9-96 DB2 catalog database command output example
db2 => catalog database DB1A as DB1A at node SC63 authentication SERVER_ENCRYPT
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
db2 =>
The list database directory command lists the contents of the system database directory. Use this command to verify the addition of a database, as illustrated in Example 9-97.
Example 9-97 DB2 list database directory command output example
db2 => list database directory
 
System Database Directory
 
Number of entries in the directory = 3
 
Database 1 entry:
 
Database alias = BLUDB01
Database name = BLUDB01
Local database directory = C:
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
 
Database 2 entry:
 
Database alias = DB1A
Database name = DB1A
Node name = SC63
Database release level = 10.00
Comment =
Directory entry type = Remote
Authentication = SERVER_ENCRYPT
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
 
Database 3 entry:
 
Database alias = SAMPLE
Database name = SAMPLE
Local database directory = C:
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
 
db2 =>
This example shows that the DB2 Client can be used to connect to two local databases (SAMPLE and BLUDB01) and to the remote database DB1A, the target. Applications connect using the database alias value that is provided in this command.
Finally, connect to the target database using the CLP for verification, as shown in Example 9-98.
Example 9-98 Connect to a DB2 for z/OS database using the CLP
db2 => connect to DB1A user db2r1
Enter current password for db2r1:
 
Database Connection Information
 
Database server = DB2 z/OS 11.1.5
SQL authorization ID = DB2R1
Local database alias = DB1A
 
db2 =>
This example shows a connection to the target DB1A, which is DB2 11 for z/OS database.
A data source, in ODBC (Open Database Connectivity) terminology, is a user-defined name for a specific database or file system. That name is used to access the database or file system through ODBC APIs. Either user or system data sources can be cataloged. A user data source is only visible to the user who cataloged it, whereas a system data source is visible to and can be used by all other users. The CATALOG ODBC DATA SOURCE command is used to catalog a user or system ODBC data source. Example 9-99 shows the command to be executed in this example environment.
Example 9-99 DB2 catalog ODBC data source command example
catalog odbc data source DB1A
Example 9-100 illustrates the execution results in this environment.
Example 9-100 DB2 catalog ODBC data source command output example
db2 => catalog odbc data source DB1A
DB20000I The CATALOG USER ODBC DATA SOURCE command completed successfully.
db2 =>
Use the list ODBC data sources command to confirm the changes, as shown in Example 9-101.
Example 9-101 DB2 LIST ODBC DATA SOURCES command example
db2 => list odbc data sources
User ODBC Data Sources
 
Data source name Description
-------------------------------- ----------------------------------------
MS Access Database Microsoft Access driver (*.mdb)
Excel Files Microsoft Excel driver (*.xls)
dBASE Files Microsoft dBase driver (*.dbf)
DZA1 IBM DB2 ODBC DRIVER - DB2COPY1
BRUXLS IBM DB2 ODBC DRIVER - DB2COPY1
DB1A IBM DB2 ODBC DRIVER - DB2V10R5
db2 =>
 
1 IBM TivoliĀ® OMEGAMONĀ® XE for DB2 Performance Expert on z/OS
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset