Different result sets for the same query
Moderators: chulett, rschirm, roy
Different result sets for the same query
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,
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,
Thanks,
HK
*Go GREEN..Save Earth*
HK
*Go GREEN..Save Earth*
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.
If it is of a reasonable length, post the query or the "where" clause.
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 ',''))
Last edited by ArndW on Tue Aug 25, 2015 6:30 am, edited 1 time in total.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>