Integration Broker Queues

Integration Broker Queues

• Publication Contracts

select queuename,statusstring,count(*) from psapmsgpubcon where trunc(createdttm) = trunc(sysdate) group by queuename,statusstring

• Subsription Contracts

select queuename,statusstring,count(*) from psapmsgsubcon where trunc(createdttm) = trunc(sysdate) group by queuename,statusstring order by 1

For a date range.

• Publication Contracts

select queuename,statusstring,count(*) from psapmsgpubcon where trunc(createdttm) between trunc(sysdate-

• Subsription Contracts

select queuename,statusstring,count(*) from psapmsgsubcon where trunc(createdttm) between trunc(sysdate-

______________________________________________________________________________________________________________________

Domain Status

SELECT DOMAIN_STATUS INTO DSTAT FROM PSAPMSGDOMSTAT

______________________________________________________________________________________________________________________

Domain is active, but messages not being processed

For publication messages

SELECT IBTRANSACTIONID,QUEUENAME,CREATEDTTM,PUBLISHTIMESTAMP,STATUSSTRING ,

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)

|| ' HOURS ' ||

floor((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

round((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600 -

(floor((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600)/60)*60) ))

|| ' SECS ' time_difference

FROM PSAPMSGPUBCON

WHERE STATUSSTRING IN ('NEW','ERROR') AND trunc(createdttm) = trunc(sysdate) ORDER BY 4

For subscription messages

Looking at the above query and this query we can find out if the domain is active but still messages are not being proecessed.

SELECT IBTRANSACTIONID,QUEUENAME,CREATEDTTM,PUBLISHTIMESTAMP,STATUSSTRING ,

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)

|| ' HOURS ' ||

floor((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

round((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600 -

(floor((((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60) -

floor(((CREATEDTTM-PUBLISHTIMESTAMP)*24*60*60)/3600)*3600)/60)*60) ))

|| ' SECS ' time_difference

FROM PSAPMSGSUBCON

WHERE STATUSSTRING IN ('NEW','ERROR') AND trunc(createdttm) = trunc(sysdate) ORDER BY 4

Activate Domain Status

**** Be Sure that PUB/SUB processes are up and running before using this script ****

SET SERVEROUT ON;

SPOOL D:\APPS\MONITORDOMAIN\MONITORDOMAIN.LOG;

DECLARE

DSTAT VARCHAR2(1) := 'T';

BEGIN

BEGIN

SELECT DOMAIN_STATUS INTO DSTAT FROM PSAPMSGDOMSTAT WHERE DOMAIN_STATUS = 'I';

EXCEPTION

WHEN NO_DATA_FOUND THEN

DSTAT:='A';

WHEN TOO_MANY_ROWS THEN

DSTAT:='I';

END;

IF DSTAT ='I' THEN

UPDATE PSIBFAILOVER SET IB_FAILOVER_FLAG = 1;

UPDATE PSAPMSGDSPSTAT SET DSPSTATUS = 0 , STATUSSTRING = 'ACT' WHERE STATUSSTRING='INACT';

UPDATE PSAPMSGDOMSTAT SET DOMAIN_STATUS = 'A' WHERE DOMAIN_STATUS = 'I';

END IF;

DBMS_OUTPUT.PUT_LINE(DSTAT);

END;

/

SPOOL OFF;

EXIT;

Send mail from oracle db

CREATE OR REPLACE PROCEDURE SEND_MAIL (

mesg_to varchar2,

mesg_subject varchar2,

mesg_text varchar2 )

IS

c utl_smtp.connection;

rc integer;

mesg_from varchar2(50) := 'MYDEVDB';

mailhost VARCHAR2(30) := 'name of your mailhost';

BEGIN

c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25

utl_smtp.helo(c, mailhost);

utl_smtp.mail(c, msg_from);

utl_smtp.rcpt(c, msg_to);

utl_smtp.data(c,'From: MYDEVDB Oracle Database' || utl_tcp.crlf ||

'To: ' || msg_to || utl_tcp.crlf ||

'Subject: ' || msg_subject ||

utl_tcp.crlf || msg_text);

utl_smtp.quit(c);

EXCEPTION

WHEN UTL_SMTP.INVALID_OPERATION THEN

dbms_output.put_line(' Invalid Operation in Mail attempt

using UTL_SMTP.');

WHEN UTL_SMTP.TRANSIENT_ERROR THEN

dbms_output.put_line(' Temporary e-mail issue - try again');

WHEN UTL_SMTP.PERMANENT_ERROR THEN

dbms_output.put_line(' Permanent Error Encountered.');

END;

-------------------------------------------

Guideline taken from internet.

Portal Strucure

Portal Structure

PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component.

PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.