Question
Tata Consultancy Services
IN
Last activity: 17 Feb 2021 10:12 EST
What is difference between pzInsKey & Primary key?
Also, can we use pzInsKey instead of primary key with Obj-Open for internal table?
Pegasystems Inc.
IN
pzInsKey is unique key throughout the Database while Primary key is unique for that perticular table. thats the main difference in primary key and pzInsKey
Accepted Solution
Pegasystems Inc.
US
Just to elaborate further.
Every Pega Platform internal database table in data or rulese schema contains a pzInskey column and is the primary key to the table. When we open a record from the database to get full data in the blob , rule or data instance, we use the pzInsKey as it's unique for the table .
Example: select pzpvstream from <pega_table> where pzInskey = '<key_value>'
The key(s) to a table, as defined in a class, can be made up one or more exposed properties to the table. The values become part of the pzInskey.
Take for example the Data-Admin-Operator-ID table.
Table: <pr_data_schema>.pr_operators
Primary Key at database level: pzInkey (Unique = yes)
Class Key (as defined in Class record): pyUserIdentifier
Example colum pzInskey: "DATA-ADMIN-OPERATOR-ID KOYLC@PEGA.COM"
Example column pyUserIdentifier: "koylc@pega.com"
Methods Obj-Open vs Obj-Open-By-Handle:
These two activity methods can be used to open an instance. At run time they both generate the same SQL.
When using Obj-Open you specify a class and then the property or set of properties that make up the keys as defined in class rule.
So for Obj-Open on Data-Admin-Operator-ID I would use a propertyName of .pyUserIdentifier and then a value.
Example:
Just to elaborate further.
Every Pega Platform internal database table in data or rulese schema contains a pzInskey column and is the primary key to the table. When we open a record from the database to get full data in the blob , rule or data instance, we use the pzInsKey as it's unique for the table .
Example: select pzpvstream from <pega_table> where pzInskey = '<key_value>'
The key(s) to a table, as defined in a class, can be made up one or more exposed properties to the table. The values become part of the pzInskey.
Take for example the Data-Admin-Operator-ID table.
Table: <pr_data_schema>.pr_operators
Primary Key at database level: pzInkey (Unique = yes)
Class Key (as defined in Class record): pyUserIdentifier
Example colum pzInskey: "DATA-ADMIN-OPERATOR-ID KOYLC@PEGA.COM"
Example column pyUserIdentifier: "koylc@pega.com"
Methods Obj-Open vs Obj-Open-By-Handle:
These two activity methods can be used to open an instance. At run time they both generate the same SQL.
When using Obj-Open you specify a class and then the property or set of properties that make up the keys as defined in class rule.
So for Obj-Open on Data-Admin-Operator-ID I would use a propertyName of .pyUserIdentifier and then a value.
Example:
At run time and from tracer with DB query enabled you can see the SQL:
The Obj-Open method is building the pzInsKey value for you.
The Obj-Open-By-Handle method allows you to directly provide the pzInsKey value.
Example:
The InstanceHandle in this case is all upper case: "DATA-ADMIN-OPERATOR-ID KOYLC@PEGA.COM".
The Step page has to be of the same class.
At runtime it's the same SQL.
To answer your question:
You are already using the primary key of the table, pzInkey, when using an Obj-Open step.
Hope this answers your question.
Tata Consultancy Services
IN
Thanks ChrisKoyl for reply that cleared the concept. But few queries as below
- From your example, can we use property name as pzInsKey & value as "DATA-ADMIN-OPERATOR-ID KOYLC@PEGA.COM" in the Obj-Open method? If yes, Pega internally append class name again as per your explanation, will it work?
- Can we formulate pzInsKey = Class Name + Class Key for all internal tables?
- The only article on pzInsKey describe the structure as below which contribute to my confusion. Can we say that this is limited to the PegaRules tables?
CLASSNAME CLASSKEY RULE_NAME #YYYYMMDD+T+HHMMSSTTTTMMM GMT
- Is pzInsKey and Internal Key same?
- Composite/Compound key is when we use multiple Class Keys? In this case do we need to give all keys in property list of Obj-Open? What happen if we miss to provide one of the key in property list?