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 an execution failed in ODI 11g then temporary tables would have been left around and cleaned up in
the subsequent run. In ODI 12c, KM tasks can now have a cleanup-type task which is executed even after a failure in
the main tasks. These cleanup tasks will be executed even on failure if
the property 'Remove Temporary Objects on Error' is set.
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.
2. Secondly as a Procedure or KM Developer.....
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:-
<?=odiRef.getObjectName("L", "%COL_PRFEMP%UNIQUE_STEP_TAG", "D")?>
SCOTT.C$_EABH7QI1BR1EQI3M76PG9SIMBQQ
<?=odiRef.getObjectName("L", "EMP%UNIQUE_STEP_TAG_AE", "D")?>
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
2.3 Name length generation
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.
