Storing Objects in DB2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rhino - 04 Jul 2006 15:56 GMT

Is it possible to store Java objects in DB2 V8.2 for Windows/Unix/Linux via
JDBC?

Specifically, if I have a 4-dimensional boolean array, i.e. boolean[][][][],
can I store it directly in a column of a DB2 table? If so, how do I do it?

It would be VERY convenient if I could store this boolean array directly in
a column of a DB2 table but I'm not at all clear on whether this is
possible, even after reading the documentation in the Information Center. I
was intrigued by the setObject() and getObject() methods in JDBC but I'm not
at all sure if I can use them for the purpose I described. I'm especially
unlclear about what datatype the column containing the Object could/should
be.

If I can't store a multidimensional boolean array directly in a table
column, I can always convert it into a representation of the array that uses
more traditional datatypes. For instance, I could convert:

boolean[] myArray = {true, false, true};

into this String:

String myString = "TFT"; //T=true; F=false

then store the String representation of the array in one of the CHAR column
types. But it would be much more convenient if I could simply store the
boolean[][][][] array directly in a column of the table. Does anyone know if
that is possible? If it is, a brief code snippet showing how to insert the
value would be VERY helpful!

Signature

Rhino

Reply to this Message

Hardy - 04 Jul 2006 16:28 GMT

It seems to be a programming strategy other than a database one. you
cannot do what you want without further coding. maybe some UDT will be
helpful and you can balance the effort on the java and UDF.

Rhino

> Is it possible to store Java objects in DB2 V8.2 for Windows/Unix/Linux via
> JDBC?
[quoted text clipped - 25 lines]
> that is possible? If it is, a brief code snippet showing how to insert the
> value would be VERY helpful!

Reply to this Message

Rhino - 04 Jul 2006 16:49 GMT

Are you saying that it is definitely NOT possible to store a boolean array
directly in a DB2 table?

If it isn't possible to store the array directly, I can transform it into
something that can be stored and I'm not too worried about writing the code.
I'm mostly just trying to be 100% sure that there is no way to store the
array directly.

--
Rhino

It seems to be a programming strategy other than a database one. you
cannot do what you want without further coding. maybe some UDT will be
helpful and you can balance the effort on the java and UDF.

Rhino ??:

> Is it possible to store Java objects in DB2 V8.2 for Windows/Unix/Linux
> via
[quoted text clipped - 33 lines]
> that is possible? If it is, a brief code snippet showing how to insert the
> value would be VERY helpful!

Reply to this Message

Serge Rielau - 04 Jul 2006 17:14 GMT

> Are you saying that it is definitely NOT possible to store a boolean array
> directly in a DB2 table?
[quoted text clipped - 3 lines]
> I'm mostly just trying to be 100% sure that there is no way to store the
> array directly.

DB2 does not support an ARRAY data type.
If all you want is store (you don't want to operate on it in DB2) then
you should linearize the array into a VARCHAR FOR BIT DATA or a BLOB (if
it's big). In C I would simply cast the beast to a char pointer and be
done with it. Not sure if there is anything similar brute but efficient
in Java :-)

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Rhino - 04 Jul 2006 20:07 GMT

>> Are you saying that it is definitely NOT possible to store a boolean
>> array directly in a DB2 table?
[quoted text clipped - 4 lines]
>> store the array directly.
> DB2 does not support an ARRAY data type.

I knew that :-)

I was wondering if there was some way to store an Object though. If I could
stuff the boolean array into an Object, that would probably meet the users
needs. Being able to store the array directly would be even better, of
course, but that sounds like it's impossible.

By the way, do you have any idea when/if DB2 will support the ARRAY
datatype? Is it in Version 9 by any chance?

> If all you want is store (you don't want to operate on it in DB2) then you
> should linearize the array into a VARCHAR FOR BIT DATA or a BLOB (if it's
> big). In C I would simply cast the beast to a char pointer and be done
> with it. Not sure if there is anything similar brute but efficient in Java
> :-)

I can manage to linearize it easily enough with a little Java code. I just
wanted to see if there was any way to store it without having to linearize
and de-linearize it.

--
Rhino

Reply to this Message

Serge Rielau - 04 Jul 2006 23:23 GMT

> By the way, do you have any idea when/if DB2 will support the ARRAY
> datatype? Is it in Version 9 by any chance?

Not in DB2 9. And if you want to know what's in Python you'll need an
NDA and sign your name in blood.
Can you define what you mean by an object? If you can pass DB2 the java
object in binary format it'll gladly store that.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Gregor Kovač - 05 Jul 2006 08:33 GMT

>> By the way, do you have any idea when/if DB2 will support the ARRAY
>> datatype? Is it in Version 9 by any chance?
[quoted text clipped - 5 lines]
> Cheers
> Serge

Yep, usually (but unfortunattely not for all Java classes) you can serialize
an object in java and you get an array of bytes, which you can put into a
BLOB or VARCHAR FOR BIT DATA.

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Rhino - 05 Jul 2006 14:09 GMT

>>> By the way, do you have any idea when/if DB2 will support the ARRAY
>>> datatype? Is it in Version 9 by any chance?
[quoted text clipped - 10 lines]
> an object in java and you get an array of bytes, which you can put into a
> BLOB or VARCHAR FOR BIT DATA.

Thanks Gregor, I think you've just answered my question. It sounds like I
always need to convert all but the simplest of Objects to byte arrays for
storage in BLOBs or VARCHAR FOR BIT DATA columns. The only things I don't
need to convert to byte arrays are integers, doubles, floats,
dates/times/timestamps, and Strings: those can be stored directly in the
corresponding DB2 datatypes.

--
Rhino

Reply to this Message

Gregor Kovač - 05 Jul 2006 16:40 GMT

> Thanks Gregor, I think you've just answered my question. It sounds like I
> always need to convert all but the simplest of Objects to byte arrays for
[quoted text clipped - 5 lines]
> --
> Rhino

What you could also do is to persist Java object fields into table columns,
like:
class Person{
String name;
String address;
}

You don't have to serialize the Person class in order to store it into the
database, you can map fields (name and address) into a table PERSON that
has NAME and ADDRESS columns.
This way you can even work with collections, ....

This process is called object/relational persistence and there are numberous
product available. One of the better ones is Hibernate
(http://www.hibernate.org/). Others can be found at
http://java-source.net/open-source/persistence

Tell us what you come up with :)

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Rhino - 05 Jul 2006 14:26 GMT

>> By the way, do you have any idea when/if DB2 will support the ARRAY
>> datatype? Is it in Version 9 by any chance?
> Not in DB2 9. And if you want to know what's in Python you'll need an NDA
> and sign your name in blood.

It's okay, I wasn't trying to find out anything that hadn't already been
revealed :-) I just thought there might have been some kind of announcement
about a general strategic direction with respect to datatype support for
coming versons of DB2.

> Can you define what you mean by an object? If you can pass DB2 the java
> object in binary format it'll gladly store that.

Virtually everything in Java is an Object. In fact, the only things that
aren't Objects are the primitives: short, int, long, boolean, char, and one
or two others. And even the primitives have wrapper classes that can turn
them into Objects. So pretty much anything you can imagine is an Object: a
File, a URL, a ResultSet, an Image, GUI components, etc. etc. etc.

I suppose I was just dreaming the impossible dream but when I saw methods
setObject() and getObject() it occurred to me it might mean that _any_ Java
Object might be storable in and retrievable from DB2 _DIRECTLY_. Then,
instead of having to linearize the data into a byte stream for the insert
and then convert it back when I read the data, I could just store it in its
original form and get it back that same way.

Naturally, I looked at the Java API and the DB2 documentation on setObject()
and getObject() but I found them very vague and they didn't answer the
question of whether they worked the way that I would have liked them to
work. But it now seems pretty clear that they DON'T work the way I wanted.

It struck me that my impossible dream might be something that DB2 will be
doing somewhere down the road and might even have been announced at some
point; I could have easily missed such an announcement. That's why I asked
whether it was coming in Viper or further down the road.

I'm going to hold on to a vague hope that direct storage of any Object in a
DB2 table will be possible some day but I'm going to make sure that I don't
expect "some day" to be any time soon :-)

In the meantime, I will convert Objects to byte arrays as I currently do
with JPGs or audio files. This is not really a problem. It would just be
that much more convenient if I didn't have to convert to or from byte arrays
to store and use my Objects.

--
Rhino

> Cheers
> Serge

Reply to this Message

Serge Rielau - 05 Jul 2006 15:14 GMT

>>> By the way, do you have any idea when/if DB2 will support the ARRAY
>>> datatype? Is it in Version 9 by any chance?
[quoted text clipped - 5 lines]
> about a general strategic direction with respect to datatype support for
> coming versons of DB2.

One new data type that you can look forward to is a decimal floating
point type. That cat is out of the bag.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Purple-D - 06 Jul 2006 17:45 GMT

Rhino,

Iam currently migrating a database which has something similar to java
objects as primary keys. However the db2 datatype for that is char(16)
in our tables, so am guessing the java application merely converts the
object data into char(16) format when inserting or accessing records
from the relational tables. Ofcourse lot of this data is represented in
hexa and would not be visible with a mere select colname from
tablename.

I had a lot of problem migrating this to windows so am guessing there
is some platform dependence.

Cheers
PD

> >> By the way, do you have any idea when/if DB2 will support the ARRAY
> >> datatype? Is it in Version 9 by any chance?
[quoted text clipped - 53 lines]
> > IOD Conference
> > http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Purple-D - 06 Jul 2006 17:47 GMT

Reply to this Message

gimme_this_gimme_that@yahoo.com - 05 Jul 2006 23:32 GMT

Hey Rhino,

(Does that mean republican in name only?)

Why not write a text representation of the object into a LOB column?

You could use Java 1.4's java.beans.* package which provides tools for
the encoding and decoding:

This will get you started. This code encodes and decodes an object
representation from a file on a disk.

You'll have to modify the implementation to writing object to a LOB
column using JDBC.

import java.io.*;
import java.beans.*;

public class ENXML
{

public static void encode( String FileName , A a )
throws FileNotFoundException
{
XMLEncoder encoder = new XMLEncoder(
new BufferedOutputStream(
new FileOutputStream( FileName )));

encoder.writeObject( a );
encoder.close();
}

public static A decode( String FileName )
throws FileNotFoundException
{
XMLDecoder decoder = new XMLDecoder(
new BufferedInputStream(
new FileInputStream( FileName )));

A a = (A)decoder.readObject();
decoder.close();
return a;
}

Reply to this Message