ODBC connector source connection

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 source connection

Post by saxena_richa »

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
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"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 »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

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
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

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

Those cause an initial connection to be made.
saxena_richa
Participant
Posts: 24
Joined: Mon Apr 24, 2006 3:34 am
Location: USA

Post by saxena_richa »

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 ?
Post Reply