Binary table mappings

IMS examples

Below is part of a DBD definition for an IMS DEDB database that uses variable-length segments. The sample includes a root and child segment both with a 9-byte sequence field and a maximum segment length of 3976 bytes.

DBD   NAME=L3FPDB09,ACCESS=DEDB,RMNAME=DBFHDC40,                 X00020007
   EXIT=((*,LOG,KEY,DATA,PATH,FLD))                              00030014
SEGM    NAME=L1ROOT,PARENT=0,BYTES=(3976,40)                     00240005
FIELD   NAME=(ROOTKEY,SEQ,U),BYTES=9,START=3,TYPE=C              00250005
SEGM    NAME=L2KEYED,PARENT=L1ROOT,BYTES=(3976,40)               00310005
FIELD   NAME=(L2KEY,SEQ,U),BYTES=9,START=3,TYPE=C                00320005

The following example is the sample mapping for the root segment. It includes a single character column for the root sequence field and then the VARBINARY column for the root segments contents.

CREATE TABLE "BINARY"." L3FPDB09_L1ROOT" DBTYPE IMS "L3FPDB09"
	"L1ROOT" 
	SCHEDULEPSB ("PL3FPD09")
(
	"L1ROOT_KEY" SOURCE DEFINITION
		ENTRY "L1ROOT"
		DATAMAP OFFSET 2 LENGTH 9
		DATATYPE C
		USE AS CHAR(9),
	"L1ROOT_DATA" SOURCE DEFINITION
		ENTRY "L1ROOT"
		DATAMAP OFFSET 0 LENGTH 3976
		DATATYPE V
		USE AS VARBINARY(3976),
	PRIMARY KEY ("L1ROOT_KEY"));

The following example is the sample mapping for the child segment. It includes two columns that map the root and child sequence fields and then the VARBINARY column for the child segments contents.

CREATE TABLE "BINARY"." L3FPDB09_L2KEYED" DBTYPE IMS "L3FPDB09"
	"L2KEYED" 
	SCHEDULEPSB ("PL3FPD09")
(
	"L1ROOT_KEY" SOURCE DEFINITION
		ENTRY "L1ROOT"
		DATAMAP OFFSET 2 LENGTH 9
		DATATYPE C
		USE AS CHAR(9),
	"L2CHILD_KEY" SOURCE DEFINITION
		ENTRY "L2KEYED"
		DATAMAP OFFSET 2 LENGTH 9
		DATATYPE C
		USE AS CHAR(9),
	"L2CHILD_DATA" SOURCE DEFINITION
		ENTRY "L2KEYED"
		DATAMAP OFFSET 0 LENGTH 3976
		DATATYPE V
		USE AS VARBINARY(3976),		
	PRIMARY KEY ("L1ROOT_KEY", "L2CHILD_KEY"));

Note: In the examples above the schema, table, and column names are arbitrary.

When changes are captured for these segments, or the data is refreshed, the contents of the L1ROOT_DATA and L2CHILD_DATA columns corresponds to the actual data stored in the IMS segment. An IMS variable length segment starts with a two-byte binary length field followed by the data.

When this data is replicated/refreshed to a relational or Kafka target, the length component is removed so that the length at the target is two less than the actual segment length. If the data is being published to MQ, the setting of the PUBBINASIS parameter determines what is written to MQ. See Format of delimited messages for more details.

VSAM example

Below is part of sample IDCAMS definition for the VSAM employee file distributed with the product. This was extracted from user sample member CACEVSAM and the COBOL copy book that maps this file is in user sample member CACEMPFD.

DEFINE CLUSTER (NAME (SAMPLE.VSAM.EMPLOYEE) - 
                KEYS       (20 0) -               
                TRACKS     (1 1) -                
                RECORDSIZE (80 80) -              
                FREESPACE  (40 40)) -             
   DATA    (NAME (SAMPLE.VSAM.EMPLOYEE.DATA) -

The following example shows a binary table mapping for this file. It includes a column that references the KSDS key and then a single VARBINARY column for the records contents.

Note the use of the USE RECORD LENGTH clause, which is required because the actual length of the record is not included as part of the data as is the case with an IMS variable-length segment. While the IDCAMS definition implies that this file uses fixed-length records because the length is not included as part of the data, it also allows a variable-length mapping to be defined.

CREATE TABLE "BINARY"."EMPLOYEE" DBTYPE VSAM
	DS "SAMPLE.VSAM.EMPLOYEE"
	(
	"ENAME" SOURCE DEFINITION
		DATAMAP OFFSET 0 LENGTH 20
		DATATYPE C
		USE AS CHAR(20),
	"RECORD_DATA" SOURCE DEFINITION
		DATAMAP OFFSET 0 LENGTH 80
		DATATYPE V
		USE AS VARBINARY(80) USE RECORD LENGTH,		
	PRIMARY KEY ("ENAME"));

Note: In the examples above, the schema, table, and column names are arbitrary.

When changes are made to the file, or a refresh is performed, the contents of the RECORD_DATA column are constructed with the 2-byte length component identifying the physical length of the record data followed by actual data.

In this example, the length contains 0x0050 (80 in decimal notation) followed by 80 bytes of data. When this data is replicated/refreshed to a relational or Kafka target, 80 bytes of data are populated at the target. If the data is being published to MQ, the setting of the PUBBINASIS parameter determines what is written to MQ. See Format of delimited messages for more details.

Creating binary table mappings

Creating a table mapping with binary column definitions by using Classic Data Architect (CDA) requires manual intervention. Using CDA is recommended as a starting point because it generates the appropriate DROP and ALTER statements as well as the basic CREATE TABLE syntax.

To create a binary table:

1. Go through the normal process of defining an IMS or VSAM table. Use the mapping wizard to define columns that match the primary key, finish using the wizard, and then switch to the Columns property page to identify these as primary keys.
2. Select the table name, right click and select Generate DDL to bring up the Generate DDL window.
3. Select the desired objects to generate and click Next to advance to the Preview DDL window and then select both the Run DDL on server and Open DDL file for editing check boxes before clicking Next.
4. Select the data source, review the selected information and then click Finish. CDA then connects to the source server and opens an edit window with the generated DDL.
5. Select the lines for the last column before the primary key clause and copy-paste the column before the primary key clause. Give the cloned column a unique name and change the DATAMAP OFFSET to 0 and update the LENGTH as follows:
   • IMS: Set LENGTH to the maximum length of the segment for the leaf segment – this should also be the same name in the ENTRY clause for the cloned column.
   • VSAM: Set LENGTH to the maximum length in the IDCAMS RECORDSIZE clause.
6. Specify DATATYPE B if you are defining a BINARY column or DATATYPE V for a VARBINARY column.
7. Change the USE AS clause as follows:
   • IMS fixed-length segment: Use BINARY(length) – a fixed length segment has a single value coded in the SEGM BYTES clause.
   • IMS variable-length segment: Use VARBINARY(maximum length) – this is the second value in the SEGM BYTES clause.
   • VSAM: Define the column as “VARBINARY(maximum length) USE RECORD LENGTH” – this is the second value on the RECORDSIZE clause in the IDCAMS definition.
8. Right click in the editing area and select Run SQL.
9. Select the source server data source and click Finish to process the DDL statements.