There are lots of time , we have issues in dealing with huge load  and we break the process in to multiple steps like First unloading using OdiSqlunload or some native Unloader and then using the SQLLDR to load the file . The process can be time consuming and some time we don’t get the exact result.

To solve these part we have integrated the complete OdiSqlUnload with SQLLDR into one LKM with support to load directly into Target table or into the Work table as required. We have tried to make these as smart and easy it can be  like single KM for both unix and windows environment , Sqlldr log in the operator so you dont have to log into the server to check what is the error due to , Filter or join multiple tables yet getting the performance of odisqlunload .

This KM is created to move huge data from source to Target using ( odisqlunload to unload table into File ) and Loading this file using SQLLDR to oracle target , and is based on the integration of OdiSqlUnload and LKM File to Oracle ( SQLLDR ) .

The LKM can be used to load into C$ or directly into the Target Table.

The Options  are as follows

CREATE_WORK_TABLES
This option enables you to create appropriate Control File ,so that SQLLDR can load directly into the Target Table else into Work Table ( C$_). By Default  WORK_TABLES  value is  NO (ie. No C$ will be created will be loaded directly into Target table using Sqlldr )
IKM Dummy is required if  WORK_TABLES=NO else select the appropriate IKM.

DIR_PATH
Please  select  the appropriate Directory where the OdiSqlUnload can unload(File)  the data from Source Tables.

DELETE SQLLDR FILES
This options enables you to delete the SQLLDR  and OdiSqlUnload Files .

DATE_FORMAT
Please enter the valid Date format used by SQLLDR to convert the date into appropriate Format.
The default Format is YYYY-MM-DD HH24:MI:SS

TIMESTAMP_FORMAT

Please enter the Valid Timestamp format used by SQLLDR to convert the Timestamp into appropriate Format.
The default Format is YYYY-MM-DD HH24:MI:SS.FF

UNIX_ENVIRONMENT
Please select the  Environment  where the OdiSqlUnload will unload  ( Unix / Windows ) used into CTL file.
This options enables to have for Unix  (ODISQLUNLOAD) ROW_SEP =n and in(CTL file) =str X’0A’
and for Windows  (ODISQLUNLOAD) ROW_SEP =nr and in(CTL file) =str X’0D0A’ .

Also for deleting files according to the environment( rm / del )

The commands of the LKM are as follows –

UNLOADING TABLE

Unloading the source table into file ,delimited by (,) comma using OdiSqlUnload

OdiSqlUnload "-FILE=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("","[TABLE_NAME]","","")%>.dat"
"-DRIVER=<%=odiRef.getInfo("SRC_JAVA_DRIVER")%>"
"-URL=<%=odiRef.getInfo("SRC_JAVA_URL")%>"
"-USER=<%=odiRef.getInfo("SRC_USER_NAME")%>"
"-PASS=<%=odiRef.getInfo("SRC_ENCODED_PASS")%>"
"-FILE_FORMAT=VARIABLE" "-FIELD_SEP=,"
"-ROW_SEP=<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1"))
{ %>n<%} else { %>rn<% } %>"
"-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
"-FETCH_SIZE=<%=odiRef.getOption("FETCH_SIZE")%>"
"-QUERY=select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "")%>
<%=odiRef.getColList("", "[EXPRESSION]", "||','||", "", "")%> from <%=snpRef.getFrom()%>
where (1=1) <%=snpRef.getJoin()%>
<%=snpRef.getFilter()%> <%=snpRef.getGrpBy()%> <%=snpRef.getHaving()%>"

GENERATE CTL

If the work tables option – yes or No , then accordingly the CTL is created ie. whether the table to be loaded is C$ or Target table.

SnpsOutFile "-File=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].ctl", "", "")%>"
OPTIONS (
	SKIP=<%=odiRef.getSrcTablesList("", "[FILE_FIRST_ROW]", "", "")%>,
	ERRORS=<%=odiRef.getUserExit("LOA_ERRORS")%>,
	DIRECT=<%=odiRef.getUserExit("LOA_DIRECT")%>
	)
LOAD DATA
INFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[RES_NAME]", "", "")%>" "str X'
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>0A<%} else {%>0D0A<% } %>'"
BADFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].bad", "", "")%>"
DISCARDFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dsc", "", "")%>"
DISCARDMAX <%=odiRef.getUserExit("LOA_DISCARDMAX")%>
<% if (odiRef.getOption("CREATE_WORK_TABLES").equals("1")) { %>
INTO TABLE <%=odiRef.getTable("L", "COLL_NAME", "W")%>
FIELDS TERMINATED BY X'2C'
TRAILING NULLCOLS
(
	<%=odiRef.getColList("", "[CX_COL_NAME]
<? if (u0022[DEST_DT]u0022.equals(u0022DATEu0022))
{?> DATE '"+snpRef.getOption("DATE_FORMAT")+ "' <?}?>
<? if (u0022[DEST_DT]u0022.equals(u0022TIMESTAMPu0022))
{?> TIMESTAMP '"+snpRef.getOption("TIMESTAMP")+ "' <?}?>" , " ,nt", "","")%>

)
<% } else {%>
INTO TABLE <%=odiRef.getTargetTable("SCHEMA")%>.<%=odiRef.getTargetTable("TABLE_NAME")%>
FIELDS TERMINATED BY X'2C'
TRAILING NULLCOLS
(
	<%=odiRef.getColList("", "[COL_NAME]
<? if (u0022[DEST_DT]u0022.equals(u0022DATEu0022))
{?> DATE '"+snpRef.getOption("DATE_FORMAT")+ "' <?}?>
<? if (u0022[DEST_DT]u0022.equals(u0022TIMESTAMPu0022))
{?> TIMESTAMP '"+snpRef.getOption("TIMESTAMP")+ "' <?}?>" , " ,nt", "","")%>

)
<% } %>

CHANGE FILE PERMISSION

Changing the file permission so that SQLLDR and ODI can read the files

import os
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>
if os.system(r"chmod 770 <%=odiRef.getOption("DIR_PATH")%>/*.* ") <> 0:
	raise 'File Permission Issues'
<%} else { %>
raise ' File Permission Not required for Windows environment '
<% } %>

CALL SQLLDR

import os
if os.system(r"sqlldr userid=<%=odiRef.getInfo("DEST_USER_NAME")%>/
<%=odiRef.getInfo("DEST_PASS")%>@<%=odiRef.getInfo("DEST_DSERV_NAME")%>
control=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl","")%>
log=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%> ") <> 0:
	openlog=open('<%=odiRef.getOption("DIR_PATH")%>/
	<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%>','r')
	raise 'OS command has signalled errors.Please see the log for details ',
	openlog.readlines()
	openlog.close()

Here if the process fails and the log will be shows into Operator , enabling to see the log without logging into Unix Box.

Although the display is not perfect still it shows great

DELETE FILES

import os
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>
if os.system(r"rm -f <%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].dat", "")%>
<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl", "")%>
<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].bad", "")%>
<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%> ") <> 0:
	raise ' ODI has failed to delete files Please remove them manually if required '
<%} else { %>
if os.system(r"cmd /c del <%=odiRef.getOption("DIR_PATH")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].dat", "")%>;
<%=odiRef.getOption("DIR_PATH")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl", "")%>;
<%=odiRef.getOption("DIR_PATH")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].bad", "")%>;
<%=odiRef.getOption("DIR_PATH")%>
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%>") <> 0:
          raise 'ODI has failed to delete files Please remove them manually if required '
<% } %>

Lets look at an example ,

First run is No Work Tables , Delete Sqlldr files – No  and I am running in the Windows environment  so Unix _environment = No .

IKM Dummy have to selected if  Create Work Tables = No ( as we are loading directly into the Target table )

OdiSqlUnload

Control File

Files are not deleted

Now lets run the KM with the following option

Create Work Tables – Yes ,Delete SQLLDR Files – Yes , IKM SQL Control Append

Being the Create Work table option as yes the c$ is create and so the CTL is created with Flow table options and columns . Also the SQLLDR loads into the C$ table  and then finally using the selected IKM ( for my example IKM Sql control Append ) data is loaded from I$ to Target table.

Control File

Lets see the code in Unix Environment

With  Unix environment as Yes the row_sep is n and Infile ‘0A’

Being an Unix environment , there are file permission issue so change the permission for SQLLDR to read the files.

Here since the delete SQLLDR Files are yes , rm command is used to remove them

Also in case the SQLLDR encounters errors , i have provide the code to display the log files into the Operator, this way you don’t need to login into Unix to view the log file for error. Although the display is not proper but still it can give you enough information on what is the error due to ?

If there is no error the log file wont be displayed.

I have included the codes to support Joins and Filter , as you can see  in the OdiSqlUnload codes where condition, have the join and the filter.

Download the LKM below.

LKM OdiSqlUnload(File) to Oracle (SQLLDR)

Download XML

IKM Dummy

Download KM_IKM_DUMMY.xml