DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
kannan_ayyanar



Group memberships:
Premium Members

Joined: 17 Apr 2010
Posts: 18
Location: Navi Mumbi
Points: 157

Post Posted: Mon Feb 25, 2019 11:35 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54501
Location: Sydney, Australia
Points: 295558

Post Posted: Wed Feb 27, 2019 4:25 pm Reply with quote    Back to top    

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 requir ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
Rate this response:  
Not yet rated
kannan_ayyanar



Group memberships:
Premium Members

Joined: 17 Apr 2010
Posts: 18
Location: Navi Mumbi
Points: 157

Post Posted: Wed Apr 10, 2019 3:44 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42923
Location: Denver, CO
Points: 221366

Post Posted: Wed Apr 10, 2019 4:10 am Reply with quote    Back to top    

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 "n ...

_________________
-craig

The Old Ones were, the Old Ones are, and the Old Ones shall be. Not in the spaces we know, but between them. They walk serene and primal, undimensioned and to us unseen.
Rate this response:  
Not yet rated
mouthou
Participant



Joined: 04 Jul 2004
Posts: 193

Points: 1874

Post Posted: Wed Apr 10, 2019 2:38 pm Reply with quote    Back to top    

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)
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 355

Points: 3699

Post Posted: Thu Apr 11, 2019 9:36 pm Reply with quote    Back to top    

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...
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54501
Location: Sydney, Australia
Points: 295558

Post Posted: Sun Apr 14, 2019 6:57 pm Reply with quote    Back to top    

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).

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours