ODI 12c – Parallel Table Load

In this post we will look at the ODI 12c capability of parallel table load from the aspect of the mapping developer and the knowledge module developer – two quite different viewpoints. This is about parallel table loading which isn’t to be confused with loading multiple targets per se. It supports the ability for ODI mappings to be executed concurrently especially if there is an overlap of the datastores that they access, so any temporary resources created may be uniquely constructed by ODI. Temporary objects can be anything basically – common examples are staging tables, indexes, views, directories – anything in the ETL to help the data integration flow do its job. In ODI 11g users found a few workarounds (such as changing the technology prefixes – see here) to build unique temporary names but it was more of a challenge in error cases.

ODI 12c mappings by default operate exactly as they did in ODI 11g with respect to these temporary names (this is also true for upgraded interfaces and scenarios) but can be configured to support the uniqueness capabilities. We will look at this feature from two aspects; that of a mapping developer and that of a developer (of procedures or KMs).

1. Firstly as a Mapping Developer…..

1.1 Control when uniqueness is enabled

A new property is available to set unique name generation on/off. When unique names have been enabled for a mapping, all temporary names used by the collection and integration objects will be generated using unique names. This property is presented as a check-box in the Property Inspector for a deployment specification.

1.2 Handle cleanup after successful execution

Provided that all temporary objects that are created have a corresponding drop statement then all of the temporary objects should be removed during a successful execution. This should be the case with the KMs developed by Oracle.

1.3 Handle cleanup after unsuccessful execution

If the agent was to crash and not be able to execute this task, then there is an ODI tool (OdiRemoveTemporaryObjects here) you can invoke to cleanup the tables – it supports date ranges and the like.

That’s all there is to it from the aspect of the mapping developer it’s much, much simpler and straightforward. You can now execute the same mapping concurrently or execute many mappings using the same resource concurrently without worrying about conflict. 

In the ODI Operator the executed code shows the actual name that is generated – you can also see the runtime code prior to execution (introduced in 11.1.1.7), for example below in the code type I selected ‘Pre-executed Code’ this lets you see the code about to be processed and you can also see the executed code (which is the default view).

References to the collection (C$) and integration (I$) names will be automatically made unique by using the odiRef APIs – these objects will have unique names whenever concurrency has been enabled for a particular mapping deployment specification. It’s also possible to use name uniqueness functions in procedures and your own KMs.

2.1 New uniqueness tags 

You can also make your own temporary objects have unique names by explicitly including either %UNIQUE_STEP_TAG or %UNIQUE_SESSION_TAG in the name passed to calls to the odiRef APIs. Such names would always include the unique tag regardless of the concurrency setting.

To illustrate, let’s look at the getObjectName() method. At <% expansion time, this API will append %UNIQUE_STEP_TAG to the object name for collection and integration tables. The name parameter passed to this API may contain  %UNIQUE_STEP_TAG or %UNIQUE_SESSION_TAG. This API always generates to the <? version of getObjectName()

At execution time this API will replace the unique tag macros with a string that is unique to the current execution scope. The returned name will conform to the name-length restriction for the target technology, and its pattern for the unique tag. Any necessary truncation will be performed against the initial name for the object and any other fixed text that may have been specified.

Examples are:-

1. <?=odiRef.getObjectName(“L”, “%COL_PRFEMP%UNIQUE_STEP_TAG”, “D”)?>
2. SCOTT.C$_EABH7QI1BR1EQI3M76PG9SIMBQQ

1. <?=odiRef.getObjectName(“L”, “EMP%UNIQUE_STEP_TAG_AE”, “D”)?>
2. SCOTT.EMPAO96Q2JEKO0FTHQP77TMSAIOSR_

Methods which have this kind of support include getFrom, getTableName, getTable, getObjectShortName and getTemporaryIndex. There are APIs for retrieving this tag info also, the getInfo API has been extended with the following properties (the UNIQUE* properties can also be used in ODI procedures);

• UNIQUE_STEP_TAG – Returns the unique value for the current step scope, e.g. 5rvmd8hOIy7OU2o1FhsF61 Note that this will be a different value for each loop-iteration when the step is in a loop.
• UNIQUE_SESSION_TAG – Returns the unique value for the current session scope, e.g. 6N38vXLrgjwUwT5MseHHY9
• IS_CONCURRENT – Returns info about the current mapping, will return 0 or 1 (only in % phase)
• GUID_SRC_SET – Returns the UUID for the current source set/execution unit (only in % phase)

The getPop API has been extended with the IS_CONCURRENT property which returns info about an mapping, will return 0 or 1. 

2.2 Additional APIs

Some new APIs are provided including getFormattedName which will allow KM developers to construct a name from fixed-text or ODI symbols that can be optionally truncate to a max length and use a specific encoding for the unique tag. It has syntax getFormattedName(String pName[, String pTechnologyCode]) This API is available at both the % and the ? phase.  The format string can contain the ODI prefixes that are available for getObjectName(), e.g. %INT_PRF, %COL_PRF, %ERR_PRF, %IDX_PRF alongwith %UNIQUE_STEP_TAG or %UNIQUE_SESSION_TAG. The latter tags will be expanded into a unique string according to the specified technology. Calls to this API within the same execution context are guaranteed to return the same unique name provided that the same parameters are passed to the call. e.g.

• <%=odiRef.getFormattedName(“%COL_PRFMY_TABLE%UNIQUE_STEP_TAG_AE”, “ORACLE”)%>
• <?=odiRef.getFormattedName(“%COL_PRFMY_TABLE%UNIQUE_STEP_TAG_AE”, “ORACLE”)?>
• C$_MY_TAB7wDiBe80vBog1auacS1xB_AE

• <?=odiRef.getFormattedName(“%COL_PRFMY_TABLE%UNIQUE_STEP_TAG.log”, “FILE”)?>
• C2_MY_TAB7wDiBe80vBog1auacS1xB.log

As part of name generation, the length of the generated name will be compared with the maximum length for the target technology and truncation may need to be applied. When a unique tag is included in the generated string it is important that uniqueness is not compromised by truncation of the unique tag. When a unique tag is NOT part of the generated name, the name will be truncated by removing characters from the end – this is the existing 11g algorithm. When a unique tag is included, the algorithm will first truncate the <postfix> and if necessary  the <prefix>. It is recommended that users will ensure there is sufficient uniqueness in the <prefix> section to ensure uniqueness of the final resultant name.

SUMMARY

To summarize, ODI 12c make it much simpler to utilize mappings in concurrent cases and provides APIs for helping developing any procedures or custom knowledge modules in such a way they can be used in highly concurrent, parallel scenarios.