TC: Preparing a Foreign Table Data Source
ISSUE:
What steps are required in order to use a Foreign Table in an EnterpriseOne Table Conversion.
SOLUTION:
Foreign Table
==========
A foreign table is any table that is not a part of the current EnterpriseOne, ie does not have spec in E1. Valid tables that can be used by a table conversion include database tables in JDE supported databases (Oracle, SQL Server, DB2). In order to get data from foreign tables to EnterpriseOne tables, a data source must be created so the table conversion program can access the foreign tables. There are four basic steps in preparing a foreign table data source: an ODBC data source name must be created, an environment must be created, a data source must be created, and Object Configuration Manager must have information added for the foreign table. It will be easiest to do the steps in the order shown.
Foreign Table Database Type
======================
The foreign table must be in a database type that can be read by EnterpriseOne. The accepted database types as of the time this was written are listed below.
1. DB2/400
2. Oracle
3. Microsoft SQL Server
Add ODBC Data Source Name
=======================
Note: These steps should only be done for foreign tables that will be accessed through ODBC. For foreign tables in an Oracle database, an ODBC data source would not be required. Also, there is no documentation specifically for table conversion running on an AS400 and accessing a SQL Server or Oracle Database Server(the same for TC running on unix accessing DB2 or SQL Server). However, it can be done but in order for the AS/400 to communicate with the SQL Server or Oracle Server, those servers server must have the host code-JDBNET loaded. The process is the same as adding another enterprise server for NT/SQL platform.
1. From the Window development machine and server select Start Button -> Settings -> Control Panel -> ODBC Icon -> System DSN tab -> Add Button. If the ODBC data source is to be limited to one person select Start Button -> Settings -> Control Panel -> ODBC Icon -> User DSN tab -> Add Button.
2. In the Create New Data Source window you must select the correct driver for the type of foreign table. Double Click on the driver.
3. In the ODBC Setup window there will be a Data Source Name Field. This name is important because the name entered here must be the exact same name used in Database Data Sources Database Name.
4. In the ODBC Setup window different types of drivers may have different ways of setting the path to the foreign tables. It is important that the path is set to correctly point to the foreign tables to be converted.
5. The rest of the settings in the ODBC Setup window need to be made by someone with knowledge of the selected data driver type. Looking at an EnterpriseOne ODBC data source of the same type may assist in this setup.
Create an Environment
=================
1. In the Fast Path field enter GH9053. This will take you to the Environments directory. Select Environment Master.
2. In Environment Master, find a valid environment and select Copy button from the tool bar. Copy all the *public OCM record.
3. Make up a name for the new environment and place it in the Environment Name field. This will be the exact name that will be used later in the Object Configuration Manager.
4. Enter a description in the Description Field.
5. Select a Path Code that you have loaded on your workstation for the Path Code Field. This environment will be used for the foreign database. The path code must exist on the machine where the table conversion is run or the table conversion will fail.
6. Select the release for the Release field. This should be the same release which will run the table conversion.
7. Place "N" for no in the Just In Time Installation field.
8. Place "Y" for yes in the Developer field.
9. Check the entries in all the fields. If they are correct select the OK button from the tool bar.
Add a Data Source
===============
1. In the Fast Path field enter GH9011. This will take you to the System Administration Tools directory. Select Database Data Sources.
2. In the Database Data Sources program select the Machine Name / Data Source for the Data Sources table (F98611) that will contain the new data source information. This should be the System data source to create a workstation database data source, example: System_B733. Do the same for server map if the TC will be running on server after development.
3. In the Work With Data Sources form, the Data Sources use field should be DB.
4. Select the Add tool bar button.
5. Enter a new data source name in the Data Source Name field. This is the EnterpriseOne data source name. It will be the data source name used in creating the mappings in the OCM.
6. In the Data Source Use, enter DB.
7. In the Data Source Type field enter the database type for the new (foreign) data source. Please note that when the foreign table is SQL the Data Source Type in the Database Data Source has to be ODBC, so for a SQL foreign table use Database Data Source, Data Source Type S-SQL Server ODBC. Do not use an OLEDB Data Source Type.
8. In the Data Class, enter B for Business Data.
9. Select the appropriate platform type.
10. Enter in the database server name where the foreign table is stored.
11. For a SQL ODBC, enter in the appropriate Object Owner ID
12. Enter the foreign database name in the Database Name field.
13. In the ODBC Data Source Name, enter the ODBC name. This must be the same name of the foreign data source entered in the ODBC data source name in the steps above.
14. Take the Advanced Form exit and verify setting for, Unicode, Use Decimal Shift, Use Julian Dates, Use Table Owner.
Object Configuration Manager Default Map
===============================
1. In the Fast Path field enter GH9011. This will take you to the System Administration Tools menu. Select Object Configuration Manager.
2. In the machine search and select window select the Machine Name / Data Source for the Object Configuration Manager table (F986101) that will contain the new OCM information. This should be the System data source for the workstation OCM record. Example: System-B9. Do the same for server map if the TC will be running on server after development.
3. In the Work With Object Mapping window, select the add button.
4. Create a new Object Mapping.
*The Environment name must be exactly the same as the environment name created in the Work With Environments. This should have been done in the environment section above.
*Enter the object name. This should be DEFAULT. By entering default here all the foreign tables will be available.
*The Primary Data Source must be the Data Source name created in Database Data Sources. This should have been done in the EnterpriseOne database data source section above.
*Set the User field to "*PUBLIC". This field can also be set to login Id of the person creating the table conversion. If this field is set to the login Id access to the foreign data tables will be limited to that person only.
*Set the object type field to "TBLE" for Table.
*Set the DS mode to "P".
*Set the QBE Allowed field to "1" - All QBE allowed.
5. Select the OK button from the tool bar.
6. Find the entry created in section 4 and highlight it.
7. In the Exit Bar or in the Form menu select Change Status. This will make the Object Status "AV".
8. Select the OK button from the tool bar.
9. Exit Object Configuration Management.
Using the new environment in Table Conversion
===================================
Within the Table Conversion on the Select Environments form, choose the environment created above for the foreign table environment. This should then show your Foreign Tables under the Foreign Table tab on the Select Input or Output forms. If getting unable to initialized environment or TC failed when run, check jde.log and resolve all table not found error by using OCM to map them to valid environment that has those tables.
If Table Conversion Design Aid prompts for User/Password for foreign table, please see solution 200955744. If this is not resolved, the TC will not run on server.
=================
1. In the Fast Path field enter GH9053. This will take you to the Environments directory. Select Environment Master.
2. In Environment Master, find a valid environment and select Copy button from the tool bar. Copy all the *public OCM record.
3. Make up a name for the new environment and place it in the Environment Name field. This will be the exact name that will be used later in the Object Configuration Manager.
4. Enter a description in the Description Field.
5. Select a Path Code that you have loaded on your workstation for the Path Code Field. This environment will be used for the foreign database. The path code must exist on the machine where the table conversion is run or the table conversion will fail.
6. Select the release for the Release field. This should be the same release which will run the table conversion.
7. Place "N" for no in the Just In Time Installation field.
8. Place "Y" for yes in the Developer field.
9. Check the entries in all the fields. If they are correct select the OK button from the tool bar.
Add a Data Source
===============
1. In the Fast Path field enter GH9011. This will take you to the System Administration Tools directory. Select Database Data Sources.
2. In the Database Data Sources program select the Machine Name / Data Source for the Data Sources table (F98611) that will contain the new data source information. This should be the System data source to create a workstation database data source, example: System_B733. Do the same for server map if the TC will be running on server after development.
3. In the Work With Data Sources form, the Data Sources use field should be DB.
4. Select the Add tool bar button.
5. Enter a new data source name in the Data Source Name field. This is the EnterpriseOne data source name. It will be the data source name used in creating the mappings in the OCM.
6. In the Data Source Use, enter DB.
7. In the Data Source Type field enter the database type for the new (foreign) data source. Please note that when the foreign table is SQL the Data Source Type in the Database Data Source has to be ODBC, so for a SQL foreign table use Database Data Source, Data Source Type S-SQL Server ODBC. Do not use an OLEDB Data Source Type.
8. In the Data Class, enter B for Business Data.
9. Select the appropriate platform type.
10. Enter in the database server name where the foreign table is stored.
11. For a SQL ODBC, enter in the appropriate Object Owner ID
12. Enter the foreign database name in the Database Name field.
13. In the ODBC Data Source Name, enter the ODBC name. This must be the same name of the foreign data source entered in the ODBC data source name in the steps above.
14. Take the Advanced Form exit and verify setting for, Unicode, Use Decimal Shift, Use Julian Dates, Use Table Owner.
Object Configuration Manager Default Map
===============================
1. In the Fast Path field enter GH9011. This will take you to the System Administration Tools menu. Select Object Configuration Manager.
2. In the machine search and select window select the Machine Name / Data Source for the Object Configuration Manager table (F986101) that will contain the new OCM information. This should be the System data source for the workstation OCM record. Example: System-B9. Do the same for server map if the TC will be running on server after development.
3. In the Work With Object Mapping window, select the add button.
4. Create a new Object Mapping.
*The Environment name must be exactly the same as the environment name created in the Work With Environments. This should have been done in the environment section above.
*Enter the object name. This should be DEFAULT. By entering default here all the foreign tables will be available.
*The Primary Data Source must be the Data Source name created in Database Data Sources. This should have been done in the EnterpriseOne database data source section above.
*Set the User field to "*PUBLIC". This field can also be set to login Id of the person creating the table conversion. If this field is set to the login Id access to the foreign data tables will be limited to that person only.
*Set the object type field to "TBLE" for Table.
*Set the DS mode to "P".
*Set the QBE Allowed field to "1" - All QBE allowed.
5. Select the OK button from the tool bar.
6. Find the entry created in section 4 and highlight it.
7. In the Exit Bar or in the Form menu select Change Status. This will make the Object Status "AV".
8. Select the OK button from the tool bar.
9. Exit Object Configuration Management.
Using the new environment in Table Conversion
===================================
Within the Table Conversion on the Select Environments form, choose the environment created above for the foreign table environment. This should then show your Foreign Tables under the Foreign Table tab on the Select Input or Output forms. If getting unable to initialized environment or TC failed when run, check jde.log and resolve all table not found error by using OCM to map them to valid environment that has those tables.
If Table Conversion Design Aid prompts for User/Password for foreign table, please see solution 200955744. If this is not resolved, the TC will not run on server.
No comments:
Post a Comment