Delete an existing check constraint on a table using RC/Migrator
Article ID: 235111
Updated On:
Products
Issue/Introduction
On the RC/Migrator for Db2 for z/OS (RCM) Table Check Constraints List screen, named sets of values that define constraints for a table can be managed
and modified by entering "U" in the Check Const field of the Table Create, Alter, or Template screen.
Resolution
During an Alteration Strategy, when altering a table, the table alteration screen has the Chk Const field in the header.
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 > NO (TS Parts:NONE) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 202/-3,846 Forgn Key => N Chk Const > Y
Append => N
If there are existing constraints defined on the table the field will have a "Y".
N - Indicates NO, that no check constraints are defined for this table. This is the initial value when creating a new table, or when altering or templating a table that previously had no check constraints.
Y - Indicates YES, that one or more check constraints are defined for the table. This is the initial value when altering or templating a table that already has one or more check constraints defined. When N is displayed in this field, you may overtype the N with Y if you want to add initial check constraints to the table. When you do this, the "Table Check Constraints List" panel is displayed, from where you can create new check constraints for the table.
U - Indicates UPDATE, that you want to view, add, updated or delete one or more of the table's check constraints. When N or Y is displayed in this field, you may overtype the N or Y with U. When you do this, the "Table Check Constraints List" panel is displayed. From this panel, you can add check constraints to the table if it does not yet have any, or you can view, update or delete one or more of the table's existing check constraints.
To update them enter a "U" command.
Chk Const > U
This screen is displayed. This is a sample where the table SNTBL has three check constraints defined.
ROPTCKCL ---------- Table Check Constraints List --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Check Constraints For Table: table >
SSID: ssid ---------------------------------------------------------- authid1
S Creator Constraint Name Beginning of Constraint Text
_ ________ __________________ _________________________________________________
_ authid1 x_xxxxxxx xxxxxx IN ( 'Y' , 'N' )
_ authid1 y_yyyyyyy yyyyyy IN ( 'Y' , 'N' )
_ authid1 z_zzzzzz zzzzzz IN ( 'C' , 'I' )
******************************* BOTTOM OF DATA ********************************
Each listed constraint has line commands available to manage them.
D - Delete a check constraint.
R - Reset a check constraint to its old definition.
S - Select a check constraint for editing.
U - Undelete a check constraint that was deleted during the current Table Check Constraints List screen edit session.
To delete a check constraint use the "D" line command.
ROPTCKCL ---------- Table Check Constraints List --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Check Constraints For Table: table >
SSID: ssid ---------------------------------------------------------- authid1
S Creator Constraint Name Beginning of Constraint Text
_ ________ __________________ _________________________________________________
_ authid1 x_xxxxxx xxxxxx IN ( 'Y' , 'N' )
_ authid1 y_yyyyyy yyyyyy IN ( 'Y' , 'N' )
D authid1 z_zzzzzz zzzzzz IN ( 'C' , 'I' )
******************************* BOTTOM OF DATA ********************************
The line changes as below.
ROPTCKCL ---------- Table Check Constraints List --------- yyyy/mm/dd hh:mm:ss
COMMAND ===> SCROLL ===> CSR
Check Constraints For Table: SNTBL >
SSID: ssid ---------------------------------------------------------- authid1
S Creator Constraint Name Beginning of Constraint Text
_ ________ __________________ _________________________________________________
_ authid1 x_xxxxxx xxxxxx IN ( 'Y' , 'N' )
_ authid1 y_yyyyyy yyyyyy IN ( 'Y' , 'N' )
_ Deleted ****************** *************************************************
******************************* BOTTOM OF DATA ********************************
It is marked as DELETED. The "U" line command can be used during the session to restore it.
PF3 back to the main table alteration screen.
Once back to the RC/M Alteration Strategy Services use the SUM command to check the alteration:
++++++++++++++++++++++++++++ ALTERED OBJECTS +++++++++++++++++++++++++++++
Table Changes for:
Creator: authid1
Name: table
------------ Table Check Constraints Changed ---------------
Constraint Name
------------------------------------------------------------
z_zzzzzz Dropped
Analyze the strategy and this DDL would be generated for this action:
-- **************************************************************
-- * *
-- * TABLE CREATE AND ALTER STATEMENTS *
-- * *
-- **************************************************************
-- authid1.SNTBL WILL BE ALTERED VIA THESE NATIVE DB2 COMMANDS.
ALTER TABLE authid1.table
DROP CONSTRAINT z_zzzzzz ;
.SYNC 5 'ALTER TABLE authid1.table'
Additional Information
Feedback
