- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 535.7K On-Premises Infrastructure
- 138.1K Analytics Software
- 38.6K Application Development Software
- 5.6K Cloud Platform
- 109.3K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
ODI 12.2.1 - Truncate the target Datastore without Knowledge Module's TRUNCATE option
Here is my situation: I can't truncate the target Datastore with Knowledge Module's TRUNCATE option before mapping starts (missing GRANT permissions) so I'm looking for an alternative.
I was thinking about getting target table names of my mappings (inside Package or Load Plan) and then forward target table names as a parameter to my stored procedure on database which will do the job.
I have few options in mind:
Option with PACKAGE:
I will put a variable as a First Step in package. It would be a Refresh Type variable and it would trigger a SQL query:
select replace(substr('<%=odiRef.getStep("OK_NEXT_STEP_NAME")%>',3),'_INSERT','') from dual
This variable would save STEP_NAME of its next step in package and after that I would forward that value to ODI procedure
Stored procedure will do the truncate and after its done my mapping can start its load.
odiRef.getStep("OK_NEXT_STEP_NAME") returns empty value when variable tries to get STEP_NAME of next mapping. Bug I guess?
The second problem is to insert ODI procedure somewhere in package. It somehow doesn't fit in the middle, between variable and mapping, and I'm not sure if it's correct to put procedure inside Begin Mapping Command of a mapping. My mapping naming convention looks like this: M_<target_table_name>_INSERT, so that's why I have to do substring over STEP_NAME.
Option with ODI REPO TABLES:
There will be lots of sequentially ordered mappings inside package and any type of manual job like renaming steps etc is not an option. I need to get a list of mappings inside my package and for this case I would need an advice on which ODI REPO tables to join in order to get this information. SNP_MAPPING, SNP_PACKAGE and SNP_STEP are probably the ones I need but I'm not 100% sure if I need anything else. After I get the list of mappings they are forwarded to stored procedure.
I'm also looking for a good advice on how to implement all of this in my LOAD PLAN. I have over 100 tables to load with data, each needs to be truncated first and then loaded so I guess dragging 100 tables inside package is not a good option.
If somebody has experience with this kind of situation I would be very thankful if you can share your knowledge.
Thanks in advance!