Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: stored procedure taking too much time to complete

    Hi all,
    DB2 10.1 ESE on Windows.

    I have two tables

    CREATE TABLE SYSREPORTS(
    actId INTEGER NOT NULL,
    propId INTEGER NOT NULL,
    sysId INTEGER NOT NULL,
    failed SMALLINT NOT NULL,
    description VARCHAR(8000),
    rnum BIGINT NOT NULL,
    CONSTRAINT pk_sysreports PRIMARY KEY ( actId, propId, sysId, failed )
    );

    CREATE TABLE SMART_PCS(
    id INTEGER NOT NULL,
    );

    Then I have a procedure:

    create PROCEDURE querySystems (
    )
    LANGUAGE SQL
    BEGIN
    declare rc integer;
    select count(*) into rc FROM SMART_PCS C
    JOIN SYSREPORTS S ON S.sysId= C.id
    WHERE S.actId = 1 AND S.propId = 1 AND S.sysId = 1 for read only;
    return rc;

    END


    Both tables have 200K rows.

    From clp:

    TRY 1
    --------
    db2stop
    db2start
    db2 "select count(*) into rc FROM SMART_PCS C
    JOIN SYSREPORTS S ON S.sysId= C.id
    WHERE S.actId = 1 AND S.propId = 1 AND S.sysId = 1 for read only"

    --> The output is shown immediately (151000)

    TRY 2
    -------
    db2stop
    db2start
    db2 call querySystems ()

    --> same output is shown after almost one hour!!!!

    I cannot understand what is the issue here, could you please help me?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Mar 2012
    Posts
    120
    Thanks for your answer, could you please explain what you think is the issue and which package i should rebind?

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as you don't have the problem with dynamic sql
    rebind the sp to supply the current access path to the sp based on current objects (index..stats)
    look at the syntax diagram in previous link and notice the sp name can be supplied
    http://publib.boulder.ibm.com/infoce.../r0009863.html
    Last edited by przytula_guy; 02-12-13 at 08:21.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Mar 2012
    Posts
    120
    Hi,
    to check dynamic SQL I created the stored procedure like

    create PROCEDURE querySystems (
    p_actId integer,
    p_propId integer,
    p_sysId integer
    )
    LANGUAGE SQL
    BEGIN
    declare rc integer;
    select count(*) into rc FROM SMART_PCS C
    JOIN SYSREPORTS S ON S.sysId= C.id
    WHERE S.actId = p_actId AND S.propId = p_propId AND S.sysId = p_sysId for read only;
    return rc;

    END

    and run it as
    db2 call querySystems (1,1,1)

    I get same result (very long time).

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    please read some doc about binding and packages and sp
    http://publib.boulder.ibm.com/infoce.../c0011922.html
    the last topic : Keep the DB2 optimizer informed
    if dynamic sql is being used in sp you will see a prep statement....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Robert1973 View Post
    Thanks for your answer, could you please explain what you think is the issue and which package i should rebind?
    I think the issue is the outdated package, which was bound with old statistics, and you need to rebind the package that belongs to your stored procedure.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Mar 2012
    Posts
    120
    Thanks!
    Rebinding the package was good for that procedure, now it takes just 2 seconds.

    Then, I noticed that if any procedure returns a varchar(8000) field, it still takes long time.

    Keeping with the example above, if I define the procedure like

    create PROCEDURE querySystems (
    p_actId integer,
    p_propId integer,
    p_sysId integer
    )
    LANGUAGE SQL
    BEGIN
    declare c1 cursor with return for
    select S.sysid, S.description FROM SMART_PCS C
    JOIN SYSREPORTS S ON S.sysId= C.id
    WHERE S.actId = p_actId AND S.propId = p_propId AND S.sysId = p_sysId for read only;
    open c1;

    END

    also rebinding the package it takes 300 seconds!!!

    Any idea of what is the issue here?

Posting Permissions

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