CA RC/Migrator for DB2 for Z/OS : How to add a value to a new column in a table

book

Article ID: 225594

calendar_today

Updated On:

Products

CA RC/Migrator for DB2 for z/OS

Issue/Introduction

In an alteration strategy, how is a constant value assigned to a particular column in a table?

Environment

DB2 for Z/OS

Resolution

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       => TBCORPT             > Creator   => authid1   > Comm/Lab => N
Database    => DBCORP                Editproc  =>          Data Cap => NONE
Tablespace  => TBCORPS               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

CMD ### PS COLUMN NAME        COLUMN TYPE        SIZE       N D FORDAT PK UK FK
___ 1   1  RFGESNAR           CHAR               40         N Y SBCS   __
___ 2   2  CODTXTNA           CHAR               2          N Y SBCS   __
V__ 3   3  NUMLINED           CHAR               2          N C SBCS   __
___ 4      TEXTLIN            CHAR               80         N Y SBCS   __
___ 5      USUMOD30           CHAR               30         N Y SBCS   __
___ 6   4  TIMESMOD           TIMESTMP           6          N Y _____  __
___ 7      TIMESMOD1          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
COMMAND ===>

Table Name => TBCORPT                                                         >

----------------------------- Column Information -----------------------------
Name                  Type              Schema      Length         Scale  N D
NUMLINED            > 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

          |---+----10---+----20---+----30---+----40---+----50---+----60---+-
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:

       ,NUMLINED CHARACTER(2) FOR SBCS DATA
                                                       NOT NULL
 WITH DEFAULT '20'


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