NV: Rebuilding Tree Selectors for nVision (PSTREESELECT)
http://repettas.wordpress.com/page/14/
http://workingscripts.blogspot.com/2008/03/nvision-performance-tuning.html
http://repettas.wordpress.com/2008/02/15/nvision-tree-selector-tables-what-are-they/
NV: Rebuilding Tree Selectors for nVision (PSTREESELECT)
Tips and Techniques
Subject: Rebuilding Tree Selectors for nVision
Content: The following are 2 methods in rebuild tree selectors.
Method 1: nVision will automatically rebuild selectors if the tree is re-saved after deleting a node and re-adding it, or if the tree is copied to a new effective date.
Method 2: delete the selector control entries associated with the ACCOUNT trees, forcing nVision to rebuild them. The following SQL statement should do this, assuming that the ACCOUNT trees have a tree structure called ACCOUNT (if not, change the structure ID):
delete from pstreeselctl
where tree_name in
(select tree_name from pstreedefn
where tree_strct_id=’ACCOUNT’);
One additional note: deleting the Selector Controls forces nVision to rebuild them from the other tree tables. It also leaves all the old selector rows orphaned in PSTREESELECT06 (if ACCOUNT is 6 char. long). But it doesn’t affect the node name lookup, which is done either on the system node table (PSTREENODE) or in a cache (swap file) of nodes nVision has already looked up. I have heard of cases where that cache was not rebuilt after a tree change, but that only happens when the nVision user stays logged on while the tree change takes place. Logging off and logging back on should solve this, and is less disruptive than deleting swaps.
————————————————-
More information regarding PSTREESELECT table.
When does PSTREESELECT get updated?
The PSTREESELECT# tables are used to speed tree-based data selection. The table gets rows inserted into it the first time a tree is used by an nvision report.
PSTREESELCTL is the table which controls whether or not to refresh data in the selector tables based on the Tree effective date. If you modify a tree and save it, the pstreeselect tables will get updated when you run an nVision report that uses that tree.
The indexes need to be tuned based on how they use it. PSG goes out and does this a lot. It is covered in Chapter 12 of the Using your GL system.
======================================================
How does PeopleTools repopulate the tree selector tables without a database
The selector tables should be recreated each time the nVision reports are run. Basically what happens is that nVision looks to see if the selector table values exist for items in the trees. If they are not there, the process recreates the static selector table values. You should never have to copy or create selector table values, the system will do it for you. Unfortunately, if you have changed the trees between the time the selector tables were deleted and the time they are rebuilt, you may have ‘orphaned’ records which may impede performance. This can be identifed and deleted using SQR.
If you are running with Dynamic Selectors on in the Performance Options, the selector values for the trees being used only exist for the duration of the report run. They will not remain after the run completes.
— Delete TreeSelectors
Delete from PSFSSYS.PSTREESELECT05;
Delete from PSFSSYS.PSTREESELECT06;
Delete from PSFSSYS.PSTREESELECT10;
— Delete Tree Control table for the respective selectors above
delete from PSFSSYS.pstreeselctl
where length in (5,6,10);
Create Oracle public database link
1: CHECK tnsname.ora – FSPRD
2: tnsping – FSPRD
3:create dblinks
create public database link
FSPRD
connect to
FSPRD
identified by
FSPRD
using ‘ FSPRD;
http://www.dba-oracle.com/t_oracle_dblink.htm
create public database link
mylink
connect to
remote_username
identified by
mypassword
using ‘tns_service_name’;
4: TEST DBLINKS:
select * from ps_serverdefn@FSPRD;
Oracle deadlocks tips
http://www.dba-oracle.com/t_deadlock.htm
Oracle deadlocks tips
|
|
E-TR: What are the names of the Tree tables?
E-TR: What are the names of the Tree tables?
Names of Tree Tables and relationship parent/child
PeopleSoft Tables – Tree Manager
Table Name Table Description
PSTREEDEFN Stores valid tree definitions. Updated by the Tree Manager. Non English stored in PSTREEDEFNLANG.
PSTREENODE Contains one entry per tree node / deptid. Defines relationships
between nodes. Updated by the Tree Manager.
PSTREEBRANCH Stores tree branches.
PSTREESTRCT Defines the different records and fields that control the tree attributes.
Updated by the Tree Manager.
PSTREELEVEL Validation table for tree levels. Updated by Tree Manager.
PS_ACCESS_GRP_TBL Stores defined Access Groups. Used for Query Security
PSTREESELCTL Control table for PSTREESELECT## tables. Also: PSTREESELNUM.
PSTREESELECT01-30 Stores tree leaf information (one table for every possible length of a chart field 1-30 characters). Work table for optimization purposes.
PSTREELEAF Contains ranges of field values that are associated with a node of a tree.
PSTREEPROMPT (Specific to 8.x and up only) Stores similar information as PSTREEDEFN and is needed for trees to be viewed through PIA
Parent/Child relationship: The range of nodes from PSTREENODE.TREE_NODE_NUM to TREE_NODE_NUM_END
represents all the nodes in the sub-tree headed by this node. So the Tree Manager uses these fields to determine parent-child relationships.
Tree_Node_Num The node number of this node.
Tree_Node_Num_End The node number of the last child of this node. The range of nodes from Tree_Node_Num through Tree_Node_Num_End
represents all the nodes in the sub-tree headed by this node.
Find Oracle top inactive session
select
s.sid sid, s.serial#, s.status status,s. CLIENT_INFO client_info ,CLIENT_IDENTIFIER,
s.username username,
UPPER(DECODE(command,
1,’Create Table’,2,’Insert’,3,’Select’,
4,’Create Cluster’,5,’Alter Cluster’,6,’Update’,
7,’Delete’, 8,’Drop Cluster’, 9,’Create Index’,
10,’Drop Index’, 11,’Alter Index’, 12,’Drop Table’,
13,’Create Sequencfe’, 14,’Alter Sequence’, 15,’Alter Table’,
16,’Drop Sequence’, 17,’Grant’, 18,’Revoke’,
19,’Create Synonym’, 20,’Drop Synonym’, 21,’Create View’,
22,’Drop View’, 23,’Validate Index’, 24,’Create Procedure’,
25,’Alter Procedure’, 26,’Lock Table’, 27,’No Operation’,
28,’Rename’, 29,’Comment’, 30,’Audit’,
31,’NoAudit’, 32,’Create Database Link’, 33,’Drop Database Link’,
34,’Create Database’, 35,’Alter Database’, 36,’Create Rollback Segment’,
37,’Alter Rollback Segment’, 38,’Drop Rollback Segment’, 39,’Create Tablespace’,
40,’Alter Tablespace’, 41,’Drop Tablespace’, 42,’Alter Sessions’,
43,’Alter User’, 44,’Commit’, 45,’Rollback’,
46,’Savepoint’, 47,’PL/SQL Execute’, 48,’Set Transaction’,
49,’Alter System Switch Log’, 50,’Explain Plan’, 51,’Create User’,
52,’Create Role’, 53,’Drop User’, 54,’Drop Role’,
55,’Set Role’, 56,’Create Schema’, 57,’Create Control File’,
58,’Alter Tracing’, 59,’Create Trigger’, 60,’Alter Trigger’,
61,’Drop Trigger’, 62,’Analyze Table’, 63,’Analyze Index’,
64,’Analyze Cluster’, 65,’Create Profile’, 66,’Drop Profile’,
67,’Alter Profile’, 68,’Drop Procedure’, 69,’Drop Procedure’,
70,’Alter Resource Cost’, 71,’Create Snapshot Log’, 72,’Alter Snapshot Log’,
73,’Drop Snapshot Log’, 74,’Create Snapshot’, 75,’Alter Snapshot’,
76,’Drop Snapshot’, 79,’Alter Role’, 85,’Truncate Table’,
86,’Truncate Cluster’, 88,’Alter View’, 91,’Create Function’,
92,’Alter Function’, 93,’Drop Function’, 94,’Create Package’,
95,’Alter Package’, 96,’Drop Package’, 97,’Create Package Body’,
98,’Alter Package Body’, 99,’Drop Package Body’)) command,
s.osuser osuser,
s.machine machine,
s.process process,
t.value value
from
v$session s,
v$sesstat t,
v$statname n
where
s.sid = t.sid
and
t.statistic# = n.statistic#
and
n.name = ‘CPU used by this session’
and
t.value > 0
and
audsid > 0 and s.status=’INACTIVE’ and s.username is not null and rownum < 10
order by
t.value desc;
Killing Oracle Sessions
Killing Oracle Sessions
If you are logged into the system with enough rights (e.g. SYSADM) you can kill database sessions that are not ACTIVE. To do so, first identify the appropriate session ID (sid) and serial number using this query:
SELECT s.sid, s.serial#, s.osuser, s.program, s.machine, s.status, s.state FROM v$session s WHERE OSUSER = ':UserID' AND STATUS <> 'ACTIVE';
See this guide for more information.
Replace User ID with your OS username.
To kill the session use the following command:
alter session kill session 'sid,serial#' immediate;
SELECT s.sid,
s.serial#,
s.osuser,
s.program,
s.machine,
s.status,
s.state
FROM v$session s
WHERE STATUS <> ‘ACTIVE’;
alter system kill session ‘2027,21077’ immediate;
SQL for all cutomized tables in Access groups
SELECT DISTINCT A.TREE_NAME, D.RECNAME
FROM PS_TREE_ACCGRP_VW A, PS_TREE_ACCESS_VW B, PSTREEACCRECPVW C,
PS_RECFLD_DESCR_VW D
WHERE A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_TREE_ACCGRP_VW A_ED
WHERE A.TREE_NAME = A_ED.TREE_NAME
AND A_ED.EFFDT <= SYSDATE)
AND A.TREE_NAME = B.TREE_NAME
AND A.TREE_NAME = C.TREE_NAME
AND D.RECNAME = C.TREE_NODE
AND D.RECNAME between ‘C_‘ and ‘D’;
IE: customized table starting with ‘C_” so as C_xxxxx.
Query Security
Query Security:
· PeopleSoft Query uses query access group trees to control the access of the tables in the PeopleSoft database.
· You create and update query access group trees using Query Access Manager.
· You should create query access group trees based on your organization’s needs and on any customizations you’ve made.
Relationship between row-level security and Query security record definitions:
· PeopleSoft applications implement row-level security by using a SQL view that joins the data table with an authorization table.
· To apply row level security:
· Open the record on which you want to apply row-level security. Click the Properties button, and select the Use tab from the Record Properties dialog box.
· Select the security record definition (usually a view) in the Query Security Record list box.
· Row-Level (Data Permission) Security Views:
1. Using PeopleSoft row-level security views enables you to restrict users from seeing certain rows of data.
2. You can restrict data by:
1. User, by using the OPRID field.
2. Primary permission list, by using the OPRCLASS field.
3. Row security permission list, by using the ROWSECCLASS field
3. 3. To implement row-level security through a security view:
2. In Application Designer, insert one of the three row-level security fields(OPRID, OPRCLASS, ROWSECCLASS) into the record definition.
3. Configure the field as a Key, but not a List Box Item.
4. Save the record and build the view.
5. Use the record as the search record or query security record.
· Steps for creating query security record:
1. Create a view depending on your security need.
Example: create a view which has oprid, rowsecclass and the parent key field and in sql editor write the following sql:
SELECT DISTINCT opr.oprid,
opr.rowsecclass,
dtl.t_cust_id FROM psoprdefn opr,
ps_t_sjt_class cls,
ps_t_sjt_class_dtl dtl WHERE cls.rowsecclass = dtl.rowsecclass
AND cls.t_cust_id = dtl.t_cust_id
AND opr.rowsecclass = cls.rowsecclass
2. Attach the above view to the record in record properties (query security record).
3. 3. Grant security to that record to which the query security view is attached.
4. 4. In PIA, traverse to query access manager (Oracle PS Tools ->people tools –> Query Security -. Query Access Manager). Then CLICK ON CREATE NEW TREE.
5. 5. If access group is already present, then search for that access group in the prompt. 
6. 6. If you want create a new access group then enter the access group name and PRESS ENTER, it will be redirected to the below page where you can create your own access group.
7. 7. Then insert the child records to the access group.
step 7.1
Step 7.2
Step 7.3
8. 8. Go to permission list and traverse to query. (People Tools -> Security -> Permission & Roles -> Permission list (Select the permission list)).
9. 9. In access group permissions, assign the tree name and access group.
10. 10. In query manager, add the record to which the query security view is attached.
NOTE: You should consider adding record definitions to the query trees in a hierarchy that matches the parent/child relationship of records in your database.
PeopleTools 8.5x – Windows – Missing or invalid version of SQL library PSORA (200,0)
PeopleTools 8.5x – Windows – Missing or invalid version of SQL library PSORA (200,0)
Error:
PeopleSoft Application Server fails to start on a Windows system where the PeopleTools version is 8.50. Specifically, the PSAPPSRV process generates an initialization error when booting. The following messages are logged to the APPSRV log:
PSAPPSRV.2692 (0) [01/26/10 14:10:10](0) Cache Directory being used: C:\Documents and Settings\hrtuxedo\psft\pt\8.50\appserv\dmo91\CACHE\PSAPPSRV_2\
PSAPPSRV.2692 (0) [01/26/10 14:10:10](1) GenMessageBox(200, 0, M): PS General SQL Routines: Missing or invalid version of SQL library PSORA (200,0)
PSAPPSRV.2692 (0) [01/26/10 14:10:10](1) GenMessageBox(0, 0, M): Database Signon: Could not sign on to database DMOHR191 with user PS.
PSAPPSRV.2692 (0) [01/26/10 14:10:10](0) Server failed to start
PSADMIN.3116 (0) [01/26/10 14:10:17](0) End boot attempt on domain dmo91
Cause:
The first logged error message refers to an invalid or missing PSORA library file. This message indicates that the PSAPPSRV process is not able to load the library. It may be that it cannot locate it or that the library located is not compatible with this current version of Tools.
Solution:
PeopleTools 8.5x has been certified to run on 64bit Windows platforms but the program itself is 32bit. This means that any library it needs to load also needs to be 32bit.
The Oracle client lib ‘PSORA’ for instance, must be the 32bit version rather than the 64bit version.
If you have the 64bit Oracle client installed please uninstall it and install the 32bit version. Also, make sure the ‘Add to Path’ entry in the psappsrv.cfg file reflects the 32 bit binaries location.
If PSAPPSRV is successfully loading the PSORA library then you will not see this logged message:
GenMessageBox(200, 0, M): PS General SQL Routines: Missing or invalid version of SQL library PSORA (200,0)
How to export-import Oracle data
How to export-import Oracle data
Usually, the backup function for Oracle databases in a lot of large companies is managed by a DBA, so SamePage administrators do not have to worry about it. But in case you are using a stand-alone Oracle 10G XE, you can simply use the Oracle export utility to take regular database backups.
For Export
You simply need to run the following command at the command prompt of the machine where you have Oracle installed:
exp username/password file=samepage.dmp log=backuplog.txt statistics=none
where
- username/password is the database username and password that is used by SamePage (Note – this should match username, password in cm.xml file under <SAMEPAGE_INSTALL_DIR>/tomcat/conf/Catalina/localhost/)
- samepage.dmp is the name of the file where you want the SamePage data to get backed up.
- backuplog.txt is file into which you want to redirect the logs
You can also copy this command into a .bat file and have it run at regular intervals.
The dmp file should be securly stored so you can import it back to re-create the schema in case of a database crash.
For Import
If you are importing the schema to another database user, you should first create that database user and give it the required privileges. Oracle allows only system user to import one schema’s objects into another.
So if the data was exported from User1, you can import it into User2 with the following command:
imp system/systempassword fromuser=User1 touser=User2 file=samepage.dmp log=implog.txt
where
- system is the Oracle’s ‘system’ username and systempassword is ‘system’ user password
- User1 is the original DB user from where the data was exported
- User2 is the new DB user into which the data is being imported
- samepage.dmp is the exported database dump file
- implog.txt is file into which you want to redirect the logs
There is a WAY- My IT notes 