Slow SQL and underperforming batch jobs on IBM i are often symptoms—not root causes. In this practical guide, Joydip Kanjilal walks through 12 proven techniques to diagnose and fix Db2 for i performance issues using real-world tools like Visual Explain, Plan Cache, and Index Advisor. From access plans and indexing strategy to buffer pools and commit frequency, this article delivers actionable insights RPG and Db2 developers can apply immediately to improve system performance and efficiency.
By Joydip Kajilal
When tuning IBM i performance, you will need to consider several factors. For example, if your batch jobs are slow, you are likely looking at one or more of the following: bad access plans, old statistics, or incorrect indexing strategies. To successfully tune Db2 performance, you need to exercise solid discipline and follow the best practices discussed in this article.
This article provides insights on how to perform real-world diagnostics, optimize behavior, index your data, and troubleshoot your batch job, with an emphasis on practical, ready-to-use examples. Through the 12 fixes identified in this article, you will also learn about diagnosing real-time issues, troubleshooting batch jobs, implementing appropriate data indexing, etc.
Db2 Performance: Why it Matters
Db2 for i processes workloads using a cost-based optimizer, meaning the optimizer selects an access path based on the information available to it, not on how the developer intended it to be processed. IBM has two basic recommendations: gather statistics to execute queries efficiently and create indexes to access results quickly and easily. Indexes are useful in both runtime processing and statistics. Hence, the quality of the index and the quality of the statistics are inherently interdependent.
Here are some of the widely used Db2 for i optimization tools:
Visual Explain: This is a tool that can display query implementation for SELECT, INSERT, UPDATE, DELETE in a graphical interface
Plan Cache (SQL Plan Cache): This tool comprises a plethora of information related to SQE queries that can be viewed through System i Navigator GUI
Index Advisor: This is yet another optimization tool - a proactive query optimizer that analyzes row selection and recommends new indexes based on the queries executed on the system.
- Start with a plan
If you observe performance issues with your SQL, you should first check the access path. IBM recommends using query optimization tools that include the plan cache and query monitoring data. If you expect index-based access but the optimizer produces a table scan, you can easily identify the potential problem.
Let us understand this with an example. Suppose you're performing a product data search that filters by order number and order status. In this case, you'd typically use a narrow index. However, if the optimizer returns a table scan, you can typically resolve the issue by correcting the statistics associated with the missing or invalid key, correcting the incorrect key order, or using a function to create a key on which the filtering condition was based.
- Monitoring and Diagnostics
The following statement will return information related to active and recent database monitors on the system:
SELECT MONITOR_ID, MONITOR_STATUS, MONITOR_LIBRARY,
MONITOR_FILE, MONITOR_JOB_FILTER
FROM QSYS2.DATABASE_MONITOR_INFO;
You can use the following code snippet to create a database monitor file (snapshot) from the SQL Plan Cache:
CALL QSYS2.DUMP_PLAN_CACHE();
The Visual Explain Tool with IBM i Access Client Solutions (ACS) provides visual representation of SQL statement performance optimization and allows you to analyze the performance of an SQL statement.
There are two approaches to start the explanation of a SQL statement using the Visual Explain Tool:
-
- Explain: This will analyze the SQL statement and provide a visual representation of how the SQL statement is intended to be executed.
- Run and Explain: This will execute the SQL statement and provide a visual representation of how the SQL statement is actually executed.
The following code snippet shows how you can use the EXPLAIN PLAN statement to inspect the access plan.
EXPLAIN PLAN FOR
SELECT order_no, order_date, product_no
FROM sales_order
WHERE company = 'ABC'
AND status = 'Pending'
AND order_date >= DATE('2026-01-01');
- Establish a Baseline
Create a baseline for performance in your Db2 queries and other metrics to reference when issues arise. Establishing a baseline provides benchmarks for identifying periods of peak usage that may cause performance issues with the database system. Also, you can use your established base metrics to set quantifiable performance goals, identify performance anomalies and abnormal conditions that can be researched to prevent future issues, and make recommendations on your system's capabilities.
- Use Indexes
For a database to work properly, you should optimize its efficiency to boost performance and speed up data retrieval. Indexing is a proven technique used for improving database performance in databases such as Db2, Oracle, etc. You should remove all useless indexes to improve the performance of insert, update, and delete operations in the database.
The following code snippet shows how you can create an index on multiple columns:
CREATE INDEX ix_order_company_status_date
ON sales_order (company, status, order_date);
Using powerful tools and techniques, you can create efficient indexes for your database to facilitate fast, seamless data retrieval. This allows businesses to avoid excessive data scans and improve overall DB2 performance.
- Retrieve only the data you need
When querying data using SELECT statements, it is recommended that you specify only the data you actually need, i.e., you should only retrieve the columns you want. When you use more columns than you actually need, it will increase resource consumption, i.e., I/O, memory usage, and network latency. The solution to this is to use SELECT…WHERE statements instead of using a SELECT * statement. When more columns are included in the index, the likelihood of accessing data via the index is maximized.
For example, the following code snippet illustrates an example of an inefficient piece of code that retrieves all columns from the sales_order table:
SELECT *
FROM sales_order;
The following piece of code shows how you can use the SELECT statement to retrieve selective columns from a database table.
SELECT product_no, product_name
FROM product
WHERE product_type = 'Laptop';
Here's another example of a selective join that retrieves only the columns you need and uses a selective join to filter the returned data:
SELECT o.order_no, c.customer_name, o.total_amount
FROM sales_order o
JOIN customer c
ON c.customer_no = o.customer_no
WHERE o.order_date >= DATE('2026-01-01');
- Refresh your Statistics
The optimizer is only as good as the accuracy of the data it uses. According to IBM, a great way to increase the performance of SQL queries is to collect appropriate background statistics on Db2 for i. This includes how to use the index and the information on key cardinality and selectivity that the Db2 optimizer needs to determine the true cost of performing a scan versus using an index.
It should be here that if the statistics are stale, the optimizer may select the wrong join order. This happens often, particularly when a large data load occurs, when multiple updates occur within a batch window, or when a large number of records are inserted into a Db2 table, which can change the distribution of the data.
- Optimize I/O Operations
An Input-Output operation is a process that reads from or writes to storage. I/O operations are much more important in Db2 databases due to the volume and frequency of data access. To improve the efficiency of I/O operations, Db2 databases leverage the following techniques:
Data Placement
When data access is quick, it takes less time to retrieve the data. Essentially, when the data to be searched is readily available, your Db2 database can fetch it quickly. This is analogous to storing the frequently accessed or searched items in the front of a store.
Data Clustering
You should group related data to minimize the number of I/O operations required to retrieve them. For example, if a library is organized so that all books about a particular subject are kept together, it will take fewer I/O operations to retrieve the entire collection of books for that subject.
Use Caching
Caching is a proven strategy for storing frequently accessed data in memory to speed up retrieval. You should store frequently accessed data in the memory so you can access it via memory without returning to the primary storage location each time.
Upgrade Hardware
Another solution to the degraded I/O performance may be to upgrade the storage architecture to modern technology. The most notable storage device to upgrade to is a Solid-State Drive. They provide superior read and write access times compared to the last generation of storage devices.
- Use locks efficiently
Locking is a proven technique that protects your data from concurrent access conflicts by preventing multiple users from updating it simultaneously, thereby preventing deadlocks and allowing your database to run at optimal performance. Typically, when two users attempt to buy an item that the system has only one of, locking will allow one transaction to proceed and the other to fail, thus ensuring the integrity of the database.
While locking is an essential feature, it must be managed properly to avoid performance penalties. Too many locks, or locks that run for longer, can slow transaction processing.
The key to using locks effectively is to balance the need for locks and data protection with the processing resources available to you. You can monitor locks to determine whether they are causing performance problems and enhance performance by reducing lock duration and/or optimizing the lock level.
- Leverage Parallel Processing
Modern computer systems often come with multiple processors. Instead of executing tasks on a single processor, you can run them on multiple processors to enhance Db2 query performance, enabling businesses to access data faster. Parallel processing is a technique that leverages multiple processors in a computer system and splits workloads across them for optimal performance. You can also take advantage of parallel processing to enhance performance.
- Manage Buffer Pools Efficiently
In a database system, a buffer pool is used for temporarily storing items that have been previously accessed; hence, when you access the same item again, you can retrieve it from the Buffer Pool instead of going back to the main store to retrieve the item, which will improve performance.
The key to efficiently managing buffer pools is deciding which data to place in them. It is a balancing act. The more correct data you store in your buffer pools, the smoother your systems will run. The more incorrect data you place in your buffer pools, the slower your systems will run.
You can manage your buffer pools effectively through regular monitoring—by checking how often each piece of data is accessed within the buffer pool, you can make better-informed decisions about a data piece’s continued existence in that buffer pool.
You should also adjust the buffer pool sizes as your database grows or as usage patterns change. If the buffer pool is not sized properly, it will be inefficient; if it is oversized, you will waste resources.
- Use Batch Commits
The rate at which you commit your data is the most critical component of your batch tuning exercise. Committing data too frequently creates a massive performance overhead. On the other hand, performing too little results in a complex restart process and longer times before a lock is completed.
A recommended approach is to commit your data once per unit of work (i.e., after a batch of records) rather than after each record. For example, committing each n transaction (where n is determined by the level of lock contention, requirements for journaling, and requirements for restorations of damaged data) is usually far better than committing after each record.
The following code listing demonstrates how you can batch commit to a Product table in Db2:
/exec sql
/cdosql COMMIT(*WAL)
/endsql
DCL-S batchSize INT(10) VALUE(1000);
DCL-S count INT(10) VALUE(0);
FOR product IN productList DO
exec sql
INSERT INTO Product (ID, NAME) VALUES (:product.Id, :product.Name)
endsql
count += 1;
IF count % batchSize = 0 THEN
exec sql COMMIT();
exec sql BEGIN WORK();
ENDIF;
ENDFOR;
exec sql COMMIT();
- Use Automation to Identify Performance Issues
You should use automation to determine performance trends and bottlenecks. For example, you can use automation to identify patterns and alert when performance issues with CPU, memory, and disk I/O are detected, which can slow down the system, degrade response times, and increase resource consumption. Moreover, these slowdowns can be costly for the business and significantly impact operations.
Key Takeaways
- You should update statistics regularly to enable optimal data access, enabling accurate estimation of query costs.
- When a function is needed in the WHERE clause, or a JOIN is applied to a column with an index, the optimizer cannot use the index.
- The use of SELECT * or narrow projections should be avoided.
- You should use narrow projections to utilize index-only access and reduce the amount of I/O.
- To improve batch job efficiency, minimize the frequency of opening and closing a batch job by determining a reasonable commit frequency.
- Always evaluate all factors associated with the slow execution of the batch job from a data-access aspect before looking at CPU issues.
- Each change to an existing workload should be tested thoroughly to determine the best solution. It should be noted that the "best" solution depends on the query pattern and data distribution.
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online