AS/400 Data Stores

There are several alternatives to mask and subset AS/400 data stores.

ODBC connection to AS/400

Access via ODBC to AS/400 data via ODBC driver has the following limitations:

  • DB2 connect 9 is needed in order to guarantee compatibility with AS/400 of Data Express.
  • Flat files created by the CRTPF command without DDLs cannot be accessed via ODBC. This access work if there is a DDL or if the file is created via SQL.
  • The target database needs commitment control and so it needs to be created and journaled before Data Express runs.
  • Performance is slow because each row moves from AS/400 to windows (where the engine runs), this is mainly true for masking process (while for subsetting this is mitigated by the fact the reduction criterion can use local AS/400 indexes, differently to other two alternatives).

The following native AS/400 column data types are supported (in parenthesis the corresponding SQL ones)

  • Character (character,varchar)
  • Packed decimal (decimal)
  • Zoned decimal (numeric)
  • Binary (integer, smallint, bigint)
  • Floating point (float, real, double)
  • Date
  • Time
  • Timestamp
  • Hexadecimal
  • Binary Character (binary)

The following native AS/400 column data types are not supported:

  • DBCS-Only
  • DBCS-Either
  • DBCS-Open
  • DBCS-Graphic (graphic, vargraphic, long vargraphic)

The following SQL column data types not having a correspondence with native AS/400 types are not supported:

  • long varchar
  • varbinary
  • decfloat
  • blob
  • clob
  • dbclob
  • datalink
  • rowid

Details about how Data Express deals with ODBC data stores are available in the documentation, in particular in Getting started with distributed data stores.

MVS flat files based approach

Engine can run on MVS. Flat files containing AS/400 data are handled as MVS sequential files. FTP is used in order to move data from AS/400 to MVS and vice versa. It is needed providing copybook mapping file layout.

Details about how Data Express deals with z-OS data stores are available in the documentation, in particular in Getting started with z-OS data stores.

Windows flat files based approach

Engine can run on windows. CPYTOIMPF is used in order to generate a delimited file, to be sent via FTP to a windows machine. Here it is processed via DataDirect ODBC 6.0 driver, provided by Progress. It is needed to provide a file structure in a native DataDirect format dictionary. Then masked data is sent via FTP back to AS/400, and restored with CPYFRMIMPF to target AS/400 table

Details about how Data Express deals with ODBC data stores (in this case with flat files) are available in the documentation, in particular in Getting started with distributed data stores.

Comparison between approaches

  • MVS flat file approach is preferred if an MVS is available. It requires copybooks, normally already available in the application
  • Windows flat file based approach requires more manual work than ODBC approach, and it is faster than it for masking and slower than it for subsetting (comparison of performances are strictly related to characteristics of the machines)
Parent topic: Supplementary Information