Testing Db2 Design Decisions

(warning: all code examples in this article have been edited for HTML compatibility, and may require editing after copying and pasting. In addition, DanL Database Consulting assumes no liability whatsoever in an individual’s use of the code examples contained in this article for testing. Using these code examples are at the user’s own risk)

I have often advocated the testing of any and all Db2 table and index design decisions before they are implemented, but how exactly is this accomplished? This challenge is especially acute when there are no testing tools or personnel available. It is even more complex when there is no existing database structures or data to work with. You are in a situation where a debate is raging in a conference room about design decisions for a high volume Db2 application and no one is sure what to do. There is still hope as it is not that hard to fabricate test objects, data, and processes in order to flush out design options. The only real challenge is that there has to be a way to monitor the tests. So, the one tool that is absolutely needed is a monitoring tool, whether it be batch reporting or online. Something is needed to distinguish tests and report test results. With that in mind let’s now talk about options for testing design decisions. I wrote about reporting on performance here.

Free Testing Tools at Your Disposal

When it comes to home grown testing there are many tools available regardless of the platform or flavor of Db2 (z/OS, LUW, iSeries). Here are some of the options that will be discussed in this article:

  • SQL to generate data
  • SQL to generate SQL
  • SPUFI (Db2 for z/OS local testing)
  • DSNTEP2 and DSNTIAUL (Db2 for z/OS local testing)
  • PLSQL stored procedures (local testing)
  • REXX (Db2 for z/OS local testing)
  • Db2batch client testing program for LUW and z/OS (z/OS remote, LUW local or remote)
  • Windows PowerShell scripts (z/OS remote, LUW local or remote)
  • Unix/Linux/Windows shell scripts (z/OS remote, LUW local or remote)
  • Java (local or remote)
  • IBM Data Studio (z/OS remote, LUW local or remote, EXPLAIN)
  • VSCode Db2 extensions (z/OS remote, LUW local or remote)

I’m sure there are more, but these have been the testing tools in my playbook for many years, and many design decisions have been made after initial benchmark tests using one or more of these tools. It takes anywhere from a few hours to just a few days to conduct a test, a relatively short amount of time where important performance decisions can be made. Although this type of testing is far from perfect it is better than nothing at all and certainly better than sitting in a meeting room hashing out design decisions without a shred of evidence.

Generating Data

In a situation where a test is being proposed, but no existing application or data exists, there is obviously a need to generate the test data and SQL statements. There is also a need to generate key values that can be placed into a file and used as input to a test process. The primary tool for this generation is SQL. The primary statement to start with is the simplest of recursive SQL statements, which is a statement that generates a result set from nothing.

Generating data example, click to expand

WITH GEN_ROWS(N) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N+1 FROM GEN_ROWS
WHERE N < 100)
SELECT N
FROM GEN_ROWS
ORDER BY N

From this it is quite easy to expand on this process to generate all sorts of data and insert it into Db2 tables for testing. In the following scenario, a test is required against a proposed employee table. This proposal is for a new application and database, so no current tables or data exists. Once we decide on the various design options we’d like to test, we’re going to have to create a table, data and statements. As long as the table definition contains the appropriate structure for the test, a primary key, any potential additional indexes, and some non-indexed columns, it is good enough to test against.

DDL example, click to expand

CREATE TABLE DANL.EMPT
(EMPNO BIGINT NOT NULL
,WORKDEPT CHAR(3) NOT NULL
,LASTNAME CHAR(50)
,PRIMARY KEY (EMPNO)
)
IN DANLDB.DANEMPT
CCSID EBCDIC;
CREATE UNIQUE INDEX DANL.XEMP2T
ON DANL.EMPT
(EMPNO ASC)
USING STOGROUP DANLSG
CLUSTER
BUFFERPOOL BP8
CLOSE NO;

Once the table is created test data can be generated and inserted in a single SQL statement. There are two key things in this SQL statement. The first is the use of the recursive common table expression to generate the rows of data. The second is the use of the RAND() function. In this particular case this function is being used to randomly assign a department code to the employee data. However, the function is extraordinarily useful for all sorts of things, especially for generating random column values, including key values.

Inserting generated data into a table, click to expand

INSERT INTO DANL.EMPT
WITH GEN_ROWS(N) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N+1 FROM GEN_ROWS
WHERE N < 100)
,GENDEPT (WORKDEPT) AS
(SELECT ‘D01’ FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ‘E01’ FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ‘E02’ FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ‘F01’ FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ‘G04’ FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ‘X01’ FROM SYSIBM.SYSDUMMY1
)
SELECT A.N
,X.WORKDEPT
,’LUKSETICH’
FROM GEN_ROWS A
INNER JOIN TABLE
(SELECT WORKDEPT, RAND() AS RANDOM_VALUE
FROM GENDEPT
WHERE A.N > 0
ORDER BY RANDOM_VALUE
FETCH FIRST 1 ROW ONLY) AS X(WORKDEPT, RV)
ON 1=1
;

Data can also be generated from data, which allows for the relatively easy generation of more variations in values. In the following example the 100 rows just generated from nothing are used to generate 100 more rows using the same WORKDEPT values, but introducing a new data column value.

Inserting additional data into a table from a table, click to expand

INSERT INTO DANL.EMPT
SELECT EMPNO + 100, WORKDEPT, ‘THOMAS’
FROM DANL.EMPT;

This type of generation can be repeated over and over with ever increasing quantities of data in order to fully populate a test table in a manner that is suitable for the test at hand. Clustering can be changed and REORG executed and then the clustering changed back in an effort to test organized and disorganized data scenarios. Any variation in table design can be made and the original generated data used to populate the various table variations. Suresh Sane had a great presentation at IDUG EMEA in 2008 entitled “Recursive SQL – Unleash the POWER!” which contains more examples of recursive SQL, including an example of generating data. If you have an IDUG membership (free) you can download this presentation.

Generating SQL Statements

Just as with generating data it is possible to generate statements in the same manner. You can generate statements from nothing using recursive SQL, or use the data in the table to generate statements that act against the table. In the following example, the data in the test table is used to generate random SELECT statements that will test random primary key index access with table access.

Generating SQL statements, click to expand

SELECT STMT,
FROM (
SELECT ‘SELECT LASTNAME FROM DANL.EMPT WHERE EMPNO = ‘ ||
STRIP(CHAR(EMPNO)) ||
‘;’
,RAND() AS DANL
FROM DANL.EMPT) AS T(STMT, DANL)
ORDER BY DANL

The generated statements can then be used as input to SPUFI, DSNTEP2, or DSNTIAUL, etc. to run a performance test. One of the drawbacks of the above statement is that, while it will test random access to the test table, every statement will find data. What if the desired test should include misses as well as hits? One choice would be to randomly delete data once the statements are generated, and another choice would be to generate the statements independent of the data in the table. Once again, recursive SQL is used to generate something from nothing.

Generating SQL statements including misses, click to expand

WITH GEN_ROWS(N) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N+1 FROM GEN_ROWS
WHERE N < 100)
SELECT ‘SELECT LASTNAME FROM DANL.EMPT WHERE EMPNO = ‘ ||
CHAR(INT(RAND()*POWER(10,INT(RAND()*6)))) ||
‘;’
FROM GEN_ROWS

Many times, I’ll use a simple REXX program to test a SQL statement in a loop. One version of a REXX test is used to test access to a table or tables based upon a set of input keys read from a file, an example of which is given later in this article. Instead of generating statements they simply generate keys that are then placed in a file for input into the program.

Generating keys for input into a program, click to expand

SELECT STRIP(CHAR(EMPNO))
,RAND() AS DANL
FROM DANL.EMPT
ORDER BY DANL;

IBM Data Studio and VSCode Db2 Extensions

IBM Data Studio is a free download from the IBM web site. I myself have become a big fan of IBM Data Studio and use it every single day in support of my databases. Although there are no new features being added to IBM Data Studio at this time, IBM has been updating it in order to keep it functional while working on the eventual replacement which will be in the form of VSCode (and Eclipse Theia) Db2 extensions (currently available). I have been using IBM Data Studio to perform the following:

  • Check on the database schema
  • Generate DDL
  • Generate data models
  • Create and test stored procedures
  • Test and run SQL
  • EXPLAIN SQL

I have even used IBM Data Studio to create a data model and then generate the DDL to deploy the database on Microsoft SQL Server! You can download IBM Data Studio by following this link.

Testing Options

Whether it is a major design decision, SQL tuning, or a plan to utilize a new Db2 feature, I always advocate performing some sort of testing to add evidence which backs up or refutes a particular decision. While I use the Db2 EXPLAIN facility to analyze SQL statements I also perform simple benchmarks on any SQL or database changes that support an access path change. The tools are not 100% reliable so the safe thing to do is test.

Deciding how to perform a test is matter of personal preference, but it also depends on what exactly the test is doing. For example, if it is a test of a change to a single SQL statement that is executed locally then perhaps SPUFI, REXX, or a native stored procedure is the way to go. If it is a test of a feature that relies on a specific connection setting then it may require using something like the Db2 Client, VSCode extensions, IBM Data Studio or perhaps a short program written in Java or Python. More extensive testing may involve SQL data generation, SQL statement generation, or perhaps utilizing shell scripts or Db2batch in some capacity.

Db2batch

Db2batch is a benchmarking tool that is a part of the Db2 client software. Db2batch is Db2 client a system command that is executed from a shell command prompt. All that is needed is a connection defined within the client to the target database, whether that target is Db2 for z/OS or Db2 for LUW, although there is greater functionality when Db2 for LUW is the target. Documentation is in the IBM Knowledge Center.

There are several options that control the execution of Db2batch where SQL statements can be grouped, and executions of groups can be controlled. This is a simple example of the execution of Db2batch on an AIX server using a KornShell script.

KornShell script to invoke Db2batch, click to expand

#! /user/bin/ksh
#
# This script is used to execute db2 batch.
#
#——————————————————-
if [[ $# = 0 ]]
then print “usage: sh db2_db2batch <dbname> <userid> <password>”
exit;
fi
if [[ $1 = “-h” ]]
then print “usage: sh db2_db2batch <dbname> <userid> <password>”
exit;
fi
if [[ $# != 3 ]]
then print “usage: sh db2_db2batch <dbname> <userid> <password>”
exit;
fi

in_dbname=$1
in_uid=$2
in_pw=$3

db2batch -d $in_dbname -f db2batch.input.sql -a $in_uid”/”$in_pw -r db2batch.out.txt,db2batch.out.summary.txt -g off -iso CS

The Db2batch input file looked like this:

Db2batch input example, click to expand

— This set of SQL statements can be used to test a DB2 data server
— It is a good way to practice monitoring or try to stress a box

— This next line sets control options
–#SET ROWS_FETCH -1
–#SET ROWS_OUT 1
–#SET PERF_DETAIL 1
–#SET ERROR_STOP NO
–SET# TIMESTAMPT

— This begins a query block. The repeat count follows the block
–#BGBLK 20
SELECT 1 FROM SYSIBM.SYSDUMMY1;
— This ends a query block
–#EOBLK

Windows PowerShell

PowerShell is the replacement for the DOS command shell in Windows. I was introduced to PowerShell in Db2 by Ember Crooks and her excellent DataGeek blog. Ember also posted a nice “hello world” PowerShell example. I was in the midst of a migration of a Db2 database from AIX to Windows, and initially set up my various support scripts using the DOS shell. After I read her blog, I got started with a PowerShell training course and in short order all my standard scripts for managing the database were rewritten in PowerShell. The great thing about PowerShell is that there is a tremendous support community on the internet, so solutions to problems are often solved simply with a Google search!

PowerShell is now my “go to” for script based remote tests to Db2. Here is an example of a PowerShell script that reads an input file containing random keys and invokes a Db2 for z/OS REST service. In this particular test I was invoking the REST service thousands of times as a stress test.

PowerShell invoking a Db2 for z/OS REST Service, click to expand

# SSL stuff starts here
if (-not(“dummy” -as [type])) {
add-type -TypeDefinition @”
using System;
using System.Net;
using System.Net.Security;
using System.Security.Cryptography.X509Certificates;

public static class Dummy {
public static bool ReturnTrue(object sender,
X509Certificate certificate,
X509Chain chain,
SslPolicyErrors sslPolicyErrors) { return true; }

public static RemoteCertificateValidationCallback GetDelegate() {
return new RemoteCertificateValidationCallback(Dummy.ReturnTrue);
}
}
“@
}

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = [dummy]::GetDelegate()
#SSL stuff ends here

# setting up for the REST call
$headers = New-Object “System.Collections.Generic.Dictionary[[String],[String]]”
$headers.Add(“Accept”, ‘application/json’)

# replace the userid and password here
$headers.Add(“Authorization”, ‘Basic DANL1234:********’)

# initialize the output file
$result | Out-File “K:\testoutput.txt”

# in this loop you are reading the test input file of records.
# change the input location to wherever you put the file
# change the input file name to testinput10k.txt to test with 10,000 identifiers
# once you verify it is working comment out the Out-File line for testing remote performance only
foreach($line in [System.IO.File]::ReadLines(“K:\testinput.txt”))
{
$guid = @{GUID=${line}}
$jsonguid = $guid | ConvertTo-Json
$result = Invoke-RestMethod https://dsndb2d0.testdb2.com:741/services/collid1/getTestGuid -Method Post -ContentType ‘application/json’ -Headers $headers -Body $jsonssn
$result | Out-File “K:\testoutput.txt” -Append
}

Java

There is a really good chance that the workstation you are using for daily business activities has Java installed on it, including the Java Development Kit. If the JDK is not present on your machine you can easily download and install it. Choices include the Oracle JDK or OpenJDK. If the JDK is installed on a workstation or server it is quite easy to compile and test Java programs. Here is an example of a “hello world” program.

Java hello world example, click to expand

public class HelloWorld {
public static void main(String[] args) {
System.out.println(“Hello world..!!!”);
}
}

The next example processes a single SQL statement and displays the result. It also contains optional code that sets the application compatibility and package set for testing of that functionality and associated package settings on z/OS.

Java Db2 simple example, click to expand

/*
Compile this with `javac DB2Test.java`
Then run it with `java -cp “/C/IBM/db2jcc4.jar:.” DB2Test2 <JDBC URL> <username> <password>`
You might need single quotes or escaping on the password…
This code was taken from http://www.justexample.com/wp/connect-db2-java/ then extended a bit.
It is unclear how the original was licensed. This work is licensed under the MIT license,
Copyright 2017, Jonathan Gnagy.
https://opensource.org/licenses/MIT
*/

import java.sql.*;
import java.util.Properties;
//import java.sql.Connection;
//import java.sql.DriverManager;
//import java.sql.SQLException;

public class DB2Test2 {

public static void main(String[] args) {
String jdbcClassName=”com.ibm.db2.jcc.DB2Driver”;
String url = args[0];
String user = args[1];
String password = args[2];
// String currentPackageSet = args[3];
String server;
String dbname;
Statement stmt1;
ResultSet rs1;

Connection connection = null;
try {
//Load class into memory
Class.forName(jdbcClassName);

//Set properties
Properties prop = new Properties();
prop.put(“user”, user);
prop.put(“password”, password);
// prop.put(“clientApplcompat”, “V11R1”);
// prop.put(“currentPackageSet”, “NULLID”);


//Establish connection
connection = DriverManager.getConnection(url, prop);

} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(connection!=null){
try {
stmt1 = connection.createStatement();
rs1 = stmt1.executeQuery(“select current date from sysibm.sysdummy1”);
while (rs1.next()) {
server = rs1.getString(1);
System.out.println(server);
}
rs1.close();
stmt1.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}

This next example is from when I was conducting a test using the ClientUser JDBC property to set the Db2 for z/OS special register CURRENT CLIENT_USERID used for auditing purposes in a system-period temporal table.

Java Db2 example setting client userid, click to expand

/*
Compile this with `javac DB2Test3.java`
Then run it with `java -cp “/C/IBM/db2jcc4.jar:.” DB2Test3 <JDBC URL> <username> <password>`
You might need single quotes or escaping on the password…
This code was taken from http://www.justexample.com/wp/connect-db2-java/ then extended a bit.
It is unclear how the original was licensed. This work is licensed under the MIT license,
Copyright 2017, Jonathan Gnagy.
https://opensource.org/licenses/MIT
*/

import java.sql.*;
import java.util.Properties;
//import java.sql.Connection;
//import java.sql.DriverManager;
//import java.sql.SQLException;

public class DB2Test3 {

public static void main(String[] args) {
String jdbcClassName=”com.ibm.db2.jcc.DB2Driver”;
String url = args[0];
String user = args[1];
String password = args[2];
// String currentPackageSet = args[3];
String server;
String dbname;
Statement stmt1;
ResultSet rs1;

Connection connection = null;
try {
//Load class into memory
Class.forName(jdbcClassName);

//Set properties
Properties prop = new Properties();
prop.put(“user”, user);
prop.put(“password”, password);
// prop.put(“clientApplcompat”, “V11R1”);
// prop.put(“currentPackageSet”, “NULLID”);


//Establish connection
connection = DriverManager.getConnection(url, prop);

} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(connection!=null){
try {
stmt1 = connection.createStatement();
connection.setClientInfo(“ClientUser”, “DanL”);
String sql = “UPDATE DANL.ALGDO3 ” +
“SET SYS_SRC_CD = ‘DANJDBC’ ” +
“WHERE NH_GUID = ‘111111111’ ” +
“AND DISB_ONST_DT = ‘2020-05-06’ ” +
“AND DISB_ONST_TYP_CD = ‘123’”;
stmt1.executeUpdate(sql);
connection.setClientInfo(“ClientUser”, “Luksetich”);
sql = “DELETE FROM DANL.ALGDO3 ” +
“WHERE NH_GUID = ‘111111111’ ” +
“AND DISB_ONST_DT = ‘2020-05-06’ ” +
“AND DISB_ONST_TYP_CD = ‘123’”;
stmt1.executeUpdate(sql);
stmt1.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

PLSQL

SQL Procedural Language is easy to learn, especially if you already know SQL. The instruction set is pretty limited and well documented, and a native stored procedure can be defined and compiled into an executable via normal SQL execution avenues (e.g. SPUFI or Db2 CLP). So, there is no reason not to use a native stored procedure to conduct performance tests. It is simply a matter of coding a loop into the stored procedure logic to run whatever SQL statements you want to test. Then, make variations and test again.

PLSQL stored procedure to conduct a performance test of a SQL statement, click to expand

CREATE PROCEDURE RANGEQUERY1 ()
VERSION V1
ISOLATION LEVEL UR
LANGUAGE SQL
QUALIFIER DANL
DYNAMICRULES BIND
P1: BEGIN

DECLARE EMPNO CHAR(10);
DECLARE LNAME CHAR(50);
DECLARE EOT INTEGER;
DECLARE RANGEQUERY VARCHAR(3000);
DECLARE RANGESQL STATEMENT;
DECLARE RANGECSR CURSOR FOR RANGESQL;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET EOT = 1;

SET EOT = 0;

FOR T AS
WITH GEN_ROWS(N) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N+1 FROM GEN_ROWS
WHERE N < 100)
SELECT CHAR(INT(RAND()*POWER(10,INT(RAND()*6)))) AS EMPNO
FROM GEN_ROWS
DO
SET EMPNO = T.EMPNO;
SET RANGEQUERY =
‘SELECT LASTNAME FROM DANL.EMPT E WHERE EMPNO = ‘
|| EMPNO;
PREPARE RANGESQL FROM RANGEQUERY;
OPEN RANGECSR;
SET EOT = 0;
WHILE EOT = 0 DO
FETCH RANGECSR INTO LNAME;
END WHILE;
CLOSE RANGECSR;
COMMIT;
END FOR;
END P1

REXX

I use REXX on Db2 for z/OS extensively for testing calls to stored procedures, and for running benchmark tests on single SQL statements. I even use REXX for simple programs in production environments when simple processing is desired but no application programmers are available. REXX is a super-powerful programming language, and what I mean by this is that you can do a significant amount of programming with a modest number of commands. It is a super simple language to learn! Here is an example of a REXX program calling a stored procedure and handling a result set.

Calling a stored procedure from REXX, click to expand

/*REXX*/
/* CHECK TO SEE IF THE REXX/DB2 COMMAND ENVIRONMENT IS AVAILABLE */
/* IF IT IS NOT, THEN ESTABLISH IT. */
/* THEN CONNECT TO THE APPROPRIATE DATABASE (SUBSYSTEM) */
/* */
/* TRACE ?I */
‘SUBCOM DSNREXX’
IF RC THEN
S_RC = RXSUBCOM(‘ADD’,’DSNREXX’,’DSNREXX’)
/* */
/* CHANGE ‘DSN1’ IN THE LINE BELOW TO YOUR TARGET SUBSYSTEM */
/* */
ADDRESS DSNREXX ‘CONNECT’ ‘DSN1’
IF SQLCODE \= “0” THEN
DO
SAY “FAILURE TO CONNECT TO DATABASE”
EXIT 8
END

ADDRESS DSNREXX
/* */
/* THE FOLLOWING TWO LINES IDENTIFY THE STORED PROC TO BE CALLED */
/* AND INITIALIZE THE HOST VARIABLE AS A NUMBER. */
/* */
SPC = “DANL.DANLSP051”
CH1 = “‘N'”
CH2 = “‘0000048003′”
SC = “0”
/* */
/* NOW CALL THE STORED PROCEDURE. */
/* */
EXECSQL “CALL :SPC (:CH1, :CH2, :SC)”
SAY “SQL CODE IS ” SQLCODE
SAY “SQL CODE IS FROM SP IS ” SC
SAY “CH1 IS ” CH1
SAY “CH2 IS ” CH2
EXECSQL “ASSOCIATE LOCATOR (:LOC1) WITH PROCEDURE :SPC”
IF SQLCODE \= “0” THEN
DO
SAY “ASSOCIATE LOCATOR FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
EXECSQL “ALLOCATE C101 CURSOR FOR RESULT SET :LOC1”
IF SQLCODE \= “0” THEN
DO
SAY “ALLOCATE CURSOR FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
DO UNTIL SQLCODE \= “0”
“EXECSQL FETCH C101 INTO :F1, :F2, :F3, :F4, :F5, :F6, F7”
IF SQLCODE = “0” THEN
DO
SAY F1
END
END
SAY “FINAL SQLCODE IS ” SQLCODE
“EXECSQL CLOSE C101”

/* CLEANUP. */
/* DISCONNECT FROM THE DATABASE, AND RELEASE ALLOCATIONS. */
/* */
ADDRESS DSNREXX “DISCONNECT”
S_RC = RXSUBCOM(‘DELETE’,’DSNREXX’,’DSNREXX’)
EXIT 0

I almost always use REXX routines in combination with an online DB2 performance monitor to capture SQL performance metrics. This involves writing 2 different types of REXX routines; one for testing I/O and one for testing CPU. In either situation the REXX routine connects to a data source, declares a cursor, and then within a loop (usually between 50 and 5000 iterations) will open the cursor, fetch from the cursor, and then close the cursor. Once the loop is finished, I sometimes put an instruction in that starts an interactive trace. That way, if I am running the REXX program from a TSO prompt (or ISPF option 6) it will pause at that point, allowing me to collect the performance metrics from my online monitor. If an online monitor is not available, I will record the start and end times of my tests and pull accounting trace reports from an SMF batch reporting tool.

The only difference between my REXX for CPU and REXX for I/O programs is that the REXX for I/O program reads data in from an input file. Usually what I do is run a query against the table or tables used in the query I am testing that will extract keys I can use as input to the REXX program. I also add an additional column to the output generated from the RAND() function and sort the output on that column. In that way my input values will be random. The output generated from this query is placed in the file that is used as input to the REXX for I/O program. The following REXX routine example is used to test for CPU usage. It runs the same statement repeatedly dependent upon the loop limit you set.

REXX program to test a SQL statement for CPU consumption, click to expand

/*REXX*/
CALL INITIALIZE
/* SET A VARIABLE EQUAL TO THE DESIRED SQL STATMENT. */
SQLSTMT=”SELECT A.LASTNAME, A.WORKDEPT”,
” FROM DANL.EMP A”,
” WHERE A.EMPNO = ‘000010’”
/* PREPARE THE STATEMENT FOR EXECUTION BY DECLARING THE CURSOR, */
/* DOING A PREPARE OF THE STATEMENT, AND OPENING THE CURSOR.*/
ADDRESS DSNREXX
“EXECSQL DECLARE C1 CURSOR FOR S1”
IF SQLCODE \= “0” THEN
DO
SAY “DECLARE CURSOR FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
“EXECSQL PREPARE S1 FROM :SQLSTMT”
IF SQLCODE < “0” THEN
DO
SAY “PREPARE FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
/* OPEN/FETCH/CLOSE 50 TIMES */
DO 50
“EXECSQL OPEN C1 “
IF SQLCODE \= “0” THEN
DO
SAY “OPEN FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
DO UNTIL SQLCODE \= “0”
“EXECSQL FETCH C1 INTO :TESTMSG, :t2 “
IF SQLCODE = “0” THEN
DO
/* SAY TESTMSG */
END
END
/* HERE THE FETCH LOOP HAS ENDED (SQLCODE <> 0), AND WE CHECK WHY.*/
SELECT
WHEN SQLCODE = 0 THEN DO
END
WHEN SQLCODE = 100 THEN DO
END
OTHERWISE
SAY “FETCH FAILED WITH SQLCODE = ” SQLCODE
SAY SQLERRMC
END
/* ALL IS WELL, SO THE CURSOR IS CLOSED. */
“EXECSQL CLOSE C1”
IF SQLCODE \= “0” THEN
DO
SAY “CLOSE FAILED WITH SQLCODE = ” SQLCODE
END
/* END THE DO LOOP HERE */
END
/* TRACE ADDED TO CAPTURE INFO IN ONLINE MONITOR */
trace ?I
/* CLEANUP. */
ADDRESS MVS “DELSTACK”
ADDRESS DSNREXX “DISCONNECT”
S_RC = RXSUBCOM(‘ADD’,’DSNREXX’,’DSNREXX’)
EXIT 0
INITIALIZE:
/* CHECK TO SEE IF THE REXX/DB2 COMMAND ENVIRONMENT IS AVAILABLE */
/* IF IT IS NOT, THEN ESTABLISH IT. */
/* THEN CONNECT TO THE APPROPRIATE DATABASE (SUBSYSTEM) */
‘SUBCOM DSNREXX’
IF RC THEN
S_RC = RXSUBCOM(‘ADD’,’DSNREXX’,’DSNREXX’)
/* CHANGE ‘DSN1’ IN THE LINE BELOW TO YOUR TARGET SUBSYSTEM */
ADDRESS DSNREXX “CONNECT” “DSN1”
IF SQLCODE \= “0” THEN
DO
SAY “FAILURE TO CONNECT TO DATABASE”
EXIT 8
END
/* INITIALIZE STACK AND FREE ANY */
ADDRESS MVS “DELSTACK”
ADDRESS MVS “NEWSTACK”
RETURN;

The following REXX routine example is used to test for I/O dependent upon the sequence of values in the associated input file.

REXX program to test a SQL statement for I/O wait time, click to expand

/*REXX*/
CALL INITIALIZE
/* SET A VARIABLE EQUAL TO THE DESIRED SQL STATMENT. */
SQLSTMT=”SELECT A.LASTNAME, A.WORKDEPT”,
” FROM DANL.EMP A “,
” WHERE A.EMPNO = ?”
/* PREPARE THE STATEMENT FOR EXECUTION BY DECLARING THE CURSOR, */
/* DOING A PREPARE OF THE STATEMENT, AND OPENING THE CURSOR. */
ADDRESS DSNREXX
“EXECSQL DECLARE C1 CURSOR FOR S1”
IF SQLCODE \= “0” THEN
DO
SAY “DECLARE CURSOR FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
“EXECSQL PREPARE S1 FROM :SQLSTMT”
IF SQLCODE < “0” THEN
DO
SAY “PREPARE FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
/* */
DO 50
PARSE PULL empcusr .
“EXECSQL OPEN C1 USING :empcusr “
IF SQLCODE \= “0” THEN
DO
SAY “OPEN FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
/* FETCH LOOP. */
DO UNTIL SQLCODE \= “0”
“EXECSQL FETCH C1 INTO :TESTMSG, :t2 “
IF SQLCODE = “0” THEN
DO
END
END
/* HERE THE FETCH LOOP HAS ENDED (SQLCODE <> 0), AND WE CHECK WHY.*/
SELECT
WHEN SQLCODE = 0 THEN DO
END
WHEN SQLCODE = 100 THEN DO
END
OTHERWISE
SAY “FETCH FAILED WITH SQLCODE = ” SQLCODE
SAY SQLERRMC
END
/* ALL IS WELL, SO THE CURSOR IS CLOSED. */
“EXECSQL CLOSE C1”
IF SQLCODE \= “0” THEN
DO
SAY “CLOSE FAILED WITH SQLCODE = ” SQLCODE
END
/* END THE DO LOOP HERE */
END
/* TRACE PLACED AT END TO CATCH DATA IN ONLIN MONITOR */
trace ?I
/* CLEANUP. */
/* DISCONNECT FROM THE DATABASE, AND RELEASE ALLOCATIONS. */
ADDRESS MVS “DELSTACK”
ADDRESS DSNREXX “DISCONNECT”
S_RC = RXSUBCOM(‘ADD’,’DSNREXX’,’DSNREXX’)
EXIT 0
INITIALIZE:
/* CHECK TO SEE IF THE REXX/DB2 COMMAND ENVIRONMENT IS AVAILABLE */
/* IF IT IS NOT, THEN ESTABLISH IT. */
/* THEN CONNECT TO THE APPROPRIATE DATABASE (SUBSYSTEM) */
‘SUBCOM DSNREXX’
IF RC THEN
S_RC = RXSUBCOM(‘ADD’,’DSNREXX’,’DSNREXX’)
/* */
/* CHANGE ‘DSN1’ IN THE LINE BELOW TO YOUR TARGET SUBSYSTEM */
/* */
ADDRESS DSNREXX “CONNECT” “DSN1”
IF SQLCODE \= “0” THEN
DO
SAY “FAILURE TO CONNECT TO DATABASE”
EXIT 8
END
/* INITIALIZE STACK AND FREE ANY */
/* LEFT OVER ALLOCATED FILES */
ADDRESS MVS “DELSTACK”
ADDRESS MVS “NEWSTACK”
X = OUTTRAP(TRASH.) /* SUPPRESS MESSAGES */
ADDRESS TSO “FREE F(SYSUT1,SYSUT2)”
X = OUTTRAP(OFF) /* SUPPRESS MESSAGES */
/* ALLOCATE THE INPUT FILE */
/* FOR THIS EXAMPLE THE INPUT IS A LIST OF EMPNO */
/* VALUES, ONE PER RECORD, FOR EXAMPLE */
/* 000010 */
/* 000110 */
ADDRESS TSO “ALLOC F(SYSUT1) DA(idinpt.text) SHR “
IF RC /=0 THEN DO
SAY “FAILURE TO ALLOCATE INPUT DATASET”
EXIT 8
END
/* READ THE INPUT FILE INTO THE STACK */
ADDRESS TSO “EXECIO * DISKR SYSUT1 (FINIS”
IF RC /=0 THEN DO
SAY “FAILURE TO READ INPUT DATASET”
EXIT 8
END
ADDRESS TSO “FREE F(SYSUT1)”
RETURN;

Summary

It is just not enough to rely on thoughts and feelings about a new design, or to believe the output of an EXPLAIN statement. You need to test your concepts, and flush out any design decisions. You need to test SQL or database structure changes to support a SQL tuning change. You can do this all quickly and easily using a simple set of free tools to accomplish the task. Dive in and begin testing your ideas. It’s fun and easy!

Leave Comment

Your email address will not be published.