Summary

There is a requirement to use Oracle Sequence in Pega stored procedure - SPPC_DATA_UNIQUEID to avoid the database contention in table 'pc_data_uniqueid'.

Error Messages

Not Applicable

Steps to Reproduce

Not Applicable

Root Cause

Slowness in unique work object ID creation because of database contention in table 'pc_data_uniqueid'.

Resolution

Perform the following local-change: 

Below is a sample SPPC_DATA_UNIQUEID based on Oracle sequence.

CREATE SEQUENCE DEFLTUNIQUEID
  MINVALUE 100010
  START WITH 100010
  INCREMENT BY 1
  CACHE 200;

CREATE SEQUENCE CUNIQUEID
  MINVALUE 100010
  START WITH 100010
  INCREMENT BY 1
  CACHE 200;

CREATE SEQUENCE WUNIQUEID
  MINVALUE 100010
  START WITH 100010
  INCREMENT BY 1
  CACHE 200;

CREATE SEQUENCE QUNIQUEID
  MINVALUE 100010
  START WITH 100010
  INCREMENT BY 1
  CACHE 200;


CREATE SEQUENCE IUNIQUEID
  MINVALUE 150000
  START WITH 150000
  INCREMENT BY 1
  CACHE 200;

CREATE SEQUENCE SUNIQUEID
  MINVALUE 100010
  START WITH 100010
  INCREMENT BY 1
  CACHE 200;

CREATE SEQUENCE QMUNIQUEID
  MINVALUE 100010
  START WITH 100010
  INCREMENT BY 1
  CACHE 200;


CREATE OR REPLACE PROCEDURE SPPC_DATA_UNIQUEID (
    ReturnKey OUT VARCHAR,
    tpyPrefix IN VARCHAR,
    tpyOrganization IN VARCHAR,
    tsuffix IN VARCHAR)
AS

--
-- $Id: sppc_data_uniqueid.sql,v 1.4 2007/09/13 23:37:37 dsila Exp $
--
-- Copyright (c) 2013  Pegasystems Inc.
-- Generate a Unique ID for a Work Item
--
-- Date         Who     RuleProID       Description of Modification
-- 17-Jan-13    werda                   Use a sequence rather than a
cursor. Accept/expect gaps between work id's--

tpxCreateDateTime         date;
tpxCreateOpName           varchar2(128);
tpxCreateOperator          varchar2(128);
tpxCreateSystemID          varchar2(32);
tpxInsName                                      varchar(128);
tpxObjClass                            varchar2(96);
tpxUpdateDateTime        date;
tpxUpdateOpName          varchar2(128);
tpxUpdateOperator         varchar2(128);
tpxUpdateSystemID         varchar2(32);
tpyLabel                                  varchar2(64);
tpyLastReservedID           number(18);
tpzInsKey                                           varchar(255);

tnewLastReservedID        number(18);
tupdater                             char(1);

begin

tpxCreateDateTime := NULL;
tpxCreateOpName := NULL;
tpxCreateOperator := NULL;
tpxCreateSystemID := NULL;
tpxInsName := upper(tpyOrganization || '!' || tpyPrefix); tpxObjClass := 'Data-UniqueID'; tpxUpdateDateTime := NULL; tpxUpdateOpName := NULL; tpxUpdateOperator := NULL; tpxUpdateSystemID := NULL; tpyLabel := NULL;
-- Select pyLastReservedID into tpyLastReservedID from pc_data_uniqueid where pzInsKey = tpzInsKey for update;
-- newLastReservedID := tpyLastReservedID + 1; CASE tpyPrefix
                When 'S-' THEN select SUNIQUEID.nextval into tnewLastReservedID from dual;
                When 'I-' THEN select IUNIQUEID.nextval into tnewLastReservedID from dual;
                When 'Q-' THEN select QUNIQUEID.nextval into tnewLastReservedID from dual;
                When 'QM-' THEN           select QMUNIQUEID.nextval into
tnewLastReservedID from dual;
                When 'W-' THEN select WUNIQUEID.nextval into tnewLastReservedID from dual;
                When 'C-' THEN select CUNIQUEID.nextval into tnewLastReservedID from dual;
  ELSE select DEFLTUNIQUEID.nextval into tnewLastReservedID from dual; END CASE; ReturnKey := tpyPrefix || tnewLastReservedID || tsuffix; end;/