Table Scan

The table scan task (tablescan) is a convenient task to search the content of a table. It can performs the following actions:

  • Counts the rows in the table
  • Counts the rows matching the where filter provided
  • Identify DBCS characters issues, by searching all CHAR/VARCHAR fields for the replacement char �. If the report attribute is provided, an Excel report is produced.
  • Apply a ColumnMatcher class to identify row matching the user provided criteria

By default the task scans all columns, but can be limited to subset of the columns (exclude element)

The table scan task is defined by the XML element tablescan 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
dirtydbcs bool Enables the search for DBCS charcater issues false
report string Full path of the analysis Excel report
bitdata string Defines how the BIT DATA columns are displayed in the report (hex, ascii, ebcdic) hex

The following elements are used to configure the unload:

Element Purpose
table Fully qualified name of the table to be loaded
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
filter Defines additional filtering on all colums. It can complements the where clause.
cast Defines optional column and value manipulation and casting
match Defines a custom match routine for specific columns

Table

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

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.

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.

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>

Filter

The filter element defines the column based filters. It is defined like the cast attribute.

Match

The match element sets up custom match routine for the table column(s) matching all the provided criteria.

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 user provided conversion routine must implement the interface HPE.Ianus.Scripting.IColumnMatcher. For a detailed description of the comparison class please refer to Column Matcher .

The following attributes are used to configure the compare element:

Attribute Type Purpose Default
sourcename string Name of the source column
sourcetype string SQL type of the source column
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 the Ianus configuration file

Example

<!-- counts all rows where PK2='A' -->
<tablescan name="SCAN01" db="QS">
	<table>dbo.TEST01x</table>
	<where>PK2='A'</where>
</tablescan>
<!-- counst all rows where any CHAR/VARCHAR column contains 'Ringo', 'George', 'John' or 'Paul'. -->
<tablescan name="SCAN01" db="QS">
	<table>dbo.TEST01x</table>
	<filter type="type" arg="CHAR">{name} in ('Ringo', 'George', 'John', 'Paul')</filter>
</tablescan>
<!-- scans for dirty DBCS and generates the report -->
<tablescan name="SCAN01" db="QS" dirtydbcs="true" report="C:\tmp\logs\TEST_DBCS_BIN.xlsx">
	<table>dbo.TEST01x</table>
</tablescan>
<!-- scans using the provided Matcher and generates the report -->
<tablescan name="SCAN01" db="QS" report="C:\tmp\logs\TEST_MATCHER.xlsx">
	<table>dbo.test_match_01</table>
	<cast type="name" arg="COL3" side="source">UPPER({name})</cast>
	<match sourcename="COL3"
			assembly="%IANUS_HOME%\Samples\ColumnMatchers\bin\Debug\%IANUS_DOTNET_VERSION%\ColumnMatchers.dll"
		   class="ColumnMatchers.MatchText"
    />
</tablescan>

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 

The table unload task (tableunload) exports the data stored in a table to a sequential file, with a format similar to the one generated by DB2 UNLOAD command.

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

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

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.

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 Element

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

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>

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