B Generating and Running Scripts to Drop and Create Indexes

This appendix provides instructions for generating and running scripts to drop and create indexes while upgrading to releases 7.9.6.2 and 7.9.6.3.

This appendix contains the following topics:

Section B.1, "Generating Scripts to Drop and Create Indexes for Upgrading to Release 7.9.6.2"
Section B.2, "Running the Script to Drop All Indexes for Upgrading to Release 7.9.6.2"
Section B.3, "Running the Scripts to Create ETL and Query Indexes for Upgrading to Release 7.9.6.2"
Section B.4, "Generating Scripts to Drop and Create Query Indexes for Upgrading to Release 7.9.6.3"
Section B.5, "Running the Scripts to Drop and Create Query Indexes for Upgrading to Release 7.9.6.3"

B.1 Generating Scripts to Drop and Create Indexes for Upgrading to Release 7.9.6.2

This section provides instructions for generating a script to drop indexes as well as 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

Go to the folder OracleBI\dwrep\Upgrade\CTLFiles.
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.
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 (,).
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
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
From the DAC toolbar, select the appropriate source system container from the drop-down list.
In the Design view, click the Indices tab.
In the top pane toolbar, click Query.

The top pane window now displays Query mode.
Copy the text string from the text file you created in step 5 into the Table Name field.
Click Go.
In the list of query results, right-click, and then select Generate Index Scripts.
Select "All records in the list."
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.
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

B.2 Running the Script to Drop All Indexes for Upgrading to Release 7.9.6.2

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 B.1, "Generating Scripts to Drop and Create Indexes for Upgrading to Release 7.9.6.2."

To run the script to drop all indexes

Open the SQL client for your database type.
Navigate to the folder \bifoundation\dac\log\scripts.
Open the dropAllIndexes.sql file, and copy the contents into the SQL client.
Execute the script.

B.3 Running the Scripts to Create ETL and Query Indexes for Upgrading to Release 7.9.6.2

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 B.1, "Generating Scripts to Drop and Create Indexes for Upgrading to Release 7.9.6.2."

To run the script to create ETL and Query indexes

Open the SQL client for your database type.
Navigate to the folder \bifoundation\dac\log\scripts.
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.
Copy the contents of the SQL script into the SQL client.
Execute the script.

B.4 Generating Scripts to Drop and Create Query Indexes for Upgrading to Release 7.9.6.3

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

To generate a scripts to drop and created query indexes

Go to the folder OracleBI\dwrep\Upgrade\CTLFiles.
Open the UPGRADE_7962_to_7963_<source system>.txt file.

This file contains a list of data warehouse tables that were upgraded for this release.
Open a text editor, and copy and paste the contents of UPGRADE_7962_to_7963_<source system>.txt into a new file.

Note that table names are separated by a comma (,).
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
Open the DAC repository for the release from which you are upgrading. For example, if you are upgrading from release 7.9.6.2 to 7.9.6.3, you would open the release 7.9.6.2 DAC repository
From the DAC toolbar, select the appropriate source system container from the drop-down list.
In the Design view, click the Indices tab.
In the top pane toolbar, click Query.

The top pane window now displays Query mode.
Copy the text string from the text file you created in step 5 into the Table Name field.
Click Go.
In the list of query results, right-click, and then select Generate Index Scripts.
Select "All records in the list."
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.
Click OK.

A message box states whether the scripts were successfully generated. The following scripts are saved in the folder \bifoundation\dac\log\scripts:
- dropQueryIndices.sql
- createQueryIndices.sql
  
  Note: Additional files may be generated; however, you will not need to run them.

B.5 Running the Scripts to Drop and Create Query Indexes for Upgrading to Release 7.9.6.3

This section provides instructions for running the dropQueryIndices.sql and createQueryIndices.sql scripts when upgrading to release 7.9.6.3. Before you perform this procedure, you must first generate the scripts, as described in Section B.4, "Generating Scripts to Drop and Create Query Indexes for Upgrading to Release 7.9.6.3."

To run the script to drop or create query indexes

Open the SQL client for your database type.
Navigate to the folder \bifoundation\dac\log\scripts.
Open either the dropQueryIndices.sql script or the createQueryIndices.sql script, depending on the upgrade step you are on.
Copy the contents of the SQL script into the SQL client.
Execute the script.