Different result sets for the same query

harikhk · Post by **harikhk** » Sun Aug 23, 2015 12:47 pm

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,

rkashyap · Post by **rkashyap** » Sun Aug 23, 2015 1:54 pm

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.

harikhk · Post by **harikhk** » Mon Aug 24, 2015 7:45 am

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

chulett · Post by **chulett** » Mon Aug 24, 2015 9:34 am

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.

chulett · Post by **chulett** » Mon Aug 24, 2015 10:28 am

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

ArndW · Post by **ArndW** » Tue Aug 25, 2015 6:29 am

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)

Code: Select all

length(replace(upper(column),'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',''))

ArndW · Post by **ArndW** » Wed Aug 26, 2015 4:36 am

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.

harikhk · Post by **harikhk** » Thu Aug 27, 2015 12:35 pm

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

chulett · Post by **chulett** » Thu Aug 27, 2015 12:39 pm

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

harikhk · Post by **harikhk** » Wed Sep 02, 2015 2:56 pm

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