When was a query last executed?

August 22, 2008 2 comments

PSQRYSTATS table tells you about when was a particular query last executed and more details. This PSQRYSTATS table has following fileds.

EXECCOUNT  – Count of Query Execution
AVGEXECTIME  – Average Query Execution Time
AVGFETCHTIME – Average Fetch Time
LASTEXECDTTM – DateTime of Last Execution
AVGNUMROWS – Average Number of Rows Fetched
NUMKILLS – Number of Times Killed


PSQRYDEFN has a field QRYTYPE. It is a number field and It’s values are listed below.
1  – QUERY

Categories: PT Tags: ,

Portal Content LOCAL_NODE

August 13, 2008 Leave a comment

On the Portal > Structure and Content, I opened a Content Ref Administration Page, on the General page, I observed Node Name as LOCAL_NODE. I clicked on the prompt for the field, it did show a LOCAL_NODE as valid value and in addition it showed this node as local and default node along with PT_LOCAL as default and local node. I was surprised as there cannot be two local and default nodes in a PIA. So I opened the component PORTAL_CREF_ADM and in that I opened the page PORTAL_CREF_ADM. Node Name field MSGNODENAME was from a derived work record PORTAL_CREF_ADM, I checked the prompt view for MSGNODENAME field, it was dynamic view PORTAL_NODE_VW, the SQL for this view is given below.

 , ‘ ‘
 , 1
 , ‘Y’
 , ‘PIA’

The LOCAL_NODE I saw on structure and content page was a dummy node.

People Tools version 8.49

Categories: PT Tags:

Steps to check REN Server failure

August 4, 2008 Leave a comment

The REN Server is a Real Time Event Notification system that allows reports to be printed to a separate browser window. 

If you are getting “Communication with REN Server failed (HTTP 403 Forbidden) ” error message, when trying to open a report to a separate browser window or trying to ping the REN server,  then follow the following steps suggested by PeopleSoft for checking REN server.

Case # 1:
Make sure that you have the proper REN server permissions configured.

Navigate to PeopleTools > Security > Permissions and Roles > Permission Lists.  Select the primary permission list that is tied to the user profile that you are testing with.  You will also have to grant permissions to the USER ID that is configured for your Process Scheduler database signon settings, if that user is different than the one that you are currently logged in as.  Once the permission list is up, go to the PeopleTools tab.  Click on the “Realtime Event Notification Permissions” link in the middle of the page.  On the next page, make sure that the access code for Reporting Window is set to full access. 

Next, go to the Web Libraries tab.  Make sure that there is an entry for WEBLIB_RPT.  If it is not there, then please add the library in.  Once that is added, click on the edit link and make sure that the access permissions are set to full access.

Make sure to save the changes before exiting.

Next, navigate to PeopleTools > Portal > Node Definitions and click search to bring up all of your nodes.  Sort so that your default local node is listed at the top.  Click on the name link for the default local node and make sure that the Authentication Option is set to password and that a password has been entered. 

Finally, navigate to PeopleTools > Security > Security Objects > Single Signon.  Make sure that your default local node is listed there.

Case #2
If using an authentication domain, make sure that the REN server configuration is specifying the domain:
  -Navigate to PeopleTools -> REN Server Configuration -> REN Server Cluster
  -Select your REN Server Cluster
    -Verify ‘Authentication Domain’ has been specified
    -Verify that the domain is specified in the ‘REN Server Browser URL’

Case # 3
This case applies to PeopleTools 8.44 only.  If the initial PIA deployment did not specify an Authentication Domain, but the environment now has one, it is necessary to add the Authentication Domain to the active Web Profile.  Navigate to PeopleTools > Web Profile.  Search for and select the active Web Profile.  (If you do not know what Web Profile is currently active, check your configuration.properties file.  It will tell you what Web Profile is currently active).  Once you have the Web Profile up, the first tab should be labeled “General”.  Make sure the field for Authentication Domain field on the General tab has your company’s Authentication Domain (ex. mycompany.com).  Make sure to save your changes and bounce the web server for the change to take effect, then go back and try running your Ping Test.

Case # 4:
One customer reported that in their REN Server Configuration > REN Server Definition page, the Application Server Domain name was case sensitive.  After they changed the Application Server Domain name to match how it was defined through PSADMIN (in this case, to include lowercase characters), they were able to access the REN server.

Case # 5:
If you are still having issues after trying the suggestions above, then try clearing cache in case some Ren configuration changes were made, but cache was not cleared afterwards.  To clear cache,  bring down the PIA web server and the application server and clear the cache.  Also, clear the cache and cookies from your browser and close all browser sessions.  Restart the PIA web server and application server.   Then run ping test again.

Categories: PS Admin Tags:

Default Local Node in IB

July 2, 2008 2 comments

The only local node definition used by PeopleSoft Integration Broker is the one designated Default Local, which represents the database onto which you are signed. Only portals use nodes designated simply as Local.

If we try to ping a local node which is not a default local node, we will get the following message. Destination node does not match the local node (158,506).

Default Local Node indicates whether the current node represents the database to which you are assigned. PeopleSoft Integration Broker is delivered with one node predefined as the default local node. You can’t change which node is the default local node, but you can use the Rename Node button to rename the default local node to more appropriately reflect your application or system.

Categories: IB Tags:

VP1/PS User ID Password Change

June 12, 2008 1 comment

To change the password for VP1/PS, following steps are to be executed.

  1. Change VP1/PS Password online. Login to the appropriate PeopleSoft instance online, PeopleTools > Security > User profile open VP1/PS profile and change the password.
  2. Shutdown appserver and process scheduler server
  3. Change the password for VP1/PS in Application Server configuration using psadmin.
  4. Change the password for VP1/PS in Process Scheduler Server configuration using psadmin.
  5. Start appserver and process scheduler server
  6. Web profile Configuration – Go to PeopleTools > Web Profile – open up the related profile and update the password in the security tab.
  7. Change the VP1/PS password in Integration broker configuration file. Go to PeopleTools > Integration Broker > Gateway open the local Gateway – Click on properties link, enter user id and password and in the gateway configuration file change VP1/PS password.
Categories: Security

Report Distribution – Report Manager

March 7, 2008 19 comments

When we schedule a processes/Report to run by loggin in as say, VP1, then to see the output/report you have to login with the same User Id VP1. If the requirement is that another user need to see the same output/report, without using VP1 user id, then we need to do report distribution and give the user access to report manager so the user can view other’s reports. One way of achieving this as given below.

  • Add the following roles to user
    • ReportDistAdmin or ReportSuperUser(See the Note below).
    • Any role containing permission List – CPPT1040 (gives access to Report manager) or similar one.
  • To all the processes/reports that user needs to have access, add the userid to distribution list of that processe/report.
    • Go to the process/report run control page
    • Slect run control Id
    • Click run
    • Click ‘distribution’ link for the process/report.
    • Select either a user and enter his User Id.
    • You have to submit the process/report, once for the distribution data you have entered to save.
  • Submit the process/report for testing.
  • Now if the user logs in and go to  Administration tab in Report Manager, he could see the process/reports and link to see the output/report. User can see the output/report using process monitor also.

 Note: The difference between the administrator (ReportDistAdmin) and super user (ReportSuperUser) roles is that the administrator role can access and update any report in the Report Manager. The super user role can  update only reports that they are authorized to view.

Report Manager related other blog entries are given below:

Query Security

March 3, 2008 2 comments
  • Query is a PeopleTool that helps you build SQL queries to retrieve information from your application tables.
  • Query takes advantage of user’s security settings, row-level security, and primary permission list.
  • For each Query user, you can specify the records they are allowed to access when building and running queries. We can achieve this by creating Query Access Groups in the Query Access Group Manager, and then you assign users to those groups with Query permissions.  

Query Access Group Trees:

  • Trees are a graphical way of presenting hierarchical information.
  • PeopleSoft Query uses query access group trees to control the access of the tables in your PeopleSoft database.
  • Define a hierarchy of PeopleSoft record definitions, based on logical or functional groupings, and then give users access to one or more nodes of the tree.
  • Users can retrieve information only from those tables whose record definitions to which they have access.
  •  Nodes: Query access group trees contain two types of Nodes: groups and records.
    • Groups are a logical representation of a set of child groups or records. It is similar to folder in Windows.
    • Records represent a PeopleSoft record definition.
  • Structure:
    • Always use the ACCESS_GROUP Tree Structure.
    • Do not use SetID or UKV/BU.
    • Do not have Details.
    • Do not use Levels.
    • No Branches.
  • Requirements:

    • The Root Node is always a group.
    • Groups must be unique in a given Tree while records definitions can be repeated.
    • Groups and records could have Child Groups and Child Records.
    • Each record needs a unique fully qualified path in the tree. You can’t add the same record under the same parent node (group or record).
  • To open an existing Query Access Group Tree, Select PeopleTools, Security, Query Access Manager.
  • Create custom Query Access Group suitable to your organization. Create functional group names and add records under the group.
  • To Add the Query Access Groups to user:
    • Open the primary Permission List for the user
    • Go to ‘Query’ Tab
    • Click on Access Group Permissions.
    • Add the tree name, select the proper Access Group, Select ‘Accessible’ button. Repeat to add more Access groups.
    • Save the permission List.

Row Level Security:

  • By default, when you give Query users access to a record definition, they have access to all the rows of data in the table built using the associated record definition.
  • With row-level security, users can have access to a table without having access to all rows on that table.
  • This type of security is typically applied to tables that hold sensitive data.
  • For example, you might want users to be able to review personal data for employees in their own department, but not for people in other departments. You would give everyone access to the PERSONAL_DATA table, but would enforce row-level security so that they could only see rows where the DEPTID matches their own.
  • PeopleSoft applications implement row-level security by using a SQL view that joins the data table with an authorization table.
  • When a user searches for data in the data table, the system performs a related record join between the view and the base table rather than searching the table directly.

Query Security Record Definitions:

  • You implement row-level security by having Query search for data using a query security record definition. The query security record definition adds a security check to the search.
  • Query security record definitions serve the same purpose as search record definitions do for panels. Just as a panel’s search record definition determines what data the user can display in the panel, the query security record definition determines what data the user can display with Query.
  • To get Query to retrieve data by joining a security record definition to the base table, you specify the appropriate Query Security Record when you create the base table’s record definition.

To apply row level security:

  1. Select PeopleTools, Application Designer to open the Application Designer, and open the record on which you want to apply row-level security.
  2. With the record definition open in the Application Designer, click the Properties button, and select the Use tab from the Record Properties dialog box.
  3. Select the security record definition (usually a view) in the Query Security Record list box.
  4. Once you’ve set the query security record definition, click OK to close the Record Properties dialog box, then save the record definition. If you’ve already used SQL Create to build a table from this record definition, you don’t need to rebuild it.
  5. Note. PeopleSoft row-level security views restrict users from seeing certain rows of data. To secure data through the search record, simply put one of the three Row Level Security fields on your record as a Key, not a List Box Item. The three Row Level Security fields are OPRID (User ID), OPRCLASS (Primary Permission List), and ROWSECCLASS (Row Security Permission List). If one of these fields is on the search record as a Key, not a List Box Item, PeopleTools does the following. PeopleTools adds a WHERE clause when it performing a SELECT through the record forcing the value to be equal to the current user’s value.