FairCom RTG COBOL User Guide

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"?>

</key>

</schema>

</table>

A new “country” field is desired and added to the existing custmast definition.

custmast2.xdd

<?xml version="1.0" encoding="US-ASCII"?>

</key>

</schema>

</table>

A “defaultvalue” attribute is required for all altered fields, as default field content must be provided:

ctutil -alter customer custmast2.xdd -hot

A 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"?>

</key>

</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 47

Obtain 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)