DB2 Load Varchar for bit data

[imam888]

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.

[Phil Sherman]

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.

[Mehmet Baserdem]

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