Posted by: repettas | March 5, 2008

How To Run Tree Utility Tree Audit

How to run the Tree Utility Tree Audit to check for Tree Corruption

There is an Application Engine program that you can run that can check for tree
corruption.  The navigation to get to the Tree Utility Tree Audit is through the
browser:

8.1x:  PeopleTools>Tree Manager>Process>Tree Utilities. 
8.4x:  PeopleTools>Tree Manager>Tree Auditor

Users will then need to create a run control id.  The default will be set to
‘Tree Utilities’.  There is a checkbox for ‘all trees’.  If this box is checked,
then the tree audit will check all the trees for tree corruption.  This would be
good to use just for regular tree maintenance, but to check for a specific tree,
you can input the tree name, tree key, and effective date. 

After the process is run, go to the Process Monitor to verify that it went to
success.  From Process Monitor you can click on a link that will take you back
to the Tree utilities.  From the Tree Utilities page you can click on the
‘View Results’ link.  In there it will give you the results of the Tree Audits.

Posted by: repettas | March 5, 2008

Tree Utility Audits Check What?

What does the Tree Utility tree Audits through the web check for?

In PeopleTools 8.1x there is a new tree utility program that will audit the trees.
What does this check for?

This tree audit will check for trees with the following:
1)  Parent node does not exist
2)  Orphan tree leaves
3)  Tree Node Numbers are great than end numbers
4)  Tree node’s end number is greater than parent’s end number
5)  Tree nodes with overlapping ranges
6)  Node’s level number is less than parent’s level number

How to fix:

  1. Parent Node does not exist: If there are no other audit errors for this tree
    then running the ‘Correct Parent Node’ Utility program should correct the problem. Do NOT run the ‘Correct Parent Node’ Utility program if there are any other audit problems with the tree.  The other errors should be addressed first before trying to correct problems by running the utility program.
  2. Orphan Tree Leaves: There are records in the PSTREELEAF table where the leaf’s parent node number does not have a correspondnig record and node number in the PSTREENODE table.  Run the ‘Delete Orphan Tree Objects’ utility to delete these records
  3. Tree Node Numbers are Greater than End Number: Tree Nodes that have node numbers greater than the ending range for that node. The only solution is to use TreeManager to delete these nodes and then run the ‘Reset Tree Node Gaps’ utility program.  You will then need to re-insert the deleted nodes.  If for some reason you are not able to delete these nodes from within TreeManager it is recommended that you call the GSC for support.
  4. Tree Node’s End Number is Greater than Parent’s End Number: This audit reports on tree nodes whose end range value is greater than the end range value of its parent node. The only solution is to use TreeManager to delete these nodes and then run the ‘Reset Tree Node Gaps’ utility program.  You will then need to use Tree Manager to re-insert the deleted nodes.  If for some reason you are not able to delete these nodes from within TreeManager it is recommended that you call the GSC for support.
  5. Tree Nodes with Overlapping Ranges: Two nodes that have overlapping node number ranges. The only solution is to use TreeManager to delete these nodes.  If for some reason you are not able to delete these nodes from within Tree Managerit is recommended that you call the GSC for support.
  6. Node’s Level Number is Less than Parent’s Level Number: Nodes whose level number is less than the level number of their parent.
  7. The level numbers can be corrected by either: Switching the levels manually from within Tree Manager. Run the ‘Correct Level Numbers’ utility to reset all invalid level numbers on the tree.
Posted by: repettas | March 5, 2008

How to Get Custom Queries from Production Database

What are the steps to get current custom queries from Production database?

In order to get a copy of your queries, you should perform the following steps:

  1. Obtain a list of queries, trees, and tree structures that you need to preserve.
  2. At the beginning of the test Move to Production, you should make a new
    copy of your current production database (I’ll refer it as “Tree/Query Copy
    of Production”). Now you should have a Copy of Production database and a
    Tree/Query Copy of Production database.
  3. Perform the test Move to Production on your Copy of Production database.
    (NOT on your “Tree/Query Copy of Production”).
  4. The Tree/Query Copy of Production database needs to be at the same
    release level as the Copy of Production database on which you just completed
    the test Move to Production. To update your Tree/Query Copy of Production to
    the same release, you run release scripts against this database. PeopleSoft
    refers to this as “reling up” the database. Use the Custom Compare template
    to “rel up” your database. Select the Product Line PEOPLETOOLS when
    configuring your Change Assistant job. Perform all the steps in the template.
  5. Create a project in the Tree/Query Copy of Production that contains all
    of the queries and trees that you wish to preserve called PRESERVED.
    Manually compare the queries, trees, and tree structures that are in your
    PRESERVED project against your Copy of Production database.
  6. Copy the PRESERVED project to the target database (Copy of Production
    database on which you ran the test Move to Production). 
  7. Queries, trees, and tree structures that you wanted to preserve should
    be in the Copy of Production database now. You must test and re-test and
    make any necessary changes if the test results are not what you expected.
  8. Once you are satisfied with the test results, you must re-export the
    PeopleTools tables from the Tree/Query Copy of Production using MVPRDEXP.DMS
    in order to actually preserve the queries, trees, and tree structures. You
    will now use the output files created from running this script as input files
    during your final Move to Production. Running the MVPRDEXP.DMS script again
    ensures that you have the most current PeopleTools tables that include your
    preserved custom queries, trees, and tree structures.

Find details on how to run each of the steps above in the appendix Queries Trees
and Tree Structures of your upgrade documentation.

Posted by: repettas | March 5, 2008

nVision Tree Undefined, Inactive or Invalid

E-NV:  Error running nvision ‘Tree undefined, inactive or invalid’

SOLUTION 200993098:  E-NV:  Error running nvision ‘Tree undefined, inactive or
invalid’

SPECIFIC TO:
Enterprise, PeopleTools, nVision, All PeopleTools versions

ISSUE: Running nVision report gives an error that the tree is undefined, inactive,
or invalid.  Checks and the tree exists and is valid. 

SOLUTION:
This error is generally caused by the business unit being used for the nVision
report not having the default setid of the tree being used.  The first thing to
check is the table set tree controls:

PeopleTools -> Utilities -> Administration -> Table Set Controls
Look for the Business Unit that you are using in your nVision report.
Go to the tree tab and see if the default SETID is the setid of the tree that
you are using.  If it is not, then add the tree to the bottom area there.  (Even if it is already the default, try adding it and see if it makes a difference).

NOTE:  When adding a tree to the tableset tree controls, you need to
first use the lookup for the SETID and then the lookup for the tree name. 
It looks backwards.

Posted by: repettas | February 15, 2008

Query Performance

Improvement on Query performance implemented in 8.17 and 8.41

Performance issue getting a list of Queries and/or Records on the Web/Windows Client and running Queries on the Web for 8.x.

To improve the performance, Development has added two mechanisms. Therefore starting with PeopleTools 8.17, when bringing up a list of Queries, the system will first check:

- whether a temp Record has been created from the App Engine run job to store the data of the SQL statement
- SQL Object
- pull data using the SQL statement directly from the Database (PTools 8.16)

Here is the description of the 2 mechanisms:
**********************************************************
1. This specific Incident (T-SSHAIK-XQ8LT) discusses a new App Engine program that has been built to de-normalizes some of the Tables that are used to build the Query Access List. If the Customer chooses to use this new feature, it will greatly simplify the SQL that needs to be executed by Query to build Access List — regardless of what process is running the Query, i.e. would work the same for Crystal as it does for online Query. This new feature is called Query Access List Cache. Here is some more information regarding it:
**********************************************************

Excerpt From Query PeopleBook Enhancement for 8.17 -

Enabling Query Access List Cache
A new optional Query Access List Cache batch process is now available for Users who work with Query Manager, Crystal Reports, and PS/nVision. When the Access List Cache is enabled, the time taken by the system to retrieve the Queries that match the search criteria entered is greatly improved.

Maintain Security – Query Access List Cache page
To enable the Access List Cache:
1. Select PeopleTools, Maintain Security, Setup, Query Access List Cache.
2. Select Enable List Cache.
3. Select the Run button to run the process.
If the process does not complete, the Cache will be disabled.

From Query PeopleBook in 8.4x -

Enabling Query Access List Cache
A new optional Query Access List Cache batch process is now available for Users who work with Query Manager, Crystal Reports, and PS/nVision. When the Access List Cache is enabled, the time taken by the system to retrieve the Queries that match the search criteria entered is greatly improved.

Security – Query Access List Cache page
To enable the Access List Cache:
1) Select PeopleTools, Security, Query Security, Query Access List Cache.
2) Select Enable List Cache.
3) Select the Run button to run the process.

If the process does not complete, the cache will be disabled.

Note. Rerun the process when changes have been made to Query Access Groups or Query Access Group settings on Roles or Permission Lists.

If you need to be able to tell if the Cache is working:
1) Clear the Access List Cache.
2) Load Windows Query directly by running psqed.exe and use a stopwatch to time how long it takes from the moment you click the ok button to login until the time Windows Query appears.
3) Enable the Access Cache and run the App Engine program to populate the Cache.
4) Perform Step #2 again and compare the times.

Note that if the time in Step #2 is less than 5 seconds, you probably won’t see much improvement as it’s mostly overhead at that point. If you try this on a full Financial Database, you should see the time drop from about 60 seconds to less than 10 seconds.
**********************************************************

The only qualifier on this is that the DBA should only make changes to SQL that modify the access plan, i.e. would be acceptable to carefully reorder Tables in FROM clause or WHERE conditions, but they would need to be very careful to make sure that the actual SQL logic/result set is not affected.

Added code to read the SQL for Accessible Record List from a SQL Object. If the SQL Object is not found, then the hard-coded SQL in C++ is used. The rules are:
1) Look for the de-normalized table for Accessible Record List.
2) If #1 is successful, get the Records using that Table.
3) If #1 is not found or data is not found in #2, look for SQL Object PS_QRYACCRECLIST.
4) If #3 is found, use that SQL for fetching the Record List.
5) If #3 is not found, use the hard-coded SQL.

Added optimizable SQL Objects PSQRYACCLISTBIGSQL and PSQRYACCLISTFASTSQL. The former is for when the Cache is not used, the latter is the Cache access SQL.

Posted by: repettas | February 15, 2008

Add Oracle Hints to Query Search Views

Adding Oracle hints to Query Search Views (Security Views)

To add hints to the view SQL, do the following:

Open Application Designer
File/Open select Record then choose the SQL View in question (ex: EMPL_SRCH_GBL)
goto Object Properties and click on Type tab, you will see the SQL View

After the word SELECT you will insert the hint. ex: SELECT /*+ RULE */ A.EMPLID, B.RMPL_REC#

If in Query and want to add a HINT in the query see below:

What you will need to do is create an EXPRESSION. Right click on Expression then select New Expression.

In the box where you can write your expression you will type in the HINT then click on the Add Field button. Add the first field in your field list. You have to do this so that PS/Query doesn’t put a comma after the hint. The text in the Expression box should look something like this: /*+ ALL_ROWS */ A.EMPLID

Syntax = /*+ */

Make sure that the is the first field you want in your query output. When you are finished adding the Expression you should click on the SQL tab to make sure the SQL looks OK. It will look something like this:

Select /*+ ALL_ROWS */ A.EMPLID
From EMPLOYEES A

Posted by: repettas | February 15, 2008

What Are the Tree Table Names

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.

Posted by: repettas | February 15, 2008

nVision Tree Selector Tables – What Are They

NV: Tree selector tables – what are they and why are they used?
 “NV: Tree selector tables – what are they and why are they used?”

 Analyzing the SQL statements that are generated when nVision reports are run. The SQL statements keep using tables called “PSTREESELECTxx” where xx is some number. What are these tables?

nVision and Query use tree “selector” tables when there is a need to interpret node data from a tree. These tables “flatten” a tree by populating the selector table with all the ranges of values for a given tree node. This table is then joined to the field being rolled-up in order to retrieve the rows that fall under that tree node.

There is a selector table for each chartfield in the GL. The number after the PSTREESELECT represents the length of the chartfield. For instance, PSTREESELECT06 is normally the selector table used for account values because the account chartfield is 6 digits in length. PeopleSoft delivers 30 selector tables.

Selector table data is built when the nVision report or query is run. Selector table data can be “static” or “dynamic” depending upon the performance options which are set for each tree. Static selectors remain in the selector tables until a tree is changed. If nVision or query detect a change in the version number of a tree the selector table data gets rebuilt. Dynamic selector data is build “on the fly.” The selector table data is built at runtime and then deleted from the table.

For more information on selector tables and nVision performance please read the PeopleTools PeopleBook. The information can be found under Reporting and Analysis Tools, nVision, Performance.

More Information: The data in the PSTREESELECTXX tables can be purged. You should do this if the data gets out of synch (especially after an upgrade). Data can be deleted from both PSTREESELECTXX and the control table PSTREESELCTL.

Posted by: repettas | February 14, 2008

Rebuilding Tree Selector Tables

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.

Posted by: repettas | February 5, 2008

Leopard Screen Capture

Screen Capture Beyond Shift-Command-3 and 4

Leopard Changes

There have been a few changes made to the way Mac OS X captures the screen under Leopard. The same old Shift-Command-3 and 4 commands still work, but there are a few more things you can do now.

You may know that by adding the Control to the key command sends the resulting screenshot to the Clipboard instead of the desktop, but did you know that after you’ve invoked Shift-Command-4, you can use other keys to refine your screen selection.

Once, you’ve hit Shift-Command-4 or Shift-Control-Command-4, you are free to release those keys and click and drag your selection of the screen. You’ll notice that the crosshairs now show the coordinates on the screen in pixels. When you click and start to drag, those numbers change to show the dimensions of the selection, also in pixels.

Once you’ve started to click and drag the selection, you can use the Spacebar to move your selection around on the screen without changing its dimensions (if you hit the Spacebar before you click and drag, the cursor changes to a camera icon and you are able to take screenshots of individual windows by clicking on them).

Holding down the Shift key allows you to constrain the height of the selection allowing you to refine its width or vice versa. It just depends on which dimension you change immediately after you press the Shift key.

Holding down the Option key causes the selection to expand from its center. This is handy if you want a certain detail to be in the center of the screenshot; just hit Shift-Command-4, hold down the Option key and then click and drag from the point that you want to be the center of your screenshot.

One last key command, and it’s not new, if you make a mistake in your selection somehow and don’t want the screenshot to be taken at all, hit the Esc key to back out of it.

« Newer Posts - Older Posts »

Categories

Follow

Get every new post delivered to your Inbox.