ODI KM Template Load Data query

Load Data

This task reads data on the source connection and loads it into the loading table. This command is always executed.

The loading phase is always using auto commit, as ODI temporary tables do not contain unrecoverable data.

Command on Source

Note the use of the getFilter, getJoin, getFrom, etc. methods. these methods are shortcuts that return contextual expressions. For example, getFilter returns all the filter expressions executed on the source. Note also the use of the EXPRESSION property of getColList, that will return the source columns and the expressions executed on the source. These expressions and source columns are aliases after CX_COL_NAME, which is the name of their corresponding column in the loading table.

This select statement will cause the correct transformation (mappings, joins, filters, etc.) to be executed by the source engine.

select    <%=snpRef.getPop("DISTINCT_ROWS")%>      <%=snpRef.getColList("", "[EXPRESSION]\t[ALIAS_SEP] [CX_COL_NAME]", ",\n\t", "", "")%>from  <%=snpRef.getFrom()%>where      (1=1)<%=snpRef.getFilter()%><%=snpRef.getJrnFilter()%><%=snpRef.getJoin()%><%=snpRef.getGrpBy()%><%=snpRef.getHaving()%>

Command on Target

Note here the use of the biding using :[CX_COL_NAME]. The CX_COL_NAME binded value will match the alias on the source column.

insert into <%=snpRef.getTable("L", "COLL_NAME", "A")%>(   <%=snpRef.getColList("", "[CX_COL_NAME]", ",\n\t", "","")%>)values(   <%=snpRef.getColList("", ":[CX_COL_NAME]", ",\n\t", "","")%>)