Specific Templates

This article describes the principal changes of Specific Templates.

The Template download section can be found at this page

 

This article is dedicated to Stambia DI S17, S18 and S19.

If you are using Stambia DI S20 please refer to this article.

 

Templates.Specific.2021-04-30

Fix the folder organization of the specific Templates

In the previous version the organization of the folders of the specific templates unexpectedly changed, which was causing some issues when using versionning systems for instance.

This has been fixed in this version, which put back the correct folder structure.

 

Templates.Specific.2020-06-25

PostgreSQL Templates

INTEGRATION PostgreSQL

Fix issue with index name

When delimiters were required on the temporary table, an incorrect index name was generated.

 

Fix issue with CDC unlock step

CDC unlock step had a wrong generation condition which avoided it to be executed.

 

SAP IQ (Sybase IQ) Templates

INTEGRATION Sybase IQ

Adding a new option "Ignore Conversion Error" to ignore data conversion error and write a NULL value instead of the erroneous value.

 

 

Templates.Specific.2019-10-02

Oracle Templates

INTEGRATION Oracle

We noticed a duplicated internal id for one of the Template's steps.

We fixed this to avoid any generation issue which might happen.

 

Templates.Specific.2019-08-09

REJECT Templates

Rejects rules with warning severity level fixed in some Templates

Reject rules which are defined with "warning" severity level in Metadata should not be removed from the flow and should be loaded in target table.

Some Reject Templates were unexpectedly removing rejected rows with "warning" severity level from the flow, which was wrong, and which has been fixed from this version.

Rows which does not fulfill a rule which has been specified with "warning" severity level will not be kept in the flow and loaded into target table.

The following Templates had the issue and have been fixed:

• REJECT DB2-UDB
• REJECT MySQL
• REJECT Oracle
• REJECT PostgreSQL
• REJECT SybaseIQ

 

Alternate Key check was not performed on correct columns

Some Reject Templates were performing Alternate Keys checks on all unique keys instead of Alternate Keys defined in Metadata.

The following Templates had the issue and have been fixed:

• REJECT SybaseIQ
• REJECT MySQL

 

SybaseIQ Reject Template was unexpectedly processing rejected rows of previous sessions

There was an issue with SybaseIQ Reject Template which was processing rejected rows of previous executions instead of only rejected rows of the current execution.

This avoided rows which were corrected to be loaded again, as they were considered as rejected in previous executions.

 

INTEGRATION DB2-UDB

A new parameter "Filter Rejects On Checked Table" has been added to manage the filtering of rejects on temporary tables.

This allows to force filtering rejected rows when doing the integration step.

Refer to parameter's documentation for further information.

 

INTEGRATION PostgreSQL

New options "Deletion Strategy"

A new option "Deletion Strategy" has been added to manage how deletion of rows in target table is performed when integrating data.

Refer to parameter's documentation for further information.

 

New option "Deletion Were Clause"

A new option "Deletion Were Clause" has been added to define the where clause when using "where clause" deletion strategy.

Refer to parameter's documentation for further information.

 

New option "Useless Update Detection Method"

A new option "Useless Update Detection Method" has been added to define how source and target data are compared to detect the changes and define which row should be inserted / updated.

Refer to parameter's documentation for further information.

 

Analyze steps now performed in transaction

All steps performing an analyze of work / target tables are now executed in the same transaction as all other statements when transactional mode is enabled, to make sure it will impact correct data.

On previous versions this was done in autocommit mode, which will therefore not process rows which are in work / target tables when being in transactional mode.

 

Truncate statement now performed in transaction

Step performing a truncation of target table is now executed in the same transaction as all other statements when transactional mode is enabled, to make sure it will impact correct data and that the next statements will work on correct data.

On previous versions this was done in autocommit mode, which could therefore cause issues when having other steps in transaction mode after the truncation.

 

INTEGRATION MSSQL

New parameter "Transaction Isolation Level"

A new parameter "Transaction Isolation Level" has been added to allow customizing the transaction isolation level when transactional mode is enabled.

Refer to parameter's documentation for further information.

 

Temporary work table is now created when using "key" deletion strategy with append mode

Temporary work table was not created when using "key" deletion strategy together with append mode.

 

STAGING MSSQL as Table and STAGING MSSQL as View

New parameter "Transaction Isolation Level"

A new parameter "Transaction Isolation Level" has been added on both Templates to allow customizing the transaction isolation level when transactional mode is enabled.

Refer to parameter's documentation for further information.

 

LOAD File to MSSQL

Package generation issue when Header Line Position is externalized

When externalizing "Header Line Position" in a File Metadata, the generation of a Package from a Mapping / Process using LOAD File to MSSQL was unexpectedly throwing an error.

This issue has been fixed in this version.

 

Templates.Specific.2019-06-11

REJECT Oracle

A new parameter named 'Delete Previous Rejects' has been added to allow managing the deletion of previous rejects.

Refer to parameter's documentation for further information.

 

Templates.Specific.2019-06-10

LOAD File to Sybase IQ

A new Template offering the possibility to load data from a database into Sybase IQ using Sybase's specific loader is now available!

Using "LOAD TABLE" statement, the new "LOAD File to Sybase IQ" Template allows to optimize the loading step through the use of specific loader.

Refer to Template and parameters documentation for further information.

 

Templates.Specific.2019-04-17

INTEGRATION MySQL and REJECT MySQL

Creation of pre-integration and reject tables is now performed with same charset as the one defined in Metadata for target table.

This helps to fix issues when working with tables with different charset than default database charset.

Note that table charset attribute can be defined and reversed in table Metadata from Designer S19.0.18 and higher, which is therefore the prerequisites to benefit of this modification.

 

Templates.Specific.2019-02-21

INTEGRATION Sybase IQ

Drop of temporary integration objects fixed

Step which drops the integration table created during integration was not executed because of an incorrect link condition.

This has been fixed and this drop step is now correctly executed at the end of the integration.

 

Templates.Specific.2019-02-08

Recycling of previous rejects fixed

When using the option to recycle the rejects of previous execution on Integration Templates an extra step is executed to add those previous rejects in the integration flow.

This step had several issues which avoided it to work properly and which have been fixed in this version.

Moreover, possible duplicates while retrieving those rejects are now filtered using DISTINCT keyword.

Those issues were existing in multiple Integration Templates which have all been fixed.

 

Templates.Specific.2019-02-05

LOAD XML to Oracle (with SQL Loader)

A new Oracle specific Template has been added to perform data loading from an XML file to Oracle using SQL*Loader.

Offering better performances than standard XML loading, this Template also supports loading multiple sources XML files at the same time, which will each be loaded in a dedicated SQL*Loader execution.

 

Templates.Specific.2019-01-16

INTEGRATION File (Mssql Export with BCP)

This new Microsoft SQL Server specific Template has been added to export data from a Microsoft SQL Server table into a file using BCP utility.

When exporting data from Microsoft SQL Server into a file in a Mapping, simply select this Template for performing the export using BCP, which offers better performances when working with large set of data.

For further information do not hesitate to consult the Template's documentation.

 

LOAD File to Mssql / INTEGRATION File to Mssql

These two Templates, which were using Microsoft SQL Server BULK statement, has been updated to also support using the BCP utility.

New parameters have been added to define which method should be used.

 

REJECT Oracle

Oracle Reject Template has been updated to offer better performances on Alternate Keys (AK) controls.

 

Templates.Specific.2019-01-07

INTEGRATION Mssql

Column comparison modified for ntext and image datatypes

All the queries performing columns comparison have been updated to take into account the particularities of columns of "ntext" and "image" datatypes.

These datatypes require the comparison to be done with the 'LIKE' operator instead of the '=' one.

When using those columns, the comparisons in the Template were not correct as they were using the '=' operator.

Queries which perform column comparison will now use 'LIKE' keywork when working with "ntext" and "image" datatypes.

 

Optimization

When defining "Deletion Strategy" parameter to "all rows" in a Mapping, all incremental update steps will not be performed anymore by the Template.

They will be automatically disabled and not executed when using this option as there will never be any updates to perform.

As a reminder this option is used to remove all rows from target table before processing.

 

Templates.Specific.2018-12-07

LOAD File to Oracle (with Loader)

When using external table mode, the create table statement for the external table now computes the size of source fields and then specifies it if needed in the statement.

The size is retrieved from the source file Metadata and is specified when it exceed 255 (the default value Oracle is using for fields in external table when no size is specified.)

This allows to load files with large fields.

 

Templates.Specific.2018-12-05

INTEGRATION DB2-UDB and STAGING DB2-UDB As Table

New parameters to configure the transactional mode used for operations on work table and target table

Two parameters have been added to choose the transactional mode that should be used by the Templates when performing operations on work tables and target tables:

'Transactional Mode On Work Tables' and 'Transactional Mode On Target'

The mode used on each action inside the Template has been revised to be more consistent, as some actions were not in the same mode between each other.

 

New parameter to add options on the work table creation statement

A new parameter called 'Work Table Option' has been added, which offers the possibility to add additional options on the crate table statement of the work tables.

It can be helpful for specifying the 'NOT LOGGED INITIALLY' option for instance, which reduce all the logging performed by DB2-UDB when doing operations on the work tables.

 

Templates.Specific.2018-10-26

INTEGRATION File to Mssql (with BULK) and LOAD File to Mssql (with BULK).proc

When using the dynamic method to load the file, the automatic calculation of columns based on the file's header was not using the row separator, field separator, and charset defined in the File Metadata.

This issue has been fixed and the steps calculating the columns will now use those when reading the file's header.

 

REJECT Mssql

A new REJECT Mssql Template has been added with the Microsoft SQL Server Templates.

This template handles the Microsoft SQL Server specificities, such as using the fully qualified name for the columns, and supporting having delimiters around the index name.

 

Templates.Specific.2018-10-22

INTEGRATION File to Mssql (with BULK)

A new INTEGRATION File to Mssql (with BULK) Template has been added with the Microsoft SQL Server Templates.

This Template offers the possibility to BULK load a file directly into a target table without going through temporary work objects.

This can be useful on situations where there is no need for transformations, rejects or updates detection.

 

INTEGRATION Sybase IQ with Slowly Changing Dimension

Several issues on the Sybase Template dedicated to Slowly Changing Dimension has been fixed.

There were some situations where the generated queries were incorrect such as when having only columns with historizedIfModfied mode, or only with updateIfModfied mode.

There were also actions unexpectedly executed in situations when it was not necessary.

All those behaviors have been fixed in this updated version.

 

Templates.Specific.2018-10-11

INTEGRATION Mssql

All the queries performing columns comparison have been updated to take into account the particularities of columns of "text" datatype.

The text datatype, which has been defined has deprecated by Microsoft requires the comparison to be done with the 'LIKE' operator instead of the '=' one.

When using those columns, the comparisons in the Template were not correct as they were using the '=' operator.

This has been fixed and the comparisons now acts differently for columns of type "text".

 

LOAD File to Mssql (with BULK)

This template has been updated to support loading files from which columns order and number can vary between each file.

A new parameter called File format Method has been added for the user to be allowed to change how the SQL Server format file containing the list of columns to load should be calculated.

This parameter supports two values:

• 'from_metadata' which corresponds to the method previously used by the Template which calculates at the generation of the delivery the columns to load based on the source File Metadata
• 'from_file_dynamic' which corresponds to the new method calculating automatically at the execution of the delivery the columns order and number based on the file itself

The new method allows to load files from which the columns may not always be at the same position and which can contain additional columns.

The template will dynamically find the columns order from the first line of the file which is expected to be the header of the file containing the columns list.

This will then be used to generate the correct format file with the correct column order, ignoring the additional columns which are not originally in the source Metadata.

This method is supported for delimited files only.

 

Informix Templates moved to a standalone package

The Informix Templates which were previously shipped with the Specific Templates package have been separated in their own package for clarity.

You can now find them in their own package in the Template's download page, and find their release notes at this page.

 

Templates.Specific.2018-09-21

REJECT Oracle

The way alternate keys (unique constraints) are verified while using the Oracle Reject template has been updated to match exactly the rules defined in Oracle reference documentation.

Most notably for the detection and handling of null values in the columns of the constraint.

For instance, when two rows have identical data for the columns of the constraint and one of those columns contains a null value in both rows, the rows are now correctly rejected, as null values are not considered to satisfy uniqueness except when all the columns of the constraint are null.

Refer to the Oracle documentation and documentation of the Template for further information.