Category Archives: DataStage

This blog is moving to a new home!

From May, 2010 now new content will be published here, our new home is at:

http://www.shangyezhineng.com/blog/

You can also subscribe to our RSS feed through FeedBurner:

商业智能 Shangyè Zhìnéng (BI) The Independent Business Intelligence Blog

DataStage: ParamName does not reference a known parameter of the job

Today I got an error that reads like this:

JobControl (@<JOB_NAME>): Controller problem: Error
calling DSSetParam($<PARAMETER_NAME>), code=-3
[ParamName does not reference a known parameter of the job]

After a quick debug/issue tracking session on Director I found out that
one of the jobs in my master sequence was missing all of our standard
parameters with database user, schema and password, since we define
them at the project level all I had to do is click on All to
default and the problem was promptly fixed.

PeopleSoft EPM:Applying Maintenance Packs and Bundles (Updates)

We had one or two issues related to DataStage ETL maps that were skipped when we applied MP5 and multiple bundles on our development environment.  To prevent this issue from happening again I decided to re-install all of our ETL maps from scratch using a Sharepoint wiki to keep track of the update level applied to each of our projects (Main + CS & HCM Integration Updates), order of the files. The format is simple and looks very much like a check-list where the team member applying the patches initials or types in his or her name as each file is imported.  Check it out:

[Project] EPM90_OWE_MDX
Common + HCM OWE + HCM MDX Cummulative Maintenance Pack 5 (MP5) (Feb 28, 2009)
COMMON
File
Contents
Applied By
Common_Utilities_E_E1.dsx

Common Utilities

Ignacio
Common_E.dsx

Common Jobs for SETUP_OWE,SETUP_DIMENSION_MAPPER,
COMMON_DIMENSIONS,GLOBAL_DIMENSIONS

Ignacio
OWE_E.dsx

All E OWE Jobs for all Warehouses

Ignacio
HCM
File
Contents
Applied By
WHR_OWS_E.dsx

E – SETUPS and OWS Jobs

N/A
WHR_MDW_LOCAL_DIMS_E.dsx

E – LOCAL DIMENSIONS

Ignacio
WHR_COMPENSATION_MART_E.dsx

E – MDW Jobs (SKU)

Ignacio
WHR_LEARNING_AND_DEVELOPMENT_MART_E.dsx

E – MDW Jobs (SKU)

N/A
WHR_RECRUITING_MART_E.dsx

E – MDW Jobs (SKU)
Ignacio
WHR_WORKFORCE_PROFILE_MART_E.dsx
E – MDW Jobs (SKU)
Ignacio

Leverage PeopleSoft EPM Data Lineage Spreadsheets

Not having to do all the leg work necessary to arrive at table-level data lineage in the PeopleSoft EPM Warehouse is a great testimony to good documentation and a great culture back there at big PS, for those of you that enjoy this benefit, here’s some suggestions on how to leverage it:

1) [Re]organize the excel spreadsheet so that it reflects the sequence of your ETL schedule and if time allows add simple job dependencies.

2) Yes, consolidate all the lineage worksheets into a single centralized list (or at least one per business domain [hr, fin, scm]).

3) Add a second worksheet to your consolidated lineage file to track hash file dependencies and read/write operations.
3) Add a third worksheet to track look up operations and their keys.

The benefits of this approach far out weight the initial investment, when you realize how tedious this task is, just keep in mind all those hours that you’ve spent in agony debugging DataStage programs in the past. This spreadsheet is a real time saver when trying to find our where a particular column came from or what jobs perform write operations on the sequential file that is messing up your dimension.

While I work on it I keep my sanity thinking about the new episode of Scrubs next week and being able to make my deadline, I’m looking forward to a big celebration and a weekend in Vegas with my friends…

Abnormal termination of stage (cast those CLOBs!)

As I started running a bunch of our delivered DataStage ETL jobs I ran into a really undescriptive warning message in several Director job logs.  Most of tem failed on the IPC stage right after the DRS stage that was extracting data out of our source database with an “Abnormal Termination of  Stage” error. After several attempts to turn on tracing and trying to find a rational reason for the error I went into my source database and realized  that there were CLOB columns in every table that was having this issue. After some testing the issue was resolved by casting the CLOB columns into the varchar2 data type:

CAST(my_column AS VARCHAR2(4000))

Now, these were jobs that were delivered with our warehouse, the designers were probably trying to make them as general as possible, after some conversations with the warehouse team I now got authorization to replace the CLOB content with empty strings to save space as the data in those columns is of low value to our BI users.

EPM Integration Updates

As previously discussed EPM 9.0 shops need to deploy the HCM9 Integration Update in order to bring data from HRMS version 9.0. One additional thing to know is that integration updates need to be deployed into a separate project if you are running datastage on Linux because of the file sytem constraint on the number sublevel directories in ext2. If you are running more than one warehouse area (ie. HCM + SCM) it is required that the delivered ETL jobs for all of them remain grouped together in the same datastage project as hash files are not visible to jobs outside of it.

Don’t go too fast about rolling out your EPM 9.0 OWS jobs if your source system is HCM 9.0

So, last week Simran and I rolled out all the delivered jobs that populate the operational data store (a.k.a. OWS and/or staging area) in PeopleSoft EPM (Enterprise Performance Warehouse) for the Human Capital Management system. We noticed that beside some really weird “Abnormal termination of stage” errors in the DataStage ETL logs most of the issues were related to missing database objects either in the source HRMS schema or in our warehouse database. After talking to one of our hired guns (cool consultants) we were told it is somehow rare for PeopleSoft to have this kind of issues, almost as rare as for them to remove columns from records between releases. We opened a new SR in metalink very quickly got on the phone with a very nice support engineer that clarified the issue. We found out EPM 9.0 as delivered out of the box is designed to source all the OWS tables from HRMS 8.9, if you need to get your staging tables populated with data from the HRMS 9.0 you need to get  a different set of jobs that are generally referred to as “Integration Updates”, there are several sets to source data from different versions of PeopleSoft and Enterprise One, if you want the detailed information go to Metalink3 and under the knowledge tab and search for keyword  679264 (Doc. ID 650224.1).

Now before finish I want to thank Frank from Oracle support and clarify that these integration updates only replace the OWS jobs, OWE and MDX get built usin the jobs delivered out of the box with EPM 9.0.