SQLFetch failed

ann · Post by **ann** » Tue Dec 27, 2005 8:33 am

Hi all,
I am trying to extract as400 table by query in ODBC stage.
It was working fine. After few days we have to add 3 cols.
When I run the modified job I am getting the following error.

Extract_Job1.CTransformerStage1.DSlink1: DSD.BCIGetNext call to SQLFetch failed.
SQL statement:SELECT col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,
col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,
col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,
col33,col34,col35,col36,col37,col38,col39,col40,col41,col42,
col43,col44,col45,col46,col47,col48,col49,col50,col51,col52,
col53,col54,col55,col56,col57,col58 FROM PRDDTA.TABLE051226

col56,col57,col58 are new columns. But if I use trim statement
in the select statement it is working. The modified statement is
SELECT trim(col1),col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,
col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,
col25,col26,col27,col28,col29,col30,col31,col32,col33,col34,col35,
col36,col37,col38,col39,col40,col41,col42,col43,col44,col45,col46,
col47,col48,col49,col50,col51,col52,col53,col54,col55,col56,col57,col58 FROM PRDDTA.TABLE051226

And we had same situations with other jobs also when i am trying to
add new cols it will give me SQLFetch failed error. But when I use trim
in one of the cols it will work fine. I just want to know why this is
happening? Is there any size limit with ODBC stage? Even if the above
job extracts more than 50 cols. And the other jobs had 5 cols and when
I add one more column why they are still giving me SQLFetch failed
error. The jobs that have 5 cols should work fine without trim when
I am adding new cols to them dont they?

Ann.

kcbland · Post by **kcbland** » Tue Dec 27, 2005 10:11 am

This is definitely weird, but just for grins, did you press the Get SQL Info button on the ODBC stage to make sure that the driver was verified for constructing the query?

My assumption is that you could correctly import metadata and are using Auto-genered SQL. The only way you could get the trim() on a column is if you switched to User-defined or used the DERIVATION column. Did you import metadata and then load it into the stage, or free-hand the metadata? Sometimes when adding more columns to an existing query, there's a habit of not reloading the entire set of columns, but just "tacking" on the additional columns. Are all of the Derivations on the metadata populated or blank?

ann · Post by **ann** » Tue Dec 27, 2005 12:14 pm

Hi kcbland ,

Thank u for ur reply.
The jobs i am trying to modify are old jobs. They dont have metadata definitions. Those used just user defined sql query .
To that i added 3 more cols. Now when i imported the same table metadata definetion . and Used that definition in my job.
now it is working without specifing trim function in sql statement. And it is leting me view the data also.

But for this way i have to select all those 58 cols which that old job used before. Which is a pain. But now on i will ask my other devlopers to use metadata definetion insteed of writing query.
But i have the same old Q why it is not letting me select if i use user defined sql query? And also it wouldnt let me view the data from odbc stage.

Ann

kcbland · Post by **kcbland** » Tue Dec 27, 2005 12:49 pm

Your metadata definitions represent the datatypes that the ODBC driver will bind to the returning rows. Inconsistencies here are best to be avoided. Importing metadata and using it insures that the database, ODBC driver, and DataStage all agree as to the content. User-defined queries are best to be avoided on simple data selections, as you lose the dynamic ability to respond to additional columns being added. Your change should have been as simple as using the imported columns to update the query.

ann · Post by **ann** » Tue Jan 03, 2006 12:54 pm

Hi all,
I am posting this here because I got same problem in different way I guess.In the begging of this post
I mentioned that I had to add 3 more cols to my job. When kcbland replied that we need Meta data
definition to do it instead of manually adding them. I did that but back then I had 58 cols of as400 table
and we were use to get only 55 and now with metadata def I added all the cols. And it is working. But now
the new job extracts 4 cols from as400 table, which has 116 cols. And we are trying to add 1 more col from it.
For this job I created table definition and imported only 5 cols, which I
needed. And when I tried to view it just freezes. And when I try to run, job freezes too.
But when import all the cols to ODBC stage and tried to view it works fine. And selected just 5 cols in transformer
stage to transfer to text file. And ran it, works fine.

And i am confused here that if we have to import all the cols in odbc stage from meta data def then why we have the option to select cols while
impoting?
little help here please

Anna

kcbland · Post by **kcbland** » Tue Jan 03, 2006 2:02 pm

Don't know why the job is hanging on this view data query. Have you asked your DBAs to see if the query is running in the database?

As for the SQL generated, you can put whatever columns you want into the ODBC/OCI stages, the auto-generated SQL adjusts for the columns present. It's better to select only the columns needed as the traffic on the network and out of the database is reduced.

Sounds like you've got a bug if the DBA group doesn't see your query running but the job is hanging.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 04, 2006 3:59 pm

The default maximum column retrievable by the ODBC stage is 400, so that's not the problem. This can be increased by MAXFETCHCOLS in the uvodbc.config file.

Are you using generated SQL or user-defined SQL in the ODBC stage?
Are there 58 columns defined on the Columns grid?
Do all the data types match what's out there in the database table?