A Preparing the Production Oracle Server

Specific PL/SQL packages must be present on your production Oracle server before you can compile MIPs on that server. This appendix describes how to run scripts pgmdeploy.sql and pgmundeploy.sql and how to later remove any PL/SQL packages that are not needed on your system. The following topics are included:

• Introduction

• Verifying and Installing PL/SQL Packages

• Removing the PL/SQL Packages

A.1 Introduction

Before you can compile MIPs on a production Oracle server, the following PL/SQL packages must be present on the production Oracle server:

• DBMS_PIPE, DBMS_OUTPUT, and UTL_RAW

  These packages are shipped with each Oracle server and are usually already installed.

• PGM, PGM_BQM, PGM_SUP, and UTL_PG

  These packages are shipped with your Oracle Procedural Gateway for message queuing.  They are installed during the creation process of the Visual Workbench repository.  Do not run deployment script on the Oracle server with an installed Visual Workbench repository.  If the Oracle server used for the repository is different than the Oracle server used in the production environment, then you must install these packages on the production Oracle server.

This section describes how to run:

• pgmdeploy.sql, a deployment script to verify the existence of the required PL/SQL packages and to install some of them if they do not exist on the production Oracle server

• pgmundeploy.sql, a script to remove the PL/SQL packages from a production Oracle server

Note:

If your production Oracle server is Oracle8i or earlier, you need to use pgmdeploy8.sql to install the current release of PG4MQ Visual Workbench deployment packages, and you need to use pgmundeploy8.sql to remove previous release of PG4MQ Visual Workbench deployment packages.

All of the examples in this section are provided with the assumption that you are installing on the current release.

A.2 Verifying and Installing PL/SQL Packages

1. Locate the necessary scripts:

   • pgm.sql

   • pgmbqm.sql

   • pgmdeploy.sql

   • pgmsup.sql

   • pgmundeploy.sql

   • prvtpg.sql

   • utlpg.sql

   These scripts are installed with the gateway, in the directory ORACLE_HOME\pg4mq\admin\deploy, where ORACLE_HOME is the gateway home directory. 

2. If your production Oracle server is on a different computer than the gateway, you need to use a file transfer method, such as ftp, to transfer files in the directory ORACLE_HOME\pg4mq\admin\deploy, where ORACLE_HOME is the gateway home directory on your gateway computer.  On your production Oracle server computer, change directory to the directory containing the deployment scripts you just transferred and skip to step 4.

3. If your production Oracle server is on the same computer as the gateway, then change directory to ORACLE_HOME\pg4mq\admin\deploy, where ORACLE_HOME is the gateway home directory.

4. Run the deployment script by entering:

   $ sqlplus /nolog @pgmdeploy.sql
   

5. At the script prompt: Enter the connect string for the Oracle server... [LOCAL], press Return to use the default of LOCAL.

6. At the script prompt Enter the following required Oracle server password, enter the password of the SYS account.

After the script verifies the SYS account password, it connects to the production Oracle server.  The script verifies and reports on which PL/SQL packages are installed there:

• If any of the Oracle server packages DBMS_OUTPUT, DBMS_PIPE or UTL_RAW are missing, the script stops.  Have your DBA install the missing packages and re-run the deployment script.

• If any of the Oracle packages PGM, PGM_BQM, PGM_SUP, and UTL_PG are missing, the script installs them on the production Oracle server.

A.3 Removing the PL/SQL Packages

You can remove the PL/SQL packages that were installed by the pgmdeploy.sql script if, for example, none of your applications in the production environment uses a MIP.  To remove these packages, perform the following steps:

1. On your production Oracle server computer, change directory to the directory containing the deployment scripts by entering the following command:

   > cd ORACLE_HOME\pg4mq\admin\deploy
   

2. Run the script by entering:

   $ sqlplus /nolog @pgmundeploy.sql
   

3. At the script prompt: Enter the connect string for the Oracle server... [LOCAL], press Enter to use the default of LOCAL.

4. At the script prompt, Enter the required Oracle server passwords, enter the password of the SYS account.

After the script verifies the SYS account password, it connects to the production Oracle server and removes the packages installed by the pgmdeploy.sql script.

After the pgmundeploy.sql script completes successfully, applications on the production Oracle server fail if they attempt to reference any of the MIPs that are compiled there.