We are happy to introduce ODI_PARALLEL , which contains codes and KM specifically written to support the following functionalities

LKM File to DB (PARALLEL)	As the name suggests this KM is used to load up to 3 Files in Parallel , it does not matter what the File size is it still can process three Files and load into same C$ table in Parallel.
IKM File to DB ( Debug )	This KM is written to support around 90% of the File Loading requirement supporting Header , Footer , Skipping Certain Lines and the best of all capturing DB Errors into a separate table .
GET FILES IN THE FOLDER	This API is used to fetch all the File(s) present in the Folder based on the Mask Provided
GET FILE ROW COUNT	This API is used to fetch the Row Count of the File
GET FIRST 100 ROWS	This API is used to fetch the First 100 Rows of the File
GET LAST 100 ROWS	This API is used to fetch the Last 100 Rows of the File
JYTHON TO JAVA	This API is used to pass Jython Variable Value and retrieve as a Java Variable
UNLOADTABLE	SqlUnload with capabilities of loading 3 Files in Parallel with Header
IKM DB to File Append	Unload Table in a KM with Option to Unload in Append and Non Append Mode.
LKM_DB_TO_DB_PARALLEL	Loading C$ from different Server in Parallel
HTML EMAIL	HTML / Text Mode Email with multiple Authentication support
HTML EMAIL SQLQUERY	HTML Email feature along with SqlUnload presenting in multiple beautiful Style
HTML MULTIPART EMAIL	HTML Email with features to append Message N number of time and finally sending the Email . It contains three sub components HTML MULTIPART EMAIL INITIALIZE HTML MULTIPART EMAIL APPEND HTML MULTIPART EMAIL SEND

Please find the below codes in Details.

Note :- The Driver is still in Beta Version and may contain Bugs.

API

Please find a few of the File API to make task such as File Row Count , Files in the Folder , First and Last 100 Rows of the Files and Jython Variable to Java Variable easier . To access this API easier ,all the codes are placed into ODI Procedure so that User just drag and drop and provide the appropriate Options.

GET FILES IN THE FOLDER

This API is used to fetch all the File(s) present in the Folder based on the Mask Provided . The File extension is case Sensitive.

The Parameters are as follows for the above API

OPTION NAME	DESCRIPTION
LOGICAL_SCHEMA_DIR	Please provide the Logical Schema name to fetch the appropriate File’s Physical Schema Folder Path
DIR	Please provide the Directory Path
MASK	If Mask is empty then all the files in the Folder are Fetched else only the Files matching the( Ex   *.csv , *.xls ,    File*.txt , t*.txt and so on

Note : -  Please provide either the LOGICAL_SCHEMA_DIR (or) DIR , if both are provide then DIR takes precedence .

The Mask is Case Sensitive  *.CSV is not equal to *.csv

You can use the  Java Variable <@=Files@> in order to paste the files list in any other ODI Objects ( say OdiSendMail )

EXAMPLE 1 :- EXTENSION MATCHING MULTIPLE FILE

SAMPLE OPERATOR  OUTPUT

EXAMPLE 2 :- EXTENSION NOT MATCHING ANY FILE

SAMPLE  OPERATOR OUTPUT

GET FILE ROW COUNT

This API is used to fetch the Row Count of the File . FileName cannot be Empty.

OPTION NAME	DESCRIPTION
LOGICAL_SCHEMA_DIR	Please provide the Logical Schema name to fetch the appropriate File’s Physical Schema Folder Path
DIR	Please provide the Directory Path
FILENAME	Filename cannot be empty

Either provide the LOGICAL_SCHEMA_DIR(or) DIR , if both provided DIR options takes precedence.FILENAME cannot be empty.

You can use the Java Variable <@=RowNo@> in order to paste the files list in any other ODI Objects ( say OdiSendMail )

SAMPLE  OPERATOR OUTPUT

GET FIRST 100 ROWS

This API is used to fetch the First 100 Rows of the File .

OPTION NAME	DESCRIPTION
LOGICAL_SCHEMA_DIR	Please provide the Logical Schema name to fetch the appropriate File’s Physical Schema Folder Path
DIR	Please provide the Directory Path
FILENAME	Filename cannot be empty

Either provide the LOGICAL_SCHEMA_DIR(or) DIR , if both provided DIR options takes precedence.FILENAME cannot be empty.

You can use the Java Variable <@=First100Rows@> in order to paste the files list in any other ODI Objects ( say OdiSendMail )

SAMPLE OPERATOR OUTPUT

GET LAST 100 ROWS

This API is used to fetch the Last 100 Rows of the File .

OPTION NAME	DESCRIPTION
LOGICAL_SCHEMA_DIR	Please provide the Logical Schema name to fetch the appropriate File’s Physical Schema Folder Path
DIR	Please provide the Directory Path
FILENAME	Filename cannot be empty

Either provide the LOGICAL_SCHEMA_DIR(or) DIR , if both provided DIR options takes precedence.FILENAME cannot be empty.

You can use the Java Variable <@=Last100Rows@> in order to paste the files list in any other ODI Objects ( say OdiSendMail )

SAMPLE OPERATOR OUTPUT

JYTHON TO JAVA

This API is used to pass the Jython Variable and fetch it into Java Variable so the value can be used in any ODI Objects.

import api.getInfo as info;
info.setJythonVariable(" PROVIDE  JYTHON VARIABLE VALUE ");
<@
import api.getInfo;
String JythonOutput = getInfo.getJythonVariable();
@>

Now you can use  <@=JythonOutput@> variable anywhere to capture the Jython Variable Value.

UNLOADTABLE

OdiSqlUnload is great tool but it false short especially when Header is required and we tend to see alternative method which is again time consuming especially for bigger file and not User friendly for Non – Developer . We are launching a similar Utility called as UnloadTable . This utility not only allows Creating Header but you can unload table in Parallel . In order to enable parallel Unload provide the COMMAND_ON_SRC_SQL_QUERY .Please look into the below Examples for better understanding of usage.

There is limit of Unload 3 tables at a Time. The Logic is streamlined to always unload 3 Files in parallel, so even if one Table unloading completes , the next table in the queue gets unloaded, this way we are saving time and also being optimized and efficient.

NOTE :- FIXED WIDTH Files Unload Presently not Supported

OPTION_NAME	OPTIONAL	DESCRIPTION
COMMAND_ON_SRC_LOGICAL_SCHEMA	NO	Specify the Logical Schema of the Technology you wish to unload
COMMAND_ON_SRC_SQL_QUERY	YES	Specify the command on Source SQL for Parallel Operation
UNLOAD_DIR_LOGICAL_SCHEMA	YES	Specify the Logical Schema of the File Technology you will to use for Unloading . This option is great so you can universally control path across environments rather than manually handling .
UNLOAD_DIR	YES	Specify the Directory to Unload Files
UNLOAD_FILENAME	NO	Unload File Name
GENERATE_HEADER	NO	True – Generates Header else No . Default Value – True
DELIMITER	YES	Default Delimiter  , (comma)  . If delimiter is not specified no delimiter will be used
ROWSEP	YES	Default Row Separator \r\n  i.e. Windows Environment  . To Unload Files in Unix Environment use  \n
FETCHSIZE	YES	Default – 1000
CHARACTERSET	YES	UTF-8
SQL_QUERY	NO	Provide the SQL query to Unload

The UnloadTable is already written in such a way that all your will need to do is to drag and drop the UnloadTable Procedure  into Package and provide the appropriate option and Execute .

EXAMPLE 1   – SINGLE TABLE UNLOAD

For Single Table Unloading , don’t provide any Command on Source Query.

Provide the COMMAND_ON_SRC_LOGICAL_SCHEMA so that ODI can fetch connections to Unload the Table.

you can either provide UNLOAD_DIR_LOGICAL_SCHEMA (or) DIR . If both provide DIR takes precedence .

UNLOAD_FILENAME is to be provided and can be of any appropriate extension

GENERATE_HEADER is an optional . In Disable data getunload without Header.

DELIMITER – By Default if the delimiter is Blank then no delimiter will be used while unloading .

SQL_QUERY has to be provided.

SAMPLE AGENT OUTPUT

EXAMPLE 2 – MULTIPLE TABLES IN PARALLEL

This options is used to Unload Multiple Tables in Parallel.There is limitation of Unloading only 3 Files at a time. For Parallel Unloading appropriate COMMAND_ON_SRC_SQL_QUERY has to be provided. An example is provided below.

SAMPLE AGENT  OUTPUT

IKM DB  to File Append

This Multi Technology IKM uses the above UnloadTable logic to Unload Table  in Append/Non Append Mode. For Append just set Truncate to False for Non Append set it to True.

According the below option the File will be unloaded . For Append Mode set Truncate to False . Usage of UnloadTable make the File unloading Faster in Append and Non Append Mode .

Load C$ Parallel (LKM_DB_TO_DB_PARALLEL)

I always thought while loading the C$ tables that sometime we are wasting time especially loading from different Source Servers. Lets say we have three different source Server and we wait for each one of them to gets completed which always made me realize this could have been made in Parallel . This LKM is to used to load DB Source table in parallel so ETL load will be more faster .

As you can see from the below Example . There are three different Source datastore, which are actually from different Source database .

Set the LKM to DB to DB Parallel . If you are setting any one of them to LKM DB to DB Parallel all the other LKM has to be the same . The default commit Size is 10000 ie every 10,000 records will be commited.

Note :- All the Source have to be a DB Technology for this KM to work . File and XML’s are not supported.

You will need to add this Code as the First step in your Target IKM.If you miss this Option in the IKM the I$ table will be inaccurate.

import DataLoad.check as check
check.check_c_completion(<%=odiRef.getSession("SESS_NO")%>)

This particular code waits till all the C$ load is completed .

As you can see all the Source Datastore is analyzed and is unloaded in the background and the check_c$_completion waits till all the c$ processing is completed

SAMPLE OPERATOR OUTPUT

HTML EMAIL

OdiSendMail is a great utility but it falls short when authenticated Email or HTML Email needs to be send. ODIExperts is happy to announce HTML EMAIL , this Email provide multiple Authentication method support such a TLS/SSL and character Set Support.
For HTML email you will need to provide the HTML tags such h1 , font , color etc . HTML tags are optional if no tags are used email will be send in TEXT mode.
There is an attachment limitation of only 10 Files.

OPTION_NAME	OPTIONAL	DESCRIPTION
SMTP_SERVER	NO	SMTP SERVER NAME
SMTP_PORT	YES	DEFAULT PORT USED -25 . Please change the Port Number if required
USERNAME	YES	If the Email Service requires any USERNAME Authentication then please do so else leave it Blank
PASSWORD	YES	If the Email Service requires any PASSWORD Authentication then please do so else leave it Blank
FROM	NO	Provide the From Address Separated by Comma
TO	NO	Provide the To Address separated by comma
CC	YES	Provide the CC Address Separated by comma
SET_SSL	YES	If you wish to use SSL authentication method then enable True . Default – False
SET_TSL	YES	If you wish to use TSL authentication method then enable True . Default – False
CHARACTERSET	YES
SUBJECT	NO	Please Provide the Subject
MESSAGE	NO	Please provide the Message here
ATTACHMENT1	YES	Provide the File path separated by semi-colon
ATTACHMENT2	YES	Provide the File path separated by semi-colon
ATTACHMENT3	YES	Provide the File path separated by semi-colon
ATTACHMENT4	YES	Provide the File path separated by semi-colon
ATTACHMENT5	YES	Provide the File path separated by semi-colon
ATTACHMENT6	YES	Provide the File path separated by semi-colon
ATTACHMENT7	YES	Provide the File path separated by semi-colon
ATTACHMENT8	YES	Provide the File path separated by semi-colon
ATTACHMENT9	YES	Provide the File path separated by semi-colon
ATTACHMENT10	YES	Provide the File path separated by semi-colon

HTML EMAIL SQLQUERY

This Email combines HTML Email functionality and UnloadTable functionality .Instead of File it unload into Email. There are 5 pre built styles available as shown in the below Screenshot . Moreover ETL output not always have to be dull and boring      . By default no styling will be applied if no style is selected.

There  are 5 Different style Default has not HTML Styling while STYLE1 , STYLE 2 , STYLE3 , STYLE4 , STYLE5 has the following color Styling.

DEFAULT STYLE

STYLE1

STYLE2

STYLE3

STYLE4

STYLE5

HTML MULTIPART EMAIL

HTML MULTIPART EMAIL INITIALIZE

There are multiple times we tend to store Error Message or other Values into Files and keep appending the Error Message and Finally attaching the file. MULTIPART HTML is created to solve this Scenario .Now you can append the Data into Email and Finally send the Data.

Note :- Presently there is limitation that all the three components
HTML MULTIPART EMAIL INITIALIZE
HTML MULTIPART EMAIL APPEND
HTML MULTIPART EMAIL SEND
needs to in the Same package and cannot be called in Child Package .

HTML MULTIPART EMAIL INITIALIZE is used to initialize the Email with appropriate Email option and Initial Message.

HTML MULTIPART EMAIL APPEND

This component allows your append n number of times .

HTML MULTIPART EMAIL SEND

Finally you can send the Email and also you can attach update 10 Files as attachments.

LOADING FILES IN PARALLEL

Sometime we have multiple Files to be loaded in Parallel and we create multiple steps in the Package to read load and insert files . The other big issue LKM File to SQL does not supports huge Files. We have create two different KM to load Multiple Files .

LKM File to DB (Parallel)
IKM File to DB (Debug)

Before explaining deeper about the above KM , the Loading process algorithm is created in such a way it can load data faster without need of any DB utility like SQLLDR ,BCP which makes it universal across DB . Also you may be surprised the loading speed is more or less near to the File DB Utility like SQLLDR ,BCP . Also no File Limit it does not matter if its a small file or huge file of millions of Rows.

Now lets jump in the above KM which uses this interesting File loading algorithm

LKM File to DB (Parallel)

As the name suggests this KM is used to load upto 3 Files in Parallel , it does not matter what the File size is it still can process three Files and load into same C$ table in Parallel.

COMMIT_SIZE is set to 10,000 in case you see any tablespace issue or DB Memory issue try reducing the commit Size or ask your DBA to increase accordingly.

Also You dont have to specify separate process to read all the files in the folder and process it one by one this is automatically taken care. All you need to do is to provide the appropriate Mask in the Source DataStore Resource Name.

For Example there are 4 files

REG1.txt
REG2.txt
REG3.txt
REG4.txt

In the Resource Name just use REG*.txt , since * represent any characters all the files matching REG*.txt will be read and processed in Parallel.

REPLACE_CHARACTERS – If you wish to remove certain special characters while loading then provide the characters separated by comma .

For Ex :-  ",#,^ so characters ” # ^ will be removed while loading File

The C$ table apart from the File Columns adds two more Columns namely ODI_FILE_NAME & ODI_FILE_ROW_NO . As the column name suggest ODI_FILE_NAME stores the File Name processed while ODI_FILE_ROW_NO stores the RowNo of the File Records.

** By Default the C$ table is not Dropped. All the data is trimmed automatically so no trim is required.

SAMPLE OUTPUT OF C$

IKM File to DB (DEBUG)

This is one of the Multi Technology  KM that is written to support around 90% of the File Loading requirement  supporting Header , Footer , Skipping Certain Lines  and the best of all capturing DB Errors into a separate table .

This KM supports reading Multiple Files matching the Mask provided in the Resource Name .

Here are the Options of KM in Details .

OPTION	DESCRIPTION
COMMIT_SIZE	Every N number of the File Data is committed. Default Value – 1000 . Please don’t provide higher Values as that might lead to Java Memory Error .
CHARACTERSET	Please provide the CHARACTERSET of the File . Default character set – UTF-8
FOOTER	Provide the FOOTER value . Default Value 0
SELECTIVE_LINES	Provide  the value in (x,y) format where x – Number of Lines to Read and Y – Number of Lines to Skip . For 3,2 which means First 3 lines are Processed and Next 2 lines namely (4th and 5th rows are skipped) and next 3 ( 6,7 and 8 th)  lines are process and next 2 lines ( 9th and 10th) are skipped  and so on.
REMOVE_CHARACTERS	Provide the Special Characters to be removed while loading separated by comma( Ex   “,~,!)
IGNORE_LINE_STARTS_WITH	Sometime Files comes with unwanted comments or certain Lines are repeated multiple times . If you wish to skip such Lines from Loading then please provide First few Characters of the Line
MOVE_OR_COPY_FILES_DIR	If you wish to Move (or) Copy the Files into a Particular Directory after the Processing  then Please provide the complete Directory Path
MOVE_OR_COPY_FILES	Valid Values – MOVE (or) COPY . MOVE – Move the Files from the Source to Target and COPY -  Copy the Files from the Source to Target. Default – Null  . If Null , No Files are  Moved or Copied.Note :- If you don’t have the required permission to copy/Move File you will encounter appropriate Error

The KM also create an E$ Table with suffix FILE  which populates the source File Data  contains Error while Inserting into  Target Table   and  contains the following columns

ODI_FILE_NAME	Contains the Full path of the File
ODI_FILE_LOAD_TBL	Contains the Target Table Name
ODI_FILE_ROW_NO	Contains the File Row No
ODI_ERR_MESS	Contains the Detailed Error Message
ODI_CHECK_DATE	System Date
ODI_SESS_NO	Session Number of the Load
and Target  Columns Data with Data type VARCHAR	All the Target Columns are created with VARCHAR data type to accommodate any kind of Data. Note :- Only the first 256 Characters of the Column Data is Captured the rest is ignored.

The speed of the File Loading might vary  depending on the Errors captured. The higher the Number of the  Errors the slower the Loading time .

Lets look at a simple example with most of the components enabled.

SOURCE FILE

Multi Technology KM so  set the Staging Area different from Target to  File Logical Schema

Set the KM accordingly

OUTPUT

Here as you can see that every second line is inserted while the third line is skipped . The Footer is also skipped .

The great part of this KM is the ability to capture such DB errors while is useful in loading multiple Files

At the end of the Loading , in the step GET_STATUS the details are provided

IKM DB to DB Insert

This KM can select from Different Technology and Insert into Target Table without need for Source / Target Table . Similar to IKM Sql to Sql Append but performs considerably better .

DOWNLOAD

This is Beta Version and may contain some bugs . Please feel free to try and let us know your comments and suggestion to make it more user friendly for ODI Users