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.
spemployee_export
in the File name field.
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.
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:
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.
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.
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.
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.
Finish your tutorial by reviewing the materials in the Summary.