iqunload -au does not have “UNIQUE CONSTRAINT” index clause [CR 608611]

Customers who have successfully used the iqunload -au utility to migrate their 12.7 database to Sybase IQ 15.0, 15.0 ESD #1, 15.1, or 15.1 ESD #1 might be in a situation where the migrated system tables have incorrect data. This incorrect data can result in the inability to modify the schema or rows within the database. If this happens, the server may return an internal system exception. The server will also permit users to update the primary key values involved in a referential relationship in such a way that would be in RI violation.

This procedure assumes that you migrated your database with Sybase IQ 15.0, 15.0 ESD #1, 15.1, or 15.1 ESD #1. If you plan to migrate a multiplex database, you must apply these steps to the coordinator node.

1. Execute the following against the migrated database using current software:

   create temporary procedure iq_check_system_tables()
   result ( Action char(255) )
   begin
      if exists( select * from  SYS.SYSIDX si 
         join SYS.SYSIQIDX siq on (si.table_id =
   siq.table_id and si.index_id = siq.index_id)
         join SYS.SYSIDXCOL sic on (sic.table_id =
   si.table_id and sic.index_id = si.index_id) 
         join SYS.SYSTAB st on (si.table_id =
   st.table_id)
         where st.server_type = 2 and siq.index_owner =
   'IQ' and si.index_category = 3 and si."unique" = 1)
   then
         select 'Database system tables require 15.1
   ESD 2 ''alter database upgrade''' as Action
      else
         select 'Database system tables check was
   successful.' as Action
      end if
   end;
   call iq_check_system_tables();
   

2. What you do next depends on the output message:

   This message...	Means this...
   Database system tables check was successful.	The migrated database does not contain any invalid system table information. You do not need to take any corrective action. No additional steps are required.
   Database system tables require 15.1 ESD 2 'alter database upgrade'	The migrated database contains invalid entries in the system tables. Please follow the remaining steps outlined here.

3. Download and install Sybase IQ 15.1 ESD #3 and use this release to perform the rest of the steps below.

4. Make a backup copy of the .db and .log file(s) from your database.

5. Start the database with the new ESD software.

6. Connect to the database with Interactive SQL (dbisql) and execute the alter database upgrade SQL command. This will produce the output:

   Database upgrade started
   Creating system views
   Creation of system views completed
   Setting option values
   Setting option values completed 
   creating variable...
   **            System table update required.
   **            System table update completed.
   Creating migration procedures
   Creation of migration procedures completed
   Creating jConnect procedures
   Creation of jConnect procedures completed
   Database upgrade finished
   29 row(s) affected
   Execution time: 68.453 seconds
   

   During the upgrade the system tables will be updated, the two lines prefixed with ** show the status of this update. (The ** characters will not be displayed on the console).

7. Repeat step 2. This should return the message:

   Database system tables check was successful.
   

8. If there were no changes to the data stored in the database, then the above system table update is all that is required and the process is completed.

9. If the data in the database was modified after migration or you wish to run an extra verification check then the stored procedure:

   iq_migration_ri_verification()
   

   This procedure is temporary is and should be run as DBA. To load this procedure, execute this command from Interactive SQL (dbisql):

    call sa_exec_script( 'checkpkfk.sql' )
   

   After you load the script, use this command to execute the procedure:

   call iq_migration_ri_verification() 
   

   This procedure will scan the database for PK/FK relationships, if any are found it will generate a script that will be used to verify the PK/FK relationships. The execution of iq_migration_ri_verification() will be fairly quick as it just scans the schema of the database. The generated verification script may take a while to complete.

   If iq_migration_ri_verification() detects PK/FK relationships, the output looks like this:

   ================================
   IQ PK/FK Referential Information
   ================================
   There are 5 PK/FK relationships detected in this database.
   
   A verification script will be generated to:
        W:/iq-15/unload/127/db/runricheck.sql
   
   This script contains SQL which will perform a join
   on tables where there exists a primary and foreign
   key relationship. As a result, this script may take
   some time to execute.In the event this script detects missing primary key
   values for which there is a foreign key value
   present, a log entry will be produced that contains
   the table name and column names that require
   corrective action.To execute the generated verification script:
   
        call sa_exec_script('W:/iq-15/unload/127/db/runricheck.sql');
   
   Please continue to the next step to run the
   runricheck.sql script. 
   

   Please continue to the next step to run the runricheck.sql script.

   If iq_migration_ri_verification() does not detect any PK/FK relationships, then the output will look like this:

    Database does not contain any PK/FK relationsships.
   	    No verification is required.
   

10. Load and Execute runricheck.sql.

    Follow the instructions provided by iq_migration_ri_verification() to load the runricheck.sql script:

    call sa_exec_script('W:/iq-15/unload/127/db/runricheck.sql');