4 JD Edwards World
This chapter includes the following sections:
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.
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)
Knowledge Modules
Oracle Data Integrator provides the Knowledge Modules listed in Table 4-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 4-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.
|
Installation and Configuration
Make sure you have read the information in this section before you start working with the JDE World data:
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/technetwork/middleware/data-integrator/overview/index.html
.
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:
-
JDEWorldJDBC.jar
- This JDE World JDBC driver file must be in the~/.odi/oracledi/userlib
folder.See Add Additional Drivers and Open Tools in the Installing and Configuring Oracle Data Integrator for more information about these folders.
-
BaseJar.jar
- This jar file must be in the~/.odi/oracledi/userlib
folder. -
config.xml
- This configuration file must be in theODI_HOME/odi/studio/bin
folder.
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 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 4-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.
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://192.0.2.1;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 Requirement in the Connectivity and Knowledge Modules Guide for Oracle Data Integrator.
Setting Up the Topology
This step consists in declaring in Oracle Data Integrator the data server, physical schema and logical schema that will be used to store the JDE World data.
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://192.0.2.1;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, refer to the JDE World JDBC Driver User Guide.
Create a Physical Schema
Create a physical schema under the data server that you have created in 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.
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 the Connectivity and Knowledge Modules Guide for Oracle Data Integrator.
Creating and Reverse-Engineering a Model
This section contains the following topics:
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.
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:
The reverse-engineering process returns the datastores grouped per module. You can use these datastores as a source or a target of your mappings.
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.
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 Integratorfor more information:
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 and the call of the RPG program is provided by the IKM JDE World Control Append.
To create a mapping targeting JDE World:
-
Create a mapping with Z-tables as target datastores.
-
Create joins, filters, and mappings as usual.
-
In the Physical diagram properties, go to the Integration Knowledge Module tab and select the IKM JDE World Control Append.
-
Set the standard KM options (INSERT, COMMIT, FLOW_CONTROL).
-
If this mapping launches the RPG program, specify the KM options as follows:
-
Set the JDE_INVOKE option to
Yes
. -
If you want to create a security file, set the connection related options as shown in Table 4-2.
Table 4-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 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
-
Set the parameters for the RPG program as shown in Table 4-3.
Table 4-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.