X hits on this document

369 views

0 shares

1 downloads

0 comments

8 / 131

Generic SQL

Knowledge Modules

Knowledge Modules in this section apply to most popular SQL compliant databases, including Oracle, Microsoft SQL Server, Sybase ASE, IBM DB2, Teradata, PostgreSQL, MySQL, Derby etc. Additional Knowledge Modules are also provided for some of these particular databases to leverage the specific SQL and powerful loader utilities to maximize performance.

Type

Knowledge Module

Description

Check

CKM SQL

Checks data integrity against constraints defined on a Datastore. Rejects invalid records in the error table created dynamically. Can be used for static controls as well as flow controls.

Integrate

Consider using this KM if you plan to check data integrity on a SQL compliant database. Use specific CKMs instead if available for your database.

Integrate

Knowledge Modules Reference Guide

8/131

IKM SQL Control Append

Integrates data in any SQL compliant target table in replace/append mode. When flow data needs to be checked using a CKM, this IKM creates a temporary staging table before invoking the CKM.

Consider using this IKM if you plan to load your SQL compliant target table in replace mode, with or without data integrity check.

To use this IKM, the staging area must be on the same data server as the target.

To use this IKM, the staging area must be on the same data server as the target.

Integrate

Consider using this KM if you plan to load your SQL compliant target table to insert missing records and to update existing ones. Use specific incremental update IKMs whenever possible as they are more optimized for performance.

IKM SQL Incremental Update (row by row)

Integrates data in any AINSI-SQL92 compliant database target table in incremental update mode with row by row logging. This KM is similar to the IKM SQL Incremental Update, and indicates in addition the state of each processed row. A log file can be created to record

IKM SQL Incremental Update

Integrates data in any SQL compliant target table in incremental update mode. This KM creates a temporary staging table to stage the data flow. It then compares its content to the target table to guess which records should be inserted and which others should be updated. It also allows performing data integrity check by invoking the CKM. Because not all databases support the same bulk update syntax, updates are done row by row. This KM is therefore not recommended for large volumes of data.

Document info
Document views369
Page views372
Page last viewedTue Dec 06 14:41:35 UTC 2016
Pages131
Paragraphs3942
Words42855

Comments