Table Unload

The table unload task (tableunload) exports the data stored in a table to a sequential file, with a format similar to the one produced by DSNTIAUL.

By default the task unload all columns for all rows, but can be limited to:

  • a subset of the columns (exclude element)
  • a subset of rows (where element)

By default, tables are unloaded using their primary key, if available. The ordering can be changed using the orderby element.

The table unload task is defined by the XML element tableunload whose attributes configure the execution parameters.

The following attributes configure the task:

Attribute Type Purpose Default
db string Name of the database where the table is stored
dateformat string Defines the external format for DATE columns. yyyy-MM-dd
timeformat string Defines the external format for TIME columns. hh\.mm\.ss
timestampformat string Defines the external format for TIMESTAMP columns. yyyy-MM-dd-HH.mm.ss.ffffff
warnmissingdbcs bool Warns when DBCS bytes not found in the codepage. true
warnmmalformeddbcs bool Warns when malformed DBCS sequences. true
charasbinary bool If set to true CHAR/VARCHAR columns are treated as BINARY. See char as binary false

The following elements are used to configure the unload:

Element Purpose
table Fully qualified name of the table to be unloaded
file Name and attributes of the output file
columns Alters the charactristics of the output fields for each columns
where Defines the row selection criteria. If omitted, all rows are unloaded
exclude Defines the regular expression based column exclusion criteria, if omitted no column is excluded
cast Defines optional column manipulation and casting

Table

The table element is used to provide the fully qualified of the table being unloaded.

The following attributes configure the table:

Attribute Type Purpose Default
bitdatacodepage string Defines the codepage to translate the binary data (i.e. CHAR FOR BIT DATA) from EBCDIC to ASCII. If not specified and no default bitdatacodepage is set for the datasource, no translation is performed. For the list of supported codepages, refer to Codepages.
layoutencoding string Defines the encoding (ebcdic or ascii) of the layout based columns As defined in database datasource configuration
layoutzoned string Defines the SIGN mode (ascii or ebcdic) of the ZONED fields of the layout based columns As defined in database datasource configuration
layoutendian string Defines the endianness of integer fields of the layout based columns As defined in database datasource configuration
layoutcodepage string Default codepage to be used for the EBCDIC/ASCII conversion of the layout based columns As defined in database datasource configuration

File

The file element defines the output file.

The following attributes configure the file:

Attribute Type Purpose Default
recfmt string Defines the record format of the file. Refer to File Formats fixed
reclen integer Defines the maximum record length
varfmt string Defines the variable record format. Refer to Variable Record Formats If not specified, the repository default is assumed.
dd string When used with in a JCL interface, the name of the DD JCL statement referencing the file. The file characteristics are extracted from the file catalog and, therefore, the other attributes (recfmt, reclen and varfmt) are ignored.
trim bool If true, trailing spaces and NULLs (0x00) are removed from line sequential records false
encoding string Name of the text encoding to be used for Line Sequential files. Latin1
cache bool If false, file copy is refreshed (applies to cached repositories only) true
Note

The list of available encodings is listed here: https://docs.microsoft.com/it-it/dotnet/api/system.text.encoding?view=net-5.0

Note

For MARS repositories, recfmt, reclen and varfmt are ignored. The file characteristics are extracted from the MARS file catalog.

File Formats

Type Synonim Description
fixed fb Fixed Record Length
variable vb Variable Record Length
lineseq ls Line Sequential

Variable Record Formats

Type Description
ibm IBM variable record format
microfocus Micro Focus variable record format

Columns

Database columns are mapped to field in the file record as follows:

DB2 Type SQL Server Type Oracle Type PostgreSQL Type External type External Length
CHAR CHAR CHAR CHAR char n
CHAR FOR BIT DATA BINARY BYTEA binary n
VARCHAR VARCHAR VARCHAR / VARCHAR2 CHARACTER VARYING / VARCHAR char n
VARCHAR FOR BIT DATA VARBINARY BYTEA binary n
TIME TIME TIME char 8
DATE DATE DATE DATE char 10
TIMESTAMP DATETIME / DATETIME2 TIMESTAMP char 26
SMALLINT TINYINT / SMALLINT SMALLINT int 2
INTEGER INT INTEGER int 4
BIGINT BIGINT BIGINT int 8
NUMERIC NUMERIC NUMERIC packed n/2+1
DECIMAL DECIMAL DECIMAL packed n/2+1
NUMBER(n<=6) integer 4
NUMBER(n<=18) integer 8
NUMBER(n>18) packed n/2+1
NUMBER(n,d) packed n/2+1
GRAPHIC NCHAR NCHAR NCHAR dbcs n*2
VARGRAPHIC NVARCHAR NVARCHAR / NVARCHAR2 NVARCHAR dbcs n*2

The data types for the external rapresentation are described in Field Data Types.

columns element is used to override the default definition for the output fields. Each override is defined by the column element.

Column

The column element is used to define external field override.

The following attributes override the field definition:

Attribute Type Purpose Default
name string Name of the column being overridden
type string Field type
length integer Field length
decimals integer Number of decimals
signed bool Indicates if the field is signed false
varlen bool Indicates if the field is varying in length false
mixedchars bool Indicates if chars have to be considere as mixed data false

Example

<tableunload name="UNLOAD.ORA" db="ORA" repository="TMPA">
    <table>IANUS.TEST01</table>
    <file recfmt="fixed" reclen="*">TMP01.UNLOAD.ORA.ASCII</file>
    <columns>
        <column name="C12" type="binary"/>
        <column name="C61" length="3"/>
        <column name="C71" length="3"/>
        <column name="C71" length="3"/>
        <column name="C80" length="4"/>
        <column name="C91" length="2"/>
        <column name="PK3" length="4" type="integer"/>
    </columns>
</tableunload>

Order

The order element sets the sort criteria for one or both sides of the comparison.

The following attributes configure the where element:

Attribute Type Purpose Default
side string Side where the criteria applies: left, right or both both
mode string Order by mode: user or key. Refer to Sort modes user

Sort modes

Tables can be sorted in the two modes:

  • key: the table is order by primary key. If the exclude attribute is defined, the element value defines the regular expression for the exclusion
  • user: the table is sorted according to the user provided ORDER BY clause specified in the element value

When mode is key, optionally, some columns can be excluded from the key, defining the attribute exclude, which value can be:

  • name: the columns whose name matches the provided regular expression are excluded from comparison
  • type the columns whose type matches the provided regular expression are excluded from comparison

Exclude

The exclude element is used to define the regular expression to exclude columns from the unload.

There are two types of exclusion criteria:

  • name: the columns whose name matches the provided regular expression are excluded from unload
  • type: the columns whose type matches the provided regular expression are excluded from unload

The following attributes are used to configure the exclude element:

Attribute Type Purpose Default
type string Defines the exclusion mode: name or type name
side string Defines the set where the exclusion applies: left, right or both both

Where

The where element defines the row selection criteria for source table. The criteria is expressed as an SQL condition.

Example

<tableunload name="UNLOAD.SQL" db="QS" repository="TMPA">
    <table>dbo.TEST01</table>
    <file recfmt="fixed" reclen="*" >TMP01.UNLOAD.SQL.PART</file>
    <where>PK1='KEY01'</where>
    <columns>
        <column name="C61" length="3"/>
        <column name="C71" length="3"/>
    </columns>
</tableunload>

Cast

The cast element is used to define the data manipulation and casting clauses for the columns.

There are two types of cast criteria:

  • name: the columns whose name matches the provided regular expression are accessed with the specified clause
  • type: the columns whose type matches the provided regular expression are accessed with the specified clause

The following attributes are used to configure the exclude element:

Attribute Type Purpose Default
type string Defines the cast mode: name or type type
arg string Defines the regular expression to match columns
side string Defines the set where the cast applies: left, source, right, target or both both

The clause is defined as SQL and the following special registers are interpolated:

Register Interpolate
{name} Column name
{length} Column size
{value} Host variable's value

Example

<tablecopy name="CAST.COPY" source="QS" target="QS1" truncate="true" mode="bulk">
	<source>dbo.TEST01</source>
	<target>dbo.TEST01_COPY</target>
	<cast type="name" arg="C1[01]" side="source">UPPER({name})</cast>
</tablecopy>
<tablecompare name="CAST.CMP.OK" left="QS" right="QS1" >
	<left>dbo.TEST01</left>
	<right>dbo.TEST01_COPY</right>
	<cast type="name" arg="C1[01]" side="left">UPPER({name})</cast>
</tablecompare>

Char As Binary

If enabled and the database encoding is EBCDIC, all CHAR/VARCHAR columns are treated as binary (i.e. CHAR FOR BIT DATA) and therefore converted according to the specified codepage (when specified).

The feature can be activated in two ways:

  • setting the attribute charasbinary to true on the task elemement, or
  • adding the element charasbinary

The charasbinary element can be used to override the codepage or to exclude specific columns from the treatment.

The following attributes configure charasbinary:

Attribute Type Purpose Default
enabled bool If set to false, the feature is deactivated true
codepage bool codepage to use codepage defined for the table

The following elements configure the table copy:

Element Purpose
exclude Exclude columns from the cast. This element works like the element exclude of the task.

Example

    <tableunload name="UNLOAD.DB2.CHARASBIN" db="DB2ZOS" repository="TMPA" charasbinary="true">
        <table bitdatacodepage="ENUS370">IBMUSER.TEST01</table>
        <file recfmt="fixed" reclen="*">TMP01ZOS.UNLOAD.DB2.ASCII</file>
    </tableunload>
    <tablecopy name="ZOSCOPY.CHARASBIN.NATIVE" mode="native" source="DB2ZOS" target="QS" truncate="true" >
        <source bitdatacodepage="ENUS037">IBMUSER.TEST01</source>
        <target>dbo.TEST01_COPY</target>
        <charasbinary/>
    </tablecopy>
    <tablecopy name="ZOSCOPY.CHARASBIN.NATIVE" mode="native" source="DB2ZOS" target="QS" truncate="true" >
        <source bitdatacodepage="ENUS037">IBMUSER.TEST01</source>
        <target>dbo.TEST01_COPY</target>
        <charasbinary codepage="ENUS037" enabled="true">
            <exclude side="right" type="name">F.*B</exclude>
            <exclude side="right" type="type">DATETIME2</exclude>
        </charasbinary>
    </tablecopy>

Convert

The convert element customizes the conversion of the column(s) matching all the provided criteria.

There are two possible customization modes:

  • plugin mode: the column is converted by a user provide plug-in (default)
  • layout mode: the column is converted according to a layout defined in the layouts element

The criteria are:

Criteria Description
sourcename the name of the source column
targetname the name of the target column
sourcetype the SQL type of the source column
sourcetype the SQL type of the target column
Note

If no criteria is defined, all columns are matched.

The following attributes are used to configure the convert element:

Attribute Type Purpose Default
mode string Defines the custom conversion mode (plugin, layout) plugin
sourcename string Name of the source column
targetname string Name of the target column
sourcetype string SQL type of the source column
targettype string SQL type of the target column
tohex bool the output of the converted is represented in HEX mode before sending to database false
path string (plugin mode) Indicates to load the class script from the specified file
assembly string (plugin mode) Indicates to load the class assembly from the specified DLL
class string (plugin mode) The name of the converter class to use
plugin string (plugin mode) Indicates to load the class from the plugin alias defined in the Ianus configuration file
layout string (layout mode) the name of the layout to use for the column the first layout defined
matcher string (layout mode) The name of the matcher class to use to assign the correct layout the first matcher defined
codepage string (charasbinary mode) The name of the codepage to convert data

The user provided conversion plugin must implement the interface HPE.Ianus.Scripting.IColumnConverter. For a detailed description of the comparison class please refer to Column Converter.

Warning

Columns converted with layout mode must target columns defined with binary data types (i.e. VARBINARY for MSSQL or RAW for Oracle).

Warning

User converter plugins must return data in a format compatible with the target column. So if the target columns is CHAR, must return a String; if the target is VARBINARY a byte[], etc.

Layouts

The layouts element defines one or more record layout to convert the layout based columns.

The following elements configure the layouts:

Element Purpose
layout Adds a layout definition. Refer to Layout
match Defines the match algorithm. Refer to Match

Layout

The layout element defines a one or more record layouts. Each layout must be identified by a unique name (task-wide).

By default, the layout fields are defined using XML:

  • Each layout element defines one single layout
  • Record fields are defined using field elements

But there is also the possibility to define the layout using COBOL data definition syntax:

  • Each layout element defines one or more layouts (one each level 01)
  • Record fields are defined using COBOL notation

For further information on layout definition, refer to Record Layouts

Match

The match elements define one ore more "match class". Whenever more than on layout is provided, Ianus needs a match class to drive the selection of the correct layout for each record compared.

This class must implement the interface HPE.Ianus.Scripting.IColumnLayoutMatch. For a detailed description of the match class please refer to the Column Layout Matcher section.

The class can be provided as:

  • C# script: the code is compiled on the fly by Ianus and declared either:
    • inline, by adding the code in the element value, or
    • by reference, providing the filename of the script on the path attribute. If the script file name is not absolute, the script is searched in the same path of the job script.
  • Class library: the class can be coded in any .NET language such as C# or VB.NET but also COBOL for .NET and compiled as .NET class library. The class can be referenced either:
    • directly: providing the filename of the DLL containing the class on the assembly attribute and the full name of the class on the class attribute, or
    • by alias: providing the name of the plugin alias defined in the Plug-ins section of the Environment Configuration File.

The following attributes configure the element:

Attribute Type Purpose Default
path string Indicates to load the class script from the specified file
assembly string Indicates to load the class assembly from the specified DLL
class string Indicates the name of the class to use
plugin string Indicates to load the class from the plugin alias defined in Plug-ins

Example

<tableunload name="UNLOAD.DB2" db="DB2" repository="TMPA">
	<table>IANUS.TEST01</table>
	<file recfmt="fixed" reclen="*">TMP01.UNLOAD.DB2.ASCII</file> 
</tableunload>
<tableunload name="UNLOAD.ORA" db="ORA" repository="TMPA">
	<table>IANUS.TEST01</table>
	<file recfmt="fixed" reclen="*">TMP01.UNLOAD.ORA.ASCII</file>
	<columns>
		<column name="C12" type="binary"/>
		<column name="C61" length="3"/>
		<column name="C71" length="3"/>
		<column name="C71" length="3"/>
		<column name="C80" length="4"/>
		<column name="C91" length="2"/>
		<column name="PK3" length="4" type="integer"/>
	</columns>
</tableunload>

Status Codes

Status Status code Description
Ready -1 Task is initialized, but not yet started
Running -2 Task is running
Success 0 Task completed successfully
Warnings 1 Task completed with warnings, one or more rows have been skipped
Errors 2 Task completed with errors, copy interrupted
Aborted 9 Task cannot be executed