As in my previous post I have described how to implement HINTS per interface. It is a little bit tricky because HINTS are stored in development “ODI interfaces” and scenario of the interface must be regenerated when a HINT is changed and completely in developers’ hands.
I have thought about it and found a way to let dba’s change HINTS by updating a parameter table for each interfaces’/procedures’ each step. Once your dba updates/inserts the desired row (per SQL statement for each Knowledge Module or ODI Procedure) and commit the record it is been executed in the next execution of that interface or procedure.
My colleague, Fuat, prepared the necessary development to me and after reviewing the code, I want to share it with you. He blogged the same stuff, it is better for you to check his blog post from here.
1. Create a database table to store HINTS in text format;
- INTERFACE_NAME : Name of the Interface that will be queried to get the HINT.
- INTERFACE_KM : Name of the Knowledge Module that Interface is using (IKM or LKM).
- KM_STEP_NAME : Name of the step in Knowledge Module which will use the HINT.
- KM_STEP_ORDER : Order of the step of Knowledge Module.
- STEP_HINT_NAME : Name of the step of the Knowledge Module. This helps dba which step is sent to database.
- STEP_HINT_ORDER : There might be more than 1 hint per step of Knowledge Module, this column shows the order of HINT.
- HINT_VALUE : Exact value of HINT to be added into SQL Statement. This value should contain /*+ and */.
2. Now it is time to edit Knowledge Module and selecting HINTS into ODI Variables and use them.
2.1. Add a step for the first line of Knowledge Module. You can name this step as Get Hints.
Remember that all the HINTS to use in this Knowledge Module would be passed into #Hint_n variables for future use. Technology of “Command on Target” is Java BeanShell and “Command on Source” is Oracle. We already know which KM we are using, Step Names with the variable names and the last filter should be applied is Interface name which can be determined with the function <%= snpRef.getStep(“STEP_NAME”) %>. All the HINTS for this Knowledge Module are in variables now. It is time to use them.
2.2. In the below picture, it is showing how variabled HINTs are used. Syntax should be as “<@=HINT_NAME@>” where HINT_NAME should be same with the variable declared on the Knowledge Module step on the “Command on Target” section.
3. Here is how this method looks like in the operator (I know it is not so fancy but you need to trust ODI).
4. And the last step, adding variabled HINTS into SQL Statements.
Limitations;
* You cannot view HINTs session values within Operator logs. But you may insert the value of the HINT into a log table for each value per Interface for previous working values. All you have to do is adding a step in your Knowledge Module/ODI Procedure to insert the value of HINTS used.
