How to add a value to a new column in a table using RC/Migrator
search cancel

How to add a value to a new column in a table using RC/Migrator


Article ID: 225594


Updated On:


RC/Migrator for DB2 for z/OS


In an RC/Migrator for Db2 for z/OS (RCM) alteration strategy, how is a constant value assigned to a particular column in a table.


The 'V' line command (or VALUE primary command on the Column Explode Detail screen) can be used on a column that is newly inserted,
or a pre-existing column that is defined with a DEFAULT type of C, A or D (WITH DEFAULT constant, GENERATED ALWAYS or GENERATED BY DEFAULT).

Depending on the column's data type and Default type setting, the VALUE command for pre-existing or new columns is able to be used in the following ways:

 1) Casting a default constant for a column defined with the "constant" Default type 'C', thus causing the "DEFAULT WITH constant" clause to be generated for the column.

 2) Specifying the following attributes for an identity column: START, INCREMENT, CACHE, MINVALUE, MAXVALUE and CYCLE attributes.
An identity column is a SMALLINT, INTEGER, DECIMAL or BIGINT column whose Default type option is set to 'A' (GENERATED ALWAYS) or 'D' (GENERATED BY DEFAULT).

 3) Specifying the non-deterministic expression for a CHAR or VARCHAR column that is defined with the GENERATED ALWAYS Default type 'A'.

When in the Table Alter screen working on a table, in the example below, the NUMLINED column is defined as a type "C" in the "D" column.

C     - User-defined CONSTANT. The WITH DEFAULT constant clause is generated. 

ROPTBALX ---------------- CA - Table Alter --------------- yyyy/mm/dd hh:mm:ss
COMMAND ===>                                                  SCROLL ===> CSR

Table       => table             > Creator   => authid1   > Comm/Lab => N
Database    => dbname                Editproc  =>          Data Cap => NONE
Tablespace  => tsname               Validproc =>          OBID     =>
Partitioning > YES (TS Parts: 12)    Audit     => NONE     Volatile => N
Table Type  => REGULAR               Restrict  => N        CCSID    => EBCDIC
Row Size    => 174/-3,874            Forgn Key => N        Chk Const > N
                                                           Append   => N

___ 1   1  aaaaaaaa           CHAR               40         N Y SBCS   __
___ 2   2  bbbbbbbb           CHAR               2          N Y SBCS   __
V__ 3   3  cccccccc           CHAR               2          N C SBCS   __
___ 4      dddddddd           CHAR               80         N Y SBCS   __
___ 5      eeeeeeee           CHAR               30         N Y SBCS   __
___ 6   4  ffffffff           TIMESTMP           6          N Y _____  __
___ 7      gggggggg          TIMESTMP           6          N D _____  __

The "V" line command, for this type and size of column displays this screen:

ROPALTH ------- Column Default Value Specifications ------ yyyy/mm/dd hh:mm:ss

Table Name => table                                                         >

----------------------------- Column Information -----------------------------
Name                  Type              Schema      Length         Scale  N D
cccccccc            > CHAR            > SYSIBM    > 2              n/a    N C

Null Indicator: N=Not Null, Y=Nullable
Default Type  : U=User, S=Sqlid, N=Null, C=Constant, Y=System, Blank=None
Source Type   : CHAR

VALUE  -> 20
Enter "/" to select option:
_ HEX (surrounding X'' delimiters added automatically, if not supplied)

The value of "20" has been entered into the VALUE field above.

When analyzed the column in the CREATE TABLE statement is generated like this:

     ,cccccccc CHARACTER(2) FOR SBCS DATA
                                                       NOT NULL

Error message RO725E is generated if the above requirements are not met when this command is used.

RO725 RO725E The V command conflicts with the column or its attribute settings.

Additional Information

Column Explode Detail Commands