Examples of copying constraints

These examples show the DDL that is generated when you copy constraints between heterogeneous databases.

The generated DDL is shown on the Preview DDL page in the Paste Database Objects wizard.

Table 1 shows examples of the DDL that is generated for copying constraints from a DB2® Version 9.7 for Linux, UNIX, and Windows source database to a DB2 Version 9.1 for z/OS® target database.

Table 1. Examples of copying constraints from DB2 Version 9.7 for Linux, UNIX, and Windows to DB2 Version 9.1 for z/OS database
DB2 Version 9.7 for Linux, UNIX, and Windows constraint definition Generated DDL for DB2 Version 9.1 for z/OS database
CREATE TABLE test2
  (col1 CHAR(20) NOT NULL
	)
  DATA CAPTURE NONE 
  IN USERS;


ALTER TABLE test2
   ADD CONSTRAINT pk_test1
   PRIMARY KEY (col1); 
CREATE TABLE NEWTON.TEST2
  (COL1 CHAR(20) NOT NULL
  )
  AUDIT NONE
  DATA CAPTURE NONE 
  CCSID EBCDIC;

ALTER TABLE NEWTON.TEST2
   ADD CONSTRAINT PK_TEST1
   PRIMARY KEY (COL1);
CREATE TABLE test4
  (col1 CHAR(20) NOT NULL
  )
  DATA CAPTURE NONE 
  IN USERS;


ALTER TABLE test4
   ADD CONSTRAINT uq_test1 UNIQUE (col1);
CREATE TABLE NEWTON.TEST4
  (COL1 CHAR(20) NOT NULL
  )
  AUDIT NONE
  DATA CAPTURE NONE 
  CCSID EBCDIC;

ALTER TABLE NEWTON.TEST4
   ADD CONSTRAINT UQ_TEST1 UNIQUE (COL1);
CREATE TABLE test8
  (col1 CHAR(20) NOT NULL
  )
  DATA CAPTURE NONE 
  IN USERS;


ALTER TABLE test8
   ADD CONSTRAINT chck_test1
   CHECK (col1 > '00060');
CREATE TABLE NEWTON.TEST8
  (COL1 CHAR(20) NOT NULL
  )
  AUDIT NONE
  DATA CAPTURE NONE 
  CCSID EBCDIC;

ALTER TABLE NEWTON.TEST8
   ADD CONSTRAINT CHCK_TEST1
   CHECK (COL1 > '00060');
CREATE TABLE test15
  (col1 CHAR(20) NOT NULL
  )
  DATA CAPTURE NONE 
  IN USERS;


ALTER TABLE test15
   ADD CONSTRAINT ref_test1
   FOREIGN KEY (col1)
   REFERENCES test2 (col1);
CREATE TABLE NEWTON.TEST15
  (COL1 CHAR(20) NOT NULL
  )
  AUDIT NONE
  DATA CAPTURE NONE 
  CCSID EBCDIC;

ALTER TABLE NEWTON.TEST15
   ADD CONSTRAINT REF_TEST1
   FOREIGN KEY (COL1)
   REFERENCES NEWTON.TEST2 (COL1);
CREATE TABLE test11b
  (col1 CHAR(20)
  )
  DATA CAPTURE NONE 
  IN USERS;


ALTER TABLE test11b
   ADD CONSTRAINT ref_test4
   FOREIGN KEY (col1)
   REFERENCES test2 (col1)
   ON DELETE CASCADE;
CREATE TABLE NEWTON.TEST11B
  (COL1 CHAR(20)
  )
  AUDIT NONE
  DATA CAPTURE NONE 
  CCSID EBCDIC;

ALTER TABLE NEWTON.TEST11B
   ADD CONSTRAINT REF_TEST4
   FOREIGN KEY (COL1)
   REFERENCES NEWTON.TEST2 (COL1)
   ON DELETE CASCADE;

Table 2 shows examples of the DDL that is generated for copying constraints from a DB2 Version 9.1 for z/OS source database to a DB2 Version 9.7 for Linux, UNIX, and Windows target database.

Table 2. Examples of copying constraints from DB2 Version 9.1 for z/OS database to a DB2 Version 9.7 for Linux, UNIX, and Windows database
DB2 Version 9.1 for z/OS database constraint definition Generated DDL for DB2 Version 9.7 for Linux, UNIX, and Windows
CREATE TABLE test2z
  (col1 CHAR(20) NOT NULL
  )
  AUDIT NONE
  DATA CAPTURE NONE 
  CCSID EBCDIC;

ALTER TABLE test2z
   ADD CONSTRAINT pk_test1
   PRIMARY KEY (col1);
CREATE TABLE DB2ADMIN.TEST2Z
  (COL1 CHAR(20) NOT NULL
  )
  DATA CAPTURE NONE ;



ALTER TABLE DB2ADMIN.TEST2Z
   ADD CONSTRAINT PK_TEST1
   PRIMARY KEY (COL1);
CREATE TABLE test4z
  (col1 CHAR(20) NOT NULL
  )
  AUDIT NONE
  DATA CAPTURE NONE 
  CCSID EBCDIC;

ALTER TABLE test4z
   ADD CONSTRAINT uq_test1 UNIQUE (col1);
CREATE TABLE DB2ADMIN.TEST4Z
  (COL1 CHAR(20) NOT NULL
  )
  DATA CAPTURE NONE ;



ALTER TABLE DB2ADMIN.TEST4Z
   ADD CONSTRAINT UQ_TEST1 UNIQUE (COL1);
CREATE TABLE test8z
  (col1 CHAR(20) NOT NULL
  )
  AUDIT NONE
  DATA CAPTURE NONE 
  CCSID EBCDIC;

ALTER TABLE test8z
   ADD CONSTRAINT chck_test1
   CHECK (col1 > '00060');
CREATE TABLE DB2ADMIN.TEST8Z
  (COL1 CHAR(20) NOT NULL
  )
  DATA CAPTURE NONE ;



ALTER TABLE DB2ADMIN.TEST8Z
   ADD CONSTRAINT CHCK_TEST1
   CHECK (COL1 > '00060');
CREATE TABLE test15z
  (col1 CHAR(20) NOT NULL
  )
  AUDIT NONE
  DATA CAPTURE NONE 
  CCSID EBCDIC;

ALTER TABLE test15z
   ADD CONSTRAINT ref_test1
   FOREIGN KEY (col1)
   REFERENCES test2z (col1);
CREATE TABLE DB2ADMIN.TEST15Z
  (COL1 CHAR(20) NOT NULL
  )
  DATA CAPTURE NONE ;



ALTER TABLE DB2ADMIN.TEST15Z
   ADD CONSTRAINT REF_TEST1
   FOREIGN KEY (COL1)
   REFERENCES DB2ADMIN.TEST2Z (COL1);
CREATE TABLE test11bz
  (col1 CHAR(20) WITH DEFAULT NULL
  )
  AUDIT NONE
  DATA CAPTURE NONE 
  CCSID EBCDIC;

ALTER TABLE test11bz
   ADD CONSTRAINT ref_test4
   FOREIGN KEY (col1)
   REFERENCES test2z (col1)
   ON DELETE CASCADE;
CREATE TABLE DB2ADMIN.TEST11BZ
  (COL1 CHAR(20) DEFAULT NULL
  )
  DATA CAPTURE NONE ;



ALTER TABLE DB2ADMIN.TEST11BZ
   ADD CONSTRAINT REF_TEST4
   FOREIGN KEY (COL1)
   REFERENCES DB2ADMIN.TEST2Z (COL1)
   ON DELETE CASCADE;

Feedback