468,101 Members | 1,464 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,101 developers. It's quick & easy.

DB2 Load Varchar for bit data

How do I load "varchar for bit" data in hexadecimal format into a DB2
table ?

Table definition
===========
create table test
( i integer, v varchar for bit data (16))

db2 "IMPORT FROM "data.txt" OF DEL MODIFIED BY COLDEL, METHOD P (1, 2)
MESSAGES "/tmp/db2-load-error.log" INSERT INTO test (i,v)";

data.txt file contents
=============
,F66A7FCB526C98B61E73BD966C85C1FA
,F66A7FCB526C98B61E73BD966C85C1FA

Error message

SQL3125W The character data in row "1" and column "2" was truncated
because
the data is longer than the target database column.

May 18 '06 #1
2 9314
I don't think there's any way to do this.

Import (and load), when using DEL, expect character data for the second
column. The character column data can be specified with or without
quotes but there is no way, as far as I know, to tell the utilities that
the data is formatted as (human) readable hex data and must be converted
back to internal format.

If you are creating this output from another source, you have two
options available.

1. Don't convert the data to readable hexadecimal. You should surround
the data with generated quotes (") and will need to double up any hex
character in the data that is a quote to avoid early termination of the
field. If you don't use the quotes, then you cannot have a comma (,)
character in the data. The hex equivalents of these characters are
dependent on your underlying hardware.

2. Do not use import. Create your data records as:
insert into test values(N,x'S.........S');
where N is the numeric and S......S is the hex string. This file can be
run, after connecting to the database, with:
db2 -tf data.txt
If an application is generating the data, you can also place commit
statements in the file at appropriate intervals.
Phil Sherman

im*****@gmail.com wrote:
How do I load "varchar for bit" data in hexadecimal format into a DB2
table ?

Table definition
===========
create table test
( i integer, v varchar for bit data (16))

db2 "IMPORT FROM "data.txt" OF DEL MODIFIED BY COLDEL, METHOD P (1, 2)
MESSAGES "/tmp/db2-load-error.log" INSERT INTO test (i,v)";

data.txt file contents
=============
,F66A7FCB526C98B61E73BD966C85C1FA
,F66A7FCB526C98B61E73BD966C85C1FA

Error message

SQL3125W The character data in row "1" and column "2" was truncated
because
the data is longer than the target database column.

May 19 '06 #2
You can create an udf to read the data file. Convert 2nd column to
hexadecimal in the function. Then insert the function's result into
your table.

Please refer to below link.

http://www-128.ibm.com/developerwork...303stolze.html

db2 "INSERT INTO test_table VALUES ( readFile( '/home/stolze/test_file'
) )"

credits to Knut.

Regards,

Mehmet Baserdem

May 21 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Uthuras | last post: by
1 post views Thread by Uthuras | last post: by
3 posts views Thread by roy_ware | last post: by
4 posts views Thread by CPD | last post: by
7 posts views Thread by tojigneshshah | last post: by
3 posts views Thread by Mark S | last post: by
2 posts views Thread by Steve Rainbird | last post: by
13 posts views Thread by rdudejr | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.