TC - Foreign Table

TC: Preparing a Foreign Table Data Source Details

Table Conversion - 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.

Table Conversion

Table conversions are the type of batch process that allows doing high-speed manipulation of data in tables

4 types of table conversions

1.Data Conversion

Allows transferring or copying data from one input table to one or more output tables. Also used to update the records in the table or business view.

2.Data Copy

Allows to copy one or more tables from one data source to another data source or from one environment to another environment.

3.Data Copy with Table input

Allows to copy tables based on the information from an input table

4.Batch Delete

Allows deleting records from a table or a business view

Foreign tables are the tables that are not one-world tables but reside in database supported by one world. We test the conversion by running in the proof mode

Table conversions consists of template and one or more versions.

We can use standard one-world tables and non-one world tables (foreign tables) for table conversions.  We must setup the data source and environment in one world to point to the location of non-one world tables.

Table conversion in Multiple tables to single table or multiple tables to multiple tables, we must establish a relationship between input tables by defining a business view.

The system does not support joining the multiple foreign tables, for that we need to define them through the one world and then create a business view over them.

Sort and Selection features in table conversion will simplify the process of writing records to multiple tables in a typical one-many conversion.

Environment=Path Code+Object Configuration Management (OCM) mapping Records

Path code used to locate the specification file in the environment.

To locate non-one world tables, the table conversion tool use the default OCM mapping records for table

Table conversion tool uses three environments i.e.,

1.Environment we signed in (determines where the table conversion specifications are stored),

2.Environment of input tables,

3.Environment of output tables.

Table conversion tasks

1.Setting up a table conversion

2.Running a table conversion

3.Preparing non-one world tables for table conversion

SETTING UP TABLE CONVERSION TOOL

With the help of the Director, you can design conversions for converting data, copying

Tables between locations, and deleting records within tables.

We can define User Defined Formats (Flat Files) to use in the conversion.

User Define Formats are the tables that store data as one continuous string of information, such as back tapes.

If you are importing data from non-One World tables, you must set up a data source and environment for those tables.

You are mapping from multiple tables, you must create a joined Business

View over the tables.

When a table conversion is processed the system triggers certain events, these events are specific to the conversion set up and also provides the points where we can add logic points to the conversion

The event flow is Copy Data, Copy Data with Table Input, and Batch Delete because these conversion types are just subsets of a data conversion.

The Data Copy conversion type does not include input and output tables, and all actions are accomplished through the Process Begin event.

Data Copy with Table Input and Batch Delete conversion types do not include output tables and all actions are accomplished through the Process Begin, Process End, and Row Fetched events.

This flexibility allows you to mix and match table conversion types within another

Conversion type, if necessary.

The Events in Table Conversion occur in the following order:

Process Begin Before fetching any records from the input table, the system invokes the Process Begin event. At this point, you can attach any logic that processes only once at the beginning of a conversion, or any other value that does not change for each individual record. This event is useful for mapping output fields that do not change for each individual record.

Data Changed If you use data sequencing, the system invokes a Data Changed event for any sequenced field that changed. Data Changed events are not cascaded or hierarchical. For example, you can attach an event rule to this event if you want to total a field or group of values.

Format Fetched If you use user-defined formats (also known as flat files) on the input table, the system invokes a Format Fetched event for each record fetched from the input table. If you use multiple user-defined formats in a conversion, the Format Fetched event that is called will correspond to the format found in the record.

Row Fetched Input Table invokes a Row Fetched event after each row is fetched from input table.

Process End After all records have been processed, the system invokes the Process End event.

You attach event rules to Process End when you want the system to process logic after all input records have been read - for example, to write a total record to an output table, or to write a record to a log file to record the status of the conversion.

The input table maybe

• A single table
• Multiple tables defined through a business view
• A single text file



Data Conversions-involves the fallowing steps

• Defining external data
• Defining input and output environments
• Defining input
• Defining output
• Mapping input to output
• Choosing logging options
• Reviewing the results of the director
• Understanding user-defined formats
• Defining user-defined formats

Data Copy-involves the fallowing steps

• Defining external data
• Defining input and output environments
• Defining data copy actions
• Choosing logging options
• Reviewing the results of the director

Data Copy with Table Input-involves the fallowing steps

• Defining external data
• Defining input and output environments
• Defining input
• Defining data copy actions
• Choosing logging options
• Reviewing the results of the director

Batch Delete-involves the fallowing steps

• Defining external data
• Defining the environment
• Defining input
• Choosing logging options
• Reviewing the results of the director

DESIGNING

The Director leads you through a linear process for creating a data conversion batch application by asking questions about its structure and function.

1.Defining external data

To apply a PO Template or a Data Structure for report interconnects.

2.Defining input and output environments

Choose the “Force Version to Override Input Environment” or “Force Version to Override Output Environment” if you are creating a table conversion that will run in a different environment than the one in which you are creating it.

3.Defining input

If Input consists of multiple tables, you must create a single, joined business view.

If you are using a text file, or if you need to define a user-defined format for a table or business view. Define data sequencing for a table or business view, if you specify a text file for input, you cannot define data sequencing or selection for that file.

One new event is created for each of the sequence columns that you define.

Define data selection criteria over database table columns. User-defined format columns are not available because they do not exist in the database.

4.Defining output

You can output to a table or text file. You can choose multiple outputs per conversion.

If you are using a text file, or if you need to define a user-defined format for a table or business view. Table Options are Currency triggers (choose this option if your input and output tables use currency fields), Clear Output (clears the output table), Force Row by Row (You can either specify the number of rows to process in the jde.ini file under [Table Conversion Engine]), Buffer Inserts to the Output Table (if there are no event rules choose this option to increase the performance).

5.Mapping input to output

Specify the event on which you want mapping to occur by choosing an event from the Events list. In most cases, you use either the Row Fetched Event or Format Fetched event. For example, if you are working with a user-defined format, choose the Format Fetched event.” Issue a Write for this Event” option to insert a row to the selected output after performing all column mappings for this event.

6.Choosing logging options

Logging options to record specific events that will occur during the conversion.

Logging options are,

Log All Errors                                   ----Every error should be logged,

Delete All Selected Records

Event List                                           ----Lists all the Events

Log Deletes                                        ----Every record deleted should be logged.

Log Updates                                       ----Every record updated should be logged.

Trace Level                                        ----Whether the tables copied by a copy table

                                                                 Environment or copy table data source system                                                                function call should be logged.

Log Details of Copy Table Actions  ----Detailed logging of each Copy Table Environment or Copy Table Data Source system function calls should be performed.        

To preview the actions of the table conversion before you run the actual conversion, choose the “Run in Proof Mode” option.

7.Reviewing the results of the director

The system displays the Properties form and Table Conversion Mappings form.

8.Understanding user-defined formats

User-defined formats are the table conversion tool’s way of dealing with fixed-width or character separated value (CSV) files in a table or text file. Used to import or export data from applications that have no other means of interaction. Importing/Exporting Text files.

If you are using user-defined input formats and do not add an event rule at the Format Fetched event, the system ignores the format, and the data from the input table is never made available to the conversion. The requirements for using user-defined formats as output are basically the same as for using user-defined formats as input.

9.Defining user-defined formats

To define user-defined formats in table conversion, do one of the following:

Define delimited, single- or multiple-format files

Define fixed-width, single- or multiple-format files

Force Version to Override Input

If you choose this option, the conversion will not run unless you creates a version that overrides the input environment.

Force Version to Override Output

If this option is chosen, the conversion will not run unless the user creates a version that will override the output environment.

Defining data copy actions

Table

If you want to copy a single table, choose <Literal> and enter the name of that table on the Single Value Tab.

To Table

Enter either the last table in a range of tables to be copied, or leave this field blank if you are copying a single table.

Source Type

Choose Data Source if your input and output sources are data sources. Choose Environment if your input and output sources are environments. When you choose Data Source or Environment, the appropriate system function (such as CopyTableEnvironment or CopyTableDataSource) is invoked during processing.

Input Source

The input source is the data source or environment from which the inputs will be read.

Output Source

The output data source is the source or environment where the output is written.

Create

If you choose <If Table Exists>, the system creates the table and runs the conversion only if both the table specification and the actual table exist in the input.

If you choose <Yes>, the system creates the table. If the table already exists in the output, the system will delete and re-create it.

If you choose <No>, the system assumes the table already exists in the output and will not re-create it.

Clear

If you choose <If Table Exists>, the system clears the table only if it exists in the input.

If you choose <Yes>, the system deletes all rows in the output table before copying the table.

If you choose <No>, the output table will not be cleared.

Note that this can result in key conflicts.

Copy

If you choose <Yes>, the system copies the data from the input table to the output table using Map Same.

If you choose <No>, no data is copied.

Owner ID & Owner Pwd

If the data source requires an owner ID and password, enter them here. If you leave these fields blank, the system enters the ID and password of the login user, or <None> if the data source does not have security.

Using Event Rules in Table Conversion

You can use event rules to build complex functional capabilities into table conversions. For example, you can use event rules to insert information into a table or delete one or more rows in a table based on certain conditions.

You attach event rules to a particular event, such as Process Begin, Row Fetched,

Format Fetched, and Process End.

Event rules in table conversion include system functions that are specific to the table conversion tool.

To use event rules in a table conversion

1. On the appropriate form in a data conversion, data copy, or data copy with table input or Data Selection for Batch Delete, choose the event to which you want to add event rules from the Events drop-down list.

2. Click the Advanced ER button.

3. On Event Rules Design, choose any of the following buttons to define specific business logic:

Assignment: An assignment defines a field as a fixed value or a mathematical expression. For example, you can create an assignment that calculates a value rather than writing a business function to calculate it.

If/While: Create if and While logic statements, which are conditional instructions for an event rule.

Business Function: You can attach an existing business function, such as a function that retrieves a next number for a new customer, or a function that converts Julian dates to month, day, and year.

System Function: You can attach an existing J.D. Edwards system function, such as Copy Table Environment or User Insert Row.

Variables: You can attach variables to accumulate totals, attach variables that conditionally control what you write to a file, keep a tally of the number of records you read in, and so on.

Else: Create Else logic statements. When you create an If statement, an Else statement is automatically inserted after the If statement.

Table I/O: Table I/O allows you to open tables in the input, output, or login environment, and also allows you to open the same table twice. It also allows you to pull in data from tables other than the input table and use data from those tables to create an output record.

Report Interconnect: You use report interconnect to connect a batch process or report to the table conversion.

4. After defining your event rules, click OK.

5. Repeat steps as necessary for the different input and output formats.

Table Conversion System Functions

Each system function within event rules that you can use within a table conversion.

CopyTableDataSource Use this system function to copy a table or range of tables from one data source to another. The system copies tables based on the specifications in the login environment.

CopyTableEnvironment Use this system function to copy a table or range of tables from one environment to another. The system copies tables based on the specifications in the input and output environment. If the specifications differ, the system performs a “map and drop,” meaning that it creates a mapping between like fields in the source and destination tables, and all other fields are ignored.

TCInsertRow This system function is inserted by the table conversion system when you choose the “Issue a write for this event?” option, and it cannot be moved. It instructs the system that data should be written to the output table.

UserInsertRow Use this system function to specify when and where a row should be inserted into the specified output table.

DeleteCurrentInputRow Use this system function to delete the current record from input table.

UpdateCurrentInputRow Use this system function to update the current record in the input table after it has been changed.

SetSelectionAppendFlag Use this system function to determine whether selection criteria added by the system function SetUserSelection will be appended to or replace the existing selection criteria on the input table.

SetUserSelection Use this system function to conditionally modify data selection on the input table. Call SetSelectionAppendFlag prior to calling SetUserSelection to determine whether to replace or append to the existing data selection information on the input table.