Design Guide

10.0
- 10.5 HotFix 2
- 10.5 HotFix 1
- 10.5
- 10.2 HotFix 1
- 10.2
- 10.1
- 10.0 HotFix 1

Back Next

User-Source-Table Section

The

User-Source-Table

(UST) section specifies how to build IDTs from User Source Tables or flat files. It is also used to define an SQL source as input data for relate.

An IDT can be built from:

a single UST

multiple USTs with a join operation

multiple USTs with a merge (union) operation

a (sequential) flat file

The IIR Table Loader can transform fields while extracting them from the data source. It can concatenate fields, insert text and change the case of the source fields, as described below.

The UST parser will automatically generate File and View definitions. There is no need to add anything to the

Files

Views

sections.

The User Source Tables must be accessible to the userid (referred to as the SSA userid) used by the IIR Table Loader. You must

GRANT SELECT

privileges to the

SSA

userid on the USTs.

For example, to grant select authority to the user

SSA

on the table

EMP

in SCOTT’s schema on the database named

server8.17

, you must

CONNECT scott/tiger@server8.17;
GRANT SELECT ON emp TO SSA;

General Syntax

The UST section uses an SQL-like syntax. The following general points should be noted:

lines beginning with two dashes "--" are treated as comments.

tokens can be substituted with the value of an environment variable by specifying the environment variable name surrounded by #s. eg the Source Schema could be specified as

#myschema#

. When parsed it would be substituted with the value of the environment variable

myschema

all definitions include either the

SYNC

NOSYNC

clause.

SYNC

specifies that the IDT must be synchronized with any changes to the UST(s). Specifying

SYNC

means that triggers will be created on the source table to enable the Update Synchronizer to reapply updates to the ID-Tables. Triggers are not created if the IDT is created from a flat file and/or the Source database does not support triggers.

NOSYNC

means that the IDT will not be synchronized with any changes made to the Source Tables.

each table definition is terminated by a semicolon.

multiple table definitions are permitted in this section.

Primary Keys

Primary Keys (PK) are used to establish a relationship between records in the USTs and IDT. When synchronized USTs are updated, the PK value(s) are passed to the Update Synchronizer to tell it which rows were changed. The PK values are provided by either

transactions created by triggers defined on the USTs, or

user created transactions stored in a "flat file".

Columns selected as Primary Keys must not contain any binary zero (NUL) characters in their value. Therefore binary columns, date / timestamp and/or character columns containing binary should not be used (e.g. W columns containing UTF-16 / UCS-2 data).

Furthermore, columns that have been converted to C(64), as listed in the following Source Data Types
section, can not be used as primary keys. These include the REAL, FLOAT, DOUBLE and NUMBER (with scale > 0) data types. In general, PK fields should be selected from CHAR, VARCHAR and/or NUMBER (scale = 0) columns.

Columns selected as Primary Keys must not contain any zero length data that are not NULL. Only DB2/UDB supports columns with this type of semantics.

Unique Primary Keys

Synchronization works most efficiently when PKs are unique. They are defined to be unique by specifying the (default) synchronization level of

reject_duplicate_pk

The nominated PK fields serve two purposes:

to define a unique PK for the IDT, and

to define a unique PK for the primary UST (defined below).

This is so that a delete from the primary UST can tell the Synchronizer which IDT record to delete. If the is not unique, the Synchronizer will delete multiple IDT records. If a UST record is added with a non-unique PK, the Synchronizer will report a " constraint violation".

Non-Unique Primary Keys

PKs do not need to be unique and are defined as such using the synchronization level of

allow_duplicate_pk

. However non-unique PKs are more expensive to synchronize, as more records will be processed.

Non-Synchronized USTs

IDTs created from UST that are not synchronized must still define a PK field, even though there is no link maintained between the USTs and IDT.

Rules

In the following paragraphs the term "must/may" is used and should be read as "must" when using unique PKs, and "may" when using non-unique PKs.

The

primary

UST is the table that is used to source the first primary key column for the IDT. The primary UST must/may contain a unique primary key. It may be a compound primary key.

Each IDT record must/may contain a unique primary key. The

sourced_from

clause is used to nominate the source column(s) which form the PK. The first

keys fields (

PK1..PKm

) specify the primary keys for the primary UST.

If the IDT is created by joining the primary to a secondary table and there is a one to many (1:M) relationship between them, there will be multiple rows in the IDT for each unique value. In this case, you should define additional columns (

PKm+1..PKn

) which when concatenated to

PK1..m

will form a unique key for each IDT record.

When merging tables, the primary key must be unique within a given User Source Table but does not need to be unique within the IDT because
IIR automatically adds an additional qualifier to each IDT record.

In general, IIR allows the PK to be composed of up to 36 columns. However, the number of columns permitted in a composite index may be further limited by the host DBMS. For example on UDB the limit is 10 columns.

All IDT’s will have a generated two byte field call

CL_ID

. This field is used in Cluster governance. For normal IDT records this field will be left unpopulated.

Define_Source (relate input)

Sourcing from Microsoft Excel

Download Guide

Watch