DSXchange

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

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.

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.

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.

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.

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

Those cause an initial connection to be made.

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 ?