3 Database Installation Tasks

There are several tasks that need to be performed for the RIB and verified in the participating applications.

Oracle Database Schemas

Each Oracle Retail Application has an associated set of RIB Artifacts that must be installed as part of the RIB integration, e.g. the RIB Hospital Tables, CLOB API libraries, and Oracle Objects.

• Insure that these have been installed appropriately per the individual applications

• Insure that the TAFR Hospital user and objects exist

• Insure that the RIB user has appropriate access and permissions

Verify That Correct RIB Hospital Database Objects are Installed in the Retail Application's Schema

Every rib-<app> application needs a database schema that contains the RIB Hospital tables. In this release, rib-<app> uses the respective retail application database schema for its location of the RIB Hospital tables. These RIB Hospital tables are not installed as part of the RIB installation, but they are installed as part of the Retail applications database schema installation. Verify that the four RIB Hospital tables are already installed in the respective retail applications database schema.

All retail application's database schema must have the database objects defined in the RIB delivered kernel SQL script called 1_KERNEL_CREATE_OBJECTS.SQL.

Note: The 1_KERNEL_CREATE_OBJECTS.SQL script is available in rib-private-kernel-database-library.zip file. The rib-private-kernel-database-library.zip can be found in the rib-home directory structure.

Note: See the RIB Operations Guide - RIB App Builder rib-home.

Since these database objects should have already been installed as part of the retail application's installation process, at this point just verify that the four hospital tables and the sequence exist in each application's database schema. Make sure that they have the correct columns to match this release of the RIB.

It is strongly recommended that all applications have a separate RIB Hospital and that they be logically and operationally associated with that application.

Note: See the RIB Implementation Guide - RIB Software Life Cycle.

Verify Database Has XA Configured for RIB

RIB uses two phase commit transaction protocol (XA) to maintain consistency between the RIB Hospital database, application database and the JMS server. The Oracle database XA resources must be configured in order to participate in XA transaction. Check to see that the XA scripts have been run on the database to make it XA transaction aware. The initXA.sql script needs to be run before XA transactions will work. These are usually installed by default in 10gR2. Use the grants shown below to enable XA transaction for the RIB database user.

grant select on v$xatrans$ to public;grant select on pending_trans$ to public;grant select on dba_2pc_pending to public; grant select on dba_pending_transactions to public; grant execute on dbms_system to public;

Verify that Correct RIB Functional Artifacts Database Objects Are Installed in PL/SQL Applications Database Schema

This section applies to PL/SQL application only, RMS and RWMS.

There are two ways to for PL/SQL applications to exchange payload data with RIB:

• Oracle Objects payloads

• CLOB xml parsing and building library

RMS uses both mechanism where as RWMS uses only Oracle Objects to communicate with RIB.

1. Verify that the RMS and RWMS database schema has the RIB delivered Oracle Objects installed. Oracle Objects are not installed as part of RIB installation; they are installed as part of the retail application database schema installation.

2. Verify that the PL/SQL retail application's database schema already have the database objects defined equivalent to the ones defined in the RIB delivered script called InstallAndCompileAllRibOracleObjects.sql.

   
   

   Note: See the RIB Operations Guide.

   
   

3. Verify that RMS (not RWMS) database schema has the RIB CLOB XML parsing and building library code installed. These CLOB XML libraries are not installed as part of RIB installation; they are installed as part of the retail application database schema installation.

4. Verify that the RMS retail application's database schema has all the database objects defined equivalent to the once defined in the RIB delivered script called 1_CLOB_CREATE_OBJECTS.SQL.

   
   

   Note: See the RIB Operations Guide.

   
   

5. Update the RIB functional artifact URL in the RMS table RIB_OPTIONS to point to the location where rib-func-artifact.war will be deployed.

   XML_SCHEMA_BASE_URL_DEFAULT = http://<hostname>:<port>/rib-func-artifact/payload/xsd

   Where:

   • hostname is the host name where rib-func-artifact.war will be deployed.

   • port is the http port of the OAS server where rib-func-artifact.war will be deployed.

Create RIB TAFR RIB Hospital

In RIB 13, there is a separate RIB Hospital for the rib-tafr application.

1. Created a database user for the rib application rib-tafr.

2. Make sure that the TAFR Hospital user has the proper database permission.

Example TAFR User Create SQL:

CREATE USER "TAFRHOSP"

IDENTIFIED BY "TAFRHOSP"

DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

GRANT "CONNECT" TO " TAFRHOSP ";

GRANT "RESOURCE" TO " TAFRHOSP ";

The rib-tafr application's database user must have the RIB Hospital tables. To create the RIB Hospital tables, run the 1_KERNEL_CREATE_OBJECTS.SQL script.

Note: The 1_KERNEL_CREATE_OBJECTS.SQL script is available in rib-private-kernel-database-library.zip file. The rib-private-kernel-database-library.zip can be found in the rib-home directory structure.

Prepare Oracle AQ JMS Provider

Oracle Streams AQ is the JMS provider that RIB uses for a synchronous communication. AQ requires Oracle RDBMS Enterprise Edition.

It is strongly recommended that the Oracle Database instance configured as the JMS provider is not shared with any other applications and not be on the same host (physical or logical) with any other applications. The steps included here are those needed to prepare for the installation, there are many architectural issues and operational parameters that must be considered before the install. These are covered in other RIB documents.

Note: See RIB Implementation Guide - Deployment Architectures. See RIB Operations Guide - JMS Provider Management, The RIB on AQ JMS

Create the RIB AQ JMS user with the appropriate access and permissions to the Oracle Streams AQ packages. This user must have at least the following database permissions:

• CONNECT

• RESOURCE

• CREATE SESSION

• EXECUTE ON SYS.DBMS_AQ

• EXECUTE ON SYS.DBMS_AQADM

• EXECUTE ON SYS.DBMS_AQIN

• EXECUTE ON SYS.DBMS_AQJMS

Example SQL:

CREATE USER "RIBAQ" IDENTIFIED BY "RIBAQ"

DEFAULT TABLESPACE "AQJMS"

TEMPORARY TABLESPACE "TEMP";

GRANT "CONNECT" TO "RIBAQ";

GRANT "RESOURCE" TO "RIBAQ";

GRANT CREATE SESSION TO "RIBAQ";

GRANT EXECUTE ON "SYS"."DBMS_AQ" TO "RIBAQ";

GRANT EXECUTE ON "SYS"."DBMS_AQADM" TO "RIBAQ";

GRANT EXECUTE ON "SYS"."DBMS_AQIN" TO "RIBAQ";

GRANT EXECUTE ON "SYS"."DBMS_AQJMS" TO "RIBAQ";

GRANT "AQ_ADMINISTRATOR_ROLE" TO "RIBAQ";

Note: See Also: Oracle® Database Administrator's Guide 10g Release 2 (10.2) Oracle® Streams Advance Queuing User's Guide and Reference 10g Release 2 (10.2)