I spend every day of my life since 1990 as a Db2 DBA both on Db2 for z/OS and Db2 for LUW. Most of this time has been in support of various application teams as they build systems that use Db2 as their data store. During that time, I’ve collected a lot of knowledge that makes my job easier and gets the answers to the developer’s questions in a timely fashion. Here are some of the latest tools and techniques that have made my life easier.
IBM Data Studio and IBM Data Server Manager
These are both free tools downloadable 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. 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:
http://www-01.ibm.com/support/docview.wss?uid=swg24037681
IBM Data Server Manager is, according to IBM, the ultimate replacement for IBM Data Studio. I have not used IBM Data Server Manager as much as I’ve used IBM Data Studio. While they share many of the same features, IBM Data Server Manager is more robust in database monitoring and less so in the database and application development realm. You can download IBM Data Server Manager by following this link:
https://www.ibm.com/developerworks/downloads/im/dsm/index.html
Test Functionality with IBM Data Studio and the Connection String
Something I have been doing a lot of lately is supporting Java development. These applications are often using a specific framework, and thus simplifying the development process. Many times, things don’t work exactly as expected, and that requires a bit of investigation. In other situations, you’d like to promote a feature of Db2, but no development team is interested in being your guinea pig. Some of these things are easily resolved with some simple tweaks of the Db2 connection string, and many times can be tested using IBM Data Studio since IBM Data Studio is in itself a Java application.
JDBC Trace
When things aren’t exactly working out they way they were advertised or expected a really helpful tool to employ is a JDBC trace. It’s simple to activate the trace simply by adding the following to the Db2 JDBC connection string:
traceFile=trace;traceFileAppend=false;traceLevel=-1;traceDirectory=C:\Dantrace;
This trace setting will turn on all of available trace details, but you can set individual traces by substituting the “-1” with the individual trace designation or an addition of the individual designations.
TRACE_NONE=0,
TRACE_CONNECTION_CALLS=1,
TRACE_STATEMENT_CALLS=2,
TRACE_RESULT_SET_CALLS=4,
TRACE_DRIVER_CONFIGURATION=16,
TRACE_CONNECTS=32,
TRACE_DRDA_FLOWS=64,
TRACE_RESULT_SET_META_DATA=128,
TRACE_PARAMETER_META_DATA=256,
TRACE_DIAGNOSTICS=512,
TRACE_SQLJ=1024,
TRACE_META_CALLS=8192,
TRACE_DATASOURCE_CALLS=16384,
TRACE_LARGE_OBJECT_CALLS=32768,
TRACE_SYSTEM_MONITOR=131072,
TRACE_TRACEPOINTS=262144,
TRACE_ALL=-1
Statement Concentration
I was encouraging a customer to utilize dynamic statement concentration for some of their Java applications that were using literal values. As a reminder statement concentration is the ability for Db2 to take similar dynamic statements with literal values and convert the literal values to parameter markers in the statement cache, which allows for a greater level of cache hits. This avoids long prepares and can result in dramatic statement performance improvement. The development staff in this situation had no time for testing. So, I was able to provide the testing via IBM Data Studio and the Db2 JDBC connection string by adding the following to the “optional” tab in the driver properties window for the connection:
statementConcentration=2
Once this change was made I could perform all the initial testing and make some initial recommendations.
Statement EXPLAIN and Testing
IBM Data Studio and IBM Data Server Manager can be used to EXPLAIN and test SQL statements. In addition to providing an access path report and graph, you can actually use these tools for benchmark performance testing of statements that process a greater than trivial amount of data. This would be in coordination with an appropriate server-side performance monitor, and not a simple elapsed time test on the client side.
Executing Stored Procedure Calls
You can use IBM Data Studio to test stored procedure calls, whether or not they are native or external stored procedures. Simply navigate to the schema and procedure, right click, and put in whatever parameters are required. Any result output parameters and result sets will appear in the SQL results window. This is also extremely useful for invoking one of the many IBM supplied stored procedures. For example, the SYSPROC.ADMIN_INFO_SYSPARM will give you information about Db2 for z/OS system parameters.
DB2 for z/OS REXX Scripting
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!
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.
db2batch for testing
This extremely flexible benchmarking tool can be used against a DB2 for LUW or DB2 for z/OS database. I’ve had problems getting it to work in the past but am currently using the db2batch command in the IBM Data Server Client version 10.5.2 quite successfully against a DB2 for z/OS database. The results will include elapsed time output per statement and at a summary level, with the option of much more detailed performance metrics for a DB2 for LUW database (including monitor metrics). The input to db2batch can be one or more SQL statements grouped into one or more transactions (groupings) with an iteration set for each transaction. You can even set up a parameter file to include input parameters to all of the SQL statements in the benchmark, although this can be a bit tricky and take some trial and error to set up.
I typically set up batches of SQL statements to test for I/O or CPU performance much in the same way I run tests against DB2 for z/OS using REXX. If I want to test for random I/O performance I’ll code some clever SQL to generate random input for my statements, such as this query against the DB2 sample table EMP (or EMPLOYEE).
WITH RANDOM_EMPNO(EMPNO) AS
(SELECT INT(RAND()*POWER(10,INT(RAND()*6)))
FROM SYSIBM.SYSDUMMY1)
SELECT E.EMPNO, E.LASTNAME
FROM EMP E
WHERE E.EMPNO = (SELECT SUBSTR(DIGITS(EMPNO),4,6)
FROM RANDOM_EMPNO)
Db2 Features that Really Save Time and Money
When programming teams are understaffed or deadlines tight these Db2 features in particular have saved my development teams significant time and effort.
Db2 Native Stored Procedures
These are a total time saver for database intensive transactions, are easy to code, can be deployed quite easily, and can give you a significant performance advantage as well. I recently became involved in a Java application that was consistently reading a collection of Db2 tables on Db2 for z/OS, parsing through the data, and then returning a result to an online request. This was happening to the tune of 20 million times per day. I was able to construct a stored procedure that did all the heavy lifting before returning a result to the application saving considerable CPU resources. Native procedures are zIIP eligible on Db2 for z/OS so it was a win-win. This stored procedure was written and tested in IBM Data Studio, but when it came time to deploy it we used DSNTEP2 with the SQLFORMAT(SQLPL) option.
System-Period Temporal Tables
Many applications need historical data, whether it be for auditing purposes or simply maintaining a transaction history. This typically involves some sort of history table that matches the base table nearly exactly. Whenever the application changes some data it first copies the data to the history table. In addition, when the table history needs to be read additional SQL is needed. Once I began introducing application programmers to system-period temporal tables they rejoiced. This feature saves them significant programming effort. System-period temporal management is automated in the Db2 engine with the maintenance of temporal start and end times, and the automatic movement of the data from base table to history table. In addition, time travelling queries automate SQL coding for historical data. If you haven’t yet explored the use of system-period temporal tables you should!
Triggers
Triggers are SQL based processes that are attached to tables and activated in response to DML statements. Triggers exist as a means of controlling and guaranteeing certain processing happens in a centralized fashion when DML is issued against a table. This includes such functionality as:
- Data validation
- Data replication and summarization
- Automated population of column values
- Stored procedure and UDF invocation
- Messaging
- Advanced referential integrity enforcement
Triggers save programming time and effort, and can be a performance advantage as well!
Summary
A DBA’s job can be a demanding one, and I hope these random tips can be used to make life a bit easier!