Exercise 1.6: Exporting and deploying the stored procedure

Deploying a stored procedure to a database is a two step process. After you finish creating a stored procedure, you can export it to a script. You can then run the export script to deploy the stored procedure to a local or remote database. You can choose to save the script in a project's Scripts folder, or you can export it to the file system. In this tutorial you will export the script to the SPSimple project's Scripts folder.

In this tutorial, you already built the stored procedure you created, so the stored procedure is already stored on the database. In a more typical scenario, you might build the stored procedure on a local test database, and then deploy it to a production database. For the purposes of the tutorial, you will create a DROP PROCEDURE statement in your export script, so that the version that you built earlier will be dropped before the CREATE PROCEDURE statement in the export script runs.

Exporting the stored procedure to a script

  1. Right-click the Stored Procedures folder under the SPSimple project, and select Export. The Export Routines wizard opens.
  2. On the Selection page, click the SPEMPLOYEE stored procedure, and click Next.
  3. On the Target and Options page, select Project Scripts Folder.
  4. Give your script a name by typing spemployee_export in the File name field.
  5. Select Include DROP statement. This option generates the DROP PROCEDURE statement in the export script. This will drop the version of the stored procedure that you built in Exercise 1.3.
  6. Click Next to view your settings on the Summary page, then click Finish.

    View the progress of the export in the DB Output view. When the export action has completed, you can see the script that was created in the SPSimple project's Scripts\Data folder. In this case the script is an SQL file. In some cases, depending on the type of stored procedure you are exporting or the target database, the script might be an XML file.

Deploying the stored procedure to the SAMPLE database

Now you can run the export script to deploy the stored procedure to the database. In this case, to simplify the tutorial scenario, we are deploying the stored procedure back to the SAMPLE database. However, you could also deploy it to another database, as long as the database version is compatible with the database that you used to create the stored procedure. For example, you can deploy from a DB2(R) UDB for Linux, UNIX, and Windows Version 8.2 database to a DB2 UDB for Linux, UNIX, and Windows Version 8.1 database, but you cannot deploy to a DB2 UDB for z/OS database.

To deploy the stored procedure to the SAMPLE database:

  1. Right-click sp_employee.sql file that you created in the SPSimple project's Scripts\Data folder, and select Deploy. The Run Script wizard opens.

    Note: The wizard that opens when you deploy a routine varies depending on the language of the routine and the target database.

  2. On the first page, keep the defaults, and click Next.

    Because you created a DROP PROCEDURE statement for your stored procedure, you can see the DROP PROCEDURE statement in the list of statements to run. If you decided at this point that you didn't want to run the DROP PROCEDURE statement, you could clear the check box next to that statement. In this case you should keep it checked so that the earlier version of the stored procedure that you built on the database will be dropped.

  3. On the Run Script Options page, click Prompt prior to committing changes.

    When you select this option, you are always prompted to either commit or roll back changes. The default option (Commit changes only upon success) prompts you to commit or roll back the actions that have completed so far only if an error occurs.

  4. On the Database Connection page, select the Use existing connection check box, and select the DB2SP connection that you created in Exercise 1.2.
  5. Click Finish.

    The Confirm export results window opens, where you can see the DROP PROCEDURE and CREATE PROCEDURE statements in the Task column. You can see whether the statements ran successfully in the Message column.

  6. Click Commit changes to commit the changes to the database.

Finish your tutorial by reviewing the materials in the Summary.

Terms of use | Feedback
(C) Copyright IBM Corporation 2000, 2005. All Rights Reserved.