< Previous | Next >

Exporting and deploying the stored procedure from the file system

In an earlier exercise, you deployed a stored procedure from the workbench. You can also deploy a stored procedure from an exported script on the file system. To deploy a stored procedure from the file system, two steps are required. After you finish creating a stored procedure, export it to a script on the file system. You then run the exported script from the file system independent from the product and then deploy the stored procedure to a local database or a remote server.

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

To export and deploy the stored procedure from the file system:

  1. Export the stored procedure to a script.
    1. Right-click the Stored Procedures folder under the SPDevelopment 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, give your script a name by typing spemployee_export in the File name field.
    4. In the Directory field, select a location on the file system for the script.
    5. Select Include DROP statement. This option generates the DROP PROCEDURE statement in the export script. This action drops the version of the stored procedure that you deployed earlier.
    6. Click Next to view your settings on the Summary page, and click Finish.
    View the progress of the export in the Data Output view. When the export action completes, you can see the script that was created in the file system. In this case, the script is an SQL file. In some cases, depending on the type of stored procedure that you are exporting or the target database, the script might be an XML file.
  2. Deploy the stored procedure from the file system 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, you will deploy 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 for Linux, UNIX, and Windows database to another DB2 for Linux, UNIX, and Windows database, but you cannot deploy the same stored procedure to a DB2 UDB for z/OS database.
    1. Open a DB2 command window.
    2. Run the following command: db2 CONNECT TO SAMPLE.
    3. Run the following command: db2 -td@ -vf location\spemployee_export.sql.
    4. Run the following command: db2 DISCONNECT SAMPLE.
The stored procedure is deployed to the database.
If this stored procedure were a Java stored procedure, you would deploy it by following the instructions in DeployInstructions.txt, which is saved to the same directory on the file system where you saved the export script.
< Previous | Next >