Source table/field metadata from datadictionary for CDC purposes

[David Butler]

We have been running CDC for a number of years and looking to develop our solution further.
One of the requests from the project team is that they wish to obtain the meta data for our table and fields from the datadictionary. We currently provide the table layout by printing out the dataview structures to a dataset for the developers to browse.
However they want something more more parseable (e.g. no pagination headers, field delimiters) most ideally JSON if possible. Can anyone make any suggestions as to what they use?

------------------------------
Snr. Mainframe Database Administrator
AXA Ireland
------------------------------

[Dale Russell]

@David Butler

What attributes of the columns of the table are you interested in capturing?  ENTITY-NAME, SQLNAME, TYPE, LENGTH, what else?

@Dale Russell​​

------------------------------
Dale Russell
Product Owner
CA Datacom Product Family
------------------------------

[David Butler]

Hi Dale,

I have checked with the team requested the data and this is what they are looking for:

Assumptions

1. The full table will be included in the CDC payload
2. The columns within the CDC payload will be in the order of the output
3. The record length will be the full allocation of length/space within the tables columns

Data We require

Data                        Example

---------------------------------------------------------------------------------

Table Name            POL

Database No.         150

Column Name        QUOTE_ID

Starting position     0

Number of characters    20

Data Type                      This can be a code, or sting, we can may this to dictionary internally. We have added complication here with some datatypes, like date formats, decimal precision etc (see notes below).

Notes

The delegates (convert to string, convert to decimal, convert to date etc.) use specific serialised data type, for example, packed decimal executes against the ebcdic array, where-as the policy number uses string, this then needs some way of being identified in the output, it is identified in the metadata by the attribute "ExecuteAgainst".

We have additional attributes we need to add, we'll have to figure out how to add those things as we go (like the "include in checksum", "force as time based insert" etc)

Here is a visualisation of how they were creating the metadata, ideally trying to create the json metadata

See attached note 1. 

And this produces

See attached  note 2.

------------------------------
Snr. Mainframe Database Administrator
AXA Ireland
------------------------------

[Tom Bizal]

I have a couple queries I run with DBSQLPR that return .csv files of column and key information. 

//*
//DBSQLPR1 EXEC PGM=DBSQLPR
// INCLUDE MEMBER=INCMUFT1
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//STDOUT DD DSN=ISTST.COMMADEL.TABLE,
// DISP=(,CATLG,DELETE),
// UNIT=(SYSDA,3),SPACE=(TRK,(150,50),RLSE)
//SYSOUT DD SYSOUT=*
//OPTIONS DD *
AUTHID=SYSUSR
NOPAGEHDR
NOTYPE
NOECHO
SQUISH
DATASEPARATOR=,
PRTFORMAT=WIDE
PRTWIDTH=279
//SYSIN DD *
SELECT FLD.SQLNAME AS COLUMN_NAME,
FLD.TYPE AS COLUMN_TYPE,
FLD.SIGN,
FLD.LENGTH AS COLUMN_LENGTH,
FLD.DECIMALS COLUMN_DECIMALS,
TBL.TABLE_NAME,
TBL.TABLE_SQLNAME,
SQL_AUTHID AS SCHEMA,
DB.OCCURRENCE AS DATABASE_NAME,
DB.DBID,
DB.DIR_NAME,
FLD.SEQNO AS COLUMN_SEQ_NUMBER,
FLD.DISP_IN_TABLE
FROM SYSADM.DIR_TABLE TBL
INNER JOIN SYSADM.DIR_DATABASE DB
ON TBL.DBID = DB.DBID
AND TBL.STATUS = DB.STATUS
AND TBL.DBID = INTEGER(001)
AND TBL.STATUS = 'P'
AND TBL.TABLE_NAME = 'POL'
INNER JOIN SYSADM.FIELD FLD
ON FLD.AGR_SQLNAME = TBL.TABLE_SQLNAME
AND FLD.STATUS = 'P'
AND FLD.ELM_NAME = ' '
AND FLD.SEQNO <> 0
AND FLD.STATUS = TBL.STATUS
AND FLD.STATUS = DB.STATUS
WHERE TBL.DBID = INTEGER(001)
AND TBL.STATUS = 'P'
ORDER BY FLD.SEQNO
;
/*
//*
//DBSQLPR2 EXEC PGM=DBSQLPR
// INCLUDE MEMBER=INCMUFT1
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//STDOUT DD DSN=ISTST.COMMADEL.KEY,
// DISP=(,CATLG,DELETE),
// UNIT=(SYSDA,3),SPACE=(TRK,(150,50),RLSE)
//SYSOUT DD SYSOUT=*
//OPTIONS DD *
AUTHID=SYSUSR
NOPAGEHDR
NOTYPE
NOECHO
SQUISH
DATASEPARATOR=,
PRTFORMAT=WIDE
PRTWIDTH=279
//SYSIN DD *
SELECT FLD.SQLNAME AS COLUMN_NAME,
FLD.TYPE AS COLUMN_TYPE,
FLD.SIGN,
FLD.LENGTH AS COLUMN_LENGTH,
FLD.DECIMALS COLUMN_DECIMALS,
TBL.TABLE_NAME,
TBL.TABLE_SQLNAME,
SQL_AUTHID AS SCHEMA,
DB.OCCURRENCE AS DATABASE_NAME,
DB.DBID,
DB.DIR_NAME,
KEY.KEY_NAME,
KEY.KEY_FIELD_SEQ
FROM SYSADM.DIR_TABLE TBL
INNER JOIN SYSADM.DIR_DATABASE DB
ON TBL.DBID = DB.DBID
AND TBL.STATUS = DB.STATUS
AND TBL.DBID = INTEGER(001)
AND TBL.STATUS = 'P'
AND TBL.TABLE_NAME = 'POL'
INNER JOIN SYSADM.FIELD FLD
ON FLD.AGR_SQLNAME = TBL.TABLE_SQLNAME
AND FLD.STATUS = 'P'
AND FLD.ELM_NAME = ' '
AND FLD.SEQNO <> 0
AND FLD.STATUS = TBL.STATUS
AND FLD.STATUS = DB.STATUS
INNER JOIN SYSADM.DIR_KEY_FIELD KEY
ON KEY.TABLE_NAME = TBL.TABLE_NAME
AND KEY.DBID = TBL.DBID
AND KEY.OCCURRENCE = FLD.ENTITY_NAME
AND KEY.STATUS = 'P'
WHERE TBL.DBID = INTEGER(001)
AND TBL.STATUS = 'P'
ORDER BY DB.DBID, TBL.TABLE_NAME, KEY.KEY_NAME, KEY.KEY_FIELD_SEQ
;
/*
//*

These are the resulting files:

COLUMN_NAME,COLUMN_TYPE,SIGN,COLUMN_LENGTH,COLUMN_DECIMALS,TABLE_NAME,TABLE_SQLNAME,SCHEMA,DATABASE_NAME,DBID,DIR_NAME,COLUMN_SEQ_NUMBER,DISP_IN_TABLE
PO,N,N,5,0,POL,DEMO_DEM_POL,SYSUSR,HUMAN-RESOURCE,1,TESTCXX,1,0
LI,N,N,3,0,POL,DEMO_DEM_POL,SYSUSR,HUMAN-RESOURCE,1,TESTCXX,2,5
PN,C,N,3,0,POL,DEMO_DEM_POL,SYSUSR,HUMAN-RESOURCE,1,TESTCXX,3,8
QTY,N,N,3,0,POL,DEMO_DEM_POL,SYSUSR,HUMAN-RESOURCE,1,TESTCXX,4,11
UCOST,N,N,5,2,POL,DEMO_DEM_POL,SYSUSR,HUMAN-RESOURCE,1,TESTCXX,5,14

COLUMN_NAME,COLUMN_TYPE,SIGN,COLUMN_LENGTH,COLUMN_DECIMALS,TABLE_NAME,TABLE_SQLNAME,SCHEMA,DATABASE_NAME,DBID,DIR_NAME,COLUMN_SEQ_NUMBER,DISP_IN_TABLE
PO,N,N,5,0,POL,DEMO_DEM_POL,SYSUSR,HUMAN-RESOURCE,1,TESTCXX,1,0
LI,N,N,3,0,POL,DEMO_DEM_POL,SYSUSR,HUMAN-RESOURCE,1,TESTCXX,2,5
PN,C,N,3,0,POL,DEMO_DEM_POL,SYSUSR,HUMAN-RESOURCE,1,TESTCXX,3,8
QTY,N,N,3,0,POL,DEMO_DEM_POL,SYSUSR,HUMAN-RESOURCE,1,TESTCXX,4,11
UCOST,N,N,5,2,POL,DEMO_DEM_POL,SYSUSR,HUMAN-RESOURCE,1,TESTCXX,5,14

[David Butler]

Hi Tom,

That's quite interesting, thanks for the queries. I will pass to the team to see does that give enough of the data they need.

Regards,
David

------------------------------
Snr. Mainframe Database Administrator
AXA Ireland
------------------------------