ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Data conversion / mapping

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Data conversion / mapping

    Hi all,

    I need to convert data from approximately 100 tables on one IBM I into another IBM I, the table layouts are different. I could brute force it and write 100 table conversion programs, but I was wondering if anyone is aware of any tooling for such a task? Altova has a tool that appears to support this type of job, any chance someone has used Altova?

    Thanks in advance,

    Steve

  • #2
    Wouldn't this just be a matter of writing an INSERT/SELECT statement that lists the fields in the right order?

    Its not clear to me what you'd use a 3rd party tool for, or why you'd write a custom program. Is some special logic required?

    Comment


    • #3
      Or write a program that generates 100 conversion programs.
      It might be the fastest way to do it.

      I assume that even though the layouts of the files are different then the fields are identical -- name, format, length, type etc.

      Comment


      • #4
        Guys, thanks for getting back to me. To clarify the tables and columns are different. Different table structures, different field lengths, different data types etc. We are converting one systems data into another system. Sometimes the conversion of a given table will be one to one from the source system to the target system, although some columns will need 'massaging' etc. Some columns have codes that will need to be converted etc. Other times there will be a one to many relationship from the source system to the target system. The Altova tool that I mentioned, has a gui that allows you to 'connect' a column from the source to a column on the target machine and the tool generates the needed sql insert etc. I was looking for a tool vs writing RPG program or sql stmnts as they will be thousands of columns involved and it will get laborious to all of this by hand.

        Comment


        • #5
          Just an idea:
          I did something similar when I had to install tables (and their content) on different systems with different levels of the application, i.e. some of the files where identical, some had more or less columns with different definitions, and some of the files did not even exist.
          I the first step I generated a table with 3 columns, table name, table library, XML (I have to admit the files where not very big). Then I generated an XML (with the SQL function XMLGROUP - this is the easiest way) containing all data of the file and saved it in my table.
          On the otherside, I read the the XML data with the XMLTABLE UDTF in composition with the information of the SYSCOLUMNS views in dynamic SQL ... and decomposed the data and inserted them into the tables.
          ... it worked great but it was a little tricky to program.

          Comment


          • #6
            You should focus on the tables on the target system.
            What files are needed to be updated there.
            Then create programs that generates the data for the files in the target system converting values from the source system.
            Thus you can determine the data source for a specific file.

            There might be 100 files in the source system but maybe only 50 files in the target system.
            This reduces the number of programs / files you have to convert.

            Comment

            Working...
            X