Rem
Rem $Header: mgwview.sql 09-oct-2006.14:08:17 chall Exp $
Rem
Rem mgwview.sql
Rem
Rem Copyright (c) 2001, 2006, Oracle. All rights reserved.  
Rem
Rem  NAME
Rem    mgwview.sql - create MGW views
Rem
Rem  DESCRIPTION
Rem    Creates views for the Oracle Messaging Gateway. This also creates
Rem    public synonyms and grants basic privileges pertaining to the views.
Rem
Rem  NOTES
Rem    Run this script AS SYSDBA
Rem
Rem  MODIFIED   (MM/DD/YY)
Rem  chall       10/09/06 - add CONNTYPE to MGW_GATEWAY view
Rem  chall       09/27/06 - add mgw_agent_options view
Rem  chall       03/08/06 - propagation job support (11g feature) 
Rem  chall       12/13/05 - multiple agent feature 
Rem  chall       10/04/04 - rename JMS_UNIFIED_CONNECTION to JMS_CONNECTION 
Rem  chall       09/28/04 - add JMS_UNIFIED_CONNECTION for mgw_mqseries_links 
Rem  chall       05/19/04 - MSMQ support 
Rem  chall       12/16/03 - remove obsolete (commented out) MSMQ code 
Rem  chall       07/14/03 - RAC shutdown/startup trigger issues (bug 2999689)
Rem  chall       11/05/02 - move view grant and synonym to be after create
Rem  chall       09/11/02 - modify MQSeries link view for JMS interface
Rem  chall       08/28/02 - mods needed for 10i admin changes
Rem
Rem ==========================================================================

--
-- mgw_gateway
--
-- This view lists gateway status and configuration information.
--

CREATE OR REPLACE VIEW mgw_gateway
AS
SELECT 
      -- agent status
    case when gw.agent_status = 0 then 'NOT_STARTED'
         when gw.agent_status = 1 then 'START_SCHEDULED'
         when gw.agent_status = 2 then 'STARTING'
         when gw.agent_status = 3 then 'INITIALIZING'
         when gw.agent_status = 4 then 'RUNNING'
         when gw.agent_status = 5 then 'SHUTTING_DOWN'
         when gw.agent_status = 6 then 'BROKEN'
         end AGENT_STATUS,
      -- agent ping status; no ping (delay) if agent not yet started
    case when gw.agent_status >= 0 AND gw.agent_status <=1 then ''
         when mgwi_admin.ping(gw.agent_name,3) = 1 then 'REACHABLE'
         else 'UNREACHABLE'
         end AGENT_PING,
      -- queued job used to start gateway agent
    gw.agent_job AGENT_JOB,
      -- agent user and database (connect string)
    gw.agent_user AGENT_USER,
    gw.agent_database AGENT_DATABASE,
      -- info about last gateway agent error
    gw.error_time LAST_ERROR_DATE,
    substr(to_char(gw.error_time, 'HH24:MI:SS'), 1, 8) LAST_ERROR_TIME,
    gw.error_message LAST_ERROR_MSG,
      -- misc config information
    gw.max_connections MAX_CONNECTIONS,
    gw.max_memory MAX_MEMORY,
    gw.max_threads MAX_THREADS,
    gw.agent_instance AGENT_INSTANCE,
    gw.agent_start_time AGENT_START_TIME,
    decode(bitand(gw.flags, 1), 0, 'JDBC_OCI', 1, 'JDBC_THIN', NULL)
        CONNTYPE,
    gw.agent_name AGENT_NAME,
    gw.service SERVICE,
    gw.initfile INITFILE,
    gw.comments COMMENTS
FROM mgw$_gateway gw
WITH READ ONLY
/

COMMENT on TABLE mgw_gateway IS 
'Messaging Gateway status and configuration information'
/

grant select on mgw_gateway to MGW_ADMINISTRATOR_ROLE;
create or replace public synonym mgw_gateway for mgw_gateway;

--
-- mgw_agent_options
--
-- This view lists supplemental agent options.
--

CREATE OR REPLACE VIEW mgw_agent_options
AS
SELECT 
      -- agent name
    ao.agent_name AGENT_NAME,
      -- option/property name
      -- property type/category
    case when ao.type = 1 then 'JAVA_SYSTEM_PROP'
         else ''
         end TYPE,
    ao.name NAME,
      -- property value; use placeholder string if encrypted
    case when ao.encrypted = 0 then ao.value
         else '<<ENCRYPTED>>'
         end VALUE,
      -- is value encrypted?
    case when ao.encrypted = 0 then 'FALSE'
         else 'TRUE'
         end ENCRYPTED
FROM mgw$_agent_options ao
WITH READ ONLY
/

COMMENT on TABLE mgw_agent_options IS 
'Messaging Gateway agent options'
/

grant select on mgw_agent_options to MGW_ADMINISTRATOR_ROLE;
create or replace public synonym mgw_agent_options for mgw_agent_options;

--
-- mgw_links
--
-- This view lists all the messaging system links.
--

CREATE OR REPLACE VIEW mgw_links
AS
SELECT ml.link_name LINK_NAME,
       case when ml.link_type = 1 then 'MQSERIES'
            when ml.link_type = 2 then 'TIBRV'
            when ml.link_type = 3 then 'MSMQ'
            end LINK_TYPE,
       ml.link_comment LINK_COMMENT,
       ml.agent_name AGENT_NAME
FROM mgw$_links ml
WITH READ ONLY
/

COMMENT on TABLE mgw_links IS 
'Links to non-Oracle messaging systems'
/

grant select on mgw_links to MGW_ADMINISTRATOR_ROLE;
create or replace public synonym mgw_links for mgw_links;

--
-- mgw_mqseries_links
--
-- This view lists all the MQSeries messaging system links.
--

CREATE OR REPLACE VIEW mgw_mqseries_links
AS
SELECT mp.link_name LINK_NAME,
       mp.queue_manager QUEUE_MANAGER,
       mp.hostname HOSTNAME,
       mp.port     PORT,
       mp.channel  CHANNEL,
       case mp.interface_type 
         when 1 then  'BASE_JAVA'
         when 8 then  'JMS_QUEUE_CONNECTION'
         when 9 then  'JMS_TOPIC_CONNECTION'
         when 10 then 'JMS_CONNECTION'
         else 'UNKNOWN'
         end INTERFACE_TYPE,
       mp.max_connections MAX_CONNECTIONS,
       mp.inbound_log_queue INBOUND_LOG_QUEUE,
       mp.outbound_log_queue OUTBOUND_LOG_QUEUE,
       ml.link_options OPTIONS,
       ml.link_comment LINK_COMMENT,
       ml.agent_name AGENT_NAME
FROM mgw$_mqseries_links mp, mgw$_links ml
WHERE mp.link_name = ml.link_name
WITH READ ONLY
/

COMMENT on TABLE mgw_mqseries_links IS 
'Link information to MQSeries messaging systems'
/

grant select on mgw_mqseries_links to MGW_ADMINISTRATOR_ROLE;
create or replace public synonym mgw_mqseries_links for mgw_mqseries_links;

--
-- mgw_tibrv_links
--
-- This view lists all the TIB/Rendezvous messaging system links.
--

CREATE OR REPLACE VIEW mgw_tibrv_links
AS
SELECT mp.link_name LINK_NAME,
       mp.service SERVICE,
       mp.daemon DAEMON,
       mp.network NETWORK,
       mp.cm_name CM_NAME,
       mp.cm_ledger CM_LEDGER,
       ml.link_options OPTIONS,
       ml.link_comment LINK_COMMENT,
       ml.agent_name AGENT_NAME
FROM mgw$_tibrv_links mp, mgw$_links ml
WHERE mp.link_name = ml.link_name
WITH READ ONLY
/

COMMENT on TABLE mgw_tibrv_links IS 
'Link information to TIB/Rendezvous messaging systems'
/

grant select on mgw_tibrv_links to MGW_ADMINISTRATOR_ROLE;
create or replace public synonym mgw_tibrv_links for mgw_tibrv_links;

--
-- mgw_msmq_links
--
-- This view lists all the Microsoft MSMQ messaging system links.
--

CREATE OR REPLACE VIEW mgw_msmq_links
AS
SELECT mp.link_name LINK_NAME,
       case mp.transactional 
         when 1 then 'TRANSACTIONAL'
         when 2 then 'NON_TRANSACTIONAL'
         else 'UNKNOWN'
         end TRANSACTIONAL,
       ml.link_options OPTIONS,
       ml.link_comment LINK_COMMENT,
       ml.agent_name AGENT_NAME
FROM mgw$_msmq_links mp, mgw$_links ml
WHERE mp.link_name = ml.link_name
WITH READ ONLY
/

COMMENT on TABLE mgw_msmq_links IS 
'Link information to MSMQ messaging systems'
/

grant select on mgw_msmq_links to MGW_ADMINISTRATOR_ROLE;
create or replace public synonym mgw_msmq_links for mgw_msmq_links;

--
-- mgw_foreign_queues
--
-- This view lists all the registered foreign queues.
--

CREATE OR REPLACE VIEW mgw_foreign_queues
AS
SELECT fq.name NAME,
       fq.link_name LINK_NAME,
       fq.provider_queue PROVIDER_QUEUE,
       case when fq.domain = 1 then 'QUEUE'
            when fq.domain = 2 then 'TOPIC'
            else ''
         end DOMAIN,
       fq.options OPTIONS,
       fq.queue_comment QUEUE_COMMENT
FROM mgw$_foreign_queues fq
WITH READ ONLY
/

COMMENT on TABLE mgw_foreign_queues IS 
'Registered queues of non-Oracle messaging systems'
/

grant select on mgw_foreign_queues to MGW_ADMINISTRATOR_ROLE;
create or replace public synonym mgw_foreign_queues for mgw_foreign_queues;

--
-- mgw_subscribers
--
-- This view lists all the propagation subscribers.
--

CREATE OR REPLACE VIEW mgw_subscribers
AS
SELECT sub.subscriber_id SUBSCRIBER_ID,
       case when sub.prop_type = 1 then 'OUTBOUND'
            when sub.prop_type = 2 then 'INBOUND'
            end PROPAGATION_TYPE,
       sub.queue_name QUEUE_NAME,
       sub.destination DESTINATION,
       sub.rule RULE,
       sub.transformation TRANSFORMATION,
       sub.exception_queue EXCEPTION_QUEUE,
         -- subscriber status; for now base on config_state only
       case when sub.config_state = 3 then 'DELETE_PENDING'
            else 'ENABLED'
            end STATUS,
       sub.num_fails FAILURES,
       sub.err_time LAST_ERROR_DATE,
       substr(to_char(sub.err_time, 'HH24:MI:SS'), 1, 8) LAST_ERROR_TIME,
       sub.err_message LAST_ERROR_MSG,
       sub.curr_prop_msgs PROPAGATED_MSGS,
       sub.curr_exq_msgs EXCEPTIONQ_MSGS,
       case bitand(sub.flags, 1) 
         when 1 then 'JMS'
         else 'NATIVE'
         end PROP_STYLE,
       sub.options OPTIONS
FROM mgw$_subscribers sub
WITH READ ONLY
/

COMMENT on TABLE mgw_subscribers IS 
'Propagation subscribers for Messaging Gateway'
/

grant select on mgw_subscribers to MGW_ADMINISTRATOR_ROLE;
create or replace public synonym mgw_subscribers for mgw_subscribers;

--
-- mgw_schedules
--
-- This view lists all the propagation schedules.
--
-- Note: Duration labeled PROPAGATION_WINDOW to mirror AQ view.
--

CREATE OR REPLACE VIEW mgw_schedules
AS
SELECT sch.schedule_id SCHEDULE_ID,
       case when sch.prop_type = 1 then 'OUTBOUND'
            when sch.prop_type = 2 then 'INBOUND'
            else 'UNKNOWN'
            end PROPAGATION_TYPE,
       sch.source SOURCE,
       sch.destination DESTINATION,
       sch.start_time START_DATE,
       substr(to_char(sch.start_time, 'HH24:MI:SS'), 1, 8) START_TIME,
       sch.duration PROPAGATION_WINDOW,
       sch.next_time NEXT_TIME,
       sch.latency LATENCY,
       case when sch.state = 0 then 'Y'
            when sch.state = 1 then 'N'
            else ''
            end SCHEDULE_DISABLED
FROM mgw$_schedules sch
WITH READ ONLY
/

COMMENT on TABLE mgw_schedules IS 
'Propagation schedules for Messaging Gateway'
/

grant select on mgw_schedules to MGW_ADMINISTRATOR_ROLE;
create or replace public synonym mgw_schedules for mgw_schedules;

--
-- mgw_jobs
--
-- This view lists all new-style and old-style propagation jobs.
--

CREATE OR REPLACE VIEW mgw_jobs
AS
SELECT sub.subscriber_id JOB_NAME,
       case when sub.prop_type = 1 then 'OUTBOUND'
            when sub.prop_type = 2 then 'INBOUND'
            else 'UNKNOWN'
            end PROPAGATION_TYPE,
       sub.queue_name SOURCE,
       sub.destination DESTINATION,
       sub.rule RULE,
       sub.transformation TRANSFORMATION,
       sub.exception_queue EXCEPTION_QUEUE,
       sch.latency POLL_INTERVAL,
       sub.options OPTIONS,
       case when sch.state = 0 then 'FALSE'
            when sch.state = 1 then 'TRUE'
            else ''
            end ENABLED,
       lk.agent_name AGENT_NAME,
       sub.link_name LINK_NAME,
       case bitand(sub.flags, 1) 
         when 1 then 'JMS'
         else 'NATIVE'
         end PROP_STYLE,
       case when sub.config_state = 3 then
            decode(bitand(sub.flags, 4), 
                   4, 'DELETE_PENDING', 
                   'SUBSCRIBER_DELETE_PENDING')
            when sub.num_fails >= 16 then 'FAILED'
            when sub.num_fails > 0 AND sub.num_fails < 16 then 'RETRY'
            else 'READY'
            end STATUS,
       sub.curr_prop_msgs PROPAGATED_MSGS,
       sub.curr_exq_msgs EXCEPTIONQ_MSGS,
       sub.num_fails FAILURES,
       sub.err_message LAST_ERROR_MSG,
       sub.err_time LAST_ERROR_DATE,
       substr(to_char(sub.err_time, 'HH24:MI:SS'), 1, 8) LAST_ERROR_TIME,
       sub.comments COMMENTS
FROM mgw$_subscribers sub, mgw$_schedules sch, mgw$_links lk 
WHERE sub.prop_type = sch.prop_type AND sub.queue_name = sch.source AND 
      sub.destination = sch.destination AND sub.link_name = lk.link_name
WITH READ ONLY
/

COMMENT on TABLE mgw_jobs IS 
'Propagation jobs for Messaging Gateway'
//

grant select on mgw_jobs to MGW_ADMINISTRATOR_ROLE;
create or replace public synonym mgw_jobs for mgw_jobs;

Rem === End file: mgwview.sql ================================================