I the encoding for the file is ISO8859-1 as provided by the mainframes team. But I am not sure which RKM and how to use it for performing the reverse engineering and get the data definition into ODI. I also tried creating the datastore manually and process the file using the existing LKM for file but that too failed. So I am kind of lost and looking for some steps on how to perform this task.
The next best option for me after ODI is SQL*Loader (I can modify the KM to use the SQL*Loader utility) but that too is not working. After surfing in many forums, I got an LKM which was developed by the users and yet to be certified by Oracle, "KM_LKM_File_to_Oracle__SQLLDR__with_EBCDIC_Option". But this option is also not working out as the SQL*Loader is unable to read this kind of file.
Basically after doing many research, I found that for doing this kind of task, it has to be carried out in two steps. But I am not getting pointers on how to do it through ODI.
1. Converting the EBCDIC to ASCII
2. Loading the converted file to tables.
Make a table with the same structure with your file.For example,there is one column like ANY_ID decimal (10),then you can think it as NUMBER(10)..
Then move or copy this table under your flat file model.When you open that table (under file model) will see packed decimal,unsigned packed decimal,binary signed pack decimal types.
You can chose all columns type like this.
Now,you have a file on source and target is oracle table.You must covert your varchar2 type EBCDIC to ASCII when your make mapping.And also modify your LKM file to oracle.
Or you can use "KM_LKM_File_to_Oracle__SQLLDR__with_EBCDIC_Option" .