VSAM on z/OS Private Preview

The Fivetran VSAM on z/OS connector replicates data from VSAM (Virtual Storage Access Method) to your destination.

VSAM is an IBM z/OS data set type and access method used to store and access records in data sets. We read VSAM files through the VSAM Bridge, a Fivetran-provided component installed on your z/OS system. For each VSAM file, we use the matching COBOL copybook to interpret the record structure.

Supported configurations

Fivetran supports replicating the following VSAM data set types:

Setup guide

Follow our step-by-step VSAM on z/OS setup guide for instructions on installing the VSAM Bridge and setting up your VSAM connection.

Sync overview

During the initial historical sync and each manually triggered re-sync, we read the full contents of each selected VSAM data set through the VSAM Bridge and use the matching COBOL copybook to decode each record. To replicate later source changes, trigger a re-sync.

Schema information

Fivetran derives your destination schema entirely from the COBOL copybook that describes each VSAM file's record layout. For every VSAM file you select, we create a table in your destination. This section explains how we convert the copybook layout into tables, columns, and rows.

Tables and columns

We create one destination table per copybook, named after the first level-01 data item in the copybook. If a copybook defines more than one level-01 item, we use only the first one.

During connection setup, you choose between Fivetran naming and Source naming for your destination schema, table, and column names. Fivetran naming converts column names to lowercase. Source naming preserves the names we derive from the copybook.

We build column names from the nested field names in the copybook, joined with a double underscore (__), and replace hyphens with underscores. With Fivetran naming, a field named COL-B nested under a group named GROUP-A becomes the destination column group_a__col_b.

We exclude the following items from your destination table:

• FILLER and other unnamed fields.
• Fields defined with RENAMES (level 66).
• Level 77 and level 88 items.

Fivetran-generated columns

We add the following columns to every table in your destination:

• _fivetran_deleted (BOOLEAN) marks rows that were deleted in the source.
• _fivetran_synced (INSTANT) indicates the time when Fivetran last successfully synced the row.

If we can't identify a primary key for a table, we also add a _fivetran_id (STRING) column and use it as the primary key.

We add these columns to give you insight into the state of your data and the progress of your data syncs. For more information about these columns, see our System Columns and Tables documentation.

Type transformations and mapping

As we decode your data, we match COBOL data types to types that Fivetran supports. If we don't support a data type or usage clause in a copybook, we exclude that column from syncing. The column appears in the Schema tab as excluded by system.

The following table illustrates how we transform your COBOL data types into Fivetran supported types:

For more information about how Fivetran handles data types, see our Data types documentation. If we are missing an important type that you need, reach out to support.

REDEFINES

COBOL copybooks can use REDEFINES to let more than one field occupy the same storage, so you can interpret those bytes in more than one way.

For equivalent or technical redefinitions, the meaning of the bytes doesn't change even if the fields have a different structure, byte length, or data type. We keep only the columns from the original definition in your destination and don't add columns from the redefining definition. When equivalent definitions use different PIC 9, PIC A, or PIC X data types with the same encoding, we treat the original definition as PIC X and create a STRING column. For example, consider the following copybook fields:

05 UPD-DT-CCYYMMDD        PIC 9(08).
05 UPD-DT-CC-YYMMDD REDEFINES UPD-DT-CCYYMMDD.
   10 UPD-DT-CC           PIC 9(02).
   10 UPD-DT-YYMMDD       PIC 9(06).

With Source naming, these fields produce only one column, UPD_DT_CCYYMMDD, in your destination. We don't add columns for UPD_DT_CC_YYMMDD, UPD_DT_CC, or UPD_DT_YYMMDD, because they are an equivalent representation of the original field.

For non-equivalent or non-technical redefinitions, the same bytes have different meanings depending on the definition. We keep columns from both the original and redefining definitions as separate, nullable columns and decode each one independently. If the bytes aren't valid for one of the definitions, we set that column to NULL. For example, consider the following copybook fields:

05 FLD-TXT                  PIC X(4).
05 FLD-PDC REDEFINES FLD-TXT PIC S9(4) PACKED-DECIMAL.

With Source naming, these fields produce two nullable columns, FLD_TXT (STRING) and FLD_PDC (BIGDECIMAL). We interpret the overlapping bytes using each definition. If the bytes aren't a valid packed decimal, we set FLD_PDC to NULL.

Fixed repeating fields (OCCURS)

When a copybook defines a fixed number of repetitions with OCCURS, we flatten the repeating field into a separate column for each occurrence, numbered with a single underscore and the occurrence number. For example, consider the following copybook field:

05 TABLE-A OCCURS 2 TIMES PIC X(4).

This field produces two columns, TABLE_A_1 and TABLE_A_2, in your destination.

Primary key

We identify your destination table's primary key from the key range defined in the VSAM key-sequenced data set metadata. The VSAM Bridge automatically retrieves the key's byte offset and length, so no additional configuration is required. We mark each included column from the original definition that has a known byte position and overlaps the key range as a primary key column. If the key range only partially overlaps an eligible column, we mark the entire column as a primary key column. We don't mark excluded columns, redefining columns, or columns without known byte positions as primary key columns.

If no eligible column overlaps the key range, or the file has no key range defined, we add a _fivetran_id (STRING) column and use it as the primary key instead.

Character encoding

VSAM files on z/OS store character data in EBCDIC, and some files also contain double-byte character data for languages such as Japanese, Korean, and Chinese. In the connector setup form, you select a single COBOL Code Page (CCSID), matching the CODEPAGE compiler option your COBOL programs were compiled with. We use this code page to decode text fields into your destination.

By default, we use IBM037 (US/Canada EBCDIC).

Data validation and error handling

If we can't interpret a decoded non-key value as a valid number, for example, invalid digits in a DISPLAY or packed-decimal numeric field, we set that column to NULL for the affected row rather than failing the sync.

If a VSAM record is longer than your copybook defines, we ignore the extra trailing bytes.

Excluding source data

We discover the VSAM data sets under your configured VSAM Files Directory that have matching copybooks. If you don't want to sync all of them, you can exclude data sets or columns on the Schema tab of your Fivetran dashboard. To do so, go to your connection details page and deselect the objects you want to omit from syncing. For more information, see our Data Blocking documentation.

Limitations

• We support historical syncs only.
• We support only key-sequenced data sets (KSDS). Other VSAM types, such as entry-sequenced data sets (ESDS), relative record data sets (RRDS), variable-length relative record data sets (VRRDS), and linear data sets (LDS), aren't supported.