Open topic with navigation

TIBCO iProcess® Engine 11.9.1

# Oracle AQ Queue Tables and Queues

Oracle Advanced Queuing (AQ) provides the underlying message queuing system used by the iProcess message queues. Each message queue defined in the `iql_queues` table must be mapped to an AQ queue, which in turn must be stored in an AQ table.

When iProcess Engine is installed, the `init2Kora.sql` script (on UNIX, or on Windows `init2Kora_tok.sql`) creates the default set of AQ tables, queues and object types required by the system (see Default Message Queues and AQ Tables.)

Note: The`init2Kora.sql` script (on UNIX, or `init2Kora_tok.sql` on Windows) also creates a non-persistent Oracle AQ queue called `sw_system_events`. This queue is used to route all iProcess events to the iProcess event manager. (iProcess event management is provided by the Oracle Event Manager on UNIX, and by the iProcess Events COM+ application on Windows.)

If you subsequently decide to add additional message queues, you must use the Oracle `DBMS_AQADM` package to manually create the Oracle AQ queue tables and queues needed by those message queues. The following sections explain how to do this.

Note: For a detailed description of the `DBMS_AQADM` package procedures referred to in this section, see the Oracle9i Supplied PL/SQL Packages and Types Reference guide in your Oracle documentation set.

## SWLOCALMSG Object Type

An AQ queue table must be associated with one particular message type, which must already exist when you create the AQ queue table.

All AQ queue tables used by the iProcess Engine must use the `SWLOCALMSG` object type, which is created when the iProcess Engine is installed.

    CREATE TYPE TABLE_OWNER.SWLOCALMSG AS OBJECT (
    msginfo               VARCHAR2(500),
    node                  varchar2(24),
    contenttype           number(2),
    data                  VARCHAR2(1000)
    );

## AQ Queue Tables

Use the `DBMS_AQADM.CREATE_QUEUE_TABLE` procedure to create an AQ queue table. The following parameters are required.

Column | Description
queue_table | Unique name of this queue table. This name is used in the `queue_phys_descr` column of the iql_queues table.
queue_payload_type | Object type of data stored by this queue table. This value must be `swlocalmsg`.
storage_clause | Tablespace to be used by this queue table. Note: Each queue table can be associated with a different tablespace, which in turn can be associated with a different datafile. Distributing queue tables in this way provides an easy way of reducing I/O contention and optimizing performance. See STORAGE Configuration Macro Values for more information.
sort_list | Sort criteria by which messages are sorted in ascending order and dequeued based on this order.
comment | Description of this queue table.
compatible | Lowest database version that this queue table is compatible with. This value must be `8.1`.

The following example, from the `init2Kora.sql` script, shows the SQL used to create one of the default background Mbox tables that is created when the iProcess Engine is installed.

    DBMS_AQADM.CREATE_QUEUE_TABLE (
    queue_table => 'TABLE_OWNER.bgmboxtable1',
    queue_payload_type => 'TABLE_OWNER.swlocalmsg',
    sort_list => ' priority, enq_time',
    storage_clause => 'tablespace AQTABLESPACE',
    comment => 'default background mbox',
    compatible => ’8.1’);

## AQ Queues

Perform the following steps to create a AQ queue and make it available for use as an iProcess message queue:

| 1. | Use the `DBMS_AQADM.CREATE_QUEUE` procedure to create the AQ queue. The following parameters are required.
Column | Description
queue_name | Unique name of this queue. This name is used in the `queue_phys_descr` column of the iql_queues table.
queue_table | Name of the queue table that holds this queue. This name is used in the `queue_phys_descr` column of the iql_queues table. Note: Multiple queues can be stored in the same queue table.
retry_delay | Delay time, in seconds, before a message is scheduled for processing again after an application rollback.
max_retries | Number of times that a dequeue with the `REMOVE` mode can be attempted on a message. The count is incremented when the application issues a rollback after executing the dequeue. The message is moved to the default exception queue when it is reaches its `max_retries`.
The following example, from the `init2Kora.sql` script, shows the SQL used to create one of the default background Mbox queues that is created when the iProcess Engine is installed.
    DBMS_AQADM.CREATE_QUEUE (queue_name => 'TABLE_OWNER.bgmboxqueue1', queue_table => 'TABLE_OWNER.bgmboxtable1', retry_delay => 300, max_retries => 12);

| 2. | Use the `DBMS_AQADM.START_QUEUE` procedure to start the queue.

| 3. | Use the `DBMS_AQADM.GRANT_QUEUE_PRIVILEGE` procedure to grant the privileges on the queue. The following parameters are required.
Column | Description
`privilege` | The AQ queue privilege to grant. This value must be `ALL` (which means both `ENQUEUE` and `DEQUEUE`).
`queue_name` | Name of the queue.
`grantee` | Grantee. This value must be the name of the iProcess Oracle Foreground user. Note: The Oracle Foreground user is defined in the third entry (after the second backslash character) on line 9 of the `SWDIR``\etc\staffpms` file. For example: `3\swpro\swuser\swpro\staffdb1\10\0`
`grant_option` | Defines whether the access privilege is granted with the `GRANT` option or not. This value must be `false`.
The following example, from the `init2Kora.sql` script, shows the SQL used to grant the required privileges on one of the default background Mbox queues that is created when the iProcess Engine is installed.
    dbms_aqadm.grant_queue_privilege (privilege => 'all', queue_name => 'TABLE_OWNER.bgmboxqueue1', grantee => 'FOREGROUND_USER', grant_option => false )

## Example

Suppose that the volume of messages handled by your system has increased significantly, and the default messages queues can no longer cope. To deal with the additional load you have decided that you need to add a new `BGMBOX3` message queue to the `BGMBSET` Mboxset. This queue requires a new `bgmboxqueue3` Oracle AQ queue and `bgmboxtable3` queue table.

Note: This example uses UNIX syntax.

To do this:

1. Use SQLPLUS to connect to the Oracle instance.

       $ORACLE_HOME/bin/sqlplus
       connect swpro/staffpro1;

2. Create an AQ queue table called `bgmboxtable3`.

       EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'swpro.bgmboxtable3', queue_payload_type => 'swpro.swlocalmsg', comment => '3rd background mbox', compatible => 8.1);

3. Create an AQ queue called `bgmboxqueue3` in this queue table.

       EXECUTE DBMS_AQADM.CREATE_QUEUE (queue_name => 'swpro.bgmboxqueue3', queue_table => 'swpro.bgmboxtable3', retry_delay => 300, max_retries => 12);

4. Start the `bgmboxqueue3` queue.

       EXECUTE DBMS_AQADM.START_QUEUE (queue_name => 'swpro.bgmboxqueue3');

5. Grant the required privileges on the `bgmboxqueue3` queue.

       EXECUTE dbms_aqadm.grant_queue_privilege (privilege => 'all', queue_name => 'bgmboxqueue3', grantee => 'swuser', grant_option => false );

6. Commit the changes, disconnect and exit.

       commit;
       disconnect;
       exit;

7. Use the `SWDIR``\util\swadm` utility to add a new message queue called `BGMBOX3`, which uses the `bgmboxqueue3` queue.

       cd $SWDIR/util
       swadm ADD_QUEUE BGMBOX3 Local 0001::bgmboxtable3:bgmboxqueue3

8. Add the `BGMBOX3` queue to the `BGMBSET` Mbox set.

       swadm ADD_QUEUE_TO_MBOXSET 1 8

`1` is the number of the `BGMBSET` Mboxset (from the `swadm` `SHOW_MBOXSETS` command) and `8` is the number of the message queue (from the `swadm` `SHOW_QUEUES` command).

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO iProcess® Engine