Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    17

    Unanswered: Stored procedure times out.

    I developed a stored procedure that does the following
    • Reads a table (around 3000 records) which has 2 columns - ID and SQLST (this contains a SQL statement
    • Reads each record into a cursor, prepares the SQL statement and executes it.
    • Results are loaded into a TMP table and a cursor is left open for client call
    • It is functioning as expected when I try to limit the rows fetch to a max of 750. When I remove the fetch clause and if it reads the whole table, it processes almost 2 min and then times out with SQLCODE = -431
    • I also used a counter to commit the results every time it the counter value is a multiple of 50.


    Are there any option that I explore for this issue? Thank you.

    Satish

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Stored procedures don't time out by themselves. If you check the error description for SQL0431N you'll see that "User defined function has been interrupted by the user." Most likely the client software that you use to start the procedure is the source of the timeout.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Code:
    SQL0431N  User defined function "<function-name>" (specific name
          "<specific-name>") has been interrupted by the user.
    
    Explanation:
    
    A user/client interrupt has occurred while the named UDF was in control.
    
    User response:
    
    This could indicate some problem in the UDF, such as an infinite loop or
    wait. If the problem persists, (that is, the need to interrupt results
    in the same error condition), then contact the author of the UDF or your
    database administrator. Until the problem is fixed, the UDF should not
    be used.
    
     sqlcode: -431
    
     sqlstate: 38504
    I do not think it timed out on the database end.

    Andy

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by vskr72 View Post
    I developed a stored procedure that does the following
    • Reads a table (around 3000 records) which has 2 columns - ID and SQLST (this contains a SQL statement
    • Reads each record into a cursor, prepares the SQL statement and executes it.
    • Results are loaded into a TMP table and a cursor is left open for client call
    • It is functioning as expected when I try to limit the rows fetch to a max of 750. When I remove the fetch clause and if it reads the whole table, it processes almost 2 min and then times out with SQLCODE = -431
    • I also used a counter to commit the results every time it the counter value is a multiple of 50.


    Are there any option that I explore for this issue? Thank you.

    Satish
    For TimeOut has to be sqlcode = -905, how I know...
    In time when you create function or stored procedure you shown the CPU time limit to SP:
    ASUTIME LIMIT nnnnnnn (or NOLIMIT). Maybe you have NOLIMIT.

    Can you shown your query to us ?

    Lenny
    Last edited by Lenny77; 04-13-12 at 17:55.

  5. #5
    Join Date
    Apr 2012
    Posts
    17
    The code is attached. Appreciate your comments.
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by vskr72 View Post
    The code is attached. Appreciate your comments.
    DYNAMIC RESULT SETS 1

    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT CPG_ID, SQL_SELECT FROM Test_Table

    DECLARE CURSOR2 cursor with return for V_DYNAMIC;

    DECLARE GTTCURS CURSOR WITH RETURN FOR
    select a.sql_id, a.sql_text, a.cnt, a.sql_code
    , timestampdiff(1,char(a.end_ts-a.start_ts)) as time_taken
    from session.Rslt a;

    REPEAT

    SET o_Tsql='select count(*) from ('||v_Tsql||')';
    SET v_Start_TS = CURRENT TIMESTAMP;
    PREPARE V_DYNAMIC from o_Tsql;
    SET v_End_TS = CURRENT TIMESTAMP;

    OPEN cursor2;
    fetch cursor2 into v_Cnt;
    insert into session.Rslt values(v_Tid, v_Tsql, v_Cnt, v_SQLCODE
    , v_Start_TS, v_End_TS);
    SET v_Cnt=0;
    CLOSE cursor2;
    SET v_SQLCODE=0;
    Fetch cursor1 into v_Tid, v_Tsql;

    UNTIL v_exitcode = 1
    END REPEAT ;
    1. cursor1 has not to have WITH RETURN option
    2. cursor2 has not to have WITH RETURN option
    3. You have to DROP the session.Rslt table before DECLARE

    Lenny

Posting Permissions

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