RI Constraint generation - FK DDL vs. trigger logic

[Anon Anon]

Hi,

We are switching from DB2 to Oracle, and I am in the midst of doing all of the TD work.  When I generate the DDL (along with PKs, FK, RI, etc.) it correctly generates FL DDL for most, but not all, of my RI Constraints.  I cannot find anything different - does anyone know what makes Gen choose to generate FK DDL vs. trigger logic?

Table LOC_HIERARCHY_GROUP has 2 relationships.  In DB2, they both generated FK DDL.  In Oracle, this one generates FK DDL:

 

And this one generates trigger logic.  Why is Oracle different??  Is there some property I can switch to make all RI Triggers generate FK DDL?

[DARIUS PANAHY]

Have you run the 'Referential Integrity Process' tool after having switched the DBMS in Technical Design?

 

Since the support for RI differs between DBMS products, the RI process sets the enforcement to Gen if the chosen DBMS does not support the specific type of delete rule.

 

I believe that DB2 has wider support than Oracle and hence for certain types of relationships that were set to DBMS for DB2, in Oracle the RI rule would need to be set to Gen not DBMS, which the RI Process tool will do for you, or you can set them manually.

[Anon Anon]

Hi Darius.  Yes, I tried that, and it still did not do what we were hoping.  I have narrowed it down to optional/one relationships that are generating the triggers.

Here is the FK DDL that Gen generated for DB2.  We are going to manually set up tests in Oracle to see if it behaves the way we want it to, or if it truly needs the trigger logic in order to keep RI.

 

ALTER TABLE "LOC_HIERARCHY_GROUP" 

     ADD FOREIGN KEY

    "LOC62B"

  ("HRCHY_CATG_CD"                    )

      REFERENCES
"LOC_HIERARCHY_CATEGORY"         

      ON DELETE SET NULL ;

  COMMIT ;

[Lynn Williams]

Hi Cathy,

Darius is correct about the different support of RI for DB2 v Oracle.

 

Your symptoms also rang a bell and I found an old case I worked on in Gen 7.5 where the following behaviour was found which is probably still the case in 8.5:

When a Transformation is done on one DB the constraints in that Data Structure List (DSL) inherit the 'RI Enforced By' property for the Tech. Design which for DB2 or Oracle is 'DBMS'. However in the DSL for other DBs the constraints do not inherit their Tech Design default value & they are given the default value for 'RI Enforced By' which is 'Gen'. If each constraint in the other DSL has it's 'RI Enforced By' property changed to be 'DBMS' then the ALTER statements should then be generated with the DDL.

 

However it you are still not getting the ALTER statements after doing that there could be something Oracle specific going on with those relationships you highlighted which Gen is generating embedded cascade/RI code for instead of allowing Oracle DBMS RI

 

Regards,

 

Lynn

[Hallett German]

Hi Cathy:

Did Lynn's note or is further assistance needed?

 

Thanks

Hal German

[Anon Anon]

Hi.  So we ended up taking the FK DDL that Gen generated for Oracle, threw away the trigger logic it generated, and used the FK DDL syntax that Gen correctly generated for DB2, and ran it in Oracle manually.  We tested inserts and deletes of multiple scenarios and they all worked, so it appears Gen has not kept up with database functionality at least for Oracle.  It would be awesome if Gen could be synched up to generate what Oracle is capable of, but I am not holding my breaeth - we will handle it all manually for now.....

 

Ugghhh... Cathy

[Lynn Williams]

Hi Cathy,

Sorry about the pain

I have recreated this behaviour with a simple model based on your previous updates

 

E1 has optional relationship to E2 (FK_E2ID2)

E1 has mandatory relationship to E3 (FK_E3ID3)

 

DB2

By default the FK_E2ID2 RI Constraint is set to DBMS - generated DDL has FK creation

By default the FK_E3ID3 RI Constraint is set to DBMS - generated DDL has FK creation

 

Oracle

By default the FK_E2ID2 RI Constraint is set to Gen. When change to DBMS the generated DDL contains Trigger code rather than FK creation

By default the FK_E3ID3 RI Constraint is set to DBMS – generated DDL has FK creation

 

I will discuss further with Gen Product Owner JCarter

 

Regards,

 

Lynn