[ODI 11g] LKM using Named pipe for performance enhancement

성능 향상을 위한 Knowledge Module 개발 - Named pipe 사용

l  LKM Oracle(OdiSqlUnload) to SQLLDR(PIPE)

Description:

이 Loading KM 은 기존에 Cezer 님이 만든 OdiSqlUnload을 사용하여 Unload 한 후 SQL * Loader를 사용하여 Load하는 로직이 담겨 있는 LKM OdiSqlUnload(File) to Oracle (SQLLDR) 과 같은 로직을 사용 한다. 하지만 이 KM에서는 파일에 데이터를 다 내린 후에 로딩 작업을 진행하지 않고 파이프 방식을 사용하여 Unload를 하는 동시에 SQLLDR를 통해 Load 함으로써 시간을 단축시킬 수 있다.

Option:

이름	기본값	설명
CREATE_TARGET_TABLE	FALSE	whether create target table or not
COMPATIBLE	9	I do not use this option.
TRUNCATE	TRUE	about target table.
FETCH_SIZE	5000	when unloading data, this option used.
DIR_PATH	/home/oracle/odi_file_dmp	the path by which files are made in the km.
SOA_SEPERATOR	,	Field seperator.
LOA_CHARACTERSET	KO16MSWIN949	sqlldr Option character set
LOA_DIRECT	TRUE	sqlldr Option DIRECT
LOA_DISCARDMAX	1	sqlldr Option DISCARDMAX
LOA_ERRORS	0	sqlldr Option ERRORS
LOA_INSERT_TYPE	APPEND	sqlldr Option insert type. there are four types that you can choose:    truncate, insert, replace, append
LOA_OPTION		sqlldr Option. ex) UNRECOVERABLE
LOA_PARALLEL	TRUE	sqlldr Option parallel
DATE_FORMAT	YYYY-MM-DD HH24:MI:SS	sqlldr Option.
TIMESTAMP_FORMAT	YYYY-MM-DD HH24:MI:SS.FF	sqlldr Option.
NLS_DATE_FORMAT		this option used when control file is created.
NLS_DATE_LANGUAGE		this option used when control file is created.
NLS_NUMERIC_CHARACTERS		this option used when control file is created.
DELETE SQLLDR FILES	TRUE	clear files that km used in job

단계:

1.     Create pipe file
기술: Operating System
명령:

mkfifo <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dat", "", "")%>

설명: Unload와 Load 사용할 Named pipe를 생성한다.

2.     Generate CTL file
기술: ODI Tools
명령:

<?   package com.oracle.odi.km.ext;   public class FormatTranslator { SQL": DateTime Format Elements                                     static String[][] transTableDate = {                       {"y",                        "Y"},            // Year, supported:  yyyy;yy => 1996;96                       {"MMMM(M)*",         "month"},     // Month, supported: MMMM => July                       {"MMM",                                   "mon"},       // Month, supported: MMM => JUL                       {"M",                       "M"},           // Month, supported: MM;M => 07;7                       {"month",                                   "MONTH"},  // (auxiliary)                       {"mon",                    "MON"},      // (auxiliary)                       {"W",                       "W"},          // Week in month                       {"w",                       "WW"},       // Week in year                       {"DDD",                    "DDD"},       // Day in year                       {"dd",                      "DD"},         // Day in month                       {"a",                        "AM"},         // Am/pm marker                       {"HH",                     "HH24"},     // Hour in day (0-23)                       {"KK",                      "HH12"},     // Hour in day (0-11)                       {"mm",                    "MI"},          // Minute in hour                       {"SSSSSS",                "FF6"},        // Millisecond in second                       {"SSSSS",                                   "FF5"},        // Millisecond in second                       {"SSSS",                   "FF4"},        // Millisecond in second                       {"SSS",                     "FF3"},        // Millisecond in second                       {"SS",                      "FF2"},        // Millisecond in second                       {"S",                        "FF1"},        // Millisecond in second                       {"ss",                       "SS"},          // Second in minute                       {"Z",                        "TZH:TZM"}  // Timezone, supported: Z => +02:00                   };                                       public static String translate( String srcDT, String colFormat ) throws Exception {                                    String format = "";                                                                       if (colFormat.trim().length()==0)                                                      /* we use Oracle's default format */                                                      format = "";                                    else {                                                      // Is source column a date?                                                      if ( srcDT.toUpperCase().equals( "DATE") ) {                                                                        format = colFormat;                                                                        for (int i = 0; i < transTableDate.length; i++)                                                                                         format = format.replaceAll( transTableDate[i][0], transTableDate[i][1] );                                                      } else                                                      // Is source column a numeric?                                                      if ( srcDT.toUpperCase().equals( "NUMERIC")) {                                                                        format = "";                                                      } else                                                      // Is source column anything else than a string?                                                      if ( !srcDT.toUpperCase().equals( "STRING" ))                                                                        throw new Exception("No format translation defined for data type "+srcDT+"!");                                    };                                    if ( format.length() > 0 )                                                      format = format;                                    return format;                   }   }   public class sqlldrColFormat {                   public static String           nlsDateLanguage = "<%= (odiRef.getOption("NLS_DATE_LANGUAGE").length()==0)?"":"NLS_DATE_LANGUAGE=''"+odiRef.getOption("NLS_DATE_LANGUAGE")+"''"%>";                   public static boolean        enforceNlsNumChars = <%=(odiRef.getOption("NLS_NUMERIC_CHARACTERS").length()>0)?"true":"false"%>;                   public static String           nlsNumChars = "NLS_NUMERIC_CHARACTERS=''<%=odiRef.getOption("NLS_NUMERIC_CHARACTERS")%>''";                   public static String           nlsDateFormat = "<%=odiRef.getOption("NLS_DATE_FORMAT")%>";                       public static String get( String colName, srcDataType, destDataType, colFormat, colPrecision, colScale ) {                                    String format = "";                                    String oracleDataFormat = FormatTranslator.translate( srcDataType, colFormat );                                      // Is the source column a DATE?                                    if ("DATE".equals(srcDataType)) {                                                      dataType = "";                                                      dataConvFunc = "";                                                      // How many digits are there for milliseconds?                                                      int precision = colFormat.replaceAll("[^S]","").length();                                                      // Does the source column contain a timezone?                                                      if (colFormat.indexOf("Z")>-1) {                                                                        if (precision > 0) {                                                                                         dataType = "TIMESTAMP(" + precision.toString() + ") WITH TIME ZONE";                                                                        } else {                                                                                         dataType = "TIMESTAMP WITH TIME ZONE";                                                                        };                                                                        dataConvFunc = "TO_TIMESTAMP_TZ";                                                      // Does the source column contain milliseconds?                                                      } else if (precision > 0) {                                                                        dataType = "TIMESTAMP(" + precision.toString() + ")";                                                                        dataConvFunc = "TO_TIMESTAMP";                                                      // Okay, it's just a normal date!                                                      } else {                                                                        dataType = "DATE";                                                                        dataConvFunc = "TO_DATE";                                                      };                                                      // Does the source column contain a language specifc element and has a date language been specified?                                                      if ((colFormat.indexOf("MMM")>-1 ||                                                          colFormat.indexOf("a")>-1) &&                                                          nlsDateLanguage.length()>0) {                                                                        // Explicit conversion function required                                                                        format = " "+(char)34+dataConvFunc+"(:"+colName+", '"+oracleDataFormat+"', '"+nlsDateLanguage+"')"+(char)34;                                                      } else {                                                                        // Is there a column format defined for this column?                                                                        if (colFormat.length()>0) {                                                                                         // Implicit conversion by date_format clause                                                                                         format = " "+dataType+" "+(char)34+oracleDataFormat+(char)34;                                                                        } else {                                                                                         // Use client nls settings to convert date/timestamp                                                                                         format = "";                                                                        };                                                      };                                                        if (nlsDateFormat.length() > 0)                                                                        format = "DATE '"+nlsDateFormat+"'";                                    } else                                      // Is the source column a NUMERIC?                                    if ("NUMERIC".equals(srcDataType)) {                                                      // Do we need to enforce NLS settings for numbers?                                                      if (enforceNlsNumChars) {                                                                        precision = new Integer(colPrecision).intValue();                                                                        scale = new Integer(colScale).intValue();                                                                        // Build a number format according to precision and scale                                                                        digitsInFront = precision - ((scale==0)?0:(scale + 1));                                                                        digitsBehind = scale;                                                                        if (digitsInFront < 0)                                                                                         throw new Exception("Column " + colName +": Scale > Precision is not supported! Please correct precision/scale!");                                                                        format = " "+(char)34+"TO_NUMBER(:"+colName+", '";                                                                        format += "99999999999999999999999999999999999999".substring(0,digitsInFront);                                                                        if (digitsBehind>0) {                                                                                         format += "D";                                                                                         format += "99999999999999999999999999999999999999".substring(0,digitsBehind);                                                                        };                                                                        format += "', '"+nlsNumChars+"')"+(char)34;                                                      } else {                                                                        format = "DECIMAL EXTERNAL";                                                      };                                    } else {                                      // The source column is a STRING => add char(length) for length greater than 255                                                      taille = new Integer(colPrecision).intValue();                                                      if (taille > 254)                                                      format = "CHAR("+colPrecision+")";                                                      else                                                      format ="";                                    };                                      return format;                   } }   ?>SnpsOutFile "-File=<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].ctl", "", "")%>" OPTIONS (                   SKIP=<%=odiRef.getSrcTablesList("", "[FILE_FIRST_ROW]", "", "")%>,                   ERRORS=<%=odiRef.getUserExit("LOA_ERRORS")%>,                   DIRECT=<%=odiRef.getUserExit("LOA_DIRECT")%>,                   PARALLEL=<%=odiRef.getUserExit("LOA_PARALLEL")%>                   ) LOAD DATA CHARACTERSET '<%=odiRef.getUserExit("LOA_CHARACTERSET")%>' INFILE "<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dat", "", "")%>" "str X'0A'" BADFILE "<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].bad", "", "")%>" DISCARDFILE "<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dsc", "", "")%>" DISCARDMAX <%=odiRef.getUserExit("LOA_DISCARDMAX")%> INTO TABLE <%=snpRef.getTable("L","TARG_NAME","A")%>  <%=odiRef.getUserExit("LOA_INSERT_TYPE")%> FIELDS TERMINATED BY '<%=odiRef.getUserExit("SOA_SEPERATOR")%>' TRAILING NULLCOLS (                   <%=odiRef.getTargetColList("", "[COL_NAME]\t"+                                    "<?=sqlldrColFormat.get(\u0022[COL_NAME]\u0022,\u0022[SOURCE_DT]\u0022, \u0022[DEST_DT]\u0022, \u0022[COL_FORMAT]\u0022, \u0022[LONGC]\u0022, \u0022[SCALE]\u0022)?>"                   , ",\n\t", "","")%> )

설명: SQL * Loader 에 사용될 Control file을 생성한다.

3.     Execute sqlldr & OdiSqlUnload
기술: ODI Tools
명령:

OdiOSCommand "-OUT_FILE=<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("[TABLE_NAME].out", "")%>" "-ERR_FILE=<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("[TABLE_NAME].err", "")%>" sqlldr "control='<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].ctl", "", "")%>'" "log='<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].log", "", "")%>'" userid=<%=odiRef.getInfo("DEST_USER_NAME")%>/<%=odiRef.getInfo("DEST_PASS")%>@<%=odiRef.getInfo("DEST_DSERV_NAME")%> & sh startcmd.sh 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=<%=odiRef.getOption("SOA_SEPERATOR")%>""-ROW_SEP=\n" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1" "-FETCH_SIZE=<%=odiRef.getOption("FETCH_SIZE")%>" "-QUERY=select <%=odiRef.getColList("", "[EXPRESSION]", "||','||", "", "")%> from <%=snpRef.getFrom()%> where (1=1) <%=snpRef.getJoin()%> <%=snpRef.getFilter()%> <%=snpRef.getGrpBy()%> <%=snpRef.getHaving()%>"

4.     Delete temporary files
기술: Operating System
명령:

rm <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dat", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].log", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].ctl", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].sql", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].sh", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].bad", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].out", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].err", "", "")%>

설명: 작업시 사용했던 file들을 삭제한다.

 

l  LKM Oracle(Spool) to SQLLDR(PIPE) v1.0

Description:

이 Loading KM은 Oracle Sqlplus에서 사용할수 있는 기능인 spool 기능을 사용하여 데이터를 Unload 한다. Load 시에는 SQL * Loader를 사용하는데, 이 때 Unload와 Load를 동시에 진행할 수 있도록 Named pipe file을 사용하여 파이프 방식으로 언로드와 로드를 동시에 진행하여 성능 향상의 장점이 있다.

Option:

이름	기본값	설명
DIR_PATH	/home/oracle/odi_file	the path by which files are made in the km.
SOA_LINESIZE	225	Spool Option. A row’s total length. Sum(length(*))
SOA_SEPERATOR	,	Field seperator.
LOA_CHARACTERSET	KO16MSWIN949	sqlldr Option.character set.
LOA_DIRECT	TRUE	sqlldr Option DIRECT
LOA_DISCARDMAX	1	sqlldr Option DISCARDMAX
LOA_ERRORS	0	sqlldr Option ERRORS
LOA_INSERT_TYPE	APPEND	sqlldr Option insert type. there are four types that you can choose:    truncate, insert, replace, append
LOA_OPTION		sqlldr Option. ex) UNRECOVERABLE
LOA_PARALLEL	TRUE	sqlldr Option parallel
DATE_FORMAT	YYYY-MM-DD HH24:MI:SS	sqlldr Option.
TIMESTAMP_FORMAT	YYYY-MM-DD HH24:MI:SS.FF	sqlldr Option.
NLS_DATE_FORMAT		this option used when control file is created.
NLS_DATE_LANGUAGE		this option used when control file is created.
NLS_NUMERIC_CHARACTERS		this option used when control file is created.
DELETE_TEMPORARY_OBJECTS	TRUE	clear files that km used in job

단계:

1.     Create pipe file
기술: Operating System
명령:

mkfifo <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dat", "", "")%>

설명: Unload와 Load 사용할 Named pipe를 생성한다.

2.     Create sql
기술: ODI Tools
명령:

SnpsOutFile "-FILE=<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].sql", "", "")%>" /* LKM Spooling Unload File v1.0 */ set pause off set head off set feedback off set timing off set pagesize 0 set linesize <%=odiRef.getUserExit("SOA_LINESIZE")%> set echo off set term off set trimspool on spool <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dat", "", "")%> select <%=odiRef.getColList("", "[EXPRESSION]", "||'"+odiRef.getUserExit("SOA_SEPERATOR")+"'||", "", "")%> from <%=snpRef.getFrom()%> where (1=1) <%=snpRef.getJoin()%> <%=snpRef.getFilter()%> <%=snpRef.getGrpBy()%> <%=snpRef.getHaving()%>; spool off

           설명: sqlplus에서 실행할 sql 파일을 생성한다. 여기에 파일로 Unload하는 쿼리가 삽입된다.

3.     Create sh File
기술: ODI Tools
명령:

SnpsOutFile "-FILE=<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].sh", "", "")%>" sqlplus -S <%=odiRef.getInfo("SRC_USER_NAME")%>/<%=odiRef.getInfo("SRC_PASS")%>@<%=odiRef.getInfo("SRC_DSERV_NAME")%> << EOF @<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].sql", "", "")%> EOF

설명: sqlplus에 접속하여 sql을 호출하는 스크립트를 생성한다. Linux 기반으로 작성되어 있다.

4.     Generate CTL file
기술: ODI Tools
명령:

<?   package com.oracle.odi.km.ext;   public class FormatTranslator {                   // Java Date Formatting information available at http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html                   // Oracle Date Formatting information available in Oracle SQL Language Reference: Chapter "Basic Elements fo Oracle SQL": DateTime Format Elements                                     static String[][] transTableDate = {                       {"y",                        "Y"},            // Year, supported:  yyyy;yy => 1996;96                       {"MMMM(M)*",         "month"},     // Month, supported: MMMM => July                       {"MMM",                                   "mon"},       // Month, supported: MMM => JUL                       {"M",                       "M"},           // Month, supported: MM;M => 07;7                       {"month",                                   "MONTH"},  // (auxiliary)                       {"mon",                    "MON"},      // (auxiliary)                       {"W",                       "W"},          // Week in month                       {"w",                       "WW"},       // Week in year                       {"DDD",                    "DDD"},       // Day in year                       {"dd",                      "DD"},         // Day in month                       {"a",                        "AM"},         // Am/pm marker                       {"HH",                     "HH24"},     // Hour in day (0-23)                       {"KK",                      "HH12"},     // Hour in day (0-11)                       {"mm",                    "MI"},          // Minute in hour                       {"SSSSSS",                "FF6"},        // Millisecond in second                       {"SSSSS",                                   "FF5"},        // Millisecond in second                       {"SSSS",                   "FF4"},        // Millisecond in second                       {"SSS",                     "FF3"},        // Millisecond in second                       {"SS",                      "FF2"},        // Millisecond in second                       {"S",                        "FF1"},        // Millisecond in second                       {"ss",                       "SS"},          // Second in minute                       {"Z",                        "TZH:TZM"}  // Timezone, supported: Z => +02:00                   };                                       public static String translate( String srcDT, String colFormat ) throws Exception {                                    String format = "";                                                                       if (colFormat.trim().length()==0)                                                      /* we use Oracle's default format */                                                      format = "";                                    else {                                                      // Is source column a date?                                                      if ( srcDT.toUpperCase().equals( "DATE") ) {                                                                        format = colFormat;                                                                        for (int i = 0; i < transTableDate.length; i++)                                                                                         format = format.replaceAll( transTableDate[i][0], transTableDate[i][1] );                                                      } else                                                      // Is source column a numeric?                                                      if ( srcDT.toUpperCase().equals( "NUMERIC")) {                                                                        format = "";                                                      } else                                                      // Is source column anything else than a string?                                                      if ( !srcDT.toUpperCase().equals( "STRING" ))                                                                        throw new Exception("No format translation defined for data type "+srcDT+"!");                                    };                                    if ( format.length() > 0 )                                                      format = format;                                    return format;                   }   }   public class sqlldrColFormat {                   public static String           nlsDateLanguage = "<%= (odiRef.getOption("NLS_DATE_LANGUAGE").length()==0)?"":"NLS_DATE_LANGUAGE=''"+odiRef.getOption("NLS_DATE_LANGUAGE")+"''"%>";                   public static boolean        enforceNlsNumChars = <%=(odiRef.getOption("NLS_NUMERIC_CHARACTERS").length()>0)?"true":"false"%>;                   public static String           nlsNumChars = "NLS_NUMERIC_CHARACTERS=''<%=odiRef.getOption("NLS_NUMERIC_CHARACTERS")%>''";                   public static String           nlsDateFormat = "<%=odiRef.getOption("NLS_DATE_FORMAT")%>";                       public static String get( String colName, srcDataType, destDataType, colFormat, colPrecision, colScale ) {                                    String format = "";                                    String oracleDataFormat = FormatTranslator.translate( srcDataType, colFormat );                                      // Is the source column a DATE?                                    if ("DATE".equals(srcDataType)) {                                                      dataType = "";                                                      dataConvFunc = "";                                                      // How many digits are there for milliseconds?                                                      int precision = colFormat.replaceAll("[^S]","").length();                                                      // Does the source column contain a timezone?                                                      if (colFormat.indexOf("Z")>-1) {                                                                        if (precision > 0) {                                                                                         dataType = "TIMESTAMP(" + precision.toString() + ") WITH TIME ZONE";                                                                        } else {                                                                                         dataType = "TIMESTAMP WITH TIME ZONE";                                                                        };                                                                        dataConvFunc = "TO_TIMESTAMP_TZ";                                                      // Does the source column contain milliseconds?                                                      } else if (precision > 0) {                                                                        dataType = "TIMESTAMP(" + precision.toString() + ")";                                                                        dataConvFunc = "TO_TIMESTAMP";                                                      // Okay, it's just a normal date!                                                      } else {                                                                        dataType = "DATE";                                                                        dataConvFunc = "TO_DATE";                                                      };                                                      // Does the source column contain a language specifc element and has a date language been specified?                                                      if ((colFormat.indexOf("MMM")>-1 ||                                                          colFormat.indexOf("a")>-1) &&                                                          nlsDateLanguage.length()>0) {                                                                        // Explicit conversion function required                                                                        format = " "+(char)34+dataConvFunc+"(:"+colName+", '"+oracleDataFormat+"', '"+nlsDateLanguage+"')"+(char)34;                                                      } else {                                                                        // Is there a column format defined for this column?                                                                        if (colFormat.length()>0) {                                                                                         // Implicit conversion by date_format clause                                                                                         format = " "+dataType+" "+(char)34+oracleDataFormat+(char)34;                                                                        } else {                                                                                         // Use client nls settings to convert date/timestamp                                                                                         format = "";                                                                        };                                                      };                                                        if (nlsDateFormat.length() > 0)                                                                        format = "DATE '"+nlsDateFormat+"'";                                    } else                                      // Is the source column a NUMERIC?                                    if ("NUMERIC".equals(srcDataType)) {                                                      // Do we need to enforce NLS settings for numbers?                                                      if (enforceNlsNumChars) {                                                                        precision = new Integer(colPrecision).intValue();                                                                        scale = new Integer(colScale).intValue();                                                                        // Build a number format according to precision and scale                                                                        digitsInFront = precision - ((scale==0)?0:(scale + 1));                                                                        digitsBehind = scale;                                                                        if (digitsInFront < 0)                                                                                         throw new Exception("Column " + colName +": Scale > Precision is not supported! Please correct precision/scale!");                                                                        format = " "+(char)34+"TO_NUMBER(:"+colName+", '";                                                                        format += "99999999999999999999999999999999999999".substring(0,digitsInFront);                                                                        if (digitsBehind>0) {                                                                                         format += "D";                                                                                         format += "99999999999999999999999999999999999999".substring(0,digitsBehind);                                                                        };                                                                        format += "', '"+nlsNumChars+"')"+(char)34;                                                      } else {                                                                        format = "DECIMAL EXTERNAL";                                                      };                                    } else {                                      // The source column is a STRING => add char(length) for length greater than 255                                                      taille = new Integer(colPrecision).intValue();                                                      if (taille > 254)                                                      format = "CHAR("+colPrecision+")";                                                      else                                                      format ="";                                    };                                      return format;                   } }   ?>SnpsOutFile "-File=<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].ctl", "", "")%>" OPTIONS (                   SKIP=<%=odiRef.getSrcTablesList("", "[FILE_FIRST_ROW]", "", "")%>,                   ERRORS=<%=odiRef.getUserExit("LOA_ERRORS")%>,                   DIRECT=<%=odiRef.getUserExit("LOA_DIRECT")%>,                   PARALLEL=<%=odiRef.getUserExit("LOA_PARALLEL")%>                   ) LOAD DATA CHARACTERSET '<%=odiRef.getUserExit("LOA_CHARACTERSET")%>' INFILE "<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dat", "", "")%>" "str X'0A'" BADFILE "<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].bad", "", "")%>" DISCARDFILE "<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dsc", "", "")%>" DISCARDMAX <%=odiRef.getUserExit("LOA_DISCARDMAX")%> INTO TABLE <%=snpRef.getTable("L","TARG_NAME","A")%>  <%=odiRef.getUserExit("LOA_INSERT_TYPE")%> FIELDS TERMINATED BY '<%=odiRef.getUserExit("SOA_SEPERATOR")%>' TRAILING NULLCOLS (                   <%=odiRef.getTargetColList("", "[COL_NAME]\t"+                                    "<?=sqlldrColFormat.get(\u0022[COL_NAME]\u0022,\u0022[SOURCE_DT]\u0022, \u0022[DEST_DT]\u0022, \u0022[COL_FORMAT]\u0022, \u0022[LONGC]\u0022, \u0022[SCALE]\u0022)?>"                   , ",\n\t", "","")%> )

설명: SQL * Loader 에 사용될 Control file을 생성한다.

5.     Execute sqlldr & spool
기술: ODI Tools
명령:

OdiOSCommand "-OUT_FILE=<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("[TABLE_NAME].out", "")%>" "-ERR_FILE=<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("[TABLE_NAME].err", "")%>" sqlldr "control='<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].ctl", "", "")%>'" "log='<%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].log", "", "")%>'" userid=<%=odiRef.getInfo("DEST_USER_NAME")%>/<%=odiRef.getInfo("DEST_PASS")%>@<%=odiRef.getInfo("DEST_DSERV_NAME")%> & sh <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].sh", "", "")%>

6.     Delete temporary files
기술: Operating System
명령:

rm <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dat", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].log", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].ctl", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].sql", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].sh", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].bad", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].out", "", "")%> <%=odiRef.getUserExit("DIR_PATH")%>/<%=odiRef.getSrcTablesList("", "[TABLE_NAME].err", "", "")%>

설명: 작업시 사용했던 file들을 삭제한다.

Usage of the LKM using Named pipe :
LKM : pipe
IKM : IKM dummy

done.

KM_LKM_Oracle_Spool__to_SQLLDR_PIPE__v1_0.xml
 

KM_LKM_Oracle_OdiSqlUnload__to_SQLLDR_PIPE__v1_0.xml