Delete an existing check constraint on a table using RC/Migrator
search cancel

Delete an existing check constraint on a table using RC/Migrator

book

Article ID: 235111

calendar_today

Updated On:

Products

RC/Migrator for DB2 for z/OS

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

Table Check Constraints List Screen