NV: Rebuilding Tree Selectors for nVision (PSTREESELECT)

♦ August 9, 2013 ♦ Leave a comment

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);

 

Leave a comment