《DB2设计与性能优化》作者做客ITPUB，举办DB2提问有奖活动，活动结束！

张广舟

原帖由 hanhoudba 于 2011-5-12 11:32 发表

这个是求救贴啊，会把回答时间挤压没了，呵呵

王飞鹏2011

原帖由 hanhoudba 于 2011-5-12 11:32 发表

王飞鹏2011

原帖由 hanhoudba 于 2011-5-12 11:26 发表
急！！！请高手写一个对应的DB2的SQL PL自定义函数，下面是Oracle原来的UDF，就是将字符串数组中的多个字符串合并成一个字符串，用逗号隔开。 比如，传入数组[‘aa’, 'bb', 'cc'], 返回‘aa, bb,cc’。
感激中！
create or replace type strings_table is table of varchar2(32);

///////////Oracle's UDF
create or replace function mymerge (pv in strings_table) return varchar2
is
ls varchar2(32000);
begin
for i in 1..pv.count loop
ls := ls || ',' || pv(i);
end loop;
return substr(ls,2);
end;

DB2中没有table of 这种定义，像你这种情况，是可以用字符数组类型代替的。这个UDF用DB2 SQL ＰL的实现如下，看看是否能对路。
1 CREATE TYPE STRINGS_TABLE AS varchar(32000) ARRAY[]#
2 drop function mymerge#
3 create function mymerge (in pv strings_table)
4 returns varchar(32000)
5 language SQL
6 begin
7 declare i int;
8 declare maxnum int;
9 declare ls varchar(32000) default '';
10 set i = 1;
11 set maxnum = CARDINALITY(pv) + 1;
12 while i< maxnum do
13 set ls = ls || ',' || pv(i);
14 set i = i+1;
15 end while;
16 return substr(ls,2);
17 end#


不要急躁。

hanhoudba

原帖由 王飞鹏2011 于 2011-5-12 12:06 发表



DB2中没有table of 这种定义，像你这种情况，是可以用字符数组类型代替的。这个UDF用DB2 SQL ＰL的实现如下，看看是否能对路。
1 CREATE TYPE STRINGS_TABLE AS varchar(32000) ARRAY[]#
2 drop function mymerge#
3 create function mymerge (in pv strings_table)
4 returns varchar(32000)
5 language SQL
6 begin
7 declare i int;
8 declare maxnum int;
9 declare ls varchar(32000) default '';
10 set i = 1;
11 set maxnum = CARDINALITY(pv) + 1;
12 while i< maxnum do
13 set ls = ls || ',' || pv(i);
14 set i = i+1;
15 end while;
16 return substr(ls,2);
17 end#


不要急躁。

听您指教！现在对这个问题还有些晕：
用这个UDF的简化的SQL语句呢，您看看下边。是这样的，它将一个产品的所有组成部件名称用mymerge组合成了一个字符串。然后呢，其中用到了multiset把单列的查询结果集转换成了strings_table, 这样的话，不知DB2还支持multiset吗?
select p.product_id, p.product_name
mymerge( cast (multiset (select pt.part_name
from part pt where pt.product_id = p.product_id)
as strings_table)
) part_names
from product p;

另外，还有其他办法吗？我不急躁啦，不过，还希望作者高手尽尽快回复。

aerwe1984

精彩！在这里跟大师学到好多知识。

李玉明-421

原帖由 〇〇 于 2011-5-11 20:42 发表
4.怎么在select语句中指定2个表之间用hash连接
5.表压缩后，什么操作会更慢

4. 可以用DB2的optguidelines, 指定hash连接
SELECT * FROM T1 , T2  where t1.c1 = t2.c1
/* <OPTGUIDELINES> <HSJOIN> <TBSCAN TABLE='T1'/> <TBSCAN TABLE='T2'/> </HSJOIN> </OPTGUIDELINES> */ ;

5. 表压缩可以减少磁盘IO，在内存中处理压缩页时，首先减压缩这个页，然后做扫描页并作处理。压缩和解压大概会增加5%～10%的CPU时间，但由于处理查询时，IO的开销比CPU大，减少的IO开销得到的好处远大于增加的CPU开销。

王飞鹏2011

原帖由 hanhoudba 于 2011-5-12 12:30 发表



听您指教！现在对这个问题还有些晕：
用这个UDF的简化的SQL语句呢，您看看下边。是这样的，它将一个产品的所有组成部件名称用mymerge组合成了一个字符串。然后呢，其中用到了multiset把单列的查询结果集转换成了strings_table, 这样的话，不知DB2还支持multiset吗?
select p.product_id, p.product_name
mymerge( cast (multiset (select pt.part_name
from part pt where pt.product_id = p.product_id)
as strings_table)
) part_names
from product p;

另外，还有其他办法吗？我不急躁啦，不过，还希望作者高手尽尽快回复。

DB2是不支持multiset的。 照你的SQL的含义，是将某条查询的结果集拼接成','隔开的字符串返回的。告诉你一个思路：尝试改写这个函数的原型，比如将mymerge里的这条SQL作为字符串传入这个函数。
如果这样ok，就利用动态SQL和游标将这个字符串拼接起来，代码可以这样写：

create function mymerge(in strings_SQL varchar(32000))
returns varchar(32000)
language SQL
begin
declare app_col varchar(1024);
declare rtcols varchar(32000) default '';
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
declare stmt statement;
declare mycursor cursor for stmt;
prepare stmt from strings_SQL;
open mycursor;
fetch mycursor into app_col;
while(SQLSTATE='00000') do
set rtcols = rtcols || ' ,' || app_col;
fetch mycursor into app_col;
end while;
close mycursor;
return substr(rtcols,2);
end#



说老实话，我还不了解你的环境，且时间也不够，简单测试如下：

$db2 "select name from tt"

name
-------------------
aa
bb
cc

3 record(s) selected.  

$db2 "values mymerge('select name from tt')"

1
-------------------
aa, bb, cc

1 record(s) selected.  


不过你提的问题在DB2应用开发中还不够典型，国内最近一两年没有出版什么DB2开发的书，这方面也积累了大量的典型问题，这也是舞动DB2系列开发篇要解决的问题。

hanhoudba

原帖由 王飞鹏2011 于 2011-5-12 13:19 发表



DB2是不支持multiset的。 照你的SQL的含义，是将某条查询的结果集拼接成','隔开的字符串返回的。告诉你一个思路：尝试改写这个函数的原型，比如将mymerge里的这条SQL作为字符串传入这个函数。
如果这样ok，就利用动态SQL和游标将这个字符串拼接起来，代码可以这样写：

create function mymerge(in strings_SQL varchar(32000))
returns varchar(32000)
language SQL
begin
declare app_col varchar(1024);
declare rtcols varchar(32000) default '';
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
declare stmt statement;
declare mycursor cursor for stmt;
prepare stmt from strings_SQL;
open mycursor;
fetch mycursor into app_col;
while(SQLSTATE='00000') do
set rtcols = rtcols || ' ,' || app_col;
fetch mycursor into app_col;
end while;
close mycursor;
return substr(rtcols,2);
end#



说老实话，我还不了解你的环境，且时间也不够，简单测试如下：

$db2 "select name from tt"

name
-------------------
aa
bb
cc

3 record(s) selected.  

$db2 "values mymerge('select name from tt')"

1
-------------------
aa, bb, cc

1 record(s) selected.  


不过你提的问题在DB2应用开发中还不够典型，国内最近一两年没有出版什么DB2开发的书，这方面也积累了大量的典型问题，这也是舞动DB2系列开发篇要解决的问题。

〇〇

原帖由 李玉明-421 于 2011-5-12 13:14 发表


4. 可以用DB2的optguidelines, 指定hash连接
SELECT * FROM T1 , T2  where t1.c1 = t2.c1
/*       */ ;

5. 表压缩可以减少磁盘IO，在内存中处理压缩页时，首先减压缩这个页，然后做扫描页并作处理。压缩和解压大概会增加5%～10%的CPU时间，但由于处理查询时，IO的开销比CPU大，减少的IO开销得到的好处远大于增加的CPU开销。

谢谢，我来试一试
另外再问个问题，db2有结果缓存？
我有个查询，就是前面31#帖子给出的代码，运行多遍，时间从3秒多2秒多到0.3秒，但另一台机器还是20多秒..
用db2batch -d tpch -f /arch/IBM/q02_3.txt >/tmp/q02_3.log执行的

〇〇

请问我用了NLJOIN优化提示，可是执行计划还是HSJOIN

Statement:
  
  select count(*)
  from t1, t2
  where t1.id=t2.id /* <OPTGUIDELINES> <NLJOIN> <TBSCAN TABLE='T1' />
          <TBSCAN TABLE='T2' /> </NLJOIN> </OPTGUIDELINES> */


Section Code Page = 1208

Estimated Cost = 15.168448
Estimated Cardinality = 1.000000

Access Table Name = DB2INST1.T2  ID = 4,13
|  #Columns = 1
|  Skip Inserted Rows
|  Avoid Locking Committed Data
|  Currently Committed for Cursor Stability
|  May participate in Scan Sharing structures
|  Scan may start anywhere and wrap, for completion
|  Fast scan, for purposes of scan sharing management
|  Scan can be throttled in scan sharing management
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  Process Build Table for Hash Join
Hash Join
|  Estimated Build Size: 4000   
|  Estimated Probe Size: 4000   
|  Access Table Name = DB2INST1.T1  ID = 4,12
|  |  #Columns = 1
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  Process Probe Table for Hash Join
Aggregation
|  Column Function(s)
Return Data to Application
|  #Columns = 1

End of section