Table Load

The table unload task (tableload) imports data from a sequential file to a table, using a format compatible with DSNTIAUL .

By default the task load all columns, but can be limited to:

a subset of the columns (exclude element)

The table copy task can operate in two modes:

Native: data copy is performed with a traditional read/insert loop
Bulk: data copy is completely delegated to the database Bulk Copy routine. This mode is available only when the target table is on SQL Server, DB2 or Oracle

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`
mode	string	Copy mode: `native` or `bulk`. Refer to (copy modes)[copy-modes]	`native`
truncate	bool	Delete data in the target table before import.	`false`
commit	integer	Perform a commit every N rows	1000
verbose	bool	Writes a log message every set of rows written	`false`
identity	bool	Disables IDENTITY before to start writing the target table	`false`
stoponerror	bool	Stop at the first error encountered (for example, a duplicated row)	`false`
format	string	Defines the file format (dsntiaul or unload)	`dsntiaul`
nopad	bool	If set to `nopad`, variable length fields (VARCHAR/VARBINARY) occupy the actual data length without additional padding	`false`
charasbinary	bool	If set to `true` CHAR/VARCHAR columns are treated as BINARY. See char as binary	`false`
partition	string	Defines the name of the destination table partition to lock (Oracle only)
converterstohex	bool	Turns the layout and plugin converter output into a hex string	`false`

The following elements are used to configure the unload:

Element	Purpose
table	Fully qualified name of the table to be loaded
file	Name and attributes of the input file
columns	Alters the charactristics of the input fields for each columns
where	Defines the row selection criteria. If omitted, all rows are loaded
exclude	Defines the regular expression based column exclusion criteria, if omitted no column is excluded
cast	Defines optional column and value manipulation and casting
syspunch	Provides DB2 LOAD statements (as produced by DB2 utilities) with input fields characteristics definitions. See SYSPUNCH.

Copy Mode

The table copy task can operate in two modes:

Native: data copy is performed with a traditional read/insert loop
Bulk: data copy is completely delegated to the database Bulk Copy routine. This mode is available only when the target table is on SQL Server, DB2 or Oracle

Table

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

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 input 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`

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`

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 one or more layouts 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.

In case you have the need to convert a column containing binary data, either with a layout converter of your own converter plugin, and the target data is not of binary data, you may consider setting the converterstohex attribute to true and handle the hex string with a proper cast.

Note

Forcing the HEX value in the column with a cast works only in native mode, as it is the only mode that actually executest a SQL INSERT.

    <tablecopy name="COPYCONV" source="DB2" target="ORA" 
               truncate="true" mode="native" converterstohex="true">
        <source bitdatacodepage="ENUS037">IANUS.SATELLITES</source>
        <target>IANUS.SATELLITES</target>
        <cast type="name" arg="^SATELLITE$" 
              side="source">CAST({name} AS VARCHAR(100) FOR BIT DATA)</cast>
        <cast type="name" arg="^SATELLITE$" 
              side="target">utl_raw.cast_to_varchar2(:{name})</cast>
        <convert sourcename="SATELLITE" plugin="SatelliteColumnConverter"/>
    </tablecopy>
    <tablecopy name="NFDB20.PROBE.ORA" source="DB2E" target="ORA" delete="truncate" enabled="true" mode="native" batchsize="250000" oraemptyvarcharfix="false" first="100" >
		<source bitdatacodepage="IBM1145_WIN1252">IANUS.CONVERT_CHARASBINARY</source>
		<target>IANUS.CONVERT_CHARASBINARY</target>
		<cast type="name" arg="TEXT" side="right">UTL_RAW.CAST_TO_VARCHAR2(:{name})</cast>
		<convert mode="charasbinary" sourcetype="VARCHAR" codepage="IBM1145_WIN1252" tohex="true"/>
	</tablecopy>
	<tablecopy name="NFDB20.PROBE.ORA" source="DB2E" target="ORA" delete="truncate" enabled="true" mode="native" batchsize="250000" oraemptyvarcharfix="false" first="100" >
		<source layoutcodepage="IBM1145_WIN1252">IANUS.CONVERT_CHARASBINARY</source>
		<target>IANUS.CONVERT_CHARASBINARY_B</target>
		<layouts>
			<layout type="COBOL" format="free" explicitconversion="true">
				01  SYS053-DESCR.
				03  FIELD02    PIC X(16).
			</layout>
		</layouts>
		<cast type="name" arg="TEXT" side="right">UTL_RAW.CAST_TO_VARCHAR2(:{name})</cast>
		<convert mode="layout" sourcetype="VARCHAR" codepage="IBM1145_WIN1252" tohex="true" />
	</tablecopy>

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
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

Input file format

The tableload task supports two major file formats:

DSNTIAUL: the file format is the same as produced by IBM DB2 DSNTIAUL utility.
UNLOAD: the file format is the same as produced by IBM DB2 UNLOAD command. When the format is set to unload, the nopad attribute indicates that variable columns are handled with theire actual length.

Besides, the headerlen attribute indicates the length of record header.

SYSPUNCH

LOAD statements produced in the DSNTIAL or UNLOAD syspunch can be used to provide additional information to the load process.

Such information are normally not necessary when loading into DB2, but may become key when when loading into Oracle or SQL server. Indeed, while on DB2 LUW target columns are supposed to match exactly the source types and length, on the other databases it might not be the case. So providing a SYSPUNCH avoids to defined column definitions (like in the examples below).

Note

SYSPUNCH is not supported for NOPAD input files

The syspunch data could be included directly or with an include.

SYSPUNCH example

	<tableload name ="LOAD.SQL.DSNTIAUL.SYSPUNCH" db="QS" repository="REPOEBCDIC" truncate="true" mode="native" enabled="true" format="dsntiaul">
		<syspunch>
			<include type="text">IANUS.UNLOAD.DSNTIAUL.txt</include>
		</syspunch>
		<table>dbo.TEST01_LOAD_UNLOAD</table>
		<file recfmt="fixed" reclen="10000">IANUS.UNLOAD.DSNTIAUL.DAT</file>
	</tableload>

Examples

Generic Example

<tableload name ="TACZP02_NORMALIZED" db="DB2" repository="REPOBGLE" truncate="true" mode="native">
	<table bitdatacodepage="ENUS037">IANUS.TACZP02_NORMALIZED</table>
	<file recfmt="fixed" reclen="*">UNLOAD.TACZP02.EBCDIC.DAT</file>
</tableload>
<tableload name ="TACZP02_GENERATED" db="DB2" repository="REPOBGLE" truncate="true" mode="native">
	<table bitdatacodepage="ENUS037">IANUS.TACZP02_GENERATED</table>
	<file recfmt="fixed" reclen="*">UNLOAD.TACZP02.EBCDIC.DAT</file>
	<exclude type="name">TS_MAJ_AUT_DB2</exclude>
</tableload>
<tableload name="LOAD.ORA" db="ORA" repository="TMPA" truncate="true" mode="bulk">
	<table>IANUS.TEST01_LOAD_UNLOAD</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>
</tableload>