A.2 Substitution Methods Reference

This section provides an alphabetical list of the substitution methods. Each method is detailed with usage, description, parameters and example code.

A.2.1 getAK() Method

Use to return information about an alternate key.

Usage

public java.lang.String getAK(java.lang.String pPropertyName)

Description

This method returns information relative to the alternate key of a datastore during a check procedure. It is only accessible from a Check Knowledge Module if the current task is tagged "alternate key".

In an action, this method returns information related to the alternate key currently handled by the DDL command.

Parameters

Parameter

Type

Description

pPropertyName

String

String containing the name of the requested property.

The following table lists the different possible values for pPropertyName.

Returns a list of columns and expressions for the alternate key currently checked.

The pPattern parameter is interpreted and then repeated for each element of the list. It is separated from its predecessor by the pSeparator parameter. The generated string starts with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains an element for each column of the current alternate key. It is accessible from a Check Knowledge Module if the current task is tagged as an "alternate key".

In an action, this method returns the list of the columns of the alternate key handled by the DDL command, ordered by their position in the key.

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameters

Type

Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of attributes that can be used in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern sequence is replaced with its value. The attributes must be between brackets. ([ and])

Example «My string [COL_NAME] is a column»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.

Pattern Attributes List

The following table lists the different values of the parameters as well as their associated description.

Parameter Value

Description

Parameter value

Description

I_COL

Column internal identifier

COL_NAME

Name of the key column

COL_HEADING

Header of the key column

COL_DESC

Column description

POS

Position of the column

LONGC

Length (Precision) of the column

SCALE

Scale of the column

FILE_POS

Beginning position of the column (fixed file)

BYTES

Number of physical bytes of the column

FILE_END_POS

End of the column (FILE_POS + BYTES)

IND_WRITE

Write right flag of the column

COL_MANDATORY

Mandatory character of the column:

0: null authorized

1: non null

CHECK_FLOW

Flow control flag of the column:

0: do not check

1: check

CHECK_STAT

Static control flag of the column:

0: do not check

1: check

COL_FORMAT

Logical format of the column

COL_DEC_SEP

Decimal symbol for the column

REC_CODE_LIST

List of the record codes retained for the column

COL_NULL_IF_ERR

Processing flag for the column:

0: Reject

1: Set active trace to null

2: Set inactive trace to null

DEF_VALUE

Default value for the column

EXPRESSION

Not used

CX_COL_NAME

Not used

ALIAS_SEP

Grouping symbol used for the alias (from the technology)

SOURCE_DT

Code of the column's datatype.

SOURCE_CRE_DT

Create table syntax for the column's datatype.

SOURCE_WRI_DT

Create table syntax for the column's writable datatype.

DEST_DT

Code of the column's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the column's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the column's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this column in the data model.

<flexfield code>

Flexfield value for the current column.

Examples

If the CUSTOMER table has an alternate key AK_CUSTOMER (CUST_ID, CUST_NAME) and you want to generate the following code:

Explanation: the getAKColList function will be used to generate the (CUST_ID numeric(10) not null, CUST_NAME varchar(50) not null) part, which starts and stops with a parenthesis and repeats the pattern (column, a data type, and not null) separated by commas for each column of the alternate key. Thus

the first parameter "(" of the function indicates that we want to start the string with the string "("

the second parameter "[COL_NAME] [DEST_CRE_DT] not null" indicates that we want to repeat this pattern for each column of the alternate key. The keywords [COL_NAME] and [DEST_CRE_DT] reference valid keywords of the Pattern Attributes List table

the third parameter ", " indicates that we want to separate interpreted occurrences of the pattern with the string ", "

the fourth parameter ")" of the function indicates that we want to end the string with the string ")"

A.2.3 getAllTargetColList() Method

Use to return information about all columns of the target table of an interface, including active and non-active columns. Active columns are those having an active mapping.

Allows you to retrieve the name of the default physical data catalog or work catalog for the data server to which is associated the physical schema corresponding to the tuple (logical schema, context). If no context is specified, the current context is used. If no logical schema name is specified, then the current logical schema is used. If no pLocation is specified, then the data catalog is returned.

Parameters

Parameter

Type

Description

pLogicalSchemaName

String

Name of the logical schema

pContextCode

String

Code of the enforced context of the schema

pLocation

String

The valid values are:

W: Returns the work catalog of the default physical schema associate to the data server to which the physical schema corresponding to the tuple (context, logical schema) is also attached.

D: Returns the data catalog of the physical schema corresponding to the tuple (context, logical schema)

Examples

If you have defined the physical schema Pluton.db_odi.dbo

Data catalog:

db_odi

Data schema:

dbo

Work catalog:

tempdb

Work schema:

temp_odi

Default Schema

Yes

that you have associated with this physical schema: MSSQL_ODI in the context CTX_DEV, and Pluton.db_doc.doc

Data catalog:

db_doc

Data schema:

doc

Work catalog:

tempdb

Work schema:

temp_doc

Default Schema

No

that you have associated with this physical schema: MSSQL_DOC in the context CTX_DEV.

The Call To

Returns

<%=odiRef.getCatalogNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "W")%>

tempdb

<%=odiRef.getCatalogNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "D")%>

db_odi

A.2.6 getCK() Method

Use to return information about a condition.

Usage

public java.lang.String getCK(java.lang.String pPropertyName)

Description

This method returns information relative to a condition of a datastore during a check procedure. It is accessible from a Check Knowledge Module only if the current task is tagged as "condition".

In an action, this method returns information related to the check constraint currently handled by the DDL command.

Parameters

Parameter

Type

Description

pPropertyName

String

Current string containing the name of the requested property.

The following table lists the different values accepted by pPropertyName:

Parameter Value

Description

ID

Internal number of the check constraint.

COND_ALIAS

Alias of the table used in the SQL statement

COND_NAME

Name of the condition

COND_TYPE

Type of the condition

COND_SQL

SQL statement of the condition

MESS

Error message relative to the check constraint

FULL_NAME

Full name of the check constraint generated with the local object mask.

COND_SQL_DDL

SQL statement of the condition with no table alias.

<flexfield code>

Flexfield value for this check constraint.

Examples

The current condition is called: <%=snpRep.getCK("COND_NAME")%>
insert into MY_ERROR_TABLE
select *
from MY_CHECKED_TABLE
where (not (<%=odiRef.getCK("COND_SQL")%>))

A.2.7 getColDefaultValue() Method

Use to return the default value of a mapped column.

Usage

public java.lang.String getColDefaultValue()

Description

Returns the default value of the target column of the mapping.

This method can be used in a mapping expression without the <%%> tags. This method call will insert in the generate code the default value set in the column definition. Depending on the column type, this value should be protected with quotes.

Parameters

None.

Examples

The default value of my target column is '+ 'odiRef.getColDefaultValue()'

A.2.8 getColList() Method

Use to return properties for each column from a filtered list of columns. The properties are organized according to a string pattern.

Returns a list of columns and expressions for a given dataset. The columns list depends on the phase during which this method is called.

In IKMs only, In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The pPattern parameter is interpreted and then repeated for each element of the list (selected according to pSelector parameter) and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

In the alternative syntax, any parameters not set are set to an empty string.

Note that this method automatically generates lookups with no specific code required.

Loading (LKM)

All active mapping expressions that are executed in the current source set, as well as all the columns from the current source set used in the mapping, filters and joins expressions executed in the staging area appear in this list. The list is sorted by POS, FILE_POS.

If there is a journalized datastore in the source of the interface, the three journalizing pseudo columns JRN_FLAG, JRN_DATE, and JRN_SUBSCRIBER are added as columns of the journalized source datastore.

Integration (IKM)

All current active mapping expressionsin the current interface appear in the list.

The list contains one element for each column that is loaded in the target table of the current interface. The list is sorted by POS, FILE_POS, except when the target table is temporary. In this case it is not sorted.

If there is a journalized datastore in the source of the interface, and it is located in the staging area, the three journalizing pseudo columns JRN_FLG, JRN_DATE,and JRN_SUBSCRIBER are added as columns of the journalized source datastore.

Check (CKM)

All the columns of the target table (with static or flow control) appear in this list.

To distinguish columns mapped in the current interface, you must use the MAP selector.

Actions

All the columns of the table handles by the DDL command appear in this list.

In the case of modified, added or deleted columns, the NEW and OLD selectors are used to retrieve either the new version of the old version of the modified column being processed by the DDL command. The list is sorted by POS, FILE_POS when the table loaded is not temporary.

Parameters

Parameter

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of the attributes usable in a pattern is detailed in the Pattern Attributes List below.

Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a column»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.

pSelector

String

String that designates a Boolean expression that allows to filter the elements of the initial list with the following format:

<SELECTOR> <Operator> <SELECTOR> etc. Parenthesis are authorized.

Authorized operators:

No: NOT or!

Or: OR or ||

And: AND or &&

Example: (INS AND UPD) OR TRG

The description of valid selectors is provided below.

Pattern Attributes List

The following table lists different parameters values as well as their associated description.

Parameter Value

Description

I_COL

Internal identifier of the column

COL_NAME

Name of the column

COL_HEADING

Header of the column

COL_DESC

Description of the column

POS

Position of the column

LONGC

Column length (Precision)

SCALE

Scale of the column

FILE_POS

Beginning (index) of the column

BYTES

Number of physical bytes in the column

FILE_END_POS

End of the column (FILE_POS + BYTES)

IND_WRITE

Write right flag of the column

COL_MANDATORY

Mandatory character of the column. Valid values are:

0: null authorized

1: not null

CHECK_FLOW

Flow control flag of the column. Valid values are:

0: do not check

1: check

CHECK_STAT

Static control flag of the column. Valid values are:

0: do not check

1: check

COL_FORMAT

Logical format of the column

COL_DEC_SEP

Decimal symbol of the column

REC_CODE_LIST

List of the record codes retained in the column

COL_NULL_IF_ERR

Processing flag of the column. Valid values are:

0: Reject

1: Set to null active trace

2: Set to null inactive trace

DEF_VALUE

Default value of the column

EXPRESSION

Text of the expression executed on the source (expression as typed in the mapping or column name making an expression executed on the staging area).

CX_COL_NAME

Computed name of the column used as a container for the current expression on the staging area

ALIAS_SEP

Separator used for the alias (from the technology)

SOURCE_DT

Code of the column's datatype.

SOURCE_CRE_DT

Create table syntax for the column's datatype.

SOURCE_WRI_DT

Create table syntax for the column's writable datatype.

DEST_DT

Code of the column's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the column's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the column's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this column in the data model.

MANDATORY_CLAUSE

Returns NOT NULL is the column is mandatory. Otherwise, returns the null keyword for the technology.

Actions: the column added to a table, the new version of the modified column of a table.

OLD

Actions: The column dropped from a table, the old version of the modified column of a table.

WS_INS

SKM: The column is flagged as allowing INSERT using Data Services.

WS_UPD

SKM: The column is flagged as allowing UDATE using Data Services.

WS_SEL

SKM: The column is flagged as allowing SELECT using Data Services.

Note:

Using certain selectors in an LKM - indicated in the previous table with an * - is possible but not recommended. Only columns mapped on the source in the interface are returned. As a consequence, the result could be incorrect depending on the interface. For example, for the UK selector, the columns of the key that are not mapped or that are not executed on the source will not be returned with the selector.

Examples

If the CUSTOMER table contains the columns (CUST_ID, CUST_NAME, AGE) and we want to generate the following code:

Explanation: the getColList function will be used to generate (CUST_ID numeric(10) null, CUST_NAME varchar(50) null, AGE numeric(3) null). It will start and end with a parenthesis and repeat a pattern (column, data type, and null) separated by commas for each column. Thus,

the first character "(" of the function indicates that we want to start the string with the string "("

the second parameter "[COL_NAME] [SOURCE_CRE_DT] null" indicates that we want to repeat this pattern for each column. The keywords [COL_NAME] and [SOURCE_CRE_DT] are references to valid keywords of the table Pattern Attribute List

the third parameter ", " indicates that we want to separate the interpreted occurrences of the pattern with the string ", ".

the fourth parameter ")" of the function indicates that we want to end the string with the string ")"

the last parameter "" indicates that we want to repeat the pattern for each column (with no selection)

A.2.9 getColumn() Method

Use to return information about a specific column handled by an action.

Returns a list of occurrences of the SQL filters of a given dataset of an interface.

In IKMs only, In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The parameter pPattern is interpreted and repeated for each element of the list and separated from its predecessor with parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains an element for each filter expression executed on the source or target (depending on the Knowledge Module in use).

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameter

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern will be repeated for each occurrence of the list.

The list of possible in a list is available in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. Attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a column»

pSeparator

String

This parameter is used to separate a pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.

Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Explanation: the getFilterList function will be used to generate the filter of the SELECT clause that must begin with "and" and repeats the pattern (expression of each filter) separated with "and" for each filter. Thus

The first parameter "and" of the function indicates that we want to start the string with the string "and"

the second parameter "([EXPRESSION])" indicates that we want to repeat this pattern for each filter. The keywords [EXPRESSION] references a valid keyword of the table Pattern Attribute List

the third parameter " and " indicates that we want to separate each interpreted occurrence of the pattern with the string "and ".

the fourth parameter "" of the function indicates that we want to end the string with no specific character.

A.2.16 getFK() Method

Use to return information about a foreign key.

Usage

public java.lang.String getFK(java.lang.String pPropertyName)

Description

This method returns information relative to the foreign key (or join or reference) of a datastore during a check procedure. It is accessible from a Knowledge Module only if the current task is tagged as a "reference".

In an action, this method returns information related to the foreign key currently handled by the DDL command.

Parameters

Parameter

Type

Description

pPropertyName

String

String containing the name of the requested property.

The following table lists the different possible values for pPropertyName.

Parameter Value

Description

ID

Internal number of the reference constraint.

FK_NAME

Name of the reference constraint.

FK_TYPE

Type of the reference constraint.

FK_ALIAS

Alias of the reference table (only used in case of a complex expression)

PK_ALIAS

Alias of the referenced table (only used in case of a complex expression)

ID_TABLE_PK

Internal number of the referenced table.

PK_I_MOD

Number of the referenced model.

PK_CATALOG

Catalog of the referenced table in the current context.

PK_SCHEMA

Physical schema of the referenced table in the current context.

PK_TABLE_NAME

Name of the referenced table.

COMPLEX_SQL

Complex SQL statement of the join clause (if appropriate).

MESS

Error message of the reference constraint

FULL_NAME

Full name of the foreign key generated with the local object mask.

<flexfield code>

Flexfield value for this reference.

Examples

The current reference key of my table is called: <%=odiRef.getFK("FK_NAME")%>. It references the table <%=odiRef.getFK("PK_TABLE_NAME")%> that is in the schema <%=odiRef.getFK("PK_SCHEMA")%>

Returns a list of columns part of a reference constraint (foreign key).

The parameter pPattern in interpreted and repeated for each element of the list, and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains one element for each column of the current foreign key. It is accessible from a Check Knowledge Module only if the current task is tagged as a "reference".

In an action, this method returns the list of the columns of the foreign key handled by the DDL command, ordered by their position in the key.

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameter

Type

Description

Parameter

Type

Description

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of possible attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a column»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to generate.

Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Parameter Value

Description

I_COL

Column internal identifier

COL_NAME

Name of the column of the key

COL_HEADING

Header of the column of the key

COL_DESC

Description of the column of the key

POS

Position of the column of the key

LONGC

Length (Precision) of the column of the key

SCALE

Scale of the column of the key

FILE_POS

Beginning (index) of the column

BYTES

Number of physical octets of the column

FILE_END_POS

End of the column (FILE_POS + BYTES)

IND_WRITE

Write right flag of the column

COL_MANDATORY

Mandatory character of the column. Valid values are:

0: not authorized

1: not null

CHECK_FLOW

Flow control flag of the column. Valid values are:

0: do not check

1: check

CHECK_STAT

Static control flag of the column. Valid values are:

0: do not check

1: check

COL_FORMAT

Logical format of the column

COL_DEC_SEP

Decimal symbol for the column

REC_CODE_LIST

List of the record codes for the column

COL_NULL_IF_ERR

Column processing flag. Valid values are:

0: Reject

1: Set active trace to null

2: Set inactive trace to null

DEF_VALUE

Default value of the column

EXPRESSION

Not used

CX_COL_NAME

Not used

ALIAS_SEP

Separator used for the alias (from the technology)

SOURCE_DT

Code of the column's datatype.

SOURCE_CRE_DT

Create table syntax for the column's datatype.

SOURCE_WRI_DT

Create table syntax for the column's writable datatype.

DEST_DT

Code of the column's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the column's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the column's writable datatype converted to a datatype on the target technology.

PK_I_COL

Internal identifier of the referenced column

PK_COL_NAME

Name of the referenced key column

PK_COL_HEADING

Header of the referenced key column

PK_COL_DESC

Description of the referenced key column

PK_POS

Position of the referenced column

PK_LONGC

Length of the referenced column

PK_SCALE

Precision of the referenced column

PK_FILE_POS

Beginning (index) of the referenced column

PK_BYTES

Number of physical octets of the referenced column

PK_FILE_END_POS

End of the referenced column (FILE_POS + BYTES)

PK_IND_WRITE

Write right flag of the referenced column

PK_COL_MANDATORY

Mandatory character of the referenced column. Valid values are:

0: null authorized

1: not null

PK_CHECK_FLOW

Flow control flag of the referenced column. Valid values are:

0: do not check

1: check

PK_CHECK_STAT

Static control flag of the referenced column. Valid values are:

0: do not check

1: check

PK_COL_FORMAT

Logical format of the referenced column

PK_COL_DEC_SEP

Decimal separator for the referenced column

PK_REC_CODE_LIST

List of record codes retained for the referenced column

PK_COL_NULL_IF_ERR

Processing flag of the referenced column. Valid values are:

0: Reject

1: Set active trace to null

2: Set inactive trace to null

PK_DEF_VALUE

Default value of the referenced column

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this column in the data model.

<flexfield code>

Flexfield value for the current column of the referencing table.

Examples

If the CUSTOMER table references the CITY table on CUSTOMER.COUNTRY_ID = CITY.ID_COUNT and CUSTOMER.CITY_ID = CITY.ID_CIT

Explanation: the getFKColList function will be used to loop on each column of the foreign key to generate the clause that begins and ends with a parenthesis and that repeats a pattern separated by and for each column in the foreign key. Thus

The first parameter "(" of the function indicates that we want to begin the string with "("

The second parameter "CUS.[COL_NAME] = CITY.[PK_COL_NAME]" indicates that we want to repeat this pattern for each column of the foreign key. The keywords [COL_NAME] and [PK_COL_NAME] reference valid keywords in the table Pattern Attributes List

The third parameter" and " indicates that we want to separate the occurrences of the pattern with the string " and ".

The fourth parameter ")" of the function indicates that we want to end the string with ")".

Internal Identifier of the Object Instance, as it appears in the version tab of the object instance window.

pI_Object

String

Internal Identifier of the Object type, as it appears in the version tab of the object window for the object type.

pPropertyName

String

Flexfield Code which value should be returned.

Examples

<%=odiRef.getFlexFieldValue("32001","2400","MY_DATASTORE_FIELD")%>

Returns the value of the flexfield MY_DATASTORE_FIELD, for the object instance of type datastore (Internal ID for datastores is 2400), with the internal ID 32001.

A.2.19 getFrom() Method

Use to return the SQL FROM clause in the given context.

Usage

public java.lang.String getFrom(java.lang.Int pDSIndex)

Description

Allows the retrieval of the SQL string of the FROM in the source SELECT clause for a given dataset. The FROM statement is built from tables and joins (and according to the SQL capabilities of the technologies) that are used in this dataset.

For a technology that supports ISO outer joins and parenthesis, getFrom() could return a string such as:

((CUSTOMER as CUS inner join CITY as CIT on (CUS.CITY_ID = CIT.CITY_ID))
left outer join SALES_PERSON as SP on (CUS.SALES_ID = SP.SALE_ID))

In IKMs only, In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

If there is a journalized datastore in source of the interface, the source table in the clause is replaced by the data view linked to the journalized source datastore.

If one of the source datastores is a temporary datastore with the Use Temporary Interface as Derived Table (Sub-Select) box selected then a sub-select statement will be generated for this temporary source by the getFrom method.

If partitioning is used on source datastores, this method automatically adds the partitioning clauses when returning the object names.

Note that this method automatically generates lookups with no specific code required.

Parameters

Parameter

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

A.2.20 getGrpBy() Method

Use to return the entire SQL GROUP BY clause in the given context.

Usage

public java.lang.String getGrpBy(java.lang.Int pDSIndex)

Description

Allows you to retrieve the SQL GROUP BY string (on the "source" during the loading phase, on the staging area during the integration phase) for a given dataset. This statement is automatically computed from the aggregation transformations detected in the mapping expressions.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

Parameters

Parameter

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

Returns a list of occurrences of SQL GROUP BY for a given dataset of an interface.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The pPattern parameter is interpreted, then repeated for each element of the list and separated from its predecessor with the pSeparator parameter. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains an element for each GROUP BY statement on the source or target (according to the Knowledge Module that used it).

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameter

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of possible attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a column»

pSeparator

String

This parameter is used to separate each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to be generated.

Pattern Attributes List

The following table lists the different values of the parameters as well as their associated description.

Explanation: the getGrpByList function will be used to generate the group by clause of the select order that must start with "group by" and that repeats a pattern (each grouping expression) separated by commas for each expression.

The first parameter "group by" of the function indicates that we want to start the string with "group by"

The second parameter "[EXPRESSION]" indicates that we want to repeat this pattern for each group by expression. The keyword [EXPRESSION] references a valid keyword of the table Pattern Attributes List

The third parameter ", " indicates that we want to separate the interpreted occurrences of the pattern with a comma.

The fourth parameter "" of the function indicates that we want to end the string with no specific character

A.2.22 getHaving() Method

Use to return the entire SQL HAVING clause in the given context.

Usage

public java.lang.String getHaving(java.lang.Int pDSIndex)

Description

Allows the retrieval of the SQL statement HAVING (on the source during loading, on the staging area during integration) for a given dataset. This statement is automatically computed from the filter expressions containing detected aggregation functions.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

Parameters

Parameter

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

Returns a list of the occurrences of SQL HAVING of a given dataset in an interface.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The parameter pPattern is interpreted and repeated for each element of the list, and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains one element for each HAVING expression to execute on the source or target (depends on the Knowledge module that uses it).

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameters

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of authorized attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a column»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to generate.

Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Explanation: The getHavingList function will be used to generate the having clause of the select order that must start with "having" and that repeats a pattern (each aggregated filtered expression) separated by "and" for each expression.

The first parameter "having " of the function indicates that we want to start the string with "having"

The second parameter "([EXPRESSION])" indicates that we want to repeat this pattern for each aggregated filter. The keyword [EXPRESSION] references a valid keyword of the table Pattern Attributes List

The third parameter" and " indicates that we want to separate each interpreted occurrence of the pattern with the string " and ".

The fourth parameter "" of the function indicates that we want to end the string with no specific character

A.2.24 getIndex() Method

Use to return information about a specific index handled by an action.

Usage

public java.lang.String getIndex(java.lang.String pPropertyName)

Description

In an action, this method returns information related to the index currently handled by the DDL command.

Parameters

Parameter

Type

Description

pPropertyName

String

String containing the name of the requested property.

The following table lists the different possible values for pPropertyName.

Parameter Value

Description

ID

Internal number of the index.

KEY_NAME

Name of the index

FULL_NAME

Full name of the index generated with the local object mask.

<flexfield code>

Value of the flexfield for this index.

A.2.25 getIndexColList() Method

Use to return information about the columns of an index handled by an action.

In an action, this method returns the list of the columns of the index handled by the DDL command, ordered by their position in the index.

The pPattern parameter is interpreted and then repeated for each element of the list. It is separated from its predecessor by the pSeparator parameter. The generated string starts with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains an element for each column of the current index.

Parameters

Parameters

Type

Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of attributes that can be used in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern sequence is replaced with its value. The attributes must be between brackets. ([ and ])

Example «My string [COL_NAME] is a column»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.

Pattern Attributes List

The following table lists the different values of the parameters as well as their associated description.

Parameter Value

Description

I_COL

Column internal identifier

COL_NAME

Name of the index column

COL_HEADING

Header of the index column

COL_DESC

Column description

POS

Position of the column

LONGC

Length (Precision) of the column

SCALE

Scale of the column

FILE_POS

Beginning position of the column (fixed file)

BYTES

Number of physical bytes of the column

FILE_END_POS

End of the column (FILE_POS + BYTES)

IND_WRITE

Write right flag of the column

COL_MANDATORY

Mandatory character of the column. Valid values are:

0: null authorized

1: non null

CHECK_FLOW

Flow control flag for of the column. Valid values are:

0: do not check

1: check

CHECK_STAT

Static control flag of the column. Valid values are:

0: do not check

1: check

COL_FORMAT

Logical format of the column

COL_DEC_SEP

Decimal symbol for the column

REC_CODE_LIST

List of the record codes retained for the column

COL_NULL_IF_ERR

Processing flag for the column. Valid values are:

0: Reject

1: Set active trace to null

2: Set inactive trace to null

DEF_VALUE

Default value for the column

EXPRESSION

Not used

CX_COL_NAME

Not used

ALIAS_SEP

Grouping symbol used for the alias (from the technology)

SOURCE_DT

Code of the column's datatype.

SOURCE_CRE_DT

Create table syntax for the column's datatype.

SOURCE_WRI_DT

Create table syntax for the column's writable datatype.

DEST_DT

Code of the column's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the column's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the column's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this column in the data model.

<flexfield code>

Flexfield value for the current column.

A.2.26 getInfo() Method

Use to return information about the current task.

Usage

public java.lang.String getInfo(java.lang.String pPropertyName)

Description

This method returns information about the current task. The list of available information is described in the pPropertyName values table.

Parameters

Parameter

Type

Description

pPropertyName

String

String containing the name of the requested property.

The following table lists the different values possible for pPropertyName:

Parameter Value

Description

I_SRC_SET

Internal identifier of the current Source Set if the task belongs to a Loading Knowledge Module

SRC_SET_NAME

Name of the current Source Set if the task belongs to a Loading Knowledge Module

COLL_NAME

Name of the current loading resource (C$) if the task belongs to a Loading Knowledge Module

INT_NAME

Name of the current integration resource (I$) if the task belongs to a string Loading, Integration or Check Knowledge Module.

ERR_NAME

Name of the current error resource (E$) if the task is part of a Loading, Integration or Check Knowledge Module

TARG_NAME

Name of the target resource if the task is part of a Loading, Integration or Check Knowledge Module

SRC_CATALOG

Name of the data catalog in the source environment

SRC_SCHEMA

Name of the data schema in the source environment

SRC_WORK_CATALOG

Name of the work catalog in the source environment

SRC_WORK_SCHEMA

Name of the work schema in the source environment

DEST_CATALOG

Name of the data catalog in the target environment

DEST_SCHEMA

Name of the data schema in the target environment

DEST_WORK_CATALOG

Name of the work catalog in the target environment

DEST_WORK_SCHEMA

Name of the work schema in the target environment

SRC_TECHNO_NAME

Name of the source technology

SRC_CON_NAME

Name of the source connection

SRC_DSERV_NAME

Name of the data server of the source machine

SRC_CONNECT_TYPE

Connection type of the source machine

SRC_IND_JNDI

JNDI URL flag

SRC_JAVA_DRIVER

Name of the JDBC driver of the source connection

SRC_JAVA_URL

JDBC URL of the source connection

SRC_JNDI_AUTHENT

JNDI authentication type

SRC_JNDI_PROTO

JNDI source protocol

SRC_JNDI_FACTORY

JNDI source Factory

SRC_JNDI_URL

Source JNDI URL

SRC_JNDI_RESSOURCE

Accessed source JNDI resource

SRC_JNDI_USER

User name for JNDI authentication on the source.

SRC_JNDI_ENCODED_PASS

Encrypted password for JNDI authentication on the source.

SRC_USER_NAME

User name of the source connection

SRC_ENCODED_PASS

Encrypted password of the source connection

SRC_FETCH_ARRAY

Size of the source array fetch

SRC_BATCH_UPDATE

Size of the source batch update

SRC_EXE_CHANNEL

Execution canal of the source connection

SRC_COL_ALIAS_WORD

Term used to separated the columns from their aliases for the source technology

SRC_TAB_ALIAS_WORD

Term used to separated the tables from their aliases for the source technology

SRC_DATE_FCT

Function returning the current date for the source technology

SRC_DDL_NULL

Returns the definition used for the keyword NULL during the creation of a table on the source

SRC_MAX_COL_NAME_LEN

Maximum number of characters for the column name on the source technology

SRC_MAX_TAB_NAME_LEN

Maximum number of characters for the table name on the source technology

SRC_REM_OBJ_PATTERN

Substitution model for a remote object on the source technology.

SRC_LOC_OBJ_PATTERN

Substitution model for a local object name on the source technology.

DEST_TECHNO_NAME

Name of the target technology

DEST_CON_NAME

Name of the target connection

DEST_DSERV_NAME

Name of the data server of the target machine

DEST_CONNECT_TYPE

Connection type of the target machine

DEST_IND_JNDI

Target JNDI URL flag

DEST_JAVA_DRIVER

Name of the JDBC driver of the target connection

DEST_JAVA_URL

JDBC URL of the target connection

DEST_JNDI_AUTHENT

JNDI authentication type of the target

DEST_JNDI_PROTO

JNDI target protocol

DEST_JNDI_FACTORY

JNDI target Factory

DEST_JNDI_URL

JNDI URL of the target

DEST_JNDI_RESSOURCE

Target JNDI resource that is accessed

DEST_JNDI_USER

User name for JNDI authentication on the target.

DEST_JNDI_ENCODED_PASS

Encrypted password for JNDI authentication on the target.

DEST_USER_NAME

Name of the user for the target connection

DEST_ENCODED_PASS

Encrypted password for the target connection

DEST_FETCH_ARRAY

Size of the target array fetch

DEST_BATCH_UPDATE

Size of the target batch update

DEST_EXE_CHANNEL

Execution canal of the target connection

DEST_COL_ALIAS_WORD

Term used to separate the columns from their aliases on the target technology

DEST_TAB_ALIAS_WORD

Term used to separate the tables from their aliases on the target technology

DEST_DATE_FCT

Function returning the current date on the target technology

DEST_DDL_NULL

Function returning the definition used for the keyword NULL during the creation on a table on the target

DEST_MAX_COL_NAME_LEN

Maximum number of characters of the column in the target technology

DEST_MAX_TAB_NAME_LEN

Maximum number of characters of the table name on the target technology

DEST_REM_OBJ_PATTERN

Substitution model for a remote object on the target technology

DEST_LOC_OBJ_PATTERN

Substitution model for a local object name on the target technology

CT_ERR_TYPE

Error type (F: Flow, S: Static). Applies only in the case of a Check Knowledge Module

CT_ERR_ID

Error identifier (Table # for a static control or interface number for flow control. Applies only in the case of a Check Knowledge Module

CT_ORIGIN

Name that identifies the origin of an error (Name of a table for static control, or name of an interface prefixed with the project code). Applies only in the case of a Check Knowledge Module

JRN_NAME

Name of the journalized datastore.

JRN_VIEW

Name of the view linked to the journalized datastore.

JRN_DATA_VIEW

Name of the data view linked to the journalized datastore.

JRN_TRIGGER

Name of the trigger linked to the journalized datastore.

JRN_ITRIGGER

Name of the Insert trigger linked to the journalized datastore.

JRN_UTRIGGER

Name of the Update trigger linked to the journalized datastore.

JRN_DTRIGGER

Name of the Delete trigger linked to the journalized datastore.

SUBSCRIBER_TABLE

Name of the datastore containing the subscribers list.

CDC_SET_TABLE

Full name of the table containing list of CDC sets.

CDC_TABLE_TABLE

Full name of the table containing the list of tables journalized through CDC sets.

CDC_SUBS_TABLE

Full name of the table containing the list of subscribers to CDC sets.

CDC_OBJECTS_TABLE

Full name of the table containing the journalizing parameters and objects.

SRC_DEF_CATALOG

Default catalog for the source data server.

SRC_DEF_SCHEMA

Default schema for the source data server.

SRC_DEFW_CATALOG

Default work catalog for the source data server.

SRC_DEFW_SCHEMA

Default work schema for the source data server.

DEST_DEF_CATALOG

Default catalog for the target data server.

DEST_DEF_SCHEMA

Default schema for the target data server.

DEST_DEFW_CATALOG

Default work catalog for the target data server.

DEST_DEFW_SCHEMA

Default work schema for the target data server.

SRC_LSCHEMA_NAME

Source logical schema name.

DEST_LSCHEMA_NAME

Target logical schema name.

SRC_I_CONNECT

Internal ID of the source data server.

SRC_I_PSCHEMA

Internal ID of the source physical schema.

SRC_I_LSCHEMA

Internal ID of the source logical schema.

SRC_I_TECHNO

Internal ID of the source technology.

DEST_I_CONNECT

Internal ID of the target data server.

DEST_I_PSCHEMA

Internal ID of the target physical schema.

DEST_I_LSCHEMA

Internal ID of the target logical schema.

DEST_I_TECHNO

Internal ID of the target technology.

Examples

The current source condition is: <%=odiRef.getInfo("SRC_CON_NAME")%> on server: <%=odiRef.getInfo("SRC_DSERV_NAME")%>

Returns a list of the occurrences of the SQL joins in a given dataset of an interface for the WHERE clause.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The pPattern parameter is interpreted and then repeated for each element in the list and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends up with pEnd.

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameter

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of authorized attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example My string [COL_NAME] is a column»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to generate.

Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Explanation: the getJoinList function will be used to generate join expressions to put in the WHERE part of the SELECT statement that must start with "and" and that repeats a pattern (the expression of each join) separated by " and " for each join. Thus:

The first parameter "and" of the function indicates that we want to start the string with "and"

The second parameter "([EXPRESSION])" indicates that we want to repeat this pattern for each join. The keyword [EXPRESSION] references a valid keyword of the table Pattern Attributes List

The third parameter " and " indicates that we want to separate each interpreted occurrence of the pattern with " and " (note the spaces before and after "and")

The fourth parameter "" of the function indicates that we want to end the string with no specific character

A.2.31 getJrnFilter() Method

Use to return the journalizing filter of an interface.

Usage

public java.lang.String getJrnFilter(java.lang.Int pDSIndex)

Description

Returns the SQL Journalizing filter for a given dataset in the current interface. If the journalized table in the source, this method can be used during the loading phase. If the journalized table in the staging area, this method can be used while integrating.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

Parameters

Parameter

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

Examples

<%=odiRef.getJrnFilter()%>

A.2.32 getJrnInfo() Method

Use to return journalizing information about a datastore.

Usage

public java.lang.String getJrnInfo(java.lang.String pPropertyName)

Description

Returns information about a datastore's journalizing for a JKM while journalizing a model/datastore, or for a LKM/IKM in an interface.

Parameters

Parameter

Type

Description

pPropertyName

String

String containing the name of the requested property.

The following table lists the different values possible for pPropertyName:

Parameter Value

Description

FULL_TABLE_NAME

Full name of the journalized datastore.

JRN_FULL_NAME

Full name of the journal datastore.

JRN_FULL_VIEW

Full name of the view linked to the journalized datastore.

JRN_FULL_DATA_VIEW

Full name of the data view linked to the journalized datastore.

JRN_FULL_TRIGGER

Full name of the trigger linked to the journalized datastore.

JRN_FULL_ITRIGGER

Full name of the Insert trigger linked to the journalized datastore.

JRN_FULL_UTRIGGER

Full name of the Update trigger linked to the journalized datastore.

JRN_FULL_DTRIGGER

Full name of the Delete trigger linked to the journalized datastore.

SNP_JRN_SUBSCRIBER

Name of the subscriber table in the work schema.

JRN_NAME

Name of the journalized datastore.

JRN_VIEW

Name of the view linked to the journalized datastore.

JRN_DATA_VIEW

Name of the data view linked to the journalized datastore.

JRN_TRIGGER

Name of the trigger linked to the journalized datastore.

JRN_ITRIGGER

Name of the Insert trigger linked to the journalized datastore.

JRN_UTRIGGER

Name of the Update trigger linked to the journalized datastore.

JRN_DTRIGGER

Name of the Delete trigger linked to the journalized datastore.

JRN_SUBSCRIBER

Name of the subscriber.

JRN_COD_MOD

Code of the journalized data model.

JRN_METHOD

Journalizing Mode (consistent or simple).

CDC_SET_TABLE

Full name of the table containing list of CDC sets.

CDC_TABLE_TABLE

Full name of the table containing the list of tables journalized through CDC sets.

CDC_SUBS_TABLE

Full name of the table containing the list of subscribers to CDC sets.

CDC_OBJECTS_TABLE

Full name of the table containing the journalizing parameters and objects.

Examples

The table being journalized is <%=odiRef.getJrnInfo("FULL_TABLE_NAME")%>

A.2.33 getLoadPlanInstance() Method

This method returns the current execution instance information for a Load Plan.

Parameters

Parameter

Type

Description

pPropertyName

String

String that contains the name of the requested property.

The following table lists the possible values for pPropertyName:

Parameter Value

Description

BATCH_ID

Load Plan instance identifier (also Instance ID). Every time a Load Plan is started, a new Load Plan instance with a unique identifier is created

RESTART_ATTEMPTS

Number of execution attempts of this Load Plan instance (also Run #). It starts at 1 when the Load Plan instance is first executed, and is incremented each time the Load Plan instance is restarted.

LOAD_PLAN_NAME

Name of the Load Plan

START_DATE

Starting date and time of the current Load Plan instance run

Examples

The current Load Plan <%=odiRef.getLoadPlanInstance("LOAD_PLAN_NAME")%> started execution at <%=odiRef.getLoadPlanInstance("START_DATE")%>

A.2.34 getModel() Method

Use to return information about a model.

Usage

public java.lang.String getModel(java.lang.String pPropertyName)

Description

This method returns information on the current data model during the processing of a personalized reverse engineering. The list of available data is described in the pPropertyName values table.

Note:

This method may be used on the source connection (data server being reverse-engineered) as well as on the target connection (repository). On the target connection, only the properties independent from the context can be specified (for example, the schema and catalog names cannot be used).

Parameters

Parameter

Type

Description

pPropertyName

String

String that contains the name of the requested property.

The following table lists the possible values for pPropertyName:

Parameter Value

Description

ID

Internal identifier of the current model

MOD_NAME

Name of the current model

LSCHEMA_NAME

Name of the logical schema of the current model

MOD_TEXT

Description of the current model

REV_TYPE

Reverse engineering type: S for standard reverse, C for customize

REV_UPDATE

Update flag of the model

REV_INSERT

Insert flag for the model

REV_OBJ_PATT

Mask for the objects to reverse.

REV_OBJ_TYPE

List of object types to reverse-engineer for this model. This is a semicolon separated list of object types codes. Valid codes are:

T: Table

V: View

Q: Queue

SY: System table

AT: Table alias

SY: Synonym

TECH_INT_NAME

Internal name of the technology of the current model.

LAGENT_NAME

Name of the logical execution agent for the reverse engineering.

REV_CONTEXT

Execution context of the reverse

REV_ALIAS_LTRIM

Characters to be suppressed for the alias generation

CKM

Check Knowledge Module

RKM

Reverse-engineering Knowledge Module

SCHEMA_NAME

Physical Name of the data schema in the current reverse context

WSCHEMA_NAME

Physical Name of the work schema in the current reverse context

CATALOG_NAME

Physical Name of the data catalog in the current reverse context

WCATALOG_NAME

Physical Name of the work catalog in the current reverse context

<flexfield code>

Value of the flexfield for the current model.

Examples

Retrieve the list of tables that are part of the mask of objects to reverse:

Returns the fully qualified name of a physical object, including its catalog and schema. The pMode parameter indicates the substitution mask to use.

Note:

The getObjectName methods truncates automatically object names to the maximum object length allowed for the technology. In versions before ODI 11g, object names were not trucated. To prevent object names truncation and reproduce the 10g behavior, add in the properties tab of the data server a property called OBJECT_NAME_LENGTH_CHECK_OLD and set its value to true.

The first syntax builds the object name according to the current logical schema in the current context.

The second syntax builds the name of the object according to the logical schema indicated in the pLogicalSchemaName parameter in the current context.

The third syntax builds the name from the logical schema and the context indicated in the pLogicalSchemaName and pContextName parameters.

The fourth syntax builds the object name according to the current logical schema in the current context, with the local object mask (pMode = "L").

The fifth syntax is equivalent to the fourth with pLocation = "D".

The last syntax is equivalent to the third syntax but qualifies the object name specifically on a given partition, using the pPartitionType and pPartitionName parameters.

Parameters

Parameter

Type

Description

pMode

String

"L" use the local object mask to build the complete path of the object. "R" use the remote object mask to build the complete path of the object.

Note: When using the remote object mask, getObjectName always resolved the object name using the default physical schema of the remote server.

pObjectName

String

Every string that represents a valid resource name (table or file). This object name may be prefixed by a prefix code that will be replaced at run-time by the appropriate temporary object prefix defined for the physical schema.

pLogicalSchemaName

String

Name of the forced logical schema of the object.

pContextName

String

Forced context of the object

pLocation

String

The valid values are:

W: Returns the complete name of the object in the physical catalog and the "work" physical schema that corresponds to the specified tuple (context, logical schema)

D: Returns the complete name of the object in the physical catalog and the data physical schema that corresponds to the specified tuple (context, logical schema)

pPartitionType

String

Specify whether to qualify the object name for a specific partition or sub-partition. The valid values are:

P: Qualify object for the partition provided in pPartitionName

S: Qualify object for the sub-partition provided in pPartitionName

pPartitionName

String

Name of the partition of sub-partition to qualify the object name.

Prefixes

It is possible to prefix the resource name specified in the pObjectName parameter by a prefix code to generate a Oracle Data Integrator temporary object name (Error or Integration table, journalizing trigger, etc.).

The list of prefixes are given in the table below.

Prefix

Description

Prefix

Description

%INT_PRF

Prefix for integration tables (default value is "I$_").

%COL_PRF

Prefix for Loading tables (default value is "C$_").

%ERR_PRF

Prefix for error tables (default value is "E$_").

%JRN_PRF_TAB

Prefix for journalizing tables (default value is "J$_").

%INT_PRF_VIE

Prefix for journalizing view (default value is "JV$_").

%JRN_PRF_TRG

Prefix for journalizing triggers (default value is "T$_").

%IDX_PRF

Prefix for temporary indexes (default value is "IX$_").

Note:

Temporary objects are usually created in the work physical schema. Therefore, pLocation should be set to "W" when using a prefix to create or access a temporary object.

Examples

You have defined a physical schema as shown below.

Data catalog:

db_odi

Data schema:

dbo

Work catalog:

tempdb

Work schema:

temp_owner

You have associated this physical schema to the logical schema MSSQL_ODI in the context CTX_DEV.

The method is similar to the getObjectName method. However, the object name is computed for the default physical schema of the data server to which the physical schema is attached. In getObjectName, the object name is computed for the physical schema itself.

A.2.43 getParentLoadPlanStepInstance() Method

This method returns the step execution instance information of the parent of the current step for a Load Plan instance. It will return an empty string if the parent step is the root step.

Parameters

Parameter

Type

Description

pPropertyName

String

String that contains the name of the requested property.

The following table lists the different possible values for pPropertyName.

Parameter Value

Description

BATCH_ID

Load Plan instance identifier (also Instance ID). Every time a Load Plan is started, a new Load Plan instance with a unique identifier is created.

RESTART_ATTEMPTS

Number of execution attempts of this Load Plan parent step instance. It starts at 1 when the Load Plan parent step instance is first started, and is incremented each time the Load Plan parent step instance is restarted.

STEP_NAME

Name of the Load Plan parent step

STEP_TYPE

Type of the Load Plan parent step

START_DATE

Starting date and time of the parent step instance of the current step of the current Load Plan instance run.

Examples

Step <%=odiRef.getParentLoadPlanStepInstance("STEP_NAME")%> has been executed <%=odiRef.getParentLoadPlanStepInstance("RESTART_ATTEMPTS")%> times

A.2.44 getPK() Method

Use to return information about a primary key.

Usage

public java.lang.String getPK(java.lang.String pPropertyName)

Description

This method returns information relative to the primary key of a datastore during a check procedure.

In an action, this method returns information related to the primary key currently handled by the DDL command.

Parameters

Parameter

Type

Description

pPropertyName

String

String that contains the name of the requested property.

The following table lists the different possible values for pPropertyName.

Returns a list of columns and expressions for the primary key being checked.

The pPattern parameter is interpreted and then repeated for each element of the list. It is separated from its predecessor by the pSeparator parameter. The generated string starts with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains an element for each column of the current primary key. It is accessible from a Check Knowledge Module if the current task is tagged as an "primary key".

In an action, this method returns the list of the columns of the primary key handled by the DDL command, ordered by their position in the key.

Parameters

Parameter

Type

Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of attributes that can be used in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern sequence is replaced with its value. The attributes must be between brackets. ([ and ])

Example «My string [COL_NAME] is a column»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.

Pattern Attributes List

The following table lists the different values of the parameters as well as their associated description.

Parameter Value

Description

I_COL

Column internal identifier

COL_NAME

Name of the key column

COL_HEADING

Header of the key column

COL_DESC

Column description

POS

Position of the column

LONGC

Length (Precision) of the column

SCALE

Scale of the column

FILE_POS

Beginning position of the column (fixed file)

BYTES

Number of physical bytes of the column

FILE_END_POS

End of the column (FILE_POS + BYTES)

IND_WRITE

Write right flag of the column

COL_MANDATORY

Mandatory character of the column. Valid values are:

0: null authorized

1: not null

CHECK_FLOW

Flow control flag for of the column. Valid values are:

0: do not check

1: check

CHECK_STAT

Static control flag of the column. Valid values are:

0: do not check

1: check

COL_FORMAT

Logical format of the column

COL_DEC_SEP

Decimal symbol for the column

REC_CODE_LIST

List of the record codes retained for the column

COL_NULL_IF_ERR

Processing flag for the column. Valid values are:

0: Reject

1: Set active trace to null

2: Set inactive trace to null

DEF_VALUE

Default value for the column

EXPRESSION

Not used

CX_COL_NAME

Not used

ALIAS_SEP

Grouping symbol used for the alias (from the technology)

SOURCE_DT

Code of the column's datatype.

SOURCE_CRE_DT

Create table syntax for the column's datatype.

SOURCE_WRI_DT

Create table syntax for the column's writable datatype.

DEST_DT

Code of the column's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the column's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the column's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this column in the data model.

<flexfield code>

Flexfield value for the current column.

Examples

If the CUSTOMER table has an primary key PK_CUSTOMER (CUST_ID, CUST_NAME) and you want to generate the following code:

Explanation: the getPKColList function will be used to generate the (CUST_ID numeric(10) not null, CUST_NAME varchar(50) not null) part, which starts and stops with a parenthesis and repeats the pattern (column, a data type, and not null) separated by commas for each column of the primary key. Thus

the first parameter "(" of the function indicates that we want to start the string with the string "("

the second parameter "[COL_NAME] [DEST_CRE_DT] not null" indicates that we want to repeat this pattern for each column of the primary key. The keywords [COL_NAME] and [DEST_CRE_DT] reference valid keywords of the Pattern Attributes List table

the third parameter ", " indicates that we want to separate interpreted occurrences of the pattern with the string ", "

the forth parameter ")" of the function indicates that we want to end the string with the string ")"

A.2.46 getPop() Method

Use to return information about an interface.

Usage

public java.lang.String getPop(java.lang.String pPropertyName)

Description

This method returns information about the current interface. The list of available information is described in the pPropertyName values table.

Parameters

Parameter

Type

Description

pPropertyName

String

String that contains the name of the requested property.

The following table lists the different possible values for pPropertyName:

Parameter Value

Description

I_POP

Internal number of the interface.

FOLDER

Name of the folder of the interface

POP_NAME

Name of the interface

IND_WORK_TARG

Position flag of the staging area.

LSCHEMA_NAME

Name of the logical schema which is the staging area of the interface

DESCRIPTION

Description of the interface

WSTAGE

Flag indicating the nature of the target datastore:

E - target datastore is an existing table (not a temporary table).

N - target datastore is a temporary table in the data schema.

W - target datastore is a temporary table in the work schema.

TABLE_NAME

Target table name

KEY_NAME

Name of the update key

DISTINCT_ROWS

Flag for doubles suppression

OPT_CTX

Name of the optimization context of the interface

TARG_CTX

Name of the execution context of the interface

MAX_ERR

Maximum number of accepted errors

MAX_ERR_PRCT

Error indicator in percentage

IKM

Name of the Integration Knowledge Module used in this interface.

LKM

Name of the Loading Knowledge Module specified to load data from the staging area to the target if a single-technology IKM is selected for the staging area.

CKM

Name of the Check Knowledge Module used in this interface.

HAS_JRN

Returns 1 if there is a journalized table in source of the interface, 0 otherwise.

PARTITION_NAME

Name of the partition or sub-partition selected for the target datastore. If no partition is selected, returns an empty string.

PARTITION_TYPE

Type of the partition or sub-partition selected for the target datastore. If no partition is selected, returns an empty string.

P: Partition

S: Sub-partition

<flexfield code>

Flexfield value for the interface.

Examples

The current interface is: <%=odiRef.getPop("POP_NAME")%> and runs on the logical schema: <%=odiRef.getInfo("L_SCHEMA_NAME")%>

A.2.47 getPrevStepLog() Method

Use to return information about the previous step executed in the package.

Returns information about the most recently executed step in a package. The information requested is specified through the pPropertyName parameter. If there is no previous step (for example, if the getPrevStepLog step is executed from outside a package), the exception "No previous step" is raised.

Parameters

Parameter

Type

Description

pPropertyName

String

String that contains the name of the requested property about the previous step. See the list of valid properties below.

The following table lists the different possible values for pPropertyName:

Parameter Value

Description

SESS_NO

The number of the session.

NNO

The number of the step within a package. The first step executed is 0.

STEP_NAME

The name of the step.

STEP_TYPE

A code indicating the type of step. The following values may be returned:

F: Interface

VD: Variable declaration

VS: Set/Increment variable

VE: Evaluate variable

V: Refresh variable

T: Procedure

OE: OS command

SE: ODI Tool

RM: Reverse-engineer model

CM: Check model

CS: Check sub-model

CD: Check datastore

JM: Journalize model

JD: Journalize datastore

CONTEXT_NAME

The name of the context in which the step was executed.

MAX_ERR

The maximum number or percentage of errors tolerated.

MAX_ERR_PRCT

Returns 1 if the maximum number of errors is expressed as a percentage, 0 otherwise.

RUN_COUNT

The number of times this step has been executed.

BEGIN

The date and time that the step began.

END

The date and time that the step terminated.

DURATION

Time the step took to execute in seconds.

STATUS

Returns the one-letter code indicating the status with which the previous step terminated. The state R (Running) is never returned.

D: Done (success)

E: Error

Q: Queued

W: Waiting

M: Warning

RC

Return code. 0 indicates no error.

MESSAGE

Error message returned by previous step, if any. Blank string if no error.

INSERT_COUNT

Number of rows inserted by the step.

DELETE_COUNT

Number of rows deleted by the step.

UPDATE_COUNT

Number of rows updated by the step.

ERROR_COUNT

Number of erroneous rows detected by the step, for quality control steps.

If the message for the condition is "Error:\n Zero is not a valid value", the generated code is as shown below. Without the getQuotedString, the code is incorrect, as the \n is not preserved and becomes a carriage return.

String condSqlOK = "Error:\n Zero is not a valid value";
String condSqlKO = "Error:
Zero is not a valid value";

Allows you to retrieve the name of the default physical data schema or work schema for the data server to which is associated the physical schema corresponding to the tuple (logical schema, context). If no context is specified, the current context is used. If no logical schema name is specified, then the current logical schema is used. If no pLocation is specified, then the data schema is returned.

Parameters

Parameter

Type

Description

pLogicalSchemaName

String

Name of the logical schema

pContextCode

String

Code of the enforced context of the schema

pLocation

String

The valid values are:

D: Returns the data schema of the physical schema corresponding to the tuple (context, logical schema)

W: Returns the work schema of the default physical schema associate to the data server to which the physical schema corresponding to the tuple (context, logical schema) is also attached.

Examples

If you have defined the physical schemas: Pluton.db_odi.dbo

Data catalog:

db_odi

Data schema:

dbo

Work catalog:

tempdb

Work schema:

temp_odi

Default Schema

Yes

that you have associated with this physical schema: MSSQL_ODI in the context CTX_DEV, and Pluton.db_doc.doc

Data catalog:

db_doc

Data schema:

doc

Work catalog:

tempdb

Work schema:

temp_doc

Default Schema

No

that you have associated with this physical schema: MSSQL_DOC in the context CTX_DEV

The Call To

Returns

<%=odiRef.getSchemaNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "W")%>

temp_odi

<%=odiRef.getSchemaNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "D")%>

dbo

A.2.51 getSession() Method

Use to return information about the current session.

Usage

public java.lang.String getSession(java.lang.String pPropertyName)

Description

This method returns information about the current session. The list of available properties is described in the pPropertyName values table.

Parameters

Parameters

Type

Description

pPropertyName

String

String that contains the name of the requested property.

The following table lists the different possible values for pPropertyName:

This method available in LKMs and IKMs, returns properties for a list of columns in a given dataset. This list includes all the columns of the sources processed by the LKM (from the source) or the IKM (from the staging area). The list is sorted by the column position in the source tables.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The properties displayed depend on whether the column is mapped or not. If the column is mapped, the properties returned are defined in the pMappedPattern pattern. If the column is not mapped, the properties returned are defined in the pUnMappedPattern pattern.

The attributes usable in a pattern are detailed in "Pattern Attributes List". Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ]). Example: "My string [COL_NAME] is a column".

The pMappedPattern or pUnMappedPattern parameter is interpreted and then repeated for each element of the list. Patterns are separated with pSeparator. The generated string begins with pStart and ends with pEnd.

If there is a journalized datastore in the source of the interface, the three journalizing pseudo columns JRN_FLG, JRN_DATE and JRN_SUBSCRIBER are added as columns of the journalized source datastore.

Parameters

Parameter

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This sequence marks the beginning of the string to generate.

pUnMappedPattern

String

The pattern is repeated for each occurrence in the list if the column is not mapped.

pMappedPattern

String

The pattern is repeated for each occurrence in the list, if the column is mapped.

pSeparator

String

This parameter separates patterns.

pEnd

String

This sequence marks the end of the string to generate.

Pattern Attributes List

The following table lists different parameters values as well as their associated description.

Parameter Value

Description

I_COL

Internal identifier of the column

COL_NAME

Name of the column

ALIAS_NAME

Name of the column. Unlike COL_NAME, this attribute returns the column name without the optional technology delimiters. These delimiters appear when the column name contains for instance spaces.

Returns a list of source tables of a given dataset in an interface. This method can be used to build a FROM clause in a SELECT order. However, it is advised to use the getFrom() method instead.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The pPattern pattern is interpreted and then repeated for each element of the list and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameters

Type

Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of possible attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a column»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to generate.

Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Attribute

Description

I_TABLE

Internal identifier of the current source table if available.

MODEL_NAME

Name of the model of the current source table, if available.

SUB_MODEL_NAME

Name of the sub-model of the current source table, if available

TECHNO_NAME

Name of the technology of the source datastore

LSCHEMA_NAME

Logical schema of the source table

TABLE_NAME

Logical name of the source datastore

RES_NAME

Physical access name of the resource (file name or JMS queue, physical name of the table, etc.). If there is a journalized datastore in source of the interface, the source table is the clause is replaced by the data view linked to the journalized source datastore.

CATALOG

Catalog of the source datastore (resolved at runtime)

WORK_CATALOG

Work catalog of the source datastore

SCHEMA

Schema of the source datastore (resolved at runtime)

WORK_SCHEMA

Work schema of the source datastore

TABLE_ALIAS

Alias of the datastore as it appears in the tables list, if available

POP_TAB_ALIAS

Alias of the datastore as it appears in the current interface, if available.

TABLE_TYPE

Type of the datastore source, if available.

DESCRIPTION

Description of the source datastore, if available.

R_COUNT

Number of records of the source datastore, if available.

FILE_FORMAT

File format, if available.

FILE_SEP_FIELD

Field separator (file)

XFILE_SEP_FIELD

Hexadecimal field separator (file)

SFILE_SEP_FIELD

Field separator string (file)

FILE_ENC_FIELD

Field beginning and ending character (file)

FILE_SEP_ROW

Record separator (file)

XFILE_SEP_ROW

Hexadecimal record separator (file)

SFILE_SEP_ROW

Record separator string (file)

FILE_FIRST_ROW

Number of header lines to ignore, if available.

FILE_DEC_SEP

Default decimal separator for the datastore, if available.

METADATA

Description in ODI format of the metadata of the current resource, if available.

OLAP_TYPE

OLAP type specified in the datastore definition

IND_JRN

Flag indicating that the datastore is including in CDC.

JRN_ORDER

Order of the datastore in the CDC set for consistent journalizing.

PARTITION_NAME

Name of the partition or sub-partition selected for the source datastore. If no partition is selected, returns an empty string.

PARTITION_TYPE

Type of the partition or sub-partition selected for the source datastore. If no partition is selected, returns an empty string.

Explanation: the getSrcTablesList function will be used to generate the FROM clause of the SELECT STATEMENT that repeats the pattern (CATALOG.SCHEMA.TABLE_NAME as POP_TAB_ALIAS) separated by commas for each table in source.

The first parameter "" of the function indicates that we want do not want to start the string with any specific character.

The second parameter "[CATALOG].[SCHEMA].[TABLE_NAME] as [POP_TAB_ALIAS]" indicates that we want to repeat this pattern for each source table. The keywords [CATALOG], [SCHEMA], [TABLE_NAME] and [POP_TAB_ALIAS] reference valid keywords of the table Pattern Attributes List

The third parameter", " indicates that we want to separate each interpreted occurrence of the pattern with the string ", "

The fourth parameter "" of the function indicates that we want to end the string with no specific character

A.2.55 getStep() Method

Use to return information about the current step.

Usage

public java.lang.String getStep(java.lang.String pPropertyName)

Description

This method returns information about the current step. The list of available information is described in the pPropertyName values table.

Parameters

Parameter

Type

Description

pPropertyName

String

String that contains the name of the requested property.

The following table lists the possible values for pPropertyName:

Parameter Value

Description

SESS_NO

Number of the session to which the step belongs.

NNO

Number of the step in the session

NB_RUN

Number of execution attempts

STEP_NAME

Step name

STEP_TYPE

Step type

CONTEXT_NAME

Name of the execution context

VAR_INCR

Step variable increment

VAR_OP

Operator used to compare the variable

VAR_VALUE

Forced value of the variable

OK_EXIT_CODE

Exit code in case of success

OK_EXIT

End the package in case of success

OK_NEXT_STEP

Next step in case of success.

OK_NEXT_STEP_NAME

Name of the next step in case of success

KO_RETRY

Number of retry attempts in case of failure.

KO_RETRY_INTERV

Interval between each attempt in case of failure

KO_EXIT_CODE

Exit code in case of failure.

KO_EXIT

End the package in case of failure.

KO_NEXT_STEP

Next step in case of failure.

KO_NEXT_STEP_NAME

Name of the next step in case of failure

Examples

The current step is: <%=odiRef.getStep("STEP_NAME")%>

A.2.56 getSubscriberList() Method

Use to return properties for each of the subscribers of a journalized table. The properties are organized according to a string pattern.

Returns a list of subscribers for a journalized table. The pPattern parameter is interpreted and then repeated for each element of the list, and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameter

Type

Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of the attributes usable in a pattern is detailed in the Pattern Attributes List below.

Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ])

Example «My name is [SUBSCRIBER]»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.

Pattern Attributes List

The following table lists different parameters values as well as their associated description.

Allows the retrieval of the fully qualified name of temporary and permanent tables handled by Oracle Data Integrator.

Parameters

Parameters

Type

Description

pMode

String

"L": Uses the local object mask to build the complete path of the object. This value is used when pMode is not specified.

"R": Uses the object mask to build the complete path of the object

"A" Automatic: Defines automatically the adequate mask to use.

pProperty

String

Parameter that indicates the name of the table to be built. The list of possible values is:

ID: Datastore identifier.

TARG_NAME: Full name of the target datastore. In actions, this parameter returns the name of the current table handled by the DDL command. If partitioning is used on the target datastore of an interface, this property automatically includes the partitioning clause in the datastore name.

COLL_NAME: Full name of the loading datastore.

INT_NAME: Full name of the integration datastore.

ERR_NAME: Full name of the error datastore.

CHECK_NAME: Name of the error summary datastore.

CT_NAME: Full name of the checked datastore.

FK_PK_TABLE_NAME: Full name of the datastore referenced by a foreign key.

JRN_NAME: Full name of the journalized datastore.

JRN_VIEW: Full name of the view linked to the journalized datastore.

JRN_DATA_VIEW: Full name of the data view linked to the journalized datastore.

JRN_TRIGGER: Full name of the trigger linked to the journalized datastore.

JRN_ITRIGGER: Full name of the Insert trigger linked to the journalized datastore.

JRN _UTRIGGER: Full name of the Update trigger linked to the journalized datastore.

JRN_DTRIGGER: Full name of the Delete trigger linked to the journalized datastore.

SUBSCRIBER_TABLE: Full name of the datastore containing the subscribers list.

CDC_SET_TABLE: Full name of the table containing list of CDC sets.

CDC_TABLE_TABLE: Full name of the table containing the list of tables journalized through CDC sets.

CDC_SUBS_TABLE: Full name of the table containing the list of subscribers to CDC sets.

CDC_OBJECTS_TABLE: Full name of the table containing the journalizing parameters and objects.

<flexfield_code>: Flexfield value for the current target table.

pLocation

String

W: Returns the full name of the object in the physical catalog and the physical work schema that corresponds to the current tuple (context, logical schema)

D: Returns the full name of the object in the physical catalog and the physical data schema that corresponds to the current tuple (context, logical schema)

A: Lets Oracle Data Integrator determine the default location of the object. This value is used if pLocation is not specified.

Examples

If you have defined a physical schema called Pluton.db_odi.dbo as shown below:

Data catalog:

db_odi

Data schema:

dbo

Work catalog:

tempdb

Work schema:

temp_owner

Local Mask:

%CATALOG.%SCHEMA.%OBJECT

Remote mask:

%DSERVER:%CATALOG.%SCHEMA.%OBJECT

Loading prefix:

CZ_

Error prefix:

ERR_

Integration prefix:

I$_

You have associated this physical schema to the logical schema called MSSQL_ODI in the context CTX_DEV and your working with a table is named CUSTOMER.

A Call To

Returns

<%=odiRef.getTable("L", "COLL_NAME", "W")%>

tempdb.temp_owner.CZ_0CUSTOMER

<%=odiRef.getTable("R", "COLL_NAME", "D")%>

MyServer:db_odi.dbo.CZ_0CUSTOMER

<%=odiRef.getTable("L", "INT_NAME", "W")%>

tempdb.temp_owner.I$_CUSTOMER

<%=odiRef.getTable("R", "ERR_NAME", "D")%>

MyServer:db_odi.dbo.ERR_CUSTOMER

A.2.59 getTargetColList() Method

Use to return information about the active columns of the target table of an interface. Active columns are those having an active mapping. To return information about all columns of the target table, including active and non-active columns, use the getAllTargetColList() Method.

The pPattern parameter is interpreted and then repeated for each element of the list (selected according to pSelector parameter) and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

In the alternative syntaxes, any parameters not set are set to an empty string.

Parameters

Parameters

Type

Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of the attributes usable in a pattern is detailed in the Pattern Attributes List below.

Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a column of the target»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.

pSelector

String

String that designates a Boolean expression that allows to filter the elements of the initial list with the following format:

<SELECTOR> <Operator> <SELECTOR> etc. Parenthesis are authorized.

Authorized operators:

No: NOT or!

Or: OR or ||

And: AND or &&

Example: (INS AND UPD) OR TRG

The description of valid selectors is provided below.

Pattern Attributes List

The following table lists different parameters values as well as their associated description.

Parameter Value

Description

I_COL

Internal identifier of the column

COL_NAME

Name of the column

COL_HEADING

Header of the column

COL_DESC

Description of the column

POS

Position of the column

LONGC

Column length (Precision)

SCALE

Scale of the column

FILE_POS

Beginning (index) of the column

BYTES

Number of physical bytes in the column

FILE_END_POS

End of the column (FILE_POS + BYTES)

IND_WRITE

Write right flag of the column

COL_MANDATORY

Mandatory character of the column. Valid values are:

0: null authorized

1: not null

CHECK_FLOW

Flow control flag of the column. Valid values are: (0: do not check, 1: check)

0: do not check

1: check

CHECK_STAT

Static control flag of the column. Valid values are: (0: do not check, 1: check)

A.2.62 getTemporaryIndex() Method

This method returns information relative to a temporary index being created or dropped by an interface.

It can be used in a Loading or Integration Knowledge Module task if the Create Temporary Index option is set to On Source or On Target for this task.

Parameters

Parameter

Type

Description

pPropertyName

String

String containing the name of the requested property.

The following table lists the different possible values for pPropertyName.

Parameter Value

Description

IDX_NAME

Name of the index. This name is computed and prefixed with the temporary index prefix defined for the physical schema.

FULL_IDX_NAME

Fully qualified name of the index. On the target tab, this name is qualified to create the index in the work schema of the staging area. On the source tab, this name is qualified to create the index in the source default work schema (LKM) or in the work schema of the staging area (IKM).

COLL_NAME

Fully qualified name of the loading table for an LKM. This property does not apply to IKMs.

The parameter pPattern in interpreted and repeated for each element of the list, and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains one element for each column of the temporary index.

It can be used in a Loading or Integration Knowledge Module task if the Create Temporary Index option is set to On Source or On Target for this task.

Parameters

Parameter

Type

Description

Parameter

Type

Description

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of possible attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a column»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to generate.

Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Parameter Value

Description

CX_COL_NAME

Computed name of the column used as a container for the current expression on the staging area

COL_NAME

Name of the column participating to the index.

POS

Position of the first occurrence of this column in the join or filter clause this index optimizes.

A.2.71 setTableName() Method

This method sets the name of temporary table used for loading or integration. this name can be any value.

When using the method, the loading or integration table name is no longer generated by ODI and does not follow the standard naming convention (for example, a loading table will not be prefixed with a C$ prefix). Yet, other methods using this table name will return the newly set value.

The fist parameter pProperty indicates the temporary table name to set. The second parameter can be any valid table name.

Parameters

Parameters

Type

Description

pProperty

String

Parameter that indicates the table name to set. The list of possible values is: