Creating User-Defined Tables for SQL Replication

Eclipse provides the necessary tools for creating Eclipse user-defined schema mappings in your SQL Database Maintenance. With the data replication you have standard Eclipse schema mappings. However, these may provide to much or too little data for the types of queries you want to run. You cannot change the standard mappings for Eclipse schemas, but you can copy them and create your own.

Note: You must have the SQL.SERVER Level 2 authorization key in order to create user-defined tables. If you are assign Level 1, the windows display as view-only.

Remember, you cannot have a user-defined tables (eclipse_ud schema) and a standard Eclipse tables (eclipse schema) sync-enabled the same file because the eclipse schema always takes priority over the eclipse_ud schema tables. The sync process for these tables is mutually exclusive. If both tables are sync-enabled, such as eclipse_ud.product and eclipse.product, the system syncs the standard eclipse table only. In this example, only eclipse.product is synced. You would have to disable the eclipse_ud.product sync.

You may need to experiment with your user-defined file settings and attributes to make sure you are syncing all the data you require for your queries.

To create a user-defined table:

From the System > Custom > Add On Products menu, select SQL Database Maintenance to display the SQL Database Maintenance window.

In the Schema field, select eclipse_ud.

The system updates the Table Names with the current eclipse_ud schemas in the system.

Click New Table.

At the New Table Name prompt, enter the name you want to use for the new table and click OK.

At the tracker prompt, enter the tracker number and click OK. The SQL Table Maintenance displays. Show Example

The system prompts you for a tracker number as your mappings are versioned and become part of the source files for your site. We recommend creating a special tracker number to record your changes (or use AAA000) and enter a description that becomes part of the programming change log for your mapping intent.

In the Eclipse File field, select which file you want to get data from for your table, such as PROD.CLASS or CUST.CLASS.

If using a parent/child relationship, use the Parent File field to indicate which parent your file is a child of.

Use the Columns, Indexes, and Properties tabs to complete your table information details. Show Example Window

Note: The bottom status bar displays the table version number and the user to whom the schema is currently open. The number in brackets indicates the number of changes you have made.

Column

Description

Column Name

By default the system populates the first line item with the record ID. This entry ensures that each row has its own record ID.

In the next column, enter the second column name and press Enter.
The SQL Table Column Maintenance window displays.
Use the window to enter further details about the column.
Repeat the data entry for each piece of user-defined Eclipse file that you want to map to this SQL table.

Note: Use the underscore character (_) to separate words. The system does not accept periods and spaces.

File Attribute

Enter the File Attribute number you want to sync. If you need to sync a sub-value, use a comma to separate the value and sub-value, such as 3, x.

For example, if you are pulling all the e-mail addresses for a customer, all the e-mails are sub-values.

Eclipse Data Type

Indicate the data type for inside Eclipse. Typically Eclipse does not need them, however, when you are converting data, such as decimals or dates, setting the data type here tells the conversion program what kind of data it is and then can import/convert to the right SQL data type.

SQL Data Type

The SQL data type, such as nvarchar, plus the character limit on the column listed in parentheses.

Indexes

Properties

Your initial setup is complete. You can always revisit the mapping and make adjustments if the results are not what you expected.

Save your changes and exit the window.

From the System > Custom > Add On Products menu, select SQL Sync Maintenance window.

Select the file you are mapping and from the File menu, select Sync Enable.

Use a SQL tool, such as SQL Server Management Stuidio, to confirm the replicated data is what you expect and want.

Go back to the SQL Sync Maintenance window for your user-defined table.

From the File menu, select Close to create a full version of your mapping.

Column	Description
Index Name	By default, the system creates two indexes. idx_eclipse_id - An index on the Eclipse record ID. The system needs this index for quick deletes for the inserts that occur during real-time Eclipse file record updates. pk_[table_name] - The primary key index on the table. If you need to create additional indexes for BAQ/Dashboard performance on your user-defined SQL table, see Adding Indexes for ICE Through Eclipse.
Index Type	SQL Server Management Stuido indicates the index type that is best for your BAQ performance. For more information about indexes see, About Indexing for ICE Eclipse Tables and Adding Indexes for ICE Through Eclipse.
Index Is Unique?	SQL Server Management Stuido indicates the index type that is best for your BAQ performance. For more information about indexes see, About Indexing for ICE Eclipse Tables and Adding Indexes for ICE Through Eclipse.