Hi
I’m testing Toad for DB2 Beta 5.0 and I’m having issues with the script it
generates with the Schema Compare with a table with an Identity column . The
script generated with Toad for DB2 Beta 4.7 works . Below you will find scripts
for the Source ddl , Target ddl , Script generated with 4.7 and Script generated
with Beta 5.0 . Let me know if you need more info .
Source script:
CREATE TABLE “AH”.“TBAH_BCH_CLM_DTL” (
“BCH_CLM_DTL_ID” INTEGER NOT NULL GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
“BCH_CLM_INFO_ID” INTEGER NOT NULL,
“APPLICATION_ID” INTEGER NOT NULL,
“PTHA_REF_NM” CHARACTER(18),
“IAH_SPONSOR_NM” VARCHAR(100) NOT NULL DEFAULT ,
“CLAIM_AMT” DECIMAL(13,2) NOT NULL DEFAULT ,
“UNIT_CNT” INTEGER NOT NULL DEFAULT ,
“PROGRAM_CDE” CHARACTER(2) NOT NULL DEFAULT ,
“CEAA_CMPLT_CDE” CHARACTER(2) NOT NULL DEFAULT ,
“MAIN_PROJ_TCD” CHARACTER(2) NOT NULL DEFAULT ,
“CBO_PTHA_FND_AMT” DECIMAL(13,2) NOT NULL DEFAULT ,
“CBO_OTHER_AMT” DECIMAL(13,2) NOT NULL DEFAULT ,
“CMHC_ONGOINGYR_CNT” SMALLINT NOT NULL DEFAULT ,
“CMHC_ONGOING_AMT” DECIMAL(13,2) NOT NULL DEFAULT ,
“CBO_ONGOINGYR_CNT” SMALLINT NOT NULL DEFAULT ,
“CBO_ONGOING_AMT” DECIMAL(13,2) NOT NULL DEFAULT ,
“REC_CREATE_TS” TIMESTAMP NOT NULL DEFAULT ,
“REC_UPDATE_UID” CHARACTER(8) NOT NULL DEFAULT ,
“REC_UPDATE_TS” TIMESTAMP NOT NULL DEFAULT
)
IN “TSAH001”
INDEX IN “IXAH001”;
ALTER TABLE “AH”.“TBAH_BCH_CLM_DTL”
DATA CAPTURE NONE
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
ALTER TABLE “AH”.“TBAH_BCH_CLM_DTL”
ADD CONSTRAINT “RCAHAPPLICATIONM” FOREIGN KEY
(“APPLICATION_ID”)
REFERENCES “AH”.“TBAH_APPLICATION”
(“APPLICATION_ID”)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
ALTER TABLE “AH”.“TBAH_BCH_CLM_DTL”
ADD CONSTRAINT “RCAH_BCH_CLM_INFO2” FOREIGN KEY
(“BCH_CLM_INFO_ID”)
REFERENCES “AH”.“TBAH_BCH_CLM_INFO”
(“BCH_CLM_INFO_ID”)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
ALTER TABLE “AH”.“TBAH_BCH_CLM_DTL”
ADD CONSTRAINT “IXAH_BCH_CLM_DTL_1” PRIMARY KEY
(“BCH_CLM_DTL_ID”);
Target Script:
CREATE TABLE “AH”.“TBAH_BCH_CLM_DTL” (
“BCH_CLM_DTL_ID” INTEGER NOT NULL GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
“BCH_CLM_INFO_ID” INTEGER NOT NULL,
“APPLICATION_ID” INTEGER NOT NULL,
“PTHA_REF_NM” CHARACTER(18),
“IAH_SPONSOR_NM” VARCHAR(100) NOT NULL DEFAULT ,
“CLAIM_AMT” DECIMAL(13,2) NOT NULL DEFAULT ,
“PROGRAM_CDE” CHARACTER(2) NOT NULL DEFAULT ,
“CEAA_CMPLT_CDE” CHARACTER(2) NOT NULL DEFAULT ,
“MAIN_PROJ_TCD” CHARACTER(2) NOT NULL DEFAULT ,
“CBO_PTHA_FND_AMT” DECIMAL(13,2) NOT NULL DEFAULT ,
“CBO_OTHER_AMT” DECIMAL(13,2) NOT NULL DEFAULT ,
“CMHC_ONGOINGYR_CNT” SMALLINT NOT NULL DEFAULT ,
“CMHC_ONGOING_AMT” DECIMAL(13,2) NOT NULL DEFAULT ,
“CBO_ONGOINGYR_CNT” SMALLINT NOT NULL DEFAULT ,
“CBO_ONGOING_AMT” DECIMAL(13,2) NOT NULL DEFAULT ,
“REC_CREATE_TS” TIMESTAMP NOT NULL DEFAULT ,
“REC_UPDATE_UID” CHARACTER(8) NOT NULL DEFAULT ,
“REC_UPDATE_TS” TIMESTAMP NOT NULL DEFAULT
)
IN “TSAH001”
INDEX IN “IXAH001”;
ALTER TABLE “AH”.“TBAH_BCH_CLM_DTL”
DATA CAPTURE NONE
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
ALTER TABLE “AH”.“TBAH_BCH_CLM_DTL”
ADD CONSTRAINT “RCAHAPPLICATIONM” FOREIGN KEY
(“APPLICATION_ID”)
REFERENCES “AH”.“TBAH_APPLICATION”
(“APPLICATION_ID”)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
ALTER TABLE “AH”.“TBAH_BCH_CLM_DTL”
ADD CONSTRAINT “RCAH_BCH_CLM_INFO2” FOREIGN KEY
(“BCH_CLM_INFO_ID”)
REFERENCES “AH”.“TBAH_BCH_CLM_INFO”
(“BCH_CLM_INFO_ID”)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
ALTER TABLE “AH”.“TBAH_BCH_CLM_DTL”
ADD CONSTRAINT “IXAH_BCH_CLM_DTL_1” PRIMARY KEY
(“BCH_CLM_DTL_ID”);
SET SCHEMA = ‘DB2ADMIN’;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE “AH”.“TBAH_BCH_CLM_DTL” TO USER
“SPO_AH”;
SET SCHEMA = ‘DB2ADMIN’;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE “AH”.“TBAH_BCH_CLM_DTL” TO GROUP
“AH_READWRITE”;
SET SCHEMA = ‘DB2ADMIN’;
GRANT SELECT ON TABLE “AH”.“TBAH_BCH_CLM_DTL” TO GROUP “AH_READONLY”;
Toad 4.7 Schema Compare generates the following script
– Script created by Quest Schema Compare at 04/10/2011 3:06:53 PM.
– Back up your database before running this script.
– Synchronizing objects from AHDAH_2.AH to AHDAH_1.AH.
