TriniDBA

Problem:

After running the SYSAUDIT report, the following errors appear under Integration Broker Audits:

(IBRK-10): IB Routing referencing invalid handlers:
Routing On Send Handler On Receive Handler
 - ------ --------------- ------------------
 ~GENERATED~17288 ACK
 1 Total Rows

Solution:

Log on to SQL Server Management Studio and run the following queries against fsdev:

select * from PSIBRTNGDEFN where ROUTINGDEFNNAME = '~GENERATED~17288'
select * from PSIBRTNGDEFN where ONRCVHDLRNAME = 'ACK'
select ONRCVHDLRNAME, count(*) from PSIBRTNGDEFN group by ONRCVHDLRNAME
select * from PSSRVHDLRRCV_VW

The outputs were:

ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 ~GENERATED~17288 2005-10-21 00:00:00.000 6 I ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-30 11:38:38.000 VP1 PPT GENERATED NULL
(1 row(s) affected)
ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 ~GENERATED~17288 2005-10-21 00:00:00.000 6 I ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-30 11:38:38.000 VP1 PPT GENERATED NULL
(1 row(s) affected)
ONRCVHDLRNAME COUNT(*)
 ------------------------------ -----------
 1500
 ACK 1
(2 row(s) affected)
IB_OPERATIONNAME ONRCVHDLRNAME DESCR
 ------------------------------ ------------------------------ ---------------------------
 CREATE_WORKLIST_ITEM OnAckForMarkedWorkedResp Create worklist entry
(1 row(s) affected)

On fsdmo, ran the following queries:

select * from PSIBRTNGDEFN where ROUTINGDEFNNAME = '~GENERATED~17288'
select * from PSIBRTNGDEFN where ONRCVHDLRNAME = 'ACK'
select ONRCVHDLRNAME, count(*) from PSIBRTNGDEFN group by ONRCVHDLRNAME
select * from PSSRVHDLRRCV_VW

The outputs were:

ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 ~GENERATED~17288 2005-10-21 00:00:00.000 1 A ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-18 16:04:16.000 PPLSOFT PPT GENERATED NULL
(1 row(s) affected)
ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 CREATE_WORKLIST_ITEM 2005-10-21 00:00:00.000 1 I ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-23 11:11:56.000 PPLSOFT Create worklist entry NULL
 ~GENERATED~17288 2005-10-21 00:00:00.000 1 A ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-18 16:04:16.000 PPLSOFT PPT GENERATED NULL
(2 row(s) affected)
ONRCVHDLRNAME
 ------------------------------ -----------
 1513
 ACK 2
(2 row(s) affected)
IB_OPERATIONNAME ONRCVHDLRNAME DESCR
 ------------------------------ ------------------------------ ---------------------------
 CREATE_WORKLIST_ITEM ACK
 CREATE_WORKLIST_ITEM OnAckForMarkedWorkedResp Create worklist entry
 (2 row(s) affected)

Note that in PSSRVHDLRRCV_VW, there is a new row for ACK.

1. The simplest solution, however, is to delete the offending routing, ~GENERATED~17288.

2. In PIA, navigate to Integration Broker > Service Utilities > Service Administration. Select the Routings tab, and open the Delete section. Enter ~GENERATED~17288 into the Routing Name field and click Search. Check Select and then click Delete.

Under Results, the following message appears: Unable to delete. Routing belongs to a restricted service.

3. One way to resolve this is to change the restricted service to unrestricted. Navigate to Integration Broker > Configuration > Service Configuration. Click the Restricted Services tab, and search for PT_WORKLIST. Uncheck Restricted, and then Save.

4. Determine the field that is used as the lookup. Navigate to Integration Broker > Integration Setup > Routings. In the Find an Existing Value tab, in Routing Name, enter ~GENERATED~17288 and click Search.

5. Type Ctrl + J and note the Page name: IB_ROUTINGDEFN

6. On GL-WEB-APP, launch Application Designer and click Insert Definitions into Project. Choose Page and enter IB_ROUTINGDEFN. Choose IB_ROUTINGDEFN and click Close.

7. Double-click IB_ROUTINGDEFN. Right-click on the OnReceive Handler field and choose View Definition. On the ONRCVHDLRNAME field, right click and choose Record Field Properties. Click the Edits tab and note the Prompt Table: PSSRVHDLRRCV_VW.

8. In Management Studio, for the fsdev database, expand the Views navigator and locate PSSRVHDLRRCV_VW. Right click on PSSRVHDLRRCV_VW, choose Script View as, choose CREATE To, and click New Query. Note the command to create the view:

CREATE VIEW [dbo].[PSSRVHDLRRCV_VW] (IB_OPERATIONNAME, ONRCVHDLRNAME, DESCR) AS SELECT IB_OPERATIONNAME , HANDLERNAME , DESCR FROM PSOPRHDLR WHERE HANDLERID = 'RECV'

9. In PIA, navigate to Integration Broker > Integration Setup > Service Operations. In the Find Service Operation tab, in the Service Operation field, enter CREATE_WORKLIST_ITEM. Then click Search. Click CREATE_WORKLIST_ITEM to view it.

10. If the record is restricted, you will not be able to enter new Handlers. See step 3 above to resolve.

11. Click the Handler tab. Add a new Handler with the following information:
Name: ACK
Type: OnReceive
Implementation: Application Class
Status: Active

Click Details and add the following information:

Package Name: PT_WF_WORKLIST
Path: : [that is, only a colon must be entered]
Class ID: OnAckForMarkedWorkedResp
Method: OnAckReceive

12. Click Save.