IBM i 7.6 introduces several useful SQL enhancements that help developers write cleaner code, improve performance analysis, and better understand query execution.
In this article, we’ll look at two important features:
- Data Change Table References (OLD TABLE, NEW TABLE, and FINAL TABLE)
- 64-bit QRO Hash Enhancement
These enhancements improve both application development and database monitoring.
Data Change Table References in IBM i 7.6
Some of the most valuable additions in IBM i 7.6 is support for the Data Change Table References OLD TABLE, NEW TABLE, and FINAL TABLE. These features allow developers to retrieve rows affected by INSERT, UPDATE, DELETE, and MERGE operations directly from the SQL statement itself.
Why is this important?
In earlier releases, developers often had to run an additional SELECT statement after an UPDATE or DELETE operation to retrieve the affected rows. This approach requires extra database calls, increases application complexity, and can affect performance in high-volume applications.
With IBM i 7.6, you can retrieve the affected rows directly as part of the SQL operation.
OLD TABLE
The OLD TABLE reference returns the values of rows before the change takes place.
Example: UPDATE + OLD TABLE
The result contains the original values that existed before the UPDATE statement was executed.
Common use cases: auditing, change tracking, before-and-after comparisons, and compliance reporting.
NEW TABLE
The NEW TABLE reference returns the values of rows immediately after the SQL statement completes.
Example: UPDATE + NEW TABLE
The returned rows contain the updated values created by the SQL statement.
Common use cases: returning updated records to applications, processing modified data, API responses, and user feedback screens.
FINAL TABLE
The FINAL TABLE reference returns rows after all database processing is complete, including any trigger actions.
Why FINAL TABLE is useful:
Imagine a trigger automatically updates a timestamp whenever an order status changes.
When using FINAL TABLE, the result includes:
- Values updated by the SQL statement
- Values modified by triggers
- The exact data stored in the database
This provides the true final state of the row.
Example: Trigger Logic
Example: UPDATE + FINAL TABLE
Result
Note that the timestamp generated by the trigger is included.
Comparison:
| Feature | Returns |
| OLD TABLE | Values before the change |
| NEW TABLE | Values immediately after the SQL statement |
| FINAL TABLE | Values after SQL processing and trigger execution |
Benefits of Data Change Table References
1. Fewer Database Calls: Retrieve affected rows without running extra SELECT statements.
2. Simpler Application Logic: Reduce additional code used to fetch changed records.
3. Better Auditing: Capture before-and-after data directly from SQL.
4. Trigger Awareness: FINAL TABLE shows the exact values stored after trigger processing.
5. Improved Performance: Fewer database operations can lead to faster execution and reduced workload.
QRO Hash Enhancement in IBM i 7.6
Another important enhancement in IBM i 7.6 is the expansion of the Query Repository Object (QRO) Hash from 32 bits to 64 bits.
What is a QRO Hash?
Every SQL statement processed by Db2 for i receives a unique identifier called a QRO Hash. This identifier is used to:
- Identify SQL statements
- Track execution history
- Analyze performance
- Compare queries across jobs
- Support SQL monitoring and Query Supervisor tools
Previous Limitations of QRO Hash:
Before IBM i 7.6, QRO Hash values were generated using a 32-bit hash. Although effective for many environments, modern applications can execute millions of SQL statements every day. As SQL workloads grow, the possibility of hash collisions also increases.
What Is a Hash Collision?
A hash collision occurs when two different SQL statements generate the same hash value. When this happens, identifying and tracking queries becomes more difficult, especially in large environments.
What’s New in IBM i 7.6?
IBM i 7.6 expands the QRO Hash size from: 32-bit → 64-bit
This significantly increases the number of available unique hash values.
Capacity Comparison
| Version | Hash Size |
| Previous Releases | 32-bit |
| IBM i 7.6 | 64-bit |
Because of the larger hash space, collisions become extremely unlikely.
Possible hash combinations increase from approximately:
To:
Verifying the QRO Hash
First, run a simple SQL statement:
Then retrieve the QRO Hash:
Expected Output in IBM i 7.6
The larger value reflects the new 64-bit hash implementation.
Benefits of the 64-bit QRO Hash
1. Reduced Hash Collisions: More unique identifiers mean fewer duplicates.
2. Better Query Tracking: SQL statements can be identified more accurately.
3. Improved Performance Analysis: Monitoring tools can distinguish between queries more reliably.
4. Enhanced Diagnostics: Makes troubleshooting easier in large enterprise environments.
5. Better Scalability: Designed to support modern applications with very large SQL workloads.
6. More Accurate Monitoring: Improves correlation between active queries, plan cache entries, and performance data.
Conclusion
IBM i 7.6 continues to enhance Db2 for i with practical features that benefit both developers and database administrators. The introduction of OLD TABLE, NEW TABLE, and FINAL TABLE makes it easier to retrieve changed data directly from SQL operations, reducing unnecessary queries and simplifying application logic.
At the same time, the upgrade from a 32-bit to 64-bit QRO Hash improves SQL identification, monitoring, and performance analysis for high-volume environments. Together, these enhancements deliver cleaner code, improved auditing, more accurate diagnostics, and better scalability for modern IBM i applications.
