How to add a value to a new column in a table using RC/Migrator
Article ID: 225594
Updated On:
Products
Issue/Introduction
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.
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 => 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
CMD ### PS COLUMN NAME COLUMN TYPE SIZE N D FORDAT PK UK FK
___ 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
COMMAND ===>
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
|---+----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:
,cccccccc 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
Feedback
