ODBC connector taking v.long to connect to source

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
saxena_richa
Participant
Posts: 24
Joined: Mon Apr 24, 2006 3:34 am
Location: USA

ODBC connector taking v.long to connect to source

Post by saxena_richa »

Hello,

I have around 100 jobs, all simple extract jobs (no filter) to pull data from SQL Server (cloud instance) using ODBC connector. It is a requirement to have a encrypted connection & we are doing this using recommended ODBC driver and settings in DSN.

The problem is that jobs are taking pretty long to establish the connection (on a avg. more than a minute). The whole batch which used to finish in under an hour, is now taking almost 4-5 hours.

I have following ques:
1) Any options to improve job connection setup time ?

2) Is there a way to establish connection once and then pull data for multiple tables? I checked ODBC connector ; it doesnt allow multiple output links, so I cannot read more than one table in one connection.
Is it possible to achieve it any other way?


Thanks for your time.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Just curious, what was different back when it used to take under an hour? What all has changed since then?
Choose a job you love, and you will never have to work a day in your life. - Confucius
saxena_richa
Participant
Posts: 24
Joined: Mon Apr 24, 2006 3:34 am
Location: USA

Post by saxena_richa »

Previous runtimes are without encryption, i.e with encryption turned off.
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

Have you enabled partitioned reads in order to speed up your extract?

JDBC also offers encryption in flight. You might want to see how to set that up and calculate the speed of it.
saxena_richa
Participant
Posts: 24
Joined: Mon Apr 24, 2006 3:34 am
Location: USA

Post by saxena_richa »

I haven't enabled partitioned reads, because that comes into play after the connection has been setup to source.

Currently this source connection is what is taking up most time. I want to improve this. Currently it takes about a min to establish the connection to source (with encryption on), which was in seconds earlier (no encryption).
With 100 jobs, this is becoming a pain point.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So the connection time is the only issue here or is data moving though the job in an encrypted state also slowing things down? I'm assuming both are at play here after trying to reconcile how 100 jobs each taking a minute longer to connect is increasing total runtime of them all by over 240 minutes rather than something closer to 100.
-craig

"You can never have too many knives" -- Logan Nine Fingers
saxena_richa
Participant
Posts: 24
Joined: Mon Apr 24, 2006 3:34 am
Location: USA

Post by saxena_richa »

Craig,

I agree. The data movement in the job is slow too.
However I want to point out that there are some jobs where connection time is around 2 - 3 minutes. Hence the total connection time is slightly more than 100 mins.

Any ideas/ suggestions to improve these timings?
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Would it be possible for you to post your odbc.ini Settings for this Connection?
Post Reply