Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41

    Question Unanswered: SQL0973N when get snapshot for locks

    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!

  2. #2
    Join Date
    Sep 2003
    Posts
    85
    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

  3. #3
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    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?

  4. #4
    Join Date
    Sep 2003
    Posts
    85
    Did you first issue:
    db2 attach to instancename (whatever the instancename is) prior to issuing the db2 get dbm cfg ... ?

  5. #5
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    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.

  6. #6
    Join Date
    Sep 2003
    Posts
    85
    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.

  7. #7
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    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.

  8. #8
    Join Date
    Feb 2004
    Posts
    21
    Quote Originally Posted by rubystep
    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
    Hi,
    your db2 command is wrong,not "db2 get dbm cfg show detail"
    the right is "db2 get dbm cfg|grep -i query"

  9. #9
    Join Date
    Sep 2003
    Posts
    85
    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

  10. #10
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    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?

  11. #11
    Join Date
    Feb 2004
    Posts
    21

    Smile

    Quote Originally Posted by jmychung
    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.)

  12. #12
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    However, I know this parameter will take effect ONLY AFTER instance restart, but I am not sure if the instance is already restarted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •