Move locally transcoded mainframe data to Google Cloud

This page explains how to transcode mainframe data locally on the mainframe to a supported format, and then move the content to Google Cloud.

Before you begin

Install Mainframe Connector to any mainframe partitioned dataset that you want to use as a procedure library (PROCLIB).

The qsam and vsam commands

Transcoding is the process of converting information from one form of coded representation to another. Mainframe Connector provides the qsam and vsam commands to transcode data locally on the mainframe.

The qsam decode and vsam decode commands use a COBOL copybook to convert the records of a mainframe dataset into a file format such as ORC, CSV, JSONL, or AVRO, and write the output locally or to a Cloud Storage bucket. For example, you can convert an EBCDIC-encoded QSAM dataset into UTF-8 ORC files. For all the supported output formats, see TranscodeFormat.

The qsam encode command does the opposite: it converts CSV, JSONL, ORC, or AVRO data, or the results of a BigQuery query, into a QSAM dataset. For all the supported input formats, see TranscodeInputFormat.

These commands provide the following capabilities:

  • Support for compound data types, including the OCCURS clause (lists), REDEFINES clause, and nested records. For more information about these data types, see qsam and vsam transcoding reference.
  • Support for the configuration of the transcoding process through a transcoder configuration file. This capability offers more flexibility when decoding data to Google Cloud, and encoding the data back to the mainframe.
  • Support for the creation of a spillover dataset, which is a table of transcoding errors that can be used for error inspection.
  • Support for multiple input and output formats. This capability lets you load your data to and from various data warehouses.

Transcode mainframe data locally and move it to Google Cloud

Mainframe Connector lets you transcode data locally on the mainframe and then move it to Cloud Storage. Follow these steps:

  1. Create a job to read the dataset on your mainframe and transcode it to the ORC format. Read the data from the INFILE dataset, and the record layout from the COPYBOOK DD.

    You can modify the default behavior of the Mainframe Connector transcoding process by providing a transcoder configuration file using the --transcode-configuration argument.

    • If your input dataset is a Queued Sequential Access Method (QSAM) file with a fixed or variable record length, use the following command:

      //STEP01 EXEC BQSH
//INFILE DD DSN=<HLQ>.DATA.FILENAME,DISP=SHR
//COPYBOOK DD DISP=SHR,DSN=<HLQ>.COPYBOOK.CPY
//CONFIG DD DISP=SHR,DSN=<HLQ>.CONFIG.SETTINGS
//STDIN DD *
BUCKET=BUCKET_NAME
qsam decode --copybook dd:COPYBOOK --transcode-configuration dd:CONFIG dd:INFILE gs://$BUCKET/tablename
/*

    • If your input dataset is a Virtual Storage Access Method (VSAM) file with a fixed or variable record length, use the following command:

      //STEP01 EXEC BQSH
//INFILE DD DSN=<HLQ>.DATA.FILENAME,DISP=SHR
//COPYBOOK DD DISP=SHR,DSN=<HLQ>.COPYBOOK.CPY
//CONFIG DD DISP=SHR,DSN=<HLQ>.CONFIG.SETTINGS
//STDIN DD *
BUCKET=BUCKET_NAME
vsam decode --copybook dd:COPYBOOK --transcode-configuration dd:CONFIG dd:INFILE gs://$BUCKET/tablename
/*

      Replace BUCKET_NAME with the name of the Cloud Storage bucket to which you want to copy mainframe data.

    In this example, the DD DataPath is used to specify the path of the copybook, input, and transcoding configuration. See DataPath for other options.

    To avoid specifying variables such as project IDs and bucket names in each job control language (JCL) procedure, you can add them in the BQSH PROCLIB and reference them across several JCL procedures as environment variables. This approach also simplifies the transition between production and non-production environments, as environment-specific variables are set in the environment's BQSH PROCLIB.

    If you want to log the commands executed during this process, you can enable load statistics.

  2. Create and submit a BigQuery load job that loads the transcoded file to BigQuery. For example, if you transcoded the data to ORC format, you can load the ORC file partitions from tablename.orc into MY_DATASET.MY_TABLE, as follows:

    Example JCL
//STEP02 EXEC BQSH
//STDIN DD *
BUCKET=BUCKET_NAME
PROJECT=PROJECT_NAME
bq load --project_id=$PROJECT \
  myproject:MY_DATASET.MY_TABLE \
  gs://$BUCKET/tablename.orc/*
/*

    Replace the following:

    • BUCKET_NAME: the name of the Cloud Storage bucket which contains the ORC files that you want to load to BigQuery.
    • PROJECT_NAME: the name of the project in which you want to execute the query.

  3. Optional: Create and submit a BigQuery query job that executes a SQL read from the QUERY DD file. Typically the query will be a MERGE or SELECT INTO DML statement that results in transformation of a BigQuery table. Note that Mainframe Connector logs job metrics but doesn't write query results to a file.

    You can query BigQuery in various ways: inline, with a separate dataset using DD, or with a separate dataset using DSN.

    Example JCL
//STEP03 EXEC BQSH
//QUERY DD DSN=<HLQ>.QUERY.FILENAME,DISP=SHR
//STDIN DD *
PROJECT=PROJECT_NAME
LOCATION=LOCATION
bq query --project_id=$PROJECT \
--location=$LOCATION/*
/*

    Replace the following:

    • PROJECT_NAME: the name of the project in which you want to execute the query.
    • LOCATION: the location where the query will be executed. We recommend that you execute the query in a location close to the data.

  4. Optional: Create and submit an export job that executes a SQL read from the QUERY DD file, and exports the resulting dataset to a mainframe as a binary file.

    //STEP04 EXEC BQSH
//OUTFILE DD DSN=<HLQ>.DATA.FILENAME,DISP=SHR
//COPYBOOK DD DISP=SHR,DSN=<HLQ>.COPYBOOK.CPY
//CONFIG DD DISP=SHR,DSN=<HLQ>.CONFIG.SETTINGS
//QUERY DD DSN=<HLQ>.QUERY.FILENAME,DISP=SHR
//STDIN DD *

PROJECT=PROJECT_NAME
qsam encode \
  dd:QUERY
  dd:OUTFILE
  --copybook dd:COPYBOOK
  --transcode-configuration dd:CONFIG
  --input-format=BIGQUERY \
  --input-parameter project_id=PROJECT_NAME \
  --input-parameter location=LOCATION/*
/*

    Replace the following:

    • PROJECT_NAME: the name of the project in which you want to execute the query.
    • LOCATION: the location where the query will be executed. We recommended that you execute the query in a location close to the data.

    You can modify the default behavior of the Mainframe Connector transcoding process by providing a transcoder configuration file using the --transcode-configuration argument.

    The data is exported to the OUTFILE DD dataset. The record layout is described by the COPYBOOK DD. For other options for copybook, outfile, and transcoding configuration paths, see DataPath.