Blogs | Hexaware

Blogs


Latest Post
In scenarios when a table in the staging area or in the data warehouse needs to be queried to find the changed records (inserted or updated), we can use the Scratch table design. Scratch table is a temporary table that can be designed to hold the changes happening against a table, once the changes are noted by the required application or process then the Scratch table can be cleaned-off. The process to capture the changes and the clean up would be designed as part of ETL process. The scenarios where to use this concept and the steps to use the…
Posted by Muneeswara C Pandian
Comments (0)
August 1st, 2007
We all probably know that “CTRL J” hot key gives us the system information. But did you know that there are a lot more available. But you do not have to trouble your brain to remember them, simply press navigate to a search or transaction page and invoke hot key “CTRL K” to get all the information. Below are the results from PeopleSoft "CTRL K". Accessing your application using the keyboard Keyboard navigation is controlled by Hot keys and Access keys . List of Hot Keys Alt 1-- Executes different buttons depending on the page type > Save button on…
Posted by Nitin Pai
Comments (1)
July 31st, 2007
In my last post, I discussed the “Power of Ten” – Ten critical success factors for realizing the dream of using Business Intelligence to run business 'intelligently' across strategic, tactical & operational domains. First in that list are the “Transaction Processing Systems” themselves. The whole fascinating world of Data Warehousing, BI, Analytics et al owes its existence in large measure to the ubiquitous, all powerful, business transaction processing systems, in short, OLTP systems. Business, when stripped of its abstractness, is actually a continuum of activities or transactions. The two broad categories of business operations, viz. making & selling, are operationalized…
Posted by Karthikeyan Sankaran
Comments (0)
July 27th, 2007
Sometime back I had a ticket asking me to make a PRIVATE query PUBLIC. The query was created by a superuser who had quit the company and whose OPRID was deleted. Here are the SQL’s you need to execute if you need to make a PRIVATE query PUBLIC using a data fix. The other straightforward alternative is to log on as the owner of the PRIVATE query and save as PUBLIC (which is not applicable in this case). UPDATE PSQRYBIND SET OPRID = ' ' WHERE OPRID = 'NPAI' AND QRYNAME = 'HX_CUSTOM_QUERY'; UPDATE PSQRYBINDLANG SET OPRID = ' '…
Posted by Nitin Pai
Comments (0)
July 26th, 2007
Ever had trouble identifying the runstatus in PSPRCSRQST table with what it meant. Run the following SQL to determine what it means. SELECT C.FIELDVALUE, C.XLATLONGNAME, C.XLATSHORTNAME,C.FIELDNAME,TO_CHAR(C.EFFDT,'YYYY-MM-DD') FROM PSXLATITEM C WHERE C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PSXLATITEM C_ED WHERE C.FIELDNAME = C_ED.FIELDNAME AND C.FIELDVALUE = C_ED.FIELDVALUE AND C_ED.EFFDT <= SYSDATE) AND C.FIELDNAME = 'RUNSTATUS' AND C.EFF_STATUS = 'A'
Posted by Nitin Pai
Comments (1)
July 23rd, 2007
UNIX command for the PS DBA – find In several of my previous projects, we used to get 4-5 tickets per week because of space related issues. Finally we implemented automated scripts to take care of this issue. The PS_HOME and psreports are the two main directories which have to be monitored for space growth. Below I explain the relevant examples of the UNIX command “find” that you can use to build your shell script. find $PS_HOME -name '*.AET' The above command will recursively find all files with .AET extension residing in the PS_HOME directory. find $PS_HOME -name '*.AET' -mtime…
Posted by Nitin Pai
Comments (0)
July 17th, 2007
When we receive the data from source systems, the data file will not carry a flag indicating whether the record provided is new or has it changed. We would need to build process to determine the changes and then push them to the target table. There are two steps to it Pull the incremental data from the source file or table Process the pulled incremental data and determine the impact of it on the target table as Insert or Update or Delete Step 1: Pull the incremental data from the source file or table If source system has audit columns…
Posted by Muneeswara C Pandian
Comments (3)
July 13th, 2007
Trace SQR The most common approach to tracing SQR is to use the–DEBUG or –S flag. In this post, I will share the techniques I use to generate the SQL Trace that I can use to troubleshoot and determine the bad SQL. If I need to trace a SQR in a development environment, I choose to modify the SQR and include the following procedure which will be called at the start of the program. begin-procedure SetSQLTrace ! Set SQL Trace begin-SQL ALTER SESSION SET SQL_TRACE = TRUE; end-SQL end-procedure However, in a production or any other environment which is under…
Posted by Nitin Pai
Comments (0)
July 12th, 2007
CLIENT_INFO Pinpointing PeopleSoft user sessions to Oracle session is vital for troubleshooting performance related issues. Previously, I used some crude techniques (using the V$SESSION.STATUS = ‘ACTIVE’ and V$SESSION.PROGRAM) to map the PeopleSoft session to the Oracle session. This was not always accurate in a production system where multiple user sessions are active at the same time and running the same program. I came across V$SESSION.CLIENT_INFO and it made my life so much easier… PeopleSoft populates CLIENT_INFO by default for all 2-tier sessions, COBOL, SQR and Process Scheduler. However, for 3-tier connections we need to enable ‘EnableDBMonitoring = 1’ in the…
Posted by Nitin Pai
Comments (0)
July 6th, 2007
In the last post, I discussed my view of BI Utopia in which information is available to all stakeholders at the right time, in the right format enabling them to make actionable decisions at both strategic & operational levels. Having said that, the BI street is not paved with gold. I consider the following key enablers as pre-requisites to achieve true 'Information Democracy’ in an enterprise. The "Power of Ten" includes: Proliferation of agile, modular & robust transaction processing systems. Real Time Data Integration Components Strong Data Governance structure Service Oriented Architecture Extensible Business centric Data Models Flexible business rules…
Posted by Karthikeyan Sankaran
Comments (0)
July 6th, 2007
Contact Us