column types in DB2 vs. other DBMS

[yoyo]

So I'm trying to make an application that currently works with MySql,
Postgre, etc... work with DB2.
THe problem I have, is, the varchar column only goes to 32k. CLOB goes
bigger, but at a major cost. SELECT DISTINCT, UPPER,LOWER, ORDER BY,
GROUP BY, among other things don't work on CLOB columns.
Without creating many resource robbing UDF's to emulate functionality,
is there something I'm missing? Is there a better way to store larger
than 32k of data in a DB2 database, and still have it be filterable?

[Serge Rielau]

yoyo wrote:

So I'm trying to make an application that currently works with MySql,
Postgre, etc... work with DB2.
THe problem I have, is, the varchar column only goes to 32k. CLOB goes
bigger, but at a major cost. SELECT DISTINCT, UPPER,LOWER, ORDER BY,
GROUP BY, among other things don't work on CLOB columns.
Without creating many resource robbing UDF's to emulate functionality,
is there something I'm missing? Is there a better way to store larger
than 32k of data in a DB2 database, and still have it be filterable?

There is always a first. Frankly this is the first time I hear a request
to run DISTINCT, ORDER BY or GROUP BY on large objects.
(UPPER/LOWER I may(!) buy into).
Would you mind telling us what could comes out of ordering a result set
by characters 32k deep into a CLOB objects? Who cares?
Whether two objects are the same (DISTINCT, GROUP BY) should not be
determined by a word document or jpeg. That's just too slow.
(It's not the UDF that's slow in that case it's the comparison itself).

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

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

[yoyo]

Serge Rielau wrote:

yoyo wrote:

>So I'm trying to make an application that currently works with MySql,
Postgre, etc... work with DB2.
THe problem I have, is, the varchar column only goes to 32k. CLOB goes
bigger, but at a major cost. SELECT DISTINCT, UPPER,LOWER, ORDER BY,
GROUP BY, among other things don't work on CLOB columns.
Without creating many resource robbing UDF's to emulate functionality,
is there something I'm missing? Is there a better way to store larger
than 32k of data in a DB2 database, and still have it be filterable?

There is always a first. Frankly this is the first time I hear a request
to run DISTINCT, ORDER BY or GROUP BY on large objects.
(UPPER/LOWER I may(!) buy into).
Would you mind telling us what could comes out of ordering a result set
by characters 32k deep into a CLOB objects? Who cares?
Whether two objects are the same (DISTINCT, GROUP BY) should not be
determined by a word document or jpeg. That's just too slow.
(It's not the UDF that's slow in that case it's the comparison itself).

Cheers
Serge

Mmm...ok sorry, it's not my query. I'm just trying to port the app, and
I was running into some queries that were coughing. I'm not the smarest
cookie when it comes to these complex queries.
They make use of the 'TEXT' datatype in MySQL and Postgre. MySQL limits
it to 64k. Postgre is, well says, unlimited. (I'd imagine it's limited
by other factors)
Given your response, I think I just re-arrangeed the query improperly.

What they were trying to do is this:

SELECT w,x,y,z from mytab where y=something group by w order by z
where column x is a clob.

DB2 coughs on this saying you cannot group by one thing when you
selected 2 other things.

SQL0119N An expression starting with "Z" specified in a SELECT clause,
HAVING clause, or ORDER BY clause is not specified in the GROUP BY
clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause
with a column function and no GROUP BY clause is specified.
It you put all the columns in the group by which it what DB2 asks for,
then it coughs on the CLOB column.

The way they did it for postgre was:

select distinct w,x,y,z from mytab where y=something order by z

DB2 coughs on this with
SQL0134N Improper use of a string column, host variable, constant, or
function "X". SQLSTATE=42907

So, I think I just need to re-think what they're tyring to get out of
this. They weren't really trying to group or order on the clob.

The upper/lower thing, on the other hand, so what's the best way to do a
case-insensitive search for a string inside a CLOB? I just want to see
if a user typed string exists in the clob, not paying attention to case.

My apologies for being an idiot, I'm a bit out of my league here, but
was just trying to help out.

[Brian Tkatch]

yoyo wrote:

Serge Rielau wrote:

yoyo wrote:

So I'm trying to make an application that currently works with MySql,
Postgre, etc... work with DB2.
THe problem I have, is, the varchar column only goes to 32k. CLOB goes
bigger, but at a major cost. SELECT DISTINCT, UPPER,LOWER, ORDER BY,
GROUP BY, among other things don't work on CLOB columns.
Without creating many resource robbing UDF's to emulate functionality,
is there something I'm missing? Is there a better way to store larger
than 32k of data in a DB2 database, and still have it be filterable?

There is always a first. Frankly this is the first time I hear a request
to run DISTINCT, ORDER BY or GROUP BY on large objects.
(UPPER/LOWER I may(!) buy into).
Would you mind telling us what could comes out of ordering a result set
by characters 32k deep into a CLOB objects? Who cares?
Whether two objects are the same (DISTINCT, GROUP BY) should not be
determined by a word document or jpeg. That's just too slow.
(It's not the UDF that's slow in that case it's the comparison itself).

Cheers
Serge

Mmm...ok sorry, it's not my query. I'm just trying to port the app, and
I was running into some queries that were coughing. I'm not the smarest
cookie when it comes to these complex queries.
They make use of the 'TEXT' datatype in MySQL and Postgre. MySQL limits
it to 64k. Postgre is, well says, unlimited. (I'd imagine it's limited
by other factors)
Given your response, I think I just re-arrangeed the query improperly.

What they were trying to do is this:

SELECT w,x,y,z from mytab where y=something group by w order by z
where column x is a clob.

DB2 coughs on this saying you cannot group by one thing when you
selected 2 other things.

SQL0119N An expression starting with "Z" specified in a SELECT clause,
HAVING clause, or ORDER BY clause is not specified in the GROUP BY
clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause
with a column function and no GROUP BY clause is specified.

Good. This is *supposed* to happen.

You could "cheat" and use agregate FUNCTIONs:

SELECT w,MIN(SUBSTR(x, 1)),MIN(y),MIN(z) from mytab where y=something
group by w order by z

But x, y, and z might not be from the same record.

BTW, the person who wrote the original query is asking for trouble, and
should not be trusted to write good SQL statements..

It you put all the columns in the group by which it what DB2 asks for,
then it coughs on the CLOB column

Exactly.

1) You can SUBSTR() the CLOB (in both the predicate and the GROUPing
clause) which should work.

2) Doing so will create more GROUPs, which may not be what you want. It
would be best to understand what the application really needs first.

The way they did it for postgre was:

select distinct w,x,y,z from mytab where y=something order by z

DISTINCT and GROUP BY without the use of an aggregate, essentially do
the same thing.

If this was a port itself, the author may also not have any idea what
the application should be doing.

DB2 coughs on this with
SQL0134N Improper use of a string column, host variable, constant, or
function "X". SQLSTATE=42907

Perhaps SUBSTR will help here as well.

So, I think I just need to re-think what they're tyring to get out of
this. They weren't really trying to group or order on the clob.

Yes! It sounds to me like whoever wrote the original SQL, hadn't the
slightest idea of SQL. You should figure out what is needed, write good
SQL here, and then patch the exisitng code with a real query.

If you know what is needed, we would be happy to help write the query.

B.

The upper/lower thing, on the other hand, so what's the best way to do a
case-insensitive search for a string inside a CLOB? I just want to see
if a user typed string exists in the clob, not paying attention to case.

My apologies for being an idiot, I'm a bit out of my league here, but
was just trying to help out.

[Serge Rielau]

SELECT w,x,y,z from mytab where y=something group by w order by z

where column x is a clob.

Pretty bizarre. I thought messing up group by was a Sybase specialty.
Let's assume:
mytab (w, x, y, z)
1, A, 2, B
1, C, 3, D
2, E, 4, F
2, G, 5, H
Which result do you expect?
The "first" row/group given the ORDER of z
1, A, 2, B
2, E, 4, F
or the "last" row/group given the order of z
1, C, 3, D
2, G, 5, H
or does it not matter?

Here is my best guess using first row per group (without delving into
the mySQL SQL Reference)
SELECT w, x, y, z
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY w ORDER BY z) AS rn,
w, x, y, z
FROM mytab) AS T
WHERE rn = 1
ORDER BY z

Lastly may I ask you to run a small test?
SELECT MAX(LENGTH(<thattextcolumn>)) FROM mytab

It appears the 64K were driven by what TEXT can do rather than what was
needed. So before you venture into the world of CLOB, it will be helpful
to find out what size you really need.
DB2 will reward you with much better performance if you stick with VARCHAR

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

[yoyo]

Serge Rielau wrote:

SELECT w,x,y,z from mytab where y=something group by w order by z
where column x is a clob.

Pretty bizarre. I thought messing up group by was a Sybase specialty.
Let's assume:
mytab (w, x, y, z)
1, A, 2, B
1, C, 3, D
2, E, 4, F
2, G, 5, H
Which result do you expect?
The "first" row/group given the ORDER of z
1, A, 2, B
2, E, 4, F
or the "last" row/group given the order of z
1, C, 3, D
2, G, 5, H
or does it not matter?

Here is my best guess using first row per group (without delving into
the mySQL SQL Reference)
SELECT w, x, y, z
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY w ORDER BY z) AS rn,
w, x, y, z
FROM mytab) AS T
WHERE rn = 1
ORDER BY z

Lastly may I ask you to run a small test?
SELECT MAX(LENGTH(<thattextcolumn>)) FROM mytab

It appears the 64K were driven by what TEXT can do rather than what was
needed. So before you venture into the world of CLOB, it will be helpful
to find out what size you really need.
DB2 will reward you with much better performance if you stick with VARCHAR

Cheers
Serge

I've looked at what is really needed for size. 32k is enough for 99% of
the time, but *some* entries will be bigger. (It's a Weblog app
http://www.s9y.org) THe report is that the 64limit has been hit a few
times. But it's a web app, so 64k is the largest generally allowable
textbox input anyway. So if 64 has been hit, I'm sure entries over 32k
have been created alot more. The team at s9y.org has told me just put
32k and leave well enough alone, but my sentiment is DB2 should be able
to handle more than the *other* guys. Re-organizing the SQL to be better
is always a plus as well, which I'm sure their open to.
My database is clean, starting from scratch, so the test won't produce
anything, except my biggest entry so far which is just under 32k. (I'm
using VARCHAR currently). The reason for wanting that column to be CLOB,
well, frankly I'm not really sure. A DB2 developer (no longer at IBM)
orginally started to port this thing to DB2, he choose CLOB(32k) for
that column. Why limit it to 32k and not use a varchar seems really
really odd, but mabey there is a reason. I'm sure he's alot smarter than
me when it comes to this stuff, so I was trying to make the CLOB thing work.
I'm still trying to figure out what results are really intended...

[Mark A]

"yoyo" <yo**@ma.comwrote in message
news:ia******************************@centurytel.n et...

I've looked at what is really needed for size. 32k is enough for 99% of
the time, but *some* entries will be bigger. (It's a Weblog app
http://www.s9y.org) THe report is that the 64limit has been hit a few
times. But it's a web app, so 64k is the largest generally allowable
textbox input anyway. So if 64 has been hit, I'm sure entries over 32k
have been created alot more. The team at s9y.org has told me just put 32k
and leave well enough alone, but my sentiment is DB2 should be able to
handle more than the *other* guys. Re-organizing the SQL to be better is
always a plus as well, which I'm sure their open to.
My database is clean, starting from scratch, so the test won't produce
anything, except my biggest entry so far which is just under 32k. (I'm
using VARCHAR currently). The reason for wanting that column to be CLOB,
well, frankly I'm not really sure. A DB2 developer (no longer at IBM)
orginally started to port this thing to DB2, he choose CLOB(32k) for that
column. Why limit it to 32k and not use a varchar seems really really odd,
but mabey there is a reason. I'm sure he's alot smarter than me when it
comes to this stuff, so I was trying to make the CLOB thing work.
I'm still trying to figure out what results are really intended...

CLOB and BLOB columns are stored outside of the normal table are in DB2 and
they don't make use of bufferpools, which are limited to a max of 32K page
size. All CLOB and BLOB reads and writes bypass the bufferpool and go
directly to disk, although OS disk caching can help here.

So that is one of the main reasons DB2 limits VARCHAR to just under 32K. In
practice, you need to make sure the row size cannot exceed 32k (minus some
page and row overhead).

[Serge Rielau]

Mark A wrote:

CLOB and BLOB columns are stored outside of the normal table are in DB2 and
they don't make use of bufferpools, which are limited to a max of 32K page
size. All CLOB and BLOB reads and writes bypass the bufferpool and go
directly to disk, although OS disk caching can help here.

So that is one of the main reasons DB2 limits VARCHAR to just under 32K. In
practice, you need to make sure the row size cannot exceed 32k (minus some
page and row overhead).

Further, once you are in LOB-land the actual value doesn't matter
anymore. CLOB(32K) will realistically have the same performance as
CLOB(2M) for your 99% case. (Of course if each row is 2MB that won't go
unnoticed).

Using the LOB may well be fast enough for your needs. If it isn't there
are tricks. But let's climb that mountain when we get there.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

[73blazer]

yoyo wrote:

Serge Rielau wrote:

> SELECT w,x,y,z from mytab where y=something group by w order by z
where column x is a clob.

Pretty bizarre. I thought messing up group by was a Sybase specialty.
Let's assume:
mytab (w, x, y, z)
1, A, 2, B
1, C, 3, D
2, E, 4, F
2, G, 5, H
Which result do you expect?
The "first" row/group given the ORDER of z
1, A, 2, B
2, E, 4, F
or the "last" row/group given the order of z
1, C, 3, D
2, G, 5, H
or does it not matter?

Here is my best guess using first row per group (without delving into
the mySQL SQL Reference)
SELECT w, x, y, z
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY w ORDER BY z) AS rn,
w, x, y, z
FROM mytab) AS T
WHERE rn = 1
ORDER BY z

Lastly may I ask you to run a small test?
SELECT MAX(LENGTH(<thattextcolumn>)) FROM mytab

It appears the 64K were driven by what TEXT can do rather than what
was needed. So before you venture into the world of CLOB, it will be
helpful to find out what size you really need.
DB2 will reward you with much better performance if you stick with
VARCHAR

Cheers
Serge

I've looked at what is really needed for size. 32k is enough for 99% of
the time, but *some* entries will be bigger. (It's a Weblog app
http://www.s9y.org) THe report is that the 64limit has been hit a few
times. But it's a web app, so 64k is the largest generally allowable
textbox input anyway. So if 64 has been hit, I'm sure entries over 32k
have been created alot more. The team at s9y.org has told me just put
32k and leave well enough alone, but my sentiment is DB2 should be able
to handle more than the *other* guys. Re-organizing the SQL to be better
is always a plus as well, which I'm sure their open to.
My database is clean, starting from scratch, so the test won't produce
anything, except my biggest entry so far which is just under 32k. (I'm
using VARCHAR currently). The reason for wanting that column to be CLOB,
well, frankly I'm not really sure. A DB2 developer (no longer at IBM)
orginally started to port this thing to DB2, he choose CLOB(32k) for
that column. Why limit it to 32k and not use a varchar seems really
really odd, but mabey there is a reason. I'm sure he's alot smarter than
me when it comes to this stuff, so I was trying to make the CLOB thing
work.
I'm still trying to figure out what results are really intended...

Here's one of the queries:
SELECT c.categoryid,
c.category_name,
c.category_icon,
c.category_description,
c.authorid,
c.category_left,
c.category_right,
c.parentid,
a.username,
a.realname
FROM s9y_category AS c
LEFT OUTER JOIN s9y_authors AS a
ON c.authorid = a.authorid
LEFT OUTER JOIN s9y_authorgroups AS ag
ON ag.authorid = 1
LEFT OUTER JOIN s9y_access AS acl
ON (ag.groupid = acl.groupid AND acl.artifact_id = c.categoryid)

GROUP BY c.categoryid
ORDER BY category_name ASC

Any suggestions on how to reformat this.? I've never used GROUP BY
clauses, I'm having a hard time understanding what they really do.
There's several more queries very similiar.

[73blazer]

73blazer wrote:

yoyo wrote:

>Serge Rielau wrote:

>> SELECT w,x,y,z from mytab where y=something group by w order by z
where column x is a clob.
Pretty bizarre. I thought messing up group by was a Sybase specialty.
Let's assume:
mytab (w, x, y, z)
1, A, 2, B
1, C, 3, D
2, E, 4, F
2, G, 5, H
Which result do you expect?
The "first" row/group given the ORDER of z
1, A, 2, B
2, E, 4, F
or the "last" row/group given the order of z
1, C, 3, D
2, G, 5, H
or does it not matter?

Here is my best guess using first row per group (without delving into
the mySQL SQL Reference)
SELECT w, x, y, z
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY w ORDER BY z) AS rn,
w, x, y, z
FROM mytab) AS T
WHERE rn = 1
ORDER BY z

Lastly may I ask you to run a small test?
SELECT MAX(LENGTH(<thattextcolumn>)) FROM mytab

It appears the 64K were driven by what TEXT can do rather than what
was needed. So before you venture into the world of CLOB, it will be
helpful to find out what size you really need.
DB2 will reward you with much better performance if you stick with
VARCHAR

Cheers
Serge

I've looked at what is really needed for size. 32k is enough for 99%
of the time, but *some* entries will be bigger. (It's a Weblog app
http://www.s9y.org) THe report is that the 64limit has been hit a few
times. But it's a web app, so 64k is the largest generally allowable
textbox input anyway. So if 64 has been hit, I'm sure entries over 32k
have been created alot more. The team at s9y.org has told me just put
32k and leave well enough alone, but my sentiment is DB2 should be
able to handle more than the *other* guys. Re-organizing the SQL to be
better is always a plus as well, which I'm sure their open to.
My database is clean, starting from scratch, so the test won't produce
anything, except my biggest entry so far which is just under 32k. (I'm
using VARCHAR currently). The reason for wanting that column to be
CLOB, well, frankly I'm not really sure. A DB2 developer (no longer at
IBM) orginally started to port this thing to DB2, he choose CLOB(32k)
for that column. Why limit it to 32k and not use a varchar seems
really really odd, but mabey there is a reason. I'm sure he's alot
smarter than me when it comes to this stuff, so I was trying to make
the CLOB thing work.
I'm still trying to figure out what results are really intended...

Here's one of the queries:
SELECT c.categoryid,
c.category_name,
c.category_icon,
c.category_description,
c.authorid,
c.category_left,
c.category_right,
c.parentid,
a.username,
a.realname
FROM s9y_category AS c
LEFT OUTER JOIN s9y_authors AS a
ON c.authorid = a.authorid
LEFT OUTER JOIN s9y_authorgroups AS ag
ON ag.authorid = 1
LEFT OUTER JOIN s9y_access AS acl
ON (ag.groupid = acl.groupid AND acl.artifact_id = c.categoryid)

GROUP BY c.categoryid
ORDER BY category_name ASC

Any suggestions on how to reformat this.? I've never used GROUP BY
clauses, I'm having a hard time understanding what they really do.
There's several more queries very similiar.

Oh, sorry, category description is the CLOB column.

[Serge Rielau]

OK. things are getting a bit clearer now.
assuming that category id is a key
It seem like your join is expanding (or at least the author of the query
thought it might).
Thd reason for that appear to be access and authorgroup which contribute
absolutely nothing to your query as far as I can see.
This puppy looks particularly poisonaous to me:
LEFT OUTER JOIN s9y_authorgroups AS ag
ON ag.authorid = 1

Try this (all I did was remove stuff):
SELECT c.categoryid,
c.category_name,
c.category_icon,
c.category_description,
c.authorid,
c.category_left,
c.category_right,
c.parentid,
a.username,
a.realname
FROM s9y_category AS c
LEFT OUTER JOIN s9y_authors AS a
ON c.authorid = a.authorid
ORDER BY category_name ASC

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

[Brian Tkatch]

yoyo wrote:

Serge Rielau wrote:

SELECT w,x,y,z from mytab where y=something group by w order by z
where column x is a clob.

Pretty bizarre. I thought messing up group by was a Sybase specialty.
Let's assume:
mytab (w, x, y, z)
1, A, 2, B
1, C, 3, D
2, E, 4, F
2, G, 5, H
Which result do you expect?
The "first" row/group given the ORDER of z
1, A, 2, B
2, E, 4, F
or the "last" row/group given the order of z
1, C, 3, D
2, G, 5, H
or does it not matter?

Here is my best guess using first row per group (without delving into
the mySQL SQL Reference)
SELECT w, x, y, z
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY w ORDER BY z) AS rn,
w, x, y, z
FROM mytab) AS T
WHERE rn = 1
ORDER BY z

Lastly may I ask you to run a small test?
SELECT MAX(LENGTH(<thattextcolumn>)) FROM mytab

It appears the 64K were driven by what TEXT can do rather than what was
needed. So before you venture into the world of CLOB, it will be helpful
to find out what size you really need.
DB2 will reward you with much better performance if you stick with VARCHAR

Cheers
Serge

I've looked at what is really needed for size. 32k is enough for 99% of
the time, but *some* entries will be bigger. (It's a Weblog app
http://www.s9y.org) THe report is that the 64limit has been hit a few
times. But it's a web app, so 64k is the largest generally allowable
textbox input anyway. So if 64 has been hit, I'm sure entries over 32k
have been created alot more. The team at s9y.org has told me just put
32k and leave well enough alone, but my sentiment is DB2 should be able
to handle more than the *other* guys. Re-organizing the SQL to be better
is always a plus as well, which I'm sure their open to.
My database is clean, starting from scratch, so the test won't produce
anything, except my biggest entry so far which is just under 32k. (I'm
using VARCHAR currently). The reason for wanting that column to be CLOB,
well, frankly I'm not really sure. A DB2 developer (no longer at IBM)
orginally started to port this thing to DB2, he choose CLOB(32k) for
that column. Why limit it to 32k and not use a varchar seems really
really odd, but mabey there is a reason. I'm sure he's alot smarter than
me when it comes to this stuff, so I was trying to make the CLOB thing work.
I'm still trying to figure out what results are really intended...

32k is enough for 99% of the time, but *some* entries will be bigger.
THe report is that the 64limit has been hit a few times.

The "quick and dirty" method would be to keep as a VARCHAR, and CREATE
a second TABLE with three COLUMNs. The first being an FK to the
original TABLE, the second being an id (autoincrementing is fine) and
the third being a large VARCHAR. The first two COLUMNs are a composite
PK. Then, when checking the VARCHAR as usual, check the other TABLE for
child records.

B.

[73blazer]

Serge Rielau wrote:

OK. things are getting a bit clearer now.
assuming that category id is a key
It seem like your join is expanding (or at least the author of the query
thought it might).
Thd reason for that appear to be access and authorgroup which contribute
absolutely nothing to your query as far as I can see.
This puppy looks particularly poisonaous to me:
LEFT OUTER JOIN s9y_authorgroups AS ag
ON ag.authorid = 1

Try this (all I did was remove stuff):
SELECT c.categoryid,
c.category_name,
c.category_icon,
c.category_description,
c.authorid,
c.category_left,
c.category_right,
c.parentid,
a.username,
a.realname
FROM s9y_category AS c
LEFT OUTER JOIN s9y_authors AS a
ON c.authorid = a.authorid
ORDER BY category_name ASC

Cheers
Serge

This query works, that is to say the page doesn't crash and I see what
I'm supposed to on it. But I have to fully test the application yet,
which probably won't happen until next week. They said something about
if something belonged to 2 categories, that's why the distinct/group by
was in there to filter that out. I've got things belonging to multiple
categories,and I only see it once displayed, so I think the results are
right.
I much appreciate your help. I might have some more queries to ask about.