Firefox and Chrome had a much better browser response time in the beginning of the test. IE response time 8-10 times more than that of Firefox and Chrome.
Midway through the test, Firefox response time had also increased, but still lesser than IE response time and it continued to vary between 20% to 50% of the response time from IE
The above pictures were point in time, but if we look at the overall time interval picture, it looks something like what is shown below. Average browser time in case of IE was almost about 4 times that of Chrome and Firefox.
QUERIESExecution Plan and Tuning:-
Select Statement Execution Order: 1. FROM and JOINs 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 6. DISTINCT 7. ORDER BY 8. LIMIT / OFFSET
In general commonly used select statements we can say F-WGHSO
Recommendations for ensuring good performance for your Database servers: Avoid using Boolean operators >, <, >=, <=, is null, is not null Avoid using Not in,! = Avoid using Like ‘%pattern’, not exists Avoid using Calculations on unindexed columns or (use union instead) Avoid using having (use a WHERE clause instead) Always use Enable aliases to prefix all columns Always use Place indexed columns higher in the WHERE clause Always use Use SQL Joins instead of using sub-queries Always use Make the table with the least number of rows the driving table by making it first in the FROM clause. Always Establish a tuning environment that reflects your production database Always Establish performance expectations before you begin Always Design and develop with performance in mind Create Indexes to support selective WHERE clauses and join conditions Use concatenated indexes where appropriate Consider indexing more than you think you should, to avoid table lookups Pick the best join method Nested loops joins are best for indexed joins of subsets Hash joins are usually the best choice for “big” joins Pick the best join order Pick the best “driving” table Eliminate rows as early as possible in the join order Use bind variables. Bind variables are key to application scalability Use Oracle hints where appropriate Compare performance between alternative syntax for your SQL statement Consider utilizing PL/SQL to overcome difficult SQL tuning issues Consider using third party tools to make the job of SQL tuning easier Use of Bind Variables: The number of compiles can be reduced to one per multiple executions of the same SQL statement by constructing the statement with bind variables instead of literals.
How you can export SQL results to Excel using SQLPLUS?
To export SQL results to Excel using SQLPLUS, you have to follow the following steps
Step 1: Login into data-base using SQL PLUS
Step 2: Set mark-up using command “ SET MARKUP HTML ON”
Step 3: Spool the output to a file “SPOOL C:\TEMP\MYOUTPUT.XLS”
Step 4: Execute your SQL QUERY
Step 5: Set the Spool off
Step 6: To view the output open the output XLS file
Custom Print Button Using HTML Area to Print Peoplesoft Page:-
A very simple way to adding a Print button to print a Peoplesoft page is by just using some javascript and putting it inside of an HTML Area. Then add that HTML Area to the Peoplesoft page:
The Excel to CI utility has been developed to replace PeopleSoft Import Manager. It enables you, as the user, to upload data into the target PeopleSoft database from an Excel Spreadsheet utilising all the business logic and editing provided by PeopleSoft.
Excel allows a maximum 252 columns and 65,000 rows.
Excel to CI uses HTTP and SOAP messaging removing the need for database connectivity.
Excel2CI steps/Tabs:-
Connection
Template
Data Input
Stage and Submit
Record Structure/Type:-
Level 0 Record =”000″
Level-1 Record =”100″
Level-2 Record=”110″ etc
Example: – URL:http://psoft.company.com:<8050>/psp/PSPRD/EMPLOYEE/ERP/h/?tab=DEFAULT Web Server: psoft.company.com Protocol: HTTP HTTP Port: 8050 (if no port specified then likely to be 80) Portal: EMPLOYEE PeopleSoft Site: PSPRD Node: PT_LOCAL Language Code: ENG Chunking Factor: 40
Integration broker Gateway should be up and running
Messaging server should be up and running on the Application server
You should have developer access on Application Designer and possibly have admin access for PIA(PeopleSoft Internet Architecture) to configure IB.
Step1: Create a new CI definition based on Personal Data component
In application designer go to File -> New and select ‘Component Interface’ from the ‘New Definition’ windowSelect source CI definition as ‘PERSONAL_DATA’
A message box pops up with the message ‘Do you want to default the properties based on the underlying Component definition, PERSONAL_DATA?’ Please select No. if we select Yes then all the fields/properties of the component would be exposed in the CI which we don’t want at the moment because we intend to keep our web service simple and we don’t want all the properties to be exposed.
You can see a screen similar to the below screenshot. Please make a note that GETKEYS of the component has just one property/field called EMPLID. This will ultimately become parameter which the consumer of the web service will pass for getting employee information. Also note that there are no ‘Properties’ in the CI window, this is because we have selected ‘No’ in the previous step when system asked to default the properties and also observe various default methods available for the CI
Drag and drop only those properties highlighted from the left widow to right window. In this example we are just exposing Name related properties/fields such as NAME_PREFIX, LAST_NAME etc from NAMES level 2 scroll
Save the component interface by giving it a name, I have used the name DEMO_PERSDATA_CI, please use the same name if possible Step2: Create Permission List, Role and User profile
In this step we will be creating a basic permission list, role and user id which will be latter used for Node configuration and invoking/consuming web service. Create new Permission list named DEMO_IB_PL and under ‘Component Interfaces’ tab add the CI ‘DEMO_PERSDATA_CI ‘ we created in previous step and give full access to all the methods
Create a role named ‘DEMO_IB_ROLE’ and just add above permission list to it DEMO_IB_PL.
Create a new user id named DEMO_IB_USR. You need to just add the above created role to it, even password is not required. If your system doesn’t allow to save the component without a password then please do enter a password
Step3: Update default Namespace and Target LocationNavigate to ‘Main Menu – PeopleTools – Integration Broker – Configuration – Service Configuration’ under ‘Service Configuration’ tab ensure that ‘Service Namespace’ and ‘Schema Namespace’ are entered. Generally Peoplesoft provides default value for these fields and we can just use it as is. Below URL’s are the default values in my system Service Namespace – http://xmlns.oracle.com/Enterprise/HCM/services
Click the ‘Setup Target Locations’ on ‘Service Configuration’ page. Note in older version of People Tools you might find ‘Target Location’ field on the page itself Enter ‘Target Location’ for ‘Web Services Target Locations’ group box. The target location follows below naming convention. http://<machine>:<port>/PSIGW/PeopleSoftServiceListeningConnector or http://<machine>:<port>/PSIGW/PeopleSoftServiceListeningConnector/<defaultlocalnode> We need to just change following two parameters: <machine>:<port> – This is nothing but the machine name/IP address and port number of Peoplesoft PIA login URL. Say for example if the login page URL of Peoplesoft application is http://testsystem:15000/psp/TESTHCM/?cmd=login http://172.21.61.30:15000/psp/TESTHCM/?cmd=login Then ‘machine:port’ will be ‘testsystem:15000’ or ‘172.21.61.30:15000’ if there is no port number for you login URL then you can ignore port. <defaultlocalnode> – This is default local node for your application/IB. Generally there will be default local node delivered by Peoplesoft. For HRMS system the Peoplesoft delivered local node will be PSFT_HR. Note: To find the default Node of you system please refer to the next section. Given above sample values, our ‘Target Location’ URL looks as below http://testsystem:15000/PSIGW/PeopleSoftServiceListeningConnector/PSFT_HR or http://172.21.61.30:15000/PSIGW/PeopleSoftServiceListeningConnector/PSFT_HR
Similarly set the ‘Target Location’ for ‘Rest Target Locations’ (This is not really required for CI/SOAP based web service but its worth doing it. Older People Tools might not have this option)The url naming convention for REST services is as below http://<machine>:<port>/PSIGW/RESTListeningConnector/<defaultlocalnode> Note: Do not set ‘Secure Target Location’ and leave it blank
Step4: Update Node definition
For out demo web service to work correctly we need to configure following two Nodes. In many cases this nodes come pre-configured but we might need to check/modify certain parameters
Default local node
Anonymous node
Default local node configuration How to Identify default local node:Navigate to ‘Main Menu – PeopleTools – Integration Broker – Integration setup – Nodes’ and search for nodes. A default node is identified by looking at the below two columns values in the search result.
Local Node = 1
Default Local Node = Y
On ‘Node Definitions’ page ensure that the following fields have check box checked against them also ensure that ‘Default User ID’ has valid user id assigned against and user id should be present in the database. Generally this field will have PS as the default if so then just check whether that id exists in the system and not locked.
Default Local Node
Local Node
Active Node
All the above fields would be non-editable fields but its worth checking once!
Anonymous Node Configuration Peoplesoft delivers a node named ‘Anonymous’ which Peoplesoft uses internally for third party inbound integration that do not pass in a requesting node details but do have a defined any-to-local routing definition enabled on the service operation to be invoked. On Node Definition page of Anonymous node ensure that the node is active, Authentication option is set to ‘None’ and enter the user id we created earlier ‘DEMO_IB_USR’ in ‘Default user ID field’ Match the configuration as per below screenshot
Ensure that ‘Authentication Token Type’ is set to ‘None’
Ensure that ‘PT_SOAPTESTER_ROUTE’ routing definition is present and active if not please add it by clicking Add button. If this routing is not present then ‘Generate SOAP Template’ utility which we will be using later for testing will not work
Step5: Create Service and Service Operation based on CI We will be using delivered ‘CI-Based Services’ wizard to create Peoplesoft Service (not web service) and Service Operation automatically. Important: Before you proceed further add the role ‘DEMO_IB_ROLE’ you created in earlier steps to your logged in user id or give access to Component Interface ‘DEMO_PERSDATA_CI‘ in one of the Permission list assigned to your logged in user profile. Failure to give access to the CI for your logged in user id means you will not be able to search for CI in ‘CI-Based Services’ wizard. Navigate to: Main Menu -> PeopleTools -> Integration Broker –> Web Services –> CI-Based Services Search for ‘DEMO_PERSDATA_CI’ under ‘Component Interface Name’ search box
Click ‘Review CI Status’ button, ‘Review Status’ page opens. If you observe on this page there is a read only field labeled Service: which has a value ‘CI_DEMO_PERSDATA_CI’. This is the Peoplesoft ‘Service’ which has been created automatically by the system. The automatically generated Service uses the naming convention CI_<Component Interface Name> Select checkbox against Get method row
Click on ‘Display Selected Actions’‘Confirm Actions’ page pops up. On this page ensure that ‘Inherit All CI Permissions’ check box is checked. If this check box is un-checked then we might need to give the permission separately on the service operation page
Click ‘Perform Selected Actions’ button, the system returns back to ‘Review Status’ page where we can see now the system has generated the service operation named ‘CI_DEMO_PERSDATA_CI_G.V1’. The naming convention for automatically generated Service operation is <Service Name><Method Name 1st Character for Get and Find methods><Message Version Number>
Verify Service Operation (CI_DEMO_PERSDATA_CI_G) that was created aboveOpen the service operation (‘Main Menu – PeopleTools – Integration Broker – Integration setup – Service Operations’) and ensure ‘Any-To-Local’ routing exists if not use ‘Regenerate Any-To-Local’ option on the same page to generate the routing.
Click on ‘Service Operation Security’ link and ensure that ‘DEMO_IB_PL’ permission list exists and has full access. Remember we had checked ‘Inherit All CI Permissions’ on Confirm Actions page in previous step which had ensured that the permission got inherited from CI definition
On the Routings tab ensure that the auto generated routing is Active
Step6: Publish WSDL Document to the Target LocationWe will be using delivered ‘Provide Web Service’ wizard to create and publish WSDL(Web Services Description Language) document for other system. Navigate to ‘Main Menu – PeopleTools – Integration Broker – Web Services – Provide Web Service’Search for the Service Operation that was created in above step and click Next button
In the ‘Select Service Operations’ page select our service operation and click Next button
The ‘View WSDL’ page appears. You can click on ‘View WSDL’ link to view the WSDL document that would be created. Click ‘Next’ button
‘Specify Publishing Options’ page appears. Click ‘Finish’ button to publish the document to the Target location. The target location is the one we configured earlier.
You will see ‘Confirms Results’ page wherein the ‘WSDL Generation Log:’ box we can see what Service has been exported and whether the WSDL was inserted into repository and finally it gives the WSDL URL.Service: CI_DEMO_PERSDATA_CI has been exported.Inserted WSDL: CI_DEMO_PERSDATA_CI.1 in the repositoryGenerated WSDL URL: http://<Your system name/ip:portnumber>/PSIGW/PeopleSoftServiceListeningConnector/PSFT_HR/CI_DEMO_PERSDATA_CI.1.wsdl Copy the ‘Generated WSDL URL’ text and save it in a notepad, we need this URL for testing.Note: You can always regenerate the URL by repeating above steps using ‘Provide Web Service’ wizard
The below step is very important and ensure that this has been done correctly. Peoplesoft uses couple of internal IB services for making web services work. Among then IB_UTILITY service is an important one from web service stand point. This utility has number of service operations which is used for handling WSDL document.
Warning: Peoplesoft strongly recommends not to make any changes for internally used service operations
IB_UTILITY
IB_GENERIC
If you are new to IB then please be careful while performing this activity. Do not modify or delete any other things other than what has been stated below.
The goal of this document was to demonstrate on how to configure a web service from scratch including security. So I am trying to demonstrate bits and pieces of security.
If you don’t want to touch the delivered internal services then please follow the below simple step to get over this configuration and it’s always the better option for testing.
Just add permission list PTPT1200 to the role ‘DEMO_IB_ROLE’ which we created earlier. If you perform this step then there is no need to change the deliver services as mentioned below. One way this is the good option since permission list PTPT1200 is generic one and has been used extensively throughout IB security by PS
If you are ok with the changing the delivered services then follow below step.
Open the service operation GETSCHEMA and on ‘General’ tab click on ‘Service Operation Security’ and add our permission list ‘DEMO_IB_PL’ and give full access.
Similarly for service operation GETWSDL add our custom permission list ‘DEMO_IB_PL’
Testing the WSDL document that was generated
Copy the ‘Generated WSDL URL’ text and paste it on to the browser and press enter. The browser will open an XML file on the page. If the XML on the page displays an error stating(Integration Service: Unable to find a routing corresponding to the incoming request message. (158,505)), then you are in trouble. Since we are not able to view the WSDL document in the browser, the consuming system will not be able to access the WSDL document and hence might not know how to invoke the service
There are multiple reasons for above error and some of the common know problems are
Routing definition doesn’t exists on the target node
Routing definition is inactive
Password issues of Node
Called service operation is inactive
The external alias names for the Service Operation is incorrect
In our case every thing looks correct as we have verified everything in earlier steps and moreover we are not invoking any service at the moment to get these type of issues(right?)!!!. Well its other way actually. As mentioned earlier Peoplesoft does uses internal services for making web service work. Peoplesoft uses/calls GETWSDL service operation of IB_UTILITY services to generate the WSDL document. I did got this error when I tried copy pasting the WSDL URL (in PT 8.43)
To fix this issue open the routing definition under GETWSDL service operation and ensure that the ‘External Alias’ on ‘Parameter’ tab is just GetWSDL and not GetWSDL.V1 etc on both Request and Response type
Now if you try to access again the WSDL link it should open a page similar as below
Step7: Test Newly published WSDL through delivered ‘Generate SOAP Template’ We will test Newly published WSDL service through delivered ‘‘Generate SOAP Template’ utility. This utility is used to generate a Peoplesoft accepted standard SOAP template which can be shared to third party vendors for calling our service. It will have all the SOAP headers for invoking the service operation correctly. Navigate to ‘Main Menu – PeopleTools – Integration Broker – Service Utilities – Generate SOAP Template’
Open our service CI_DEMO_PERSDATA_CI
On ‘Generate SOAP Template’ page click on operation name CI_DEMO_PERSDATA_CI_G.V1’
On ‘SOAP Message Template’ page click on ‘Invoke Service Operation’ Button
‘SOAP Tester’ page pops up. We need to change the value of following tags under ‘SOAP Request Message’ <wsse:Username>, <wsse:Password> and <EMPLID>. Following are the values we need to enter between the tags
<wsse:Username> = DEMO_IB_USR – the user id we created
<wsse:Password> – leave it blank since we have not enable user id/password required option on service operation page
<EMPLID> – Enter the emplid you want to search. If you recall we have just exposed GET method of CI and the CI we created just has EMPLID as the property for GET method so the system automatically exposes only that field
In above SOAP template you need to just change the value of <EMPLID> tag to reflect your local data base and click on ‘Send Message’ button. You will get the response message below the request message and you can see the records based on the id you passed as the parameter. In the below screenshot we can see that for the employee id 00000000300 request message, the service is returning 2 rows of Name related data for the employee based on two different effective date. Also note that the tags from <EMPLID> till <NAMES_DISPLAY> are those properties which we had exposed while creating component interface DEMO_PERSDATA_CI
Lastly below is the simplified version of the SOAP template where we are passing just empty header without any credential information and if you copy paste this in the SOAP request the system will still get the information. Well this is due to the fact that we have not enabled ‘User/Password Required’ on service operation General Tab. To keep it simple, when no credentials are passed the system uses the user id assigned to the requesting node. In our case the requesting node is Anonymous node where we had assigned the user id DEMO_IB_USR in the ‘Default User ID’ field.
In the Pop up dialog box, Enter a Project name and in the ‘Initial WSDL’ field enter the WSDL URL which was generated by Peoplesoft. Also ensure that the ‘Create sample request for all operations’ option is checked. Click ‘Ok’
The SoapUI application will read the WSDL file from the URL and populate the Project window and looks similar as below screen
Double click on the ‘Request 1’ text/link from the project window. The ‘Request 1’ window on the right of the project window gets populated with SOAP template. Please note that the SOAP template has been automatically created by SoapUI application by looking into the contents of WSDL document.
We need to just change the value of ? in between the EMPLID tag <m10:EMPLID>?</m10:EMPLID> to invoke the service.
Enter a value for <EMPLID> tag. For demo I have entered the <EMPLID> tag value as 00000000300 and clicked the run icon. We can see that the response message is returned. If we observe it’s the same response message that was returned during our ‘ Generate SOAP Template’
Lastly we can just copy paste the SOAP template XML that was generated by PS(with values of tag substituted) during testing using ‘ Generate SOAP Template’ into the Request window and run the service. We can see that we will get the response/output as before
This completes the end-to-end creation of web service based on CI.
PeopleSoft Tips & Tricks 
