例如,DB2服务器是V9.76,那么就需要从db2的安装包/opt/IBM/db2/V9.7/java下面复制db2jcc.jar和db2jcc_license_cu.jar到WAS的JDBC库路径下面。否则会由于JDBC驱动不同版本的兼容问题,会出现一些不可预见的问题,这个一定要注意。
调整后的:
update db cfg using MAXLOCKS 80;
update db cfg using LOCKLIST 40960;
update db cfg using LOCKTIMEOUT 30;
update db cfg using CATALOGCACHE_SZ 102400;
update db cfg using LOGBUFSZ 10240;
update db cfg using PCKCACHESZ 102400;
update db cfg using sheapthres_shr 1638400;
update dbm cfg using SHEAPTHRES 0;
update db cfg using sortheap 819200;
update db cfg using STMT_CONC LITERALS;
update dbm cfg using DIAGSIZE 1024;
调整前的:
Percent. of lock lists per application (MAXLOCKS) = 80
Max storage for lock list (4KB) (LOCKLIST) = 10240
Lock timeout (sec) (LOCKTIMEOUT) = -1
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 3072
Log buffer size (4KB) (LOGBUFSZ) = 2048
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(2400)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 1638400
Sort heap threshold (4KB) (SHEAPTHRES) = 40000
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(4096)
Statement concentrator (STMT_CONC) = OFF
当多表关联时,优化器选择哈希连接,由于字段类型不匹配导致查询性能非常低,随着目标表数据量的变大,查询的性能问题逐渐暴露出来。
解决办法是,从设计上调整表设计,将关联字段调整为同一类型,如果是字符串,长度也要求完全一致。
下面这个例子就是一个典型,将关联条件 T1.pk_invbasdoc=T2.vgoodscode字段类型调整为一致后,查询性能提高了10倍:
CREATE TABLE
T1
(
PK_INVBASDOC VARCHAR(20),
...
)
CREATE TABLE
T2
(
...
VGOODSCODE CHARACTER(20),
)
SELECT
...
FROM
T1,
T2
WHERE
T1.pk_invbasdoc=T2.vgoodscode
使用ROOT账户 ,执行chown -R db2inst1:db2grp1 /home/db2inst1 命令后,随后使用命令su - db2inst1切入实例用户账户下,这时执行db2set、db2ilist等命令,会报如下错误:
$ db2set
DBI1306N The instance profile is not defined.
Explanation:
The instance is not defined in the target machine registry.
User response:
Specify an existing instance name or create the required instance.
这时连接DB2失败,用命令行连接db2,也会报如下错误:
$ db2 connect to mydb user db2inst1 using db2inst1
SQL30082N Security processing failed with reason "42" ("ROOT CAPABILITY
REQUIRED"). SQLSTATE=08001
查询出错信息,如下所示,但是在db2 V9.76下面根本没有db2rfe命令:
$ db2 ?SQL30082N
To enable local client or server authentication for non-root
installations, the system administrator must run the db2rfe
script
解决办法是,进入root账号,重新刷新实例的profile,故障消失:
# ./db2iupdt db2inst1
本来修改HP-UX内核参数是根据:db2osconf的建议值来设置:
******************* System Resources ***********************
Physical RAM ==> 137259171840
Number of CPUS ==> 16
System Scale Factor ==> 8192.00
* Recommended HP-UX Kernel Parameters Based on this System *
nproc ==> 8208
maxuprc ==> 2736
max_thread_proc ==> 32767
maxfiles ==> 4096
maxfiles_lim ==> 65535
nflocks ==> 16384
ninode ==> 11469
msgmnb ==> 65535
msgtql ==> 6554
msgmni ==> 6554
semmns ==> 16385
semmni ==> 8192
semmnu ==> 8192
shmmax ==> 123533254656
shmmni ==> 8192
maxdsiz_64bit ==> 1900000000
这些参数都已经按建议值修改过,但是还报错。按下面内核值修改,再次执行load,问题解决。
msgmnb (max # of bytes on a message queue, i.e max-msg-qbytes in Solaris 10+) = 2097152
msgmni (max # of message queues, i.e max-msg-ids in Solaris 10+) = 32767
msgtql (max # of message on all message queues, i.e max-msg-messages in Solaris 10+) = 262144
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25714482/viewspace-758379/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25714482/viewspace-758379/