Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    10

    Unanswered: Blob free lob locator sp, adodb, sql0954c

    Hi,

    Have the following SP

    CREATE OR REPLACE PROCEDURE TEST ()
    DYNAMIC RESULT SETS 1
    BEGIN
    DECLARE MYCUR01 CURSOR WITH RETURN TO CLIENT FOR SELECT BLOB, M_KEY FROM "BOB1" OPTIMIZE FOR 1 ROWS FOR READ ONLY;
    OPEN MYCUR01;
    END@

    I use ADODB provider "IBMDADB2.1;OLE DB Services = -4" to read data from DB2.

    I can open this SP and loop through recordset - fine. My problem starts with the BLOB field.
    Using server-side cursor and reading many, many rows, the application-memory for this session grows in DB2 until no memory is left, at all.
    --> SQL0954C SQLSTATE=57011

    I cannot increase APPLHEAPSZ to open-end.

    I just want to "read and forget" the blobs, I don't want to come back. I think DB2 keeps all internal lob locators from SP open until I do a commit.

    Is there a way to call "FREE LOB locator" during my loop? How would I do that?


    Using client cursor is impossible - the client memory is too low.

    Some more code:

    pRstSrcData->Open(_variant_t(FormatString(_T("TEST"))),
    _variant_t((IDispatch*)pCPE->pConnection), ADODB::adOpenForwardOnly, ADODB::adLockReadOnly, ADODB::adCmdStoredProc);
    while (!(pRstSrcData->EndOfFile))
    {
    _variant_t _rows_data_ = pRstSrcData->GetRows(x);
    ... do something here ...
    }

    Or is it impossible, when using GetRows()?

    C:\>db2level
    DB21085I Instanz "DB2" verwendet "32" Bit und DB2-Codefreigabe "SQL09075" mit Aktualitõts-ID "08060107".
    Informationstoken: "DB2 v9.7.500.4299", "special_27924", "IP23286_27924" und
    FixPak "5".
    Produkt ist in "C:\Programme\IBM\SQLLIB" mit DB2-Kopienamen "DB2COPY1" installiert.

    Thank you all.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't know if it's going to solve your problem (a LOB locator does not take much memory, because it's just that - a locator, a pointer), but a COMMIT invalidates LOB locators, as far as I know.

    If I were you, I'd define the cursor to fetch M_KEY only, then fetch individual BLOBs by key in a separate statement that would release its resources after each execution.

  3. #3
    Join Date
    Nov 2009
    Posts
    10
    I can test it, I think it would work. But how about performance/overhead? It will require multiple requests - speaking about 850k or more rows.

    Should I SELECT the blob one-by-one by m_key, or is it safe to use RID_BIT, maybe some kind of WHERE IN ('x,','y','z')?

    850K*4 = 3.4MB, thus the lob locators are not a problem, then the whole blobs stay in memory of DB2 until I do a commit!

    Is there no way to release _all_ blob resources during my loop? I use a forward only cursor, why will not the memory be released?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I wouldn't use multiple statements.

    Maybe there is some problem in your application not releasing the LOBs properly, which keeps the locators open? A few years ago, I run across some problem and you could update your CLI config with PATCH2=50, which causes DB2 to release the locators on the next SQLFetch() call: Patch2 CLI/ODBC configuration keyword

    Another alternative could be to open the cursor as WITH HOLD and then commit between all the FETCH operations.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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