Introduction
As part of the mainframe modernization movement, many organizations are putting their legacy non-relational data into Db2, so it is under relational database control. Building a relational data model for this data can be very complex and time consuming. It requires a lot of data modelling to normalize the data, plus it also requires developing programs to convert the data from record oriented (like sequential or VSAM) to relational. So, rather than spending the time to normalize the record-oriented data into a data model containing many tables and hundreds of columns, some companies chose a shortcut approach of creating a single DB2 table for each dataset with an identifier column for use as a search key, and then a long varchar column in which to insert the complete sequential or VSAM record as-is. The conversion program needed to load the data into a single column is much simpler, making the overall project a fraction of the size it would be if they did full relational conversion.
Using this method requires the creation of new programs that parse through the single column of data in order to make it usable.
This is where a new feature in DVM comes in. It allows you to run SQL queries against the single Db2 column containing multiple data fields as if it had been fully normalized into a relational table. It does this by mapping out the data structures in a single column via a COBOL copybook containing the desired column layout.
Let's see how this works.
Getting Started
Our existing Db2 table has the following definition:
QA_STAFF_RECORD
S_ID SMALLINT
DATA_RECORD CHAR(28)
LAST_UPDATE TIMESTAMP
Where the DATA_RECORD column contains all of the data within the VSAM file. Notice that the S_ID column is a redundant field. S_ID is a unique value that is also contained within the DATA_RECORD but would otherwise be inaccessible to a SQL statement without first parsing the DATA_RECORD field. It is for SQL query optimization only.
This is the COBOL record layout for the DATA_RECORD column:
- S_ID and S_ DEPT are defined as binary computational data type
- S_NAME and S_JOB is defined as character data type
- S_SALARY and S_COMMISSION are defined as decimal data type
If you ran a simple SELECT against this table directly, the data displayed in the DATA_RECORD column would be a combination of binary and non-binary data, and not of much use.
Using the DVM Studio to virtualize the record structure
Data Virtualization Manager for z/OS enables the transformation of these binary, decimal, and character fields into individual columns in a virtual table.
- From DVM Studio, create a virtual table for the existing QA_STAFF_RECORD Db2 table:
Next, proceed to the ADMIN section in the studio. Here, we create what is known as a COBOL metadata layout, which will apply the copybook format to the single Db2 column.
- Right-click on COBOL and select “Create Metadata”:
- Assign a table name for the Copybook
- Select the Copybook from the source file:
Even though the COBOL metadata record is now defined in the DVM server, it is not yet a query-able data object because it is not linked to any data. It must be mapped to an existing virtual table.
- Right-click on the DATA_RECORD column in your virtual table and select “Link Db2 Column to Metadata Map”
- Select the Metadata Map you just created:
- With the data source linked and the data mapped, virtual table DB2_DBA9_STAFF_COBOL now exists based on the source data in a ready-to-use format for the DATA_RECORD column in the base Db2 table.
SQL queries can now be run against the individual data fields stored in the DATA_RECORD column via the virtual table as if it were a fully normalized multi-column table.
You can use the “Generate Query” option in the DVM Studio drop-down menu for the newly defined virtual table to see the transformed results.
Summary
DVM for z/OS simplifies the task of accessing multiple data fields stored in a single Db2 column without having to do any complex parsing code.
#Data Server Manager
#Db2 Analytics Accelerator for z/OS
#Db2Toolsforz/OS#DVMz