案例：db2 connect to <dbname> 无法连接数据库

fjmingyang

我上面没表达清楚,是这样的,我也碰到同样的问题
$ db2level
DB21085I  Instance "db2ocrm" uses "32" bits and DB2 code release "SQL08016"
with level identifier "02070106".
Informational tokens are "DB2 v8.1.1.58", "s040914", "U800265", and FixPak "6".
Product is installed at "/usr/opt/db2_08_01".
$ oslevel
5.3.0.0

$ db2 connect to ocrmdb
DB21015E  The Command Line Processor backend process request queue or input
queue was not created within the timeout period.
----------------------------------------
db2diag.log的错误如下：
2009-01-14-19.31.10.791380   Instance:db2ocrm   Node:000
PID:708826(db2)   TID:1   Appid:none
command line process  clp_start_bp Probe:3
CLP frontend unable to get REQUEST queue handle
0x2FF2086C : 0x870F0042                                 ...B
-----------------------------
按照你的方法,我也作了db2trc on -t -f db2trc.dmp,可是db2trc.dmp 文件有2.9G ,我接下来该如何追踪下去呢，我的db2diag.log并没有你案例中提到的2170这样的值
由于db2trc.dmp太大， db2trc flw -t db2trc.dmp db2trc.flw 产生的db2trc.flw也很大，估计得有2G，用vi根本没法打开，用grep main db2trc.flw 也没有发现包含你案例中提到的clp_bp_main 段落，
另外我用db2trc flw -t -p 708826 db2trc.dmp db2trc.flw 挑选出错connect process的flw，供你参考

wangzhonnew

how long did you turn on the trace?
you should do it at maint hour so that minimum database activities are running, because db2trc is instance level, if there are other database is busy to do something, the output file could be huge.

also write the trace/connect into a same script so that the connect command is fired right after trace is turned on....

wangzhonnew

also the trace you did is the front-end process only
1324029     22639053:574643268   | | clp_start_bp entry
1324047     22639053:574649175   | | | sqloexec entry
1324052     22639053:574650040   | | | sqloexec data [probe 10]
1325251     22639053:575974172   | | | sqloexec data [probe 30]
1325252     22639053:576206785   | | | sqloexec data [probe 30]
1325276     22639053:583974162   | | | | sqlowchd entry
1325279     22639053:583974776   | | | | sqlowchd data [probe 10]
1326982     22639053:584696290   | | | sqloexec data [probe 50]
1326983     22639053:584864144   | | | sqloexec data [probe 50]
1328109     22639053:585478161   | | | | sqlowchd data [probe 20]
1328112     22639053:585479383   | | | | sqlowchd data [probe 40]
1328115     22639053:585479973   | | | | sqlowchd exit
1328118     22639053:585480451   | | | sqloexec data [probe 40]
1328178     22639053:585503769   | | | sqloexec data [probe 42]
1328232     22639053:585524455   | | | sqloexec data [probe 110]
1328234     22639053:585525604   | | | sqloexec exit
1328267     22639053:585537964   | | | clp_get_qhandle entry
1328290     22639053:585546687   | | | | sqloOpenMLNQue entry
1328295     22639053:585547807   | | | | sqloOpenMLNQue data [probe 1]
1328344     22639053:585568126   | | | | | sqlogkey entry
1328346     22639053:585568585   | | | | | sqlogkey data [probe 1]
1328389     22639053:585588213   | | | | | sqlogkey exit [rc = 0x63657388 = 1667593096]
1328416     22639053:585599723   | | | | sqloOpenMLNQue exit [rc = 0x870F0042 = -2029060030 = SQLO_QUE_NOT_EXIST]
1328419     22639053:585600809   | | | | sqlorest entry
1328420     22639053:585601157   | | | | sqlorest data [probe 10]
1958718     22639054:585699697   | | | | sqlorest exit
1958743     22639054:586286802   | | | | sqloOpenMLNQue entry
1958749     22639054:586288647   | | | | sqloOpenMLNQue data [probe 1]
...
16589367    22639117:411538109   | | | | sqloOpenMLNQue entry
16589374    22639117:411540326   | | | | sqloOpenMLNQue data [probe 1]
16589383    22639117:411546981   | | | | | sqlogkey entry
16589384    22639117:411547310   | | | | | sqlogkey data [probe 1]
16589398    22639117:411554931   | | | | | sqlogkey exit [rc = 0x63657388 = 1667593096]
16589414    22639117:411564060   | | | | sqloOpenMLNQue exit [rc = 0x870F0042 = -2029060030 = SQLO_QUE_NOT_EXIST]
16589416    22639117:411564673   | | | | sqlorest entry
16589417    22639117:411565277   | | | | sqlorest data [probe 10]
16783315    22639118:411598358   | | | | sqlorest exit
16783337    22639118:411608370   | | | | sqloOpenMLNQue entry
16783339    22639118:411610800   | | | | sqloOpenMLNQue data [probe 1]
16783354    22639118:411616610   | | | | | sqlogkey entry
16783357    22639118:411617199   | | | | | sqlogkey data [probe 1]
16783375    22639118:411625714   | | | | | sqlogkey exit [rc = 0x63657388 = 1667593096]
16783402    22639118:411636436   | | | | sqloOpenMLNQue exit [rc = 0x870F0042 = -2029060030 = SQLO_QUE_NOT_EXIST]
16783407    22639118:411637581   | | | clp_get_qhandle exit [rc = 0x870F0042 = -2029060030 = SQLO_QUE_NOT_EXIST]
16783413    22639118:411639436   | | clp_start_bp error [probe 2]

that means the trace only tells "okay, we got SQLO_QUE_NOT_EXIST error" because the queue that communicate between front-end and back-end process is not exist, but it doesn't tell us WHY~~~

you should reproduce the problem and capture the trace again in non-business hour and send the full trace file...
it doesn't help much without the full picture of trace file

zhangzongjun

原帖由 fjmingyang 于 2009-1-14 20:01 发表
我上面没表达清楚,是这样的,我也碰到同样的问题
$ db2level
DB21085I  Instance "db2ocrm" uses "32" bits and DB2 code release "SQL08016"
with level identifier "02070106".
Informational tokens are "DB2 v8.1.1.58", "s040914", "U800265", and FixPak "6".
Product is installed at "/usr/opt/db2_08_01".
$ oslevel
5.3.0.0

$ db2 connect to ocrmdb
DB21015E  The Command Line Processor backend process request queue or input
queue was not created within the timeout period.
----------------------------------------
db2diag.log的错误如下：
2009-01-14-19.31.10.791380   Instance:db2ocrm   Node:000
PID:708826(db2)   TID:1   Appid:none
command line process  clp_start_bp Probe:3
CLP frontend unable to get REQUEST queue handle
0x2FF2086C : 0x870F0042                                 ...B
-----------------------------
按照你的方法,我也作了db2trc on -t -f db2trc.dmp,可是db2trc.dmp 文件有2.9G ,我接下来该如何追踪下去呢，我的db2diag.log并没有你案例中提到的2170这样的值
由于db2trc.dmp太大， db2trc flw -t db2trc.dmp db2trc.flw 产生的db2trc.flw也很大，估计得有2G，用vi根本没法打开，用grep main db2trc.flw 也没有发现包含你案例中提到的clp_bp_main 段落，
另外我用db2trc flw -t -p 708826 db2trc.dmp db2trc.flw 挑选出错connect process的flw，供你参考

多长时间就生成了2G多的文件？

fjmingyang

db2trc on -t -f db2trc.dmp
db2 connect to testdb user testuser using testuser
db2trc off

根据狼建议的那样 我把这3个命令放在一个shell scripts中执行，同时我断开了大部分连接只剩3各连接，生成的trace文件还是有650M ,晕

wangzhonnew

possible to do it when there's no user connections?
because trace a query will take HUGE amount of space because the optimizer codepath is VERY VERY complicate and will write tons of information into trace, so that making trace file grow like a monster

and btw do you see the connect timeout EVERY TIME for a specific database? or it happen randomly to one database? or it happen to random database?
is it a new system or existing system? is the problem happened before? if not happened before what has been changed?

can you give a very detail problem description and envionrment/background description.

fjmingyang

这个数据库是搭建有几个月时间了，这个connect timeout 问题只发生在这个数据库所在机器上，并且每次connect都发生，之前并未发生过该问题，  
另外在其他机器上是能正常db2 connect这个数据库的

这台服务器是否做过什么变动目前不清楚(还未问过该服务器的管理员)。
你需要什么样的envionrment/background description呢？

另外该服务器还没有性能问题（大概问题是压力测试时20个并发的话，CPU使用率很高90%，没有特别耗cpu的process,从topas看单个db2agntp只有4%，打算关闭intra_parallel再测看看）,，详细情况明天回公司开新贴请教该性能问题

wangzhonnew

how many databases exist on this instance?
if possible i'd still like the trace file being captured without any other application connected to any databases in the instance.

what platform it is? and is it 32 or 64 bit instance?
is it possible to try to catalog the database as a loop-back connection and see what's going on? (loop-back connection means catalog the localhost server as a tcpip node, and catalog the database as a remote database and try to connect)

fjmingyang

环境如下
平台aix 5.3    db2 8.1.6
     db2inst1      db2inst2
       32bit             64bit
       db2inst1实例下并没有数据库，编目(不是loop-back方式)指向db2inst2实例下的DB
问题发生在db2inst1这个用户下（输入db2 就发生time out, 还没开始做connect db 动作） ，其他用户用同一个编目却没有这种情况
附件是 我断开了所有连接下做的db2trc

wangzhonnew

原帖由 fjmingyang 于 2009-1-15 19:04 发表
环境如下
平台aix 5.3    db2 8.1.6
     db2inst1      db2inst2
       32bit             64bit
       db2inst1实例下并没有数据库，编目(不是loop-back方式)指向db2inst2实例下的DB
问题发生在db2inst1这个用户下（输入db2 就发生time out, 还没开始做connect db 动作） ，其他用户用同一个编目却没有这种情况
附件是 我断开了所有连接下做的db2trc

got fmt file?
what do you mean by db2inst1实例下并没有数据库，编目(不是loop-back方式)指向db2inst2实例下的DB??? how did you catalog the database?