Rational Developer for System z


< Previous | Next >

Exporting and deploying the stored procedure from the file system

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:

  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, mark the checkbox next to the SPEMPLOYEE stored procedure, and click Next.
    3. On the Target and Options page of the wizard, enter spemployee_export in the File name field.
    4. 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.
    5. 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.
    6. 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.
  2. 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.

    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, where location is the directory to which you exported the stored procedure.
    4. 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.
< Previous | Next >

Terms of use | Feedback



This information center is powered by Eclipse technology. (http://www.eclipse.org)