DB2 does not complain(or warn) about data loss

[rahulj]

I am trying to insert data from one table to another using the query below

INSERT INTO SORT_KEY_TEMP2 (SELECT * FROM SORT_KEYS ORDER BY SK_PAGE_ID FETCH FIRST 100 ROWS ONLY);

Table

SORT_KEY_TEMP2
	Name		Data type		Length	Nullable
	SK_PAGE_ID	CHARACTER		8	Yes
	SK_LAUNCH_DATE	TIMESTAMP		10	Yes
	SK_LIST_TYPE	CHARACTER		1	Yes
	SK_SORT_KEY	VARCHAR	FOR BIT DATA	1000	Yes
	SK_ALPHA_GROUP	VARCHAR			5	Yes

Table

SORT_KEYS
	Name		Data type		Length	Nullable
	SK_PAGE_ID	CHARACTER		8	Yes
	SK_LAUNCH_DATE	TIMESTAMP		10	Yes
	SK_LIST_TYPE	CHARACTER		1	Yes
	SK_SORT_KEY	VARCHAR			1000	Yes
	SK_ALPHA_GROUP	VARCHAR			5	Yes

The above query runs fine using control center for DB2 version 8 on AIX.
But on actually viewing the data within table SORT_KEY_TEMP2
, I find that the 'SK_SORT_KEY' column contains no data.
First and foremost it is suprising that DB2 does not throw any warning or error to report this data loss
Second is there a suggested alternate way of accomplishing this thing.
The data in SK_SORT_KEY is a sequence of bytes stored as VARCHAR.

[pronerd]

Are you completely sure the data is not there? It might be that the the SQL client is just not displaying it since it is "BIT DATA", which I assume means binary data. If your SQL client can only display ASCII data it may not be able to display what ever is in that column.

You might try testing this by running something like this to see if there is something there.

SELECT SK_SORT_KEY, LENGTH(SK_SORT_KEY)
FROM SORT_KEY_TEMP2

The other possibility is that there is no data in the source column (SK_SORT_KEY VARCHAR) that qualifies as "BIT DATA" so there is nothing to insert. Just a guess.

[rahulj]

You are right !. The data is present in the column but the DB2 client does not show that up. Gr8 thinking !!. Thanks!!!.

One more related question I had was that the data, which is a sequence of bytes, is primarily used as a collation key to enable database to sort data based on it and hence do you see any impact if the data type for storing data is changed from VARCHAR to VARCHAR FOR BIT DATA? Specifically do you see the impact like the database does not sort the data in the same order as before, just because the data storage (data type) has been changed from VARCHAR to VARCHAR FOR BIT DATA.

And Thanks again !!.

[pronerd]

do you see any impact if the data type for storing data is changed from VARCHAR to VARCHAR FOR BIT DATA? Specifically do you see the impact like the database does not sort the data in the same order as before, just because the data storage (data type) has been changed from VARCHAR to VARCHAR FOR BIT DATA.

Just guessing this question is way beyond me. I do not use DB2 that often. I would think that changing a key used for sorting from ASCII values to binary could very possibly change the sort order. So I would avoid it if possible. You would need to talk to a DB2 DBA to find out for sure.