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.
