Rem
Rem $Header: mq_adm_setup.sql 11-jul-2003.15:49:21 kbittler Exp $
Rem
Rem mq_adm_setup.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.
Rem
Rem    NAME
Rem      mq_adm_setup.sql - SQL script to configure MGW entities.
Rem
Rem    DESCRIPTION
Rem      This script sets up a "round-trip" propagation to illustrate both
Rem      outbound (AQ->MQ) and inbound (MQ->AQ) propagation. An outbound
Rem      propagation job moves messages from the AQ queue MGWUSER.MGW_BASIC_SRC
Rem      to a user supplied WebSphere MQ queue. An inbound propagation job
Rem      messages from the same user supplied WebSphere MQ queue to the AQ queue
Rem      MGWUSER.MGW_BASIC_DEST. The end result should be messages moved from
Rem      MGWUSER.MGW_BASIC_SRC to MGWUSER.MGW_BASIC_DEST.
Rem
Rem PREREQUISITES:
Rem
Rem * The following database entities are assumed:
Rem   - Users: mgwagent, mqadmin, mquser
Rem   - AQ Queues: mquser.mq_q_toto, mquser.mgw_basic_dest
Rem
Rem --------------------------------------------------------------------------
Rem
Rem This script does the following (via the dmbs_mqadmin package):
Rem
Rem * Sets the agent user connection information:
Rem   username: mgwagent
Rem   password: mgwagent
Rem   database: 
Rem
Rem * Creates an WebSphere MQ link
Rem   Link name: mqlink
Rem
Rem * Creates an WebSphere MQ foreign queue.
Rem   Name:   destq
Rem   Link:   mqlink
Rem   Native: 
Rem
Rem * Creates an OUTBOUND subscriber.
Rem   Sub id: sub_aq2mq
Rem   Source: mquser.mq_q_toto
Rem   Dest:   destq@mqlink
Rem
Rem * Creates an INBOUND subscriber.
Rem   Sub id: sub_mq2aq
Rem   Source: destq@mqlink
Rem   Dest:   mquser.mgw_basic_dest
Rem
Rem * Creates OUTBOUND schedule:
Rem   Sched id: sch_aq2mq
Rem   Source:   mquser.mq_q_toto
Rem   Dest:     destq@mqlink
Rem
Rem * Creates an INBOUND schedule.
Rem   Sub id: sch_mq2aq
Rem   Source: destq@mqlink
Rem   Dest:   mquser.mgw_basic_dest
Rem
Rem ==========================================================================
set serveroutput on size unlimited
connect mqadmin/mqadmin;

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.
--
  --:gv_mq_queue_name    := 'PHOENIX.TEST';
  --:gv_mq_queue_name    := 'PHOENIX.WLP1';
begin
  :gv_database         := 'WLINT2';
  :gv_mq_queue_manager := 'CASTOR.Z';
  --:gv_mq_queue_name    := 'PHOENIX.WLP1';
  :gv_mq_queue_name    := 'PHOENIX.TEST';
  :gv_mq_channel       := 'C.CASTOR.Z.NT_07';
  :gv_mq_host          := '172.18.224.62';
  :gv_mq_port          := 1414;
  :gv_mq_username      := NULL; -- optional
  :gv_mq_password      := NULL; -- optional
  :gv_mq_inbound_log_queue  := NULL ;
  :gv_mq_outbound_log_queue := 'PHOENIX.WLP2';
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 => 'MQAGENT', password => 'MQAGENT', 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_output.put_line(' dbms_mgwadm.create_msgsystem_link( linkname=>''mqlink'',propertie => v_prop, option=> v_options );' );
  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') );
     v_options := sys.mgw_properties( sys.mgw_property('MQ_openOptions', '32784') );
  --   v_options := sys.mgw_properties( sys.mgw_property('MQ_openOptions', '16') );

  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        => 'mquser.mq_q_toto',
      -- 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 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            =>'mquser.mq_q_toto',
      -- MGW foreign queue with link
      destination       =>'destq@mqlink');

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