This chapter describes how to work with JD Edwards World Knowledge Modules in Oracle Data Integrator.
This chapter includes the following sections:
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.
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)
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.
|
Make sure you have read the information in this section before you start working with the JDE World data:
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.
This section lists the technlogy specific requirements of the JDE World Knowledge Modules.
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.
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 the ODI_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 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.
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.
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.
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.
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" of the Developer's Guide for 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" of the Developer's Guide for Oracle Data Integratorand associate it in a given context.
Setting up a project using JDE World features follows the standard procedure. See "Creating an Integration Project" of the Developer's Guide for 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:
This section contains the following topics:
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 Developer's Guide for Oracle Data Integrator.
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 Developer's Guide for Oracle Data Integrator. This section details only the fields specific to JDE World tables:
In the Reverse tab of the Model, select RKM JDE World or RKM JDE World (JDE World JDBC Driver).
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.
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.
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.
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:
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:
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 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
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: |
|
JDE_CLNAME |
The type of the PRG program |
For example: |
|
JDE_VERSION |
The name of the version of the report that you want to process |
For example: Note:
|
|
JDE_KCO |
The batch ID |
For example: 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: 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.