12 Configuring Queues and Propagations

Configuring an ANYDATA Queue

An ANYDATA queue stores messages whose payloads are of ANYDATA type. Therefore, an ANYDATA queue can store a message with a payload of nearly any type, if the payload is wrapped in an ANYDATA wrapper. Each Oracle Streams capture process, apply process, and messaging client is associated with one ANYDATA queue, and each Oracle Streams propagation is associated with one ANYDATA source queue and one ANYDATA destination queue.

The easiest way to create an ANYDATA queue is to use the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package. This procedure enables you to specify the following settings for the ANYDATA queue it creates:

• The queue table for the queue

• A storage clause for the queue table

• The queue name

• A queue user that will be configured as a secure queue user of the queue and granted ENQUEUE and DEQUEUE privileges on the queue

• A comment for the queue

If the specified queue table does not exist, then it is created. If the specified queue table exists, then the existing queue table is used for the new queue. If you do not specify any queue table when you create the queue, then, by default, streams_queue_table is specified.

You can use a single procedure, the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package, to create an ANYDATA queue and the queue table used by the queue. For SET_UP_QUEUE to create a new queue table, the specified queue table must not exist.

For example, run the following procedure to create an ANYDATA queue with the SET_UP_QUEUE procedure:

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table => 'strmadmin.streams_queue_table',
    queue_name  => 'strmadmin.streams_queue',
    queue_user  => 'hr');
END;
/

Running this procedure performs the following actions:

• Creates a queue table named streams_queue_table. The queue table is created only if it does not already exist. Queues based on the queue table store messages of ANYDATA type. Queue table names can be a maximum of 24 bytes.

• Creates a queue named streams_queue. The queue is created only if it does not already exist. Queue names can be a maximum of 24 bytes.

• Specifies that the streams_queue queue is based on the strmadmin.streams_queue_table queue table.

• Configures the hr user as a secure queue user of the queue, and grants this user ENQUEUE and DEQUEUE privileges on the queue.

• Starts the queue.

Default settings are used for the parameters that are not explicitly set in the SET_UP_QUEUE procedure.

When the SET_UP_QUEUE procedure creates a queue table, the following DBMS_AQADM.CREATE_QUEUE_TABLE parameter settings are specified:

• If the database is Oracle Database 10g Release 2 or later, the sort_list setting is commit_time. If the database is a release prior to Oracle Database 10g Release 2, the sort_list setting is enq_time.

• The multiple_consumers setting is TRUE.

• The message_grouping setting is transactional.

• The secure setting is TRUE.

The other parameters in the CREATE_QUEUE_TABLE procedure are set to their default values.

You can use the CREATE_QUEUE_TABLE procedure in the DBMS_AQADM package to create a queue table of ANYDATA type with different properties than the default properties specified by the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package. After you create the queue table with the CREATE_QUEUE_TABLE procedure, you can create a queue that uses the queue table. To do so, specify the queue table in the queue_table parameter of the SET_UP_QUEUE procedure.

Similarly, you can use the CREATE_QUEUE procedure in the DBMS_AQADM package to create a queue instead of SET_UP_QUEUE. Use CREATE_QUEUE if you require custom settings for the queue. For example, use CREATE_QUEUE to specify a custom retry delay or retention time. If you use CREATE_QUEUE, then you must start the queue manually.

Example of Configuring a Propagation Using DBMS_STREAMS_ADM

The following example runs the ADD_TABLE_PROPAGATION_RULES procedure in the DBMS_STREAMS_ADM package to create a propagation:

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name              => 'hr.departments',
    streams_name            => 'strm01_propagation',
    source_queue_name       => 'strmadmin.strm_a_queue',
    destination_queue_name  => 'strmadmin.strm_b_queue@dbs2.net',
    include_dml             => TRUE,
    include_ddl             => TRUE,
    include_tagged_lcr      => FALSE,
    source_database         => 'dbs1.net',
    inclusion_rule          => TRUE,
    queue_to_queue          => TRUE);
END;
/

Running this procedure performs the following actions:

• Creates a propagation named strm01_propagation. The propagation is created only if it does not already exist.

• Specifies that the propagation propagates LCRs from strm_a_queue in the current database to strm_b_queue in the dbs2.net database.

• Specifies that the propagation uses the dbs2.net database link to propagate the LCRs, because the destination_queue_name parameter contains @dbs2.net.

• Creates a positive rule set and associates it with the propagation because the inclusion_rule parameter is set to TRUE. The rule set uses the evaluation context SYS.STREAMS$_EVALUATION_CONTEXT. The rule set name is system generated.

• Creates two rules. One rule evaluates to TRUE for row LCRs that contain the results of DML changes to the hr.departments table. The other rule evaluates to TRUE for DDL LCRs that contain DDL changes to the hr.departments table. The rule names are system generated.

• Adds the two rules to the positive rule set associated with the propagation. The rules are added to the positive rule set because the inclusion_rule parameter is set to TRUE.

• Specifies that the propagation propagates an LCR only if it has a NULL tag, because the include_tagged_lcr parameter is set to FALSE. This behavior is accomplished through the system-created rules for the propagation.

• Specifies that the source database for the LCRs being propagated is dbs1.net, which might or might not be the current database. This propagation does not propagate LCRs in the source queue that have a different source database.

• Creates a propagation job for the queue-to-queue propagation.

Example of Configuring a Propagation Using DBMS_PROPAGATION_ADM

The following example runs the CREATE_PROPAGATION procedure in the DBMS_PROPAGATION_ADM package to create a propagation:

BEGIN
  DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
    propagation_name   => 'strm02_propagation',
    source_queue       => 'strmadmin.strm03_queue',
    destination_queue  => 'strmadmin.strm04_queue',
    destination_dblink => 'dbs2.net',
    rule_set_name      => 'strmadmin.strm01_rule_set',
    queue_to_queue     => TRUE);
END;
/

Running this procedure performs the following actions:

• Creates a propagation named strm02_propagation. A propagation with the same name must not exist.

• Specifies that the propagation propagates messages from strm03_queue in the current database to strm04_queue in the dbs2.net database. Depending on the rules in the rule sets for the propagation, the propagated messages can be LCRs or user messages, or both.

• Specifies that the propagation uses the dbs2.net database link to propagate the messages.

• Associates the propagation with an existing rule set named strm01_rule_set. This rule set is the positive rule set for the propagation.

• Creates a propagation job for the queue-to-queue propagation.