Testing Oracle 11g Message Gateway with IBM websphere MQ v7

TestCase setup
· MQ server
o IP: 192.168.1.205
o MQ server V7 installed successfully
· Oracle server
o IP: 192.168.1.221
o Oracle database 11g freshly installed
o Database T1 created during the installation as “orcl”
1 Create test MQ objects on Websphere server
Queue manager: TESTQM
Local Queue: TQ, LOG_IN, LOG_OUT
Channel: TQCONN(server-connect), MCA user ID=oracle

From OS : Add oracle into mqm group
2 Install MQ client on Oracle server
2.1 Log in as root.
And make sure all RPMs are in your current directory.
2.2 User creation
Create following user with group name of “mqm”
mqm, crtmqm,strmqm
2.3 Create mqm under /opt and /var
create /opt/mqm, make it RW to mqm group and 600MB in space capacity
create /var/mqm, make it RW to mqm group and 300MB in space capacity
2.4 Run the mqlicense.sh script.
If you want to view a text-only version of the license, which can be read by a screen-reader, type: ./mqlicense.sh -text_only
The license is displayed.
If want to accept the license without it being displayed, you can run the mqlicense.sh script with the -accept option. ./mqlicense.sh -accept
You must accept the license agreement before you can proceed with the installation.
2.5 Client installation:
Install minimum components:
· MQSeriesRuntime
· MQSeriesJRE
· MQSeriesServer
· MQSeriesJava

for 64bit:
rpm -ivh MQSeriesRuntime-7.0.0-0.x86_64.rpm
rpm -ivh MQSeriesJava-7.0.0-0.x86_64.rpm MQSeriesJRE-7.0.0-0.x86_64.rpm MQSeriesServer-7.0.0-0.x86_64.rpm

Then:
rpm -ivh MQSeriesClient-7.0.0-0.x86_64.rpm
3 Loading Database Objects into the Database
Using SQL*Plus, run ORACLE_HOME/mgw/admin/catmgw.sql as user SYS as SYSDBA
4 Setting Up a mgw.ora Initialization File
$ cp sample_mgw.ora mgw.ora

vi mgw.ora
s/myOracleHome/u01\/app\/oracle\/product\/11.1.0\/db_1/g
5 Setup listener.ora/tnsnames.ora
5.1 Configure listener.ora
# Add a SID_DESC
SID_LIST_LISTENER= (SID_LIST=
(SID_DESC =
(SID_NAME= mgwextproc)
(ENVS=”LD_LIBRARY_PATH=JRE_HOME/lib/sparc/server:ORACLE_HOME/lib”)
(ORACLE_HOME=ORACLE_HOME)
(PROGRAM = extproc))

SID_NAME The SID that is specified in the net service name in tnsnames.ora. In the following example, the SID_NAME is mgwextproc. ENVS Set up the LD_LIBRARY_PATH environment needed for the external procedure to run. The LD_LIBRARY_PATH must contain the following paths:
JRE_HOME/lib/PLATFORM_TYPE/server
ORACLE_HOME/lib
It should also contain any additional libraries required by third-party messaging systems. See “Setting Up Non-Oracle Messaging Systems”.
ORACLE_HOME Your Oracle home directory. Using $ORACLE_HOME does not work. PROGRAM The name of the external procedure agent, which is extproc

For example:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC =
(SID_NAME=mgwextproc)
(ENVS=”LD_LIBRARY_PATH=/u01/app/oracle/product/11.1.0/db_1/jdk/jre/lib/amd64/server:/u01/app/oracle/product/11.1.0/db_1/lib”)
(ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1)
(PROGRAM=extproc)
))
5.2 Configure tnsnames.ora
MGW_AGENT =
(DESCRIPTION=
(ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC)(KEY=EXTPROC1521)))
(CONNECT_DATA= (SID=mgwextproc) (PRESENTATION=RO)))
Note: KEY=EXTPROC should match what’s in listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
6 Create username
create user mgwuser identified by mgwuser;
grant connect, resource to mgwuser;
grant execute on dbms_aq to mgwuser;
grant execute on dbms_aqadm to mgwuser;

grant connect, resource, MGW_AGENT_ROLE to mgwagent identified by mgwagent;
grant connect, resource, MGW_ADMINISTRATOR_ROLE to mgwadm identified by mgwadm;

7 Checkpoint one
7.1 Start MGW
connect mgwadm/mgwadm
exec dbms_mgwadm.db_connect_info(‘mgwagent’,’mgwagent’, ‘t1’);
exec dbms_mgwadm.startup;
7.2 Check MGW status
select * from MGW_GATEWAY;
If “ORA-28575: unable to open RPC connection to external procedure agent”
Then fix listener.ora and tnsnames.ora configurations
· tnsnames.ora must have a net service name entry named MGW_AGENT.
· The SID value specified for CONNECT_DATA of the MGW_AGENT net service name in tnsnames.ora must match the SID_NAME value of the SID_DESC entry in listener.ora.
· If the MGW_AGENT net service name is set up for an IPC connection, the KEY values for ADDRESS in tnsnames.ora and listener.ora must match.
· Other values in tnsnames.ora or listener.ora may be incorrect or not match.
Note: No log file will be generated in the scenario.
7.3 Check log file for error messages
7.4 Stop MGW
exec dbms_mgwadm.shutdown;
8 AQMQ testing with SYS.MGW_BASIC_MSG_T payload
8.1 Create test queues in oracle
connect mgwuser/mgwuser
set serveroutput on

— create queue tables and queues
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;
/
8.2 Setup oracle OMG for MQ
connect mgwadm/mgwadm;

variable gv_database VARCHAR2(32);
variable gv_mq_queue_manager VARCHAR2(32);
variable gv_mq_queue_name VARCHAR2(32);
variable gv_mq_channel VARCHAR2(32);
variable gv_mq_inbound_log_queue VARCHAR2(32);
variable gv_mq_outbound_log_queue VARCHAR2(32);
variable gv_mq_username VARCHAR2(32);
variable gv_mq_password VARCHAR2(32);
variable gv_mq_host VARCHAR2(48);
variable gv_mq_port VARCHAR2(6);

—
— WARNING!!!! The values below must be set before this script is run.
—
begin
:gv_database := ‘t1’; — tnsname entry for local database
:gv_mq_queue_manager := ‘TESTQM’; — Queue manager
:gv_mq_queue_name := ‘TQ’; — local Queue
:gv_mq_channel := ‘TQCONN’; — “server-connect” channel in TESTQM
:gv_mq_host := ‘192.168.1.205’; — MQ server
:gv_mq_port := ‘1417’; — port for TESTQM
:gv_mq_username := NULL; — optional
:gv_mq_password := NULL; — optional
:gv_mq_inbound_log_queue := ‘LOG_IN’; — local Queue
:gv_mq_outbound_log_queue := ‘LOG_OUT’; — local Queue
end;
/
prompt
prompt Setting the gateway agent user
prompt

rem If we do not set the service_name argument it will default to the
rem ORACLE_SID of the MGW process (the listener process).
begin
dbms_mgwadm.db_connect_info(
username => ‘mgwagent’,
password => ‘mgwagent’,
database => RTRIM(:gv_database));
end;
/

prompt
prompt Creating MQSeries link
prompt

declare
v_options sys.mgw_properties;
v_prop sys.mgw_mqseries_properties;
begin
if (:gv_mq_queue_manager IS NULL) then
dbms_output.put_line(‘queue manager must be specified’);
end if;

— set options if desired

— set certain mqseries properties used for MQSeries
— NOTE: This information must be changed for your MQSeries setup.
v_prop := sys.mgw_mqseries_properties.construct();

v_prop.max_connections := 1;

— queue manager name (required)
v_prop.queue_manager := RTRIM(:gv_mq_queue_manager);

— hostname for queue manager host (required)
v_prop.hostname := RTRIM(:gv_mq_host);

— channel name (required)
v_prop.channel := RTRIM(:gv_mq_channel);

— port (required — 1414 is MQSeries default)
v_prop.port := RTRIM(:gv_mq_port);

— username given to queue manager
if (:gv_mq_username IS NOT NULL) then
v_prop.username := RTRIM(:gv_mq_username);
end if;

— password given to queue manager
if (:gv_mq_password IS NOT NULL) then
v_prop.password := RTRIM(:gv_mq_password);
end if;

— name of MQSeries queue to be used for MGW logging on outbound jobs
if (:gv_mq_outbound_log_queue IS NOT NULL) then
v_prop.outbound_log_queue := RTRIM(:gv_mq_outbound_log_queue);
end if;

— name of MQSeries queue to be used for MGW logging on inbound jobs
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.create_msgsystem_link(
linkname =>’mqlink’, — link name
properties => v_prop, — mqseries driver properties
options => v_options ); — options

end;
/

prompt
prompt Registering foreign queue
prompt

declare
v_options sys.mgw_properties;
begin
— Set options for register 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 );
end;
/

prompt
prompt Adding outbound subscriber.
prompt

begin
— 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,
— AQ queue name
queue_name => ‘mgwuser.mgw_basic_src’,
— MGW foreign queue with link
destination => ‘destq@mqlink’);
— no selection rule used for this subscriber
— no transformation invoked on dequeue
— no exception queue
end;
/

prompt
prompt Adding inbound subscriber.
prompt

begin
— 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(
— MGW subscriber name
subscriber_id => ‘sub_mq2aq’,
propagation_type => dbms_mgwadm.inbound_propagation,
— MGW foreign queue with link
queue_name => ‘destq@mqlink’,
— AQ queue name
destination => ‘mgwuser.mgw_basic_dest’);
— no selection rule can be used for this subscriber
— no transformation invoked on dequeue
— no exception queue
end;
/

prompt
prompt Scheduling propagation
prompt

begin
dbms_mgwadm.schedule_propagation(
— schedule name
schedule_id => ‘sch_aq2mq’,
— outbound propagation
propagation_type => dbms_mgwadm.outbound_propagation,
— AQ queue name
source =>’mgwuser.mgw_basic_src’,
— MGW foreign queue with link
destination =>’destq@mqlink’);

— The remaining fields currently not used by MGW
end;
/

begin
dbms_mgwadm.schedule_propagation(
— schedule name
schedule_id => ‘sch_mq2aq’,
— outbound propagation
propagation_type => dbms_mgwadm.inbound_propagation,
— MGW foreign queue with link
source => ‘destq@mqlink’,
— AQ queue name
destination =>’mgwuser.mgw_basic_dest’);

— The remaining fields currently not used by MGW
end;
/

Clean testing:
connect mgwadm/mgwadm

Rem Remove the subscribers.
begin
— Since this subscriber was for outbound propagation
— removing it will remove the underlying AQ subscriber.
dbms_mgwadm.remove_subscriber(subscriber_id => ‘sub_aq2mq’,
force => dbms_mgwadm.NO_FORCE);
dbms_mgwadm.remove_subscriber(subscriber_id => ‘sub_mq2aq’,
force => dbms_mgwadm.NO_FORCE);
end;
/

exec dbms_mgwadm.remove_subscriber(subscriber_id => ‘sub_aq2mq’,force => dbms_mgwadm.FORCE);
exec dbms_mgwadm.remove_subscriber(subscriber_id => ‘sub_mq2aq’,force => dbms_mgwadm.FORCE);

Rem Remove the schedules.
begin
dbms_mgwadm.unschedule_propagation(schedule_id => ‘sch_aq2mq’);
dbms_mgwadm.unschedule_propagation(schedule_id => ‘sch_mq2aq’);
end;
/

Rem Unregister the MQSeries queue.
begin
dbms_mgwadm.unregister_foreign_queue(name => ‘destq’, linkname => ‘mqlink’);
end;
/

Rem Finally, remove the link.
begin
dbms_mgwadm.remove_msgsystem_link(‘mqlink’);
end;
/
8.3 Checkpoint two
8.3.1 Start MGW
connect mgwadm/mgwadm
exec dbms_mgwadm.startup;
8.3.2 Check MGW status
select * from MGW_GATEWAY;
If “ORA-28575: unable to open RPC connection to external procedure agent”
Then fix listener.ora and tnsnames.ora configurations
Note: No log file will be generated in the scenario.
8.3.3 Check log file for error messages
8.3.4 Stop MGW
exec dbms_mgwadm.shutdown;
8.4 Testing
· Start MGW
· Enqueue
· Check : select count(*) from mgwuser.mgw_basic_sc; — the count should increase by 1.
· Dequeue
Enqueue scripts:

SQL> !more enq.sql
declare
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
msgid RAW(16);
payload sys.mgw_basic_msg_t;
header sys.mgw_name_value_array_t;
text_body sys.mgw_text_value_t;
msg varchar2(100);

BEGIN
— Create the header. This may normally be set to NULL.
— As an example, we set a couple of WebSphere MQ message properties
— via the built-in capabilities of MGW_BASIC_MSG_T. In most cases
— these properties will not need to be set.
header := sys.mgw_name_value_array_t(
sys.mgw_name_value_t.construct_integer(‘MGW_MQ_characterSet’, ‘1208’),
sys.mgw_name_value_t.construct_integer(‘MGW_MQ_priority’, ‘7’)
);

select ‘Sample Msg:’||to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) into msg from dual;

— Create the text body.
text_body := sys.mgw_text_value_t(msg, NULL);

— Construct the message from its constituent parts.
payload := sys.mgw_basic_msg_t(header, text_body, NULL);

— Enqueue the message.
dbms_aq.enqueue(queue_name => ‘mgwuser.mgw_basic_src’,
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => payload,
msgid => msgid);
commit;
END enqueue;
/

Dequeue script:
SQL> !more deq.sql
select count(*) from mgwuser.mgw_basic_sc;

set serveroutput on

declare
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
msgid RAW(16);
payload SYS.MGW_BASIC_MSG_T;
BEGIN
— FIRST_MESSAGE
dequeue_options.navigation := 1;
dequeue_options.wait := dbms_aq.NO_WAIT;

dbms_aq.dequeue(queue_name => ‘mgwuser.mgw_basic_dest’,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => payload,
msgid => msgid);
DBMS_OUTPUT.PUT_LINE (‘Message:’ || payload.text_body.small_value);
commit;
END dequeue;
/

select count(*) from mgwuser.mgw_basic_sc;

SQL> !more cnt.sql
prompt mgwuser.mgw_basic_sc
select count(*) from mgwuser.mgw_basic_sc;

prompt mgwuser.mgw_basic_mc
select count(*) from mgwuser.mgw_basic_mc;