Quickly change a table schema on-the-fly
FairCom RTG 5 introduced an extended resource format allowing nearly all FairCom Database features to be applied to RTG tables, including hot alter table. Now you can alter an existing RTG table definition by adding columns for new application usage. This feature makes rolling out new application updates much easier, saving time and resources. It is similar to SQL ALTER TABLE.
How to use it
Given a new XDD schema definition with only new fields, perform a ctutil -alter to update the table. The table must already have a schema resource applied from a previous sqlize or other method resulting in a schema resource.
ctutil -alter table <newtable.xdd>
ctutil -sqlize table <newtable.xdd> (required to obtain latest definitions in SQL if already sqlized)
Options:
The “defaultvalue” attribute is required as you must define a default value for each new field. This will be applied to the sqlized table as a SQL DEFAULT constraint. It will also be applied to COBOL writes using SQL as well as SQL inserts that don’t specify a value for the field. “defaultvalue” replaces “cbdefault” and “bindefault”.
Existing runtimes
Existing runtimes can continue to work against altered (expanded) tables without recompiling them with a new COBOL FD to match:
<recordcheck> no - This allows opening an existing table with a larger record size than the current FD expects.
<maxlencheck> no - This allows an existing larger record read from disk to populate a smaller buffer used when reading. The data is truncated to fit. Otherwise an error is returned.
Limitations
Hot alter table support requires an RTG resource version of RTGIv2. You can determine the current version with ctutil -info:
ctutil -info custmast
If your file shows up as RTGIv1 (or other), then bring your table up to the RTGIv2 version.
Only new fields are allowed at the end of records. This maintains backward RTG compatibility for existing FDs to be used against the new table.
Existing “field” definitions cannot be changed.
A default value must be provided for each new field.