Transforming historical data (PTF BQY2138 applied)

Use the Data Transformation utility, BBM9MD75, to unpack and filter the record entries (REs) in the historical data extracted by BBM9MD73 (History Data Extraction utility). BBM9MD75 also prepares selected subsets of the historical data by transforming the format of each field to a format that is easily ingested by off-mainframe data analytics tools, such as Excel or Splunk. The internal binary data in REs is transformed into comma-separated values (CSV) format. Additionally, instead of specifying field offsets and lengths using the SORT utility you can specify row and column filtering in SQL select-like statements by field names, so only the desired data is extracted and formatted. This reduces the time to transfer the data off-mainframe and reduces the cost of data ingestion for tools that charge by the number of bytes of data read. 

The SORT utility provides the best performance for data filtering. As such, for large historical data extracts, you may want to first filter records using the SORT utility and then pass the reduced historical data extract as input for the data transformation utility for final filtering and data transformation.

You can run the BBM9MD75 utility as a batch utility. You can insert it as a job step immediately after BBM9MD73 and/or the system SORT utility (if the RE fields have not been rearranged), or in a separate standalone job after you run BBM9MD73.

Prerequisites

BBM9MD75 runs as a compiled REXX load module. The IBM Compiler and Library for REXX on IBM Z product is required to run the utility. BMC Recmmends that you install the REXX library that accompanies the compiler in the Link Pack Area.

If you do not have the REXX compiler and library installed, you can run the BBM9MD75 utility with the REXX Alternate Library which is packaged with z/OS. If you use the Alternate Library the utility will run in interpreted mode, which runs less efficiently than running in compiled mode. IBM recommends that you install the Alternate Library in the LINKLIST.

Refer to the z/OS REXX Compiler and Library and the z/OS REXX Alternate Library documentation for further details.

• z/OS REXX Compiler and Library documentation
• z/OS REXX Alternate Library documentation

Sample JCL for running BBM9MD75

The following is a sample JCL for running the BBM9MD75 utility:

//DTRANS01 JOB ...
//DUMPXTRT EXEC PGM=IKJEFT01,REGION=0M,PARM=’BBM9MD75’
//STEPLIB   DD  DSN=prefix..BBLINKLIB,DISP=SHR     
//MD73IN    DD  DSN=BMC.SYSA.MVMVS.HISTDSN(+0),DISP=SHR
//          DD  DSN=BMC.SYSA.MVMVS.HISTDSN(+1),DISP=SHR
//BBACTDEF  DD  DSN=prefix..BBACTDEF,DISP=SHR
//BBSAMP    DD  DSN=prefix..BBSAMP,DISP=SHR
//BBMAP     DD  DSN=prefix..BBMAP,DISP=SHR 
//SYSTSPRT  DD  SYSOUT=*                           
//SYSTSIN   DD  DUMMY
//SYSIN     DD  *
*
* SELECT 1:  Address Space Record Map             (ASRE)
*
SELECT ASREASID,ASRENAME,ASREJBID,ASREYFL,
       ASRSLAP,ASRSDUR,ASRSTCB,ASRSSRB,ASRSFMC,ASRSWSS,
       ASRILAP,ASRIDUR,ASRITCB,ASRISRB,ASRIFMC,ASRIWSS
FROM   BBDTDM.BBMUCE01
WHERE  (ASREYFL=’01’X | ASREYFL=’02’X) 
       & LCLTIME > “20190101000101.00”;
*
*  SELECT 2:  Mounted File System Record Map    (FMRE)
*
SELECT *
FROM   BBUTDM.BBUUCEC2;
//OUT1      DD  DSN=prefix..MVMVS.CE01,DISP=(,CATLG),
//   SPACE=(CYL,(500,100),RLSE),UNIT=SYSDA,                      
//   DCB=(LRECL=32000,BLKSIZE=32760,RECFM=VB,DSORG=PS)                                                
//OUT2      DD  DSN=prefix..MVMVS.CEC2,DISP=(,CATLG),
//   SPACE=(CYL,(500,100),RLSE),UNIT=SYSDA,                      
//   DCB=(LRECL=32000,BLKSIZE=32760,RECFM=VB,DSORG=PS)                                                
//                                                 

In the sample JCL, you extract and transform two RE types from a data set extracted from a PAS in data set BMC.SYSA.MVMVS.HISTDSN(+0).

You specify two SELECT statements in the SYSIN input stream:

• The first SELECT specifies a set of fields from the RE described by the TDM member BBACTDEF(BBDTDM), and the data map in BBSAMP(BBMUCE01) or BBMAP(BBMUCE01), for the Address Space Record Map.
• The second SELECT statement specifies all the fields for the Mounted File System Record Map, BBMUTDM. The BBUUCEC2 RE, is transformed for all rows in the input data set.
• Each SELECT statement writes output to DDNAMEs OUT1 and OUT2.

BBM9MD75 DDNAMEs 

The following table describes all of the input/output DDNAMEs for the BBM9MD75 utility:

Condition Codes

BBM9MD75 sets the following condition codes so you can test the outcome of the data transformation and, if necessary, take appropriate action:

SELECT statements

The SYSIN input stream contains one or more SQL-like SELECT statements, describing which columns and rows to filter from for a specific MainView product record. Any input lines that begin with an asterisk (*) in column one are ignored as a comment lines.

The field names included in the output for each SELECT statement are listed immediately after the SELECT keyword. The special value of asterisk (*) indicates to include all fields defined in the history record data map, in the order they are listed in the associated data map for the RE type.

You specify the target RE type in the FROM clause, which names the member in the BBACTDEF library that contains the MainView product TDM, and the library member name that contains the record data map.

Use the optional WHERE clause to specify the criteria under which the RE rows are selected. You can compare any valid REXX expression referencing field names in the specified data map in the FROM clause to any literal value.

You can include the special field names LCLTIME, UTCTIME, ORECRTIN, ORECOTYP, ORECISID, and ORECSYS in the expression. These special fields contain the following values originating from the history record OREC header:

All SELECT statements must end with a semi-colon (;) or the end of the input stream. If you use a semi-colon, it must be the last character on the input line. 

SELECT statement syntax diagram

Any relational expression that REXX evaluates as a zero or one value is accepted by the WHERE clause, including functions such as SUBSTR.

Example of literals

CSV output format

The utility writes output for each SELECT statement to the respective OUTn where n is the SELECT statement number. Each record written to the OUTn DD contains columns and rows in display code format.

The first row in each output data set contains the names of the columns for the rows to follow. The second and subsequent rows contain the common fields from the OREC record header, followed by the requested columns from the REs that you specified in the FROM clause. Each row contains fields from a single RE, even if the original history extract data set was packed, and the OREC header information is repeated for each row associated with a logical record collected during an interval.

All values are separated by commas. Character fields enclosed in double quotation marks (“…”).

The common fields that precede all RE fields are listed and described in the following table:

The fields you specified in the SELECT statement follow the common fields listed in the previous table. Each field is either a numeric value or a character value surrounded by quotation marks. Fields are separated by commas. 

The following table describes how field types used in data maps are translated to the output CSV format: