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
Tip: Converting String to Number in QUERY/400 April 18, 2006
Theoretically there is no command to convert a string to number in Query/400, but I found this tip on an iSeries forum. It is little clumsy but can be handy workaround when you do not have access to SQL.
Here is what you do:
The TIMESTAMP function accepts a character representation of a valid time stamp. Because part of the time stamp is a 6 digit microsecond, use the character string to be converted for the microsecond.
* The date and time used is constant and is NOT important for the conversion
* The format of the microsecond is mmmmmm:
6 digits are required and they must be numbers.
If your field is less that 6 digits, it is to be Zero-filled to the left, for example, ‘0000xx’, for a 2-digit field.
If your field is more than 6 digits, do multiple conversions and use numeric operations to calculate the correct value.
If your field requires a decimal portion, do the conversion, then use numeric operations to convert to the correct decimal value.
Once the time stamp is created, the MICROSECOND function can be used to retrieve the numeric representation of the character field.
Following is an example:
fldString is a 4 digit character field in the file. Define the following result fields:
tStamp TIMESTAMP(’1995-01-01-12.00.00.00′||fldString)
fldNumeric MICROSECOND(TIMESTAMP)
If fldString has a value of ‘0045′, then tStamp will look like ‘1995-01-01-12.00.00.000045′ and fldNumeric will result in 45.
If you have a better solution, please post it here by clicking on comments.
Possible Related Links
