Hi all,

I've got a DB2 database for a J2EE app that is going into production. The app puts into a varchar(4000) field some 'supplementary data' that belongs to a transaction from this system. The data is entered into this field as key/value pairs.

I'm currently writing some reporting solutions for this system. I'm just wondering what's the best way to handle this field.

I would have the 'key' to find the respective value for but then the SQL will take a while and there will most probably be a performance hit on the entering of data from the system.

The question i have is:
1. is there a better to handing reporting on this field?
2. if i do a search on this varchar(4000) field will i lock the table so then while someone is running this report the operational J2EE system won't be able to insert data into this table?

Appreciate any comments.
mav.

Are you searching for how the varchar begins or searching for some string within the column? If it's a string within the varchar and its always in the same position you may want to think about putting that into its own column, then put an index on that new column.

The varchar field might have 50 key/value pairs and i'm looking for the value of a particular key. (eg. the 20th set).

The key won't be in the same position every time in the field either, so i can't go straight there.

I know that if i create a new table and extract these field into that new table with an index on the key then it will be great. However, i'm trying to stay away from changing the db schema.

any other suggestions?

mav.