This is the Seventh and last of several blog articles addressing Db2 performance. All the content of these articles will be based upon Db2 for z/OS. However, much of it can also be applied to Db2 for LUW. The articles will address mostly application performance regarding coding efficient SQL statements as well as efficient Db2 application coding techniques and settings. There is a saying in the industry, “it depends”, which is just another waying of saying “I don’t know”. While these articles will avoid using either of these terms, please keep in mind that your result can and will be different. With that in mind any design decision should be thoroughly tested prior to implementation. For testing references please see
https://www.db2expert.com/db2expert/testing-db2-design-decisions/
Call Only When Necessary
Yes, indeed the most efficient SQL statement is the one that is never executed. There are two things to remember about Db2. First, it’s a black box of sorts. That is, under the covers it is managing data in a vast number of physical datasets, maintaining the associated metadata that obscures the actual physical location and organization of the data. Thus, relieving the application developer of any knowledge of the physicality of the data they are accessing. All the developer needs to know is the tables and columns they need to access. That comes with an associated cost such that a database call will never be as cheap as a native file system call. Second, any call to Db2 from an application program (all calls to Db2 are from a program) is an external call from that application program. For example, if the call is in a CICS program or COBOL batch program to Db2 for z/OS located in the same z/OS LPAR it is considered a cross-memory call from one address space to another. If the call is coming from a remote application on another server, then that call is not only external from the Db2 distributed address space, but it involves traversing a network as well.
So, how can calls to the Db2 be reduced? There are a lot of things that can be done in this regard, and this is the single most important thing that an application developer can do to improve performance. You see, when a DBA sees an application with excessive “in Db2” times, they can investigate individual SQL statements that the application executes. If they see a few SQL statements using a lot of resources it can be an easy fix without application changes, such as adding an index or running statistics. However, if what they see is a whole lot of inexpensive statements executing then there is little a DBA can do to fix the issue. In this case it’s all on the application developer to reduce the number of calls.
Please refer to these articles for specific recommendations:
- Call avoidance recommendations
- Using SQL joins to avoid calls to the data server
- Using subqueries to reduce calls to the data server
- Using stored procedures to reduce call to the data server
- Using data change table references to reduce calls to the data server
- Multi-row operations
In this article I’m going to discuss ways to improve performance in modern service-oriented and microservice applications!
Modern Applications
My definition of a modern application is one in which is developed using Agile development practices and DevOps automation to deliver application services and microservices to a cloud platform. These services are typically written in Java and are typically microservice oriented, meaning that they serve only a minor task, function, or method for a very specific piece of information, and are called as a component to a much larger application that includes many services and microservices. Many of these services are built using an integrated development environment (IDE) that may provide data access objects which offer an abstract interface to a database. This is achieved by mapping database tables to application objects, and then generating SQL code automatically to access the database tables.
While this modern application development leads to a faster and more flexible deployment of application components it does present some challenges regarding application and database performance. In this article I’ll address some of the challenges and offer advice on how to avoid them or optimize the database access in these situations.
Services Calling Services
Service oriented Agile development practices using frameworks and open software components are important for cost savings related to application development. However, if the framework used and the development techniques are not uniquely controlled (and they often aren’t), then this could lead to some sloppy design, which could lead to redundant service calls as shown in the following image.
It is important that there is a proof of concept, or at least accurate testing involving the data server, to be sure that the data server is being called the least number of times for a given service or transaction.
During my work for one customer each microservice included an optional “test” mode, which could be invoked via a global parameter. When in “test” mode the application would log the service name and entry and exit time each time a service was called. In that way we could track the relationship between a business transaction and the various service calls that supported that transaction. The result of this policy resulted in a tremendous amount of machine time savings, improved transaction response times, and no major impact to application development time. This was because we could catch redundant service calls during testing.
Calling the database server too often is the number one waste of machine resources that I have witnessed in all my years of working with Db2.
GUIDs, UIDs, UUIDs, and Clustering
Clustering is where Db2 attempts to keep rows in a table in sequence as defined by a clustering index. Clustering is important for sequential application processing, which is complimented by Db2 for z/OS performance enhancers sequential detection and index look-aside, and in Db2 for LUW with sequential detection. A clustering index is defined by a database administrator and is required on Db2 for z/OS, but optional on Db2 for LUW.
Db2 can achieve incredibly high read rates in support of batch processing when a sequential reader (usually the driving cursor for a process) is reading from a table in the order of the clustering key and the table is reasonably well organized. Depending upon insert and update activity and available free space in the table space, table space REORGs may be required at certain intervals to maintain this high efficiency. The cursor fetching the data should use the clustering key in the predicate, and in the ORDER BY clause, to ensure the best performance. Thus, it is important for the application developer to work with the database administrator to coordinate on establishing clustering.
UUIDs are standard practice for many modern designs that incorporate an object-relational data stack. A standardized UUID is randomized based upon an internal time clock, but in practice it could be generated by any number of different algorithms. When UUIDs are introduced into a database design they can impact the decisions that are made relative to indexing, clustering, and free space! Therefore, it is important to understand how these IDs are generated for a particular database design and make appropriate accommodations. An important thing to keep in mind is are the generated IDs sequential or random in nature. This has dramatic impacts on table free space and clustering. Perhaps it would be fruitful to address the need for natural keys for high volume tables, or possibly compound UUID keys for critical parent-child relationships.
It is possible to negotiate the natural key on the main table as an “in” for a database design. Allowing for clustered access based upon that natural key, and then cluster the rest of the tables by the UUID of that parent table, whether those keys be identifying or non-identifying. Remember this is something that is being done for high performance. If high performance is not an expected feature of the application, then there is no need to argue with the design team over primary keys or clustering.
A common clustering between two related tables can be beneficial for random readers as well as most sequential readers. A good example is a table relationship between a parent table and child table that is identifying. Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity’s primary key.
When traversing the index of the inner table (TABLE2 in this example) via the primary key index, key values for multiple rows will be co-located. If TABLE2 is well organized, then all the rows related to TABLE1 will be clustered together. This will minimize the number of random read operations on behalf of the query. Certain things, such as the bind parameter RELEASE(DEALLOCATE), including for high performance DBATS, can also enable the built-in performance enhancers sequential detection and index look aside.
Clustering, RELEASE(DEALLOCATE), high performance DBATs, and usage of UUIDs and other system-generated keys are something that should be discussed with a database administrator when creating database tables and performance is a concern.
Stored Procedures using Arrays
The proliferation of modern application development includes the development of remote batch processing. These applications operate in a similar fashion to traditional batch processing, but because they are cloud-based remote applications they can experience significant network latency. One enhancement to a remote batch process that I was involved with recently included passing array variables into a stored procedure that then processing a batch of work in a single call to the data server. This is described in some detail in my previous article discussing multi-row processing.
Chunking
Batch processing using Java is becoming increasingly popular since large scale z/OS based batch programs are being rewritten for deployment in the cloud. It is important for application developers to understand proper processing of such things like checkpoint/restart, proper cursor positioning, proper transaction control, and efficient driving cursor design. There is a Java batch processing standard, JSR 352, which is a good starting point for understanding proper batch processing. A lot of the standard follows common mainframe batch practices that have been well established for many years.
Chucking is basically equivalent to old school checkpoint restart processing, where a driving cursor or file used as input is controlled by a persistent component that records the current position of the process. It also controls transactions and commit processing, and the saved information can be used as restart after a failure in the batch.
There is a correct way and an incorrect way to do Java batch processing. Following internet recommendations can lead to trouble with regards to performance. Make sure that it is fully understood before programming begins. Spring Batch is a pretty good open-source framework for batch processing, and with very minimal customization can be used to build very efficient batch processes (relatively speaking).
There is potentially an extremely inefficient way to do cursor driven batch processing that I have personally witnessed at several customer sites and is also an often-recommended technique promoted on the internet. In this situation the batch process is driven by a database cursor. The driving cursor result set is limited to the chunk size by utilizing a FETCH FIRST clause. The rows are processed, and then the chunk is committed. The last identifier used in the previous chunk is then used to drive the cursor for the next chunk. The process repeats until the cursor finally returns no data, and then the batch process ends. This is bad for two reasons. First, multiple cursors are being sent to Db2 when only one is necessary. Second, if the driving cursor results in a materialized result inside Db2 then performance can be seriously compromised. In that situation Db2 is reading all the data that qualifies, putting that into a workfile, returning only a subset of the data and throwing the rest away. Then the process commits, and the whole thing starts again. Extremely inefficient.
DO NOT DO THIS!
The correct way to do Java batch processing when using a driving cursor is to keep that driving cursor open for the duration of the batch job. You can use the resultSetHoldability property of the IBM Data Server Driver to keep cursors open across a commit. In this way the driving batch cursor is only processed once during the entire batch, and chunks can be committed without closing the cursor. This requires separate processes for the reader and processor so that the reader keeps the cursor open and feeds the processor data to update the database and commit its chunks. In other words, the reader must remain active and keep state. The separate process to update the database does not have to maintain state. Of course, the process doing the reading will have to maintain state during the entire duration of the batch execution.
Another important IBM Data Server Driver property to consider is the fetchSize, which controls how often the Db2 client goes to the server to get data by controlling the number of rows that are returned for a database fetch operation. This is different than an application fetch. An application may fetch data that is already in the client cache, and the client will only go to the server and fetch an additional number of rows when the cache is exhausted. This manages the message traffic to the data server across the network. By default, the client will fetch the entire result set into memory so that may be impractical due to memory limitations on the client. So, it is more appropriate to set the fetchSize to something that balances client memory with network latency, as well as an appropriate chunk size that balances concurrency with performance. Committing too often may present performance problems and committing too infrequently may introduce locking and concurrency problems. If using a flat file to drive a batch process it can help if that file is sorted in a sequence that matches the cluster of the tables.
Coming in the Next Blog
I’m going to re-explore EXPLAIN since it has grown way beyond a PLAN_TABLE.