Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2010
    Posts
    23
    Provided Answers: 1

    Answered: Sudden insatiable demand for PCKCACHESZ

    Hi. This isn't as specific a question as I'd like, but it is what it is. I am looking for thoughts on where to look next.

    I'm running DB2 10.1 on Windows 7 (32 bit).

    The application is a complex Java server app in Weblogic 11g.

    It's a developer's environment, with everything running on one PC, but no other users to worry about.

    I'm running a complex action, which imports information from a file, and which will do several thousand inserts in several tables, including many LOB's, in a single transaction.

    I am getting this message:

    com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-973, SQLSTATE=57011, SQLERRMC=PCKCACHESZ, DRIVER=4.15.113

    My configuration had a default setting for PCKCACHESZ, Automatic 4096, I believe.

    I've gradually upped it - it is now set to 100000, and still I get that message.

    I do this kind of operation all the time, in the course of diagnosing customer issues. To be sure, no two packages I import are identical. This one seems notable for having a lot of small LOB writes (say, 62K records, each 30 bytes).

    So I guess my question is this: does it ring a bell with anybody?

    What could provoke such a sudden thirst for that type of cache?

    Thanks,
    Art

  2. Best Answer
    Posted by ArthurHGardner

    "After taking care of the side-issue (due to setting up a trigger with AFTER UPDATE), I find that the transaction leading to this thread is resolved. The solution is setting STMT_CONC to LITERALS.

    I am guessing that I can now dial back on my PCKCACHESZ.

    One more remark: I finally did get hold of a 64-bit platform on which to run this test, and the error did NOT happen, with the same input and STMT_CONC set OFF. So the person who remarked on my 32 bitness was right, too. "


  3. #2
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    As per IBM documentation, If PCKCACHESZ is set to AUTOMATIC (before change), all applications must disconnect from the DB2 in order for the change (new value) to take effect. Did you disconnect all connections and restart instance before doing further tests?

  4. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    this parameter has Propagation Class = immediate. So, it can be changed online.
    What's the result of the following query:
    Code:
    select VALUE, VALUE_FLAGS, DEFERRED_VALUE, DEFERRED_VALUE_FLAGS 
    from sysibmadm.dbcfg 
    where name='pckcachesz'
    Regards,
    Mark.

  5. #4
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Hi Mark,

    The documentation for 10.1 indicates that propagation is immediate. I might have looked at documentation for older version while posting. However, results of test are ambiguous. The change seems take effect immediate only if command is issued from session that is already connected to database. These are the results on our test server (10.5) after changing from automatic (-1) to 60000.

    Scenario 1:
    * Connected to DB2 from one session. Ran your query. Results ( value -1 , DEFERRED_VALUE -1). Left DB2 session open.
    * Changed configuration from second session WITHOUT CONNECTING TO DB2 using command "db2 update db cfg for sample using pckcachesz 60000"
    * Ran your query from first session again. Results ( value -1 , DEFERRED_VALUE 60000).


    Scenario 2:
    * Connected to DB2 from one session. Ran your query. Results ( value -1 , DEFERRED_VALUE -1). Left DB2 session open.
    * CONNECTED TO DB2 FROM SECOND SESSION. Changed configuration from second session using command "db2 update db cfg for sample using pckcachesz 60000"
    * Ran your query from first session again. Results ( value 60000 , DEFERRED_VALUE 60000)

    Satya...

  6. #5
    Join Date
    Oct 2010
    Posts
    23
    Provided Answers: 1
    Thanks for your reply, Satya. After each change, I stopped the only client (Weblogic), then issued db2stop and db2start. Does that seem like enough to you?

  7. #6
    Join Date
    Oct 2010
    Posts
    23
    Provided Answers: 1
    Missed Mark's questions. That query gives me 0 record(s) selected.

    But get db cfg for MYTABLE show detail > myfile.txt shows this:

    Package cache size (4KB) (PCKCACHESZ) = 115000 115000

    One more thing: my last test did not give me a DB2 message, but a transaction timeout (currently set to 20 minutes) in Weblogic. So now I must extend that, and retry.
    Last edited by ArthurHGardner; 10-27-15 at 10:41.

  8. #7
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Satya,

    There is nothing ambiguous in your tests.
    To change a db parameter immediately (for parameters supporting this functionality of course) you have to connect to this database first.
    Your changes will be deferred if you don't have a database connection.
    DB2 worked here as designed.
    Regards,
    Mark.

  9. #8
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Mark,

    Thanks for the info.

    Art,

    If you have used db2stop and db2start, then new values should be in effect irrespective of how update cfg command is issued and other active connections.

    The value for PCKCACHESZ in db configuration is the maximum value allowed. DB2 might assign less memory for package cache depending on availability. Both DB2 and Weblogic are running on single 32-bit machine, there might be issue with amount of memory available for DB2. Check db2diag.log for memory related errors.

    Satya...

  10. #9
    Join Date
    Oct 2010
    Posts
    23
    Provided Answers: 1
    Thanks, all. I am at the maximum for this parameter, still no joy. I don't think I am out of memory.

    On a hunch, I tried STMT_CONC = LITERALS, which has greatly accelerated certain related transactions. My transaction, however, took a new exception, due to the presence of a trigger, which will require a new issue.

    If I end up resolving the original issue, I will post that information.

  11. #10
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You might want to flush the dynamic cache after changing STMT_CONC to literals, or changing PCKCACHESZ (with the immediate option).
    Nobody will take you seriously if you persist with a 32-bit DB2-server on Windoze in year 2015.
    Just not necessary to inflict such sadism, don't you know.
    (Earlier hacks like /3g, or (pre V10) num_estore_seg/estore_seg_sz/DB2_AWE were temporary solutions).

  12. #11
    Join Date
    Oct 2010
    Posts
    23
    Provided Answers: 1
    I may have to live with that. My company will ship me a more up-to-date computer to me when it's good and ready. Yes, we have servers, but sometimes it's convenient to test locally. Appreciate your concern though.

  13. #12
    Join Date
    Oct 2010
    Posts
    23
    Provided Answers: 1
    After taking care of the side-issue (due to setting up a trigger with AFTER UPDATE), I find that the transaction leading to this thread is resolved. The solution is setting STMT_CONC to LITERALS.

    I am guessing that I can now dial back on my PCKCACHESZ.

    One more remark: I finally did get hold of a 64-bit platform on which to run this test, and the error did NOT happen, with the same input and STMT_CONC set OFF. So the person who remarked on my 32 bitness was right, too.
    Last edited by ArthurHGardner; 11-03-15 at 12:43.

Posting Permissions

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