Problem

 When a method is created for tables with a referential  integrity (RI) Data Express ensures that the relationships  between the tables stay consistent.  

This  article contains  explanations of how Data Express 4.0 handles tables with Referential Integrity  in z/OS  and how the parent and child tables  are ordered in the steps of the method. 

 Resolution 

 We assume: 

    -  The referential integrity classes are imported by running the BURDDUR (Import Classification From Referential Integrity) job.

   -  The  methods are created  by running   the  BTEDDUD (Import Method  from Referential  Integrity) job.   The class  passed as a parameter to this  job   is  called   ‘’Initial’’ or  ‘’starting” class.  

    These   jobs  can be started from  the  “Work with job” menu of Data Express Data Builder.   

Example I: 

In general, the parent and the child tables  are not in the same step.  Normally the class used as a “link” between parent and child tables appears in different steps. 

This example  shows the case when  the “link” class is output for the parent table and input for the child table.   This means that the parent table is located  in a lower step than the child table.  

Four tables are included in this example.   

CREATE TABLE DELGT.CUSTOMER

( COD_CUS                CHAR(10) NOT NULL WITH DEFAULT,

  NAME                   CHAR(40) NOT NULL WITH DEFAULT,

  SURNAME                CHAR(40) NOT NULL WITH DEFAULT,

  DATE_OF_BIRTH          CHAR(08) ,

  PLACE_OF_BIRTH         CHAR(50) NOT NULL WITH DEFAULT,

  ADDRESS                CHAR(50) NOT NULL WITH DEFAULT)

IN DBURSVIL.DETESTLG;

 CREATE TABLE DELGT.ACCOUNT

(COD_CUS                CHAR(10)            NOT NULL WITH DEFAULT,

OFF_NUM                DECIMAL(5)          NOT NULL WITH DEFAULT,

ACC_NUM                DECIMAL(10)         NOT NULL WITH DEFAULT,

AMOUNT                 DECIMAL(12)         NOT NULL WITH DEFAULT)

IN DBURSVIL.DETESTLG;

CREATE TABLE DELGT.CCARD,

(OFF_NUM                DECIMAL(5)          NOT NULL WITH DEFAULT,

ACC_NUM                DECIMAL(10)         NOT NULL WITH DEFAULT,

CARD_TYPE              CHAR(1)             NOT NULL WITH DEFAULT,

CARD_NUM               CHAR(16)            NOT NULL WITH DEFAULT,

AVAILABILITY           DECIMAL(25)         NOT NULL WITH DEFAULT,

EXP_DATE               CHAR(8)             NOT NULL WITH DEFAULT)

IN DBURSVIL.DETESTLG;

CREATE TABLE DELGT.OPERAT,

(CARD_TYPE              CHAR(1)            NOT NULL WITH DEFAULT,

CARD_NUM               CHAR(16)            NOT NULL WITH DEFAULT,

ACCOUNT_OPER           DECIMAL(25)         NOT NULL WITH DEFAULT,

DATE_OPRT              CHAR(8)             NOT NULL WITH DEFAULT)

IN DBURSVIL.DETESTLG;

The following combined fields are specified  in the tables: 

   1.  FLD000000005-FIL000000004  includes  the columns OFF_NUM ACC_NUM  in the table  ACCOUNT.   

  2.  FLD000000007-FIL000000005  includes  the columns OFF_NUM ACC_NUM  in the table  CCARD.   

  3.  FLD000000008-FIL000000005  includes the columns CARD_TYPE CARD_NUM in the table CCARD. 

  4.  FLD000000005-FIL000000006 includes the columns CARD_TYPE CARD_NUM in the table OPERAT. 

The referential model, expressed in the format used by Data Express, is the following one:

 NRT40OW;CUSTOMER;COD_CUS;NRT40OW;ACCOUNT;COD_CUS;;;;

NRT40OW;ACCOUNT;OFF_NUM;NRT40OW;CCARD;OFF_NUM;;;;

NRT40OW;ACCOUNT;ACC_NUM;NRT40OW;CCARD;ACC_NUM;;;;

NRT40OW;CCARD;CARD_TYPE;NRT40OW;OPERAT;CARD_TYPE;;;;

NRT40OW;CCARD;CARD_NUM;NRT40OW;OPERAT;CARD_NUM;;;;

Regarding the logical order of the tables  in the RI file: 

A)     From the class definition point of view (JOB BURDDUR) there are no problems with the order of the relationships  defined into the file.  You are free to insert the tables into the RI file  without a logical order.

B)      From the method definition point of view  (JOB BTEDDUR) the sequence of relationship is essential, i.e. parents must be first.  In fact during the method creation the first class selected as initial class must be the class of the parent table and starting from this class  the method should  include all the other child tables and related tables in the correct order.   

The column COD_CUS (customer number) links the table CUSTOMER with the table ACCOUNT, the  two columns relationship OFF_NUM and ACC_NUM  link the table ACCOUNT  with the table CCARD,  the other two columns relationship CARD_TYPE and CARD_NUM link  the table CCART  with the table  OPERAT. 

The starting  class  CLA0478 passed as a parameter to  the BTEDDUD  job is the one associated to the column  COD_CUS.  

According to the created method  Data Express starts reducing  the CUSTOMER and ACCOUNT tables according to a list of customer code,  then reduces the table CCARD with the value of the key (ACC_NUM OFF_NUM) related to the ACCOUNT table  and then the same process with the table OPERAT related to the table CCARD thru the relationship of the other two columns(CARD_TYPE CARD_NUM). 

The starting  class here  CLA0478  appears as input one, not as  output class.    

The other two classes  CLA0481 and CLA0484   describes  the relationships that appear as input for some tables and output for other ones. And this happens at different steps (where the input step is greater than the output one).  

In the example the class CLA0484 is input for step 30 and output for step 20. The output class for the table in step 20 is used as an input class for the table in the step 30. This means that the output filter produced in the step 20 will be used as an input filter for the table in step 30.    

Note that not all tables have an output filter, only the ones being parent tables in a relationship.  The  child tables  appear  after the parent  tables.   

The  case where all elaboration with the same class are in the same step and there are no problems because, whatever is the order, no one of the tables affects any other table.   The starting class of the model  is CLA0478  (the example at the beginning) and  it is generated as input class for the table, not as output class.  

This example can be presented in the following way:

Table A -> Table B

Table B -> Table C

Table C -> Table D

Supposing the initial class is the one involved in A->B relationship the following method  will be created:  

Step 20   Table A,  Table B

Step 30   Table C

Step 40   Table D

Output class for Table B is input class for Table C.

Output class for Table C is input class for Table D.  

As you can see table A is  a parent for Table B, but they are in the same step because we choose as initial class the one involved in A->B relationship.   

It is possible to move a data store from one step  to  a new one.  To do so, open the method and right click on the data store you want to move to another step.  From the dropdown menu select  ‘’Move Selected to’’ and  choose the other step.          

Example II:

Here is a very simple  example  when  the parent and the child   tables are always in one step.   

 Assume  we have table  groups  with  different  relations  loaded in one  MachineID/Company.   

Group 1

TABLE A FIELD A -> TABLE B  FIELD B 

 Group2 

TABLE C FIELD C -> TABLE D  FIELD D    

The job BURDDUR will assign:

CLA1 to the TABLE A/FIELD A and TABLE B/FIELD B  and

CLA2 to the TABLE C/FIELD C and TABLE D/FIELD D 

The job BTEDDUR  will generate a method starting from the “class”  you will choose!  If you choose the class CLA1, the tables A and B will be in a step  10 and the tables C and D will be in the excluded files, since they are not connected to the first group tables. If you choose the class CLA2, the tables C and D  will be in a  step  10 and the tables A and B  will be in the excluded files, since they are not connected to the second  group of tables.

Example III: 

Let’s consider now the case of “recursive” relationships.   If you have recursive relationships, you can get “loops”.

 Data Express  does not handle loops and so it “cuts” relationships leading to a point of the chain already explored.

Example:

 If you have:

 Table A -> Table B

Table B -> Table C

TABLE C -> Table D

Table D -> Table E

Table E -> TABLE  C

The Table E which is parent of Table C is ignored because it leads to a “loop” and so it is not handled.  The generated method will be as below:    

 Step 10   Table A, Table B

Step 20   TABLE  C

Step 30   Table D

Step 40   Table E

Output class for Table B is input class for TABLE C.

Output class for Table C is input class for Table D.

Output class for Table D is input class for Table E.