Preparing Foreign Tables for Table Conversion

This chapter provides an overview of foreign tables and discusses how to:

Add data sources.
Add environments.
Set up default Object Configuration Manager (OCM) mappings.

Understanding Foreign Tables

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
iSeries
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.

Adding Data Sources

This section discusses how to add Oracle's JD Edwards EnterpriseOne data sources.

Forms Used to Add Data Sources

Form Name	FormID	Navigation	Usage
Machine Search & Select	W986115E	EnterpriseOne Life Cycle Tools, System Administration Tools, System Administration Tools (GH9011), Data Source Management, Database Data Sources	Select where the data source resides.
Work With Data Sources	W986115A	Select the appropriate machine on which the data source resides, and then click Select on the Machine Search & Select form.	Add a data source, or select a data source to modify.
Data Source Revisions	W986115O	Click Add on the Work With Data Sources form.	Add JD Edwards EnterpriseOne data sources. Enter data source name, use, type, data class, platform, server name, database name, and owner.

Adding Data Sources

Access the Data Source Revisions form.

Data Source Name

Enter a unique name that identifies the data source.

Data Source Use

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.

Data Source Type

Select a UDC (98|DD) that indicates the type of database.

Data Class

Select a UDC (H96|DU) that describes the type of tables in the database, such as business data, control tables, and data dictionary.

Platform

Select a UDC (H93|PL) that indicates the type of physical hardware that the database resides on, such as AS/400, Linux, and Sun Microsystems.

Database Server Name

Select the enterprise server where the database or file resides.

Object Owner ID

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

Database Name

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.

Database Instance

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

ODBC Data Source Name

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 UDB on OS/400.

SSE/ODBC.

MSDE/ODBC.

DB2 UDB on UNIX or Windows.

Schema

Enter the database table prefix or owner. This field is available for these data source types:

DB2 UDB on OS/390.

DB2 UDB on UNIX or Windows.

Database Alias Name

Enter the name of the database. This field is available for these data source types:

DB2 UDB on OS/390.

DB2 UDB on UNIX or Windows.

Library Name

Enter the name of the database. This field is available for the DB2 UDB on OS/400 data source type.

Adding Environments

The easiest way to add an environment to JD Edwards EnterpriseOne is to copy an existing environment.

This section discusses how to add Oracle's JD Edwards EnterpriseOne Environments.

Forms Used to Add Environments

Form Name	FormID	Navigation	Usage
Work With Environments	W0094E	EnterpriseOne Life Cycle Tools, System Administration Tools, System Administration Tools, Environment Management (GH9053), Environment Master	Select an environment to copy.
Copy Environment	W0094B	Click an environment to copy, and select Copy Environment from the Row menu on the Work With Environments form.	Enter a name for the new environment.

Adding 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.

Setting Up Default OCM Mappings

This section provides an overview of OCM mappings and discusses how to set up default OCM mappings.

Understanding 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.

Forms Used to Set Up Default OCM Mappings

Form Name	FormID	Navigation	Usage
Machine Search and Select	W986110D	EnterpriseOne Life Cycle Tools, System Administration Tools, System Administration Tools (GH9011), Data Source Management, Object Configuration Manager	Select a data source.
Work With Object Mappings	W986110B	Select the appropriate machine on which the data source resides, and then click Select on the Machine Search & Select form.	Add, modify, and copy mappings.
Object Mapping Revisions	W986110C	Click Add on the Work With Object Mappings form.	Enter the environment name, object name, primary data source, system role, object type, and data source mode for a new mapping, and indicate whether to allow QBE (query by example) for the mapping.

Setting Up Default OCM Mappings

Access the Object Mapping Revisions form.

Environment Name

Select the name of the environment where the tables reside.

Object Name

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.

Primary Data Source

Select the name of the primary data source.

System Role

Enter *PUBLIC, or select the JD Edwards EnterpriseOne user or role that had permissions to use these mappings. *PUBLIC gives all users permissions.

Object Type

Select a UDC (98|OM) that indicates the type of object for which you are creating a mapping.

Data Source Mode

Select a UDC (98|DM) that indicates whether the primary or secondary data source should be used.

Secondary Data Source

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.

Allow QBE (query by example)

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.

Data Source Name	Enter a unique name that identifies the data source.
Data Source Use	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.
Data Source Type	Select a UDC (98\|DD) that indicates the type of database.
Data Class	Select a UDC (H96\|DU) that describes the type of tables in the database, such as business data, control tables, and data dictionary.
Platform	Select a UDC (H93\|PL) that indicates the type of physical hardware that the database resides on, such as AS/400, Linux, and Sun Microsystems.
Database Server Name	Select the enterprise server where the database or file resides.
Object Owner ID	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
Database Name	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, SQLServer, or Client Access data source is added, then this field contains the Windows ODBC data source name. To minimize the number of connections to SQLServer, 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.
Database Instance	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
ODBC Data Source Name	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 UDB on OS/400. SSE/ODBC. MSDE/ODBC. DB2 UDB on UNIX or Windows.
Schema	Enter the database table prefix or owner. This field is available for these data source types: DB2 UDB on OS/390. DB2 UDB on UNIX or Windows.
Database Alias Name	Enter the name of the database. This field is available for these data source types: DB2 UDB on OS/390. DB2 UDB on UNIX or Windows.
Library Name	Enter the name of the database. This field is available for the DB2 UDB on OS/400 data source type.

Environment Name	Select the name of the environment where the tables reside.
Object Name	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.
Primary Data Source	Select the name of the primary data source.
System Role	Enter PUBLIC, or select the JD Edwards EnterpriseOne user or role that had permissions to use these mappings. PUBLIC gives all users permissions.
Object Type	Select a UDC (98\|OM) that indicates the type of object for which you are creating a mapping.
Data Source Mode	Select a UDC (98\|DM) that indicates whether the primary or secondary data source should be used.
Secondary Data Source	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.
Allow QBE (query by example)	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.