OracleFree referencePDF, Excel, Word exportOracle PeopleSoftAll Oracle PeopleSoft tools

PeopleSoft Integration Broker Tables and Message Status Reference

This reference lists the PeopleSoft Integration Broker tables behind the Service Operations Monitor, the message statuses you see there, and what to do about each. It covers PeopleTools 8.5x and 8.6x.

Download:

Row type	Table / status	Contents / meaning	Key or status column	Action or purge note
Table	PSAPMSGPUBHDR	Asynchronous publication header: one row per published message (transaction)	IBTRANSACTIONID; status in PUBSTATUS	Shown on the Asynchronous Services monitor; archive or purge with APPMSGARCH
Table	PSAPMSGPUBCON	Publication contracts: delivery of a published message to each target node	IBTRANSACTIONID; status in PUBCONSTATUS	Errors here are usually connection or target node problems
Table	PSAPMSGSUBCON	Subscription contracts: processing of an inbound message by a handler	IBTRANSACTIONID; status in SUBCONSTATUS	Errors here are usually handler PeopleCode or data problems
Table	PSIBLOGHDR	Synchronous service operation log header	GUID	Populated when synchronous logging is enabled; grows quickly at high log levels
Table	PSIBLOGDATA	Synchronous message request and response data	GUID	May contain payload data; restrict access and purge on a schedule
Table	PSIBLOGERR	Synchronous error messages	GUID	First place to look for failed synchronous calls
Table	PSOPERATION	Service operation definitions	IB_OPERATIONNAME	Whether the operation is synchronous or asynchronous and its versions
Table	PSSERVICE	Service definitions	IB_SERVICENAME	Groups service operations
Table	PSMSGNODEDEFN	Node definitions	MSGNODENAME	Default local node, authentication option and node status
Table	PSQUEUEDEFN	Queue definitions for asynchronous operations	QUEUENAME	Paused queues stop delivery; check queue status first
Table	PSIBRTNGDEFN	Routing definitions	ROUTINGDEFNNAME	Inactive or missing routing is a common cause of messages not going out
Status	New	Message or contract created, waiting to be processed	Status column of the header or contract	Many in New: check that the pub/sub domain and dispatchers are running
Status	Started	Picked up by a dispatcher	Status column	Long time in Started can mean a stuck handler
Status	Working	Being processed by a handler	Status column	Check application server handler processes if it does not progress
Status	Done	Processed successfully	Status column	Eligible for archive and purge
Status	Error	Processing failed	Status column	Read the error, fix the cause, then resubmit from the monitor
Status	Retry	Failed delivery that will be tried again	Status column	Usually a target node that is down or unreachable
Status	Timeout	Processing exceeded the allowed time	Status column	Check target response time and handler performance
Status	Edited	Message data was edited in the monitor	Status column	Resubmit after editing; record who changed it
Status	Cancelled	Cancelled by an administrator	Status column	Not processed; confirm the business impact
Status	Hold	Held and not processed until released	Status column	Release or cancel from the monitor once the reason for the hold is resolved
Tip	Dispatchers and handlers	PSBRKDSP and PSBRKHND (broker), PSPUBDSP and PSPUBHND (publication), PSSUBDSP and PSSUBHND (subscription) run in the application server pub/sub domain	Application server domain	If statuses stay New or Started, check these processes first
Tip	Resubmit or cancel	Use the Asynchronous Services monitor to resubmit or cancel operation instances, publication contracts and subscription contracts	PeopleTools, Integration Broker, Service Operations Monitor	Avoid updating status columns with SQL
Tip	Archive and purge	The APPMSGARCH Application Engine program archives and deletes processed asynchronous messages	Run on a schedule	Without it, PSAPMSG tables grow and monitor queries slow down

Status columns are numeric codes decoded by translate values. On your database run: SELECT FIELDNAME, FIELDVALUE, XLATLONGNAME FROM PSXLATITEM WHERE FIELDNAME IN ('PUBSTATUS', 'PUBCONSTATUS', 'SUBCONSTATUS') ORDER BY FIELDNAME, FIELDVALUE to get the numeric code for each status name. Table and column names apply to PeopleTools 8.48 and later Integration Broker; confirm for your release in the Integration Broker PeopleBooks.

Get an Integration Broker health query pack

Tell us your PeopleTools release and we will send checked queries for errors, retries and backlog by service operation and node.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Filter on row type. Table rows show where asynchronous publications, publication contracts, subscription contracts and synchronous logs are stored, and the key columns for joins. Status rows explain each monitor status and the usual action. Tip rows cover the processes and programs you will touch when things go wrong. Run the PSXLATITEM query in the notes once to get your numeric status codes, then write monitoring queries against PSAPMSGPUBCON and PSAPMSGSUBCON for error and retry counts by service operation.

Publication contract errors point outward, to the target
Subscription contract errors point inward, to handler code or data
Synchronous failures are in PSIBLOGERR when logging is on

How asynchronous messaging is stored

When PeopleSoft publishes an asynchronous service operation, a header row is written to PSAPMSGPUBHDR with an IBTRANSACTIONID. The publication dispatcher and handlers create a publication contract in PSAPMSGPUBCON for each routing to a target node, and deliver it. On the receiving side, each inbound message creates a subscription contract in PSAPMSGSUBCON processed by the handler PeopleCode. Queues group related messages; ordered queues process in sequence, so one error can hold the messages behind it. Synchronous calls skip these tables and are logged in PSIBLOGHDR, PSIBLOGDATA and PSIBLOGERR.

Worked example: messages stuck in New

After a weekend patch, HR job data stopped reaching a downstream system. The monitor showed hundreds of publication contracts in New for the operation. Queue status in PSQUEUEDEFN was Run, and routings were active, so the cause was not configuration. The application server pub/sub domain had been restarted without pub/sub servers enabled, so PSPUBDSP and PSPUBHND were not running. After enabling them and rebooting the domain, contracts moved through Started and Working to Done within minutes, with no resubmission needed.

Gotchas

Do not change status columns with SQL; resubmit and cancel through the monitor so headers and contracts stay consistent. Ordered queues hold later messages behind an error, so fix the oldest error first. Synchronous logging at a detailed level writes full payloads into PSIBLOGDATA, which may include personal data, so keep it off in production unless debugging and purge afterwards. Schedule APPMSGARCH so processed messages do not accumulate for years.

Fix the oldest error in an ordered queue first
Treat logged payloads as sensitive data
Schedule archive and purge from day one

Frequently Asked Questions

What are the main PeopleSoft Integration Broker tables?

Asynchronous messages are in PSAPMSGPUBHDR (publication headers), PSAPMSGPUBCON (publication contracts) and PSAPMSGSUBCON (subscription contracts), keyed by IBTRANSACTIONID. Synchronous logging uses PSIBLOGHDR, PSIBLOGDATA and PSIBLOGERR. Definitions live in PSOPERATION, PSSERVICE, PSMSGNODEDEFN, PSQUEUEDEFN and PSIBRTNGDEFN.

How do PeopleSoft Integration Broker queues work?

Asynchronous service operations are assigned to queues defined in PSQUEUEDEFN. A queue can be ordered, so messages process in sequence, or unordered for parallel processing. A paused queue stops delivery. In an ordered queue, an error blocks the later messages in the same queue until the earlier one is fixed and resubmitted or cancelled.

Where do I see Integration Broker errors?

Use the Service Operations Monitor: Asynchronous Services shows operation instances, publication contracts and subscription contracts with their status and error messages. Synchronous errors appear in the synchronous monitor when logging is on, stored in PSIBLOGERR. Application server logs for the pub/sub domain help when handlers fail to start.

How do I purge Integration Broker messages?

Run the delivered APPMSGARCH Application Engine program on a schedule to archive and delete processed asynchronous messages. Decide retention with audit before purging. For synchronous logs, keep logging levels low in production and clear old log data through the delivered options so PSIBLOGDATA does not grow without limit.

If you want an assistant that watches your Integration Broker queues and explains failures in plain English, running on your own PeopleSoft servers, we can build that with you.

Talk to an Expert Explore More Free Tools