Extract produces nothing but columns repeated as output

Cheintze · Post by **Cheintze** » Tue Oct 02, 2012 3:08 pm

We have a server job that extracts data from our system (MAPPER). It connects fine however instead of reading the data in each column, the ODBC connection is getting nothing but the column names repeated as data.

So basically, all 172k records read the same - a duplicate of the column name. We have had this issue before, but were always able to fix it by simply changing the columns in the ODBC connection from upper to lower case, which seemed to work. That does not work for some reason now.

Has anyone ever experienced this?

Thanks,
Clay Heintze

ArndW · Post by **ArndW** » Wed Oct 03, 2012 3:05 am

If you have MS-Office installed you've probably also got "msquery" installed, which will let you test the ODBC connection to see if the problem lies in the ODBC connectivity or in DataStage.

What sort of storage lies behind the ODBC connection? If it is an Excel document then I've seen strange behaviour like this before. Since ODBC to Excel chooses each column's data type according to the contents of the first n-rows one can get unexpected results.

ray.wurlod · Post by **ray.wurlod** » Wed Oct 03, 2012 3:02 pm

Does this also occur if you use the DataStage diagnostic tool DS_CONNECT ?

Cheintze · Post by **Cheintze** » Thu Oct 04, 2012 5:38 am

I am not sure how to use DS_CONNECT or where to find it. I am relatively new to using DataStage and we have not had training on it.

ArndW · Post by **ArndW** » Thu Oct 04, 2012 5:54 am

In that case MSQUERY will help you quicker.

chulett · Post by **chulett** » Thu Oct 04, 2012 7:05 am

Nobody gets any training on DS_CONNECT.

From what I recall it is under the "branded_ODBC" directory and comes with a readme that walks you through its use. Poke around, locate it and give it a shot.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 04, 2012 3:22 pm

DS_CONNECT is executed within the dssh environment, possibly from the Administrator client Command window, possibly by logging in on the engine machine.

Code: Select all

DS_CONNECT <<DSN>>

You are prompted for a user name and password for the data source, and then connection succeeds or fails.
If connection succeeds you can execute any SQL query against the data source.
To exit from DS_CONNECT use the "dot command" .Q

There is also a test utility in branded_odbc, but this is only for the drivers that are shipped with DataStage.

chulett · Post by **chulett** » Thu Oct 04, 2012 3:41 pm

Ah... guess I was thinking of the wrong thing.

DSXchange

Extract produces nothing but columns repeated as output

Extract produces nothing but columns repeated as output

DS_CONNECT?