ODI 12c Direct Load with no Flow control $ Tables from Source to Target

ODI 12c Direct Load with no Flow control $ Tables from Source to Target

 

A very basic load process with no controls apart from a truncate. Add some controls to it to make it slicker though…… Couldn’t see any other simple blog on this subject with ODI 12c . We always get people asking this question. Its basic we agree but still its one of those things everyone will need now and then.

 

Copy LKM SQL to SQL and rename it to LKM SQL to SQL No Flow (Or whatever you like).

Create 2 steps

 The first step to truncate the target table if the option is set to true on the KM and the next step simply to insert data from source to target in the target table.

Truncate Table

1. Target Command

<% if (odiRef.getOption( "TRUNCATE" ).equals( "1" )) { %>

truncate table <%=odiRef.getTable("L","TARG_NAME","A")%>

<% } %>

 

2. Add Options

TRUNCATE

Step Commands to Load data from Source to Target table.

Target Command

insert into <%=odiRef.getTable("L", "TARG_NAME", "A")%>

(

                    <%=odiRef.getColList("", "[CX_COL_NAME]", ",\n\t", "","")%>

)

values

(

                    <%=odiRef.getColList("", ":[CX_COL_NAME]", ",\n\t", "","")%>

)

 

Source Command

<%for (int i=odiRef.getDataSetMin(); i <= odiRef.getDataSetMax(); i++){%>

<%=odiRef.getDataSet(i, "Operator")%>

select        <%=odiRef.getPop("DISTINCT_ROWS")%>

                    <%=odiRef.getColList(i, "", "[EXPRESSION]\t[ALIAS_SEP] [CX_COL_NAME]", ",\n\t", "", "")%>

from          <%=odiRef.getFrom(i)%>

where       (1=1)

<%=odiRef.getFilter(i)%>

<%=odiRef.getJrnFilter(i)%>

<%=odiRef.getJoin(i)%>

<%=odiRef.getGrpBy(i)%>

<%=odiRef.getHaving(i)%>

<%}%>

 

IKM Clone as a dummy

 

Clone IKM SQL Control Append again Rename it to whatever you like.

 

Remove all the steps.

 

 

 

USE THE IKM and LKM in your mapping and it should run with no $ Tables.

 

After You have created your mapping and used the LKM and IKM created.

 

Operator log will look like below with your named steps etc.
 

 

This is a very simple example . I advise you build in some more control functionality etc… KM customisation is always fun. I have uploaded example Knowledge modules to the DELIVERBI document portal if you don't want to undertake these very simple tasks....

Have a look at the XML files to Import to ODI 12c as an example

Thanks

Sha & Krish