Essbase Connector Error – Client Commands are Currently Not Being Accepted

DataStage Essbase Connector, Essbase Connector Error, Client Commands are Currently Not Being Accepted

DataStage Essbase Connector

While investigating a recent Infosphere Information Server (IIS), Datastage, Essbase Connect error I found the explanations of the probable causes of the error not to be terribly meaningful.  So, now that I have run our error to ground, I thought it might be nice to jot down a quick note of the potential cause of the ‘Client Commands are Currently Not Being Accepted’ error, which I gleaned from the process.

Error Message Id

  • IIS-CONN-ESSBASE-01010

Error Message

An error occurred while processing the request on the server. The error information is 1051544 (message on contacting or from application:[<<DateTimeStamp>>]Local////3544/Error(1013204) Client Commands are Currently Not Being Accepted.

Possible Causes of The Error

This Error is a problem with access to the Essbase object or accessing the security within the Essbase Object.  This can be a result of multiple issues, such as:

  • Object doesn’t exist – The Essbase object didn’t exist in the location specified,
  • Communications – the location is unavailable or cannot be reached,
  • Path Security – Security gets in the way to access the Essbase object location
  • Essbase Security – Security within the Essbase object does not support the user or filter being submitted. Also, the Essbase object security may be corrupted or incomplete.
  • Essbase Object Structure –  the Essbase object was not properly structured to support the filter or the Essbase filter is malformed for the current structure.

Related References

IBM Knowledge Center, InfoSphere Information Server 11.7.0, Connecting to data sources, Enterprise applications, IBM InfoSphere Information Server Pack for Hyperion Essbase

Printable PDF Version of This Article

 

DataStage – How to use single quoted parameter list in an Oracle Connector

Data Integration

Data Integration

While working with a client’s 9.1 DataStage version, I ran into a situation where they wanted to parameterize SQL where clause lists in an Oracle Connector stage, which honestly was not very straight forward to figure out.  First, if the APT_OSL_PARAM_ESC_SQUOTE is not set and single quotes are used in the parameter, the job creates unquoted invalid SQL when the parameter is populated.  Second, I found much of the information confusing and/or incomplete in its explanation.   After some research and some trial and error, here is how I resolved the issue.  I’ll endeavor to be concise, but holistic in my explanation.

When this Variable applies

This where I know this process applies, there may be other circumstances to which is this applicable, but I’m listing the ones here with which I have recent experience.

Infosphere Information Server Datastage

  • Versions 91, 11.3, and 11.5

Oracle RDBMS

  • Versions 11g and 12c

Configurations process

Here is a brief explanation of the steps I used to implement the where clause as a parameter.  Please note that in this example, I am using a job parameter to populate on a portion of the where clause, you can certainly pass the entire where clause as a parameter, if it is not too long.

Configure Project Variable in Administrator

  • Add APT_OSL_PARAM_ESC_SQUOTE to project in Administrator
  • Populate the APT_OSL_PARAM_ESC_SQUOTE Variable \
APT_OSL_PARAM_ESC_SQUOTE Project Variable

APT_OSL_PARAM_ESC_SQUOTE Project Variable

Create job parameter

Following your project name convention or standard practice, if you customer and/or project do not have established naming conventions, create the job parameter in the job. See jp_ItemSource parameter in the image below.

Job Parameter In Oracle Connector

Job Parameter In Oracle Connector

Add job parameter to Custom SQL in Select Oracle Connector Stage

On the Job parameter has been created, add the job parameter to the SQL statement of the job.

Job Parameter In SQL

Job Parameter In SQL

Related References

IBM Knowledge Center > InfoSphere Information Server 11.5.0

Connecting to data sources > Databases > Oracle databases > Oracle connector

IBM Support > Limitation of the Parameter APT_OSL_PARAM_ESC_SQUOTE on Plugins on Parallel Canvas

IBM Knowledge Center > InfoSphere Information Server 11.5.0

InfoSphere DataStage and Quality > Stage > Reference > Parallel Job Reference > Environment Variables > Miscellaneous > APT_OSL_PARAM_ESC_SQUOTE

 

How to know if your Oracle Client install is 32 Bit or 64 Bit

Oracle Database, How to know if your Oracle Client install is 32 Bit or 64 Bit

Oracle Database

 

How to know if your Oracle Client install is 32 Bit or 64 Bit

Sometimes you just need to know if your Oracle Client install is 32 bit or 64 bit. But how do you figure that out? Here are two methods you can try.

The first method

Go to the %ORACLE_HOME%\inventory\ContentsXML folder and open the comps.xml file.
Look for <DEP_LIST> on the ~second screen.

If you see this: PLAT=”NT_AMD64” then your Oracle Home is 64 bit
If you see this: PLAT=”NT_X86” then your Oracle Home is 32 bit.

It is possible to have both the 32-bit and the 64-bit Oracle Homes installed.

The second method

This method is a bit faster. Windows has a different lib directory for 32-bit and 64-bit software. If you look under the ORACLE_HOME folder if you see a “lib” AND a “lib32” folder you have a 64 bit Oracle Client. If you see just the “lib” folder you’ve got a 32 bit Oracle Client.

Related References

 

OLTP vs Data Warehousing

Database, OLTP vs Data Warehousing

Database

OLTP Versus Data Warehousing

I’ve tried to explain the difference between OLTP systems and a Data Warehouse to my managers many times, as I’ve worked at a hospital as a Data Warehouse Manager/data analyst for many years. Why was the list that came from the operational applications different than the one that came from the Data Warehouse? Why couldn’t I just get a list of patients that were laying in the hospital right now from the Data Warehouse? So I explained, and explained again, and explained to another manager, and another. You get the picture.
In this article I will explain this very same thing to you. So you know  how to explain this to your manager. Or, if you are a manager, you might understand what your data analyst can and cannot give you.

OLTP

OLTP stands for OLine Transactional Processing. With other words: getting your data directly from the operational systems to make reports. An operational system is a system that is used for the day to day processes.
For example: When a patient checks in, his or her information gets entered into a Patient Information System. The doctor put scheduled tests, a diagnoses and a treatment plan in there as well. Doctors, nurses and other people working with patients use this system on a daily basis to enter and get detailed information on their patients.
The way the data is stored within operational systems is so the data can be used efficiently by the people working directly on the product, or with the patient in this case.

Data Warehousing

A Data Warehouse is a big database that fills itself with data from operational systems. It is used solely for reporting and analytical purposes. No one uses this data for day to day operations. The beauty of a Data Warehouse is, among others, that you can combine the data from the different operational systems. You can actually combine the number of patients in a department with the number of nurses for example. You can see how far a doctor is behind schedule and find the cause of that by looking at the patients. Does he run late with elderly patients? Is there a particular diagnoses that takes more time? Or does he just oversleep a lot? You can use this information to look at the past, see trends, so you can plan for the future.

The difference between OLTP and Data Warehousing

This is how a Data Warehouse works:

How a Data Warehouse works

How a Data Warehouse works

The data gets entered into the operational systems. Then the ETL processes Extract this data from these systems, Transforms the data so it will fit neatly into the Data Warehouse, and then Loads it into the Data Warehouse. After that reports are formed with a reporting tool, from the data that lies in the Data Warehouse.

This is how OLTP works:

How OLTP works

How OLTP works

Reports are directly made from the data inside the database of the operational systems. Some operational systems come with their own reporting tool, but you can always use a standalone reporting tool to make reports form the operational databases.

Pro’s and Con’s

Data Warehousing

Pro’s:

  • There is no strain on the operational systems during business hours
    • As you can schedule the ETL processes to run during the hours the least amount of people are using the operational system, you won’t disturb the operational processes. And when you need to run a large query, the operational systems won’t be affected, as you are working directly on the Data Warehouse database.
  • Data from different systems can be combined
    • It is possible to combine finance and productivity data for example. As the ETL process transforms the data so it can be combined.
  • Data is optimized for making queries and reports
    • You use different data in reports than you use on a day to day base. A Data Warehouse is built for this. For instance: most Data Warehouses have a separate date table where the weekday, day, month and year is saved. You can make a query to derive the weekday from a date, but that takes processing time. By using a separate table like this you’ll save time and decrease the strain on the database.
  • Data is saved longer than in the source systems
    • The source systems need to have their old records deleted when they are no longer used in the day to day operations. So they get deleted to gain performance.

Con’s:

  • You always look at the past
    • A Data Warehouse is updated once a night, or even just once a week. That means that you never have the latest data. Staying with the hospital example: you never knew how many patients are in the hospital are right now. Or what surgeon didn’t show up on time this morning.
  • You don’t have all the data
    • A Data Warehouse is built for discovering trends, showing the big picture. The little details, the ones not used in trends, get discarded during the ETL process.
  • Data isn’t the same as the data in the source systems
    • Because the data is older than those of the source systems it will always be a little different. But also because of the Transformation step in the ETL process, data will be a little different. It doesn’t mean one or the other is wrong. It’s just a different way of looking at the data. For example: the Data Warehouse at the hospital excluded all transactions that were marked as cancelled. If you try to get the same reports from both systems, and don’t exclude the cancelled transactions in the source system, you’ll get different results.

online transactional processing (OLTP)

Pro’s

  • You get real time data
    • If someone is entering a new record now, you’ll see it right away in your report. No delays.
  • You’ve got all the details
    • You have access to all the details that the employees have entered into the system. No grouping, no skipping records, just all the raw data that’s available.

Con’s

  • You are putting strain on an application during business hours.
    • When you are making a large query, you can take processing space that would otherwise be available to the people that need to work with this system for their day to day operations. And if you make an error, by for instance forgetting to put a date filter on your query, you could even bring the system down so no one can use it anymore.
  • You can’t compare the data with data from other sources.
    • Even when the systems are similar. Like an HR system and a payroll system that use each other to work. Data is always going to be different because it is granulated on a different level, or not all data is relevant for both systems.
  • You don’t have access to old data
    • To keep the applications at peak performance, old data, that’s irrelevant to day to day operations is deleted.
  • Data is optimized to suit day to day operations
    • And not for report making. This means you’ll have to get creative with your queries to get the data you need.

So what method should you use?

That all depends on what you need at that moment. If you need detailed information about things that are happening now, you should use OLTP.
If you are looking for trends, or insights on a higher level, you should use a Data Warehouse.

 Related References

 

 

Oracle – How to get a list of user permission grants

IBM Infosphere Information Server (IIS), Oracle – How to get a list of user permission grants

IBM Infosphere Information Server (IIS)

Since the Infosphere, information server, repository, has to be installed manually with the scripts provided in the IBM software, sometimes you run into difficulties. So, here’s a quick script, which I have found useful in the past to identify user permissions for the IAUSER on Oracle database’s to help rundown discrepancies in user permissions.

 

SELECT *

FROM ALL_TAB_PRIVS

WHERE  GRANTEE = ‘iauser’

 

If we cannot run against the ALL_TAB_PRIVS view, then we can try the ALL_TAB_PRIVS view:

 

SELECT *

FROM USER_TAB_PRIVS

WHERE  GRANTEE = ‘iauser’

 

Related References

oracle help Center > Database Reference > ALL_TAB_PRIVS view

Databases – Database Isolation Level Cross Reference

Database Type Isolation Levels Cross Reference

Database And Tables

 

Here is a table quick reference of some common database and/or connection types, which use connection level isolation and the equivalent isolation levels. This quick reference may prove useful as a job aid reference, when working with and making decisions about isolation level usage.

Database isolation levels

Data sources

Most restrictive isolation level

More restrictive isolation level

Less restrictive isolation level

Least restrictive isolation level

Amazon SimpleDB

Serializable Repeatable read Read committed Read Uncommitted

dashDB

Repeatable read Read stability Cursor stability Uncommitted read

DB2® family of products

Repeatable read Read stability* Cursor stability Uncommitted read

Informix®

Repeatable read Repeatable read Cursor stability Dirty read

JDBC

Serializable Repeatable read Read committed Read Uncommitted

MariaDB

Serializable Repeatable read Read committed Read Uncommitted

Microsoft SQL Server

Serializable Repeatable read Read committed Read Uncommitted

MySQL

Serializable Repeatable read Read committed Read Uncommitted

ODBC

Serializable Repeatable read Read committed Read Uncommitted

Oracle

Serializable Serializable Read committed Read committed

PostgreSQL

Serializable Repeatable read Read committed Read committed

Sybase

Level 3 Level 3 Level 1 Level 0

 

Related References

 

Oracle SQL – How to limit the number of rows returned by a select query

Oracle doesn’t recognize the ’Limit’ command, however, there is any easy way to achieve the same result.  Using the Oracle/function in the ‘where’ clause.

ROWNUM Pseudo Column Description

The Oracle/PLSQL ROWNUM pseudo column returns a number that represents the order that a row is selected by Oracle from a table or joined tables. The first row has a ROWNUM of 1, the second has a ROWNUM of 2, and so on.

Basic RUWNUM Syntax

SELECT column_name(s)

FROM Schema.table_name

WHERE ROWNUM <= number

Order by column_name(s);

 

Example SQL Limiting to One row

The one row limit has two accurate way to get the desired result:

·       SQL Limiting to One row using equal to

SELECT RSNBL_CODE,

MVNG_AVG1_PRD

FROM Blog.SCP_FCST_CONTROL

where rownum = 1;

·       SQL Limiting to One row using less than or equal to

SELECT RSNBL_CODE,

MVNG_AVG1_PRD

FROM Blog.SCP_FCST_CONTROL

where rownum <= 1;

Oracle Rownum Limited to One Row

Oracle Rownum Limited to One Row

 

Example SQL Limiting to multiple rows

SELECT RSNBL_CODE,

MVNG_AVG1_PRD

FROM Blog.SCP_FCST_CONTROL

where rownum <= 10;

Oracle Rownum Limited to Multiple Rows

Oracle Rownum Limited to Multiple Rows

 

Oracle JDBC ISJDBC.CONFIG Configuration

 JDBC ( Java Database Connectivity)

JDBC ( Java Database Connectivity)

This jdbc information is based on Oracle Database 11g Release 2 (11.2.0.4), on a RAC (Oracle Real Application Clusters), JDBC for InfoSphere Information Server11.5 on Red Hat Linux .  so, here are a few pointers for building an IBM InfoSphere Information Server (IIS) isjdbc.config file.

Where to place JAR files

For Infosphere Information Server installs, as a standard practice, create a custom jdbc file in the install path.  And install any download Jar file not already installed by other applications in the jdbc folder. Usually, jdbc folder path looks something like this:

  • /opt/IBM/InformationServer/jdbc

CLASSPATH

  • jar
  • Classpath must have complete path and jar name

CLASS_NAMES

  • jdbc.OracleDriver

JAR Source URL

  • In this example we used the jar files from the client install, but if you want to skip the client install you can down load the drivers here: Oracle JDBC Drivers

DB2 DEFAULT PORT

  • 1521

JDBC URL FORMAT

  • jdbc:oracle:thin:@//:/ServiceName

or

  • jdbc:oracle:thin:@<host>:<port>:<SID>

JDBC URL EXAMPLE

  • jdbc:oracle:thin:@//RAC01-scan:1521/DW

isjdbc.config EXAMPLE

CLASSPATH=/u01/app/oracle/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar;

CLASS_NAMES=oracle.jdbc.OracleDriver

Isjdbc.config FILE PLACEMENT

  • /opt/IBM/InformationServer/Server/DSEngine

Related References

Structured Query Language (SQL) Tuning

Structured Query Language (SQL) Tuning

Structured Query Language (SQL) Tuning

Tuning SQL is one of those skills, which is part art and part science.  However, there are a few fundamental approaches, which can help ensure optimal SQL select statement performance.

Structuring your SQL

By Structuring SQL Statements, much performance can be gained through good SQL statement organization and sound logic.

Where Clause Concepts:

Use criteria ordering and Set Theory thinking.  SQL  can be coupled with set-theory to aid conception of the operations being conducted. Order your selection criteria  to execute criteria which arrives at the smallest possible row set first. Doing so, reduces the volume of rows to be processed by follow-on operations. This does require an understanding of the data relationships to be effective.

SQL First Select Criteria Pie Chart

SQL First Select Criteria Pie Chart

 

Join Rules (equi joins, etc.)

When  constructing your joins, consider these rules:

  • Join on keys and indexed columns: The efficiency of your program improves when tables are joined based on indexed columns, rather than on non-indexed ones.
  • Use equi-joins (=), whenever possible
  • Avoid using of sub-queries
  • Re-write EXISTS and NOT EXISTS subqueries as outer joins
  • Avoid OUTER Joins on fields containing nulls
  • Avoid RIGHT OUTER JOINS: Always select FROM your primary table (or derived table) and LEFT OUTER JOIN to auxiliary tables.
  • Use Joins Instead of Subqueries: A join can be more efficient than a correlated subquery or a subquery using IN. Use caution when specifying ORDER BY with a Join: When the results of a join must be sorted, limiting the ORDER BY to columns of a single table can cause the database to avoid a sort.
  • Provide Adequate Search Criteria: When possible, provide additional search criteria in the WHERE clause for every table in a join. These criteria are in addition to the join criteria, which are mandatory to avoid Cartesian products

Order of Operations SQL & “PEMDAS”

To improve your SQL, careful attention needs to be paid to the mathematical order of operations; especially, parentheses since they not only set the order of operation, but also the boundaries of each subset operation.

  • PEMAS is “Parentheses, Exponents, Multiplication and Division, and Addition and Subtraction”.
  • Use parentheses () to group and specify the order of execution. SQL observes the normal rules of arithmetic operator precedence.
Precedence Operator(s) Operation(s) Notes
1 ( ) Parentheses If the parentheses are nested, the expression in the innermost pair is evaluated first. If there are several un-nested parentheses, then parentheses are evaluated left to right.
2 *
/
%
Multiplication
Division
Modulus
If there are several, evaluation is left to right.
3 +
Addition
Subtraction
If there are several, evaluation is left to right.

 

Index Leveraging (criteria ordering, hints, append, etc.)

  • Avoid Full Table Scans: within the scope of a SQL statement, there are many conditions that will cause the SQL optimizer to invoke a full-table scan.  Avoid Queries:
  • with NULL Conditions (Is NUll, Is Not NUll)
  • Against Unindexed Columns
  • with Like Conditions
  • with Not Equals Condition (<>, !=, not in)
  • with use built-in Function (to_char, substr, decode, UPPER)
  • Use UNION ALL instead of UNION if business rules allow
  • UNION: Specifies that multiple result sets are to be combined and returned as a single result set. Query optimizer performs extra work to return to avoid duplicate rows.
  • UNION ALL: Incorporates all rows into the results. This includes duplicates. Query optimizer just needs to concatenate the result sets with no extra work
  • Use stored procedures instead of ad hoc queries when possible. Stored procedures are precompiled and cached
  • Avoid cursor use when possible
  • Select only the rows needed
  • Use NOLOCK hint in select statement to avoid blocking
  • Commit transactions in smaller batches
  • Whenever possible use tables instead of views
  • Make sure comparison columns whether using JOIN or WHERE clause are exactly same data type. For example if we are comparing Varchar column to nchar columns the query optimizer has to do a CONVERT before comparing the values

Note: You do not necessarily need to remove all full table scans from your query’s execution plan. Tables with few rows, few columns, or thin columns may fit into few database blocks. In this case, a full table scan will always be the most efficient access

Oracle SQL – Removing Non-Numeric Characters

SQL (Structured Query Language, Oracle, SQL, Removing Non-Numeric Characters

SQL (Structured Query Language)

Sometimes, when converting fields from text to numeric fields in SQL, there is a need to remove one or more non-numeric values.  These approaches have been useful for handling multiple non-numeric values.

Removing non numeric values – Approach 1:

I have found this approach useful, when working with character (Char, Varchar, NVARCHAR) fields being converted to non-integers outputs (e.g. Decimal).

CAST(( CASE WHEN TRIM(<<SourceFieldName>>) > ‘9999999999’ or  length(REGEXP_SUBSTR(<<SourceFieldName>>, ‘[A-z]’, 1, 1))>0 or trim(<<SourceFieldName>>) in (‘-‘,’+’,’?’,’/’,’*’) THEN NULL ELSE trim(<<SourceFieldName>>) END) AS    DECIMAL(10,2)) AS <<TargetFieldName>>,

Removing non numeric values – Approach 2:

 

If you need to know if a content of a field is numeric, I have most this approach useful when working with character (Char, Varchar, NVARCHAR) fields being converted to integers:

CAST(( CASE WHEN LENGTH(TRANSLATE(<<SourceFieldName>>,’0123456789′,”))=0 THEN (<<SourceFieldName>> ELSE NULL END) as INTEGER) as <<TargetFieldName>>,

Related Posts

Oracle SQL Date Formatting Examples

Oracle Database, SQL Date Formatting Examples, TO_CHAR

Oracle Database – SQL Date Formatting Examples

Below is a SQL of various examples of Oracle date formats, which I have found to be a useful reference.

 

Select

SYSDATE,

TO_CHAR(SYSDATE,’YYYY-MM-DD AD’) AS ANNO_DOMINI,

TO_CHAR(SYSDATE,’YYYYMMDD’) AS SORT_PATTERN1,

TO_CHAR(SYSDATE,’YYYY/MM/DD’) AS DATE_PATTERN1,

TO_CHAR(SYSDATE,’DD/MM/YYYY’) AS DATE_PATTERN2,

TO_CHAR(SYSDATE,’DD/MON/YYYY’) AS DATE_PATTERN3,

TO_CHAR(SYSDATE,’DD-MON-YY’) AS DATE_PATTERN4,

TO_CHAR(SYSDATE,’CC’) AS CENTURY,

TO_CHAR(SYSDATE,’YYYY’) AS FOUR_DIGIT_YEAR,

TO_CHAR(SYSDATE,’YY’) AS YEAR_OF_CENTURY,

TO_CHAR(SYSDATE,’J’) AS JUALIAN_DATE,

TO_CHAR(SYSDATE,’Q’) AS CALENDAR_QUARTER_OF_YEAR,

TO_CHAR(SYSDATE,’WW’) AS CALENDAR_WEEK_OF_YEAR,

TO_CHAR(SYSDATE,’DDD’) AS NUMBER_OF_CALENDAR_DAY_OF_YEAR,

TO_CHAR(SYSDATE,’W’) AS WEEK_OF_MONTH,

TO_CHAR(SYSDATE,’DAY’) AS NAME_OF_DAY_OF_WEEK,

TO_CHAR(SYSDATE,’DY’) AS ABBREVIATED_DAY_OF_WEEK,

TO_CHAR(SYSDATE,’MM’) AS NUMERIC_MONTH_OF_YEAR,

TO_CHAR(SYSDATE,’MON’) AS ABBREVIATED_MONTH_OF_YEAR,

TO_CHAR(SYSDATE,’MONTH’) AS SPELLED_OUT_MONTH_OF_YEAR

FROM DUAL;

 

Related References

 

Oracle SQL – Trimming a Specific Literal from a Field

Oracle Database

Oracle Database

I have frequently used the trim command for one purpose or another, but I have not used it to extract a literal out of a field, which I recently used to extract invalid values from a field in SQL.  So, I am providing a sample SQL for Future reference.

 

SQL Pattern:

  Select trim (‘I’ FROM <<FieldName>>)  AS <<FieldName>>

From <<TableName>>;

 

Substitution example:

In this example an invalid indicator (‘Y’) is being removed, performing a Null Value Logic (NVL) substitution to a integer compatible value, then the field is converted to an integer.

 

Select

CAST( NVL(trim (‘Y’ FROM AA.Status_ID) ,’0′)AS                   INT)         AS PACK_SZ

From  Inventory_code_TBL AA;

 

Reference Links:

 

 

PS Query Timestamp to Date Logic

Oracle Peoplesoft Query Timestamp to Date Logic

Oracle Peoplesoft

Converting a timestamp to a date in PeopleSoft Query seems to be a common item, which I have helped others with, to the point that I kept a personal note on the coding logic.  So, I thought I would post this quick note in case it may be useful to someone.  This item would seem to be straight forward, however, PS query tries to help you with the process, so, some experimentation is usually involved to get the code to out just right.

Here is a sample expression I use for the timestamp to date conversion in PS Query.

  • Create an expression with an Expression Type of “Number
  • Set Length to 10
  • Sample code: TO_DATE(SUBSTR(<<RecordAlias.TimestampFieldName>>,1,10))
  • PS Query interpretation: TO_DATE(SUBSTR(TO_CHAR(CAST((<<RecordAlias.TimestampFieldName>>) AS TIMESTAMP),’YYYY-MM-DD-HH24.MI.SS.FF’),1,10))

Related References

PS Query Migration Validation SQLs

Oracle Peoplesoft Query Migration Validation SQL

Oracle Peoplesoft

While PeopleSoft PeopleTools Application Designer is the best way to validate query migrations, sometimes a few SQL scripts can be helpful when verifying that queries and associated tree exist in an environment with the necessary nodes.  These SQL can be run in the appropriate SQL editor and were written against an Oracle RDBMS.

Is Query in the Environment?

SELECT DISTINCT QRYNAME

FROM PSQRYFIELD

WHERE QRYNAME LIKE ’<<QUERYNAME>>%’;

 

Is Query Tree in Environment?

SELECT TREE_NAME

FROM PSTREENODE

WHERE TREE_NAME =’<<TREENAME>>’

ORDER BY 1;

 

Is Record in Query Security Tree?

SELECT TREE_NODE

FROM PSTREENODE

WHERE TREE_NAME =’<<TREENAME>>’

AND TREE_NODE LIKE ’%<<RecordName>>%’;

 

Related References

How to Verify PeopleSoft Record Build in Oracle

Oracle Peoplesoft Record Build verification

Oracle Peoplesoft

A frequent problem during migrations of PeopleSoft record definitions is the omission of a record build, even though the record exists in the PeopleTools project Meta Data.  An easily way to solve this issue is to run a SQL script to test the new record exist and/or that altered record field(s) exist.  A simple count script can provide the necessary answers and save lost project time and reduce testing defect research and resolution activities up front.  Here a couple of quick samples, which can be assemble in to a script and run in Oracle SQL Developer as a script.

Verify that a new PeopleSoft record exists in Oracle

SELECT COUNT(*) AS CNT_<<RecordName>>

FROM PS_<<RecordName>>;

 

Verify that a new field exists on an existing PeopleSoft record in Oracle

 

SELECT COUNT(<<FieldName>>) As CNT_<<RecordName_FieldName>>

FROM PS_<<RecordName>>;

Note: This SQL can also, be used to see if a field was dropped, only it should produce an error for the removed field.

Related References

 

 

How to Add User Identification to a PeopleSoft Query?

Oracle Peoplesoft, How to Add User Identification to a PeopleSoft Query

Oracle Peoplesoft

In PeopleSoft (PS) query, it is possible to add the user name of the person who ran the query as a column.  To do so create an expression, substring the %Operator global variable, and use the expression as a field.

Example PeopleSoft Query Expression for UserID

SUBSTRING(%Operator,1,30)

PeopleSoft XLAT Value Lookup SQL

Oracle, Peoplesoft, XLAT Lookup SQL, PSXLATITEM

Oracle Peoplesoft, XLAT Lookup SQL

I’ve had this SQL snippet laying around for a while, so, before I lose it, I thought I would post it for future reference.  Below is SQL to lookup a XLAT values, in case you don’t want to or cannot go through PeopleSoft Application Designer.

XLAT Lookup SQL

SELECT XLAT.FIELDNAME,

XLATFIELDVALUE,

XLAT.XLATLONGNAME

FROM PSXLATITEM XLAT

WHERE XLAT.FIELDNAME = ‘<<FieldName>>’

AND XLAT.EFF_STATUS = ‘A’

AND XLAT.EFFDT = ( SELECT MAX(XLAT1.EFFDT)

FROM PSXLATITEM XLAT1

WHERE  XLATFIELDNAME =XLAT1.FIELDNAME

AND XLATFIELDVALUE = XLAT1.FIELDVALUE)

ODER BY XLAT.FIELDVALUE

Related References

What is the difference between left join and left outer join in SQL

When working with different databases syntax can cause questions and confusion.  Recent having been ask what the difference was between a left join and a left outer join, a subject which I hadn’t thought about in a while, I thought a simple explanation might be in order.   Actually, there is no difference between a left join and a left outer join, other than syntax.  Both perform the exact same operation in SQL, where the Left (Outer) Join will retain those rows for which there was a match in both tables and, also, retain those rows which exist only in the left (controlling) table of the join.

Left Outer Join Relationship, SQL, Outer Join,

Left Outer Join Relationship