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



Group memberships:
Premium Members

Joined: 18 Oct 2018
Posts: 8
Location: Sydney
Points: 93

Post Posted: Tue Jul 02, 2019 3:03 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
Hi,
I have a requirement to read data from 7 different tables using a ODBC stage and apply some logic. Table structure is different for each table so want to add new columns with NULL value. I don't want to use Transformer stage as I need to use 7 transformers.

It would be great help, If anyone have any idea to add extra columns with NULL value without using a Transformer.

Thanks
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42985
Location: Denver, CO
Points: 221730

Post Posted: Tue Jul 02, 2019 8:19 am Reply with quote    Back to top    

Without getting into why you would need seven transformers for this, they're not how you "add a new column" without a derivation, without a value. If you really want to use a stage for this then the Modify stage would be the answer, I would think. Better yet, let the database handle the NULL as it should be the default values for the columns in question (yes?) so don't do anything - don't even mention them . Your job will insert the proper derived values into the columns that require them and any not mentioned will default. Easy peasy.

Now, I may be cornfusing Informatica and DataStage but we always keep all columns in our metadata and simply don't "hook them up" so they're not included in the generated DML and thus default. From what I recall of the DS way, simply omitting them from the metadata you use in jobs like this would accomplish the same thing.

My two cents.

_________________
-craig

Help I'm steppin' into the twilight zone, place is a madhouse, feels like being cold
My beacon's been moved under moon and star, where am I to go now that I've gone too far?
Rate this response:  
Not yet rated
dwh_user



Group memberships:
Premium Members

Joined: 18 Oct 2018
Posts: 8
Location: Sydney
Points: 93

Post Posted: Tue Jul 02, 2019 8:59 pm Reply with quote    Back to top    

The reason behind to make same metadata for all 7 tables is to Funnel the data and bring it into one link.

I can't do this in source query by selecting "NULL as ColumnName" because this is not allowed as per our code standards.

Also I tried to handle this with database, but our environment is using ODBC stage to load the data into database, And when we give less number of columns in source than the target table, its throwing error with message "All columns of the table must be specified in the INSERT BULK statement".

If I change to not do Bulk Insert via change the Array Size to 1 then job is taking 7-8 mins to load only 20-30K records.
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: 42985
Location: Denver, CO
Points: 221730

Post Posted: Wed Jul 03, 2019 9:43 am Reply with quote    Back to top    

Okay... always best to include relevant details right off the bat. A few quick thoughts:

1. That's a bit of an odd "code standard" IMHO but okay
2. SQL Server, I assume, since Windows and ODBC
3. What's your target volume? How often will this run?
4. Why are you setting the Array Size to 1? Conventional (non-bulk) inserts certainly don't require that unless perhaps you have LOBs to handle?

Again, relevant details help.

_________________
-craig

Help I'm steppin' into the twilight zone, place is a madhouse, feels like being cold
My beacon's been moved under moon and star, where am I to go now that I've gone too far?
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: 42985
Location: Denver, CO
Points: 221730

Post Posted: Wed Jul 03, 2019 11:04 am Reply with quote    Back to top    

And since you didn't mention it, Modify stage? Pretty sure that should be an option here.

_________________
-craig

Help I'm steppin' into the twilight zone, place is a madhouse, feels like being cold
My beacon's been moved under moon and star, where am I to go now that I've gone too far?
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 373

Points: 3853

Post Posted: Wed Jul 10, 2019 11:45 am Reply with quote    Back to top    

what do you already have? Several stages will let you inject a column at will in 11.x. You can also add them to your extract if its from a database, just add a column as type & name to the select (does not have to be null, are you allowed to inject a non-null column?). Transformers used to be a performance hit but not in 11... that is not a bad way, not sure why you want to avoid it.

you could also make a sequence job that calls the same parallel job 7 times with different parameters and builds datasets. The parallel job can write it into funnel format, and one final job reads/funnels/loads. If your swap disk is fast, there is no performance hit here, so this is useful if your setup includes a powerful swap area.
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: 54524
Location: Sydney, Australia
Points: 295662

Post Posted: Sat Jul 13, 2019 8:22 pm Reply with quote    Back to top    

I'm thinking Column Generator stage.

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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