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

晴天小龙

很早就听说DB2数据库了！但一直没接触过。
请问：
DB2 和Oracle的语法区别大吗？
存储过程和函数和Oracle有什么区别？听说DB2的存储过程调试起来比较复杂。
目前DB2最新使用的是什么版本？有没有免费版？
Oracle或SQL Server的数据通过什么方式移植到DB2里？
维运起来方便吗？

heat6

DB2和sql server ,oracle的優缺點?

李玉明-421

哦，你需要设置下面的注册变量，然后重启数据库才有效。 你可以再试一下！

1. db2set DB2_OPTPROFILE=YES;
   
2. db2stop force;
   
3. db2start;

复制代码

原帖由 〇〇 于 2011-5-12 14:29 发表
请问我用了NLJOIN优化提示，可是执行计划还是HSJOIN

Statement:
  
  select count(*)
  from t1, t2
  where t1.id=t2.id /*   
             */


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

Ryan-liumin

DB2是不是适合任何数据库的需求？

〇〇

为什么我启用了分区内并行，反而更慢了

468.879329 seconds 482.459168
  3.456943 seconds   4.283617
35.196633 seconds  49.522005
24.756155 seconds  25.135826
27.720584 seconds  30.382384
103.166410 seconds 100.084318
40.526446 seconds  47.550015
34.452728 seconds  40.439229
88.036224 seconds  87.582887
30.404406 seconds  36.627290
  6.502055 seconds   7.831795
38.336033 seconds  33.335061
49.450705 seconds  66.658736
32.759258 seconds  32.816498
36.062827 seconds  36.917765
  8.496194 seconds  19.588469
27.698119 seconds  23.122384
167.040236 seconds 234.650377
40.308232 seconds
587.439039 seconds
61.395391 seconds
60.756332 seconds

启用步骤
[db2inst1@750_ora1a:/home/db2inst1]#>db2batch -d tpch -f /arch/IBM/q01-22_f.txt > /tmp/q01-22_f_zip.log


[db2inst1@750_ora1a:/home/db2inst1]#>[db2inst1@750_ora1a:/home/db2inst1]#>[db2inst1@750_ora1a:/home/db2inst1]#>
[db2inst1@750_ora1a:/home/db2inst1]#>db2 get dbm cfg|grep PARA
Enable intra-partition parallelism     (INTRA_PARALLEL) = NO
[db2inst1@750_ora1a:/home/db2inst1]#>db2 update dbm cfg using INTRA_PARALLEL YES
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

[db2inst1@750_ora1a:/home/db2inst1]#>db2 get dbm cfg|grep PARA
Enable intra-partition parallelism     (INTRA_PARALLEL) = YES
[db2inst1@750_ora1a:/home/db2inst1]#>db2 get dbm cfg|grep DEGR
Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
[db2inst1@750_ora1a:/home/db2inst1]#>db2stop force
05/12/2011 17:14:57     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@750_ora1a:/home/db2inst1]#>db2start
05/12/2011 17:15:04     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@750_ora1a:/home/db2inst1]#>db2expln -d tpch -q "select count(*) from tpch.nation,tpch.region" -t

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = Yes (Bind Degree = ANY  )

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                   "DB2INST1"


Statement:
  
  select count(*)
  from tpch.nation, tpch.region

Intra-Partition Parallelism Degree = 48

Section Code Page = 1208

〇〇

原帖由 李玉明-421 于 2011-5-12 17:52 发表
哦，你需要设置下面的注册变量，然后重启数据库才有效。 你可以再试一下！

db2set DB2_OPTPROFILE=YES;
db2stop force;
db2start;

好了，谢谢

〇〇

原帖由 〇〇 于 2011-5-12 18:40 发表
为什么我启用了分区内并行，反而更慢了

468.879329 seconds 482.459168
  3.456943 seconds   4.283617
35.196633 seconds  49.522005
24.756155 seconds  25.135826
27.720584 seconds  30.382384
103.166410 seconds 100.084318
40.526446 seconds  47.550015
34.452728 seconds  40.439229
88.036224 seconds  87.582887
30.404406 seconds  36.627290
  6.502055 seconds   7.831795
38.336033 seconds  33.335061
49.450705 seconds  66.658736
32.759258 seconds  32.816498
36.062827 seconds  36.917765
  8.496194 seconds  19.588469
27.698119 seconds  23.122384
167.040236 seconds 234.650377
40.308232 seconds
587.439039 seconds
61.395391 seconds
60.756332 seconds

启用步骤
[db2inst1@750_ora1a:/home/db2inst1]#>db2batch -d tpch -f /arch/IBM/q01-22_f.txt > /tmp/q01-22_f_zip.log


[db2inst1@750_ora1a:/home/db2inst1]#>[db2inst1@750_ora1a:/home/db2inst1]#>[db2inst1@750_ora1a:/home/db2inst1]#>
[db2inst1@750_ora1a:/home/db2inst1]#>db2 get dbm cfg|grep PARA
Enable intra-partition parallelism     (INTRA_PARALLEL) = NO
[db2inst1@750_ora1a:/home/db2inst1]#>db2 update dbm cfg using INTRA_PARALLEL YES
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

[db2inst1@750_ora1a:/home/db2inst1]#>db2 get dbm cfg|grep PARA
Enable intra-partition parallelism     (INTRA_PARALLEL) = YES
[db2inst1@750_ora1a:/home/db2inst1]#>db2 get dbm cfg|grep DEGR
Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
[db2inst1@750_ora1a:/home/db2inst1]#>db2stop force
05/12/2011 17:14:57     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@750_ora1a:/home/db2inst1]#>db2start
05/12/2011 17:15:04     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@750_ora1a:/home/db2inst1]#>db2expln -d tpch -q "select count(*) from tpch.nation,tpch.region" -t

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = Yes (Bind Degree = ANY  )

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                   "DB2INST1"


Statement:
  
  select count(*)
  from tpch.nation, tpch.region

Intra-Partition Parallelism Degree = 48

Section Code Page = 1208

等了一晚上也不出结果，
用fore application all终止，发现结果已经输出，还挂在那里干什么？

〇〇

truncate后load,runstats
怎么查询还会出错？
[db2inst1@750_ora1a:/home/db2inst1]#>db2 truncate table tpch.lineitem
DB20000I  The SQL command completed successfully.
[db2inst1@750_ora1a:/home/db2inst1]#>
[db2inst1@750_ora1a:/home/db2inst1]#>
[db2inst1@750_ora1a:/home/db2inst1]#>date;db2 load from /arch/IBM/lineitem.tbl of del MODIFIED BY COLDELX7C MESSAGES /tmp/msgl2.tmp insert into tpch.lineitem ;date
Fri May 13 07:01:53 GMT+08:00 2011



Number of rows read         = 59986052
Number of rows skipped      = 0
Number of rows loaded       = 59986052
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 59986052

Fri May 13 07:13:18 GMT+08:00 2011
[db2inst1@750_ora1a:/home/db2inst1]#>[db2inst1@750_ora1a:/home/db2inst1]#>[db2inst1@750_ora1a:/home/db2inst1]#>
[db2inst1@750_ora1a:/home/db2inst1]#>time db2 "runstats on table tpch.lineitem WITH DISTRIBUTION AND INDEXES ALL"


DB20000I  The RUNSTATS command completed successfully.

real    5m52.43s
user    0m0.02s
sys     0m0.00s
[db2inst1@750_ora1a:/home/db2inst1]#>db2 "select count(*) from tpch.lineitem"

1         
-----------
SQL0668N  Operation not allowed for reason code "1" on table "TPCH.LINEITEM".  
SQLSTATE=57016

〇〇

google了一下
SQL0668N Operation not allowed for reason code "1" on table "<table-name>".
SQLSTATE=57016
This error occurs when the table is in the Check Pending state. The Check Pending state occurs when the integrity of the table is not enforced and the content of the table is invalid. This error can also occur when an operation on a parent table or an underlying table that is not in a Check Pending state, but its dependent tables are in a Check Pending state.

To bring the table out of the Check Pending state, do the following:

Type the following commands at the command prompt:
db2 connect to wcamart user user_id using password
db2 set integrity for table_name immediate checked

但是我还是不明白，他是指主外键？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从磁盘读出表数据到内存时，读到的都是压缩后的数据，所需I/O次数会比压缩前减少很多，节省了I/O时间；当然，数据到内存后需要解压缩并耗费cpu时间。压缩后是否变慢取决于多耗费的CPU时间是否大于节省的I/O时间。