Running the SQL update scripts

When upgrading to an 8.3.x service pack (later than 8.3.00), after upgrading the Application Server you must run a set of SQL Update scripts to upgrade the BMC Server Automation database to the service pack level compatible with the Application Server.

Before you begin

Ensure that the computer on which you run the SQL Update scripts fulfills the following requirements:

• Has access to the BMC Server Automation database server
• Has SQL tools installed, such as SQL*Plus (in the case of Oracle) or sqlcmd (in the case of Microsoft SQL Server)
  You can install the sqlcmd utility from the SQL Server install media, or as part of the SQL Server 2008 R2 feature pack or the SQL Server 2012 feature pack. You can find the utility from the full SQL server install under Shared Features > Management Tools - Basic.
  Click here to download a standalone installer from the Microsoft website. 

In addition, ensure that the user that you created for the BMC Server Automation database owner has permissions to create tables and indexes in the database.

To run the SQL update scripts for an Oracle database

1. Obtain the zipped package of SQL Update scripts from the BMC Electronic Product Distribution (EPD) website, as described in Downloading the installation files.
   The EPD link for this package is BSA 8.3... SQL Upgrade Script Package. The downloaded package is named 83<SP level>-SQL_Update_Scripts.zip file, where <SP level> can be one of the following values: SP3, SP2-P1, SP2, or SP1. 
2. Extract the contents of the zipped package of SQL Update scripts to a temporary local directory.
3. Navigate to the directory that contains the update scripts  (temporaryDirectory/8.3.0x/Remediation/oracle).
4. Log on to SQL*Plus as the BMC Server Automation database owner (as provided to blasadmin during BMC Server Automation schema creation).

5. Run the script, as follows:

   SQL>start install_hotfix_update_oracle_script.sql
   
6. To run the script that resolves defect QM001784134 and creates various indexes to improve database performance (especially during database cleanup), perform the following steps:
   1. Through SQL*Plus (or any other Integrated Development Environment) tool, navigate to the QM001784134 directory (temporaryDirectory/8.3.0x/Remediation/oracle/scripts/QM001784134).

   2. Run the following series of SQL commands:

      SQL> SET trim ON;
      SQL> SET head OFF;
      SQL> SET feedback ON;
      SQL> SET linesize 256;
      SQL> SET pagesize 1024;
      SQL> SET SERVEROUTPUT ON SIZE 100000;
      SQL> start QM001784134_ignore_index_input.sql
      SQL> start QM001784134.sql

To run the SQL update scripts for a Microsoft SQL Server database

1. Obtain the zipped package of SQL Update scripts from the BMC Electronic Product Distribution (EPD) website, as described in Downloading the installation files.
   This zipped package is named 83<SP level>-SQL_Update_Scripts.zip file, where <SP level> can be one of the following values: SP3, SP2-P1, SP2, or SP1. 
2. Extract the contents of the zipped package of SQL Update scripts to a temporary local directory.
3. Navigate to the directory that contains the upgrade scripts (temporaryDirectory/8.3.0x/Remediation/sqlserver).

4. Execute the install_hotfix_update_sqlserver_script.bat file with the following input parameters:
   install_hotfix_update_sqlserver_script.bat <DBServerName> <DBName> <Username> <Password>

   install_hotfix_update_sqlserver_script.bat testserver test_db BLuser BMC123

   After upgrading the SQL update scripts from version 3.1.01 to 4.1, the scripts do not execute when the password has special characters.

   BMC recommends entering the password in double quotes while executing this script on command prompt.

5. To run the script that resolves defect QM001784134 and creates various indexes to improve database performance (especially during database cleanup), perform the following steps:

   1. Navigate to the QM001784134 directory (temporaryDirectory/8.3.0x/Remediation/sqlserver/scripts/QM001784134).
   2. Using sqlcmd (or any other SQL tool), execute the QM001784134_ignore_index_input.sql script with the following input parameters:
      sqlcmd -S <DBServerName> -U <Username> -d <DBName> -i QM001784134_ignore_index_input.sql -o QM001784134_ignore_index.log
   3. To remove corrupted records, execute the QM001784134.sql script with the following input parameters:
      sqlcmd -S <DBServerName> -U <Username> -d <DBName> -i QM001784134.sql -o QM001784134.log

   Note

   The QM001784134.log file might contain the following warning message: "Caution: Changing any part of an object name could break scripts and stored procedures."  You can ignore this warning.