Appendix C: The SQL compare utility

The SQL compare utility is an informational tool that compares your existing table fields and unique key information with those of the Service Manager version you are upgrading to, and then reports the new and modified fields and unique keys that will merge into the existing tables. You can use the list of the fields and unique keys produced by the SQL compare utility to determine whether any fields or unique keys in your current system differ from those in the new version. You can also use the report to determine which new fields and unique keys you must add to RDBMS-mapped files if you choose to make the changes manually during the applications upgrade.

After running the SQL compare utility on the table fields, refer to the following table for the result types and the recommended actions.

Result type	Recommended actions
(Null)	The related field is on the id field in the licenseinfo dbdict. No further action is necessary.
Nonexistent in Service Manager 9.61 fresh installation	This field is added by tailoring. No further action is necessary.
Not to be modified to other field type by the Upgrade Utility	Manually modify this field type according to the matching solution described in Data type mismatches.
Not to be modified to other field database type by the Upgrade Utility	Manually modify this field type according to the matching solution described in Data type mismatches.
Not to be moved to alias table by the Upgrade Utility	Manually modify this field type according to the matching solution described in Data type mismatches.
To be modified by the Upgrade Utility	This field type modification will be done automatically by the Upgrade Utility during the upgrade process. You can also manually modify this field type before the upgrade to improve the upgrade performance.
To be modified by the Upgrade Utility, and the original field name xxxx will be renamed to xxxx.old	This field type modification will be done automatically by the Upgrade Utility. No further action is necessary.
To be added by the Upgrade Utility	This new field will be added automatically by the Upgrade Utility during the upgrade process. You can also manually add this new field manually before the upgrade to improve the upgrade performance.

After running the SQL compare utility on the table unique keys, refer to the following table for the result types and the recommended actions.

Result type	Recommended actions
Nonexistent in Service Manager 9.61 fresh installation	This key normally is added by tailoring. Check this key to see whether it includes any field of added primary key or unique key.
Exist in Old OOB, and will be removed by the Upgrade Utility	This key exists in Old OOB only, but does not exist in Service Manager 9.61. The Upgrade Utility will remove this key automatically. No further action is necessary.
To be added by the Upgrade Utility	This key does not exist in Old OOB, but exist in Service Manager 9.61. The Upgrade Utility will add this key automatically. No further action is necessary.
To be ignored by the Upgrade Utility	The field is added as a unique key because Service Manager does not support operations on tables with primary keys when the applications version is earlier than 9.32. You can manually change the key type to primary key after the system upgrade.

Running SQL Compare

The following SQL Compare files are included when you install the Service Manager Upgrade Utility:

• sqlupgrade.unl
• upgdbdct.dta

SQL Compare returns messages for dbdict mappings that contain new fields. You can update the dbdicts to contain the fields specified by the SQL Compare applications before you begin the applications upgrade.

To run the SQL Compare utility, follow these steps:

1. From the System Navigator, click System Administration > Ongoing Maintenance > Upgrade Utility > SQL Compare Utility.
2. Select Run SQL Compare Utility, and then click Next.

3. Type the full path to upgdbdct.dta including the final back slash (\) or forward slash (/), depending on your operating system. For example, if you copied the files to a temporary directory, the path might be:

   Windows: c:\temp\upgrade\

   Linux: ~/upgrade/

   Note You must have read, write, and execute permission to the folder.

4. Click the Next button.

   SQL Compare returns the following message:

   Process Complete. Please check for any additional messages.

   The results of the SQL Compare process are stored in the sqlupgrade table. This table resets each time you run SQL Compare.

To view the SQL compare results of the table fields, follow these steps:

1. From the System Navigator, click System Administration > Ongoing Maintenance > Upgrade Utility > SQL Compare Utility.
2. Select View SQL Field Compare Results, and then click Next.
3. Click Search. The results are displayed in a record list.

Each table field difference found by the SQL Compare Utility appears as a separate record in the sqlupgradefield file. This record also lists the new fields that you must add to the database dictionary if you are updating your RDBMS mapped system manually.

The sqlupgradefield record provides the following information for each field you must add or modify if you are updating your RDBMS mapped system manually.

List of sqlupgradefield fields

Field	Description
File Name	The exact file name which delegates the database dictionary.
Field Name	The exact field name to add or modify to the associated database dictionary.
Field Structure	The field structure in the database dictionary.
Field Type	The field type in the database dictionary.
Field Level	The field level in the database dictionary.
Result Type	The actions happened or will happen to this field.
Field Alias Of	If this is an alias field, it contains the name of the primary field that it is an alias of. Otherwise, this field is blank.
Field SQL Table Alias	The SQL table alias of this field in the database dictionary.
Field SQL Name	The SQL name of this field in the database dictionary.
Field SQL Type	The SQL type of this field in the database dictionary.
Field SQL RC	The encoding format of this field in the database dictionary.
Exceptions	The exceptions that occurs when adding or modifying this field.

To view the SQL Compare results of the table unique keys, follow these steps:

1. From the System Navigator, click System Administration > Ongoing Maintenance > Upgrade Utility > SQL Compare Utility.
2. Select View SQL Unique Key Compare Results, and then click Next.
3. Click Search. The results are displayed in a record list.

Each table unique key difference found by the SQL Compare Utility appears as a separate record in the sqlupgradekey file. This record also lists the new unique keys that you must add to the database dictionary, if you want to check the possible duplicated record manually.

The sqlupgradekey record provides the following information for each field you must add or modify, if you want to check the possible duplicated record manually.

List of sqlupgradekey fields

Field	Description
File Name	The exact file name which delegates the database dictionary.
Result Type	The actions happened or will happen to this field.
Key Type	The unique key types include primary, unique, and noduplicated.
Key Field	Concatenates all the key fields with the | character.
Exceptions	The exceptions that occurs when adding or modifying this unique key.

Add new fields

For the new fields to perform correctly, they must exist in both the Service Manager database dictionary and the SQL database. If you are updating your RDBMS mapped system manually, you must add them to the SQL database and update the existing Service Manager SQL mapping in the database dictionary. When you update a table in sqlsystemtables, add fields only through the database dictionary. Modifying the SQL mapping damages the file structure of the table.

Determine the correct structure

In most cases, you should add the new field to the descriptor structure. However, sometimes the Structure field contains something other than the word "descriptor". When this occurs, add the new field to the appropriate location.

Action to take with non-descriptor fields:

In this instance	Add the field here
The field resides in another structure	Check the cm3r dbdict. There is a "middle" field of structure type.
The field is an array	If the field is an array, the field name appears twice in the new field list. Check any field of array type. The root field has array type, but the same name field normally has the real type (for example, VARCHAR(60)). Use the first entry to determine the structure where you should add the array. The Structure field in the second entry reflects both the structure for the array (unless it uses the descriptor structure) and the name of the array itself.
The field is part of an array of structures	Check the kmcategory dbdict permission field, which is a structured array field.

Process the NVARCHAR SQL type

Although the upgrade version uses the VARCHAR SQL type for fields, SQL Compare and Upgrade Utility retain the use of the NVARCHAR SQL type after the upgrade. For example, if the SQL type of a field is NVARCHAR(60) in the current version and the SQL type of this field in the upgrade version should be VARCHAR(60), the SQL type of this field will remain NVARCHAR(60) in the upgraded version.

If the current and updated versions of a field have different length SQL types, SQL Compare and Upgrade Utility use the longer length. For example, if the field SQL Type is NVARCHAR(60) in the current version and if the SQL Type of this field in the upgrade version is VARCHAR(100), the SQL Type of this field will be NVARCHAR(100) in the upgraded version.

If you want to tailor the SQL type of a field from VARCHAR to NVARCHAR and retain the same length, be sure to perform tailoring from the database instead of from the Service Manager database dictionary. Otherwise, the tailoring will be ignored by Service Manager.