QED MD - Components - Table to Device Redirects

• Overview
• Multiple Tables
• Advanced File Options
• Access Method
• Output Append
• Sort Delete
• Input Delete

• Fixed/Variable
• DD Name
• Concatenation
• Use
• Data Group
• Translation Devices

Overview

Mostly, QED works with databases. Access to the data is done either via entities or by direct reading of the database tables. Two of its uses, however, are to handle data from other sources and to transfer data from databases to other destinations. The mechanisms provided to enable access to these external data sources are the Tables and Devices components.

Although defined in the Mapping components Tables and Devices, the Devices are external to QED, sitting between QED and the actual source (or destination) of the data. The diagram shows a simplified view of the relationships (arrows indicate data flows):

Diagram qed080

Table definitions held in the system normally refer to SQL databases. However, the table definitions are actually independent of the source of the data, so it is possible to redirect the access path, by means of Tables and Devices components, to almost any source of data. The only requirement is that the data in the external source (usually a sequential file) should be structured as a table, with each record in the file corresponding to a row in a table and each field corresponding to a column.

The external file must have a record created for it using Device - Amend MANT and the table to which it is to be attached must also exist, before you can create the Table to Device Redirects. The Device Record specifies the location of the external file.

A tables component entry creates a link between one table definition and one external file. (It is possible to have more than one table linked to a single file.)

A device component entry defines the external files used within the Mapping. The options available are documented later in this section.

Once the link exists, then the table to which the external file is attached can be used by the Mapping as if it were a normal database table.

An external device may be attached to a table in one of three ways: as an In Device, an Out Device, or a Sort Device. Records are read from the In Device, or written to the Out Device. The Sort Device is used to rearrange the records in the file into the order specified by a key column in the table definition.

When a device is attached to a table as an In Device, then the records it contains must be in the correct order - the table key column order.

Prior to reading data into the table, records from the In Device are sorted via the Sort Device into the sequence defined by the key column of the associated table. QED will automatically use a sort device. There is no need for a physical device to be named in the Tables or Devices components but users can specify their own sort devices, if required, within these components.

(A key column in a table is a column containing values which can uniquely identify any single row in the table. Key columns are normally indexed, for rapid searching and access.)

QED will automatically define the external output files that it uses (unless the external file is Output Append). If the external file already exists, it is first deleted, then defined. The only exceptions to this rule are mainframe VSAM files, which must be pre-defined before the Mapping is run.  All Input and Output devices may have a translation program specified (XLATE_PRG) which will convert between an external data file format (e.g. comma separated value CSV or XML) and the internal e5 one.

Multiple Tables

It was stated above that a single file may have links to more than one table definition. An example of this kind of usage is the case where a file contains two kinds of records, identified by a particular field.

Assume that a record of one type can have multiple associated records of the other type and call them headers and transactions respectively. A table is defined to handle the header records and another for the transactions. A common field contains a single character which distinguishes the record types. Both tables are associated with the same device, but a Choose expression is used to separate the records to be passed through them:

Records with a record type H are processed via the table HDR_TBL and records with type T are processed via TRN_TBL.

Advanced File Options

The devices component defines the external file's characteristics, access method and delete options. If no entry exists for an external file in the device component, QED will generate defaults based on what it knows about the tables that are to be redirected to the external file.

Access Method

The access method defines how QED will try to access the external file. There are three options:

• Record_Sequential - The standard IBM mainframe access method (default).
• Line_Sequential - A Unix only file access method.
• VSAM - An IBM mainframe file type.

Output Append

If the QED Mapping is to add data to an existing output file (rather than create a new file), the Output Append flag should be set. The file must exist when the Mapping is run; if it does not the Mapping will fail.

Sort Delete

When this flag is set, the Mapping will automatically delete any sort files it uses. Normally, such files should be deleted as they are temporary work files.

Input Delete

When this flag is set, the Mapping will automatically delete any input files after they have been read. If the file access type is VSAM, the file can not be deleted.

Fixed/Variable

This option controls the file size characteristics. There are three options:

• Fixed - The file is of a fixed size, as defined in the Devices component max size.
• Variable - The file varies in size as defined in the max/min size in the Devices component.
• Variable_on_size - The file varies in size depending on a LEN field held at the start of each of the file's records, which may be different for each record. The min/max definition, if stated, will be used as the smallest and largest record size for the file but if used must match exactly for output and input files. For output files, the LEN field must be set up during an insert or the Mapping will fail.

DD Name

Holds a DD NAME which will be used when the Mapping is run.

Concatenation

This flag, when set, indicates that the Mapping is using a device concatenation instead of a normal device. Concatenations are only valid for input devices. All of the devices being concatenated must have exactly the same characteristics in order for the Mapping to work.

Use

This indicator controls how the device is to be used within the Mapping. The normal use is Automatic Control which means all opens/closes/deletes are performed by QED. The device pretends to be a normal database table (with a few restrictions). If the Use indicator is set to User control, QED does nothing with the device and all actions must be performed by the user. This gives the facility, for example, for a Mapping to write data to a device and then read it back later.

Data Group

This is used when the Translation Device (see later) has been set to any of the XLATE_XML* translation includes.   When a file containing XML data needs to be read or created this indicates which QED Data Group is to be used.

Translation Devices

QED has the ability to read non-e5 standard files. This is achieved by reading the file as normal (via Tables and Devices components) and adding an Xlate program and an Xlate parameter to the device definition. QED will call the translate program instead of reading/writing the external file.

Currently the only Xlate programs available are for a delimited format file (CSV) or XML (See Data Group and all XML related commands and functions).

A CSV file can be delimited by any valid character such as a comma (,) or a semi colon (;). The data itself can be surrounded by a second character, for example quote marks ('') or double quotes (''). The data can have one record, or a list of fields, per line. For example:

'A1','descr','address line 1','A2','descr A2'

'address line'

or

A1;descr;address line 1

A2;descr A2;address line

Several predefined formats are available by prompting on the XLATE INCLUDE field, for example, Excel, Access and Lotus 123.

See Import / Export (XLATE) FILES CSV/XML

An XML file contains data surrounded by Tags which conforms to the eXtensible Markup Language Version 1.0 definition. There are two main predefined formats with and without automatic schema and stylesheet generation.

See also

QED MD - Components

QED Mapping Development Home Page