Some trouble between WebSphere MQ and Oracle 11g

7 views
Skip to first unread message

letd...@gmail.com

unread,
Sep 6, 2018, 2:43:37 AM9/6/18
to
Hi all!

I do all configuration steps, all seems right, but test messages in WebSphere MQ Explorer keep in place.

Ok, lets to detail:

On Oracle side i create users mgwuser, mgwadm with

CREATE USER mgwadm IDENTIFIED BY mgwadm;
GRANT CONNECT, RESOURCE to mgwadm;
GRANT EXECUTE ON DBMS_AQADM TO mgwadm;
GRANT Aq_administrator_role TO mgwadm;
GRANT MGW_ADMINISTRATOR_ROLE to mgwadm;

CREATE USER mgwuser IDENTIFIED BY mgwuser;
GRANT CONNECT, RESOURCE to mgwuser;
GRANT MGW_AGENT_ROLE to mgwuser;

Under mgwuser do next scripts:

begin
dbms_aqadm.create_queue_table(
queue_table => 'mgw_basic_mc',
queue_payload_type => 'sys.mgw_basic_msg_t',
multiple_consumers => TRUE);

dbms_aqadm.create_queue(queue_name =>'mgw_basic_src',
queue_table => 'mgw_basic_mc',
max_retries => 1000);

dbms_aqadm.start_queue('mgw_basic_src');

dbms_aqadm.create_queue_table(queue_table => 'mgw_basic_sc',
queue_payload_type => 'sys.mgw_basic_msg_t',
multiple_consumers => FALSE);

dbms_aqadm.create_queue(queue_name =>'mgw_basic_dest',
queue_table => 'mgw_basic_sc',
max_retries => 1000);

dbms_aqadm.start_queue('mgw_basic_dest');
end;


Under mgwadm do:

declare
gv_database VARCHAR2(32) := 'MYBASE'; -- tnsname entry for local database
gv_mq_host VARCHAR2(48) := '192.168.0.7'; -- MQ server
gv_mq_port VARCHAR2(6) := '1420'; -- port for TESTQM
gv_mq_channel VARCHAR2(32):= 'ORACLE'; -- “server-connect” channel in TESTQM

gv_mq_queue_name VARCHAR2(32) := 'TESTQ'; -- local Queue
gv_mq_inbound_log_queue VARCHAR2(32) := 'TESTIN'; -- local Queue
gv_mq_outbound_log_queue VARCHAR2(32) := 'TESTOUT'; -- local Queue

gv_mq_queue_manager VARCHAR2(32) := 'GUAC'; -- Queue manager
gv_mq_username VARCHAR2(32) := 'oracle'; -- optional
gv_mq_password VARCHAR2(32) := 'oracle'; -- optional

v_options sys.mgw_properties;
v_prop sys.mgw_mqseries_properties;
begin
-- Setting the gateway agent user
dbms_mgwadm.db_connect_info(username => 'mgwagent',password => 'mgwagent',database => RTRIM(gv_database));

-- Creating MQSeries link
-- set certain mqseries properties used for MQSeries
v_prop := sys.mgw_mqseries_properties.construct();
v_prop.max_connections := 1;
v_prop.queue_manager := RTRIM(gv_mq_queue_manager);
v_prop.hostname := RTRIM(gv_mq_host);
v_prop.channel := RTRIM(gv_mq_channel);
v_prop.port := RTRIM(gv_mq_port);
if (gv_mq_username IS NOT NULL) then v_prop.username := RTRIM(gv_mq_username); end if;
if (gv_mq_password IS NOT NULL) then v_prop.password := RTRIM(gv_mq_password); end if;
if (gv_mq_outbound_log_queue IS NOT NULL) then v_prop.outbound_log_queue := RTRIM(gv_mq_outbound_log_queue); end if;
if (gv_mq_inbound_log_queue IS NOT NULL) then v_prop.inbound_log_queue := RTRIM(gv_mq_inbound_log_queue); end if;

--dbms_mgwadm.unschedule_propagation(SCHEDULE_ID=>'sch_mq2aq');
--dbms_mgwadm.unschedule_propagation(SCHEDULE_ID=>'sch_aq2mq1');
--dbms_mgwadm.remove_subscriber(subscriber_id => 'sub_aq2mq1', force => dbms_mgwadm.FORCE);
--dbms_mgwadm.remove_subscriber(subscriber_id => 'sub_mq2aq', force => dbms_mgwadm.FORCE);
--dbms_mgwadm.remove_subscriber(subscriber_id => 'sub_aq2mq', force => dbms_mgwadm.FORCE);
--dbms_mgwadm.unregister_foreign_queue(name => 'destq', linkname => 'mqlink');
--dbms_mgwadm.remove_msgsystem_link(linkname =>'mqlink');

dbms_mgwadm.create_msgsystem_link(
linkname =>'mqlink', -- link name
properties => v_prop, -- mqseries driver properties
options => v_options ); -- options

-- Registering foreign queue
-- MQ_openOptions: 2066 is TODO
v_options := sys.mgw_properties(sys.mgw_property('MQ_openOptions', '2066'));

dbms_mgwadm.register_foreign_queue(
name => 'destq', -- MGW foreign queue name
linkname => 'mqlink', -- name of link to use
provider_queue => RTRIM(gv_mq_queue_name), -- name of MQSeries queue
domain => dbms_mgwadm.DOMAIN_QUEUE, -- single consumer queue
options => v_options );


-- Adding outbound subscriber.
-- queue_name should be an AQ queue
-- destination should be a non-Oracle queue, subscriber rule applies to AQ subscriber
-- exception queue must be a NORMAL AQ queue with payload the same as the source queue
dbms_mgwadm.add_subscriber(
subscriber_id => 'sub_aq2mq', -- MGW subscriber name
propagation_type => dbms_mgwadm.outbound_propagation,
queue_name => 'mgwuser.mgw_basic_src', -- AQ queue name
destination => 'destq@mqlink'); -- MGW foreign queue with link
-- no selection rule used for this subscriber,no transformation invoked on dequeue,no exception queue


-- Adding inbound subscriber.
-- destination should be an AQ queue
-- queue_name should be a non-Oracle queue
-- exception queue must be a WebSphere MQ queue name of a queue that exists on the queue manager the link uses.
dbms_mgwadm.add_subscriber(
subscriber_id => 'sub_mq2aq', -- MGW subscriber name
propagation_type => dbms_mgwadm.inbound_propagation,
queue_name => 'destq@mqlink', -- MGW foreign queue with link
destination => 'mgwuser.mgw_basic_dest'); -- AQ queue name
-- no selection rule can be used for this subscriber, no transformation invoked on dequeue, no exception queue


-- Scheduling propagation
--dbms_mgwadm.unschedule_propagation(SCHEDULE_ID=>'sch_aq2mq1');
dbms_mgwadm.schedule_propagation(
schedule_id => 'sch_aq2mq1', -- schedule name
propagation_type => dbms_mgwadm.outbound_propagation, -- outbound propagation
source =>'mgwuser.mgw_basic_src', -- AQ queue name
destination =>'destq@mqlink'); -- MGW foreign queue with link
-- The remaining fields currently not used by MGW


--dbms_mgwadm.unschedule_propagation(SCHEDULE_ID=>'sch_mq2aq');
dbms_mgwadm.schedule_propagation(
schedule_id => 'sch_mq2aq',
propagation_type => dbms_mgwadm.inbound_propagation,
source => 'destq@mqlink',
destination =>'mgwuser.mgw_basic_dest');

commit;

end;

begin
dbms_mgwadm.enable_propagation_schedule('sch_mq2aq');
dbms_mgwadm.enable_propagation_schedule('sch_aq2mq1');
end;



dbms_mgwadm is started



Ok, in WebSphere MQ Explorer i create two local queue under GUAC - TESTIN, TESTOUT, place in it test messages, but its keep in place.

In connection with applications(WebSphere) i see connect from Oracle

Need help with solving this trouble.

Thanks in advance, Andrey
Reply all
Reply to author
Forward
0 new messages