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



Joined: 24 Apr 2006
Posts: 24
Location: USA
Points: 248

Post Posted: Tue May 01, 2018 7:55 am Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Parallel
OS: Unix
Hi,

I have a job where I am using ODBC connector stage as source to read from sql server.

In the logs, I see that it connects to the source two times. Why is that so? Isnt ODBC connector stage sequential by default?

I have not configured parallel read option. I want to run it in sequential mode. I changed this in properties, but still its connecting two times to source.
Please see the log below:-


Type: Info Event: Parallel job initiated
.. .. .. ..
.. .. .. .. .. ..

Type: Info Event: main_program: orchgeneral: loaded (...)
Type: Info Event: ODBC_SQL_BYR: Connected to Microsoft SQL Server, version 12.00.2271 through driver VMsqls00.so.
Type: Info Event: ODBC_SQL_BYR: The driver does not support quoted identifiers in SQL statements
Type: Info Event: target: Using case-insensitive indentifiers
Type: Info Event: target: Generated table action SQL: TRUNCATE TABLE T.BUYERGRP
Type: Info Event: main_program: APT configuration file: /opt/IBM/InformationServer/Server/Configurations/default.apt (...)
Type: Info Event: main_program: This step has 2 datasets: (...)
Type: Info Event: ODBC_SQL_BYR,0: Connected to Microsoft SQL Server, version 12.00.2271 through driver VMsqls00.so.
Type: Info Event: ODBC_SQL_BYR,0: The driver does not support quoted identifiers in SQL statements
Type: Info Event: target: Number of rows inserted: 4
Type: Info Event: main_program: Step execution finished with status = OK.
Type: Info Event: main_program: Startup time, 0:20; production run time, 0:33.
Type: Info Event: Parallel job reports successful completion
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42762
Location: Denver, CO
Points: 220350

Post Posted: Tue May 01, 2018 8:01 am Reply with quote    Back to top    

Source? Both of those messages are target related. And I'm wondering if the two connections are specific to having that "generated table action" - the truncate, meaning without it you'd only have one. Just a guess, have no way to confirm this myself.

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
saxena_richa
Participant



Joined: 24 Apr 2006
Posts: 24
Location: USA
Points: 248

Post Posted: Tue May 01, 2018 11:35 am Reply with quote    Back to top    

Thanks Craig.

My job design is as follows:

ODBC (sqlserver) -----> xfr ------> Netezza connector
(select statement) -----> simple mapping-----> Truncate &load to netezza


The netezza connector is set for truncate and load. So the message should be for netezza connector.

I want the source ODBC to run sequential. I tried to set property to 'Sequential', as well as 'Default'.
For both runs, I see connection messages twice.
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: 42762
Location: Denver, CO
Points: 220350

Post Posted: Tue May 01, 2018 12:05 pm Reply with quote    Back to top    

Okay... just because you see two connections being made doesn't mean it isn't running in a serial / sequential fashion. Unless someone here knows for certain, in your shoes I'd open a support case to query how exactly the connector handles what you are doing. That may be behavior you have no control over or have any ability to change.

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2818
Location: USA
Points: 21341

Post Posted: Tue May 01, 2018 1:19 pm Reply with quote    Back to top    

It looks like "ODBC_SQL_BYR" is your source stage name.

This is a semi-educated guess...

The first job log entry "ODBC_SQL_BYR: Connected to Microsoft SQL Server ..." is connecting in order to gather and/or validate metadata and check to see if it matches what you have defined on the Columns tab of your stage properties.

The second job log entry "ODBC_SQL_BYR,0: Connected to Microsoft SQL Server..." is connecting in order to do the actual work by executing the query you have defined or the one that is generated by the stage.

The ",0" part in the job log indicates node 0 is executing your SQL, and counting stats with node zero. If you had yet another job log entry for the same stage with ",1" then it would indicate you're running on 2 nodes in parallel. What you've posted confirms it is running sequentially.

Another thing to note is that while the parallel job log entries are sorted by timestamp, they are not always in a logical, sequential, expected order. It could indeed be truncating the target table and logging that activity earlier than you might expect.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
PaulVL



Group memberships:
Premium Members

Joined: 17 Dec 2010
Posts: 1255

Points: 8249

Post Posted: Tue May 01, 2018 2:57 pm Reply with quote    Back to top    

Does the stage have a Fail on type mismatch or Fail on size mismatch set?

Those cause an initial connection to be made.
Rate this response:  
Not yet rated
saxena_richa
Participant



Joined: 24 Apr 2006
Posts: 24
Location: USA
Points: 248

Post Posted: Fri May 04, 2018 11:08 am Reply with quote    Back to top    

Yes. My current job setting is:
Fail on size mismatch=yes
Fail on type mismatch=yes

Is this is a separate connection, additional to when the data is read ?
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