JDE Refreshes with Database Tools

      JDE Refreshes with Database Tools

      Bill Rehm, ATS – Database Lead

      JD Edwards has built-in tools that allow application administrators to manipulate database data and objects. The tools admins use the most are OMW and R98403. With OMW you can copy individual tables between data sources as well as create tables and indexes. R98403 gives administrators a wide range of options for copying data and tables between environments. There are other tools as well, but those are the most popular.

      Sometimes companies need to copy (or ‘refresh’) data from one environment to another, and by using a version of the R98403 UBE an administrator can complete a refresh without needing to know anything about the underlying database or about databases in general. They can just put in the source and target plus a few other options and let it run. The UBE takes care of transporting the data, renaming schemas, updating user info, managing security, and much more.

      There’s another level of refresh called a ‘path code refresh’ which, in addition to copying the business data, also refreshes the code base to the new environment. This is a little more complex than a simple data refresh, but the R98403 handles everything including updating table columns in the F983051.

      An incredible feature of the JDE tools is that they can copy data between different database platforms! Even better, this is included for free in the JDE toolset. There is third party software that can do what the R98403 does, but licensing costs are tens of thousands of dollars minimum.

      With such an amazing tool, why would anyone want to perform a refresh directly on the database itself? Because R98403 is SLOW. Very, very slow. For a small amount of data, say a couple hundred gigabytes, using R98403 is probably acceptable. When you start creeping up on a terabyte of data (and beyond) you’re looking at days and days to copy data with R98403.

      Your DBA can perform a full refresh in the fraction of the time it takes R98403 to complete. When exporting the data, the DBA can specify that the export capture the database at a distinct moment in time. This keeps the refreshed data internally consistent and eliminates issues with next numbers and other in-flight transactions. R98403 cannot do this.

      The drawback to using database tools is that all the work R98403 does in the background has to be done manually by the DBA. A mistake with any one of the additional steps can make the target environment completely unusable and might even require a repeat of the entire process to make it right.

      How do you do a refresh using database tools? I can’t really go in to the exact commands to do it since there are so many different variables. I will, however, lay out the blueprint for a data and path code refresh from JDE production to the development environment and you can use that to set up your procedure.

      Key Steps of a Data-Only Refresh

      • Make sure you have enough space!
        • Space for the export
        • Space on the target database for the new data
        • Space for increased transaction logs/archive logs
      • Export the data from the source
        • Oracle – Use the Data Pump tool, PRODDTA and PRODCTL schemas
        • SQL Server – Copy-only backup, JDE_PRODUCTION database
      • Make data available to the target location by exporting the backup file:
        • Directly to storage on target server
        • To a shared storage location
        • To the local server, then copy over the network to the target server
      • Back up target (optional)
        • If there are tables to preserve or important work in progress, you’ll need to back them up.
        • Menus, UDCs, and other setup tables are commonly preserved.
      • Export user and security information (optional)
        • If you have very different database security between environments, this is important.
      • Import the data to the target
        • Oracle – Also Data Pump
        • SQL Server – Restore database
      • Rename schemas and tablespaces
        • Oracle – Schema and tablespace renaming happens during the import process
        • SQL Server – Schema rename is done after the restore
      • Update user and security information
        • Restore your exported configuration if applicable
        • SQL Server – run the Orphaned Users script

      Additional Steps for a Path Code Refresh

      • IMPORTANT – In this section you are updating key system tables. DO NOT SKIP THE TABLE BACKUPS.
      • Export central objects in addition to the data, e.g. PD920
      • After import, update F983051 in the target database and set the VRENHV column to the target environment name e.g. DV920
      • Update ESU information in the system database
        • Back up F9671 and F9672 in the system database
          • Oracle - SY920 schema
          • SQL Server – JDE920 database, SY920 schema
        • Create work tables of F9671/2 that contain only records from the source environment using the environment columns as reference
          • where F9671_wrk.sfsudfut2 = ‘PD920’
          • where F9872_wrk.supathcd = ‘PD920’
        • Update the environment columns in the work tables with
          • set F9871_wrk.sfsudfut2 = ‘DV920’
          • set F9872_wrk.supathcd = ‘DV920’
        • Delete all records from the original tables where the path code = ‘DV920’
        • Insert all records from the work tables into the original tables
      • Update ESU information in the planner database
        • It’s the same procedure as above, except it’s performed on the deployment server planner database. The tables reside in the JDESY920 schema.
      • Update Object Librarian (OL) information. It’s a similar procedure to the ESU update, just with different tables.
        • Back up OL920.F9861, F9862, and F98603
        • Create work tables that contain records only from the source environment
          • sipathcd, f98602.sdpathcd, and f98603.smpathcd are the environment columns
        • Update those work tables to set the environment columns to the target environment DV920
        • Delete all records from the original tables where the path code = ‘DV920’
        • Insert all records from the work tables into the live tables

      Rollback

      • Production data exports do not affect the integrity of the data.
      • If your problem is with the target data import, just re-run the refresh.
      • Mistakes with the system tables require restoring the individual table backups you took before updating them.

      Important

      A manual refresh should only be conducted by an experienced DBA. Even with the most rock-solid procedure, mistakes can – and will – be made. At GSI we conduct data refreshes for many different clients on a regular basis. If you need someone to manage your refreshes or even your entire database infrastructure, just give the GSI' s JD Edwards Database Management Services Practice Team a call!

      To find about GSI’s products or JD Edwards services, call us at 855-GSI-4ERP or click on CONTACT US to send us a request for more information.

      Meet the Author

      Bill Rehm
      , , , , , , , , ,