Improving the Field Definitions – Moving Beyond AS/400 Data Types
After the straight conversion is stable, the real long-term value comes from improving the actual column definitions. The biggest and most common improvement is replacing old numeric date fields with real DATE or TIMESTAMP columns.
Why numeric dates were common on the AS/400
Many systems stored dates as 6S0, 7S0 or 8S0 (or packed equivalents) in YYMMDD, CYYMMDD or YYYYMMDD format. This was storage-efficient and easy to compare with simple numeric operations, but it pushed all validation, century handling and date arithmetic into every program.
Modern approach
Use proper DATE or TIMESTAMP columns. The database then owns the integrity and the arithmetic becomes natural.
Example of upgrading the credit date from the earlier customer table:
ALTER TABLE CUSMAST
ADD COLUMN CUS_CREDIT_DATE DATE;
-- Convert existing values (example assumes YYYYMMDD in CUSDT)
UPDATE CUSMAST
SET CUS_CREDIT_DATE = DATE(
SUBSTR(DIGITS(CUSDT), 1, 4) || '-' ||
SUBSTR(DIGITS(CUSDT), 5, 2) || '-' ||
SUBSTR(DIGITS(CUSDT), 7, 2)
)
WHERE CUSDT > 0
AND CUSDT BETWEEN 19000101 AND 20991231;
-- Verify the conversion thoroughly
-- Then drop the old column and rename
ALTER TABLE CUSMAST DROP COLUMN CUSDT;
ALTER TABLE CUSMAST RENAME COLUMN CUS_CREDIT_DATE TO CUSDT;
You can also rebuild the table completely with a new definition and an INSERT…SELECT that performs the conversion in one pass. This is often cleaner for larger changes.
Benefits you actually feel
- Invalid dates can no longer be written.
- Date arithmetic is simple and reliable: CUSDT + 30 DAYS, CURRENT_DATE - CUSDT DAYS, etc.
- Range queries and indexes on real DATE columns perform better.
- Both native RPG and SQL understand the values without constant conversion code.
- TIMESTAMP columns give you precise audit trails when you need date + time.
Other worthwhile type improvements
- Keep money and quantity fields as DECIMAL (still the most efficient for business numbers).
- Change binary fields to INTEGER or BIGINT.
- Turn fixed-length character fields that are truly variable into VARCHAR.
- Add identity columns for new surrogate keys.
- Add a row-change timestamp column (ROW CHANGE TIMESTAMP) for automatic last-changed tracking.
Impact on existing programs
When you change lengths or data types you will normally need to recompile the programs that reference those fields. Plan these changes in controlled batches. Update the external descriptions, recompile the affected programs, and test. Many shops do the pure same-size conversion first (zero recompiles), then schedule the type improvements later when they have testing capacity.
Recommended overall sequence
- Convert high-value Physical Files to SQL tables with identical field definitions.
- Convert the related Logical Files to Indexes and Views.
- Measure and confirm performance and stability.
- Improve data types (especially dates) on a planned schedule, accepting the recompiles.
- Introduce longer descriptive names, constraints and modern features once the foundation is solid.
This progressive approach lets you move from classic AS/400 files to a modern IBM i SQL database without a big-bang project and without breaking the applications that already work. Each step delivers real benefits while keeping risk under control.