Hi

            I'm doing a SCHEMA COMPARE with TOAD for DB2 4.5.0.722 Beta Version and it generates Script A below .   For table TBAH_PROJ_INFO it generated an EXPORT of the data ,   dropped the table , then proceeded in recreating the table with column PROJ_INFO_ID defined with its IDENTITY definitions then does a load of the data later on .   What I was expecting to happen for tableTBAH_PROJ_INFO  was the following :   EXPORT of the data ,   dropped the table , recreate the table with column PROJ_INFO_ID without its IDENTITY definitions ,  load of the data ,  an Alter to the table for column PROJ_INFO_ID giving its IDENTITY definitions then another Alter to the table with a RESTART value .  
            I do a second     SCHEMA COMPARE with TOAD for DB2 4.5.0.722 Beta Version and it generates Script B below.  As you can see for table TBAH_APPLICATION it generated  EXPORT of the data ,   dropped the table , recreate the table with column APPLICATION_ID without its IDENTITY definitions ,  load of the data ,  an Alter to the table for column APPLICATION_ID giving its IDENTITY definitions then another Alter to the table with a RESTART value .  

           Are there settings / parameters that we set in TOAD that affects which strategy TOAD will use to generate the script ?   Both SCHEMA COMPAREs were done on the same PC , using the same TOAD for DB2 ,  and with the same OPTIONS .    Let me know if you need more info .

Start of Script A:

-- Script created by Quest Schema Compare at 09/07/2009 11:04:18 AM.
-- Back up your database before running this script.
-- Synchronizing objects from DBAHDDL.AH to AHDAH_1.AH.
-- ATTENTION! You have an REORG statement(s) which will COMMIT your transaction.
-----------------------------------------------------------
--                     Unloading data...
-----------------------------------------------------------
SET SCHEMA = "DB2ADMIN";

EXPORT TO
  '%DATA_PATH%\AH_TBAH_CB_LINE123342027.del'
  OF DEL
  SELECT * FROM "AH"."TBAH_CB_LINE";

EXPORT TO
  '%DATA_PATH%\AH_TBAH_PROJ_INFO1500297746.del'
  OF DEL
  SELECT * FROM "AH"."TBAH_PROJ_INFO";

-----------------------------------------------------------
--                  Dropping foreign keys...
-----------------------------------------------------------
-- Drop foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_CBLTR_GL_XREF"
  DROP FOREIGN KEY "RCAHCB_LINE2";

-- Drop foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_EARNING_PRD"
  DROP FOREIGN KEY "RCAHCB_LINE3";

-- Drop foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_SECURITY"
  DROP FOREIGN KEY "RCAHCB_LINE6";

-- Drop foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_CBLNQSTN_XREF"
  DROP FOREIGN KEY "RCAHCB_LINE4";

-- Drop foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_CBLN_CDE_XREF"
  DROP FOREIGN KEY "RCAHCB_LINE5";

-- Drop foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_RULE_XREF"
  DROP FOREIGN KEY "RCAHCB_LINE7";

-- Drop foreign keys, referenced to TBAH_PROJ_INFO
ALTER TABLE "AH"."TBAH_PROJ_AD_RLAT"
  DROP FOREIGN KEY "RCAH_PROJ_INFO_04";

-- Drop foreign keys, referenced to TBAH_PROJ_INFO
ALTER TABLE "AH"."TBAH_PROJ_BLDG"
  DROP FOREIGN KEY "RCAH_PROJ_INFO_02";

-- Drop foreign keys, referenced to TBAH_PROJ_INFO
ALTER TABLE "AH"."TBAH_PROJ_FINC"
  DROP FOREIGN KEY "RCAH_PROJ_INFO_01";

-- Drop foreign keys, referenced to TBAH_PROJ_INFO
ALTER TABLE "AH"."TBAH_PROJ_MLSTN_DT"
  DROP FOREIGN KEY "RCAH_PROJ_INFO_03";

-----------------------------------------------------------
--                    Dropping indexes...
-----------------------------------------------------------
-- Dropping constraint "TBAH_CB_LINE"."IXAH_CB_LINE_01" enforced by index "AH"."IXAH_CB_LINE_01"
ALTER TABLE "AH"."TBAH_CB_LINE"
  DROP PRIMARY KEY;

DROP INDEX "AH"."IXAH_LOAN_DTL_05";

-- Dropping constraint "TBAH_PROJ_INFO"."IXAH_PROJ_INFO_01" enforced by index "AH"."IXAH_PROJ_INFO_01"
ALTER TABLE "AH"."TBAH_PROJ_INFO"
  DROP PRIMARY KEY;

-- Dropping constraint "TBAH_APLC_RJRS_CDE"."IXAH_APLC_RJRSCDE1" enforced by index "AH"."IXAH_APLC_RJRSCDE1"
ALTER TABLE "AH"."TBAH_APLC_RJRS_CDE"
  DROP PRIMARY KEY;

-- Dropping constraint "TBAH_REJECTED_REAS"."IXAH_REJECTEDREAS1" enforced by index "AH"."IXAH_REJECTEDREAS1"
ALTER TABLE "AH"."TBAH_REJECTED_REAS"
  DROP PRIMARY KEY;

-----------------------------------------------------------
-- Dropping tables, views, aliases, nicknames and routines...
-----------------------------------------------------------
DROP TABLE "AH"."TBAH_REJECTED_REAS";

DROP TABLE "AH"."TBAH_APLC_RJRS_CDE";

-----------------------------------------------------------
-- Updating tables, views, aliases, nicknames and routines...
-----------------------------------------------------------
DROP TABLE "AH"."TBAH_CB_LINE";

CREATE TABLE "AH"."TBAH_CB_LINE" (
  "CB_LINE_NBR"        CHARACTER(3)        NOT NULL,
  "REC_EFF_TS"        TIMESTAMP        NOT NULL        DEFAULT ,
  "CB_LINE_NBR_DSE"        VARCHAR(24)        NOT NULL        DEFAULT ,
  "CB_LINE_NBR_DSF"        VARCHAR(24)        NOT NULL        DEFAULT ,
  "ON_RESERVE_IND"        CHARACTER(1)        NOT NULL        DEFAULT ,
  "DISABILITY_IND"        CHARACTER(1)        NOT NULL        DEFAULT ,
  "NHA_SCTN_NBR"        CHARACTER(5)        NOT NULL        DEFAULT ,
  "CPTL_CB_LINE_NBR"        CHARACTER(3)        NOT NULL        DEFAULT ,
  "BGT_CB_LINE_NBR"        CHARACTER(3)        NOT NULL        DEFAULT ,
  "CU_CDE"        CHARACTER(4)        NOT NULL        DEFAULT ,
  "ACTV_PRTY_IND"        CHARACTER(1)        NOT NULL        DEFAULT ,
  "CMHC_SYS_ID"        CHARACTER(4)        NOT NULL        DEFAULT ,
  "PGM_GRP_CDE"        CHARACTER(3)        NOT NULL        DEFAULT ,
  "NHA_PGM_CDE"        CHARACTER(2)        NOT NULL        DEFAULT ,
  "BUDGET_CDE"        CHARACTER(2)        NOT NULL        DEFAULT ,
  "REC_UPDATE_UID"        CHARACTER(8)        NOT NULL        DEFAULT ,
  "REC_UPDATE_TS"        TIMESTAMP        NOT NULL        DEFAULT ,
  "REC_LST_EFF_TS"        TIMESTAMP        NOT NULL        DEFAULT '9999-12-31-23.59.59.999999'
)
  IN "TSAH001"
  INDEX IN "IXAH001";

ALTER TABLE "AH"."TBAH_CB_LINE"
  DATA CAPTURE NONE
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ACTIVATE NOT LOGGED INITIALLY;

--This is system required index
CREATE UNIQUE INDEX "AH"."IXAH_CB_LINE_01"
  ON "AH"."TBAH_CB_LINE"
    ( "CB_LINE_NBR" ASC )
  CLUSTER
  ALLOW REVERSE SCANS;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ADD CONSTRAINT "IXAH_CB_LINE_01" PRIMARY KEY
    ("CB_LINE_NBR");

ALTER TABLE "AH"."TBAH_APPLICATION"
  DROP COLUMN "TOT_UNT_CNT" CASCADE;

REORG TABLE "AH"."TBAH_APPLICATION";

ALTER TABLE "AH"."TBAH_APPLICATION"
  DROP COLUMN "TOT_BED_CNT" CASCADE;

REORG TABLE "AH"."TBAH_APPLICATION";

DROP TABLE "AH"."TBAH_PROJ_INFO";

CREATE TABLE "AH"."TBAH_PROJ_INFO" (
  "PROJ_INFO_ID"        INTEGER        NOT NULL        GENERATED ALWAYS
    AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
       NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
  "APPLICATION_ID"        INTEGER        NOT NULL,
  "PROJ_NM"        VARCHAR(80)        DEFAULT NULL,
  "PROGRAM_SPECIAL_TCD"        CHARACTER(2)        DEFAULT NULL,
  "APLNT_TYP_CDE"        CHARACTER(2)        DEFAULT NULL,
  "ACTIVE_PARTY_CDE"        CHARACTER(2)        DEFAULT NULL,
  "TRIBAL_CONCL_CDE"        CHARACTER(5)        DEFAULT NULL,
  "APRV_LEND_CDE"        CHARACTER(5)        DEFAULT NULL,
  "MKT_GEO_AREA_CDE"        CHARACTER(3)        DEFAULT NULL,
  "RMOT_AREA_IND"        CHARACTER(1)        DEFAULT NULL,
  "FORG_ZN_CDE"        CHARACTER(2)        DEFAULT NULL,
  "HSNG_PGM_PROP_ID"        VARCHAR(15)        DEFAULT NULL,
  "MIN_HLTH_SFTY_IND"        CHARACTER(1)        DEFAULT NULL,
  "ACPT_GOOD_GOVN_IND"        CHARACTER(1)        DEFAULT NULL,
  "RES_FLR_AREA_CNT"        DECIMAL(9,0)        DEFAULT NULL,
  "RES_FLR_MSRMT_TCD"        CHARACTER(2)        DEFAULT NULL,
  "NRES_FLR_AREA_CNT"        DECIMAL(9,0)        DEFAULT NULL,
  "NRES_FLR_MSRMT_TCD"        CHARACTER(2)        DEFAULT NULL,
  "TOT_LAND_AREA_CNT"        DECIMAL(9,0)        DEFAULT NULL,
  "LAND_MSRMT_TCD"        CHARACTER(2)        DEFAULT NULL,
  "TOT_UNT_CNT"        INTEGER        NOT NULL        DEFAULT ,
  "TOT_BED_CNT"        INTEGER        NOT NULL        DEFAULT ,
  "TOT_BLDG_CNT"        INTEGER        NOT NULL        DEFAULT ,
  "TOT_BLDG_RTRFT_CNT"        INTEGER        NOT NULL        DEFAULT ,
  "TOT_UNT_RTRFT_CNT"        INTEGER        NOT NULL        DEFAULT ,
  "TOT_UNT_RTRFT2_CNT"        INTEGER        NOT NULL        DEFAULT ,
  "GROUP_SEQ_NBR"        INTEGER        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_PROJ_INFO"
  DATA CAPTURE NONE
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;

ALTER TABLE "AH"."TBAH_PROJ_INFO"
  ACTIVATE NOT LOGGED INITIALLY;

--This is system required index
CREATE UNIQUE INDEX "AH"."IXAH_PROJ_INFO_01"
  ON "AH"."TBAH_PROJ_INFO"
    ( "PROJ_INFO_ID" ASC )
  ALLOW REVERSE SCANS;

ALTER TABLE "AH"."TBAH_PROJ_INFO"
  ADD CONSTRAINT "IXAH_PROJ_INFO_01" PRIMARY KEY
    ("PROJ_INFO_ID");

SET SCHEMA = "AH";

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN";

CREATE VIEW "AH"."VWAH_PGM_GRP_CDE"
    ( "CDE_TBL_ID", "PGM_GRP_CDE", "CDE_EFF_DT", "CDE_DESC_EN", "CDE_ABRV_EN",
    "CDE_DESC_FR", "CDE_ABRV_FR", "CDE_LST_EFF_DT", "REC_CREATE_UID", "REC_DELETE_TS",
    "REC_CREATE_TS" )
AS
SELECT TBAH_AHPS_CDEDSCN.CDE_TBL_ID,TBAH_AHPS_CDEDSCN.CDE_TBL_KEY,TBAH_AHPS_CDEDSCN.CDE_EFF_DT,TBAH_AHPS_CDEDSCN.CDE_DESC_EN,TBAH_AHPS_CDEDSCN.CDE_ABRV_EN,TBAH_AHPS_CDEDSCN.CDE_DESC_FR,TBAH_AHPS_CDEDSCN.CDE_ABRV_FR,TBAH_AHPS_CDEDSCN.CDE_LST_EFF_DT,TBAH_AHPS_CDEDSCN.REC_CREATE_UID,TBAH_AHPS_CDEDSCN.REC_DELETE_TS,TBAH_AHPS_CDEDSCN.REC_CREATE_TS  
  FROM TBAH_AHPS_CDEDSCN    
  WHERE CDE_TBL_ID = 'PGGCD'
WITH NO ROW MOVEMENT;

-----------------------------------------------------------
--                    Updating indexes...
-----------------------------------------------------------
SET SCHEMA = "DB2ADMIN";

CREATE INDEX "AH"."IXAH_LOAN_DTL_05"
  ON "AH"."TBAH_LOAN_DTL"
    ( "APPLICATION_ID" ASC )
  CLUSTER
  ALLOW REVERSE SCANS;

CREATE INDEX "AH"."IXAH_PROJ_INFO_02"
  ON "AH"."TBAH_PROJ_INFO"
    ( "APPLICATION_ID" ASC )
  CLUSTER
  ALLOW REVERSE SCANS;

CREATE INDEX "AH"."IXAH_PROJ_INFO_03"
  ON "AH"."TBAH_PROJ_INFO"
    ( "FORG_ZN_CDE" ASC )
  ALLOW REVERSE SCANS;

-----------------------------------------------------------
--                      Loading data...
-----------------------------------------------------------
COMMIT;

LOAD CLIENT FROM '%DATA_PATH%\AH_TBAH_CB_LINE123342027.del'
  OF DEL
  INSERT INTO AH.TBAH_CB_LINE(
    CB_LINE_NBR,
    REC_EFF_TS,
    CB_LINE_NBR_DSE,
    CB_LINE_NBR_DSF,
    ON_RESERVE_IND,
    DISABILITY_IND,
    NHA_SCTN_NBR,
    CPTL_CB_LINE_NBR,
    BGT_CB_LINE_NBR,
    CU_CDE,
    ACTV_PRTY_IND,
    CMHC_SYS_ID,
    NHA_PGM_CDE,
    BUDGET_CDE,
    REC_UPDATE_UID,
    REC_UPDATE_TS,
    REC_LST_EFF_TS
    )
 INDEXING MODE REBUILD
 ALLOW NO ACCESS;

LOAD CLIENT FROM '%DATA_PATH%\AH_TBAH_PROJ_INFO1500297746.del'
  OF DEL
  INSERT INTO AH.TBAH_PROJ_INFO(
    PROJ_INFO_ID,
    APPLICATION_ID,
    PROJ_NM,
    PROGRAM_SPECIAL_TCD,
    APLNT_TYP_CDE,
    ACTIVE_PARTY_CDE,
    TRIBAL_CONCL_CDE,
    APRV_LEND_CDE,
    MKT_GEO_AREA_CDE,
    RMOT_AREA_IND,
    FORG_ZN_CDE,
    HSNG_PGM_PROP_ID,
    MIN_HLTH_SFTY_IND,
    ACPT_GOOD_GOVN_IND,
    RES_FLR_AREA_CNT,
    RES_FLR_MSRMT_TCD,
    NRES_FLR_AREA_CNT,
    NRES_FLR_MSRMT_TCD,
    TOT_LAND_AREA_CNT,
    LAND_MSRMT_TCD,
    TOT_BLDG_CNT,
    TOT_BLDG_RTRFT_CNT,
    TOT_UNT_RTRFT_CNT,
    TOT_UNT_RTRFT2_CNT,
    REC_CREATE_TS,
    REC_UPDATE_UID,
    REC_UPDATE_TS
    )
 INDEXING MODE REBUILD
 ALLOW NO ACCESS;

-----------------------------------------------------------
--                 Restoring foreign keys...
-----------------------------------------------------------
-- Restore foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_CBLTR_GL_XREF"
  ADD CONSTRAINT "RCAHCB_LINE2" FOREIGN KEY
    ("CB_LINE_NBR")
  REFERENCES "AH"."TBAH_CB_LINE"
    ("CB_LINE_NBR")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_EARNING_PRD"
  ADD CONSTRAINT "RCAHCB_LINE3" FOREIGN KEY
    ("CB_LINE_NBR")
  REFERENCES "AH"."TBAH_CB_LINE"
    ("CB_LINE_NBR")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_SECURITY"
  ADD CONSTRAINT "RCAHCB_LINE6" FOREIGN KEY
    ("CB_LINE_NBR")
  REFERENCES "AH"."TBAH_CB_LINE"
    ("CB_LINE_NBR")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_CBLNQSTN_XREF"
  ADD CONSTRAINT "RCAHCB_LINE4" FOREIGN KEY
    ("CB_LINE_NBR")
  REFERENCES "AH"."TBAH_CB_LINE"
    ("CB_LINE_NBR")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_CBLN_CDE_XREF"
  ADD CONSTRAINT "RCAHCB_LINE5" FOREIGN KEY
    ("CB_LINE_NBR")
  REFERENCES "AH"."TBAH_CB_LINE"
    ("CB_LINE_NBR")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_CB_LINE
ALTER TABLE "AH"."TBAH_RULE_XREF"
  ADD CONSTRAINT "RCAHCB_LINE7" FOREIGN KEY
    ("CB_LINE_NBR")
  REFERENCES "AH"."TBAH_CB_LINE"
    ("CB_LINE_NBR")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_PROJ_INFO
ALTER TABLE "AH"."TBAH_PROJ_AD_RLAT"
  ADD CONSTRAINT "RCAH_PROJ_INFO_04" FOREIGN KEY
    ("PROJ_INFO_ID")
  REFERENCES "AH"."TBAH_PROJ_INFO"
    ("PROJ_INFO_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_PROJ_INFO
ALTER TABLE "AH"."TBAH_PROJ_BLDG"
  ADD CONSTRAINT "RCAH_PROJ_INFO_02" FOREIGN KEY
    ("PROJ_INFO_ID")
  REFERENCES "AH"."TBAH_PROJ_INFO"
    ("PROJ_INFO_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_PROJ_INFO
ALTER TABLE "AH"."TBAH_PROJ_FINC"
  ADD CONSTRAINT "RCAH_PROJ_INFO_01" FOREIGN KEY
    ("PROJ_INFO_ID")
  REFERENCES "AH"."TBAH_PROJ_INFO"
    ("PROJ_INFO_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_PROJ_INFO
ALTER TABLE "AH"."TBAH_PROJ_MLSTN_DT"
  ADD CONSTRAINT "RCAH_PROJ_INFO_03" FOREIGN KEY
    ("PROJ_INFO_ID")
  REFERENCES "AH"."TBAH_PROJ_INFO"
    ("PROJ_INFO_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-----------------------------------------------------------
--                  Updating foreign keys...
-----------------------------------------------------------
ALTER TABLE "AH"."TBAH_PROJ_INFO"
  ADD CONSTRAINT "RCAH_FORG_ZN_CDE_1" FOREIGN KEY
    ("FORG_ZN_CDE")
  REFERENCES "AH"."TBAH_FORG_ZN_CDE"
    ("FORG_ZN_CDE")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

ALTER TABLE "AH"."TBAH_PROJ_INFO"
  ADD CONSTRAINT "RCAHAPPLICATIONJ" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

End of Script A:



Start of Script B:

-- Script created by Quest Schema Compare at 09/07/2009 11:38:43 AM.
-- Back up your database before running this script.
-- Synchronizing objects from AHDAH_1.AH to AHSAH_1.AH.
-----------------------------------------------------------
--                     Unloading data...
-----------------------------------------------------------
SET SCHEMA = "DB2ADMIN";

EXPORT TO
  '%DATA_PATH%\AH_TBAH_APPLICATION373671877.del'
  OF DEL
  SELECT * FROM "AH"."TBAH_APPLICATION";

EXPORT TO
  '%DATA_PATH%\AH_TBAH_REPAIR_DTL373671877.del'
  OF DEL
  SELECT * FROM "AH"."TBAH_REPAIR_DTL";

-----------------------------------------------------------
--                  Dropping foreign keys...
-----------------------------------------------------------
-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_APLCCNTC_RLAT"
  DROP FOREIGN KEY "RCAHAPPLICATION5";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_APLCNTPD_RLAT"
  DROP FOREIGN KEY "RCAHAPPLICATION4";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_APLCXTSN_RLAT"
  DROP FOREIGN KEY "RCAHAPPLICATIONH";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_APLC_STAT"
  DROP FOREIGN KEY "RCAHAPPLICATION1";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_LOAN_DTL"
  DROP FOREIGN KEY "RCAHAPPLICATIONB";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_FINC_TRN"
  DROP FOREIGN KEY "RCAHAPPLICATION2";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_RELATED_ACCNT"
  DROP FOREIGN KEY "RCAHAPPLICATION9";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_FINC_TRN_DRFT"
  DROP FOREIGN KEY "RCAHAPPLICATIONI";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_REPAIR_SUM"
  DROP FOREIGN KEY "RCAHAPPLICATIONE";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_APLCAGNT_RLAT"
  DROP FOREIGN KEY "RCAHAPPLICATIOND";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_HHDISABILITY"
  DROP FOREIGN KEY "RCAHAPPLICATIONG";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_HSHLD_DEDUCT"
  DROP FOREIGN KEY "RCAHAPPLICATIONC";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_HSHLD_INCOME"
  DROP FOREIGN KEY "RCAHAPPLICATION7";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_IAD_XTSN_SUM"
  DROP FOREIGN KEY "RCAHAPPLICATION3";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_LETTER_LOG"
  DROP FOREIGN KEY "RCAHAPPLICATIONF";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_REJECTED_REAS"
  DROP FOREIGN KEY "RCAHAPPLICATION6";

-- Drop foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_PROPERTY"
  DROP FOREIGN KEY "RCAHAPPLICATION8";

-----------------------------------------------------------
--                    Dropping indexes...
-----------------------------------------------------------
-- Dropping constraint "TBAH_APPLICATION"."IXAH_APPLICATION_1" enforced by index "AH"."IXAH_APPLICATION_1"
ALTER TABLE "AH"."TBAH_APPLICATION"
  DROP PRIMARY KEY;

-- Dropping constraint "TBAH_REPAIR_DTL"."IXAH_REPAIR_DTL_01" enforced by index "AH"."IXAH_REPAIR_DTL_01"
ALTER TABLE "AH"."TBAH_REPAIR_DTL"
  DROP PRIMARY KEY;

-----------------------------------------------------------
-- Updating tables, views, aliases, nicknames and routines...
-----------------------------------------------------------
ALTER TABLE "AH"."TBAH_CB_LINE"
  ALTER COLUMN "ON_RESERVE_IND" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ALTER COLUMN "DISABILITY_IND" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ALTER COLUMN "NHA_SCTN_NBR" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ALTER COLUMN "CPTL_CB_LINE_NBR" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ALTER COLUMN "BGT_CB_LINE_NBR" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ALTER COLUMN "CU_CDE" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ALTER COLUMN "ACTV_PRTY_IND" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ALTER COLUMN "CMHC_SYS_ID" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ALTER COLUMN "NHA_PGM_CDE" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_CB_LINE"
  ALTER COLUMN "BUDGET_CDE" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_FIRST_NATION"
  ALTER COLUMN "INAC_FRST_NTN_CDE" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_FIRST_NATION"
  ALTER COLUMN "PROV_CDE" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_FIRST_NATION"
  ALTER COLUMN "REC_UPDATE_UID" SET        DEFAULT;

DROP TABLE "AH"."TBAH_APPLICATION";

CREATE TABLE "AH"."TBAH_APPLICATION" (
  "APPLICATION_ID"        INTEGER        NOT NULL,
  "CMHC_ACCT_NBR"        INTEGER        NOT NULL        DEFAULT ,
  "SRL_CMTMT_NBR"        CHARACTER(3)        NOT NULL        DEFAULT ,
  "OFF_CDE"        CHARACTER(3)        NOT NULL        DEFAULT ,
  "CB_LINE_NBR"        CHARACTER(3)        NOT NULL        DEFAULT ,
  "LANG_CRSP_CDE"        CHARACTER(2)        NOT NULL        DEFAULT ,
  "TOT_UNT_CNT"        INTEGER        NOT NULL        DEFAULT ,
  "TOT_BED_CNT"        INTEGER        NOT NULL        DEFAULT ,
  "CLNT_TCD"        CHARACTER(2)        NOT NULL        DEFAULT ,
  "NATV_BND_APLNT_IND"        CHARACTER(1)        NOT NULL        DEFAULT ,
  "NATV_STAT_IND"        CHARACTER(1)        NOT NULL        DEFAULT ,
  "FIRST_NATION_ID"        INTEGER        DEFAULT NULL,
  "SPONSOR_ID"        INTEGER        DEFAULT NULL,
  "AGENT_FEE_HOLD_IND"        CHARACTER(1)        NOT NULL        DEFAULT ,
  "ADVANCING_STAT_CDE"        CHARACTER(2)        DEFAULT NULL,
  "ADVANCING_STAT_DT"        DATE        DEFAULT NULL,
  "ACT_INT_ADJ_DT"        DATE        DEFAULT NULL,
  "DEPENDENT_CNT"        SMALLINT        NOT NULL        DEFAULT ,
  "INCM_INFO_VRFY_DT"        DATE        DEFAULT NULL,
  "INCM_LMT_AMT"        DECIMAL(13,2)        DEFAULT NULL,
  "SCTY_DOC_RCV_DT"        DATE        DEFAULT NULL,
  "APLC_ASGN_OP_ID"        CHARACTER(8)        NOT NULL        DEFAULT ,
  "APRV_LN_GRP_SEQNBR"        INTEGER        DEFAULT NULL,
  "CUR_LN_GRP_SEQ_NBR"        INTEGER        DEFAULT NULL,
  "APLC_RCV_TCD"        CHARACTER(2)        DEFAULT NULL,
  "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_APPLICATION"
  DATA CAPTURE NONE
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;

ALTER TABLE "AH"."TBAH_APPLICATION"
  ACTIVATE NOT LOGGED INITIALLY;

--This is system required index
CREATE UNIQUE INDEX "AH"."IXAH_APPLICATION_1"
  ON "AH"."TBAH_APPLICATION"
    ( "APPLICATION_ID" ASC )
  ALLOW REVERSE SCANS;

ALTER TABLE "AH"."TBAH_APPLICATION"
  ADD CONSTRAINT "IXAH_APPLICATION_1" PRIMARY KEY
    ("APPLICATION_ID");

ALTER TABLE "AH"."TBAH_DTL_STAT_CDE"
  ALTER COLUMN "DTL_STATUS_CDE_DSE" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_DTL_STAT_CDE"
  ALTER COLUMN "DTL_STATUS_CDE_ABE" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_DTL_STAT_CDE"
  ALTER COLUMN "DTL_STATUS_CDE_DSF" SET        DEFAULT;

ALTER TABLE "AH"."TBAH_DTL_STAT_CDE"
  ALTER COLUMN "DTL_STATUS_CDE_ABF" SET        DEFAULT;

DROP TABLE "AH"."TBAH_REPAIR_DTL";

CREATE TABLE "AH"."TBAH_REPAIR_DTL" (
  "REPAIR_DTL_ID"        INTEGER        NOT NULL,
  "APPLICATION_ID"        INTEGER        DEFAULT NULL,
  "RPR_ITEM_SEQ_NBR"        SMALLINT        NOT NULL        DEFAULT ,
  "WRK_RPR_TCD"        CHARACTER(2)        NOT NULL        DEFAULT ,
  "REPAIR_TCD"        CHARACTER(4)        NOT NULL        DEFAULT ,
  "RPR_CTGRY_CDE"        CHARACTER(2)        NOT NULL        DEFAULT ,
  "RPR_COST_AMT"        DECIMAL(13,2)        NOT NULL        DEFAULT ,
  "RPR_COST_EST_AMT"        DECIMAL(13,2)        NOT NULL        DEFAULT ,
  "RPR_COST_TO_DT_AMT"        DECIMAL(13,2)        NOT NULL        DEFAULT ,
  "DTL_STATUS_CDE"        CHARACTER(2)        NOT NULL        DEFAULT ,
  "DTL_STATUS_UID"        CHARACTER(8)        NOT NULL        DEFAULT ,
  "DTL_STATUS_TS"        TIMESTAMP        NOT NULL        DEFAULT ,
  "GROUP_SEQ_NBR"        INTEGER        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_REPAIR_DTL"
  DATA CAPTURE NONE
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE;

ALTER TABLE "AH"."TBAH_REPAIR_DTL"
  ACTIVATE NOT LOGGED INITIALLY;

--This is system required index
CREATE UNIQUE INDEX "AH"."IXAH_REPAIR_DTL_01"
  ON "AH"."TBAH_REPAIR_DTL"
    ( "REPAIR_DTL_ID" ASC )
  ALLOW REVERSE SCANS;

ALTER TABLE "AH"."TBAH_REPAIR_DTL"
  ADD CONSTRAINT "IXAH_REPAIR_DTL_01" PRIMARY KEY
    ("REPAIR_DTL_ID");

-----------------------------------------------------------
--                    Updating indexes...
-----------------------------------------------------------
CREATE INDEX "AH"."IXAH_APPLICATION_2"
  ON "AH"."TBAH_APPLICATION"
    ( "ADVANCING_STAT_CDE" ASC )
  ALLOW REVERSE SCANS;

CREATE INDEX "AH"."IXAH_APPLICATION_3"
  ON "AH"."TBAH_APPLICATION"
    ( "FIRST_NATION_ID" ASC )
  ALLOW REVERSE SCANS;

CREATE INDEX "AH"."IXAH_APPLICATION_4"
  ON "AH"."TBAH_APPLICATION"
    ( "CB_LINE_NBR" ASC )
  ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "AH"."IXAH_APPLICATION_5"
  ON "AH"."TBAH_APPLICATION"
    ( "CMHC_ACCT_NBR" ASC, "SRL_CMTMT_NBR" ASC )
  CLUSTER
  ALLOW REVERSE SCANS;

CREATE INDEX "AH"."IXAH_REPAIR_DTL_02"
  ON "AH"."TBAH_REPAIR_DTL"
    ( "DTL_STATUS_CDE" ASC )
  ALLOW REVERSE SCANS;

-----------------------------------------------------------
--                      Loading data...
-----------------------------------------------------------
COMMIT;

IMPORT FROM
  '%DATA_PATH%\AH_TBAH_APPLICATION373671877.del'
 OF DEL
  MODIFIED BY COMPOUND=5
  NOCHECKLENGTHS
  COMMITCOUNT 1000
  INSERT INTO "AH"."TBAH_APPLICATION"
 (
  "APPLICATION_ID",
  "CMHC_ACCT_NBR",
  "SRL_CMTMT_NBR",
  "OFF_CDE",
  "CB_LINE_NBR",
  "LANG_CRSP_CDE",
  "TOT_UNT_CNT",
  "TOT_BED_CNT",
  "CLNT_TCD",
  "NATV_BND_APLNT_IND",
  "NATV_STAT_IND",
  "FIRST_NATION_ID",
  "AGENT_FEE_HOLD_IND",
  "ADVANCING_STAT_CDE",
  "ADVANCING_STAT_DT",
  "ACT_INT_ADJ_DT",
  "DEPENDENT_CNT",
  "INCM_INFO_VRFY_DT",
  "INCM_LMT_AMT",
  "SCTY_DOC_RCV_DT",
  "APLC_ASGN_OP_ID",
  "REC_CREATE_TS",
  "REC_UPDATE_UID",
  "REC_UPDATE_TS"
  );

IMPORT FROM
  '%DATA_PATH%\AH_TBAH_REPAIR_DTL373671877.del'
 OF DEL
  MODIFIED BY COMPOUND=5
  NOCHECKLENGTHS
  COMMITCOUNT 1000
  INSERT INTO "AH"."TBAH_REPAIR_DTL"
 (
  "REPAIR_DTL_ID",
  "APPLICATION_ID",
  "RPR_ITEM_SEQ_NBR",
  "RPR_CTGRY_CDE",
  "RPR_COST_AMT",
  "RPR_COST_TO_DT_AMT",
  "DTL_STATUS_CDE",
  "DTL_STATUS_UID",
  "DTL_STATUS_TS",
  "GROUP_SEQ_NBR",
  "REC_CREATE_TS",
  "REC_UPDATE_UID",
  "REC_UPDATE_TS"
  );

-----------------------------------------------------------
--                      Loading data...
-----------------------------------------------------------
ALTER TABLE  "AH"."TBAH_APPLICATION"
ALTER "APPLICATION_ID" SET         GENERATED ALWAYS
    AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
       NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER);

ALTER TABLE  "AH"."TBAH_APPLICATION" ALTER COLUMN "APPLICATION_ID"
RESTART WITH 101286;

ALTER TABLE  "AH"."TBAH_REPAIR_DTL"
ALTER "REPAIR_DTL_ID" SET         GENERATED ALWAYS
    AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE,
       NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER);

ALTER TABLE  "AH"."TBAH_REPAIR_DTL" ALTER COLUMN "REPAIR_DTL_ID"
RESTART WITH 217769;

-----------------------------------------------------------
--                 Restoring foreign keys...
-----------------------------------------------------------
-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_APLCCNTC_RLAT"
  ADD CONSTRAINT "RCAHAPPLICATION5" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_APLCNTPD_RLAT"
  ADD CONSTRAINT "RCAHAPPLICATION4" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_APLCXTSN_RLAT"
  ADD CONSTRAINT "RCAHAPPLICATIONH" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_APLC_STAT"
  ADD CONSTRAINT "RCAHAPPLICATION1" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_LOAN_DTL"
  ADD CONSTRAINT "RCAHAPPLICATIONB" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_FINC_TRN"
  ADD CONSTRAINT "RCAHAPPLICATION2" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_RELATED_ACCNT"
  ADD CONSTRAINT "RCAHAPPLICATION9" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_FINC_TRN_DRFT"
  ADD CONSTRAINT "RCAHAPPLICATIONI" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_REPAIR_SUM"
  ADD CONSTRAINT "RCAHAPPLICATIONE" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_APLCAGNT_RLAT"
  ADD CONSTRAINT "RCAHAPPLICATIOND" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_HHDISABILITY"
  ADD CONSTRAINT "RCAHAPPLICATIONG" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_HSHLD_DEDUCT"
  ADD CONSTRAINT "RCAHAPPLICATIONC" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_HSHLD_INCOME"
  ADD CONSTRAINT "RCAHAPPLICATION7" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_IAD_XTSN_SUM"
  ADD CONSTRAINT "RCAHAPPLICATION3" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_LETTER_LOG"
  ADD CONSTRAINT "RCAHAPPLICATIONF" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_REJECTED_REAS"
  ADD CONSTRAINT "RCAHAPPLICATION6" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-- Restore foreign keys, referenced to TBAH_APPLICATION
ALTER TABLE "AH"."TBAH_PROPERTY"
  ADD CONSTRAINT "RCAHAPPLICATION8" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE RESTRICT
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-----------------------------------------------------------
--                  Updating foreign keys...
-----------------------------------------------------------
ALTER TABLE "AH"."TBAH_APPLICATION"
  ADD CONSTRAINT "RCAHFIRST_NATION1" FOREIGN KEY
    ("FIRST_NATION_ID")
  REFERENCES "AH"."TBAH_FIRST_NATION"
    ("FIRST_NATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

ALTER TABLE "AH"."TBAH_APPLICATION"
  ADD CONSTRAINT "RCAHCB_LINE1" FOREIGN KEY
    ("CB_LINE_NBR")
  REFERENCES "AH"."TBAH_CB_LINE"
    ("CB_LINE_NBR")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

ALTER TABLE "AH"."TBAH_APPLICATION"
  ADD CONSTRAINT "RCAHADVN_STAT_CDE1" FOREIGN KEY
    ("ADVANCING_STAT_CDE")
  REFERENCES "AH"."TBAH_ADVN_STAT_CDE"
    ("ADVANCING_STAT_CDE")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

ALTER TABLE "AH"."TBAH_APPLICATION"
  ADD CONSTRAINT "RCAHAPPLICATIONU" FOREIGN KEY
    ("SPONSOR_ID")
  REFERENCES "AH"."TBAH_SPONSOR"
    ("SPONSOR_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

ALTER TABLE "AH"."TBAH_REPAIR_DTL"
  ADD CONSTRAINT "RCAHDTL_STAT_CDE2" FOREIGN KEY
    ("DTL_STATUS_CDE")
  REFERENCES "AH"."TBAH_DTL_STAT_CDE"
    ("DTL_STATUS_CDE")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

ALTER TABLE "AH"."TBAH_REPAIR_DTL"
  ADD CONSTRAINT "RCAHAPPLICATIONA" FOREIGN KEY
    ("APPLICATION_ID")
  REFERENCES "AH"."TBAH_APPLICATION"
    ("APPLICATION_ID")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    ENABLE QUERY OPTIMIZATION;

-----------------------------------------------------------
--                   Executing runstats...
-----------------------------------------------------------
RUNSTATS ON TABLE "AH"."TBAH_APPLICATION"
  ALLOW WRITE ACCESS;

RUNSTATS ON TABLE "AH"."TBAH_REPAIR_DTL"
  ALLOW WRITE ACCESS;

End of Script B:


thanks

Andre

André Gagnier Database Administrator Tel: (613) 748-2279 Email: agagnier@... 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@... 700 chemin Montréal, C3-426, Ottawa (Ontario) K1A 0P7 Société canadienne d'hypothèques et de logement (SCHL) http://www.schl.ca