3 Database Installation Tasks

There are several tasks that must be performed for 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 (for example, the RIB Hospital Tables, CLOB API libraries, and Oracle Objects).

Ensure that these have been installed appropriately, per the individual applications.
Ensure that the TAFR Hospital user and objects exist.
Ensure that the RIB user has appropriate access and permissions.

RIB and Multibyte Deployments

If RIB is deployed in an environment where multibyte characters are used in the message data, there are considerations that must be understood. Improper database setup can lead to error messages indicating the inability to insert values that are too long.

Note:

See the section, ”Pre-Implementation Considerations for Multibyte Deployments,” in the Oracle Retail Integration Bus Implementation Guide.

These considerations are beyond the scope of the RIB documentation and should be discussed with the site Database Administration team prior to installation.

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. Externalizing the RIB Hospital tables from the application database schema is supported.

There are two options:

rib-<app> can use the respective application database schema to host the RIB hospital tables.
rib-<app> can have a separate database or a separate schema to host the RIB hospital tables.

Note:

The RIB Hospital schema must not be shared across retail applications. Each rib-<app> should have its own RIB hospital tables in both of the options listed above.

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 database schema.

Note:

See Appendix C, ”RIB Installation Checklists.”

The database schema for all retail applications 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 the rib-private-kernel-database-library-<version>.jar file. The rib-private-kernel-database-library-<version>.jar can be found in the rib-home directory structure (/Rib1502ForAll15xxApps/rib-home/integration-lib/internal-build/
rib).

Note:

See the section, ”RIB App Builder rib-home,” in the Oracle Retail Integration Bus Operations Guide.

Because 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 ”RIB Software Life Cycle” in the Oracle Retail Integration Bus Implementation Guide.

Verify that Database XA Resources are 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 12c (12.x).

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, ORFM, and RWMS.

There are two ways through which PL/SQL applications exchange payload data with RIB:

Oracle Objects payloads
CLOB xml parsing and building library

RMS and ORFM use both mechanisms, whereas RWMS uses only Oracle Objects to communicate with RIB.

Verify that the RMS, ORFM, and RWMS database schema have 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.

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

Note:

See the Oracle Retail Integration Bus Operations Guide. InstallAndCompileAllRibOracleObjects.sql script is packaged as a part of rib-public-payload-database-xml-library.zip and is available under <RIB-HOME>/application-assembly-home/rib-func-artifacts/.

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.

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

Note:

See the Oracle Retail Integration Bus Operations Guide. 1_CLOB_CREATE_OBJECTS.SQL script is packaged as a part of rib-public-payload-database-xml-library.zip and is available under <RIB-HOME>/application-assembly-home/rib-func-artifacts/.

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= 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 WebLogic server where rib-func-artifact.war will be deployed.

Create RIB TAFR RIB Hospital

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

Create a database user for the rib application rib-tafr.
Make sure that the TAFR Hospital user has the proper database permission.

Example TAFR User Create SQL:

CREATE USER <tafr hosp user>

IDENTIFIED BY <tafr hosp password>

DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

GRANT "CONNECT" TO <tafr hosp user>;

GRANT "RESOURCE" TO <tafr hosp user>;

ALTER USER <tafr hosp user>

QUOTA UNLIMITED ON USERS;

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 the rib-private-kernel-database-library-<version>.jar file. The rib-private-kernel-database-library-<version>.jar can be found in the rib-home/integration-lib/internal-build/rib/ directory structure. Extract the script and provide it to the Database Administrator (DBA) to create the required database objects.

Prepare Oracle AQ JMS Provider

Oracle Streams AQ is the JMS provider that RIB uses for asynchronous communication. It requires Oracle Database 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 installation. These are covered in other RIB documents.

RIB and AQ JMS Database Processes

The RIB's use of the AQ JMS should be understood, and the Oracle Database instance that is configured as the AQ JMS must be configured to support the number of server side user processes needed for the RIB adapters that will be installed and configured in each deployment environment. The number of JMS AQ processes depends on the RIB configuration.

Note:

See the section, ”Pre-Implementation Considerations - JMS Server Considerations,” in the Oracle Retail Integration Bus Implementation Guide.

Note:

See the section, ”Deployment Architectures,” in the Oracle Retail Integration Bus Implementation Guide. See also the ”JMS Provider Management” and ”The RIB on AQ JMS” sections in the Oracle Retail Integration Bus Operations Guide.

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 <rib aq user> IDENTIFIED BY <rib aq password>

DEFAULT TABLESPACE "RETAIL_DATA"

TEMPORARY TABLESPACE "TEMP";

GRANT "CONNECT" TO <rib aq user>;

GRANT "RESOURCE" TO <rib aq user>;

GRANT CREATE SESSION TO <rib aq user>;

GRANT EXECUTE ON "SYS"."DBMS_AQ" TO <rib aq user>;

GRANT EXECUTE ON "SYS"."DBMS_AQADM" TO <rib aq user>;

GRANT EXECUTE ON "SYS"."DBMS_AQIN" TO <rib aq user>;

GRANT EXECUTE ON "SYS"."DBMS_AQJMS" TO <rib aq user>;

GRANT "AQ_ADMINISTRATOR_ROLE" TO <rib aq user>;

ALTER USER <rib aq user>

QUOTA UNLIMITED ON RETAIL_DATA;

Note: