This content has been marked as final. Show 5 replies
I can see no way to do that with only one interface in the current version of ODI.
Future versions might include multi-loading features and you will be able to play with the number of row.
For the moment I would go for a procedure.
Actually, it won't be a "small" KM modification.
But it may be possible.
Based on the "IKM SQL to File Append" and "LKM File to SQL"
First, modify the "LKM File to SQL"
- in the "Create work table" step, add a new column named RANK_ROW (number)
- in the "Load data" step, populate this new column. For example, with rownum (1, 2, 3, 4...).
Then, modify the "IKM SQL to File Append"
- Duplicate 10 times the steps "Truncate target file" ; "Insert column headers" and "Insert new rows"
- In each step, modify <%=snpRef.getTargetTable("TABLE_NAME")%> to add the number. Example <%=snpRef.getTargetTable("TABLE_NAME")%>_1 ; <%=snpRef.getTargetTable("TABLE_NAME")%>_2 ...
- In "Insert new rows" step, modify the "command as source" to add FILTER in the where clause.
Filter on the RANK.
For example, in the first one, you write "and RANK between 1 and 9"
In the second one, "and RANK between 10 and 19"
You could improve that by using Jython to loop on the rank, and avoid duplicate the steps... but it will be more complicated.
I once had a similar task and successfully used the approach described here: https://blogs.oracle.com/dataintegration/entry/odi_11g_parallel_pipelined_unload
See, if this might be helpful for you, too.
Hi A. Drieux,
Thanks for the response !
Actually i don't need the "LKM File to SQL" because my source is MS SQL Server so i am having Staging on Source so in that case i don't need to have the LKM, only IKM i required which is "IKM MS SQL to FILE APPEND".
so can you please help me bit to modify the IKM only for the purpose.
appreciate your help !
Hi1 person found this helpful
You can use the same logic, even without LKM.
You juste have to create this "rank" column on your master source table.
Then filter on it in each duplicated step of your IKM.
If you're not allowed to create this column on source table, then create a temporary table (with an extra interface) that will store your data with this rank. You can for example use "yellow interface" to do this.
PS : do no hesitate to mark helpful posts as "correct" or "helpful".