Add a new Unique Constraint on a table using RC/Migrator alteration strategy
Article ID: 257633
Updated On:
Products
RC/Migrator for DB2 for z/OS
Issue/Introduction
What is the procedure to add a new unique constraint on a table using a RC/Migrator for Db2 for z/OS (RCM) alteration strategy.
Resolution
Database Sample Structure:
RQOD ----------- RC/Q Object Dependency ------------ yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
RU094 RU094I SQL has been processed successfully.
DB2 Object ===> DB Option ===> O Where => N
Data Base ===> dbname > Creator ===> authid1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------authid1 - LINE 1 OF 4
CMD DATABASE TABLESPACE TABLENAME INDEXNAME
________ dbname
________ tsname
________ TABLE01
________ INDEX01
******************************* BOTTOM OF DATA ********************************
Start an Alteration Strategy and Alter the table:
ROPTBAL ------------------- Table Alter ------------------ yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Option => A Object => T Mode => O ONLINE
Item Name => TABLE01 > Creator => authid1 > Where => N
SSID: DT32 ------------------------------------------------------ authid1 >
Table => TABLE01 > Creator => authid1 > Comm/Lab => N
Database => dbname Editproc => Data Cap => NONE
Tablespace => tsname Validproc => OBID =>
Partitioning > YES (TS Parts: 7) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 1,158/-31,548 Forgn Key => N Chk Const > N
Append => N
CMD ### PS COLUMN NAME COLUMN TYPE SIZE N D FORDAT PK UK FK
___ 1 1 EMP_NO CHAR 9 N _ MIXED 1
___ 2 EMP_AGE SMALLINT 2 N _ _____ __
___ 3 EMP_INT1 INTEGER 4 N _ _____ __
___ 4 EMP_INT2 INTEGER 4 N _ _____ __
___ 5 EMP_INT3 INTEGER 4 N _ _____ __
___ 6 EMP_INT4 INTEGER 4 N _ _____ __
___ 7 EMP_INT5 INTEGER 4 N _ _____ __
___ 8 EMP_INT6 INTEGER 4 N _ _____ __
___ 9 EMP_BIGINT BIGINT 8 N _ _____ __
___ 10 EMP_QUITDATE V9MIX_UDTTSTAMP 6 Y N _____ __
___ 11 EMP_LSTRING VARCHAR 1000 N _ MIXED __
___ 12 SQL_DESC VARCHAR 100 N _ MIXED __
******************************* BOTTOM OF DATA ********************************
Enter the UNIQUECONST command in the COMMAND field above.
COMMAND : UNIQUECONST
DESCRIPTION : The UNIQUECONST command displays the Unique Constraints Manage-
ment screen, from where you can insert, modify and delete primary
key and unique key constraints for your table.
SYNTAX : UNIQUECONST
ABBREVIATION: UC
ROPUCM1 --------- Unique Constraints Management --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Table => TABLE01 >
Edit Mode => CONSTRAINT
SSID: ssid ------------------------------------------------------ authid1
CMD CONSTRAINT T C.01
___ EMP_NO P EMP_NO
E__ EMP_AGE___________ U CONSTRAINT CONTAINS NO COLS; ENTER 'E' CMD TO EDIT. <---------Add in the new constraint name.
******************************* BOTTOM OF DATA ********************************
Then use the "E" line command to edit it.
ROPUCM2 --------- Unique Constraints Management --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Table => TABLE01 > Constraint => EMP_AGE >
Edit Mode => COLUMN Type => U
SSID: ssid ---------------------------------------------------------- authid1
CMD Seq Column
I__ <- CONSTRAINT CONTAINS NO COLUMNS; USE 'I' COMMAND TO ADD A COLUMN ->
******************************* BOTTOM OF DATA ********************************
Select a column using the "I" line command.
ROPUCM2 --------- Unique Constraints Management --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Table => TABLE01 > Constraint => EMP_AGE >
Edit Mode => COLUMN Type => U
SSID: ssid ---------------------------------------------------------- BASLU02
CMD Seq Column
___ 1 __________________
******************************* BOTTOM OF DATA ********************************
.
.
.
ROPUCM2 --------- Unique Constraints Management --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Table => TABLE01 > Constraint => EMP_AGE >
Edit Mode => COLUMN Type => U
SSID: ssid ---------------------------------------------------------- authid1
CMD Seq Column
___ 1 EMP_AGE___________
******************************* BOTTOM OF DATA ********************************
PF3 back.
ROPUCM1 --------- Unique Constraints Management --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Table => TABLE01 >
Edit Mode => CONSTRAINT
SSID: ssid ------------------------------------------------------ authid1
CMD CONSTRAINT T C.01
___ EMP_NO P EMP_NO
___ EMP_AGE U EMP_AGE
******************************* BOTTOM OF DATA ********************************
PF3 back to the Alteration Strategy Services screen.
Create a unique index on this new unique constraint column using the "C" option
on this screen.
-------------- RC/M Alteration Strategy Services -------------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Strategy ===> UNIQUECO Description ===>
Creator ===> authid1 Share Option ===> U (U,Y,N,X,L) SSID ===> ssid
--------------------------------------------------------------------- authid1
Option => c (A,C,T,D) Object => I (SG,DB,TS,T,I,V,S,A,TG,SQ,PR,UF)
Name => INDEX02 > Creator => authid1 > Where ==> N
CMD OBJECT TYPE NAME CREATOR PROCESS
_ TABLE TABLE01 > authid1 ALTER
******************************* BOTTOM OF DATA ********************************
Enter the target table name, then enter, then select the column EMP_AGE with an "S".
Then when back on the create index screen set the Unique Rule => YES
ROPIALTX ------------------ Index Alter ------------------ yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Index Name => INDEX02 > Creator => authid1 > Comment => N
Table Name => TABLE01 > Creator => authid1 > Unique Rule => YES
Buffer Pool => BP0 Cluster => NO Close Dataset => YES
Partitioned => NO GBP Cache => CHANGED Defer Build => NO
Piecesize => Padded => DEFAULT Index Type => 2
Copy => Compress => NO Define => YES
BTWOO => NO
CMD SEQ# PS KEY-COLUMN-NAME ORDER COLTYPE SRCTYPE SIZE N
___ 1 EMP_AGE ASC SMALLINT 2 N
******************************* BOTTOM OF DATA ********************************
Then Save the Strategy and do an Analysis.
The result is a new index created......
-- **************************************************************
-- * *
-- * NEW INDEX CREATE AND ALTER STATEMENTS *
-- * *
-- **************************************************************
-- authid1.EMP_AGEI WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.
.AUTH authid1
SET CURRENT SQLID = 'authid1';
CREATE UNIQUE INDEX authid1.INDEX02 ON
authid1.TABLE01
( EMP_AGE ASC
)
USING STOGROUP stogroup
ERASE NO
FREEPAGE 0
PCTFREE 10
BUFFERPOOL BP0
CLOSE YES
;
.SYNC 10 'CREATE INDEX authid1.EMP_AGEI'
and the table is altered accordingly....
-- **************************************************************
-- * *
-- * ALTER TABLE FOR ADD PRIMARY KEY *
-- * ADD UNIQUE *
-- * *
-- **************************************************************
.AUTH authid1
ALTER TABLE authid1."TABLE01"
ADD CONSTRAINT EMP_AGE UNIQUE
(EMP_AGE
);
.SYNC 15 'ALTER TABLE'
Following with utilities after this such as an Image Copy of the tablespace and Runstats on the new Index.
After the analysis is executed by Db2 the table now looks like this:
CREATE TABLE authid1.TABLE01
(EMP_NO CHARACTER(9) FOR MIXED DATA
NOT NULL
,EMP_AGE SMALLINT
NOT NULL
,EMP_INT1 INTEGER
NOT NULL
,EMP_INT2 INTEGER
NOT NULL
,EMP_INT3 INTEGER
NOT NULL
,EMP_INT4 INTEGER
NOT NULL
,EMP_INT5 INTEGER
NOT NULL
,EMP_INT6 INTEGER
NOT NULL
,EMP_BIGINT BIGINT
NOT NULL
,EMP_QUITDATE authid1.V9MIX_UDTTSTAMP
WITH DEFAULT authid1.V9MIX_UDTTSTAMP ( NULL )
,EMP_LSTRING VARCHAR(1000) FOR MIXED DATA
NOT NULL
,SQL_DESC VARCHAR(100) FOR MIXED DATA
NOT NULL
,CONSTRAINT EMP_AGE UNIQUE
(EMP_AGE
)
,CONSTRAINT EMP_NO PRIMARY KEY
(EMP_NO
)
)
IN dbname.tsname
APPEND NO
NOT VOLATILE CARDINALITY
DATA CAPTURE NONE
AUDIT NONE
CCSID EBCDIC
PARTITION BY RANGE
(EMP_NO NULLS LAST ASC
)
( PARTITION 1
ENDING ('AAAAAAAAA'
) INCLUSIVE
, PARTITION 2
ENDING ('BBBBBBBBB'
) INCLUSIVE
, PARTITION 3
ENDING ('CCCCCCCCC'
) INCLUSIVE
, PARTITION 4
ENDING ('DDDDDDDDD'
) INCLUSIVE
, PARTITION 5
ENDING ('EEEEEEEEE'
) INCLUSIVE
, PARTITION 6
ENDING ('FFFFFFFFF'
) INCLUSIVE
, PARTITION 7
ENDING ('GGGGGGGGG'
) INCLUSIVE
);
Additional Information
Feedback
Yes
No
Powered by
