Hi, when I issue snapshot for locks command, I get the following error:

Tue Sep 14 11:37:57 HKG 2004
SQL0973N Not enough storage is available in the "QUERY_HEAP" heap to process
the statement. SQLSTATE=57011

Then I tried to increase the value of QUERY_HEAP_SZ from 1000 to 5000, but still get this error.

What is the meaning of SQL0973N error? How can I know what value of QUERY_HEAP_SZ is large enough? Are there any adverse effect on setting this parameter too large?

Thanks a lot!

Was the instance recycled to ensure that it took effect?
Check by issuing:
db2 attach to instancename
db2 get dbm cfg show detail | grep -i query

to see what the current and delayed values are for QUERY_HEAP_SZ .

HTH,
Ruby

I am using AIX. I follow the step but get nothing after issuing: db2 get dbm cfg show detail | grep -i query

Also, if I use W2k, how can I get the current and delayed setting?

Did you first issue:
db2 attach to instancename (whatever the instancename is) prior to issuing the db2 get dbm cfg ... ?

yes, I issued. I get dbm cfg by issuing: db2 get dbm cfg
but get nothing by issuing: db2 get dbm cfg for detail | grep -i query

will the result dump to screen or to file? I am not familiar with unix commands

Thanks for your help.

One must issue" after the db2 attach ..
db2 get dbm cfg show detail | grep -i query
(it's show detail and not for detail).
Before I responded to you I had issued the above commands which produced the correct and desired results.
So, my guess is that something is missing when you're doing it.

Hi the following is what I get from a telnet session:



telnet (ibmp615)


























AIX Version 5
(C) Copyrights by IBM and by others 1982, 2000.
login: setdb2
setdb2's Password:
************************************************** *****************************
* *
* *
* Welcome to AIX Version 5.1! *
* *
* *
* Please see the README file in /usr/lpp/bos for information pertinent to *
* this release of the AIX Operating System. *
* *
* *
************************************************** *****************************
Last unsuccessful login: Fri Sep 10 10:29:24 TAIST 2004 on /dev/pts/3 from 192.168.33.82
Last login: Tue Sep 21 09:45:01 TAIST 2004 on

[YOU HAVE NEW MAIL]

Database Connection Information

Database server = DB2/6000 7.2.5
SQL authorization ID = SETDB2
Local database alias = SETDB

DB20000I The SQL command completed successfully.
setdb2@ibmp615:/home/setdb2 > db2 attach to db2inst1

Instance Attachment Information

Instance server = DB2/6000 7.2.5
Authorization ID = SETDB2
Local instance alias = DB2INST1

setdb2@ibmp615:/home/setdb2 > db2 get dbm cfg show detail | grep -i query
setdb2@ibmp615:/home/setdb2 >

the command return nothing.

Hi,
your db2 command is wrong,not "db2 get dbm cfg show detail"
the right is "db2 get dbm cfg|grep -i query"

Gentlemen:
See below:
insgipdm@paehowup2641[/home/insgipdm/x127355]>db2 get dbm cfg show detail | grep -i query
Query heap size (4KB) (QUERY_HEAP_SZ) = 1000 1000
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY ANY

The 1st value is the current value and the 2nd value is the delayed value.
By the way, I just noticed that you are running UDB 7.2.5 and I was giving you info for a UDB 8.1.5 instance.

HTH,
Ruby

Thanks Metatron and rubystep.

I tried the command without 'show detail' and can get something.
I only see 1 value after the parameter name. Is it the current value?

[setinst@ssmsset01]/db2home/setinst>db2 get dbm cfg|grep -i query
Query heap size (4KB) (QUERY_HEAP_SZ) = 5000
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY

How can I query the current value and the delayed value of regestry parameter db2_rr_to_rs?

db2_rr_to_rs is one db2 registry parameter .
you can use command :"db2set",to see its value(if you have already set that parameter.)

However, I know this parameter will take effect ONLY AFTER instance restart, but I am not sure if the instance is already restarted.