473,320 Members | 1,939 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DB2 does not complain(or warn) about data loss

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

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO SORT_KEY_TEMP2 (SELECT * FROM SORT_KEYS ORDER BY SK_PAGE_ID FETCH FIRST 100 ROWS ONLY);
  2.  
Table
Expand|Select|Wrap|Line Numbers
  1. SORT_KEY_TEMP2
  2.     Name        Data type        Length    Nullable
  3.     SK_PAGE_ID    CHARACTER        8    Yes
  4.     SK_LAUNCH_DATE    TIMESTAMP        10    Yes
  5.     SK_LIST_TYPE    CHARACTER        1    Yes
  6.     SK_SORT_KEY    VARCHAR    FOR BIT DATA    1000    Yes
  7.     SK_ALPHA_GROUP    VARCHAR            5    Yes


Table
Expand|Select|Wrap|Line Numbers
  1. SORT_KEYS
  2.     Name        Data type        Length    Nullable
  3.     SK_PAGE_ID    CHARACTER        8    Yes
  4.     SK_LAUNCH_DATE    TIMESTAMP        10    Yes
  5.     SK_LIST_TYPE    CHARACTER        1    Yes
  6.     SK_SORT_KEY    VARCHAR            1000    Yes
  7.     SK_ALPHA_GROUP    VARCHAR            5    Yes
  8.  


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.
Sep 10 '08 #1
3 1709
pronerd
392 Expert 256MB
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT SK_SORT_KEY, LENGTH(SK_SORT_KEY)
  2. FROM SORT_KEY_TEMP2
  3.  
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.
Sep 10 '08 #2
rahulj
4
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 !!.
Sep 11 '08 #3
pronerd
392 Expert 256MB
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.
Sep 11 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

89
by: Cuthbert | last post by:
After compiling the source code with gcc v.4.1.1, I got a warning message: "/tmp/ccixzSIL.o: In function 'main';ex.c: (.text+0x9a): warning: the 'gets' function is dangerous and should not be...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.