For complete information on the JD Edwards World System, see the Oracle JD Edwards World documentation page.
This chapter includes the following sections:
J.D. Edwards World System enables integration with Enterprise systems through its interoperability framework. The Oracle JD Edwards World Adapter uses the Adapter framework and leverages various integration access methods to provide the greatest amount of flexibility and functionality.
JDE Edwards World System is a database-based integration: the J.D.Edwards application is intrinsically a database. Its reports and any information extraction from it are based on SQL queries. Similarly, write interactions with JDE Edwards World System are based on inserts into special tables. The special tables are called z-tables and are used to temporarily store data that you have imported from an external source (a flat file, spreadsheet, or another type of file). The JD Edwards World System processes this data and imports it into real database tables. The Z-tables help to ensure that the format and process of importing data are as consistent as possible.
Z-files contain all the information needed to process a transaction in the JD Edwards System and there is an associated batch processing program for each Z-file.
Z-files correspond to different types of transactions, such as sales orders and addresses. The JD Edwards World System documentation documents these different types of files and their use within the JD Edwards World System.
The JD Edwards World Adapter exposes the underlying JD Edwards World System database tables through the filter of an integration by doing the following:
Exposing only integration points, that is, alternative descriptive names of tables/columns. The JD Edwards World Adapter does not expose the "real" names of the JD Edwards World tables.
Providing a powerful UI that enables you to find the correct integration touch points
Providing a JDBC driver which ensures that only certain trusted callers are allowed to insert data into a limited set of tables.
The information in the JD Edwards World system is in tables in a format unusable to a Database Adapter. Numbers are stored without the decimal place, dates are in a certain format, and connections must be made with "translate binary=true" (AS/400 DB2 specific).
For the purposes of the JD Edwards World Adapter, these formats are handled by the JD Edwards World JDBC driver, which also handles most of the typical runtime Adapter functions such as environment setup and data translation. Thus, the JDBC driver provides a means of communication between the JD Edwards World Adapter and the JD Edwards World system Z-tables.
The JD Edwards World Adapter Features includes these features
JDE World Create Connection Template. Through the Configuration Wizard, the Adapter provides the use of connection templates to assist in connecting to the JD Edwards World System.
JDE World Custom Connection Parameters. Through the Configuration Wizard, the JD Edwards World Adapter provides the use of connection parameters specific to JDE World System.
Operation determined by table selected. The JD Edwards World Adapter enables the user to choose an operation that is determined by the table that is selected.
Select Table Browser. This functionality enables you to browse JD Edwards World Z-tables, as an aide and prerequisite to your using the JD Edwards Adapter to perform a Select against the Z-tables.
JD Edwards World System Z-tables grouped by business module. To assist you in determining which tables you want to perform inserts and selects against, the JD Edwards World Adapter provides JD Edwards World System business module groupings of Z-tables. These business modules have descriptive names that are clearly indicative of their use in the Enterprise.
The JD Edwards World Adapter exposes only those Z-tables that are to be used as integrations endpoints. It does not expose Z-tables that are not integration endpoints.
The JD Edwards World Adapter provides connection pooling support.
Z-table Insert Support. Inserts to JD Edwards World System Z-tables require that the SQL connections obtained by the JDE World JCA adapter have permission to execute insert statements.
Again, the Z-tables against which the JD World System Adapter provides you the ability to perform operations are staging tables: that is, they are data interfaces to import/export data to and from J D Edwards. External Applications always interface with the Z-tables from where you can move data into real JDEdwards World System tables using batch programming and other interfaces on the back-end JD Edwards World System.Different transactions in different functional areas have their own defined Z-tables and configurations to move them into real JD Edwards World tables. Refer to each of the vertical (Financial, Manufacturing, Human Resources) sets of JD Edwards World documentation, where you can find information about processing options that enable you to move data from their respective Z-tables.
No Polling Support. There is no polling support for the JD Edwards World Adapter.
No Transaction Support. Though many adapters generally support XA transactions, the JDEdwards World Adapter does not. This is partly because JDE World systems are non-transactional. Journalling is typically not enabled on the AS/400 DB2 database underlying the JD Edwards World System, meaning that not only are XA transactions not possible, but starting and committing a local transaction is also not supported.
Also, there are only two database options available within the JD Edwards World Adapter: select and insert. Select does not need to be transactional. Insert is also to a Z-table only, and is always associated with a non-transactional RPG call.
There are several preparatory configuration steps involved with the JD Edwards World Adapter. Prior to deploying the Adapter, you must provide both runtime and designtime configuration.
A JCA connector must provide pooling support. With the Database Adapter, SQL connections are obtained from a Data source. In this way, pooling requirements are delegated to the underlying JDBC driver and its DataSource implementation.
Because the JD Edwards World JCA Adapter is based on the Oracle Database Adapter, the process for configuring pooling support for the JD Edwards World Adapter is similar to that of providing pooling support for the Oracle Database Adapter.
To provide connection pooling for the JD Edwards World Adapter:
Ensure that the Oracle WebLogic Server is installed on a build which contains the JD Edwards World Adapter. Next, search for and find the JD Edwards World Adapter on the Search Panel on the WebLogic Console.
Enter the name of the Adapter in the Search field. The Search Panel displays the search results, JDEdwards World Adapter, with its type AppDeployment, See Figure 15-1.
Figure 15-1 Searching for the JDEdwards World Adapter on the WebLogic Console
The JDEdwards World Adapter is an optional Adapter, thus its initial JDeveloper state is Installed, not Active. You must make the JDEdwards World Adapter state active. To make the state Active:
Select the Targets tab on the WebLogic Console
Select the Servers to which to deploy the Adapter. Click Save.
Figure 15-2 Selecting a Server on the WebLogic Console
You must obtain the
jt400.jar file from Sourceforge (the
jt400.jar file is called JTOpen 7.9 at that location.) JTOpen is the open source version of the IBM Toolbox for Java Licensed Program Product (LPP).
jt400.jar file under
To verify that the Adapter is active, select the Control tab. You might need to manually activate it. To do so, select the checkbox next to
JDBCWorldAdapter and choose Start.
Figure 15-3 Verifying the JD Edwards World Adapter is in Active Status
The next step is to create a data source connection by proceeding to Data Sources -->Services on the WebLogic Console and selecting Generic Data Source:
Figure 15-4 Choosing a Generic Data Source
Supply the name
jdbc/JDEWorldDataSource. This represents the term to which the default JCA
eis/JDEWorld/JDEWorldDemo instance is referring. Use database type
Figure 15-5 Defining the JDBC Data Source JDEWorldDataSource
Within the Transactions tab, disable XA by ensuring that Supports Global Transactions is not selected. See Figure 15-6.
Figure 15-6 Ensuring Supports Global Transactions is Not Selected on the WebLogic Transactions Tab
Proceed to the Connection Pool page to configure the Connection Pool information. See Figure 15-7.
The Connection Pool URL should resemble the following. Be sure to also specify three settings (User, ServerName, and JDEWEnvironment) as properties on the Connection Pool page, even though all three appear either in the connection URL already or were entered when you configured the data source:
jdbc:jdew://JDED;JDEWEnvironment=QA92;JDEWTableNomenclature=OBJN_OBJT;JDEWColumnNomenclature=FDFT_FDFN;translate binary=true;date format=mdy;prompt=false;auto commit=true; user=BPELTEST serverName=JDED2.us.mydomain.com JDEWEnvironment=A93BPELTEST JDEWTableNomenclature=OBJN_OBJT JDEWColumnNomenclature=FDFT_FDFN
Figure 15-7 Configuring the Connection Pool URL on the WebLogic Console
Select Advanced and set Connection Reserved Timeout to 180. This value is required because creating a connection can take three minutes the first time the Adapter connects using the Connection Pool.
Figure 15-8 Setting the Connection Reserved Timeout Value
Next, select the Control tab to manually control each instance of the JDBC Data Source.
Figure 15-9 Using the Control Tab to Manually Manage Each Instance of the Deployed Data Source
You can use the JD Edwards Adapter Configuration Wizard to configure the JD Edwards Adapter to perform an insert operation to the JDEdwards World Z-tables.
To do so:
Figure 15-10 JDE World Adapter Configuration Wizard Select Table Screen
To do this, you must have a basic understanding of the Z-Tables against which you want to perform the operation and the root database table. It also helps to understand the JD Edwards World System business module against which you are performing insertions or making queries.
This page enables you to import Z-tables by clicking the Import Tables button at the bottom of the page. When you select a Z-Table from this list, you are selecting a table that is the root database for the operation.You can also remove Z-tables you have added.
To do so:
Select the Import Tables button to import Z-tables, and select the table you want as the root database table, or
After importing, you can remove tables. To remove a table from the list, select the appropriate tables, then select the Remove Tables button to remove Z-Tables from the list of ones you want imported.
Figure 15-11 Selecting the Root Database Table
You can define new relationships by clicking the Create button, which is shown at the bottom of the screen and enables you to provide a table that can be reachable from the root database. Next to the Create button is the Remove button, which enables you to remove a table from being reachable from the root database table.
You can also rename a relationship table by clicking it in rename it. You can also just move to the next screen.
After you have optionally completed creation or removal, or not performed either of these, as in the example, select Next.
Figure 15-12 The JDEWorld Adapter Configuration Wizard Relationships Screen
You can also uncheck any attributes you want to exclude from the database queries you perform. Note that you cannot exclude primary key attributes. Select Select All to select all attributes or Deselect All to deselect all attributes.
Figure 15-13 The JDEWorld Adapter Configuration Wizard Attribute Filtering Screen
Figure 15-14 The JDE World Adapter Configuration Wizard Advanced Options Screen
In the Auto-Retries section, specify the value for auto-retry in case of an operation time out. This value is used if there is a connection-related fault, and the Invoke activity can be automatically retried a limited number of times. You can specify the following values in the fields in this section:
To retry indefinitely, type
unlimited in the Attempts field.
Interval is the delay between retries.
Backoff Factor: x enables you to wait for increasing periods of time between retries. 9 attempts with a starting interval of 1 and a back off of 2 leads to retries after 1, 2, 4, 8, 16, 32, 64, 128, and 256 (28) seconds.
Max Interval is the number of intervals of delays between retries.
Figure 15-15 JDE World Adapter Configuration Wizard JCA Endpoint Properties
The JDEWorld Adapter provides the ability to perform a select, or query from a JD Edwards World Z-table. You can configure this operation using the JD Edwards World Adapter Configuration Wizard.
To do so:
Figure 15-16 JD Edwards World Adapter Select Table Screen
Here, Select has been chosen.
Figure 15-17 JDE World Adapter Configuration Wizard with Select Operation Chosen
This page enables you to import Z-tables by selecting the Import Tables button at the bottom of the page. When you select a Z-Table from this list, you are selecting a table that is the root database for the operation.
You can also remove Z-tables you have added. To do so:
Click the Import Tables button to import Z-tables, and select the table you want as the root database table, or
After importing tables, you can remove them. To remove a table from the list, select the appropriate tables, then select the Remove Tables button to remove Z-Tables from the list of ones you want imported.
Figure 15-18 JDE World Adapter Configuration Wizard Service Connection Screen
Figure 15-19 The JDE World System Browser
Figure 15-20 Selecting a System Table in the JDE World System Browser
Figure 15-21 Using the JDE World System Browser to Display Column and Type Information
Remove Tablesbutton to remove tables from your prospective Import list that you do not wish to import,
Figure 15-22 JDE World Adapter Configuration Wizard Service Connection Screen with Tables Ready for Import
Figure 15-23 The JDE Adapter Configuration Wizard Define Primary Keys Screen
This screen shows the relationships that are reachable from the root database table you have indicated on the previous screen. If there are no reachable relationships between tables, the screen says "No reachable relationships."
To use this screen:
Define a new relationship by selecting the Create button, or
Select Remove to remove a relationship.
To rename a relationship, select the relationship and click Rename.
When you have finished defining, renaming or removing a relationship, or if you have no relationships to create or modify, select Next.
Figure 15-24 The JDE World Adapter Configuration Wizard Relationships Screen
You can use this screen to exclude any attributes from your database queries. To do so:
Uncheck any attributes you do not want to use in your queries.
Use the Select All button at the bottom of the screen to select all attributes.
Use the Deselect All button to deselect attributes.
Figure 15-25 The JD Edwards World Adapter Configuration Wizard Attribute Filtering Screen
Enter the parameters in the field at the top and/or select the Add button to add additional SQL criteria.
Figure 15-26 The JDE World Adapter Configuration Wizard Define Selection Criteria Screen
Select Edit to use the SQL Expression Builder to create or modify the SQL Expression. See Figure 15-27 for an example of the screen.
Figure 15-27 The Expression Builder
To define your own custom Select SQL, modify the pre-generated SQL string in the Expression Builder, and click OK to use the modified SQL.
Back on the Define Selection Criteria Screen, select Use Outer Joins to return a Single Result Set for both Master and Detail Tables to use an advanced feature that influences how many total statements are used when querying against multiple related tables. The safest method is to use the default (1 per table), and this feature attempts 1 in total, by performing an outer join on all related tables into a single result set.
On this screen, you can enter new values for either or both of the JDBC options provided, or select Next to accept the default values of 0.
Query Timeout - enables you to configure a timeout value on the Select operation.
Max Rows - the maximum number of rows to return from the Select operation.
Click Next when finished.
Figure 15-28 The JDEdwards World Adapter Configuration Wizard Adapter Advanced Options Screen
To use the recommended values, select Next. These properties include:
Attempts-The number of retries. This defaults to 4.
Interval(s)-The delay, or interval, between retries. This value defaults to 1.
Backoff factor:x- Enables you to wait for increasing periods of time between retries. For example, 9 attempts with a starting interval of 1 and a back off of 2 leads to retries after 1, 2, 4, 8, 16, 32, 64, 128, and 256 (28) seconds. This value defaults to 2.
Max Interval- The maximum interval for retries. This value defaults to 120 seconds.
Figure 15-29 Oracle JDEdwards World Adapter Configuration Wizard JCA Endpoint Properties Screen
The WSDL and .jca configuration files for each of the example configured JD Edwards Adapters, using the
Addressbook tables in this chapter, follow for your reference.
The following example shows the WSDL file for the Insert operation example.
Example - WSDL File for Insert Operation
<wsdl:definitions name="jdeworldReference" targetNamespace="http://xmlns.oracle.com/pcbpel/ adapter/jdeworld/JDEW_selectAll/Insert/jdeworldReference" xmlns:jca="http://xmlns.oracle.com/pcbpel/wsdl/jca/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="http://xmlns.oracle.com/pcbpel/adapter/jdeworld/JDEW_selectAll/Insert/jdeworldReference" xmlns:plt="http://schemas.xmlsoap.org/ws/2003/05/partner-link/" xmlns:top="http://xmlns.oracle.com/pcbpel/adapter/ db/top/jdeworldReference" > <plt:partnerLinkType name="jdeworldReference_plt" > <plt:role name="jdeworldReference_role" > <plt:portType name="tns:jdeworldReference_ptt" /> </plt:role> </plt:partnerLinkType> <wsdl:types> <schema xmlns="http://www.w3.org/2001/XMLSchema" > <import namespace="http://xmlns.oracle.com/pcbpel/ adapter/db/top/jdeworldReference" schemaLocation="../Schemas/jdeworldReference_table.xsd" /> </schema> </wsdl:types> <wsdl:message name="F01018Z____Address_Book_Email_URL_Z_FileCollection_msg"> <wsdl:part name="F01018Z____Address_Book_Email_URL_Z_FileCollection" element="top:F01018Z____Address_Book_Email_URL_Z_FileCollection"/> </wsdl:message> <wsdl:portType name="jdeworldReference_ptt"> <wsdl:operation name="zFileInsert"> <wsdl:input message="tns:F01018Z____Address_Book_Email_URL_Z_FileCollection_msg"/> </wsdl:operation> </wsdl:portType> </wsdl:definitions>
The following example shows the .jca file for the Insert operation example.
Example - .jca File for Sample Insert Operation
<adapter-config name="jdeworldReference" adapter="jdeworld" wsdlLocation="../WSDLs/jdeworldReference.wsdl" xmlns="http://platform.integration.oracle/blocks/ adapter/fw/metadata"> <connection-factory UIConnectionName="JDEWConnection1" location="eis/JDEWorld/JDEWConnection1"/> <endpoint-interaction portType="jdeworldReference_ptt" operation="zFileInsert"> <interaction-spec className="oracle.tip.adapter.jdeworld. JDEWorldZTableInteractionSpec"> <property name="DescriptorName" value="jdeworldReference.F01018Z____Address_Book_Email_URL_Z_File"/> <property name="MappingsMetaDataURL" value="jdeworldReference-or- mappings.xml"/> </interaction-spec> </endpoint-interaction> </adapter-config>
The following example shows the WSDL file for the sample Select operation example.
Example - WSDL File for Sample Select Operation
<wsdl:definitions name="jd" targetNamespace="http://xmlns.oracle.com/pcbpel/ adapter/jdeworld/JDEW_selectAll/JDED_select1/jd" xmlns:jca="http://xmlns.oracle.com/pcbpel/wsdl/jca/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="http://xmlns.oracle.com/ pcbpel/adapter/jdeworld/JDEW_selectAll/JDED_select1/jd" xmlns:plt="http://schemas.xmlsoap.org/ws/2003/05/partner-link/" xmlns:top="http://xmlns.oracle.com/pcbpel/adapter/db/top/jd" > <plt:partnerLinkType name="jd_plt" > <plt:role name="jd_role" > <plt:portType name="tns:jd_ptt" /> </plt:role> </plt:partnerLinkType> <wsdl:types> <schema xmlns="http://www.w3.org/2001/XMLSchema" > <import namespace= "http://xmlns.oracle.com/pcbpel/adapter/db/top/jd" schemaLocation="../Schemas/jd_table.xsd" /> </schema> </wsdl:types> <wsdl:message name="jdSelect_inputParameters"> <wsdl:part name="jdSelect_inputParameters" element="top:jdSelectInputParameters"/> </wsdl:message> <wsdl:message name= "F0101______Address_Book_MasterCollection_msg"> <wsdl:part name= "F0101______Address_Book_MasterCollection" element="top:F0101______Address_Book_MasterCollection"/> </wsdl:message> <wsdl:portType name="jd_ptt"> <wsdl:operation name="jdSelect"> <wsdl:input message="tns:jdSelect_inputParameters"/> <wsdl:output message= "tns:F0101______Address_Book_MasterCollection_msg"/> </wsdl:operation> </wsdl:portType> </wsdl:definitions>
The following example shows the .jca file for the sample Select operation
Example - .jca File for Sample Select Operation
<adapter-config name="jd" adapter="jdeworld" wsdlLocation="../WSDLs/jd.wsdl" xmlns="http://platform.integration.oracle/blocks/ adapter/fw/metadata"> <connection-factory UIConnectionName="JDEWConnection1" location="eis/JDEWorld/JDEWConnection1"/> <endpoint-interaction portType="jd_ptt" operation="jdSelect"> <interaction-spec className="oracle.tip.adapter.jdeworld.JDEWorldReadInteractionSpec"> <property name="DescriptorName" value="jd.F0101______Address_Book_Master"/> <property name="QueryName" value="jdSelect"/> <property name="MappingsMetaDataURL" value="jd-or-mappings.xml"/> <property name="ReturnSingleResultSet" value="false"/> </interaction-spec> </endpoint-interaction> </adapter-config>