All About SOA backend DB Queries

ORACLE SOA USEFUL SQL QUERIES

Get payload by MSG ID - ESB

select consumer_name,dbms_lob.substr(a.user_data.text_lob,4000,1) as first_part, dbms_lob.substr(a.user_data.text_lob,4000,4001) as second_part from aq$esb_java_deferred a where msg_id = '780268E7F0852AEAE0401403D8DA3175'

Query to list inbound adapter services ESB

select sys.name as system, e.name as service from wf_systems sys,wf_events e, esb_service_type s where sys.guid = e.system_guid and e.service_type_guid = s.guid and e.type = 'GROUP' and s.java_class_name = 'oracle.tip.esb.server.service.impl.inadapter.InboundAdapterService'

Remove the top message in the queue for a specific subscriber

1. This action should NEVER be taken without approval from L3 support. 2. This action should NEVER be taken without full analysis of the issue and that this is the right course

Basically we used the built-in procedure dbms_aqadm.purge_queue_table, this has 3 parameters as follows:

queue_table which is the name of the queue table (like esb_java_deferred) purge_condition which is a string in the format of "a SQL WHERE clause, and it is case-sensitive. The condition is based on the columns of aq$queue_table view" purge_options which identifies other options relevant to the purge activity, essentially whether to block the queue while purging

to remove the top message in the queue for a specific subscriber you would first have to determine the message ID of the message in the queue then call the function as in the following example:

DECLARE
po_t dbms_aqadm.aq$_purge_options_t;
BEGIN
po_t.block := false;
dbms_aqadm.purge_queue_table('GEHC_BAM_REGPACKT', 'msg_id=''8057B6D680F3031EE0401403BBDA4396''', po_t);
END;
/

#Get Payload

1. SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(log_message,2000, 1)) FROM LOG_OBJECTS
WHERE dbms_lob.INSTR(log_message, utl_raw.cast_to_raw('21944772~901775-SPECIAL_DTX-3.1_G~11284'),1,1) > 0

2. select to_char(ml.invoke_time,'DD-MON-YY HH:Mi:ss am') "Invoke Time" , lo.log_message, ml.* from log_objects lo, messagelogs ml
where lo.logid = ml.logid and ml.invoke_time
BETWEEN to_date('21-Mar-10 14:00:00','DD-MON-YY HH24:MI:SS')
AND to_date('21-Mar-10 14:15:00','DD-MON-YY HH24:MI:SS') AND serviceid='SID0003114' order by 1 desc

SELECT to_char(ml.invoke_time,'DD-MON-YY HH:Mi:ss am') "Invoke Time" , UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(lo.log_message,2000, 1))||UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(lo.log_message,2000, 2001)) AS Payload1,
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(lo.log_message,2000, 4001)) Payload2 , ml.SERVICEID,ml.LOG_TYPE
from log_objects lo, messagelogs ml where lo.logid = ml.logid
and ml.invoke_time
BETWEEN to_date('21-Mar-10 14:00:00','DD-MON-YY HH24:MI:SS')
AND to_date('21-Mar-10 14:15:00','DD-MON-YY HH24:MI:SS') AND ml.serviceid='SID0003114'
--and dbms_lob.INSTR(lo.log_message, utl_raw.cast_to_raw('310005-002-AGM'),1,1) > 0
-- ml.LOG_TYPE='Request'
ORDER BY 1 DESC

Java Deferred Q

/* Java Deferred queue - Consumers, State, Count */

select consumer_name, msg_state, count(*) from aq$esb_java_deferred group by consumer_name, msg_state;

/* Java Deferred queue - List of messages by enqueue time for a specific consumer in a specific state */

select a.queue, a.consumer_name, a.msg_state, a.enq_timestamp, a.retry_count, a.msg_id from aq$esb_java_deferred a where consumer_name = 'consumer from previous query' and msg_state = 'state from previous query' order by enq_timestamp;

/* Java Deferred queue - Size of a specific message in queue */

select a.queue, a.consumer_name, a.msg_state, a.enq_timestamp, length(a.user_data.text_lob) msg_size, a.retry_count, a.msg_id from aq$esb_java_deferred a where msg_id = 'message id from previous query';

 ESB AQ Creation

CREATE OR REPLACE PROCEDURE create_queue (qname VARCHAR2)
AS
  qtablename VARCHAR2(110) := qname;

BEGIN

  BEGIN
    dbms_aqadm.stop_queue (queue_name => qname);
  EXCEPTION
  WHEN OTHERS THEN
    null;
  END;

  BEGIN
    dbms_aqadm.drop_queue (queue_name => qname);
  EXCEPTION
  WHEN OTHERS THEN
    null;
  END;

  BEGIN
    dbms_aqadm.drop_queue_table (Queue_table => qtablename);
  EXCEPTION
  WHEN OTHERS THEN
    null;
  END;

  dbms_aqadm.create_queue_table(Queue_table => qtablename,
                                Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
                                multiple_consumers => true,
                                primary_instance => 1,
                                secondary_instance=> 2,
                                compatible => '10.2');
  dbms_aqadm.create_queue (Queue_name => qname,
                           Queue_table => qtablename);
  dbms_aqadm.start_queue(qname);
END;
/
BEGIN
  create_queue('ESB_JAVA_DEFERRED');
  create_queue('ESB_CONTROL');
  create_queue('ESB_ERROR');
  create_queue('ESB_ERROR_RETRY');
  create_queue('ESB_MONITOR');
END;
/

select queue_table, primary_instance, secondary_instance, compatible
from all_queue_tables
where owner='ORAESB'

HP Systinet3 No CVS details Export

select name_val as "Service", c_soafcvsproject_val as "CVS Project", c_implementationtechnology_val as "Tech" from ryga_implementation where c_implementationtechnology_val in ('BPEL', 'ESB', 'SOAf') and c_soafcvsproject_val is null order by "Service"

HP Systinet SSO expiry export 

select 'Production' "SSOType", org.name_val as "System", org.c_productionssoid_val as "SSO ID", to_char(to_timestamp_tz('1970-01-01 utc', 'yyyy-mm-dd tzr') + numtodsinterval(org.c_prdctnsoidexprdt_val /1000,'second'), 'DD-MON-YYYY') as "Expiry Date", to_timestamp_tz('1970-01-01 utc', 'yyyy-mm-dd tzr') + numtodsinterval(org.c_prdctnsoidexprdt_val /1000,'second') "Expiry Timestamp", org.c_ssoidowner_val "SSO ID Owner"

from ryga_contact org
                INNER JOIN ry_resource orgRes ON
                             ( orgRes.id=org.id and
                               orgRes.m_deleted='0')
where org.discriminator = 'organizationUnit' and
       (to_timestamp_tz('1970-01-01 utc', 'yyyy-mm-dd tzr') + numtodsinterval(org.c_prdctnsoidexprdt_val /1000,'second')) < (systimestamp + interval '30' DAY)

union
select 'Non-Production' "SSOType", org.name_val  as "System", org.c_nonproductionssoid_val as "SSO ID", to_char(to_timestamp_tz('1970-01-01 utc', 'yyyy-mm-dd tzr') + numtodsinterval(org.c_nprdctnsoidexprdt_val /1000,'second'), 'DD-MON-YYYY') as "Expiry Date", to_timestamp_tz('1970-01-01 utc', 'yyyy-mm-dd tzr') + numtodsinterval(org.c_nprdctnsoidexprdt_val /1000,'second') "Expiry Timestamp", org.c_ssoidowner_val  "SSO ID Owner"

from ryga_contact org
                INNER JOIN ry_resource orgRes ON
                             ( orgRes.id=org.id and
                               orgRes.m_deleted='0')
where org.discriminator = 'organizationUnit' and
       (to_timestamp_tz('1970-01-01 utc', 'yyyy-mm-dd tzr') + numtodsinterval(org.c_nprdctnsoidexprdt_val /1000,'second')) < (systimestamp + interval '30' DAY)

order by "SSOType" desc, "Expiry Timestamp";

# BPEL Transaction Check

SELECT count(1) from cube_instance where MODIFY_DATE between to_date ('2008/09/29 00:00:00', 'yyyy/mm/dd HH24:MI:SS')
    AND to_date ('2008/09/29 04:00:00', 'yyyy/mm/dd HH24:MI:SS') and state=5 and process_id='Process_eMatrixPLMCS_PDM_ProductDataTransfer';

STATE CODE----------STATE NAME
0----------------------Initiated state
1----------------------Open,Running state
2----------------------Open, Suspended state
3----------------------Open,Faulted state
4----------------------Closed,Pending-Cancel state
5----------------------Closed,Completed state
6----------------------Closed,faulted state
7----------------------Closed,Cancelled state
8----------------------Closed,aborted state
9----------------------Closed,Stale state


SELECT * from cube_instance where MODIFY_DATE between to_date ('2008/10/24 00:00:00', 'yyyy/mm/dd HH24:MI:SS')
    AND to_date ('2008/10/24 07:09:00', 'yyyy/mm/dd HH24:MI:SS')
     and state=5 and process_id='Process_eMatrixPLMCS_PDM_ProductDataTransfer';
    Process_eMatrixPLMCS_PDM_ProductDataTransfer
    select sysdate from dual--10/24/2008 7:09:11 AM
   
select instance_key, bpel_process_name, state_text, to_char(creation_date,'DD-Mon-YY HH24:mi:ss'), to_char(modify_date,'DD-Mon-YY HH24:mi:ss')
        from orabpel.bpel_process_instances
        where (state = 1) and modify_date > (sysdate - 2)
        order by modify_date desc
 

select count(1) from where bpel_process_name='Process_eMatrixPLMCS_PDM_ProductDataTransfer'

Oracle ESB

select consumer_name,
dbms_lob.substr(a.user_data.text_lob,4000,1) as first_part,
dbms_lob.substr(a.user_data.text_lob,4000,4001) as second_part
from aq$esb_java_deferred a
where msg_id = '780268E7F0852AEAE0401403D8DA3175'

--The issue may be more to do with ESB RT internal queue connection and the exception stack may be misleading.

select sys.name as system, e.name as service
from wf_systems sys,
wf_events e, esb_service_type s
where sys.guid = e.system_guid
and e.service_type_guid = s.guid                           
and e.type = 'GROUP'
and s.java_class_name = 'oracle.tip.esb.server.service.impl.inadapter.InboundAdapterService'