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.