DBX (Export) batch command

Purpose

To export data from a Db2® object (table, view, synonym or alias) or the result table from a SELECT statement, in any local or distributed Db2® system, to a sequential or VSAM file.

Usage

The FM/Db2 Export Utility can export data from a Db2® table or view using one of the following methods:

File Manager: The output data set can be a sequential data set, a partitioned data set, or a VSAM file.
The Db2® UNLOAD utility: The output data set is constrained by the Db2® UNLOAD utility to be a sequential data set. Any reformatting of the output data is performed by the Db2® UNLOAD utility. Therefore some template mapping and re-formatting options that are available when using File Manager are not available.
The Db2® DSNTIAUL sample program: The output data set is constrained by the Db2® DSNTIAUL utility to be a sequential data set. Any reformatting of the output data is performed by the Db2® DSNTIAUL sample program utility. Therefore some template mapping and re- formatting options that are available when using File Manager are not available.

When exporting large volumes of data, the Db2® UNLOAD utility offers superior performance when compared with File Manager or the DSNTIAUL sample program.

You can choose a pre-defined (standard) export data format, or specify the data format using a copybook or template. You can specify that File Manager is to export the data, or use a Db2® utility or sample program to export the data. For a user-defined data format you can specify how the Db2® null indicator is represented (either one byte or two byte) and where the null indicator is located in the exported data. You can also specify that the data is to be exported in delimited format (comma separated variables).

Figure 1.

User options

CSV options

Placement

Null type

Data type

SQLDD=ddname

ddname is the DDNAME, specified in the JCL, that identifies either a data set containing an SQL SELECT statement to be processed, or a DDNAME used to specify an in-line SQL SELECT statement.

SQLRECSZ=nn

nn is the usable record size for the SQL statement. For in-line SQL statements, specified in the JCL, use a value of 72, to avoid problems with sequence numbers in columns 73-80 inclusive.

For a data set, set an appropriate value.

OBJIN=location.owner.name

The optional name of the Db2® remote server (location) where the object is located; the optional name of the owner of the object (owner) and the object name (name) to be exported.

When location is not specified the current (local) DB2® server is used. When the owner is not specified the object name is qualified using the current SQLID. When FM/DB2 generates the utility control statements, the owner value is non-blank.

OBJIN should be used when the fully qualified name fits on a single line in the JCL deck. The last usable column is column 71. When the fully qualified name does not fit on a single line in the JCL deck, use one or more of the OBJILOCN, OBJIOWNR, OBJINAME keywords to specify the object.

OBJILOCN=location

The optional name of the Db2® remote server (location) where the object to be exported is located. See Specifying a Db2 object name.

OBJIOWNR=owner

The optional name of the owner of the object (owner) to be exported. See Specifying a Db2 object name.

OBJINAME=name

The object name (name) for the object to be exported. See Specifying a Db2 object name.

TINPUT=ddname

Defines a reference to a DD statement for the data sets which contain the Db2® template that describes the Db2® object to be exported. If you specify a concatenated DD, then you must provide the member name, member, via the TINMEM keyword. See Specifying the template for a Db2 object.

TINMEM=member

The name of the template member in the datasets identified by the TINPUT parameter, if it has not been specified on the DD statement. This parameter must not be specified if the TMIN parameter is specified. See Specifying the template for a Db2 object.

TMIN=template_data_set_name(member)

The PDS (template_data_set_name) and member name (member) of the FM/Db2 template that describes the Db2® object to be exported. See Specifying the template for a Db2 object.

OUTPUT=ddname

Defines a reference to a DD statement for the export data set. This keyword and the DSNOUT keyword are mutually exclusive. The JCL generated by FM/Db2 specifies OUTPUT=FMNOUT in the File Manager utility control statements, and an appropriate DD statement in the JCL.

DSNOUT=dsname

Defines the name of the export data set or an absolute path to the export HFS file (directory). This keyword and the OUTPUT keyword are mutually exclusive. The name may include a member name in parenthesis. An absolute path to an HFS file (directory) must be enclosed in apostrophes. If the data set name does not fit on one line, you can split it over more than one line.

TOUTPUT=ddname

Defines a reference to a DD statement for the data sets which contain the template member that describes the record structure of your output data. The default is TDDOUT.

TOUTMEM=member

The name of the template member in the dataset(s) identified by the TOUTPUT parameter, if it has not been specified on the DD statement. This parameter must not be specified if the TCOUT parameter is specified.

TCOUT=template/copybook_data_set_name(member)

The PDS (template/copybook_data_set_name) and member name (member) of the template or copybook that describes the record structure of the exported data. The JCL generated by FM/Db2 specifies TCOUT, when an output copybook or template is required. See Specifying the template/copybook for a data set.

GENREP

Whether to generate and save the output template to the data set specified on the TCOUT keyword or TDDOUT DD statement.

NO: Don't generate and save.
YES: Generate and save.

NULLIND=character

For CSV format only, defines the character used to indicate that the value for a column is the Db2® null value, in the exported data. The default is the underscore character. The value for this character is set using the editor Null Column Indicators: Display option. When specified in the File Manager utility control statements, the character must be a printable character other than a comma. The value cannot be enclosed in quotation marks.

NULLIND is always specified in the File Manager utility control statements. The value specified is ignored for all DATAFORMAT specifications other than CSV.

ROWS

The number of records to be exported:

ALL: All records selected from the object are written to the export data set.
num: The maximum number of records to be written to the export data set. Valid range: 1-99999999.

NATIVE

Specifies whether FM/Db2 should process Unicode encoded data natively when exporting data from a Unicode encoded table. This option can be ignored if the default processing of Unicode data is desired; that is, Unicode data is potentially substituted with error characters when copying data between Unicode tables.

YES: Process Unicode data natively.
NO: Do not Process Unicode data natively. This is the default.

CSV

Specifies whether the exported data is to be in delimited variables format or not:

NO: Export data is not to be in delimited format. This is the default.
YES: Export data is to be in delimited format.

DATAFORMAT

The data format for the exported data:

SQLDA: The default. Any nullable column is preceded by a 2-byte binary field containing the null indicator. This field contains -1 if the column has the null value and 0 otherwise. Any decimal field occupies 16 bytes, with the data left-justified in the field and trailing binary zeros. Any graphic or vargraphic field is followed by two bytes containing binary zeros.
UNLOAD: In this format any nullable column is preceded by a 1-byte field containing the null indicator. This field contains hexadecimal "FF" if the column had a null value and 0 otherwise. 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 two-byte length fields that indicate the size of the actual data without the padding. CCSID conversion is not supported. The exported data is in the CCSID used to bind the FM/Db2 plan.
DSNTIAUL: Any nullable column is followed by a 1-byte field containing the null indicator. This field contains "?" if the column had a null value. Any decimal field occupies the minimum number of bytes needed to represent the data as a packed decimal field.
USER: The data format is defined by a user-specified copybook or template and template mapping. Alternatively the data format can be defined using the NIUSAGE, PLACE, TYPE (NICHAR, NIINTEGER), DECIMAL, INTEGER, FLOAT, and SOSI keywords.
CSV: The data is exported as delimited variables. The default delimiter is the comma, the SEPARATOR keyword can be used to specify a different delimiter. A copybook/template cannot be used to describe the delimited variables data format.

CSVHDR

Specifies whether column headers are to be produced on the first record of the export file when CSV=YES has been specified. Possible values are:

NO: Column headers not produced.
YES: Column headers produced.

CSVASIS

Specifies whether the automatic encapsulation for CSV data is disabled when the data contains the CSV separator character or a quote.

YES: Automatic encapsulation is disabled.
NO: Automatic encapsulation is enabled.

This option applies to character data other than pure DBCS data (GRAPHIC datatypes). One use of this option is to prevent double encapsulation of character data that has already been encapsulated with quotes before insertion into a DB2® table.

SEPTYPE=CHAR/INTEGER

Specifies, for CSV format only, the type of separator used to separate each field in the output.

CHAR: One or two characters is used to separate data in each field. Use the SEPARATOR keyword to specify the characters to be used.
INTEGER: A signed integer is used to specify a 2-byte separator. Use SEPINT to specify the signed integer to be used.

SEPARATOR='characters'

Specifies the one or two characters used to separate each field when exporting to CSV format. The default is the comma.

SEPINT=signed_integer

Specifies a signed integer used to separate each field when exporting to CSV format. The value specified is converted to the 2-byte signed representation of the integer. Examples:

-1 results in a two-byte separator X'FFFF'
257 results in a two-byte separator X'0101'

QUOTED

Specifies whether output field values are to be enclosed in quotes. The default is QUOTED=NO. If this option is specified, and SNGLQUOTE is not specified, or, SNGLQUOTE is set to NO, then output fields will be enclosed in double quotes.

YES: Enclose output field values in quotes.
NO: Default. Do not enclose output field values in quotes.

SNGLQUOTE

Specifies whether output fields values are to be enclosed in single quotes or double quotes. The default is SNGLQUOTE=NO.

YES: Enclose output field values in single quotes.
NO: Default. Enclose output field values in double quotes.

LZERO

Specifies whether numeric fields will be prefixed with leading zeros. The default is LZERO=NO.

YES: Prefix numeric fields with leading zeros.
NO: Default. Do not prefix numeric fields with leading zeros.

LEADING

Specifies if leading blanks within Db2 CHAR and VARCHAR columns are to be included in the CSV export data set. The default is LEADING=YES.

YES: Include leading blanks in the exported data set. If the value of the Db2 column is all blanks, and TRAILING=YES is specified, then all of the blanks are included in the exported data set.
NO: Do not include leading blanks in the exported data set.

TRAILING

Specifies if trailing blanks within Db2 CHAR and VARCHAR columns are to be included in the CSV exported data set. The default is TRAILING=NO.

YES: Include trailing blanks in the exported data set. If the value of the Db2 column is all blanks, and LEADING=YES is specified, then all of the blanks are included in the exported data set.
NO: Do not include trailing blanks in the exported data set.

PLACE

The location of any Db2® null indicator fields in the exported data:

BEFORE: The field for the Db2® null indicator appears immediately before the data for the column.
AFTER: The field for the Db2® null indicator appears immediately after the data for the column.
USER: The field for the Db2® null indicator is defined using a copybook or template, with template mapping.

NIUSAGE

Specifies, for DATAFORMAT=USER only, whether or not null indicators appear in the exported data. To specify null indicator usage for CSV format use the NITYPE, NICHAR, and NIINTEGER keywords.

NONE: The exported data is not to contain any DB2® null indicators. Using this option with a DB2® object that has nullable columns results in a loss of information, in that null indicators in the source record are not exported.
SEPARATE: The exported data is to contain DB2® null indicators. The position and data type of the null indicator fields is specified using the PLACE, TYPE, NICHAR, and NIINTEGER keywords.

TYPE=CHAR/INTEGER

The data format for the null indicator fields, for DATAFORMAT=USER only. The null indicator can be either a one-byte (character) or two-byte (signed integer) field. The character or integer used to indicate the DB2® null value can be specified using the NICHAR (TYPE=CHAR) or NIINTEGER (TYPE=INTEGER) keywords.

NITYPE=CHAR/INTEGER

The data format for the null indicator fields, for DATAFORMAT=CSV only. The null indicator can be either a one-byte (character) or two-byte (signed integer) field. The character or integer used to indicate the DB2® null value can be specified using the NICHAR (NITYPE=CHAR) or NIINTEGER (NITYPE=INTEGER) keywords.

NICHAR=character

The character used to indicate that a column has the DB2® null value. NICHAR should only be specified with DATAFORMAT=USER and TYPE=CHAR.

NIINTEGER=signed_integer

The integer used to indicate that a column has the DB2® null value. NIINTEGER should only be specified with DATAFORMAT=USER and TYPE=INTEGER. You can specify any signed integer in the range -32767 to 32768.

DECIMAL

Specifies the data format for any decimal columns in the exported data. Possible values are:

INTERNAL: Decimal data appears in internal packed decimal format.
ZONED: Decimal data appears as a string of zoned decimal digits.
EXTERNAL: Decimal data appears as a string of characters, the string represents the decimal number, for example '3.1415'.

INTEGER

Specifies the data format for any integer columns in the exported data. An integer column can be a SMALLINT, INTEGER or BIGINT. Possible values are:

NO: Integer data appears in internal format, occupying 2 bytes (SMALLINT), 4 bytes (INTEGER) or 8 bytes (BIGINT).
EXTERNAL: Integer data appears as a string of characters, the string represents the integer, for example, '42', '1000000'.

FLOAT

Specifies the data format for any floating point columns in the exported data. A floating point column is a REAL, FLOAT, DOUBLE or DECFLOAT column. Possible values are:

NO: The floating point data appears in internal format.
EXTERNAL: Floating point data appears as a string of characters, the string represents the number, for example '-1.0000000000000000E+00'.

SOSI

Specifies whether the data for graphic data type columns (GRAPHIC, VARGRAPHIC) should be enclosed in shift-out/shift-in (sosi) characters. Possible values are:

NO: The graphics data is not enclosed in sosi characters.
YES: The graphics data is enclosed in sosi characters.

Examples

Example 1: Export data from Db2® sample table DSN8810.EMP to a sequential file, using SQLDA format. The output data set is allocated in the batch job.

//DBX JOB (acct),'name'
//* Export DSN8810.EMP table to a data set
//*
//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DSN1,SQID=ID1')
//STEPLIB  DD DSN=FMN.SFMNMOD1,DISP=SHR
//         DD DSN=DB2V810.DSN1.SDSNEXIT,DISP=SHR
//         DD DSN=DB2.V810.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//FMNOUT   DD DISP=(MOD,CATLG,DELETE),UNIT=SYSALLDA,
//            AVGREC=U,
//            LRECL=144,RECFM=FB,DSORG=PS,
//            SPACE=(144,(42,4),RLSE),
//            DSN=ID1.EXPORT.EMP.SQLDA
//SYSIN    DD *
$$FILEM DBX OBJIN="DSN8810"."EMP",
$$FILEM OUTPUT=FMNOUT,
$$FILEM NULLIND=_,
$$FILEM CSV=NO,
$$FILEM DATAFORMAT=FMSQLDA,
$$FILEM ROWS=ALL
/*

Example 2: Export data from Db2® sample table DSN8810.EMP to a sequential file, using Db2® UNLOAD format. The output data set is allocated in the batch job.

//DBX JOB (acct),'name'
//* Export DSN8810.EMP table to a data set
//*
//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DSN1,SQID=ID1')
//STEPLIB  DD DSN=FMN.SFMNMOD1,DISP=SHR
//         DD DSN=DB2V810.DSN1.SDSNEXIT,DISP=SHR
//         DD DSN=DB2.V810.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//FMNOUT   DD DISP=(MOD,CATLG,DELETE),UNIT=SYSALLDA,
//            AVGREC=U,
//            LRECL=101,RECFM=FB,DSORG=PS,
//            SPACE=(101,(42,4),RLSE),
//            DSN=ID1.EXPORT.EMP.UNLOAD
//SYSIN    DD *
$$FILEM DBX OBJIN="DSN8810"."EMP",
$$FILEM OUTPUT=FMNOUT,
$$FILEM NULLIND=_,
$$FILEM CSV=NO,
$$FILEM DATAFORMAT=UNLOAD,
$$FILEM ROWS=ALL
/*

Example 3: Export data from Db2® sample table DSN8810.EMP to a sequential file, using Db2® DSNTIAUL format. The output data set is allocated in the batch job.

//DBX JOB (acct),'name'
//* Export DSN8810.EMP table to a data set
//*
//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DSN1,SQID=ID1')
//STEPLIB  DD DSN=FMN.SFMNMOD1,DISP=SHR
//         DD DSN=DB2V810.DSN1.SDSNEXIT,DISP=SHR
//         DD DSN=DB2.V810.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//FMNOUT   DD DISP=(MOD,CATLG,DELETE),UNIT=SYSALLDA,
//            AVGREC=U,
//            LRECL=101,RECFM=FB,DSORG=PS,
//            SPACE=(101,(42,4),RLSE),
//            DSN=ID1.EXPORT.EMP.DSNTIAUL
//SYSIN    DD *
$$FILEM DBX OBJIN="DSN8810"."EMP",
$$FILEM OUTPUT=FMNOUT,
$$FILEM NULLIND=_,
$$FILEM CSV=NO,
$$FILEM DATAFORMAT=DSNTIAUL,
$$FILEM ROWS=ALL
/*

Example 4: Export data from a table with a long name containing spaces to a sequential file, using SQLDA format. The output data set is pre-allocated.

//DBX JOB (acct),'name'
//* Export a table with a long name to a data set
//*
//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DSN1,SQID=ID1')
//STEPLIB  DD DSN=FMN.SFMNMOD1,DISP=SHR
//         DD DSN=DB2V910.DSN1.SDSNEXIT,DISP=SHR
//         DD DSN=DB2.V910.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//FMNOUT   DD DISP=MOD,
//            DSN=ID1.EXPORT.EMP.EXAMPLE4
//SYSIN    DD *
$$FILEM DBX OBJIOWNR=ID1,
$$FILEM OBJINAME='\$Very long table name to demonstrate how \$',
$$FILEM '\$long names are handled\$',
$$FILEM OUTPUT=FMNOUT,
$$FILEM NULLIND=_,
$$FILEM CSV=NO,
$$FILEM DATAFORMAT=FMSQLDA,
$$FILEM ROWS=ALL
/*

Example 5: Export data from Db2® sample table DSN8810.EMP to a sequential file, using a user-defined data format. The output data set is pre-allocated. In the user-defined data format:

Null indicators are used (NIUSAGE=SEPARATE)
Null indicators appear immediately after the data for the column (PLACE=AFTER)
The null indicator is a two byte (signed integer) field (TYPE= INTEGER)
The value used to indicate a column has the Db2® null value is -55.
Any DECIMAL data type columns appear in external format (for example '3.1414')
Any INTEGER data columns appear in external format (for example '1234')
Any floating point data type columns appear in external format.

//DBX JOB (acct),'name'
//* Export DSN8810.EMP table to a data set
//*
//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DSN1,SQID=ID1')
//STEPLIB  DD DSN=FMN.SFMNMOD1,DISP=SHR
//         DD DSN=DB2V910.DSN1.SDSNEXIT,DISP=SHR
//         DD DSN=DB2.V910.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//FMNOUT   DD DISP=MOD,
//            DSN=ID1.EXPORT.EMP.EXAMPLE5
//SYSIN    DD *
$$FILEM DBX OBJIN="DSN8910"."EMP",
$$FILEM OUTPUT=FMNOUT,
$$FILEM NULLIND=_,
$$FILEM CSV=NO,
$$FILEM DATAFORMAT=USER,
$$FILEM NIUSAGE=SEPARATE,
$$FILEM PLACE=AFTER,
$$FILEM TYPE=INTEGER,
$$FILEM NIINTEGER=-55,
$$FILEM DECIMAL=EXTERNAL,
$$FILEM INTEGER=EXTERNAL,
$$FILEM FLOAT=EXTERNAL,
$$FILEM ROWS=ALL
/*

Example 6: Export data from Db2® sample table DSN8810.EMP to a sequential file, using a CSV (delimited variables) format. The delimiter character is the '!' character. The output data set is pre- allocated.

//DBX JOB (acct),'name'
//* Export DSN8810.EMP table to a data set
//*
//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DSN1,SQID=ID1')
//STEPLIB  DD DSN=FMN.SFMNMOD1,DISP=SHR
//         DD DSN=DB2V910.DSN1.SDSNEXIT,DISP=SHR
//         DD DSN=DB2.V910.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//FMNOUT   DD DISP=MOD,
//            DSN=ID1.EXPORT.EMP.CSV
//SYSIN    DD *
$$FILEM DBX OBJIN="DSN8910"."EMP",
$$FILEM OUTPUT=FMNOUT,
$$FILEM NULLIND=_,
$$FILEM CSV=YES,
$$FILEM DATAFORMAT=CSV,
$$FILEM SEPARATOR='!',
$$FILEM ROWS=ALL
/*

Example 7: Export data from Db2® sample table DSN8810.EMP to a sequential file, using a CSV (delimited variables format). The delimiter character is x'FFFF', defined using the NIUSAGE, TYPE and NIINTEGER keywords. The output data set is pre-allocated.

//DBX JOB (acct),'name'
//* Export DSN8810.EMP table to a data set
//*
//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DSN1,SQID=ID1')
//STEPLIB  DD DSN=FMN.SFMNMOD1,DISP=SHR
//         DD DSN=DB2V910.DSN1.SDSNEXIT,DISP=SHR
//         DD DSN=DB2.V910.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//FMNOUT   DD DISP=MOD,
//            DSN=ID1.EXPORT.EMP.CSV2
//SYSIN    DD *
$$FILEM DBX OBJIN="DSN8910"."EMP",
$$FILEM OUTPUT=FMNOUT,
$$FILEM NULLIND=_,
$$FILEM CSV=YES,
$$FILEM DATAFORMAT=CSV,
$$FILEM NIUSAGE=CSV,
$$FILEM TYPE=INTEGER,
$$FILEM NIINTEGER=-1,
$$FILEM ROWS=ALL
/*

Return codes

The return codes from the DBX function:

0: The function completed successfully
16: A serious error was encountered

Related functions

DBI: Import data from a sequential or VSAM file into a Db2® table (DBI (Import) batch command)
DBC: Copy data from one Db2® object to another (DBC (Copy) batch command)
D2G: Create (generate) Db2® data (D2G (Data Generate) batch command)