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:
- Export the stored procedure to a script.
- Right-click the Stored Procedures folder
under the SPDevelopment project, and select Export. The
Export Routines wizard opens.
- On the Selection page, click the SPEmployee stored procedure,
and click Next.
- On the Target and Options page, give your script a name by typing spemployee_export in
the File name field.
- In the Directory field, select a location
on the file system for the script.
- 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.
- 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.
- 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.
- Open a DB2 command window.
- Run the following command: db2 CONNECT TO SAMPLE.
- Run the following command: db2 -td@ -vf location\spemployee_export.sql.
- 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.