DSXchange

Hi All,
When I am executing a query from db2 connector with read query from sql and direct query, the result sets are different.
The query has an xmltable and on the result set applying a where clause.
When the query is executed by reading from file the where clause applied on xml table result is not filtering the dat.
But when the same query when executed directly from db2connector stage, the result set obtained is one with the where clause applied.
Would like to know if this some kind of known bug or I am missing something here
Just to elaborate,
For the same query,
Result set when executed from "Read select statement from file " returns 12 records(example)
With the query written directly in "Select Statement" returns 10 records(example)

Why this different behaviour for same query.

Datastage Version : 8.7

Thanks,

You can start by reviewing debugging information for the DataStage DB2 Connector, generated by setting CC_MSG_LEVEL to 2 (especially for the version of the job in which select statement is read from file).

If it is of a reasonable length, post the query or the "where" clause.

There is no warning returned.
and with CAMPA% no rows returned

Using all UPPERCASE it doesn't surprise me no rows are returned unless you also do the same to the field being tested, that's an old trick for a 'case-insensitive' match.

That makes zero sense to me. In your shoes, I would involve support.

What is the data type of the "column" and what are the database NLS settings? Also add the following to the select for the rows in question and the length should be 1 (the non-latin character)

harikhk wrote:...And with the query of length, the length returned is not equal to 1 but equal to the length of the content of the column

If you did the query as posted, this shows a problem. The statement will remove uppercase A-Z and spaces from the column and return the length which should be 0 unless other characters are present. If the length remains identical to the original string then this means that the characters are not the same.

Code page 1208 will be UTF-8, where the 1-byte LATIN characters are represented by the same values and thus should be backward compatible. What tool are you using to execute your SELECT and what are the NLS setting for that query session? I suspect that they might be different.

I am using Toad for DB2 to execute the query. Not sure how to get the NLS for that session

We have Toad for Oracle and they're displayed under the "Database / Administer / NLS Parameters" menu option.

Sorry for delayed reply.
I could not find these settings in toad for db2.
Is there any other way I can fix this.

For now I am extracting all the data and applying the filter in datastage transformer stage and is working as expected with correct results