Upgrade Guide for DB2 UDB for z/OS > Upgrading a Development Environment Database from the Midtier >

Executing SQL Files After the First Pause

Upgrades: All upgrades.

Environments: Development (midtier-centric) environment.

This topic is part of an upgrade process. See How to Perform the Upgrade.

After you launch the Siebel Upgrade Wizard on the mid-tier to start the upgrade process, it generates PRET and drop files and then stops at the first pause. Before re-starting the Upgrade Wizard, execute the files that have been generated to prepare the database tables for upgrading.

Execute the files in the following order from the DB2 command window:

• Dropifdb.sql. Drops EIM/IF tables.
• Drop_view.sql. This file drops all views related to the schema.
• Droptrig.sql. This file drops all triggers related to the schema. This file may be empty.
• PRET.sql. This static file (consisting of five update statements) is run on the source tables. It performs updates to the tables, such as adding columns and revising date formats, so that unloads from the source tables can load, compatible with the target tables.

  This affects source tables with columns that changed from NULLABLE to NOT NULL (not accepting null values).

• Pret_sia.sql. This file is only generated for upgrades of Siebel Industry Applications (SIA). It performs updates to SIA-specific tables.

To execute each SQL file

1. From the DB2 command window, go to the output directory that you specified (this is where the SQL files were generated) and type:

   db2 connect to DATA_SOURCE user DATABASE_USER_NAME using PASSWORD

   db2 set current sqlid='SCHEMA_QUALIFIER'

   The following message appears if the command is successful:

   DB20000I The SQL command completed successfully.

2. Open the SQL file in the specified output directory and check the SQL delimiter. This can be either ";" or "/".
3. Execute each SQL file, in the order in which they are listed above, by entering the command that is appropriate for the SQL delimiter.
   • If the delimiter is ";" then the command to execute the sql file is:

   Db2 -stvf SQL_FILENAME.sql -z LOGFILENAME.log

   Where:

   SQL_FILENAME.sql = name of the SQL file

   LOGFILENAME.log = name of the log file in which the results of this command are stored. You can specify any name.

   For example, to execute drop_view.sql the command is:

   Db2 -stvf drop_view.sql -z drop_view.log

   • If the delimiter is "/" (or any character other than " ; "), then the command to execute the sql file is:

   Db2 -td/ -svf SQL_FILENAME.sql -z LOG_FILENAME.log

   Where:

   SQL_FILENAME.sql = the name of the .SQL file

   LOG_FILENAME.log = the name you specify for the log file in which the results of this command are stored (you can specify any name)

   To execute schema.sql the command is:

   Db2 -td/ -svf schema.sql -z schema.log

   NOTE:  The -s option in the -stvf command and the -svf command specifies that the execution of the SQL commands is to stop if an SQL error is returned. You should specify the -s option when running all Siebel upgrade scripts from the mid-tier. If you do not, you will have to restore your database and rerun the upgrade if you encounter errors because of data dependencies in these jobs.

4. Repeat Step 3 for each SQL file.

About Responding to SQL Errors

If you encounter an error when executing the SQL files, check the errors.txt (UNIX) or errors.rtf (Windows) file. If the error is listed in the error file, it is an acceptable error. In this case, delete the statements that have already executed from the SQL file and resubmit it to allow the remaining statements to execute.

If you encounter an error that is not listed in the errors file, it is an unacceptable error. You must correct the condition that caused the error before proceeding any further.

For further information, see Reviewing the Database Upgrade Log Files.