When a mainframe application performs a sequential read on a VSAM file, records are retrieved in an order determined by the EBCDIC collating sequence. By contrast, after the mainframe application has been transformed by Heirloom, the same records are retrieved in an order determined by the ASCII collating sequence. Therefore we implemented the option of using EBCDIC data key column for the retrieval of VSAM/VDB records. The feature is tested to be working with MS SQL Server, DB2 and PostgreSQL.
If flag sql.file.useEbcdicBinaryColumn=true is passed (flag can be passed via command line as described in Transform feature article), the VDB table is loaded with data extracted from the mainframe. An EBCDIC binary index column would be populated with key values in EBCDIC encoding. This approach is used in oppose to varchar column with EBCDIC collation, because of the support for other database other than MS SQL Server.
The new varbinary column is named the same way as the original key column with CI prefix as can be seen on the screenshot. The new varbinary column is used as primary key and would have a clustered index created. Ebcdic column is created also for every alternate key
When VDB file is opened, records would be retrieved according to the order of the EBCDIC index column if the EBCDIC index column exists.
For IDCAMS FROMKEY/TOKEY read, we need to specify additional sql.file.useEbcdicBinaryColumn=true configuration in IDCAMS.properties. Thus TOKEY value would be converted to its EBCDIC equivalent before records are compared.
If we want to exclude an Ebcdic column to be created for a specific alternate index, add the following fields in prof file.
ASCII-ALTKEY-OFFSETS=[n, m, ...<list of alt key offsets to be excluded>]
ASCII-ALTKEY-LENGTHS=[x, y, ...<list of alt key lengths to be excluded>]
The values should be added in the same format as in OUTFILE-ALTKEY-OFFSETS and OUTFILE-ALTKEY-LENGTHS properties. For example if we want to skip creation of an EBCDIC column for idx25_15 we need to add the following configuration to prof file:
Please note that another option is the usage of sql.file.useMSSQLcollateEbcdic for which would not require reuploading of data using Transform. In this solution we perform a case on each select and that leads to major performance problems on larga datasets.