how to use sql script

How to Use SQL Script to Compare and/or Create OCMs within JD Edwards

As a CNC, there are various reasons you will be asked to create new JD Edwards environments.

Below are a few of the methods a CNC can use to create a new JDE environment:

  • Use the Environment Director (P989400)
  • Use the Installation Planner (P9840) and Installation Workbench (P9841)
  • Manually create a new Environment
    • Creates OCM (Object Configuration Manager) if it does not already exist
    • Writes F98403 record for the new environment
    • Load Business Data
    • Load Control Tables
    • Configure Enterprise servers
    • Create Server Map OCM
    • Create Server Map F98611 records
    • Writes F9651 records for Enterprise servers
    • Configure BSFN and UBE mappings

Ok, so now that we know there are a lot of steps that need to be completed when creating a new JDE environment. To simplify the process, I have discovered a SQL query that can help find missing OCMs (Object Configuration Manager) and create new OCM records. These queries can enable you to compare environments and create new or missing OCM records. The queries will identify and create standard OCM records that are missing after running the Environment Director (P989400) or Installation Workbench (P9841).

To Check for missing OCM records by comparing between environments, run the following query:

SELECT OMAPPLID, OMOBNM, OMDATP, OMDATS, OMUGRP, OMOAPP, OMDATM, OMOVRE, OMSY, OMSTSO, OMPID, OMFUNO, OMUSER, OMOCM1, OMJOBN, OMOCM2, OMUPMJ, OMOCM3, OMUPMT, OMOCMA, OMOCMB, OMOCMC, OMOCMDSC FROM SVM920.F986101 WHERE OMENHV=’old_env‘ AND OMSTSO=’AV’ AND OMOBNM NOT IN (SELECT OMOBNM FROM SVM920.F986101 WHERE OMENHV=’new_env‘);

    • The orange text can be changed to either Server Map or System Map
    • The green text can be changed to any environment that you would like to compare

A similar SQL query can be used for checking missing OCMs in other data sources and in other environments. After running the compare query above, you can use the query below to add missing OCM records.

To Insert OCM records from an environment where they exist into an environment where they are missing, run the following query:

INSERT INTO SVM920.F986101 (SELECT ”new_env” AS OMENHV, OMAPPLID, OMOBNM, OMDATP, OMDATS, OMUGRP, OMOAPP, OMDATM, OMOVRE, OMSY, OMSTSO, OMPID, OMFUNO, OMUSER, OMOCM1, OMJOBN, OMOCM2, OMUPMJ, OMOCM3, OMUPMT, OMOCMA, OMOCMB, OMOCMC, OMOCMDSC FROM SVM920.F986101 WHERE OMENHV=’old_env ‘ AND OMSTSO=’AV’ AND OMOBNM NOT IN (SELECT OMOBNM FROM SVM920.F986101 WHERE OMENHV=”new_env”));

    • In the above example, the SQL query will insert the OCM records in the F986101 table on the Server. Map the data source for the new environment ( new_env), by selecting the OCM records which exist in the old environment ( old_env), but are missing for the new environment (new_env).

We all know that there are many ways to complete the same tasks within and outside of JDE E1, I have now provided yet another option that can be used when comparing or creating OCM records for new environments. And always be careful when running SQL statements over databases. Making a backup is always a good practice.


Learn more about Syntax solutions for JD Edwards, or to start a 1-1 conversation with one of our solutions architects visit our Contact Us page and we’ll set up a call.