Upgrading SQL procedures

To manually upgrade your SQL procedures to DB2 Version 9.7:

1. Connect to the upgraded database.
2. Run the following query to identify the SQL procedures that you need to re-create:

      SELECT procschema, specificname 
      FROM syscat.procedures
      WHERE language = 'SQL' AND fenced = 'N' AND 
            substr(IMPLEMENTATION, 10,6) = 'pgsjmp'

   Take note of the schema and specific name values returned by this query, because you will need this information to perform subsequent steps.
3. Run the db2look tool to generate a DDL script for all your database objects:

       db2look -d sample -e -o db2look.sql -a

   where sample is the database name, the -e option generates DDL statements for database objects, the -o db2look.sql option indicates the output file that will contain the DDL statements, and the -a option indicates all objects created by all users.

   Edit the db2look.sql file to keep only the DDL statements necessary to create the SQL procedures that you identified in step 2.

4. For each SQL stored procedures that you identified in step 2, use the DROP PROCEDURE statement indicating the schema name and specific name to uniquely identify each procedure:

      DROP SPECIFIC PROCEDURE schema-name.specific-name

   Alternatively, if you have a DDL script that drops and re-creates your SQL procedures, edit it to drop and re-create only the SQL procedures identified in step 2, and run it. Then proceed to step 6.
5. Re-create the SQL procedures identified in step 2 using the CREATE PROCEDURE statement. Alternatively, you can run your own DDL script or the db2look.sql file that you created in step 3.
6. Test your SQL procedures to ensure that they run as expected under DB2 Version 9.7. You can use the Data Studio or the Command Line Processor (CLP) interface to test them. The following example illustrates how to invoke an SQL procedure using the CLP :

      CONNECT TO sample
   
        Database Connection Information
   
       Database server        = DB2/AIX64 9.7.0
       SQL authorization ID   = TESTDB2
       Local database alias   = SAMPLE
   
      CALL schema-name.procedure-name ( [parameter-list] )

7. If you created projects in the Development Center to develop your SQL procedures, upgrade any existing projects to the Data Studio using the upgrade wizard. You can use projects that you created in the Developer Workbench without having to upgrade them.