Exported data formats

You can export data in four pre-defined formats:
  • Db2® (File Manager internal) format
  • Db2® external (or unload) format
  • Db2® DSNTIAUL unload format
  • Comma-delimited format (CSV output)
and two formats that require user definition:
  • A user-defined format
  • Copybook layout format

The output format depends on which Data format option you selected on the Export Options - (2 of 3) panel:

To export data in Db2® (File Manager internal) format, specify option 1 (FM/Db2 (SQLDA) format).

To export data in Db2® external (or unload) format, specify option 2 (Db2® unload format).

To export data in Db2® DSNTIAUL unload format, specify option 3 (DSNTIAUL format).

To export data in user-defined format, specify option 4 (User defined).

To export data in comma-delimited format (CSV output), specify option 5 (Delimited Variables (CSV)).

The output format depends on which Template usage processing option you select.

To export data in Db2® (File Manager internal) format, specify Template usage option 3 (Generate from input) or option 4 (Generate and save) and export option 1 FM/Db2 (SQLDA) format. When you specify these options, FM/Db2 copies the output template from the input template, and exports the data in the format returned by Db2® if an SQLDA is used to access the result table. In this format, any nullable column is preceded by a 2-byte binary field containing the null indicator. If the From column has a null value, the null indicator field for the corresponding To field contains -1, otherwise it contains 0. Each decimal field in the output data set occupies 16 bytes, with the data left-aligned followed by trailing binary zeros. Any graphic or vargraphic field is followed by two bytes containing binary zeros. Use this option if you plan to import the exported data into another Db2® table using Import (option 3.6).

To export data in Db2® unload format, specify Template usage option 3 (Generate from input) or option 4 (Generate and save) and select export option 2 Db2® unload format. The exported data is in the same format as that returned by the Db2® UNLOAD utility, with the NOPAD option not specified. In this format, any nullable column is preceded by a 1-byte binary field containing the null indicator. If the “From” column has a null value, the null indicator field for the corresponding “To” field contains X'FF', otherwise it contains 0. Any decimal field occupies the minimum number of bytes needed to represent the data as a packed decimal field. Variable length columns in the unloaded records are padded to their maximum length and the padded data fields are preceded by length fields that indicate the size of the actual data without the padding. CCSID conversion is not supported. The exported data will be in the CCSID used to bind the FM/Db2 plan.

To export data in DSNTIAUL format, specify Template usage option 3 (Generate from input) or option 4 (Generate and save), and select export option 3. DSNTIAUL format. In this format, any nullable column is followed by a 1-byte binary field containing the null indicator. If the "From" column has a null value, the null indicator field for the corresponding "To" field contains "?", otherwise it contains 0. Any decimal field occupies the minimum number of bytes needed to represent the data as a packed decimal field.

To export data in comma-delimited format (CSV output), specify Template usage option 5 (None. CSV output). If you specify this option, FM/Db2 does not generate an output template but writes the exported data as varying-length records, separated by a delimiter. For delimited format, FM/Db2 uses the Null column display indicator to indicate that data for a nullable column is the null value.

To export data in user-defined format, specify Template usage option 3 (Generate from input) or option 4 (Generate and save), and select export option 4. User defined format. For a user-defined format, FM/Db2 provides the following options for nullable columns:
  • To use or not use nulls. In the latter case, there would be no fields in the exported data corresponding to the null indicator. Set Null Indicators Usage to 2. None on Export Options screen.
  • To place the null indicator either before or after the data for the column to which it applies. Set Null Indicators Placement to 1. Before or 2. After on the Export Options screen.
  • To use either a 1-byte (character format) or 2-byte (signed integer format) for the null indicator, set Null Indicators Type to 1. One byte or 2. Two byte on the Export Options screen. Set the value to be used in Indicator Char or Integer.
To export data in user-defined format, and to place either a 1-byte (character format) or a 2-byte (signed integer format) null indicator somewhere in the output record, but not before or after the data for the column to which it applies, select Null Indicators Placement to 3. User defined on the Export Options - (2 of 3) panel. You must provide a copybook and specify Template usage option 1 (Above) or 2 (Previous), and select Edit template mapping and map the null indicators. If the field names in the copybook are not the same as the Db2® column names, you will also need to map each Db2® column to the appropriate field in the copybook.

To export data in any other format, you must use a user-defined template. Specify Template usage option 1 (Above) to use the template you have specified in the To Copybook or Template section of the panel, or Template usage option 2 (Previous) to use the template last used for the table from which you are exporting data. If exporting columns that permit null values, FM/Db2 processes them as follows: if the field that corresponds to a nullable column is followed by an unmapped 2-byte binary field, FM/Db2 maps the null indicator for the column to this 2-byte binary field. In all other situations, FM/Db2 does not map the null indicator to any field in the output template.

Related references