CSV command examples

These examples present complete JCL for converting logs to CSV format.

Where an example shows only a SYSIN data set, the JCL is the same as the preceding example.

Converting all fields to CSV

The following JCL extracts a single CICS® monitoring facility (CMF) performance class record from the dumped SMF data set 'HLQ.SMF.DAILY', converts all of its fields to CSV, and then writes the CSV data to SYSOUT:

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//SMFIN    DD DISP=SHR,DSN=HLQ.SMF.DAILY
//CSV      DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
CSV CODE(CMF) LABELS OUTLIM(1)
/*

The LABELS parameter causes the CSV command to write a header row with field names. By default, the CSV command does not write a header row.

The OUTLIM parameter is useful for testing, to restrict processing to a limited number of output records.

The CSV data is encoded in EBCDIC code page 1047, the default output encoding of the CSV command.

The following JCL produces similar output, but reads today's first CMF record from an SMF log stream, instead of reading the first CMF record in a data set. A LOGSTREAM command at the start of the SYSIN data set replaces the SMFIN DD statement in the previous JCL.

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//CSV      DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
LOGSTREAM SMF:IFASMF.PROD
START 0 /* Today
CMF CODE(CMF) LABELS OUTLIM(1)
/*

The following JCL extracts all DB2® system statistics trace (IFCID 001) records from a dumped SMF data set, converts the data to CSV, and then writes the data to an MVS sequential data set:

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//SMFIN    DD DISP=SHR,DSN=HLQ.SMF.DAILY
//JSON     DD DSN=MY.FUW.CSV,DISP=(NEW,CATLG),  
//            UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE)
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
CSV CODE(DTR:001) LABELS
/*

The previous examples write each CSV row to a separate record in EBCDIC, with no end-of-line markers.

The following JCL writes CSV to a z/OS® UNIX file in EBCDIC, with a newline character (X'0A') at the end of each line. The output DD statement specifies FILEDATA=TEXT:

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//SMFIN    DD DISP=SHR,DSN=HLQ.SMF.DAILY
//CSV      DD PATH='/u/myid/fuw.csv',
//            FILEDATA=TEXT,
//            PATHOPTS=(OWRONLY,OCREAT,OEXCL),
//            PATHDISP=(KEEP,DELETE),
//            PATHMODE=(SIRUSR,SIWUSR,SIRGRP)
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
CSV CODE(DTR:001) LABELS
/*

The following JCL writes CSV to a z/OS UNIX file in ASCII, with a line feed character (X'0A') at the end of each line. The output DD statement specifies FILEDATA=BINARY and the CSV command specifies ASCII EOL(LF):

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//SMFIN    DD DISP=SHR,DSN=HLQ.SMF.DAILY
//CSV      DD PATH='/u/myid/fuw.csv',
//            FILEDATA=BINARY,
//            PATHOPTS=(OWRONLY,OCREAT,OEXCL),
//            PATHDISP=(KEEP,DELETE),
//            PATHMODE=(SIRUSR,SIWUSR,SIRGRP)
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
CSV CODE(DTR:001) LABELS ASCII EOL(LF)
/*

Specifying which fields to extract

By default, the CSV command extracts all fields of the selected record type.

There are several methods for specifying which fields to extract. The most straightforward method is to follow the CSV command with a FIELDS command that specifies a list of field names:

CSV CODE(SMF:30.)
FIELDS(                     
  SMF30JBN
  SMF30SIT
  SMF30STD
  SMF30CPT
)

Selecting which records to extract

To select which records to extract, follow the CSV command with a CODE command and subsequent COND statements that specify filter conditions.

The following example selects SMF type 30 records where both of the following conditions are true:

The record is for a job step termination
The job owner is MYID

CSV CODE(SMF:30.)
FIELDS(                     
  SMF30JBN
  SMF30SIT
  SMF30STD
  SMF30CPT
)
CODE(SMF:30.)              /* Must match CODE parameter of CSV command
  COND SMF30STP EQ 5       /* Record subtype for job step termination
  COND SMF30RUD EQ 'MYID'  /* RACF user ID of the job owner

Tip: Filter conditions can refer to fields that are not extracted.

Creating a CSV file using a form

The following JCL creates a CSV file from a dumped SMF data set, based on a form named SMF30 stored in the control repository MY.FUW.CONTROLS.

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//SMFIN    DD DISP=SHR,DSN=HLQ.SMF.DAILY
//FUWCDS   DD DISP=SHR,DSN=MY.FUW.CONTROLS
//CSV      DD DSN=MY.FUW.CSV,DISP=(NEW,CATLG),  
//            UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE)
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
CSV FORM(SMF30)
/*

To overwrite an existing CSV file, use the following CSV DD statement:

//CSV DD DSN=MY.FUW.CSV,DISP=SHR

To append to an existing CSV file:

//CSV DD DSN=MY.FUW.CSV,DISP=MOD

Creating a CSV file from SMF type 30 records filtered by job name

The following JCL creates a CSV file of SMF type 30 job termination records for job names that begin with UID:

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//SMFIN    DD  DISP=SHR,DSN=SYSA.PROD.SMF(0)
//CSV      DD  DSN=MY.SMF30.CSV,
//             DISP=(NEW,CATLG),
//             UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSPRINT DD  SYSOUT=*
//SYSIN    DD  *
CSV CODE(SMF:30.) LABELS FIELDCASE(ASIS)
FIELDS(
   SMF30JBN:"Job name"
   SMF30CPT:"CPU time"
)
CODE(SMF:30.)
  COND SMF30JBN EQ 'UID*' /* Job name
  COND SMF30STP EQ 5      /* Job termination
/*

Creating CSV files for CICS, DB2, and MQ accounting

The following JCL creates three CSV files from the following log records:

SMF 101: DB2 accounting classes 1, 2, 3, 7, and 9 (IFCID 003)
SMF 110: CICS CMF performance class (transaction accounting)
SMF 115: MQ accounting class 1 or 3

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//SYSPRINT DD  SYSOUT=*
//SMFIN    DD  DISP=SHR,DSN=HLQ.SMF.DAILY
//CSVDB2   DD  DSN=MY.CSV,DB2,
//             DISP=(NEW,CATLG),
//             UNIT=SYSDA,SPACE=(CYL,(1,1))
//CSVCICS  DD  DSN=MY.CSV.CICS,
//             DISP=(NEW,CATLG),
//             UNIT=SYSDA,SPACE=(CYL,(1,1))
//CSVMQ3    DD DSN=MY.CSV.MQ.CLASS3,
//             DISP=(NEW,CATLG),
//             UNIT=SYSDA,SPACE=(CYL,(1,1))
//CSVMQ1    DD DSN=MY.CSV.MQ,CLASS1,
//             DISP=(NEW,CATLG),
//             UNIT=SYSDA,SPACE=(CYL,(1,1))
//LOGRPT   DD  SYSOUT=*
//SYSIN    DD  *
CSV OUTPUT(CSVDB2) CODE(DTR:003) SCHEMA(DB2S) +
    LABELS +
    SECTIONS(HEADER,QWAC,QMDA,QMDAINFO) +
    SECTIONS(QTXA,QTGA,QXST,QBAC,QWHS,QWHC,QWHA) +
    TOKENS(ALL,DB2+)

CSV OUTPUT(CSVCICS) CODE(CMF:6E13) SCHEMA(CICSS) +
    LABELS +
    SECTIONS(HEADER,DFHTASK,IMSDBCTL) +
    TOKENS(ALL)

CSV OUTPUT(CSVMQ3) CODE(SMF:74) SCHEMA(MQ1S) +
    LABELS +
    SECTIONS(HEADER,WTID,WTAS,WQSTAT,QWHS,QWHC) +
    TOKENS(ALL)
CODE(SMF:116.)
COND SM116STF EQ +1  /* Class 3 detailed statistics

CSV OUTPUT(CSVMQ1) CODE(SMF:74) SCHEMA(MQ3S) +
    LABELS +
    SECTIONS(HEADER,QWAC,QMAC,QWHS,QWHC) +
    TOKENS(ALL)
CODE(SMF:116.)
COND SM116STF EQ +0  /* Class 1 brief statistics
/*

Creating a CSV file for IMS accounting from the IMS log

The following JCL creates a CSV file for IMS transactions from the IMS log:

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//SYSPRINT DD  SYSOUT=*
//LOGIN    DD  DISP=SHR,DSN=IMSP.SLDS
//CSVIMS   DD  DSN=MY.IMS.CSV,
//             DISP=(NEW,CATLG),
//             UNIT=SYSDA,SPACE=(CYL,(1,1))
//LOGRPT   DD  SYSOUT=*
//SYSIN    DD  *
IMSVRM=156 /* IMS version 15
IMSINDEX   /* Create IMS transaction index records from the log
CSV OUTPUT(CSVIMS) CODE(IMS:CA01) SCHEMA(S1) +
    LABELS +
    SECTIONS(ALL) TOKENS(IMS)
/*

Extracting DB2 IFCID 001 to CSV and JSON

The DB2 system statistics record (IFCID 001) is an example of a complex record that contains many (unrelated) sections that sometimes repeat.

The following JCL creates four output files:

CSV1: Contains fixed (non-repeating) sections.
CSV2: Contains instances of the repeating section QWSA in separate records.
JSON1: Same as CSV1, but in JSON format.
JSON2: Contains instances of the repeating section QWSA as array elements.

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//SYSPRINT DD  SYSOUT=*
//SMFIN    DD  DISP=SHR,DSN=HLQ.SMF.DAILY
//CSV1     DD  DSN=MY.DTR001.CSV1,
//             DISP=(NEW,CATLG),
//             UNIT=SYSDA,SPACE=(CYL,(1,1))
//CSV2     DD  DSN=MY.DTR001.CSV2,
//             DISP=(NEW,CATLG),
//             UNIT=SYSDA,SPACE=(CYL,(1,1))
//JSON1    DD  DSN=MY.DTR001.JSON1,
//             DISP=(NEW,CATLG),
//             UNIT=SYSDA,SPACE=(CYL,(1,1))
//JSON2    DD  DSN=MY.DTR001.JSON2,
//             DISP=(NEW,CATLG),
//             UNIT=SYSDA,SPACE=(CYL,(1,1))
//LOGRPT   DD  SYSOUT=*
//SYSIN    DD  *
CSV OUTPUT(CSV1) CODE(DTR:001) SCHEMA(S1) +
    LABELS +
    SECTIONS(HEADER,Q9ST,QVLS,QVAS,QSST,QLST,QJST,QDST)

CSV OUTPUT(CSV2) CODE(DTR:001) SCHEMA(S2) +
    LABELS +
    SECTIONS(HEADER) VERTICALSECTION(QWSA)

JSON OUTPUT(JSON1) CODE(DTR:001) SCHEMA(S3) METADATA(M3) +
     SECTIONS(HEADER,Q9ST,QVLS,QVAS,QSST,QLST,QJST,QDST)

JSON OUTPUT(JSON2) CODE(DTR:001) SCHEMA(S4) METADATA(M4) +
     SECTIONS(HEADER) HORIZONTALSECTIONS(QWSA)
/*

Streaming heterogeneous CSV to a TCP socket

The following JCL streams three log record types in CSV format to a TCP socket on a remote system:

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//CSV      DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
LOGSTREAM SMF:IFASMF.PROD
START 0 /* Today
STREAM NAME(STREAM1) HOST(splunk) PORT(6789) +
       TIMEFORMAT(ISO8601) TYPECOLUMN
* CICS performance
CSV CODE(CMF) STREAM(STREAM1) 
FIELDS(
  TRAN, USRCPUT /* More fields...
)
* DB2 accounting
CSV CODE(DTR:003) STREAM(STREAM1)
FIELDS(   
  SM101SID, SM101SSI,
  QWHCCTR, QWHCPLAN, QWHCOPID, QWHCCN,
  ET1, CPU1, ET2, CPU2 /* More fields...
)
* Job termination
CSV CODE(SMF:30.) STREAM(STREAM1)
FIELDS(                     
  SMF30JBN, SMF30CPT,  /* More fields...
)
CODE(SMF:30.)                                    
  COND SMF30STP EQ 5
/*

The remote system must be configured to parse each CSV row based on the value of the type field, which is the second column in each row.

Creating CSV files for viewing in a spreadsheet-like table in the plug-in

To create a CSV file that you can view in a spreadsheet-like table in the Transaction Analysis Workbench plug-in, use a CSV command with the following parameters:

LABELS: Includes a header row of column labels.
OUTPUT(CSVx): The ddname of the CSV file must begin with the characters CSV. For example: CSV, CSV1, CSVXYZ.
METADATA(METAx): The CSV command must create metadata for the CSV. The ddname of the metadata must begin with the characters META, and the remaining characters must match the ddname for the corresponding CSV file. For example, META, META1, METAXYZ.
DELIMITER(,): The field delimiter must be a comma. This is the default value, so you do not need to explicitly specify this parameter.

//UIDFUW   JOB NOTIFY=&SYSUID
//FUWBATCH EXEC PGM=FUWBATCH
//STEPLIB  DD DISP=SHR,DSN=<FUW HLQ>.SFUWLINK
//SMFIN    DD DISP=SHR,DSN=HLQ.SMF.DAILY
//FUWCDS   DD DISP=SHR,DSN=MY.FUW.CONTROLS
//CSV1     DD SYSOUT=*
//META2    DD SYSOUT=*
//CSV2     DD SYSOUT=*
//META2    DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
CSV1 FORM(SMF30) OUTPUT(CSV1) METADATA(META1) LABELS
CSV2 FORM(CMF)   OUTPUT(CSV2) METADATA(META2) LABELS
/*