6 Working with JD Edwards EnterpriseOne Table Conversion Workbench

The Table Conversion Workbench runs the table conversions that migrate Oracle's JD Edwards World business data to the new format for Oracle's JD Edwards EnterpriseOne 9.2. It updates the Table Conversion Scheduler table (F98405) to indicate completion, and writes a conversion log record to the Table Conversion - History Log table (F984052) for each conversion.

You must run the table conversions, which appear in the JD Edwards EnterpriseOne 9.2 Table Conversion Workbench. If you do not own or use a particular suite (system), ignore all errors received related to those files.

Note:

If you have chosen to run TCs on the Enterprise Server, these TCs will still run on the Deployment Server:

• Any TC below sequence 100

• Any TC with the Run On Deployment Server flag turned on

The Data Massage column is a flag that tells the TC workbench NOT to check the table to see if it is already in the target format. For other TCs, the workbench will not run the Table Conversion if the table is already converted.

6.1 Table Conversion Workbench Architecture

The Table Conversion (TC) Workbench (P9840TC) can be run using Convert All (this should always be used when migrating from JD Edwards World to JD Edwards EnterpriseOne) or Convert Selected.

Convert All runs with two loops. The outer loop keeps running until there are no more TCs to run, and there are no more TCs in a running status. The inner loop runs through the F98405 table looking for all TCs at a status less than 60. As it reads each new record, it then checks to see if there are any TCs waiting at status 35 for dependencies. It first tries to run those (checks again if their dependencies are satisfied), before trying to run the new TC that it just read.

The Technical TCs (those that are at conversion sequence < 100) run one at a time. For TCs above sequence 100, Convert All uses the maximum batch jobs specified in the Plan (4 by default), meaning it submits up to that number of TCs at once. Convert All submits 4, then loops waiting for a job to end and submits the next job. If you set the queue to a number larger than 4, only 4 are submitted at a time.

For each TC submitted, the program first checks whether the TC has dependencies and checks the status of those dependencies. If any of the dependencies are still running (status 45), the TC is changed to status 35 (waiting for dependencies).

Convert Selected can be used to submit several TCs at once and keeps looping until they are submitted. It does not keep looping until they have run. Press Find to see if they are done.

P9840TC monitors and updates F98405 (kept locally in JDEPLAN). The TCs update F984052 (kept in System – 910). At any point in time, there are records in F98405 at status 45 for TCs that actually have completed. The TC Workbench calls a business function at various points in its loop, which compares F984052 and F98405, and updates the status in F98405 to completed (60) or failed (50). If the TC Workbench fails (for example, you cancel out of it), go to P9840TC and drill down into the TCs, and press FIND. This synchronizes F98405 with F984052. The FIND button on the TC Workbench also synchronizes F98405 and F984052.

If the Enterprise Server fails while running the TC Workbench, you might have up to 4 jobs at status 45 (running), even though they might have failed. Follow the instructions in the paragraph above to get everything back into sync. If a TC actually fails altogether on the server (for example, you cancel the job) it will never get as far as updating F984052, and the process is not able to update F98405 correctly. When you press Convert Selected or Convert All, the process does not re-submit anything that is at status 45. When restarting the TC Workbench, you need to manually check whether any of the jobs at status 45 are still running, and set the status back to 30 or 50 if you want the TC Workbench to re-submit them.

All the Table Conversions have the same job name - R98405A. When looking in the JDE.LOG for that job, there is a message stating which TC is running.

6.2 Running the Table Conversion Workbench

Personnel

CNC Administrator

Logon Status

On the Deployment Server, logged on as user JDE in the JDEPLAN environment.

Prerequisites

If running TCs on the Enterprise Server, temporary tables for migration must be created and services must be started on the Enterprise Server. The necessary changes must be made to the JDE.INI file.

Remove all run time cache, .ddb, and .xdb files from the enterprise server location before running TC workbench.

Concurrent Tasks

None

6.2.1 To run the Table Conversion Workbench

1. Fast Path to P9840TC and click ADD.

   
   

2. On the Maintain Table Conversion Plans form, complete these field and click OK:

   
   

   Field	Value
   Plan Name	Enter a name for the migration plan.
   Plan Description	Enter a description for the plan.
   Plan Detail Status	This should be set at a status 30.
   From Release Level	A733, A921, A931, or A940
   To Release Level	E920
   Previous Environment	Enter a name to identify the environment you are migrating. This is a temporary environment that is created over the JD Edwards World business and common data libraries. For example, DVTEMP, PYTEMP, PDTEMP, and so on.
   Target Environment	Enter the name of the environment to which you are migrating data.
   Run TC on Deployment Server	This option should not be selected. The workbench should submit the TCs to the Enterprise Server. Although it is not required to run table conversions on the Enterprise Server, performance might be enhanced by doing so. If you have a small database you might decide to the TCs on the Deployment Server. If so, select option Run TC on Deployment Server.
   Enterprise Data source	Enter the Enterprise Data Source name. This is the name of the Enterprise Server.
   TC Max Concurrent	Enter the TC Max Concurrent. This value should match the maximum jobs set up for the QBATCH queue on the Enterprise Server. You control how many TCs are actually running at any time by the queue setup, using WRKJOBQ. Make sure the IBM i JOBQ MAX ACT is set to match the TC Throttle that you select. Verify by using these commands: DSPSBSD QBATCH Option 6
   Tracing	This option should not be selected.

   
   

3. On the Setup DataSources/Mappings for World/CRM Migration form, complete these fields:

   
   

   In the Business Data Library field, enter the name of the JD Edwards World Business Data library you plan to migrate:

   Business Data – <xxxxxx>

   Where <xxxxxx> is the name of the temporary environment.

   In the Control Tables Library field, enter the name of the JD Edwards World Common Data library you plan to migrate:

   Control Tables – <xxxxxx>

   Where <xxxxxx> is the name of the temporary environment.

4. Click OK to access the Data Source Revisions form for Business Data.

   
   

5. Click OK to access the Data Source Revisions form for Control Tables.

   
   

6. Click OK on the message dialog.

   
   

7. On the Work with Table Conversion Plans form, find and select the plan name.

8. From the Row exit, select TC Planner to review all the JD Edwards World to JD Edwards EnterpriseOne TCs in the plan.

   
   

9. Verify that all the Table Conversions are at status 30.

10. Click Cancel. This returns you to the Work with Table Conversion Plans form.

11. This step applies ONLY to customers performing Direct Migration from World A7316 to EnterpriseOne 9.2.

    1. Run P9840TC and click Find.

    2. Highlight the created plan and click the TC Planner Row Exit.

    3. Find and select program R893111G. Change the status from ”30” to ”60” from ”DTL STS” QBE.

       
       

    4. Click Form Exit. Save as shown in the following graphic.

       
       

    5. After you save it, the status changes to ”60”.

       
       

    6. Close the TC planner application.

12. Select the plan name, click Row, and select Table Conversion Workbench:

    
    

13. From the Form menu, select Convert All.

Troubleshooting:

While the conversions run, you can track their progress by selecting Table Conversion\Merge Log (P984052). On the System Installation Tools menu (GH961), double-click Advanced Operation, and then Table Conversion/Merge Log. The program shows a list of conversions that have processed.

6.2.2 Monitoring Table Conversions

Table Conversion Workbench implements a throttling mechanism to control conversions. If conversions are run on the Deployment Server, it launches one conversion at a time. If conversions are run on the Enterprise Server, it looks up the maximum number of batch jobs in the queue table and submits that many conversions at one time. Either way, technical conversions are always throttled to 1.

6.2.2.1 To view the status of conversions:

1. Fast Path to P9840TC.

2. Click Find.

3. Highlight the Plan Name.

4. From the Row Exit Menu, select TC Planner.

5. Click Find.

   From the Row exit select View TC Logs verify that the conversions are successful.

   
   

   Platform-Specific (Task Manager, wrkactjob, ps) utilities can also be used to monitor the conversions.

   Database-Specific Queries that query unconverted tables, temp tables, converted tables, and database system tables can be helpful in monitoring conversions.

6.2.3 Log Files

The log files are stored in specific locations:

• On the IBM i, the naming convention is JDE_<IBM i Job Number>.LOG and JDEDEBUG_<IBM i Job Number>.LOG. The logs are stored in /JDE920.

• On all platforms, the conversion logs are stored in the print queue directory. The naming convention is <Report>_<Version>_D<Date>_T<Time>_J<JobNumber>.log

The print queue folder is /E920SYS/PrintQueue The JDE.INI settings for logging are shown in this example:

[DEBUG]

;Turn off JDEDEBUG.LOG, set to FILE to turn on JDEDEBUG.LOG Output=NONE

;Turn on JDE.LOG, set to 0 to turn off JDE.LOG LogErrors=1

;Do not send logging info to client, set to 1 to send logging info to client (not recommended)

ClientLog=0

[JDENET]

;Turn off JDENET tracing. Set to 1 to turn on JDENET tracing. netTrace=0

[TCENGINE]

;Turn on minimal table conversion tracing. Set to 0 to turn off tracing (not recommended). Set to 10 for maximum tracing (useful for troubleshooting).

TraceLevel=1

Note:

If you are having problems running TCs on the server, the IBM joblog can be helpful. Set up the logging with these settings in the JDE.INI file:

PrintUBEJoblog=TRUE

PrintUBEJoblogOnError=TRUE

When you are finished, change the PrintUBEJoblog to FALSE.

Note:

There is a possibility that there might be invalid errors in the JDE.LOG. Ignore any errors similar to ”No rounding record found in table < tablename> – <datasource>.”

6.2.4 Stopping Table Conversions

6.2.4.1 To stop table conversions

1. Close the Workbench.

   On the Deployment Server, open Task Manager (press CTRL+ALT+DELETE and select Task Manager). Go to the Applications tab. Select Installation Workbench. Click End Task.

2. Delete waiting jobs from Submitted Reports (GH9011).

   Enter W in the Status QBE and click Find. If any are shown, highlight all rows and delete.

All conversions should now be stopped. There are several more steps involved with cleaning up the database and resetting Table Conversion Workbench. See Restarting Workbench below.

You might decide to leave one or more table conversions running but close Installation Workbench and restart the remaining conversions. See Restarting Workbench below.

Note:

Installation Workbench should not be closed if Table Conversions are running normally. Only close it if problems have occurred and conversions need to be restarted.

6.2.5 Restarting Workbench

6.2.5.1 To restart workbench:

1. Delete SQL packages. Do this before signing onto the software on the Deployment Server. Services must be down before deleting SQL packages.

   1. STRPDM

   2. Option 2

      Library: <data library>, for example CRPDTA

      Name: *ALL

      Type: *SQLPKG

   3. Option 4

   4. Click Enter.

2. To rerun a conversion, reset the status of the conversion in Table Conversion Planner from 35, 45, or 50 to 30. If a conversion is still running when workbench is restarted, leave its status at 45.

Problem Submitting

If Table Conversion Workbench fails to submit a conversion to the server (for example, services are not running), it sets the conversion status to 50 and does not submit any more conversions.

F9843, F9843T

Table Conversion Schedule (GH9611) - lists all conversions that are run. They are conversion types 3, 31, and 32. Table Conversions that must run on the Deployment Server are flagged here.

This resides in System Local and System – 910

F98405

Workbench Status (GH961) - Table Conversion scheduler records, among others, are copied from F9843 to F98405 during Finalize Plan. The Workbench is driven off records in this table.

This resides in Planner – 910 data source.

F984052

History (GH9611) - Stores conversion history for table conversions. A new record is created in this table every time a conversion is launched. R98405A writes the status for a completed conversion to this table. Table Conversion Workbench polls this table for completion status and updates the status in F98405.

Resides in the System – 910 data source. The table is originally mapped to Planner – 910. It is copied to System – 910 and remapped during Release Master. The table must be in System – 910 so conversions on the Enterprise Server can communicate their completion status back to Table Conversion Workbench.

F986110

Submitted Reports (GH9011) - the software creates records in this table for conversions launched on the server. They can be viewed using Submitted Reports.

Resides in Server Map <xxxx> – 920 Server Map, where <xxxx> is the machine name.

F986130

Job Queues (GH9013) - Job Queues may be defined by the installer in the JDEPLAN environment. If any queues have been defined, they are copied to the System – 910 data source during Release Master. If no queues have been defined, Release Master creates default queues in the System – 910 data source. The Enterprise Server is mapped to the table in the System – 910 data source. Log into the DEP920 environment and open the Job Queues application to view queues.

Resides in System Local and System – 910.

6.3 Rerunning Table Conversion Programs

Personnel

CNC Administrator

Logon Status

On the Deployment Server, logged on as user JDE with the database password for user JDE, in the JDEPLAN environment

Prerequisites

Remove all run time cache, .ddb, and .xdb files from the enterprise server location before running TC workbench.

Concurrent Tasks

None

Technical conversions can be rerun without restoration. For process type 5 only, when you run a table conversion program from Table Conversion Workbench, the following occurs:

• All table conversions should be rerun through the Table Conversion Workbench. If the status of the conversion is 60, you might need to change it back to 30.

• The table conversion engine uses the object map to locate the table being converted in both the From and To environments.

• In the migration process from JD Edwards World to JD Edwards EnterpriseOne, application tables being converted will reside on a different data source. This creates a cross data source conversion.

• The physical table on the data source should be formatted following the specifications for the previous release.

• The table conversion engine creates a temporary table on this data source. Drop all temporary tables before starting the Table Conversion rerun procedure. The temporary file name has the notation <Table>TCTemp, for example, F03B20TCTEMP.

6.3.1 To find the temporary tables:

1. Enter the following commands:

   STRSQL

   SELECT SYSTEM_TABLE_SCHEMA, TABLE_NAME FROM SYSTABLES WHERE TABLE_NAME LIKE 'F%TCTEMP'

   DROP TABLE PRODDTA/F0101TCTEMP

   Do not drop temp tables for conversions still running.

   Drop the F00165T in the JD Edwards World Business Data library.

2. Enter this command to clear out tables:

   CLR PFM PRODDTA/<table_name>

   Clear out these tables: F40941, F40942, F40943, F0618TA, F42140A, F42150A, F42160A, F3201, F3211, F3215, F3216, F3290.

   See the Programmers Guide for any additional instructions when rerunning Table Conversions.

3. When the table conversion engine runs an in-place table conversion, the temporary table required for the conversion could be created and never dropped. In this case, rerunning the conversion program fails when creating the temporary table because the table already exists. To resolve this, drop or rename the temporary table before rerunning the table conversion. The naming convention for temporary tables is <table_name>tc<object_name>, for example, F03B20tcR8903B20.

4. When the migration is complete and you are satisfied with the results, delete all SY = 89 tables from their data sources.