ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

LKM OdiSqlUnload(File) to Oracle ( SQLLDR )

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

image

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 ,

image

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

image

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

image

image

OdiSqlUnload

image

Control File

image

Files are not deleted

image

Now lets run the KM with the following option

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

image

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.

image

Control File

image

Lets see the code in Unix Environment

image

image

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

image

image

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

image

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

image

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.

image

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.

image

image

Download the LKM below.

LKM OdiSqlUnload(File) to Oracle (SQLLDR)

http://cid-d7195c8badc40d0b.office.live.com/embedicon.aspx/odiexperts.com/KM_LKM%20OdiSqlUnload5File6%20to%20Oracle%205SQLLDR6.xml

IKM Dummy

http://cid-d7195c8badc40d0b.office.live.com/embedicon.aspx/odiexperts.com/KM_IKM_DUMMY.xml

21 Comments

  1. Could you please let me know, where OdiSqlUnloader wants to unload the file(s)? Is it the odi-server, the agent-server or target-server?

    • Karlo ,

      Thanks for visiting us. By default everything will be carried out depending on Agent you selected. If you selected you Local(No Agent) then it wil lbe your local machine else the server where the respective Agent is located.

  2. Hi Devendra,

    I am sorry. I think you are referring to the LKM OdiSqlUnload(File) to Oracle ( SQLLDR ) post..My issue is about a CDC process under ODI by using a logical PK or UK. you can scroll up and find from my previous comments on this post.
    I had commented on a wrong post previously due to some urgency.

    Thanks,
    Chary

  3. Hi Cezar,

    I need to suggest this to a customer. can you please provide some detailed on this.

    Thanks,
    Chary

    • Chary,

      I beleive most of the detail have been mentioned in the post itself . This KM is planned to use where the data movement is high between source and target of different database and so using odisqlunload we can extract into File and later using SQLLDR move the data into Target. Please let us know if you need any other help.

  4. Hi Cezar,

    Thanks for your reply.
    I am a bit confused on the process you defined. Can you please provide me a detailed information/steps on this.

    Thanks in advance.
    Chary.

  5. Hi Guys,

    Can you please help me with the below requirement.

    I have a table in AS400 that don’t have a physical PK or UK defined.
    I have defined a logical UK for ODI and used CDC logic to capture the changes on the table. I have to replicate the changes of these table on Oracle 11g Database.
    when there is a change for this column in AS400 which i have defined as part of UK, ODI does an INSERT instead of an UPDATE. The result of this in Oracle we have some records that we don’t have in AS400.

    Is there any possibility to identify these records ?

    Also I would like ODI to recognize the OLD record (from the old UK) and delete it, and than INSERT the new record with the changed UK.

    How do i achieve this.? Any pointers.

    Thanks,
    Chary

    • Hi Chary,

      how are you?

      As the UK is updated and then you loose the “link” between source and origin, the only way to delete these records at target is creating a interface to a temp table all UK from source (just the UK you don’t need other columns) and then use a ODI procedure to perform a “delete… not exists..” on this temp table.

      In this way you will achieve what you need….

      Does it make any sense to you?

      Thank you for visiting us!!!!

  6. Hi,

    I am using ODI to load data from an ascii file ( pipe separated) to an Oracle Table. So I created a Interface with LKM as “File to Oracle (SQlLDR)” and IKM as “SQL Control Append”. CKM is “CKM Oracle”.

    I am using ODI Studio 11g with jdk 1.6 and am trying this on my windows XP machine. I also have sqlldr installed on my local machine. But still the interface fails with an error. The interface works if i use “LKM File to SQL” but i want to use SQLLDR because i will have high volumes coming in.

    Can you please help.

    Interface Error on Step Call sqlldr :

    org.apache.bsf.BSFException: exception from Jython:
    Traceback (innermost last):
    File “”, line 34, in ?
    Load Error: See K:Project GalaxyTech TeamODIDataFiles/NIN_GL_BALANCES.log for details

    at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:345)
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:169)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2374)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java:1615)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java:1580)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java:2755)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2515)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:534)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:449)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1954)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1818)
    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:559)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:481)
    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1040)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:114)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
    at java.lang.Thread.run(Thread.java:619)

    Jython Code :

    import java.lang.String
    import java.lang.Runtime as Runtime
    from jarray import array
    import java.io.File
    import os
    import re
    import javaos

    def reportnbrows():

    f = open(r”K:Project GalaxyTech TeamODIDataFiles/NIN_GL_BALANCES.log”;, ‘r’)
    try:
    for line in f.readlines():
    if line.find(“MAXIMUM ERROR COUNT EXCEEDED”)>=0 :
    raise line
    finally:
    f.close()

    ctlfile = r”"”K:Project GalaxyTech TeamODIDataFiles/NIN_GL_BALANCES.ctl”;”"
    logfile = r”"”K:Project GalaxyTech TeamODIDataFiles/NIN_GL_BALANCES.log”;”"
    outfile = r”"”K:Project GalaxyTech TeamODIDataFiles/NIN_GL_BALANCES.out”;”"
    oracle_sid=”
    if len(”)>0: oracle_sid = ‘@’+”

    loadcmd = r”"”sqlldr apps/%s control=”%s” log=”%s” > “%s” “”" % (oracle_sid,ctlfile, logfile, outfile)

    rc = os.system(loadcmd)

    if rc 0 and rc 2:
    raise “Load Error”, “See %s for details” % logfile

    if rc==2:
    reportnbrows()

    • Rajesh,

      Please look into the log file to see the error . Also make sure you are able to trigger the sqlldr outside and see if that works and try to call the sqlldr file and check to see its functions very well.
      Please reach us back if you still have issues.

  7. Dear All,
    I have problems in loading text files using ODI. There are spaces that I need to trim because each row the spaces are differrent in size. The other thing it removes the first 3-4 arabic charachters from each field.

    Thank

    • Nancy ,
      Are you using this particular KM and is your source Oracle ?
      you can use Trim to remove the spaces while loading into the Target.
      You mean to say why unloading into file the first 3-4 characters are remove from each field or is it happening while loading into Target. Please confirm ?

  8. Hi, iam using odisqlunload to unload data from as400 to flat file.
    first, i don’t know how to apply the concat operator || because this is specific from oracle, have you any ideas why proceed with as400?

    second, is not possible run odisqlunload in source server, iam running in target server odisqlunload reduce the execution time in 1 hour. if i try to put the odisqlunload execution in source server , can i reduce my execution time?

    thanks

    • Hi Raul,
      Although i have not tried odisqlunload with AS400 , i think it should work as behing the screen there are some java code to unload the data . The only think check for Date , Decimal formatting. Also yes the select query expression was modified so that concatenation of column make the fetch faster . Correct me if iam wrong i think there is come ‘concat’ function which you implement (or) else
      just use the query expression like this without pipes and use it .
      “-QUERY=select ”
      Let me know if you need any help.

  9. Hi,

    I don’t see any links to download this file!

  10. How would this work if the source table and target table are in two different database ?
    Will the OdiSqlUnload pull data over the network ?

    • Hi Mark,

      Yes , odiSqlUnload will pull over the network and unload , so please specify the path where the Agent is located.

      If in case you are not able to see optimal performance using OdiSqlunload , use the native database unloader and unload at the source side and do an FTP at the target server and load into the target.

      Please let us know if you require any more help.

  11. Guys
    The one comment which jumps out the page at me is that you are not using the [-FETCH_SIZE=] option on SQLUnload – when dealing with bulk data this is highly recommended – it will improve extract performance significantly. By default ODI will use the fetch size defined in topology – (normally 30 rows) but when doing this kind of processing I suggest 5,000-30,000 – depending on your data, only testing will tell where is best.
    In other performance tweaks, we have generated the extract sql to concatenate the fields together and pass a single string to ODI (e.g. rather than SELECT FIELD1, FIELD2, FIELD3… we generate SELECT FIELD1||’,'||FIELD2||’,'||TO_STRING(FILED3)..). It appears that the databases tend to be faster at doing that, and leaving ODI to just write a single string to the file. There are some examples of this KM available If you want I’ll dig you one out so you can compare performance.
    Craig

Leave a Reply

Required fields are marked *.