Gen EDGE

We are in the midst of trying to switch our Gen code from CICS/COBOL/DB2 to Unix/Java/Oracle.  I have been fairly successful in switching the TD, but have hit a roadblock.

In DB2, the columns in Data Structure List have this option:

In Oracle, even though I set "Enforce Default Values within DBMS" in TD properties, that column option does not appear for Oracle:

I have searched, and cannot find anywhere in Gen where DBMS default values are even set.  We can fiddle with the Oracle DDL, but as long as Gen thinks the columns are NOT NULL instead of NOT NULL WITH DEFAULT, then all of our Create statements won't generate in Java, as not all creates have every attribute accounted for - we let the default DBMS values fill in the rest.

Hi Cathy,

I had not come across this Oracle limitation in Gen before so I checked it with our Sustaining Engineering team

Currently we only support the addition of “WITH DEFAULT” in DDL generated for DB2 so that is why "DBMS Datatype Default Value" option is not visible for Oracle.

Using that option for DB2 would have allowed you to not have to set mandatory attributes in CREATE statements.

To workaround the limitation for Oracle you should be able to do one of the following:

1. Set those attributes to be optional instead of mandatory so that Gen will put default data into the fields when not specified in CREATE statements.
2. As you indicated fiddle/manipulate the DDL generated for Oracle to add the required WITH DEFAULT clauses. However by doing this you said your CREATE statements for Java/Oracle still fail to generate. Is that due to Consistency Check Errors or Warnings e.g. WARNING : ICCEC03W A CREATE statement should SET or default a value for each mandatory attribute?

Thanks

Lynn

Hi Lynn,

Well......  we have complete systems already built in Gen (14000+ progarms) and have been running in DB2 for years.  The big "selling point" of keeping Gen around at all at our site (management has been wanting get rid of Gen for along time now) is that we could easily take all of our existing Gen TD and code and switch targets from mainframe to distributed without too much difficulty.  This is proving to be very untrue for us.  We have about 870 tables, and the DB2 DBA standard here is to make all attributes mandatory, so I would have to go in and manually switch thousands of attributes to optional from mandatory in order to switch to Oracle.  How difficult is it for your Sustaining Engineering team to add Oracle to the list of DDL that allows "WITH DEFAULT"?  It is not a limitation or Oracle itself; it is a currrent limitation within CA Gen.

The options are all pretty ugly from my viewpoint: 

1) Setting existing attributes from Mandatory to Optional, though this is looking like it may currently be our only option.

2) Adding SET statements to all of the existing CREATEs for missing attributes

3) Having CA fix Gen to allow "WITH DEFAULT" for Oracle.  This option would also be manually intensive, unless we can preserve what is currently set for DB2 when we switch to Oracle TD.  If we could preserve what we have set, that would be a huge win for us.

Thanks for any help you can provide.

Hi Cathy,

Thanks for the added info & I appreciate the impact for you. I will discuss further with Sustaining Engineering & John Carter

Regards,

Lynn

I think that Oracle does not support WITH DEFAULT (at least I could not find it in the Oracle document I looked at: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm.  Instead, it supports a DEFAULT clause with a value specified for it.  So rather than changing all of the attributes to optional, add default values to the attributes that are actually optional and set the DBMS Default Value Enforcement value for the corresponding columns to Attribute Default Value.  This will generate DEFAULT followed by the default value in the DDL.

While the above is a better long term solution, depending on how many attributes you know to be really optional, or if you don't know which attributes are treated as optional, this could also be labor intensive.  A short term solution may be to create a post-processor to add the appropriate DEFAULT clause (depending on the column datatype) to the DDL after it is generated.