jump to navigation

Data Mapping Question August 23, 2006

MMUG received an email from a iSeries user about data mapping. If some one has dealt with this problem before and can help this user, please contact us or post the solution by clicking on Comments.

We are looking to see if there are any tools available or maybe 3rd party software available that other iSeries users have used and have been successful in a data move.

We will be migrating to an ‘off the shelf’ software package. Prior to the move, we want to have as much as possible, “clean data” to migrate into the new software package. We have currently been using queries and sql statements to identify the bad data, fix it, then use the CPYF command in a CL to copy the data into our staging files. These staging files will then be used to migrate the data into the new software. We are thinking there is a “better way” or a more efficient way.

What we have been running into is that if you run the CPYF CL first, it will stop on the first record that contains bad data (the staging file has been set up with more constraints than the file that we are copying from) and then not continue through the other records but end out. It would be nice if it would write out the bad record to some type of log file (it does write out the record number in the QEZJOBLOG) and then continue on with copying over the other good records . The type of error the CL throws is an unrecoverable read/write error and therefore won’t continue on.

Have you been through any kind of data moves that you may have used a third party tool or a way on the iSeries to accomplish this feat? Any suggestions or advice is appreciated.


Possible Related Links
  • iSeries Users Group Forums are now open
  • ISCSI TECHNOLOGIES FOR SYSTEM I
  • PHP on the System i - Lunch and Learn

    • Input-Output Strategies May 15, 2006

    I came across an interesting and thought provoking post by Max Harris on his blog From Here to Modernity, where  questions about I-O Strategies (when dealing with a critical table) evoked responses from more than 90 iSeries professionals.

    Four options were presented as follows:

    1. Replacing F-specs in RPG program with SQL in all the programs or modules that use the table ( Embedded SQL ).
    2. Move I-O to a SRVPGM and have a procedure return the record format.
    3. Move I-O to a SRVPGM and have several procedures which return various data structures.
    4. Move I-O to a SRVPGM and have field getters and field setters (JAVA).

    We all know that first option is not very efficient, but read the post and related comments and arrive at your own conclusion.


    Possible Related Links
  • For Sale
  • Jobs
  • Advertise

    • Open Source iSeries Toolkit April 11, 2006

    While searching for an open source software for my other project, I came across Open Source iSeries Toolkit. For those of you new to Open Source Software, it is a program in which the source code is available to the general public for use and/or modification from its original design free of charge. Open source code is typically created as a collaborative effort in which programmers improve upon the code and share the changes within the community. Open source sprouted in the technological community as a response to proprietary software owned by corporations. Learn more about it at opensource.org.

    iSeries Toolkit is a bag of utilities that support Dynamic SQL, Messaging, IFS file access, User spaces, User indexes, Dynamic Native File Access, XML, and Source Generation. These RPGIV ILE and Java tools work on most recent releases of the iSeries and AS/400.

    Get Involved

    Apart from being a user of this toolkit, you can get involved as a developer. You can actually help developing the iSeries Toolkit by either fixing the bugs reported or adding / enhancing the utilities by checking the features requested. This open source project has a very active community that is constantly working on improving the toolkit. You can download the toolkit and try it yourself.

    Here are some of the features requested:

    Are you ready to accept these challenging tasks?

    If you have any comments or opinions, please click on "Comment" link below this article.


    Possible Related Links
  • How to register for iSeries forums
  • Convert any iSeries database file to CSV format
  • iSeries Users Group Forums are now open

    • SQL Brain teaser March 29, 2006

    I could have inserted a Sudoku puzzle here, but since we are an iSeries Users Group, here is a brain teaser for the SQL chumps out there. If you know the answer, feel free to post the code with explanation in the comments area below this post or e-mail it to us. If there are no responses, answer will be posted next week. So here it is:

     I have two tables and I want to update Column_X in Table 1 with the value in Column_Y in Table 2. Table1 and Table2 are matched on Key1. You have to make sure no null values are written to the Column_X.
     
     Will this piece of SQL code work?
     
     UPDATE Table1 T1
            SET Column_X = (SELECT Column_Y FROM Table2 T2 where T1.Key1 = T2.Key1)
     
     What happens when there is no match? How can you prevent it from writing nulls in Column_X?

    Hint : You may find your solution in DB2 Cookbook, which is a free download from Graeme Birchall’s website.


    Possible Related Links
  • No related posts