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 by adding columns without requiring file migration. This makes rolling out new application updates faster, easier, and less resource-intensive. 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:
- -full - forces a full table rewrite of all records to a new file.
- -hot - adds new columns/fields with no forced rewrite of records.
The -hot option can only be used on a file with the ctFLEXREC format applied. In RTG v4.5.0 and later, tables are created by default with this format. Otherwise, use ctutil -upgrade to apply the format.
Using the -full option on a non-ctFLEXREC file results in the file length being extended to the new XDD defined length. It will not convert the file to ctFLEXREC.
The “defaultvalue” attribute is required 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”.
For more information, see the step-by-step examples below.
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 custmastIf the file shows up as RTGIv1 (or other), upgrade your table 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.
Hot Alter Table step-by-step examples
Given a starting customer table definition, you must have already performed a -sqlize or otherwise embedded the XDD definition for the table. -sqlinfo custmast.xdd can also be used if you do not require the table to be available in SQL:
ctutil -make customer custmast.xdd
cutil -sqlize customer custmast.xdd ctreeSQL
custmad.xdd
<?xml version="1.0" encoding="US-ASCII"?>
<table name="CUSTMAST-FILE" minRecLen="4" maxRecLen="1024">
<key duplicate="false">
<segment offset="0" size="4"/>
<part name="CM_CUSTNUMB" offset="0" size="4"/>
</key>
<schema name="CUSTMAST-FILE" size="157">
<field name="CM_CUSTNUMB" size="4" type="Alphanum" digits="4" scale="0"/>
<field name="CM_CUSTZIPC" size="9" type="Alphanum" digits="9" scale="0"/>
<field name="CM_CUSTSTAT" size="2" type="Alphanum" digits="2" scale="0"/>
<field name="CM_CUSTRTNG" size="1" type="Alphanum" digits="1" scale="0"/>
<field name="CM_CUSTNAME" size="47" type="Alphanum" digits="47" scale="0"/>
<field name="CM_CUSTADDR" size="47" type="Alphanum" digits="47" scale="0"/>
<field name="CM_CUSTCITY" size="47" type="Alphanum" digits="47" scale="0"/>
</schema>
</table>
A new “country” field is desired and added to the existing custmast definition.
custmast2.xdd
<?xml version="1.0" encoding="US-ASCII"?>
<table name="CUSTMAST-FILE" minRecLen="4" maxRecLen="1071">
<key duplicate="false">
<segment offset="0" size="4"/>
<part name="CM_CUSTNUMB" offset="0" size="4"/>
</key>
<schema name="CUSTMAST-FILE" size="204">
<field name="CM_CUSTNUMB" size="4" type="Alphanum" digits="4" scale="0"/>
<field name="CM_CUSTZIPC" size="9" type="Alphanum" digits="9" scale="0"/>
<field name="CM_CUSTSTAT" size="2" type="Alphanum" digits="2" scale="0"/>
<field name="CM_CUSTRTNG" size="1" type="Alphanum" digits="1" scale="0"/>
<field name="CM_CUSTNAME" size="47" type="Alphanum" digits="47" scale="0"/>
<field name="CM_CUSTADDR" size="47" type="Alphanum" digits="47" scale="0"/>
<field name="CM_CUSTCITY" size="47" type="Alphanum" digits="47" scale="0"/>
<field name="CM_CUSTCTRY" size="47" type="Alphanum" digits="47" scale="0" defaultvalue="USA"/>
</schema>
</table>A “defaultvalue” attribute is required for all altered fields, as default field content must be provided:
ctutil -alter customer custmast2.xdd -hotA default value is defined with a <field default=""/> attribute. Its value can be specified in multiple formats depending on field content requirements:
- A character string: <field default="0"/>
- COBOL reserved words: LOW-VALUE/LOW-VALUES, HIGH-VALUE/HIGH-VALUES and SPACE/SPACES: <field default="LOW-VALUE"/>
- A hexadecimal value if prepended by ‘\x’: <field default="\xA0B1C3D4"/>
- Base64 value if prepended by ‘\6’: <field default="\6VVNBICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA="/>
To specify a character string that starts with a backslash, prepend it with another backslash: <field default="\\000"/>
The new record lengths are updated and reported after a successful alter:
ctutil -info customer
Version 4.0.3.169-240601 - Micro Focus COBOL and ExtFH Edition
Initialized from 'ctree.conf'
Record length : 4 bytes
Variable : yes
Max length : 1071 bytes
Operation completed successfully.Note: You must re-sqlize the table after an alter for changes to be reflected in the SQL system tables:
ctutil -sqlize customer custmast2.xdd ctreeSQL
Review your changes after updating your table with ctutil:
./ctutil -sqlinfo altertest
Version 4.0.3.169-240601 - Micro Focus COBOL and ExtFH Edition
Initialized from 'ctree.conf'
<?xml version="1.0" encoding="US-ASCII"?>
<table minRecLen="4" maxRecLen="1071">
<key duplicate="false" primary="true">
<segment offset="0" size="4" type="String"/>
</key>
<schema >
<field name="CM_CUSTNUMB" size="4" type="Alphanum" digits="4" scale="0" />
<field name="CM_CUSTZIPC" size="9" type="Alphanum" digits="9" scale="0" />
<field name="CM_CUSTSTAT" size="2" type="Alphanum" digits="2" scale="0" />
<field name="CM_CUSTRTNG" size="1" type="Alphanum" digits="1" scale="0" />
<field name="CM_CUSTNAME" size="47" type="Alphanum" digits="47" scale="0" />
<field name="CM_CUSTADDR" size="47" type="Alphanum" digits="47" scale="0" />
<field name="CM_CUSTCITY" size="47" type="Alphanum" digits="47" scale="0" />
<field name="CM_CUSTCTRY" size="47" type="Alphanum" digits="47" scale="0" bindefault="\6VVNBICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA=" />
</schema>
</table>The default value defined by “defaultvalue” is reported as a base64 encoded value:
ISQL> table altertest;
COLNAME NULL ? TYPE LENGTH CHARSET NAME COLLATION
------- ------ ---- ------ ------------ ---------
cm_custnumb NOT NULL CHAR 4
cm_custzipc NOT NULL CHAR 9
cm_custstat NOT NULL CHAR 2
cm_custrtng NOT NULL CHAR 1
cm_custname NOT NULL CHAR 47
cm_custaddr NOT NULL CHAR 47
cm_custcity NOT NULL CHAR 47
cm_custctry CHAR 47Obtain current schema version
ctutil -info returns current schema versions for eligible files (that is, those having the ctflexrec attribute).
For example, a newly created file with <ctflexrec> configuration option enabled will show:
Hot alter schemas : yes
Altered schemas in use : 0 = (0 - 0)The file after an hot alter schema:
Hot alter schemas : yes
Altered schemas in use : 1 = (1 - 0)The file after compaction with the ctcmpcif utility with the -online option:
Hot alter schemas : yes
Altered schemas in use : 0 = (1 - 1)The file after an additional hot alter schema:
Hot alter schemas : yes
Altered schemas in use : 1 = (2 - 1)