473,325 Members | 2,816 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,325 software developers and data experts.

Problem with VARCHAR FOR BIT DATA

I need to update a fairly wide non-indexed column for a very large
number of rows in several tables. One solution we were tossing around
was to avoid all the problems with performance and log space by
exporting the data to flat files, using an external program to make the
changes, then reload the data into the target tables. Preliminary tests
showed good results.

We ran into a problem where one table had a column defined as VARCHAR
FOR BIT DATA. Several of the rows had binary values that when exported
to flat files showed up as strange control characters and newline
characters which confused most text processing utilities. Is there a
way to get around this situation by materializing the data in a
non-binary format and reloading it as such?

Thanks,

Mike L. Bell

Oct 23 '06 #1
9 10663
You could use the HEX() function on the column.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 23 '06 #2
I just tested that function and it does produce output that can be
processed as I need. Once I've done the conversion, I'm not sure how I
tell DB2 to take the character-ized version of the data and convert
back into binary format via the LOAD utility. My first pass just loaded
the converted data into the varchar field, truncating the last half.

What used to be x'2000' gets converted to x'32303030' (where 32 is
ASCII "2" and 30 is ASCII "0"). I didn't see anything immediately
obvious under the MODIFIED BY modifiers in the docs. Is there a mod
that will do this?

Mike
Serge Rielau wrote:
You could use the HEX() function on the column.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 23 '06 #3
mikelbell2000 wrote:
I just tested that function and it does produce output that can be
processed as I need. Once I've done the conversion, I'm not sure how I
tell DB2 to take the character-ized version of the data and convert
back into binary format via the LOAD utility. My first pass just loaded
the converted data into the varchar field, truncating the last half.

What used to be x'2000' gets converted to x'32303030' (where 32 is
ASCII "2" and 30 is ASCII "0"). I didn't see anything immediately
obvious under the MODIFIED BY modifiers in the docs. Is there a mod
that will do this?
Given the table created as:

D:\SQLLIB\BIN>db2 create table junk (c1 varchar(16) for bit data)
DB20000I The SQL command completed successfully.

you can insert hex value of 2000 into it:

D:\SQLLIB\BIN>db2 insert into junk values x'2000'
DB20000I The SQL command completed successfully.

which you can retrieve in two different ways:

D:\SQLLIB\BIN>db2 select * from junk

C1
-----------------------------------
x'2000'

1 record(s) selected.
D:\SQLLIB\BIN>db2 select hex(c1) from junk

1
--------------------------------
2000

1 record(s) selected.
If this is not what you want - then I don't understand your problem; please
elaborate.

Jan M. Nelkem
Oct 23 '06 #4
This is what currently happens

insert into junk values x'2000'
select * from junk
c1
----------
x'2000'

export to dumpfile.txt of del select hex(c1) from junk.
dumpfile.txt contains:
"2000"
load from dumpfile.txt of del replace into junk

select * from junk
c1
--------
x'32303030'

I need junk to c1 to be: x'2000'. Some kind of conversion during the
load process needs to happen to un-HEX the character string back to
binary.

For a more realistic scenario, need to unload data similar to:

ITEM_ID CONT_ID

-----------------------------
----------------------------------------------------------------
x'20060918142255954039000000' EWKN3TQ7M2J32Q1C

then edit the file substituting
"94+3+ICM8+icmnlsdb14+Srt_Transfer59+26+PCBB%24A4D CROTYHKX++++++++++18+PCBB%24A4DCROTYHKX++1+14+1043 "
for "EWKN3TQ7M2J32Q1C" , then reload the table from the new file.
Exporting the ITEM_ID field to a delimited file produces:

" ^F ^X^T""U^Õ@9","EWKN3TQ7M2J32Q1C" (with three trailing
null characters at the end of the first field)

Depending on the control characters in the field, utilities like grep,
sed, awk, etc. will do wild things. This is the reason it would be nice
to HEX the value as suggested (thus removing the control characters),
perform text substitution, then reload the table on the newly processed
file converting the HEXed value back to binary along the way to
preserve its original context.

Hope this clarifies.

Mike
Jan M. Nelken wrote:
>
Given the table created as:

D:\SQLLIB\BIN>db2 create table junk (c1 varchar(16) for bit data)
DB20000I The SQL command completed successfully.

you can insert hex value of 2000 into it:

D:\SQLLIB\BIN>db2 insert into junk values x'2000'
DB20000I The SQL command completed successfully.

which you can retrieve in two different ways:

D:\SQLLIB\BIN>db2 select * from junk

C1
-----------------------------------
x'2000'

1 record(s) selected.
D:\SQLLIB\BIN>db2 select hex(c1) from junk

1
--------------------------------
2000

1 record(s) selected.
If this is not what you want - then I don't understand your problem; please
elaborate.

Jan M. Nelkem
Oct 23 '06 #5
mikelbell2000 wrote:
This is what currently happens

insert into junk values x'2000'
select * from junk
c1
----------
x'2000'

export to dumpfile.txt of del select hex(c1) from junk.
dumpfile.txt contains:
"2000"
load from dumpfile.txt of del replace into junk

select * from junk
c1
--------
x'32303030'

I need junk to c1 to be: x'2000'. Some kind of conversion during the
load process needs to happen to un-HEX the character string back to
binary.
Ok - I think I got it.

It appears that load/import does not accept hexadecimal notation in input file
(x'2000' would be inserted as hex 58273230303027 ).

What is working is this:

D:\Working>db2 -x -o- -z junk.txt "select 'INSERT INTO JUNK VALUES X''' concat h
ex(c1) concat ''' ;' from junk"

Pay attention to placement and numbver of single quotes.

This creates a file junk.text like this:

D:\Working>type junk.txt
INSERT INTO JUNK VALUES X'4672616E6B' ;
INSERT INTO JUNK VALUES X'1248163264' ;

You can then use junk.txt as input batch file to db2:

db2 -tvf junk.txt

to insert those values back.

Seems almost straightforward ... :-)

Jan M. Nelken
Oct 24 '06 #6
Thanks, Jan. Your approach would definitely solve the immediate issue,
but at the expense of incurring logging and being much slower than the
high speed LOAD command which we were trying to take advantage of.
We've got millions of rows to update and a short window to do it in.
The hopes were to dump the data while still online, run the
substitution scripts then reload the tables quickly.

Our research up to now indicates that there is no translation function
available to the LOAD command to aid in our plight. Your solution is
elegant, but not viable for our constraints. If there are other
"outside-the-box" ideas, we're open to them as well.

Thanks again,
Mike
Jan M. Nelken wrote:
>
Ok - I think I got it.

It appears that load/import does not accept hexadecimal notation in input file
(x'2000' would be inserted as hex 58273230303027 ).

What is working is this:

D:\Working>db2 -x -o- -z junk.txt "select 'INSERT INTO JUNK VALUES X''' concat h
ex(c1) concat ''' ;' from junk"

Pay attention to placement and numbver of single quotes.

This creates a file junk.text like this:

D:\Working>type junk.txt
INSERT INTO JUNK VALUES X'4672616E6B' ;
INSERT INTO JUNK VALUES X'1248163264' ;

You can then use junk.txt as input batch file to db2:

db2 -tvf junk.txt

to insert those values back.

Seems almost straightforward ... :-)

Jan M. Nelken
Oct 24 '06 #7
mikelbell2000 wrote:
Thanks, Jan. Your approach would definitely solve the immediate issue,
but at the expense of incurring logging and being much slower than the
high speed LOAD command which we were trying to take advantage of.
We've got millions of rows to update and a short window to do it in.
The hopes were to dump the data while still online, run the
substitution scripts then reload the tables quickly.

Our research up to now indicates that there is no translation function
available to the LOAD command to aid in our plight. Your solution is
elegant, but not viable for our constraints. If there are other
"outside-the-box" ideas, we're open to them as well.
You may consider following "improvements" to your concerns:

a) Use AUTOCOMMIT OFF and NOT LOGGED INITALLY to address logging issue;
b) Use blocking insert to improve speed:

instead of:

INSERT INTO JUNK VALUES X'4672616E6B' ;
....
INSERT INTO JUNK VALUES X'1248163264' ;

construct:

INSERT INTO JUNK VALUES
X'4672616E6B',
....
X'1248163264'
;

thus use single INSERT for inserting multiple rows - up to SQL statement limit.
Combining both a) and b) should yield acceptable results.

If you insist on using LOAD - considering LOAD FROM CURSOR and feed cursor with
data processed by an application program massaging input data...

Jan M. Nelken
Oct 24 '06 #8
Jan M. Nelken wrote:
If you insist on using LOAD - considering LOAD FROM CURSOR and feed cursor
with data processed by an application program massaging input data...
Or you can use pipes, which is probably a more obvious way to involve an
application that modifies the data to be loaded.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Oct 25 '06 #9
How about writing a simple C or Java program to re-translate the
human-readable form back to the binary format on a byte-by-byte basis
for the specific field in the loadable file? It would read 2 characters
at time then write the single byte to the output file. This would have
the effect of shrinking the field in half sizewise and representing in
the same binayr format DB2 would expect on input. It would have to pass
through the rest of the fields as is without translation. Seems like it
should be a about a page of code or so. You could make it go the
proverbial extra mile by making it a filter like the aforementioned
grep, sed and awk utilities so it can be processed all in one shot.

Although not a solution using the built-in functionality of the DB2
arsenal, this solution would be keeping to the spriit of the original
scenario you suggested.

Evan

Jan M. Nelken wrote:
mikelbell2000 wrote:
>
construct:

INSERT INTO JUNK VALUES
X'4672616E6B',
...
X'1248163264'
;

thus use single INSERT for inserting multiple rows - up to SQL statement limit.
Combining both a) and b) should yield acceptable results.

If you insist on using LOAD - considering LOAD FROM CURSOR and feed cursor with
data processed by an application program massaging input data...

Jan M. Nelken
Oct 25 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Dariusz | last post by:
Below is part of a code I have for a database. While the database table is created correctly (if it doesn't exist), and data is input correctly into the database when executed, I have a problem...
2
by: UrgeOverkill | last post by:
I'm having a problem sending data from a socket server. The server side reports that it has sent 4845 bytes but the client reports only 1448 bytes received. The kicker is that this ONLY happens...
2
by: Intrepid Soldier | last post by:
Hello all, My database has a single table with a single column of the type VARCHAR(20). I insert one record into this table through the command line and then I try to retrieve this record through...
2
by: RipTide | last post by:
Background: Using an unsupported/abandoned multi-user multi-database program that uses Access 97 and Jet 3.5. Program itself appears to have been built with PowerBuilder 6.5. Databases reside on...
0
by: manoj339 | last post by:
I have varchar variable declared .When I print this var it shows me correct value before a C function. Inside a c function this variable value is getting truncated . The c function does not pass...
2
by: eighthman11 | last post by:
Hello everyone, I'm using Access 2000 and SQL 8.0 This maybe easy but I can't figure it out. I have a linked access table to a SQL server table. I use this table on a Grid on an Access form. ...
2
by: nedu | last post by:
Hi all, Assume the col value has following. col1 ---------------- 1asd asd2 as3asf
3
by: arial | last post by:
Hi all, I am not sure this question will belong to a sql group or .net. but the problem I have is, my data entry forms crashes when some enter a apostrophe character in one of the field. that...
0
inch
by: inch | last post by:
I am trying to hack some C++ code from a colleague. It uses ADODB to query the data in an Excel spreadsheet. Once the data has been queried it stores it in memory for quick access later. For each...
4
by: poolboi | last post by:
hi guys i've having some problem extracting data from a text file example if i got a text file with infos like: Date 2008-05-01 Time 22-10 Date 2008-05-01 Time 21-00 Date 2008-05-02 Time...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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)...
1
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.