Introduction
Upgrading to IBM i 7.5 brings new capabilities, improved standards compliance, and platform enhancements. While many changes are seamless, there are a few that can affect existing applications, SQL statements, RPG programs, and database objects. Some issues appear during compilation, while others only surface when a program is running, making them harder to identify.
This blog walks through the key changes introduced in IBM i 7.5, explains their impact, and highlights practical ways to identify affected applications before and after the upgrade
Understanding the Types of Changes
Not every change affects applications in the same way. Broadly, these changes fall into three categories:
• Compile-Time Changes
These issues appear when a program is recompiled.
Examples:
- %FIELDS on a subfile UPDATE
- KEY reserved word enforcement
• Runtime Changes
Programs compile successfully but fail or behave differently when executed.
Examples:
- Numeric field compared to blank values
- CTE result changes
- Authority-related failures
• Recommended Maintenance
Programs continue to run, but IBM recommends recompilation or validation.
Examples:
- Embedded SQL programs
- CLI-based applications
Key Changes That Could Impact Your Applications
1. Comparing Numeric Fields to Blank or Empty Strings
Many older applications rely on implicit conversion where blank values are treated as zero. IBM i 7.5 no longer allows this behaviour.
Example: These comparisons can now fail with runtime SQL errors instead of automatically converting blanks to numeric values.
Check For
- Numeric columns compared to ”
- Numeric columns compared to ‘ ‘
- Blank character host variables passed into numeric comparisons
Important Note
This behaviour was also introduced through PTFs on IBM i 7.3 and 7.4. Some environments may already be exposed to this issue before moving to IBM i 7.5. IBM i 7.5 removes the previous workaround available through the QIBM_QUERY_CONVERT_BLANK_TO_0 environment variable.
Recommended Action
- Scan SQL source code
- Review embedded SQL programs
- Execute regression testing on critical applications
2. KEY Reserved Word Enforcement
The word KEY has long been considered a reserved SQL keyword. Earlier releases allowed its use in many situations, but IBM i 7.5 now enforces the restriction more strictly.
Example:
May Fail:
Works because the identifier is quoted.
Check For
- Tables named KEY
- Columns named KEY
- SQL aliases named KEY
- DDS and DDL objects using KEY
Recommended Action
- Quote object names
- Rename affected columns or aliases
- Scan database objects before upgrading
3. %FIELDS No Longer Supported with Subfile UPDATE
RPG developers should carefully review applications that use %FIELDS together with subfile updates. IBM i 7.5 treats this as a compile-time error.
Example:
This statement no longer compiles on IBM i 7.5.
IBM-Supported Options
- Compile using TGTRLS(V7R4M0) or lower
- Use environment variable QIBM_RPG_DISALLOW_SUBFILE_BIF_FIELDS
Check For
- %FIELDS usage in RPG
- Subfile update operations
- Programs scheduled for recompilation
4. Common Table Expressions (CTEs) Can Produce Different Results
IBM i 7.5 changes how Common Table Expressions (CTEs) are processed when referenced multiple times within a query. Instead of repeatedly executing the CTE logic, the system now materializes the result and shares it across references. This aligns with SQL standards.
Why It Matters
The result set may differ from earlier releases if underlying tables are modified while the query is running.
Check For
- Queries using WITH
- Reports referencing the same CTE multiple times
- Applications processing frequently changing data
Recommended Action
- Identify CTE-heavy queries
- Compare outputs before and after upgrade
- Regression-test business reports
5. Embedded SQL and CLI Programs
IBM i maintains excellent program compatibility, so embedded SQL programs typically continue to run after an upgrade. However, IBM recommends recompiling programs to take advantage of updates introduced in the SQL precompiler.
Changes include support for newer SQL capabilities such as:
- BOOLEAN data type enhancements
- TRY_CAST
- SQL CLI include changes
Recommended Action
- Create an inventory of embedded SQL programs
- Identify applications using SQL CLI APIs
- Schedule recompilation as part of upgrade planning
6. Authority Requirement Changes
IBM i 7.5 introduces modified authority requirements for certain SQL operations. Activities that previously succeeded may now generate authorization errors.
Areas to Review
- CREATE OR REPLACE statements
- CREATE VIEW operations
- Deployment and automation jobs
- User profiles running DDL
Recommended Action
Review security settings and validate permissions before moving into production.
7. Trigger-Related Changes
Several trigger-related changes were introduced in IBM i 7.5.
What’s Changed?
- MIRROR YES is no longer supported in CREATE TRIGGER and ALTER TRIGGER statements.
- BEFORE triggers are no longer supported on:
- SQL_ERRORT
- SYSIXADV
Recommended Action
- Inventory existing triggers
- Validate trigger definitions
- Test critical trigger-based processes
8. LISTAGG Behaviour Changes
IBM documents changes related to LISTAGG error handling and overflow processing. Applications relying on existing LISTAGG behaviour should be reviewed carefully.
Recommended Action
- Locate LISTAGG usage
- Execute regression testing
- Validate expected outputs
9. JSON Store Technology Preview Removed
The Db2 for i JSON Document Store technology preview is no longer available in IBM i 7.5. Applications relying on this feature must be updated.
Recommended Alternative
Use standard QSYS2 SQL/JSON functions supported by Db2 for i.
Medium-Risk Changes
These changes may not affect every environment but should still be reviewed.
10. UCS-2 CCSID Changes in RPG
Initialization of UCS-2 fields using:
- INZ
- CTDATA
Now uses the source file CCSID instead of the job CCSID. Applications supporting multilingual data should validate behaviour after upgrade.
Recommended Action
Review RPG programs using:
- UCS-2 fields
- CTDATA
- INZ initialization
11. Changes in QSYS2 Views
IBM modified several QSYS2 catalogues views and system information interfaces. Monitoring and reporting solutions that depend on these views may require adjustments.
Impacted Objects
- USER_INFO
- USER_INFO_BASIC
- SYSTEM_STATUS
- SYSTEM_STATUS table functions
Examples of Changes
- Additional MFA and TOTP information
- ATTENTION_LIGHT updates
- USER_STORAGE behaviour updates
Recommended Action
Validate all monitoring dashboards and custom reports.
12. Query Optimizer and Parallelism Changes
IBM i 7.5 introduces optimizer and SMP parallelism enhancements. While many workloads may benefit, execution plans can change.
Potential Impact
- Faster execution for some workloads
- Slower performance for others
- Different access plans
Recommended Action
- Benchmark critical workloads
- Compare execution plans
- Monitor production performance after upgrade
How to Scan Your Environment
One of the most effective ways to prepare for IBM i 7.5 is to scan your libraries and source code before upgrading. The original assessment recommends searching for:
Database Objects
- Columns named KEY
- Tables named KEY
- Views
- Triggers
Program Objects
- Embedded SQL programs
- SQL CLI programs
Source Code Patterns
- %FIELDS
- =”
- <>”
- USER_INFO references
- SYSTEM_STATUS references
- SYSIXADV references
Running these scans provides a clear list of objects that require review before the migration.
A Real-World Perspective
In one production environment containing approximately 3,400 programs, upgrade scans identified:
- 33 RPGLE members using %FIELDS
- Around 590 embedded SQL programs requiring review
- Zero columns named KEY
The purpose of these scans is not just to find issues—it is to create a measurable upgrade plan and eliminate surprises later in the project.
Conclusion
IBM i 7.5 brings several enhancements, but it also introduces changes that can impact existing applications if not reviewed beforehand. From SQL behaviour updates to RPG and database-related changes, understanding these areas early can help avoid unexpected issues during or after the upgrade.
By reviewing affected objects, running impact assessments, and thoroughly testing critical applications, organizations can ensure a smoother upgrade experience. A little preparation before the move can go a long way in reducing risks and helping teams take full advantage of everything IBM i 7.5 has to offer.
