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 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 SQL Results view.
When the export action completes, use a file browser to 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.