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.