Step 3: Add unique and non-unique indexes

You can add unique and non-unique indexes by running certain commands, and once you have run those commands, you can ensure that none of the index creations failed in the log file.

About this task

Note: If you set the yfs.apply.sql.manually property to Y in the <INSTALL_DIR>/Migration/9.5/buildmigration.properties file, you must manually apply the following DDLs before running the install-transaction-indexes target:
  • <INSTALL_DIR>/Migration/<previous version directory>/database/scripts/<dbtype>/transaction/uniqueindexadds.sql
  • <INSTALL_DIR>/Migration/<previous version directory>/database/scripts/<dbtype>/transaction/indexadds.sql
where <previous version directory> corresponds to the location of the previous version directory and <dbtype> corresponds to the database type (Db2 or Oracle).

This command adds all the unique indexes and nonunique indexes. If nonunique indexes are not to be installed, then the argument -Dnonuniqueindex=Y should not be passed.

Note: Typically, you update the unique transaction indexes by running the install-transaction-indexes target before updating tables by running the upgrade-transaction-tables target. However, if more than one record exists in the YFS_USER table for DISPLAY_USER_ID or ENTERPRISE_CODE or in the YFS_PROPERTY_METADATA table for BASE_PROPERTY_NAME or CATEGORY, you must run the upgrade-transaction-tables target before running the install-transaction-indexes target. Otherwise the install-transaction-indexes target fails because a unique index cannot be created for the respective table.

Nonunique indexes are required for performance improvement. On a large database, nonunique indexes may take a significant amount of time to be installed. You must analyze all the nonunique indexes and verify if all of them are required.

  • When migrating from 9.3, 9.4, or 9.5 to 10.0, run the following command: 
    
${ANT_HOME}/bin/ant -Druntime=<INSTALL_DIR> -Druntime.old=<INSTALL_DIR_OLD>
 -f buildmigration.xml -logfile <logfile>
 -Dtarget=install-transaction-indexes -Dnonuniqueindex=Y migrate

    The *.done files created in the 9.5 status folder for the install-transaction-indexes task are:

    • transaction_ant_installuniqueindexes.xml.done
    • transaction_ant_installnonuniqueindexes.xml.done
      Note: Refer to the logfile to ensure that none of the index creations failed.