In an earlier exercise, you deployed a stored procedure
from the workbench; however, you can also deploy a stored procedure
from an exported script on the file system. As you finish creating
the stored procedure, export it to a script on the file system, run
the exported script from an independent file system, and deploy the
stored procedure to a local database or remote server.
In this tutorial, you will run the exported script on
your local system, and deploy the stored procedure in the local SAMPLE
database. You will also create a DROP PROCEDURE statement in your
export script to drop the version you deployed earlier to the local
SAMPLE database 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, mark the
checkbox next to the SPEMPLOYEE stored procedure, and click Next.
- On the Target and Options page
of the wizard, enter spemployee_export in the File
name field.
- To the right of the Directory text
field, select the BROWSE button, and browse for the particular directory
on your local workstation that you would like to save the exported
stored procedure.
- Mark the checkbox, Include DROP statement. This option generates the DROP PROCEDURE statement in the export
script. This action drops the version of the stored procedure that
may have been deployed earlier.
- Click Next to view your settings
on the Summary page, and click Finish. In the SQL Results , you should
see that the export was successful.
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. By running this export script, you can
deploy the stored procedure back to the SAMPLE database. Alternatively,
instead of deploying the export script back to the original database,
you could also deploy it to another database, as long as that version
is compatible with the database you used to create the stored procedure.
For
example, you can deploy 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 for 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,
where location is the directory to which you exported
the stored procedure.
- Run the following command: db2 DISCONNECT SAMPLE.
The stored procedure is deployed to the database.
An alternative way of deploying a stored procedure would
be to modify, and then run the SQL stored procedure as specified in
the DeployInstructions.txt.