Alter/Create a table with a Column Name > 18 Characters using RC/Update
Article ID: 25353
Updated On:
Products
Issue/Introduction
How to Alter or Create a Table with a Column Name > 18 Characters in RC/Update for Db2 for z/OS (RCU).
Resolution
There are two methods available for defining a column name greater than 18 characters in a table using RC/Update:
Method 1. Having inserted a new column to the table from the CA - Table Alter/Create screen, type EXPAND on the command line place the cursor on the column name field and press ENTER.
ROPTBAL ---------------- CA - Table Alter ---------------- yyyy/mm/dd hh:mm:ss
COMMAND ===> expand SCROLL ===> CSR
Option => A Object => T Mode => O ONLINE
Item Name => TBEMP > Creator => authid1 > Where => N
SSID: ssid ------------------------------------------------------ authid1 >
Table => TBEMP > Creator => authid1 > Comm/Lab => N
Database => DBCORP Editproc => Data Cap => NONE
Tablespace => TSEMP Validproc => OBID =>
Partitioning > NO (TS Parts: 4) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 175/-3,873 Forgn Key => N Chk Const > N
Append => N
CMD ### PS COLUMN NAME COLUMN TYPE SIZE N D FORDAT PK UK FK
___ 1 EMPNO CHAR 6 N _ SBCS 1
___ 2 FIRST_NAME CHAR 50 N _ SBCS __
___ 3 MIDDLE_NAME CHAR 50 N _ SBCS __
___ 4 LAST_NAME CHAR 50 N _ SBCS __
___ 5 WORK_GROUP CHAR 6 Y N SBCS __
___ 6 PHONE_NO CHAR 10 Y N SBCS __
___ 7 SEX CHAR 1 N _ SBCS __
___ 8 ↓_________________ __________________ __________ Y N _____ __ <<<Cursor at start of COLUMN NAME field
******************************* BOTTOM OF DATA ********************************
The Expanded Edit/View screen will display and allow a column name to be entered up to 30 characters.
--------------------------- Expanded Edit/View ---------------------------
Command ===>
NAME:
Column_greater_than_18_charsss
Press PF3 or enter to return to the CA - Table Alter screen.
Result:
ROPTBAL ---------------- CA - Table Alter ---------------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Option => A Object => T Mode => O ONLINE
Item Name => TBEMP > Creator => authid1 > Where => N
SSID: ssid ------------------------------------------------------ authid1 >
Table => TBEMP > Creator => authid1 > Comm/Lab => N
Database => DBCORP Editproc => Data Cap => NONE
Tablespace => TSEMP Validproc => OBID =>
Partitioning > NO (TS Parts: 4) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 175/-3,873 Forgn Key => N Chk Const > N
Append => N
CMD ### PS COLUMN NAME COLUMN TYPE SIZE N D FORDAT PK UK FK
___ 1 EMPNO CHAR 6 N _ SBCS 1
___ 2 FIRST_NAME CHAR 50 N _ SBCS __
___ 3 MIDDLE_NAME CHAR 50 N _ SBCS __
___ 4 LAST_NAME CHAR 50 N _ SBCS __
___ 5 WORK_GROUP CHAR 6 Y N SBCS __
___ 6 PHONE_NO CHAR 10 Y N SBCS __
___ 7 SEX CHAR 1 N _ SBCS __
___ 8 Column_greater_th> __________________ __________ Y N _____ __
******************************* BOTTOM OF DATA ********************************
The column name does not fit but it has a ">" sign to indicate that it is a scrollable field using PF10 /PF11 and the cursor is positioned inside the field.
Method 2. When adding a column to the table from the CA - Table Alter/Create screen, begin typing the column name and then press PF11.
The cursor's position will be moved to the left edge of the column name field, along with the character that was positioned under the cursor. This will allow additional characters to be typed in order to complete the name, to a maximum of 30 characters.
Press PF10 to return to the beginning of the column name.
On the example above to show how this works the cursor is placed in the new column name at the end of the word 'greater' and PF11 is pressed.
The screen looks like this...........note in the heading below it tells you that the cursor is at position "14 thru 30 of 30" in the column name field.
ROPTBAL ---------------- CA - Table Alter ------------------- 14 thru 30 of 30
COMMAND ===> SCROLL ===> CSR
Option => A Object => T Mode => O ONLINE
Item Name => TBEMP > Creator => authid1 > Where => N
SSID: ssid ------------------------------------------------------ authid1 >
Table => TBEMP > Creator => authid1 > Comm/Lab => N
Database => DBCORP Editproc => Data Cap => NONE
Tablespace => TSEMP Validproc => OBID =>
Partitioning > NO (TS Parts: 4) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 175/-3,873 Forgn Key => N Chk Const > N
Append => N
CMD ### PS COLUMN NAME COLUMN TYPE SIZE N D FORDAT PK UK FK
___ 1 < CHAR 6 N _ SBCS 1
___ 2 < CHAR 50 N _ SBCS __
___ 3 < CHAR 50 N _ SBCS __
___ 4 < CHAR 50 N _ SBCS __
___ 5 < CHAR 6 Y N SBCS __
___ 6 < CHAR 10 Y N SBCS __
___ 7 < CHAR 1 N _ SBCS __
___ 8 <r_than_18_charsss __________________ __________ Y N _____ __
In the new column name the first character is now 'r'. To go back left along the field press PF10 with the cursor still in the column field.
When all the attributes of the new column have been added....
ROPTBAL ---------------- CA - Table Alter ---------------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Option => A Object => T Mode => O ONLINE
Item Name => TBEMP > Creator => authid1 > Where => N
SSID: ssid ------------------------------------------------------ authid1 >
Table => TBEMP > Creator => authid1 > Comm/Lab => N
Database => DBCORP Editproc => Data Cap => NONE
Tablespace => TSEMP Validproc => OBID =>
Partitioning > NO (TS Parts: 4) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 206/-3,842 Forgn Key => N Chk Const > N
Append => N
CMD ### PS COLUMN NAME COLUMN TYPE SIZE N D FORDAT PK UK FK
___ 1 EMPNO CHAR 6 N _ SBCS 1
___ 2 FIRST_NAME CHAR 50 N _ SBCS __
___ 3 MIDDLE_NAME CHAR 50 N _ SBCS __
___ 4 LAST_NAME CHAR 50 N _ SBCS __
___ 5 WORK_GROUP CHAR 6 Y N SBCS __
___ 6 PHONE_NO CHAR 10 Y N SBCS __
___ 7 SEX CHAR 1 N _ SBCS __
E__ 8 COLUMN_GREATER_TH> CHAR 30 Y N _____ __
the "E" (Explode) line command can be used to display the column attributes...
Take note of the "NAME -> COLUMN_GREATER_THA > " field.
ROPTBCX1 ------------- Column Explode Detail ------------- yyyy/mm/dd hh:mm:ss
COMMAND ===>
NEW Column Detail Specifications for Table => TBEMP >
NAME -> COLUMN_GREATER_THA > PRIM KEY SEQ ->
SEQUENCE -> 8 FOREIGN KEY ->
TYPE -> CHAR > SOURCE TYPE ->
MASK -> TYPE SCHEMA -> SYSIBM >
LENGTH -> 30 SCALE -> 0 FOR DATA ->
NULL -> Y DEFAULT IND -> N HIDDEN -> N
DEFAULT VAL -> >
LABEL ->
COMMENT ->
->
->
FLDPROC ->
PARMS ->
->
->
Use PF7/PF8 on COMMAND line to scroll COMMENT and PARMS fields UP/DOWN
Enter VALUES on COMMAND line to update DEFAULT or EXPRESSION value
As on the main alteration screen , the EXPAND command is available on this screen also along with PF10/11 to handle the long column name as previously shown.
The DDL when generated to do the ALTER looks like this:
ALTER TABLE authid1.TBEMP
ADD COLUMN_GREATER_THAN_18_CHARSSS CHAR ( 30 )
;
Feedback
