Discussion
ANZ Bank
IN
Last activity: 26 Jun 2015 9:33 EDT
Different tables which gets updated when we run BIX Extract from a command line
We are running BIX extract using command line from a .sh file. We were earlier using the DB user which is being used by application server. Due to some reasons, we were asked not to use that user any more and we have to give requirements to DBA.
Can anyone help me identify what all tables will get updated when we run BIX extract using command line.
We are using 7.1.6 for some applications and 7.1.7 for few.
Pegasystems Inc.
US
The details of the BIX product and associated FAQs including information on the extract may be found with the product documentaton. Please see:
https://collaborate.pega.com/question/trying-work-bix-business-intelligence-exchange-pega-718-pdn-we-need User Guide and Release Notes
https://docs-previous.pega.com/business-intelligence-exchange-bix/bix-design-models
Lu
The Pega Academy Support Team
ANZ Bank
IN
Hi Lu,
Thanks for your response. The links which were provided are very informative.
I was looking for what are the different PRPC tables on which there will be insert/delete operations will be performed other than pr_extract_time table. When we run BIX extract using command line. The reason I want this information is I would like to know if there would be any deadlock kind of a situation when I use a different DB user (other than the one which is configured in my applicaiton server) and run the extract.
Thanks,
Chaitanya
PEG
IN
Hi Chaitanya,
When we run BIX, it won't perform any insert/delete operations on tables. BIX will issue a select command to fetch data. Apart from pr_extract_time table few log related tables will be affected like pr_log_usage.
ANZ Bank
IN
Hi Gollg,
I agree, that is what is understood. But, when we have created a DB user who got only read only privileges on all the tables in DATA and RULES schema and insert privileges for pr_extract_time table, we were getting below errors. Which clearly shows BIX requires INSERT & DELETE & UPDATE privileges apart from SELECT privileges on many tables. We havent tried to discover the number of tables on which these kind of operations are performed. Instead we have given the above privileges on all the tables in DATA and RULES schema. Not only these operations. BIX even demands EXECUTE privileges on to certain stored procs that comes with the product.
SQL: update ***_DATA.pr_data_admin set PXCREATEDATETIME = ? , PXCREATEOPERATOR = ? , PXCREATEOPNAME = ? , PXCREATESYSTEMID = ? , PXINSNAME = ? , PXOBJCLASS = ? , PXPRODUCTNAME = ? , PXPRODUCTPATCHVERSION = ? , PXPRODUCTVERSION = ? , PXSYSTEMNAMESETFROMFILE = ? , PXUPDATEDATETIME = ? , PXUPDATEOPERATOR = ? , PXUPDATEOPNAME = ? , PXUPDATESYSTEMID = ? , PYACCESSGROUP = ? , PYDEFAULTAPPNAME = ? , PYDEFAULTAPPVERSION = ? , PYEXPIRATION = ? , PYLABEL = ? , PYMANAGER = ? , PYNAME = ? , PYNODENAME = ? , PYORGANIZATION = ? , PYORGDIVISION = ? , PYORGUNIT = ? , PYOWNER = ? , PYPURPOSE = ? , PYRULESETNAME = ? , PYSETTING = ? , PYSYSTEMNAME = ? , PYTIMESTAMP = ? , PYWORKBASKET = ? , PYWORKGROUP = ? , PYWORKGROUPNAME = ? , PYWORKPOOL = ? , pzPVStream = ? where pzInsKey = ?
Hi Gollg,
I agree, that is what is understood. But, when we have created a DB user who got only read only privileges on all the tables in DATA and RULES schema and insert privileges for pr_extract_time table, we were getting below errors. Which clearly shows BIX requires INSERT & DELETE & UPDATE privileges apart from SELECT privileges on many tables. We havent tried to discover the number of tables on which these kind of operations are performed. Instead we have given the above privileges on all the tables in DATA and RULES schema. Not only these operations. BIX even demands EXECUTE privileges on to certain stored procs that comes with the product.
SQL: update ***_DATA.pr_data_admin set PXCREATEDATETIME = ? , PXCREATEOPERATOR = ? , PXCREATEOPNAME = ? , PXCREATESYSTEMID = ? , PXINSNAME = ? , PXOBJCLASS = ? , PXPRODUCTNAME = ? , PXPRODUCTPATCHVERSION = ? , PXPRODUCTVERSION = ? , PXSYSTEMNAMESETFROMFILE = ? , PXUPDATEDATETIME = ? , PXUPDATEOPERATOR = ? , PXUPDATEOPNAME = ? , PXUPDATESYSTEMID = ? , PYACCESSGROUP = ? , PYDEFAULTAPPNAME = ? , PYDEFAULTAPPVERSION = ? , PYEXPIRATION = ? , PYLABEL = ? , PYMANAGER = ? , PYNAME = ? , PYNODENAME = ? , PYORGANIZATION = ? , PYORGDIVISION = ? , PYORGUNIT = ? , PYOWNER = ? , PYPURPOSE = ? , PYRULESETNAME = ? , PYSETTING = ? , PYSYSTEMNAME = ? , PYTIMESTAMP = ? , PYWORKBASKET = ? , PYWORKGROUP = ? , PYWORKGROUPNAME = ? , PYWORKPOOL = ? , pzPVStream = ? where pzInsKey = ?
[java] SQL Inserts: <2014-06-11 17:49:02.974> <System> <System> <pega> <PEGA> <Data-Admin-System> <<null>> <<null>> <<null>> <<null>> <2015-01-07 21:41:51.614> <Hanumang> <Gururaj H Galagali> <prpc> <<null>> <<null>> <<null>> <<null>> <pega System Identifier> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> <pega> <<null>> <<null>> <<null>> <<null>> <<null>> <<stream>> <DATA-ADMIN-SYSTEM PEGA>
[java]
[java] Caused by SQL Problems.
[java] Problem #1, SQLState 42000, Error code 1031: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
Last SQL: delete from ***_DATA.pr_sys_locks where pxSystemName = ? and pxSystemNode = ?
java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
Last SQL: delete from PEGAFSIF_DATA.pr_sys_locks where pxSystemName = ? and pxSystemNode = ?
java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
Caused by: com.pega.pegarules.pub.database.DatabaseException: Database-General 1031 42000 ORA-01031: insufficient privileges
[java]
[java] From: (BFBA106B6AC1D2F1814101DA1D45677BD)
[java] SQL: insert into ***_RULES.pr_sys_ruleset_index (PXCREATEDATETIME , PXCREATEOPNAME , PXCREATESYSTEMID , PXINSNAME , PXOBJCLASS , PYRULESETVERSION , PZRULESETINDEX , PZRULESETLISTHASH , PZRULESETNAME , PZRULESETVERSIONMAJOR , PZRULESETVERSIONMINOR , PZRULESETVERSIONMINORPATCH , PZRULESETVERSIONPATCH , pzInsKey) values (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)
[java] SQL Inserts: <2015-06-23 15:22:56.233> <<null>> <12ff7b53bc15b8c686f8f4f9fa0acc79> <4FF0C05DAAA08916C2FE59EB7B39ACCC!PEGA-PROCESSCOMMANDER> <System-RuleSet-Index> <07-10> <0> <4ff0c05daaa08916c2fe59eb7b39accc> <Pega-ProcessCommander> <7> <10> <10-AA> <-1> <SYSTEM-RULESET-INDEX 4FF0C05DAAA08916C2FE59EB7B39ACCC!PEGA-PROCESSCOMMANDER>
[java]
[java] Caused by SQL Problems.
[java] Problem #1, SQLState 42000, Error code 1031: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
Thanks,
Chaitanya
Pegasystems Inc.
IN
Hi Chaitanya,
When you run BIX from command line, first BIX will connect to PRPC database and make the server up for which it will connect to basic prpc tables which are required for starting the server.
During the extarct also, BIX needs to update some basic tables like system lock tables, cache tables ,log usage tables etc. This is internal to PRPC OOTB tables .
Regardless of the option you choose, as the user connecting to the database, you must be granted the CONNECT role and have INSERT, SELECT, UPDATE, and DELETE object privileges for all tables and views and the EXECUTE object privilege for functions and procedures.
Keep in mind, if you start limiting BIX's access to DB's, it could act abnormally