Null values

[Anon Anon]

I have an entity defined with some optional attributes and their corresponding data structure columns show them as null which is what they are in DB2 as well.  I would have expected that when I CREATEd this entity and didn't set those attributes in the CREATE then the row would be inserted with those values as NULL.  But instead I'm seeing space or zero depending on the datatype.  Is there some setting that would enable me to have nulls in those columns?

Thanks

[Rafa.Cola_at_Facet]

Hi Sumithar,

Yes there is a place to set this. It sits inside View Maintenance.

Use the Toolset help and look for NULL. First item shown on my search sufficed: "Using Null Statement". You can also try just copying and pasting this "mk:@MSITStore:C:\Program%20Files%20(x86)\CA\CA%20Gen%20r8.0\gen\TOOLSET.CHM::/Null_Properties.htm" without the double quotes into an IE browser or into a Windows Explorer.

A quick read tells me you may need to set up equivalent local nullable attributes and then set the entity view ones to those but I may be wrong.

 

Rafa.

[Rafa.Cola_at_Facet]

Adding to my previous comments:

1- You can set the attribute directly to NULL in a CREATE statement.

2- You can set the option "Initialize Unspecified Optional Fields to Null" in Technical Design Properties dialog but this will affect the whole Database.

(file:///C:/Program%20Files%20(x86)/CA/CA%20Gen%20r8.5/Gen/HELP/8011.htm)

[Anon Anon]

Thanks Rafa.  Before generating code on my PC I can check the option "Initialize Unspecified Optional Fields to Null" and make Gen generate a statement to explicitly set the attribute to NULL.  Is there a counterpart on the Host Encyclopedia?

[Rafa.Cola_at_Facet]

I don't think there is such option in HE. I have searched the "Host Encyclopedia Construction User Guide" and could find nothing.

You will have to set this option using the Toolset.

[Lynn Williams]

Hi Sumithar,

Further to Rafa's reply, just to confirm that after setting the "Initialize Unspecified Optional Fields to Null" option on the Toolset if you then check-in that model change, the Host Encyclopedia Construction will also then use it.

[Anon Anon]

Lynn,

I changed the setting on the PC and checked the subset back in but when I generate code on the mainframe it still has a host variable in the INSERT statement whose value is being set to SPACES before the INSERT.  (on the workstation however once that box is checked the INSERT explicitly has NULL corresponding to that column)

Thanks!

[Lynn Williams]

Hi Sumithar,

I think you have missed something.

I just verified this behaviour works as expected in a simple Gen 8.5 model with entity E1 (optional and mandatory strings) & Procedure Step PSTEP1 that uses it i.e.

• I set the "DB2 z/OS" Technical Design to have "Initialize Unspecified Optional Fields to Null" enabled
• On the toolset I generated MVS/COBOL using that same DBMS(TD)
• I uploaded the model to the Host and generated the same there
• In both cases the code contains this comment at the top "Init unspecified optional fields to NULL selected" & I see this in the code for the CREATE statement

 

          EXEC SQL INSERT INTO "E1" (

                  "STRING1_OPTIONAL",

                  "STRING2_MANDATORY",

                  "NUM1"

            ) VALUES (

                  NULL,

                  :STRING2-MAND-004EN,

                  :NUM1-006EN

            )

 

Even if you are using an older Gen version I would have expected this to have always worked.

I have attached my test model with the generated code for toolset and Host in the cobol sub-directory

 

Hope this helps.

 

Regards,

 

Lynn

[Anon Anon]

Hi Lynn,

Thanks for sticking with me.  I found out that I had to explicitly scope the Tech Design Default object into my subset with protection of 'M' for me to be able to change that option and have it "stick" when I check my subset back in.  Only then does the code generation on the mainframe set those columns to be NULL in the INSERT statement.

 

For implementation of our code we migrate from our Dev Model into a QA model and then code is generated from the QA model, compiled and linked to create a load module that is tested in the QA environment (and then promoted to Prod).  Usually we don't subset from QA model.

Should I now include this Tech Design Default as an element to be migrated?

 

Thanks!

[Lynn Williams]

Hi Sumithar,

Glad to hear that you resolved the problem

> Should I now include this Tech Design Default as an element to be migrated?

Yes and  for version control "TECH DESIGN DEFAULT" is also a selectable (aggregate) object.

See the HE Version Control User Guide for further details.

 

Regards,

 

Lynn

[Rafa.Cola_at_Facet]

Hi Sumithar,

when you talk about migrating to QA and to Production I understand you have an existing system which worked previously with optional attributes being stored as SPACES/ZEROES.

If you are talking about an existing CA Gen system, you should be aware of possible changes of behaviour in the whole existing system once the migrated change will affect the whole database not just that one table. For instance, pre-existing code such as "IF optional_attribute IS SPACES" and "IF optional_attribute IS ZEROES" may no longer work as previously for NULL is different from ZEROES/SPACES. Working or not will depend on how the CREATE of such attributes was made as we have discussed in this thread.

In addition, if you have other DBMSs other than DB2, you will need to change and migrate for those as well to keep them synchronized.

[Rafa.Cola_at_Facet]

Lynn,

I just confirmed behaviour described by Sumithar in the Toolset for C-MS/SQL and COBOL-DB2 z/OS using sample.ief's ADD_EMPLOYEE action block.

After setting the Initialize Unspecified Optional Fields to Null for each DBMS, inside CREATE new employee statement, I have deleted the SET address_line_2 TO import employee address_line_2 statement and generated the code.

Once there was no setting of the address_line_2 attribute, the code generated a INSERT NULL in that column.

 

Sumithar, would you mind double checking you are not setting the attribute inside the CREATE statement?

[Lynn Williams]

Thanks Rafa.

Yes that is correct.

Any SET statement for an optional attribute in the CREATE statement will override the "Initialize Unspecified Optional Fields to Null" option because the user is explicitly specifying that attribute.