C Generating and Running Scripts to Drop and Create Indexes

This appendix provides instructions for generating and running a script to drop indexes and a script to create indexes during the upgrade to release 7.9.6.2.

This appendix contains the following topics:

• Section C.1, "Generating Scripts to Drop and Create Indexes"

• Section C.2, "Running the Script to Drop All Indexes"

• Section C.3, "Running the Scripts to Create ETL and Query Indexes"

C.1 Generating Scripts to Drop and Create Indexes

This section provides instructions for generating a script to drop indexes and scripts to create ETL indexes and query indexes. This procedure is applicable when you are upgrading to release 7.9.6.2.

To generate a script to drop indexes and a script to create indexes

1. Go to the folder OracleBI\dwrep\Upgrade\CTLFiles.

2. Open the UPGRADE_7961_to_7962_<source system>.txt file.This file contains a list of data warehouse tables that were upgraded for this release.

3. Open a text editor, and copy and paste the contents of UPGRADE_7961_to_7962_<source system>.txt into a new file.

   Note that table names are separated by a comma (,).

4. Find and replace the comma (,) with OR.

   Make sure there is only one space before and after OR. The format of the text string should appear as follows:

   table_1 OR table_2 OR table_3 OR table_4

   For example:

   W_POSITION_D OR W_PURCH_COST_F OR W_EXPENSE_F OR W_PURCH_CYCLE_LINE_A OR W_ORA_GL_LINKAGE_INFO_REVN_TMP OR W_POSITION_DH

5. Open the DAC repository for the release from which you are upgrading. For example, if you are upgrading from release 7.9.6.1 to 7.9.6.2, you would open the release 7.9.6.1 DAC repository

6. From the DAC toolbar, select the appropriate source system container from the drop-down list.

7. Click Design, and then click the Indices tab.

8. In the top pane toolbar, click Query.

   The top pane window now displays Query mode.

9. Copy the text string from the text file you created in step 5 into the Table Name field.

10. Click Go.

11. In the list of query results, right-click, and then select Generate Index Scripts.

12. Select "All records in the list."

13. In the Generate Index Scripts dialog box, do one of the following:

    • To generate index scripts based on a physical data source connection that you previously set up, select the "Use source information" check box, and then select the appropriate physical data source connection from the drop-down list.

    • To generate index scripts based on the data warehouse database type, deselect the "Use source information" check box, and then select the appropriate database type.

      If the database type is Oracle, enter a table owner, and optionally enter a tablespace in which the indexes will be dropped and created.

      If the database type is SQL Server, DB2 or Teradata, enter a table owner.

14. Click OK.

    A message box states whether the scripts were successfully generated. The following scripts are saved in the folder \bifoundation\dac\log\scripts:

    • dropAllIndexes.sql

    • createETLIndexes.sql

    • createQueryIndexes.sql

C.2 Running the Script to Drop All Indexes

This section provides instructions for running the dropAllIndexes.sql script to drop all indexes during the upgrade to release 7.9.6.2. Before you perform this procedure, you must first generate the script, as described in Section C.1, "Generating Scripts to Drop and Create Indexes."

To run the script to drop all indexes

1. Open the SQL client for your database type.

2. Navigate to the folder \bifoundation\dac\log\scripts.

3. Open the dropAllIndexes.sql file, and copy the contents into the SQL client.

4. Execute the script.

C.3 Running the Scripts to Create ETL and Query Indexes

This section provides instructions for running the createETLIndexes.sql and createQueryIndexes.sql scripts to create indexes during the upgrade to release 7.9.6.2. Before you perform this procedure, you must first generate the scripts, as described in Section C.1, "Generating Scripts to Drop and Create Indexes."

To run the script to create ETL and Query indexes

1. Open the SQL client for your database type.

2. Navigate to the folder \bifoundation\dac\log\scripts.

3. Open either the createETLIndexes.sql script or the createQueryIndexes.sql script, depending on the upgrade step you are on.

   Note: You must run the createETLIndexes.sql script before you run the Informatica workflows and the createQueryindexes.script after you run the Informatica workflows.

4. Copy the contents of the SQL script into the SQL client.

5. Execute the script.