Updated stored procedure, sppr_sys_reservequeueitem_b
The Pega-provided stored procedure sppr_sys_reservequeueitem_b is used for various Pega processes. Over time, this stored procedure has been updated to improve its performance.
The indexes, which are displayed as text in this article, and the stored procedure, which can be downloaded from the Product Resource link under Related Content at the bottom of this article, are included in Pega 7.2.1 and later releases. If you are running a release prior to Pega 7.2.1, you can safely apply the following index changes and stored procedure code to your Pega environment. (Find the ZIP file under Related Content, Product Resource.)
Oracle, DB2 for z/OS, and DB2 for LUW (formerly UDB)
Oracle, DB2 for z/OS, and DB2 for LUW (formerly UDB)
CREATE INDEX pr_sys_queues_index1
ON data_schema.pr_sys_queues
(
PYITEMSTATUS asc,
PYAGENTNAME asc,
PYMINIMUMDATETIMEFORPROCESSING asc,
PZINSKEY asc
)
/
CREATE INDEX pr_sys_queues_index2
ON data_schema.pr_sys_queues
(
PYITEMID asc,
PYITEMSTATUS asc,
PXPROCESSINGNODEID asc
)
MSSQL
CREATE INDEX pr_sys_queues_index1
ON data_schema.pr_sys_queues
(
pyItemStatus asc,pyAgentName asc,pyMinimumDateTimeForProcessing asc,pzInsKey asc
)
go
CREATE INDEX pr_sys_queues_index2
ON data_schema.pr_sys_queues
(
pyItemId asc,pyItemStatus asc,pxProcessingNodeId asc
)
go
PostgreSQL (Postgres)
CREATE INDEX pr_sys_queues_index1
ON data_schema.pr_sys_queues
(
pyitemstatus asc,
pyagentname asc,
pyminimumdatetimeforprocessing asc,
pzinskey asc
)
;
CREATE INDEX pr_sys_queues_index2
ON data_schema.pr_sys_queues
(
pyitemid asc,
pyitemstatus asc,
pxprocessingnodeid asc
)
;