Oracle Advanced Queue Schema Configuration (Inbound/Outbound Propagation) With IBM MQ

Demonstration of configuring Oracle schemas to propagate message using Oracle advanced queue through IBM Websphere.

1.    Configuring MQ Admin user for client OUTBOUND_CLIENT, INBOUND_CLIENT

1.1         Granting Queue propagation privileges to AQ admin user.  

grant connect, resource, aq_administrator_role, mgw_agent_role, mgw_administrator_role to admin_user;

grant execute on sys.dbms_aqadm to admin_user;

grant execute on sys.dbms_aq to admin_user;

--granting dbms_aqin is not in any note but you may need it

grant execute on sys.dbms_aqin to admin_user;

grant execute on dbms_transform to admin_user;

1.2         Granting QUEUEING & DEQUEING privileges to the AQ admin user 

EXECUTE dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','admin_user',FALSE)

EXECUTE dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','admin_user',FALSE)

 2.    CREATE A NEW AQ QUEUE for OUTBOUND Propagation

Create OUTBOUND_CLIENT QUEUE Table

BEGIN

dbms_aqadm.create_queue_table( queue_table => 'OUTBOUND_CLIENT' || '.iai', multiple_consumers => TRUE, queue_payload_type => 'sys.mgw_basic_msg_t');

dbms_aqadm.create_queue( queue_name => 'OUTBOUND_CLIENT' || '.iai_queue', queue_table => 'OUTBOUND_CLIENT' || '.iai', retention_time => 30 );

DBMS_AQADM.START_QUEUE (queue_name => ' OUTBOUND_CLIENT ' || '.iai_queue');

END;

3.    Create a new AQ QUEUE table for INBOUND Propagation

BEGIN

dbms_aqadm.create_queue_table( queue_table => ' INBOUND_CLIENT' || '.igs', multiple_consumers => TRUE, queue_payload_type => 'sys.mgw_basic_msg_t');

dbms_aqadm.create_queue( queue_name => ' INBOUND_CLIENT' || '.igs_queue', queue_table => ' INBOUND_CLIENT' || '.igs', retention_time => 30 );

DBMS_AQADM.START_QUEUE (queue_name => ' INBOUND_CLIENT' || '.igs_queue');

END;

4.    Creating Trigger on queue table to monitor propagation details. (OPTIONAL)

Precreate a table iai_logger depending upon your application logic.

CREATE OR REPLACE TRIGGER OUTBOUND_CLIENT.iai_enqueue_trigger

        AFTER INSERT ON OUTBOUND_CLIENT.iai

        FOR EACH ROW

BEGIN

        INSERT INTO OUTBOUND_CLIENT.iai_logger (msgid,quote_name,enq_time) values (:new.msgid,:new.corrid,to_char(:new.enq_time,'DD-MON-YYYY HH24:MI:SS'));

END;

/

CREATE OR REPLACE TRIGGER OUTBOUND_CLIENT.iai_dequeue_trigger

        AFTER UPDATE ON OUTBOUND_CLIENT.iai

        FOR EACH ROW

WHEN (new.deq_time is not null)

BEGIN

UPDATE iai_logger set deq_time = to_char(:new.deq_time,'DD-MON-YYYY HH24:MI:SS') where msgid = :new.msgid;

END;

/

5.    Create AQ gateway Link (OUTBOUND_CLIENT)

Use the IBM Websphere MQ username,password,port,Channel, queue manager name etc ..that is configured for your case.

declare

      v_options sys.mgw_properties;

      v_prop    sys.mgw_mqseries_properties;

    begin

      v_prop := sys.mgw_mqseries_properties.construct();

      v_prop.max_connections := 1;

      v_prop.username := 'queue0x';     

      v_prop.password := 'password';      

      v_prop.hostname := 'mq_hostname'; 

      v_prop.port     := 12345;           

      v_prop.channel  := 'OUTBOUND.Q.COMPANY';

      v_prop.outbound_log_queue := 'OUTBOUND.QL.EQUOTES.LOG';

      v_prop.queue_manager := 'Q00';    

      dbms_mgwadm.create_msgsystem_link(

          linkname => 'OUTBOUND_CLIENT' || '_mqlink',

          properties => v_prop,          

          options => v_options );        

    end;

    /

6.    Create Non oracle Queue & Register (OUTBOUND_CLIENT)

declare

      v_options sys.mgw_properties;

    begin

      dbms_mgwadm.register_foreign_queue(

          name           => 'non_oracle_queue',        

          linkname       => 'OUTBOUND_CLIENT' || '_mqlink',     

          provider_queue => 'OUTBOUND_CLIENT.QL.EQUOTES',       

          domain         => dbms_mgwadm.DOMAIN_QUEUE,

          options        => v_options);

    end;

    /

7.    Add Subscriber (OUTBOUND_CLIENT)

BEGIN

      dbms_mgwadm.add_subscriber(

        subscriber_id    => 'OUTBOUND_CLIENT' || '_sub_iai_export',   

        propagation_type => dbms_mgwadm.outbound_propagation,

        queue_name       => 'OUTBOUND_CLIENT' || '.iai_queue',

        destination      => 'non_oracle_queue@' || 'OUTBOUND_CLIENT' || '_mqlink');

    END;

    /

8.    Scheuling the propagation (OUTBOUND_CLIENT)

BEGIN

      dbms_mgwadm.schedule_propagation(

        schedule_id      =>  'OUTBOUND_CLIENT' || '_sch_iai_export',

        propagation_type => dbms_mgwadm.outbound_propagation,

        source           => 'OUTBOUND_CLIENT' || '.iai_queue',

        destination      => 'non_oracle_queue@' || 'OUTBOUND_CLIENT' || '_mqlink');

    END;

    /

9.    Setup INBOUND_CLIENT for Inbound Queue

10. Create Queue Table (INBOUND_CLIENT)

BEGIN

dbms_aqadm.create_queue_table( queue_table => 'INBOUND_CLIENT' || '.igs', multiple_consumers => TRUE, queue_payload_type => 'sys.mgw_basic_msg_t');

dbms_aqadm.create_queue( queue_name => 'INBOUND_CLIENT' || '.igs_queue', queue_table => 'INBOUND_CLIENT' || '.igs', retention_time => 30 );

DBMS_AQADM.START_QUEUE (queue_name => 'INBOUND_CLIENT' || '.igs_queue');

END

11.  Create Gateway Link for Inbound Queue

declare

      v_options sys.mgw_properties;

      v_prop    sys.mgw_mqseries_properties;

    begin

      v_prop := sys.mgw_mqseries_properties.construct();

      v_prop.max_connections := 1;

      v_prop.username := 'mq0x';       

      v_prop.password := 'password';        

      v_prop.hostname := 'hostname';       

      v_prop.port     := 12345;            

      v_prop.channel  := 'IN.Q.COMPANY'; 

      v_prop.inbound_log_queue := 'IN.Q.FT.LOG';

      v_prop.queue_manager := 'IQ00';    

      dbms_mgwadm.create_msgsystem_link(

          linkname => 'INBOUND_CLIENT_IGS' || '_mqlink',  

          properties => v_prop,                  

          options => v_options );                

    end;

    /

12.  Create Non oracle queue & Register (INBOUND_CLIENT)

declare

      v_options sys.mgw_properties;

    begin

        dbms_mgwadm.register_foreign_queue(

          name           => 'non_oracle_queue',        

          linkname       => 'INBOUND_CLIENT_IGS' || '_mqlink',

          provider_queue => 'IQ.QL.FT.TRIGGER',       

          domain         => dbms_mgwadm.DOMAIN_QUEUE,  

          options        => v_options);

    end;

    /

13. Add subscriber (INBOUND_CLIENT)

BEGIN

      dbms_mgwadm.add_subscriber(

         subscriber_id    => 'INBOUND_CLIENT' || '_sub_igs_notify',     

         propagation_type => dbms_mgwadm.inbound_propagation,

         queue_name       => 'NON_ORACLE_QUEUE@INBOUND_CLIENT_IGS_MQLINK',

         destination      => 'INBOUND_CLIENT.IGS_QUEUE');

    END;

14. Start Propagation (INBOUND_CLIENT)

BEGIN

      dbms_mgwadm.schedule_propagation(

        schedule_id      =>  'INBOUND_CLIENT' || '_sch_igs_export', 

        propagation_type => dbms_mgwadm.inbound_propagation,

        source           => 'NON_ORACLE_QUEUE@INBOUND_CLIENT_IGS_MQLINK',

        destination      => 'INBOUND_CLIENT.IGS_QUEUE');

    END;

15. Testing the propagation

Send a message through queue, that might be XML, LOB.

Following query should show some data and disappears once message gets propagated through IBM Queue.

select Q_NAME,ENQ_TIME,DEQ_TIME,USER_DATA from OUTBOUND_CLIENT.iai;

Check how many messages been propagated/failure etc

select subscriber_id, Status, Propagated_msgs, Failures, Last_Error_Msg from mgw_subscribers;

16. Some useful MQ VIEWS

select * from SYS.MGW_MQSERIES_LINKS ;

select * from MGW_LINKS;

select * from MGW_SUBSCRIBERS;

select * from MGW_SCHEDULES;

select * from MGW_FOREIGN_QUEUES;

select * from MGW_GATEWAY;

select * from SYS.MGW$_MQSERIES_LINKS;

select * from SYS.MGW_RECV_LOG_QT;

select * from SYS.MGW_SEND_LOG_QT;