Post reply

Does it make any difference if I simply add a "collate" in query, though it's not necessary?

  • December 30, 2010 at 7:15 am

    #142903

    Hi Experts,

    I am facing a problem with using Collate in query.

    I wanted to take data from one table linking to another table in another database, but in same location and

    same domain (Main Server DB).Had to pull data from different servers like this. We have added those servers

    as linked severs to the Main Server.I created a script for loping through the linked servers and got the

    data using dynamic SQL. It was working fine without any issue.

    Later there was a requirement to pull data from someother server which was in some other domain and

    Differenet location (same way). When I added that linked server in the script it was giving collation error

    in the Outer Join statement. Hence I added the "Collate" key word in the JOIN to Main DB table, in the dynamic SQL

    script. (Means "Collate" is applied even when data is picked from the server which is in the same location

    and same domain). It returned data without any prbolem.

    But today I noticed an issue. There's a difference in the data comming from those servers which are in the

    same location and domain. When I queried those servers WITH and WITHOUT "collate" keyowrod it returns

    different set of data.

    Why so.? Does it make any difference if I simply add a "collate" in query (join in this case).?

    Though the collate is actualy not necessary.

    Please clarify me.

    Thanks in advance.

  • December 30, 2010 at 11:37 pm

    #1268015

    .. Still awaiting for responses..!!!!

  • January 2, 2011 at 1:38 pm

    #1268325

    It would if collation is a matter of case sensitivity (_CS_ vs _CI_).

  • January 3, 2011 at 11:22 am

    #1268581

    You can get incorrect results, however, if the weighting of null characters is different between the federated database and the data source.

    Likewise, for comparison statements, be careful if you are submitting statements to a case-insensitive data source. The weights assigned to the characters "I" and "i" in a case-insensitive data source are the same. For example, in a case-insensitive data source with an English code page, STEWART, SteWArT, and stewart would all be considered equal. The federated database, by default, is case-sensitive and would assign different weights to the characters.

    More... http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.websphere.ii.federation.tuning.doc/tuning/iiyftpuscs.html

  • January 10, 2011 at 11:02 pm

    #1271716

    Thanks for your replies..

    So what's the workaround..? Will it work perfectly if I convert both the values to uppercase...?

    Thanks a lot once again.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply