3 JD Edwards World

This chapter describes how to work with JD Edwards World Knowledge Modules in Oracle Data Integrator.

This chapter includes the following sections:

3.1 Introduction

JD Edwards (JDE) World is an integrated applications suite of comprehensive ERP software that combines business value, standards-based technology, and deep industry experience into a business solution with a low total cost of ownership.

3.1.1 Concepts

The JDE Knowledge Modules for Oracle Data Integrator use mature database-level integration methods for JDE World, in order to:

  • Reverse-Engineer JDE World data structures

  • Read data from JDE World (Direct Database Integration)

  • Write data through the Z-tables to a JDE World Application (Interface Table Integration)

3.1.2 Knowledge Modules

Oracle Data Integrator provides the Knowledge Modules listed in Table 3-1 for handling JDE World data. These specific JDE World KMs provide connectivity and integration of the JDE World platform with any database application through Oracle Data Integrator.

Table 3-1 JDE Knowledge Modules

Knowledge Module Description

RKM JDE World

Reverse-engineers the metadata of the applications' objects such as tables and interface tables from JDE World installed on DB2 iSeries database, through DB2 iSeries JDBC driver (jt400).

RKM JDE World (JDE World JDBC Driver)

Reverse-engineers the metadata of the applications' objects such as tables and interface tables from JDE World installed on DB2 iSeries database, through JDE World JDBC Driver.

IKM JDE World Control Append

Integrates data from any source to JDE World. Integrates data in a Z-table in control append mode.

  • Data can be controlled: invalid data is isolated in the Error Table and can be recycled

  • The KM performs integration into JDE World with a RPG program


3.2 Installation and Configuration

Make sure you have read the information in this section before you start working with the JDE World data:

3.2.1 System Requirements and Certifications

Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.

The list of supported platforms and versions is available on Oracle Technical Network (OTN):

http://www.oracle.com/technology/products/oracle-data-integrator/index.html.

3.2.2 Technology Specific Requirements

This section lists the technlogy specific requirements of the JDE World Knowledge Modules.

RKM JDE World

  • jt400.jar - This jar file must be in the ~/.odi/oracledi/userlib folder.

RKM JDE World (JDE World JDBC Driver)

  • JDEWorldJDBC.jar - This JDE World JDBC driver file must be in the ~/.odi/oracledi/userlib folder.

IKM JDE World Control Append

The RPG program requires the following files:

Note:

These three files are delivered with ODI and are located in the ODI_HOME/odi/misc/jde-world directory.

Tip:

The ODI_HOME/odi/misc/jde-world directory also contains a jde.properties file. This properties file is a template that you can make use of if you are using the JDE_SECURITY_FILE option in the IKM JDE World Control Append. See Section 3.6.2, "Integrating Data in JDE" for more information about this option. When using this template make sure to:
  • Rename the properties file

  • Enter the connection information (JD Edwards World user, password, environment, and address) .

    See Table 3-2 for more information about the connection related options. Also, an example of a security file is provided below the table.

  • Move it to a directory that is accessible only by Oracle Data Integrator Studio or the standalone agent. Ensure that this directory is not accessible to any other user as the properties file contains the user name and password.

3.2.3 Connectivity Requirements

Oracle Data Integrator connects to the database hosting the JDE World data using JDBC connectivity.

The RKM JDE World (JDE World JDBC Driver) uses the JDE World JDBC driver to access the database to extract metadata infromation from JDE World. The JDE World JDBC driver provides the ability for non-JDE World applications to access JDE World data while maintaining the level of security and the flexibility built into the JDE World software.

Both the IKM JDE World Control Append and the RKM JDE World use the standard IBM Toolbox for Java driver (jt400.jar), which runs SQL queries to insert or access the database to extract metadata information for JDE World.

Note:

in the JDBC URL, use the *SQL naming convention. Do not specify the naming convention to be system as, for example in : jdbc:as400://195.10.10.13;translate binary=true;naming=system.

*SQL should always be used unless your application is specifically designed for *SYS. Oracle Data Integrator uses the *SQL naming convention by default.

For detailed information on JDBC connectivity with IBM DB2 for iSeries, see "IBM DB2 for iSeries Connectivity Requirements" in the Connectivity and Knowledge Modules Guide for Oracle Data Integrator.

3.3 Setting Up the Topology

This step consists in declaring in Oracle Data Integrator the data server, as well as the physical and logical schemas that will be used to store the JDE World data.

3.3.1 Create a Data Server

The JDE World tables are stored in an IBM DB2 for iSeries library.

When working with RKM JDE World:

Create a data server for the IBM DB2 for iSeries technology using the standard procedure, as described in"Creating a DB2/400 Data Server" of the Connectivity and Knowledge Modules Guide for Oracle Data Integrator:

Note:

When defining the connection parameters for the data server, set in the JDBC URL field translate binary=true

For example:

jdbc:as400://10.139.142.183;translate binary=true

This data server must point to the library that stores the JDE World data.

When working with RKM JDE World (JDE World JDBC Driver):

Create a data server for the IBM DB2 for iSeries technology using following information:

  • JDBC Driver: com.jdedwards.as400.access.JDEWJDBCDriver

  • JDBC URL: jdew://<host>;translatebinary=true;JDEWEnvironment=<name>;user=<name>;pwd=<passwd>;JDEWTableNomenclature=OBJN_OBJT;JDEWColumnNomenclature=FDFT_FDFN

    For options specified in the JDBC URL, please refer to the JDE World JDBC Driver User Guide.

3.3.2 Create a Physical Schema

Create a physical schema under the data server that you have created in Section 3.3.1, "Create a Data Server". Use the standard procedure, as described in "Creating a Physical Schema" in Administering Oracle Data Integrator.

This schema must point to the library that contains the JDE World tables that you want to reverse-engineer.

Note:

The library storing the JDE tables should never be defined as a work schema in the physical schema definition. Moreover, this library must not be used as staging area of a mapping.

Create for this physical schema a logical schema using the standard procedure, as described in "Creating a Logical Schema" in Administering Oracle Data Integrator and associate it in a given context.

3.4 Setting up an Integration Project

Setting up a project using JDE World features follows the standard procedure. See "Creating an Integration Project" of the Developing Integration Projects with Oracle Data Integrator.

Import the following KMs into your Oracle Data Integrator project:

  • IKM JDE World Control Append

  • RKM JDE World

  • RKM JDE World (JDE World JDBC Driver)

In addition to these specific JDE World KMs, import the standard LKMs for the technology hosting your JDE World tables. For a list of available KMs, see "IBM DB2 for iSeries Knowledge Modules" in in the Connectivity and Knowledge Modules Guide for Oracle Data Integrator:

3.5 Creating and Reverse-Engineering a Model

This section contains the following topics:

3.5.1 Create a Model

Create a Model based on the IBM DB2/400 technology hosting the JDE World tables and on the logical schema created when configuring the JDE World connection using the standard procedure, as described in "Creating a Model" of the Developing Integration Projects with Oracle Data Integrator.

3.5.2 Reverse-Engineer JDE Tables

The JDE RKMs are able to reverse-engineer JDE tables. These RKMs retrieve metadata from JDE objects such as tables and interface tables.

To perform a Customized Reverse-Engineering of JDE tables with the RKM JDE World or RKM JDE World (JDE World JDBC Driver), use the usual procedure, as described in "Reverse-engineering a Model" of the Developing Integration Projects with Oracle Data Integrator. This section details only the fields specific to JDE World tables:

  1. In the Reverse tab of the Model, select RKM JDE World or RKM JDE World (JDE World JDBC Driver).

  2. Set the options for the selected RKM:

    For RKM JDE World (JDE World JDBC Driver), set the options as follows:

    • JDE_MODULES: Indicate the JDE System Short Name, for example:

      00 for Foundation Environment

      01 for Address Book

      02 for Electronic Mail.

      % for all JDE Systems

      Default is 01.

      Note:

      You can also specify a list of modules. In the list, the modules must be separated by commas and enclosed within single-quote characters, for example:

      '01','02','04'

    For RKM JDE World, set the options as follows:

    • JDE_DATA_TABLES: Set this option to YES to reverse-engineer data tables

    • JDE_Z_TABLES: Set this option to YES to reverse-engineer interface tables (Z-tables)

    • JDE_MODULES: Indicate the JDE System Short Name, for example:

      00 for Foundation Environment

      01 for Address Book

      02 for Electronic Mail.

      % for all JDE Systems

      Default is 01.

      Note:

      You can also specify a list of modules. In the list, the modules must be separated by commas and enclosed within single-quote characters, for example:

      '01','02','04'

    • JDE_LANGUAGE: Indicate the language used for retrieving object descriptions and comments, for example E for English, F for French, and S for Spanish. Default is E.

  3. Specify the reverse mask in the Mask field in order to select the tables to reverse. The Mask field, in the Reverse tab, filters reverse-engineered objects based on their name. The Mask field must not be empty and must contain at least the percentage symbol (%).

The reverse-engineering process returns the datastores grouped per module. You can use these datastores as a source or a target of your mappings.

3.6 Designing a Mapping

You can use JDE World data tables as a source of a mapping. JDE Z-tables can be used as the target of a mapping.

The KM choice for a mapping determines the abilities and performance of this mapping. The recommendations in this section help in the selection of the KM for different situations concerning loading and integrating JDE World data.

3.6.1 Loading Data from JDE

After performing a reverse-engineering using the RKM JDE World or RKM JDE World (JDE World JDBC Driver), you can use JDE World data tables as a source of a mapping to extract data from the JDE World application and integrate them into another system (Data warehouse, other database and so forth).

Using JDE World as a source in these conditions is the same as using a DB2/400 as a source in a mapping. The generic SQL and IBM DB2 for iSeries KMs can be used for this purpose. See the following chapters in the Connectivity and Knowledge Modules Guide for Oracle Data Integrator for more information:

3.6.2 Integrating Data in JDE

After performing a reverse-engineering using the RKM JDE World, you can use JDE Z-tables as a target of a mapping to load data from any system to the JDE World application with the IKM JDE World Control Append.

The integration of data into JDE World is performed in two phases:

  • During the first phase data is integrated into a set of Z-tables using several mappings, without calling the RPG program. These mappings can use the IKM JDE World Control Append with the JDE_INVOKE option set to No.

  • During the second phase the RPG program is launched to integrate the data from these Z-tables into JDE World. This is typically done in the mapping loading the last required Z-table. This mapping also uses the IKM JDE World Control Append with the JDE_INVOKE option set to Yes.

These mappings should be sequenced in a package.

Oracle Data Integrator can automatically call the RPG program to write to JDE World. The RPG program call should be activated in the IKM only after loading all the required Z-table for populating JDE. The capability to load the Z-Tables, as well as the call of the RPG program is provided by the IKM JDE World Control Append.

To create a mapping targeting JDE World:

  1. Create a mapping with Z-tables as target datastores.

  2. Create joins, filters, and mappings as usual.

  3. In the Physical diagram properties, go to the Integration Knowledge Module tab and select the IKM JDE World Control Append.

  4. Set the standard KM options (INSERT, COMMIT, FLOW_CONTROL).

  5. If this mapping launches the RPG program, specify the KM options as follows:

    1. Set the JDE_INVOKE option to Yes.

    2. If you want to create a security file, set the connection related options as shown in Table 3-2.

      Table 3-2 Connection Related KM Options

      Option Value Notes

      USE_SECURITY_FILE

      Yes

      To enhance security when the RPG program is submitted, the system reads the text file specified in the JDE_SECURITY_FILE option and uses the JD Edwards World user, password, environment, and address as indicated in the text file.

      JDE_SECURITY_FILE

      Absolute path of the connection security file

      This file contains the JDE World user, password, environment, and address specified in the JDE_USER, JDE_PASSWORD, JDE_ENVIRONMENT, and JDE_ADDRESS options. See the security file example below for more information.

      Mandatory if USE_SECURITY_FILE is set to yes. In this case, there is no need to set valuse for JDE_USER, JDE_PASSWORD, JDE_ENVIRONMENT, and JDE_ADDRESS.

      You can use the template security file that is delivered with the files required for the RPG program. For more information, see the Tip in Section 3.2.2, "Technology Specific Requirements".

      JDE_USER

      JDE World user

      The user must have the ONEWORLD profile.

      JDE_PASSWORD

      JDE World password

      This user's JDE World password.

      JDE_ENVIRONMENT

      JDE World environment

      The JDE World environment

      JDE_ADDRESS

      JDE World address

      The IP or full address of the iSeries server


      The following example shows a security file. Make sure to use the same syntax and key names in your security file.

      Username=ODI
      Password=password
      Environment=JDEENV
      Address=iseries.organization.com
      
    3. Set the parameters for the RPG program as shown in Table 3-3.

      Table 3-3 RPG Program related KM Options

      Option Value Notes

      JDE_PRGNAME

      The name of the PRG program

      For example: P01051Z

      JDE_CLNAME

      The type of the PRG program

      For example: J01051Z

      JDE_VERSION

      The name of the version of the report that you want to process

      For example: XJDE0006.

      Note:

      • Enter the version name of the report to duplicate and process; you cannot submit the template of a report

      • ODI creates a temporary version

      JDE_KCO

      The batch ID

      For example: 1

      Note that the batch ID allows to specify which rows are to be processed by the RPG programm.

      JDE_EDTN

      The transaction ID

      For example: 1

      Note that transaction ID allows to group several rows in a single transaction. This is typically the case for header-detail tables.The headers are linked to the detail by the transaction id.This IKM does not provide truncation.


Limitations of the IKM JDE World Control Append

  • When using the RECYCLE_ERRORS option, an Update Key must be set for your mapping.

  • When using this module with a journalized source table, data is automatically filtered to not include source deletions.

  • The FLOW_CONTROL and STATIC_CONTROL options call the Check Knowledge Module to isolate invalid data (if no CKM is set, an error occurs).

  • The RPG program must be executed on the JDE World iSeries server.

  • The Oracle Data Integrator run-time agent can be installed on this server. However, it is not necessary to install the run-time agent on the server to run the RPG program. The RPG program can be executed using a local agent.

  • Besides the information whether the RPG program has been started or not, ODI does not give any further details about the execution of the program. To know more about the execution of the program you can view the log file created by the iSeries server and issue the Work with Spooled Files (WRKSPLF) command.