Toad World® Forums

Toad for DB2 Beta 5.0 Issues with the Schema Compare with a table with an Identity column

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.

Hi,

Will you please tell me the full release number of the 5.0 beta you were using?

Regards,
Adam

Hi

should be the latest 5.0.0.461 .

thanks

Andre
André Gagnier
Database Administrator
Tel: (613) 748-2279
Email: agagnier@cmhc-schl.gc.ca
700 Montreal Road, C3-426, Ottawa, Ontario, K1A 0P7
Canada Mortgage and Housing Corporation (CMHC)
http://www.cmhc.ca
André Gagnier
Administrateur de base de données
Téléphone : (613) 748-2279
Courriel : agagnier@cmhc-schl.gc.ca
700 chemin Montréal, C3-426, Ottawa (Ontario) K1A 0P7
Société canadienne d’hypothèques et de logement (SCHL)
http://www.schl.ca

From:
adam.ririe_906 To:
Date:
04/10/2011 04:50 PM Subject:
[toad_db2] Re: Toad for DB2 Beta 5.0 Issues with the Schema Compare with a table
with an Identity column Sent by:
image004.gif

Hi

should be the latest 5.0.0.461 .

thanks

Andre
André Gagnier
Database Administrator
Tel: (613) 748-2279
Email: agagnier@cmhc-schl.gc.ca
700 Montreal Road, C3-426, Ottawa, Ontario, K1A 0P7
Canada Mortgage and Housing Corporation (CMHC)
http://www.cmhc.ca
André Gagnier
Administrateur de base de données
Téléphone : (613) 748-2279
Courriel : agagnier@cmhc-schl.gc.ca
700 chemin Montréal, C3-426, Ottawa (Ontario) K1A 0P7
Société canadienne d’hypothèques et de logement (SCHL)
http://www.schl.ca

From:
adam.ririe_906 To:
Date:
04/10/2011 04:50 PM Subject:
[toad_db2] Re: Toad for DB2 Beta 5.0 Issues with the Schema Compare with a table
with an Identity column Sent by:
att1.dat (43 Bytes)

Hi Andre,

I’m having trouble recreating your script issue on 5.0.0.461. Can you please send me screen shots of your Tools -> Options -> Schema compare settings.

You can send them to me directly at adam.ririe@quest.com

Regards,
Adam

This issue has been resolved on the final beta.

Thanks