Run time column propogation

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
kannan_ayyanar
Premium Member
Premium Member
Posts: 19
Joined: Sat Apr 17, 2010 10:28 am
Location: Navi Mumbi

Run time column propogation

Post by kannan_ayyanar »

Hi,

I have a dataset with 100 columns. I have about 20 rules to check whether the column is null or blank. Each rule will be using different columns from a source dataset.

for each rule, I need to create 2 files one with empty values and other with non empty values.

I am thinking to create one job and pass required columns (the column name for which I need to check whether it is null or not) to the job, the job will check the null ability (function is same for all columns) and create 2 files needed.

any thought would be appreciated. Let me know for further details. Thanks.
Kannan
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I would use data rules. That way you only need to define the data rule definition once, and bind it onto the various columns, perhaps encapsulating them all in a rule set.

Of course, this requires an Information Analyzer licence.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
kannan_ayyanar
Premium Member
Premium Member
Posts: 19
Joined: Sat Apr 17, 2010 10:28 am
Location: Navi Mumbi

Post by kannan_ayyanar »

Thanks Ray.

I am planning to do in DataStage. is there an option to pass if else statement as a parameter to transformer.

In transformer, i have stage variable - svCheck

svCheck - if isnull(link1.columnA) then 'Y' else 'N'

when i tried to do by passing parameter, its not taking.

In Job property - i have defined a parameter Param1 with default value as below.

Param1 - if isnull(link1.columnA) then 'Y' else 'N'

In stage variable i am using the parameter.

svCheck - Param1


Is there an option to execute the Param1 command (i.e if else statement)

Let me know for any other option. Thanks again.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

kannan_ayyanar wrote:is there an option to pass if else statement as a parameter to transformer.
All I've got time for right now is the short answer and unfortunately the short answer is "no".
-craig

"You can never have too many knives" -- Logan Nine Fingers
mouthou
Participant
Posts: 208
Joined: Sun Jul 04, 2004 11:57 pm

Post by mouthou »

I also don't think there the inbuilt stages have options to accept the entire IF-THEN-CLAUSE as dynamic parameter. As an alternate approach, you can use MODIFY/FILTER STAGE which allows the dynamic columns based processing. Atleast you check the nullability of the parameter column there and set some flag so that the flag is used to create 2 files.

Other alternatives for this scenario:

- You may check the rarely known "Custom stage" option (OR)
- as the worst case, you can create a process to load the dataset to your DB and take the entire dynamic processing on the DB side depending on the DB you use (like Oracle, Teradata all support DYNAMIC SQL concept for these scenarios)
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

how complex are these rules, and can you change your extract?
seems like a coalesce or similar statement for each rule would work if the rules are binary (eg column rule 1 = if any of these are null, null else 1) and then trip off the rule columns would be easier if you have this much leeway...
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I'd use the Data Rules stage in DataStage. It can support rules whose definitions are of the If..Then..Else form (but which must end up generating a true/false (or 0/1) value).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply