36 Set Up Universal File Converter

This chapter contains these topics:

• Section 36.1, "About Universal File Converter (UFC)"

• Section 36.2, "Understanding the Universal File Converter Setup"

• Section 36.3, "Setting Up Universal File Converter"

36.1 About Universal File Converter (UFC)

The Universal File Converter is used to import data from one AS/400 physical file to another AS/400 physical file. If you have more than one file to convert, you can set up a separate version for each type of conversion required. The Universal File Conversion Setup program loads information to the Crossover Rules file (F0031) about the fields in the files you are converting.

A major benefit of using UFC is that once the process has been tested, it can be made into a production process using a Dream Writer version.

The system uses the information in the Crossover Rules file to transfer the data from a field in one file to a field in another file, or to a field in multiple files.

This program also has processing options that let you convert data from both JD Edwards World and non-JD Edwards World files.

36.1.1 Before You Begin

• Before you run the setup procedure make sure the To files exist.

Caution:

Do not attempt to use the Universal File Converter on a file that contains double byte data. The converter program may corrupt the integrity of the bracketing "shift in" and "shift out" characters that are automatically inserted by double byte terminals.

36.2 Understanding the Universal File Converter Setup

The setup program is the first part of a three-part conversion process. Specify a From file and a To file through the DREAM Writer processing options. You can specify up to four To files. If you require multiple From files, specify a join logical as the From file. This join logical is over all the files you select for the From file. Use the name of the join logical in the first processing option.

The program retrieves field information for all fields in the From file and loads this information to the Crossover Rules file, F0031.

The program then retrieves field information for the To files. If the Reference (data dictionary) field in the To file matches the From file Reference field, the program makes an association between the two fields. The system writes information for the To file to the record in the Crossover Rules file associated with the From file field.

**FILLER conversions are automatically generated for From file fields with no corresponding To file fields and for To file fields with no corresponding From file fields. **FILLER fields will be ignored during the conversion process. You can override a **FILLER entry with the appropriate field name, position, and characteristics if the field exists in the file but has a different field name.

If there are any other associations you need, do them manually using the Crossover Rules selection on the menu.

36.2.1 Considerations

UFC is written to map data character-by-character from any physical file resident on the iSeries (AS/400) to any other resident physical file. If the input file resides on a PC, it must be transferred to the iSeries via a data transfer utility.

If you are using UFC to populate Z1 files, Z1 batch processing is recommended for uploading data to the appropriate production files. Areas where these processes are available include:

Area	Menu
Address Book	G01313
General Accounting	G09311
Accounts Payable	G04311
Accounts Receivable	G03311
Time Card Entry	G07121
Budgeting	G1421
EDI	G47 menus
Batch Sales Order Entry	G4212

To protect your production data, create a test library and put a copy of the "from file" and "to file" with attached logical files in it. There may be a logical file attached to the to file with a unique key requirement. By having this file attached during the UFC process, all the key field requirements will be met. Now gather the following two pieces of information. Type command DSPFP on the from file in the test library. Page down to the last panel. Write down the Format name and the number of records in the file. The number of records should be 100 or less for the initial testing. During testing, the number of records can be used to make sure all records were read. When the conversion has been tested successfully a full-file test can be made. The record format name will be used in the last step.

36.3 Setting Up Universal File Converter

Figure 36-1 Universal File Converter screen

Description of "Figure 36-1 Universal File Converter screen"

To create a new version

1. From the Universal File Converter menu (G9841), choose Versions Setup.

   Figure 36-2 Versions Setup screen

   
   Description of "Figure 36-2 Versions Setup screen"
   
   

   The Versions Setup form displays. The examples shown are for illustrative purposes only.

   This program loads information into the Crossover Rules File (F0031) about the fields in the files you are converting. The system uses the information in the Crossover Rules File to transfer the data from a field in one file to a field in another file or to a field in multiple files. Neither file has to be a JD Edwards world file. However, the from file cannot be a multi-member type file. To verify the number of members in a file do the DSPFD command on the from file. Look for the Number of members field. The value should be a one.

2. Make a copy of the XJDE0001 version.

   Use your version name and title to tie it to the data you are converting, for example ABCONV and "Address book conversion."

   Caution:

   Do NOT make any changes on the Additional Parameters screen. The based on file should be *NONE and the Format name should be INONE.

3. Display the processing options.

4. Specify a From file and a To file. You can specify up to four To files.

   If you require multiple From files, specify a join logical as the based on file for your version. The join logical will encompass all the files you wish to use for the From file.

   Note:

   If either file is set to JD Edwards World file = Y, numeric field names will be looked up for decimal point information. If either file is not created with Data Dictionary data item name, make sure to set the JD Edwards World file = N. Page down to enter the test library name.

5. Return to the Versions List.

6. Execute the version.

   After entering the file names and the libraries in the processing options, you do need to submit the DREAM Writer version. P00120 will find the "from" and "to" files and create a record in F0031 for each field in these files. If there is no print file, the F0031 will have version records added for each field in the setup in field name sequence.

   Figure 36-3 Processing Options Revisions screen

   
   Description of "Figure 36-3 Processing Options Revisions screen"
   
   

Option	Description
Enter the name of the file	The name of the From file to convert the data from.
JD Edwards World File?	Y if the From file is a JD Edwards World file, or N if it is not. If Y, the first two characters of the field name will be stripped and the remaining field name will be looked up in the JD Edwards Data Dictionary.
Enter the name of the file OR files to convert the data to.	The names of the To files in the spaces provided.
JD Edwards World File?	Y if the To file is a JD Edwards World file, or N if it is not.

Figure 36-4 Processing Options Revisions screen

Description of "Figure 36-4 Processing Options Revisions screen"

Option	Description
Enter the library containing the From file.	The name of the From file library, or leave blank to search your library list.
Enter the library containing the To file.	The name of the To file library, or leave blank to search your library list.

To set up crossover rules

This is where the mapping details are entered. There are many conversion rules in UFC to cover most situations, try them first before using custom Xxxxx programs. You need only map the fields that you are concerned with.

1. From menu G9841, take Selection 3 to go to the Crossover Rules.

2. Inquire on the version name you created and ran in Step 2.

   P00120 is pre-loaded in the Form ID field.

   Note:

   The program in Step 2 above attempts to match field names. Any field with no match will have **FILLER for a Field Name. If **FILLER is on either the "From" or "To" side, the data will not be mapped. If the "From" side has fewer **FILLER fields, press F8 to clear them. If the "To" side has fewer, press F9.

   The remaining fields will be in alphabetical order, not by position within the record. UFC is usually used to bring data into a JD Edwards World World file, so that assumption will be used in this exercise.

3. Pick a field on the "To" side that you want to map into.

   Start with the field name on the "From" side and key in a meaningful field name. Use F14 to enter additional documentation.

4. Set the "From" field "type" to A for Alpha.

   This is the most inclusive data type and will be used for numeric fields as well.

   You will need to know where the data to be mapped starts in the input record. UFC moves data character-by-character, so if the input data has leading spaces, increment the "Begin Pos" field to skip over them. Similarly, on the "To" side, manipulate the "Begin Pos" as necessary so the data will be mapped correctly. The field names are not important to the process. The type, position and length values are important.

5. On the "To" side, the type can be "A", "S", or "P" for Alpha, Signed or Packed. "A" fields will have the "Byte" field set to the length of the data and the "Dig" field should be 0."S" fields should have the "Bytes" and "Dig" fields set to the same value. "P" fields should have the "Dig" set to (("Bytes" x 2) - 1). It takes 8 bytes to hold 15 packed digits. In most cases, the "Dec" field will be 00.

6. Press F4 to open the fold area. Press F1 on the "Conv Rule" field. The most used functions are the date formats, *DFT and *NN. Most file dates are stored in Julian format in JD Edwards World World. The incoming data should be 6 digits long and will probably be in a *MDY or *DMY format. On the "To" side, the field will also be 6 digits long but will have the *JUL format. The *DFT rule has room for 6 contiguous digits of data. *NN uses the first four digits for system code and the next 2 for the bucket number.

7. When all the incoming fields with data have been mapped, type C in the action code and press enter. All fields in the "To" file will be initialized to the proper empty format if not mapped.

   See Also:

   • Chapter 37, "Work with Crossover Rules"

To create a new version of P00111

1. On menu G9843, take Selection 4 to go to the Versions List for P00111.

2. Make a copy of the XJDE0001 version and give it the same name and title as in Step 2A for continuity purposes.

3. On the Additional Parameters screen:

   • Enter the input file name in the Based on file field.

   • Press Enter.

   • Press F12 to return to the Add'l Parameters screen.

   • Ensure the format name is the one from the DSPFD command in the preparation step.

   • Press Enter.

4. Display the Processing Options.

5. Set the Processing Options as follows:

   • Processing Option 1 needs the version of P00120 from Step 2 so it can find the right crossover rule records in the F0031.

   • Enter the appropriate file and library names for options 2, 3, and 4.

   • Option 5 is usually set to 1 to clear the file so just the records from the latest conversion will be there.

   • Option 6 is usually blank.

   Caution:

   Do not change Data Selection and Data Sequencing. They are used for the OPNQRYF statement. Since we want to use the full file, they don't apply.

6. Return to the Versions List.

7. Submit your version.

To review your results

1. Go to Work with Spooled Files and look for R00111.

   At the bottom of this spooled file will be how many records were read and written.

2. Type command DSPPFM on the "to" file to see if the mapping is correct.

3. In order to see all the data, press F10, then F11 to see the hexadecimal representation of the data in an over/under presentation.

36.3.1 Trouble Shooting

• Sometimes P00111 will have problems with the input file.

  • On the Additional Parameters screen, if the file output type is 1, change it to 2 and vice versa.

• Make sure the P00111 based on file and format names are correct.

  • See "To create a new version of P00111".

• There may be just one record in the "to file" and a message in the Joblog about a duplicate record.

  • Type command DSPFD on the file and its logicals to see which one has the "unique" key requirement. You will need to map data to each of the key fields. If there is no matching data, maybe a *NN conversion rule will have to be attached to one of the key fields as a tie breaker.

36.3.2 Key Words

Valid conversion rules keywords include:

Function	Key Words
Date Conversion	*MDY, *DMY, *YMD, *JUL, *SYSVAL
Initialization	*BLANKS, *ZEROES
Business Unit	*RAB right adjust blank fill
Default from Dictionary:	*DEF User Defined Code Lookup, *UDCssssrr where ssss is the System Code and rr is the Code Type
Default Constant	*DFTcccccc where cccccc is the constant
Next Number	*NNssssxx where ssss is the System Code and xx is the Number