This chapter contains the following topics:
Foreign tables are text files, or any other files or tables, that are not recognized by Oracle's JD Edwards EnterpriseOne software. However, they must reside in a database that is supported by JD Edwards EnterpriseOne, such as:
Oracle
Access
IBM i
SQL Server
Before you can work with foreign tables in Oracle's JD Edwards EnterpriseOne Table Conversion Design Aid, you must define the database to JD Edwards EnterpriseOne. This definition is created when you set up an ODBC data source or an Oracle Call Interface (OCI) data source that points to the database where the foreign tables reside.
You must also add a data source in JD Edwards EnterpriseOne that points to the ODBC or OCI data source that you defined. For each ODBC data source, database instance, or library that contains foreign tables, you must set up an environment. The environment points to the JD Edwards EnterpriseOne data source, which in turn points to the database or library. In addition, you must set up an ODBC mapping from the data source to the environment.
Note:
When you work with foreign tables, you must discuss database permissions with a database administrator. Your JD Edwards EnterpriseOne user ID (or, if you are using the JD Edwards EnterpriseOne security server feature, the database user to which it maps) might need to be changed to give you permissions to use the tables in the foreign database. Without these permissions, you cannot view the foreign tables in JD Edwards EnterpriseOne Table Conversion Design Aid. Under certain conditions, the table conversion engine must create temporary tables in the output environment and requires create-and-drop permissions for the database.This section discusses how to add Oracle's JD Edwards EnterpriseOne data sources.
Access the Data Source Revisions form.
Enter a unique name that identifies the data source.
Select a user-defined code (UDC) (98|SU) that indicates how the data source is configured. Select Servers (SVR) to run UBEs and business functions. Select Database (DB) to access table data.
Only database data sources are used to access data in tables.
Select a UDC (98|DD) that indicates the type of database.
Select a UDC (H96|DU) that describes the type of tables in the database, such as business data, control tables, and data dictionary.
Select a UDC (H93|PL) that indicates the type of physical hardware that the database resides on, such as IBM Power Systems,Linux, and Sun Microsystems.
Select the enterprise server where the database or file resides.
Enter the database table prefix or owner. This field is available for these data source types:
SQL Server OLEDB
SQL Server ODBC
SSE/ODBC.
MSDE/ODBC
MSDE/OLEDB
OEE
Oracle
Enter the name that was assigned to the database during installation, such as HPDEVORAP or HP9000.
How this field is used depends on the data source type. If an Oracle data source is added, then this field contains the Oracle connect string. If a Microsoft Access, SQL*Server, or Client Access data source is added, then this field contains the Windows ODBC data source name. To minimize the number of connections to SQL*Server, the ODBC data source name should be the machine name where the SQL*Server database resides and the catalog name should be defined using the individual database name. For example, if you have two databases, DatabaseA and DatabaseB, on a machine called INTELNT, this field should contain INTELNT and the catalog name should be set to DatabaseA for one data source and DatabaseB for the other data source.
The data source name can be different from the ODBC or Oracle database name, if necessary.
Enter the name of the server instance. This name is required for these data source types:
SQL Server OLEDB
SQL Server ODBC
SSE/ODBC
MSDE/ODBC
MSDE/OLEDB
Enter the name that is assigned to the database during installation, such as HPDEVORAP or HP9000. This field is available for these data source types:
Access
SQL Server ODBC
DB2 UDB on OS/390
DB2 for IBM i
SSE/ODBC
MSDE/ODBC
IBM DB2 for LUW (Linux, UNIX, Windows)
Enter the database table prefix or owner. This field is available for these data source types:
DB2 UDB on OS/390
IBM DB2 for LUW (Linux, UNIX, Windows)
Enter the name of the database. This field is available for these data source types:
DB2 UDB on OS/390
IBM DB2 for LUW (Linux, UNIX, Windows)
Enter the name of the database. This field is available for the DB2 for IBM i data source type.
The easiest way to add an environment to JD Edwards EnterpriseOne is to copy an existing environment.
This section discusses how to add JD Edwards EnterpriseOne environments.
Access the Work With Environments form.
Click the environment that most closely matches the environment that you want to create.
For example, you might click the environment that you are signed in to or any other environment that you can access from the workstation.
From the Row menu, select Copy Environment.
On the Copy Environment form, enter a name for the new environment in the New Environment field.
To copy only the *PUBLIC OCM mappings of an environment, select Copy *PUBLIC Records Only and click OK.
The Copy *PUBLIC Records Only option is selected by default. Clear the option to copy all mappings for the environment, including individual users and *PUBLIC.
This section provides an overview of OCM mappings and discusses how to set up default OCM mappings.
You must map all JD Edwards EnterpriseOne objects, including tables, by environment. You can map all objects of a specific object type using a single map, or you can define individual mappings by object name. When you define a default OCM mapping, you can select an existing environment and map the objects for that environment to the data sources where those objects exist.
You must create a default map for the table (TBLE) object type. Create this mapping with:
Literal value of DEFAULT as the object name.
This option enables you to map all tables in an environment to a specific data source.
TBLE as the object type.
You must also indicate the data source where this table object resides. When you create a default map for the TBLE object type, all table objects point to the default data source unless the table has its own specific mapping. JD Edwards EnterpriseOne Table Conversion Design Aid uses this mapping for foreign tables.
To map a specific TBLE object type to a data source that is different from the DEFAULT, enter the table name as the object name. For example, F0002 is mapped to Control Tables-Test while the DEFAULT table mapping is mapped to Business Data-Test. The system reads the DEFAULT mapping for all tables except the F0002 table.
Each environment must have a default map for table objects for the *PUBLIC user profile because no inherent default location exists for table objects. If table objects do not have a default map and are not explicitly mapped by name, JD Edwards EnterpriseOne produces a Select/Failed error message when it tries to access the tables. Additionally, the tables do not appear in the input or output forms in JD Edwards EnterpriseOne Table Conversion Design Aid.
Access the Object Mapping Revisions form.
Select the name of the environment where the tables reside.
Enter DEFAULT or the name of the specific object that you are mapping. The value DEFAULT creates a default map for all objects of a specific type.
Select the name of the primary data source.
Enter *PUBLIC, or select the JD Edwards EnterpriseOne user or role that had permissions to use these mappings. *PUBLIC gives all users permissions.
Select a UDC (98|OM) that indicates the type of object for which you are creating a mapping.
Select a UDC (98|DM) that indicates whether the primary or secondary data source should be used.
Select the name of the secondary data source. This field is available only when secondary has been selected for the data source mode. This data source is used if the primary data source or the data item in the primary data source cannot be located.
Select a UDC (98|QB) that indicates whether row-level record locking is selected for the data source. Row-level record locking should be selected to help prevent database integrity issues.