This is the first of several blog articles addressing Db2 application 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. The application developer is relieved 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. This obscuring of the physicality of the data store 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 data server 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.
Don’t Call Db2
I will go into more detail as this blog series continues, but the main goal of Db2 application programming should be to call the data server only when necessary. This should be a rule when designing the application program as reducing calls to the database is purely a programming measure and there is nothing that can be done within the database engine to reduce the number of times it is called. The most obvious infraction is the “ping” transaction.
SELECT 1 FROM SYSIBM.SYSDUMMY1;
One of the uses of this statement is to check if the database is available. Why? If the database is available, you’ll receive a response from a call to the database. If the database is not available, you’ll receive a database is not available message, at which point you can attempt to reestablish the connection. Avoiding the “ping” transaction does require more responsibility for the application. The application program should be interpreting some messages returned from the data server and simply not echoing the message back to the calling service or end user. There is quite a variety of Db2 error codes and reason codes that can be handled by an application without requiring user intervention.
Another way to reduce calls to the database is to cache code values locally. There are often many encoded values that are stored in the database in support of an application that don’t change very often, for example, the 50 United States. These could be store in the database as encoded values (e.g. IL for Illinois), but the table they’re stored in doesn’t have to be called every time an address is entered into an application. Code values can be cached locally within the application and refreshed at intervals appropriate for the codes.
In this blog and the blogs that follow I’ll talk about all sorts of coding techniques that can be used to reduce calls to the database, but this should be the primary objective when thinking about Db2 application performance.
- Code joins
- Use multi-row fetch
- Adjust the fetch size
- Use data change table references
- Consolidate calls into stored procedures
- Utilize Db2 automation
The Impact of Call Reduction
In you’re an application developer you may not have easy access to Db2 monitoring or Db2 performance metrics in general. So, if this information is not easily accessible you could simply make some assumptions. Imagine a call such as this (using the Db2 sample database for an example):
SELECT LASTNAME FROM DSN81210.EMP WHERE EMPNO = ‘000010’;
This is likely a very efficient statement. Given no information about the cost to execute this statement you can assume that the network, cross-memory call, and statement compile cost might be at least as expensive as the execution of the statement itself inside the database engine. So, coding something like this:
SELECT 1 FROM SYSIBM.SYSDUMMY1;
SELECT LASTNAME FROM DSN81210.EMP WHERE EMPNO = ‘000010’;
You can safely assume that the cost is at least double. So, eliminating the unnecessary call can make the process or service half as expensive. The perceived response time for the transaction might be unnoticeable to a human being, but for the system and network it can really add up. The cost savings from call reductions can be dramatic! At one customer site about 10 million dollars per year was saved by simply caching code values locally.
Coming in the Next Blog
In the next blog article, I’ll be talking about some programming techniques which basically will deliver multiple pieces of information in single SQL calls, continuing the focus on reducing calls to the database.
Okeg