|
Sun4 To Sun5 Data Migration |
|
|
|
|
Sun Documentation -
System Implementation
|
|
Page 8 of 9
Switching on Referential Integrity
18. Enter the Business Unit code of the SunSystems 5 business unit you wish to apply the referential integrity to.
19. Click RI. This enables referential integrity to be applied to the selected business unit tables and the system tables.
20. The application of Referential integrity is a two-stage process. Firstly the specified business unit and system tables are checked for referential integrity errors. If errors are found these are reported and processing is terminated. If no errors are found referential integrity is applied to the tables.
21. If referential integrity errors are reported these must be corrected before referential integrity is re-run. The errors are held in the table RI_ERR. Correct the errors and click Apply RI. See the section below for an explanation of how to interpret the entries in the RI_ERR table. Click Cancel to end the Data Migration session before accessing the RI_ERR table.
22. If no errors are reported in the table RI_ERR, the referential integrity is applied for the specified business unit and system tables.
23. Check if any errors occurred during the application of referential integrity by opening the file called RefIntegrity-ZZZ.LOG (where ZZZ is the SunSystems version 4.2 business unit). This file is created in your Print folder. In a standard SunSystems 5 installation this folder is called \_print).
This completes the successful migration of the system tables and the data for a business unit.
Correcting Referential Integrity Errors
Referential Integrity in SunSystems 5 is much stricter than in SunSystems 4, hence there may be referential integrity errors reported after a migration as data that is acceptable in SunSystems 4 is not allowed in SunSystems 5. All referential integrity errors occur because a value in a foreign key column on a child table does not occur as data in the primary key column of the parent table. The user must correct these errors. There are two ways of correcting these errors. Either a row must be inserted into the parent table with the data from the foreign key in the primary key, or the foreign key data or row containing the foreign key in error must be deleted from the child table.
The rows in the RI_ERR table give the user the information on the Parent table, the Child table, the Child Table column affected and the data missing from the Parent table primary key. The entries in the RI_ERR table do not report the primary key value of the child rows in error.
There are various methods of finding these:
I. If the number of rows in the child table is small, access the static data maintenance function that maintains the child function e.g. ZZZ_SUPP is the Supplier Table, so the SDM function is Suppliers Setup (SUS). Click next through the rows until you find the reported value in the reported column.
II. For larger sets of data it may be more convenient to access the tables directly using
SQLServer.
⇒ Use Enterprise Manager to bring up a grid of data for the child table:
1. Click Start, Programs, Microsoft SQLServer 7, Enterprise Manager. Open the server running SunSystems by double clicking on the Server Name, click Databases, The Sun database, Tables. Scroll down the right pane to the child table in error. Right click on the child table to be interrogated, click open table, return all rows.
2. The rows in the table are displayed in a grid. Search for the invalid values in the column reported. The primary key is usually the first column in the grid.
⇒ Use Query Analyser to query the table and bring back a set of results:
1. Click Start, Programs, Microsoft SQLServer 7, Query Analyser.
2. Enter the SQL Server, i.e. the name of the server the SunSystems database resides on.
3. Enter the Login name and Password.
4. In the Query Analyser window select the Database from the dropdown list in the top right hand corner of the window.
5. Key in the following query.
SELECT * FROM AAA
WHERE BBB = CCC
Where AAA = Child Table Name, BBB = Child Column Name, CCC = The Foreign
Key Value
Note: columns that allow alphanumeric values must have the value in quotes. Columns that only allow numeric values must not have the value in quotes.
The primary key is usually the first column in the returned data set.
|
Sun Documentation 
