This article describes the steps required to setup a sample Oracle Rdb database that may be used to house the Java^TM PetStore tables.

1. Create an emtpy Rdb Database
2. Create PetStore User
3. Set up SQL*Net Access
4. Verify the Listener

Assumptions

Throughout this document, references are made to various computer systems, directories, etc. Most of the values used are for documentation purposes only and MUST be changed to fit your environment. These environment-dependent values are bolded throughout this article.

Specifically, the following assumptions are made:

• An OpenVMS system will be used to house the Oracle Rdb 7.1 repository. MYAXP is used as the system name.

• Oracle Rdb 7.1 and Oracle SQL/Services 7.1.5 have been installed.

• The device name MYDISK is used to hold the various scripts and database files.

• The VMS and database username used is ESTOREUSER with a password of ESTORE.

• The name PETRDB is used for the database SID and PETSTORE.RDB is used for the database filename.

• The OCI Listener has been defined and is assigned to port 1527. NOTE: This port number may likely be different on your system! If so, use the port number already set up for your system.

• The VMS and database username used is ESTOREUSER with a password of ESTORE.

• Various symbol and Logical Definitions

     $ SQL == $SQL$
     $ SQLSRV == "run/nodebug sys$system:sqlsrv_manage71.exe"
  

     $ DEFINE SQL$DATABASE MYDISK:[ESTOREUSER.DATA]PETSTORE.RDB
  

Step 1: Create an empty Rdb Database

1. Using the SQL CREATE DATABASE command, create an empty database to house the tables used by the Java^TM PetStore application.

       $ SHOW DEFAULT
        MYDISK:[ESTOREUSER]
       $ CREATE/DIRECTORY [.DATA]
       $ SET DEFAULT [.DATA]
       $ SHOW DEFAULT
        MYDISK:[ESTOREUSER.DATA]
   
       $ SQL
       SQL> create database filename petstore ;
   

Step 2: Create PetStore User

The username used by the Java^TM PetStore application must be created at the VMS level. Later, this username will be established within the database.

1. Create VMS user ESTOREUSER with password ESTORE.

Step 3: Set up SQL*Net Access

1. Run the SQL*Net for Rdb scripts:

       $ SQL
       SQL> @sys$library:SQL_FUNCTIONS71.SQL
       SQL> commit work ;
       SQL> @sys$library:RDB_NATCONN_PREPARE71.SQL
       SQL> commit work ;
       SQL> @sys$library:RDB_NATCONN_DBMSOUTPUT_PREPARE.SQL
       SQL> commit work ;
       SQL> @sys$library:RDB_NATCONN_DBMSAPPL_PREPARE.SQL
       SQL> commit work ;
   

2. Add the ESTOREUSER user to the database

       $ @sys$library:rdb_natconn adduser
       Database: MYDISK:[ESTOREUSER.data]petstore.rdb
       Username: ESTOREUSER
       Password: ESTORE
       Username:  <ctrl-z>
       $
   

3. Create a script (start_services.sqs) containing the following lines defining the SQL/Services for the PETRDB database.

       set verify on;
       connect server ;
   
       shutdown service PETRDB ;
       drop service PETRDB ;
       create service PETRDB 
           sql version 7.1 
           protocol OCI 
           attach 'filename MYDISK:[ESTOREUSER.data]petstore.rdb'
           autostart on 
           application_transaction_usage concurrent
           idle_user_timeout 300
           idle_executor_timeout 300
           min_executors 0
           max_executors 20
           sql_init_file 'MYDISK:[ESTOREUSER]sql_init.sql'
           owner 'ESTOREUSER'
           process_init 'MYDISK:[ESTOREUSER]proc_init.com';
       shutdown dispatcher oci_disp ;
       restart server ;
       connect server ;
   
       start dispatcher oci_disp ;
       start service PETRDB;
       show service PETRDB full ;
       show dispatcher oci_disp full ;
       exit
   

   where sql_init.sql contains:

       alter session log full ;
       alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'  ;
   

   and where proc_init.com contains:

       $ @sys$library:sql$setver 71
   

4. Verify that OCI_LISTENER has been defined in the listener.ora file on MYAXP. If Oracle has been installed on MYAXP, the listener.ora file resides in ORA_ROOT:[NETWORK.ADMIN] otherwise it is located in SYS$COMMON:[SQLSRV71.SQLNET.NETWORK.ADMIN].

   The listener.ora file needs to contain a single entry for the OCI_LISTENER similar to:

       OCI_LISTENER =
         (ADDRESS_LIST =
            (ADDRESS = 
               (COMMUNITY = TCP_COM.world)
               (PROTOCOL = TCP)
               (HOST = MYAXP)
               (PORT = 1527)
             )
         )
   

5. Start the new service and restart the OCI dispatcher using the script created above.

       $ sqlsrv
       SQLSRV> @start_services.sqs ;
       SQLSRV> exit
   

Step 4: Verify the Listener

The listener can be verified by any PC or other system where Oracle SQL*Net has been installed and the tnsping (or tnsping80) Oracle utility is available or by using SQL*Plus.

• Using tnsping...

  From a command window, use the following command:

      C:\> tnsping "(ADDRESS=(PROTOCOL=tcp)(HOST=MYAXP)(PORT=1527))"
  

• Using SQL*Plus...

  First, add the following entry to the local tnsnames.ora file using any text editor.

      PETRDB =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = tcp)(HOST = MYAXP)(PORT = 1527))
          (CONNECT_DATA = (SID = PETRDB))
        )
  

  Then using SQL*Plus:

      > sqlplus ESTOREUSER/ESTORE@PETRDB
  
      SQL*Plus: Release 8.1.7.0.0 - Production on Sun Dec 30 11:09:53 2001
  
      (c) Copyright 2000 Oracle Corporation.  All rights reserved.
  
  
      Connected to:
      Oracle Rdb OCI Server Release 7.1.0.0.1 - Development, Level 1.7
      Oracle Rdb SQL Release 7.1.0.0.0 - Production
  
      SQL>