DB2 - L

 View Only
  • 1.  [Db2 z/OS] How Db2 uses workfile tablespaces for a query

    Posted Mar 21, 2022 10:38 AM

    Does anyone know where I should be looking, for a deep dive on how Db2 handles externalizing sort and data pages in the bufferpools (and how it reports such things – SMF 101?)?

     

    One of our SQL operations failed, last week, when it encountered an SMS "Unable to extend tablespace" message.  Db2 reported that the temp workfile was "full," and rolled back the SQL. 

     

    I cannot, from the message, tell whether _all_ of the tablespaces backing BP4K01 were full, or whether only one was full.  I'm hoping there's a discussion about what DB2 does (and hopefully how to look at the numbers; what processes are locking how many pages, and maybe _where_ they are locking pages (what workdatabase tablespaces!).

     

     

     

     

    Philip Sevetson

    Computer Systems Manager

    FISA-OPA

    5 Manhattan West

    New York, NY 10001

    psevetson@fisa-opa.nyc.gov

    917-991-7052 m

    212-857-1659 f

    image001.png@01D261E4.BE68E970

     

    **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**


  • 2.  RE: [Db2 z/OS] How Db2 uses workfile tablespaces for a query

    Posted Mar 21, 2022 10:24 PM
    A DGTT can only be stored in a single workfile tablespace. When that tablespace is full, your
    application starts getting -904 errors. (Think what happens if it is in two tablespaces and you
    have an index - in which tablespace does a RID value point to.)

    If a tablespace used for a, e.g., sort workfile, gets full, you get the "cannot extend" message
    and Db2 switches to a new tablespace of the same page size. It is only when there are no
    more usable tablespaces that the application gets -904 errors.

    Note use of WFDBSEP.

    https://www.ibm.com/docs/en/db2-for-zos/12?topic=session-dsntip9-work-file-database-panel
    has more details.

    BP4K01 looks like part of the name of a tablespace - rather than a bufferpool.

    Apparently there will be modifications in Vnext in the use of workfile tablespaces. But no
    details. Yet.


    James Campbell


    On 21 Mar 2022 at 14:37, Philip Sevetson via International DB2 Use wrote:

    > Does anyone know where I should be looking, for a deep dive on how Db2 handles externalizing sort and data pages in the bufferpools (and how it reports such things - SMF 101?)?
    >
    > One of our SQL operations failed, last week, when it encountered an SMS "Unable to extend tablespace" message. Db2 reported that the temp workfile was "full," and rolled back the SQL.
    >
    > I cannot, from the message, tell whether _all_ of the tablespaces backing BP4K01 were full, or whether only one was full. I'm hoping there's a discussion about what DB2 does (and hopefully how to look at the numbers; what processes are locking how many pages, and maybe _where_ they are locking pages (what workdatabase tablespaces!).
    >
    >
    >
    >
    > Philip Sevetson




  • 3.  RE: [Db2 z/OS] How Db2 uses workfile tablespaces for a query

    Posted Mar 22, 2022 02:00 AM
      |   view attached
    Here's some slides all about Work file usage that I put together as a "single place to find all the info"

    If anyone has any other info I will be more than happy to update and add stuff!

    I especially like the graphic on the last page from IBMer Peter Hartmann that visually shows the huge differences between PBG and non-PBG work file assignment.

    These were extracted from my "Are you happy or are you still deprecated?" by the way

    ------------------------------
    Roy Boxwell
    Software Engineering GmbH
    ------------------------------

    Attachment(s)

    pdf
    Workfile usage.pdf   334 KB 1 version


  • 4.  RE: [Db2 z/OS] How Db2 uses workfile tablespaces for a query

    Posted Mar 22, 2022 04:44 PM
    An IDUG presentation by Adrian Collett (EU16A03):
    https://www.idug.org/HigherLogic/System/DownloadDocumentFile.ashx?DocumentFileKey=3
    ae8c332-954f-4df2-a225-8764e777d794&forceDialog=0

    James Campbell

    On 22 Mar 2022 at 6:00, Roy Boxwell via International DB2 Users G wrote:

    > Here's some slides all about Work file usage that I put together as a "single place to find all the info"
    >
    > If anyone has any other info I will be more than happy to update and add stuff!
    >
    > I especially like the graphic on the last page from IBMer Peter Hartmann that visually shows the huge differences between PBG and non-PBG work file assignment.
    >
    > These were extracted from my "Are you happy or are you still deprecated?" by the way
    >
    > ------------------------------
    > Roy Boxwell
    > Software Engineering GmbH
    > ------------------------------
    > -------------------------------------------
    > Original Message:
    > Sent: Mar 21, 2022 10:37 AM
    > From: Philip Sevetson
    > Subject: [Db2 z/OS] How Db2 uses workfile tablespaces for a query
    >
    > Does anyone know where I should be looking, for a deep dive on how Db2 handles externalizing sort and data pages in the bufferpools (and how it reports such things - SMF 101?)?
    >
    > One of our SQL operations failed, last week, when it encountered an SMS "Unable to extend tablespace" message. Db2 reported that the temp workfile was "full," and rolled back the SQL.
    >
    > I cannot, from the message, tell whether _all_ of the tablespaces backing BP4K01 were full, or whether only one was full. I'm hoping there's a discussion about what DB2 does (and hopefully how to look at the numbers; what processes are locking how many pages, and maybe _where_ they are locking pages (what workdatabase tablespaces!).
    >
    >
    >
    >
    > Philip Sevetson




  • 5.  RE: [Db2 z/OS] How Db2 uses workfile tablespaces for a query

    Posted Mar 23, 2022 02:56 AM
    Yep equally very useful, but what on earth happened to that PDF ? The fonts are all over the place and some of the slides are completely illegible... Must ask Adrian for a "fresh" copy!


    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 6.  RE: [Db2 z/OS] How Db2 uses workfile tablespaces for a query

    Posted Mar 23, 2022 04:33 AM
      |   view attached
    Yep you're right Roy...shocking 😱  don't know how that happened.

    Anyway, here is the original.

    HTH

    Ciao4now!

    ------------------------------
    AdrianCollettExpertise4IT s.r.l.
    ------------------------------

    Attachment(s)

    pdf
    EU16A03.pdf   7.86 MB 1 version


  • 7.  RE: [Db2 z/OS] How Db2 uses workfile tablespaces for a query

    Posted Mar 23, 2022 05:00 AM
    Cheers!

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-



    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 8.  RE: [Db2 z/OS] How Db2 uses workfile tablespaces for a query

    Posted Mar 23, 2022 07:29 AM

    Adrian,

     

    I just stumbled across the messed-up copy at the IDUG files myself, resulting from  a Google search.  TYVM for that fixed version.

     

    /phil

     

     

    Philip Sevetson

    Computer Systems Manager

    FISA-OPA

    5 Manhattan West

    New York, NY 10001

    psevetson@fisa-opa.nyc.gov

    917-991-7052 m

    212-857-1659 f

    image001.png@01D261E4.BE68E970