1

I understand that collation can be set differently in different tables in a database. Collation is understood from What does character set and collation mean exactly?

There is a query that performs CAST from a char results as shown below. There are no tables involved. I guess, the encoding applied will be based on the collation in database level. Is this assumption correct?

SELECT  CAST ( SSS.id_encrypt ('E','0000000{0}') AS CHAR(100) FOR BIT DATA)  
AS ENCRYPT_ID FROM FFGLOBAL.ONE_ROW FETCH  FIRST 1 ROW ONLY

QUESTION

In the question Get Byte[] from Db2 without Encoding answer given by @AlexFilipovici [.Net BlockCopy ] provides a different result when compared to CAST result. Why is it so if there is no codepage associated?

Based on National language support - Character conversion

Bit data (columns defined as FOR BIT DATA, or BLOBs, or binary strings) is not associated with any character set.

REFERENCE

  1. Get Byte[] from Db2 without Encoding
  2. Default code page for new databases is Unicode
  3. National language support - Character conversion
Community
  • 1
  • 1
LCJ
  • 22,196
  • 67
  • 260
  • 418
  • Don't ask one question like this covering two different RDBMSs as the answers will likely be completely different. They are two separate questions. (Edit actually I see you have actually asked **four** questions!) – Martin Smith Mar 10 '13 at 21:55
  • @MartinSmith Thanks. I have updated the question to make it DB2 specific – LCJ Mar 11 '13 at 06:31
  • I think you have too many questions being asked here. Also, our DBA site may be a better place for this question, if you'd like it migrated. – Kev Mar 11 '13 at 22:02
  • @Kev To answer this question .Net related knowledge is needed (.Net BlockCopy). Hence it is not good to move to DBA site. – LCJ Mar 12 '13 at 05:12

2 Answers2

1

To find out the collation at database level in SQL Server, try this:

SELECT DATABASEPROPERTYEX('databasename', 'Collation');

More: DATABASEPROPERTYEX

Iswanto San
  • 18,263
  • 13
  • 58
  • 79
1

To answer your questions:

#1: Specifying FOR BIT DATA on a character-based data type (in DB2) means that DB2 stores / returns the raw data back with no codepage associated (i.e. it's just a string of bytes and will not go through any codepage conversion between client and server).

#2: In DB2 for Linux, UNIX and Windows, you can determine the database's collation by querying SYSIBMADM.DBCFG

 select name,value
  from sysibmadm.dbcfg
 where name in ('codepage','codeset');

#3: Per @Iswanto San:

SELECT DATABASEPROPERTYEX('databasename', 'Collation');
Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25
  • In the question http://stackoverflow.com/questions/15153100/get-byte-from-db2-without-encoding answer given by @AlexFilipovici provides a different result when compared to CAST resut. Why is it so if there is `no codepage associated` ? – LCJ Mar 07 '13 at 03:54
  • 1
    If there is a codepage associated then the DB2 server will perform codepage conversion between the database's codepage (i.e. how data is stored in the database) and the client application's codepage. The `GetBytes()` function in the solution you point out is working with data that's already been modified between client/server. – Ian Bjorhovde Mar 08 '13 at 16:41
  • Why do this question and above comment not answer your question? – Ian Bjorhovde Mar 13 '13 at 23:00