3 Replies Latest reply: Jul 28, 2013 5:42 AM by umit RSS

    Reverse engineer packed decimal (EBCDIC) file format using ODI 11g (ODI 11.1.1.7)

    srivatsan purushothaman

      Hi,

       

      I am trying to load the data from a packed decimal file (EBCDIC) to tables in Oracle Database tables. These files have originated from IBM mainframe systems. In order to create the corresponding datastore for the source file, I am trying to reverse engineer the above flat file that I have got.  In ODI, I am not able to see any specific RKMs for this type of file.  The generic one available "RKM File" is also not capable of getting the definition.  I even tried the option "Reverse Engineer Cobol Copybook" but getting an error message "Not a valid cobol copy book".

       

      If any one has done simillar things, can you please help me out on how to accomplish this?

        • 1. Re: Reverse engineer packed decimal (EBCDIC) file format using ODI 11g (ODI 11.1.1.7)
          Ayush Ganeriwal-Oracle

          Can you try setting proper encoding in the file driver properties as specified in Files - 11g Release 1 (11.1.1). ODI would be able to read the file if it is java supported encoding.

          • 2. Re: Reverse engineer packed decimal (EBCDIC) file format using ODI 11g (ODI 11.1.1.7)
            srivatsan purushothaman

            Hi Ayush,

            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.

            • 3. Re: Reverse engineer packed decimal (EBCDIC) file format using ODI 11g (ODI 11.1.1.7)
              umit

              Hi,

               

              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" .


              Thx