DB2与其他DBMS中的列类型 [英] column types in DB2 vs. other DBMS

问题描述

所以我正在尝试创建一个当前适用于MySql的应用程序，
Postgre等...使用DB2。

我遇到的问题，是的，varchar列只有32k。 CLOB的价格更高，但需要付出很大的代价。 SELECT DISTINCT，UPPER，LOWER，ORDER BY，

GROUP BY，除其他外，不用于CLOB列。

没有创建许多资源抢夺UDF''模仿功能，

有什么我想念的吗？是否有更好的方法来存储比DB2数据库中的32k数据更大的数据，并且仍然可以过滤？

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?

推荐答案

yoyo写道：

yoyo wrote:

所以我正在尝试制作一个目前适用于MySql的应用程序，

Postgre等。 ..使用DB2。

我遇到的问题是，varchar列只有32k。 CLOB的价格更高，但需要付出很大的代价。 SELECT DISTINCT，UPPER，LOWER，ORDER BY，

GROUP BY，除其他外，不用于CLOB列。

没有创建许多资源抢夺UDF''模仿功能，

有什么我想念的吗？是否有更好的方法来存储比DB2数据库中的32k数据更大的数据，并且仍然可以过滤？

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?

总有第一个。坦率地说，这是我第一次听到请求

在大型物体上运行DISTINCT，ORDER BY或GROUP BY。

（UPPER / LOWER我可能（！）买入）。

你能不能告诉我们在一个CLOB对象的32k深度字符中输入结果集

会产生什么结果？谁在乎呢？

两个对象是否相同（DISTINCT，GROUP BY）不应该是由word文档或jpeg决定的。那太慢了。

（在这种情况下，这不是UDF，而是比较本身）。


干杯

Serge

-

Serge Rielau

DB2解决方案开发

IBM多伦多实验室


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

Serge Rielau写道：

Serge Rielau wrote:

yoyo写道：

yoyo wrote:

>所以我正在尝试制作一个应用程序目前适用于MySql，Postgre等...与DB2合作。
我遇到的问题是，varchar列只有32k。 CLOB更大，但成本很高。 SELECT DISTINCT，UPPER，LOWER，ORDER BY，
GROUP BY，除其他外，不在CLOB列上工作。
没有创建许多资源抢夺UDF来模拟功能，
有什么我想念的吗？是否有更好的方法来存储比DB2数据库中的32k更大的数据，并且仍然可以过滤？

>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?

总有第一个。坦率地说，这是我第一次听到请求

在大型物体上运行DISTINCT，ORDER BY或GROUP BY。

（UPPER / LOWER我可能（！）买入）。

你能不能告诉我们在一个CLOB对象的32k深度字符中输入结果集

会产生什么结果？谁在乎呢？

两个对象是否相同（DISTINCT，GROUP BY）不应该是由word文档或jpeg决定的。那太慢了。

（在这种情况下，这不是UDF，而是比较本身）。


干杯

Serge

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

嗯...好的抱歉，这不是我的疑问。我只是试图移植应用程序，并且

我遇到了一些咳嗽的问题。当涉及到这些复杂的查询时，我不是最好的

cookie。

他们在MySQL和Postgre中使用''TEXT''数据类型。 MySQL限制

到64k。好吧，Postgre是无限的。 （我认为它受限于其他因素的b $ b）

鉴于您的回复，我想我只是不正确地重新安排了查询。


他们试图做的是：


从mytab中选择w，x，y，z其中y =某个组按w顺序排列z

其中第x列是clob。


DB2咳嗽这个说你不能分组一个当你选择
另外2个事情。


SQL0119N以Z开头的表达式在SELECT子句中指定，

HAVING子句或ORDER BY子句未在GROUP BY

子句中指定，或者它在SELECT子句，HAVING子句或ORDER中BY子句
带有列函数的
并且没有指定GROUP BY子句。

它将所有列放在DB2所要求的组中，

然后它在CLOB专栏上咳嗽。


他们为postgre做的方式是：


选择不同的w，来自mytab的x，y，z其中y = z的顺序

DB2咳嗽这个用

SQL0134N不正确使用字符串列，主变量，常数，或

函数X。 SQLSTATE = 42907


所以，我想我只需要重新考虑一下他们是什么东西才能摆脱这个
。他们并不是真的想要分组或订购。


另一方面，上/下的东西是什么，所以最好的方法是做什么？ br />
不区分大小写搜索CLOB中的字符串？我只想看看

如果clob中存在用户输入的字符串，而不关注案例。


我为自己是个白痴道歉，我我的联赛在这里不合适，但是

只是想帮忙。

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.

yoyo写道：

yoyo wrote:

Serge Rielau写道：

Serge Rielau wrote:

yoyo写道：

yoyo wrote:

所以我''我试图创建一个当前可以与MySql一起使用的应用程序，

Postgre等...使用DB2。

问题我有，varchar列只到32k。 CLOB的价格更高，但需要付出很大的代价。 SELECT DISTINCT，UPPER，LOWER，ORDER BY，

GROUP BY，除其他外，不用于CLOB列。

没有创建许多资源抢夺UDF''模仿功能，

有什么我想念的吗？是否有更好的方法来存储比DB2数据库中的32k数据更大的数据，并且仍然可以过滤？

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?

总有第一个。坦率地说，这是我第一次听到请求

在大型物体上运行DISTINCT，ORDER BY或GROUP BY。

（UPPER / LOWER我可能（！）买入）。

你能不能告诉我们在一个CLOB对象的32k深度字符中输入结果集

会产生什么结果？谁在乎呢？

两个对象是否相同（DISTINCT，GROUP BY）不应该是由word文档或jpeg决定的。那太慢了。

（在这种情况下，这不是UDF，而是比较本身）。


干杯

Serge

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

嗯...好的抱歉，这不是我的疑问。我只是试图移植应用程序，并且

我遇到了一些咳嗽的问题。当涉及到这些复杂的查询时，我不是最好的

cookie。

他们在MySQL和Postgre中使用''TEXT''数据类型。 MySQL限制

到64k。好吧，Postgre是无限的。 （我认为它受限于其他因素的b $ b）

鉴于您的回复，我想我只是不正确地重新安排了查询。


他们试图做的是：


从mytab中选择w，x，y，z其中y =某个组按w顺序排列z

其中第x列是clob。


DB2咳嗽这个说你不能分组一个当你选择
另外2个事情。


SQL0119N以Z开头的表达式在SELECT子句中指定，

HAVING子句或ORDER BY子句未在GROUP BY

子句中指定，或者它在SELECT子句，HAVING子句或ORDER中BY子句
带有列函数的
并且未指定GROUP BY子句。

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.

好​​。这* *应该*发生。


你可以欺骗并使用agregate FUNCTIONs：


来自mytab的SELECT w，MIN（SUBSTR（x，1）），MIN（y），MIN（z）其中y =某事

by w order by z


但x，y和z可能不是来自同一记录。


BTW，编写原始查询的人要求麻烦，并且不应该信任
来编写好的SQL语句..

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..

你呢将所有列放在DB2所要求的组中，

然后它在CLOB列上咳嗽

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

正好。


1）你可以SUBSTR（）CLOB（在谓词和GROUPING

子句中）应该有效。


2）这样做会产生更多的GROUP，这可能不是你想要的。它最好先了解应用程序真正需要的内容。

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.

他们为postgre做的方式是：


从mytab中选择不同的w，x，y，z其中y = z
的顺序

The way they did it for postgre was:

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

DISTINCT和GROUP BY不使用一个聚合，基本上就是做同样的事情。


如果这是一个端口本身，作者可能也不知道是什么

应用程序应该这样做。

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咳嗽这个用

SQL0134N不正确使用字符串列，主变量，常量，或

函数X。 SQLSTATE = 42907

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

也许SUBSTR也会在这里提供帮助。

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.

是的！对我来说听起来就像编写原始SQL的人一样，对于SQL没有任何想法。你应该弄清楚需要什么，在这里写好，然后用真正的查询来修补exisitng代码。


如果你知道需要什么，我们很乐意帮助编写查询。


B.

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.

上/下的东西，另一方面那么，什么是对CLOB中的字符串进行不区分大小写的搜索？b

$ b的最佳方法是什么？我只想看看

如果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.

这篇关于DB2与其他DBMS中的列类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！