Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    Unanswered: Stored procedure HANGS..

    Hello all,

    I am writing a procedure as below but its getting hanged every time for unlimited time.

    Whats wrong in this .. can any one find it ?

    DB2 10.1 on RHEL 5




    SET SCHEMA EPROC~

    SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2INSTS" ~


    CREATE PROCEDURE EPROC.DSC_UPDATE_STATUS()
    SPECIFIC DSC_UPDATE_STATUS
    LANGUAGE SQL

    BEGIN ATOMIC
    DECLARE DIGITAL_CERT_INFO_FOUND INTEGER DEFAULT NULL; --
    DECLARE DIGITAL_CERT_INFO_FOUND_DE INTEGER DEFAULT NULL; --
    DECLARE SQLCODE INTEGER DEFAULT 0 ; --
    DECLARE USER_REF_ID_FIELD1 INTEGER; --
    DECLARE REF_ID_FIELD2 INTEGER; --
    DECLARE USER_TYPE_FIELD3 CHARACTER; --
    DECLARE DE_SERIAL_NO_FIELD4 VARCHAR(50); --

    DECLARE DIGITAL_CERT_INFO CURSOR FOR
    select
    USER_REF_ID,
    REF_ID,
    USER_TYPE
    from EP_DIGITAL_CERT_INFO
    where STATUS='a'
    and CERT_EXPIRY_TIME<current timestamp ;

    DECLARE DIGITAL_CERT_INFO_DE CURSOR FOR
    select
    USER_REF_ID,
    REF_ID,
    DE_SERIAL_NO
    from EP_DIGITAL_CERT_INFO
    where STATUS='a'
    and DE_EXPIRY_TIME<current timestamp ;


    OPEN DIGITAL_CERT_INFO; --
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DIGITAL_CERT_INFO_FOUND = SQLCODE; --
    SET DIGITAL_CERT_INFO_FOUND = 0; --
    FETCH FROM DIGITAL_CERT_INFO INTO
    USER_REF_ID_FIELD1,
    REF_ID_FIELD2,
    USER_TYPE_FIELD3; --
    END; --

    WHILE DIGITAL_CERT_INFO_FOUND = 0 DO

    update EP_DIGITAL_CERT_INFO set STATUS='e' where REF_ID= REF_ID_FIELD2 ; --
    IF USER_TYPE_FIELD3 = 'v' THEN
    update EP_VENDOR_MASTER set DSC_STATUS='n' where REF_ID= REF_ID_FIELD2 ; --
    else
    update EP_PM_USERS set DSC_STATUS='n' where USER_REF_ID= USER_REF_ID_FIELD1 ; --
    END IF ;
    FETCH FROM DIGITAL_CERT_INFO INTO USER_REF_ID_FIELD1,REF_ID_FIELD2,USER_TYPE_FIELD3;

    END WHILE ;--


    CLOSE DIGITAL_CERT_INFO ; --

    OPEN DIGITAL_CERT_INFO_DE; --
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DIGITAL_CERT_INFO_FOUND_DE = SQLCODE; --
    SET DIGITAL_CERT_INFO_FOUND_DE = 0; --
    FETCH FROM DIGITAL_CERT_INFO_DE INTO
    USER_REF_ID_FIELD1,
    REF_ID_FIELD2,
    DE_SERIAL_NO_FIELD4; --
    END; --

    WHILE DIGITAL_CERT_INFO_FOUND_DE = 0 DO

    update EP_DIGITAL_CERT_INFO set STATUS='e' where REF_ID= REF_ID_FIELD2 ; --
    update EP_ENCRYPTION_CERT_STORE
    set DE_STATUS='e'
    where USER_REF_ID= USER_REF_ID_FIELD1
    and DE_SERIAL_NO= DE_SERIAL_NO_FIELD4
    and DE_STATUS='a'
    and DE_EXPIRY_TIME<current timestamp ;
    FETCH FROM DIGITAL_CERT_INFO_DE INTO
    USER_REF_ID_FIELD1,
    REF_ID_FIELD2,
    DE_SERIAL_NO_FIELD4;
    END WHILE ;--





    CLOSE DIGITAL_CERT_INFO_DE ; --

    END ~


    thanks
    ssumit

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    something related to a specific condition with data or no data-found
    if problem can be reproduced activate/run debug mode and see what is happening
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Handlers might be not active within WHILE loops.

    IBM Knowledge Center | Compound SQL (compiled) statement

    Description

    ...
    ...
    ...
    ...


    handler-declaration
    ...
    ...

    A handler is said to be active for the duration of the execution of the set of SQL-procedure-statements that follow the set of handler-declarations within the compound statement in which the handler is declared, including any nested compound statements.

    ...
    Note: I marked Bold/Ialic

  4. #4
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Can it be due to same statement

    update EP_DIGITAL_CERT_INFO set STATUS='e' where REF_ID= REF_ID_FIELD2 ; --

    executed in in both both while loops one after the other. The value for "REF_ID_FIELD2" can be same for both the ceursors.

    This may cause the locks and thus hanging ?

    Any ideas..


    Thanks

    ssumit
    ssumit

  5. #5
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    modified proc body as:

    SET SCHEMA EPROC~

    SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","DB2INSTS" ~


    CREATE PROCEDURE EPROC.DSC_UPDATE_STATUS()
    SPECIFIC DSC_UPDATE_STATUS
    LANGUAGE SQL

    BEGIN ATOMIC
    DECLARE DIGITAL_CERT_INFO_FOUND INTEGER DEFAULT 0; --
    DECLARE DIGITAL_CERT_INFO_FOUND_DE INTEGER DEFAULT 0; --
    -- DECLARE SQLCODE INTEGER DEFAULT 0 ; --
    DECLARE USER_REF_ID_FIELD1 INTEGER; --
    DECLARE REF_ID_FIELD2 INTEGER; --
    DECLARE USER_TYPE_FIELD3 CHARACTER; --
    DECLARE DE_SERIAL_NO_FIELD4 VARCHAR(50); --

    DECLARE DIGITAL_CERT_INFO CURSOR FOR
    select
    USER_REF_ID,
    REF_ID,
    USER_TYPE
    from EP_DIGITAL_CERT_INFO
    where STATUS='a'
    and CERT_EXPIRY_TIME<current timestamp ;

    DECLARE DIGITAL_CERT_INFO_DE CURSOR FOR
    select
    USER_REF_ID,
    REF_ID,
    DE_SERIAL_NO
    from EP_DIGITAL_CERT_INFO
    where STATUS='a'
    and DE_EXPIRY_TIME<current timestamp ;


    OPEN DIGITAL_CERT_INFO; --
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DIGITAL_CERT_INFO_FOUND = 1;--
    END ; --
    WHILE DIGITAL_CERT_INFO_FOUND = 0 DO
    FETCH FROM DIGITAL_CERT_INFO INTO
    USER_REF_ID_FIELD1,
    REF_ID_FIELD2,
    USER_TYPE_FIELD3; --

    update EP_DIGITAL_CERT_INFO set STATUS='e' where REF_ID= REF_ID_FIELD2 ; --
    IF USER_TYPE_FIELD3 = 'v' THEN
    update EP_VENDOR_MASTER set DSC_STATUS='n' where REF_ID= REF_ID_FIELD2 ; --
    else
    update EP_PM_USERS set DSC_STATUS='n' where USER_REF_ID= USER_REF_ID_FIELD1 ; --
    END IF ;


    END WHILE ;--

    CLOSE DIGITAL_CERT_INFO ; --
    --

    OPEN DIGITAL_CERT_INFO_DE; --
    BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DIGITAL_CERT_INFO_FOUND_DE = 1; --
    END ; --
    WHILE DIGITAL_CERT_INFO_FOUND_DE = 0 DO
    FETCH FROM DIGITAL_CERT_INFO_DE INTO
    USER_REF_ID_FIELD1,
    REF_ID_FIELD2,
    DE_SERIAL_NO_FIELD4; --
    update EP_DIGITAL_CERT_INFO set STATUS='e' where REF_ID= REF_ID_FIELD2 ; --
    update EP_ENCRYPTION_CERT_STORE
    set DE_STATUS='e'
    where USER_REF_ID= USER_REF_ID_FIELD1
    and DE_SERIAL_NO= DE_SERIAL_NO_FIELD4
    and DE_STATUS='a'
    and DE_EXPIRY_TIME<current timestamp ;
    END WHILE ;--
    CLOSE DIGITAL_CERT_INFO_DE ; --



    END ~


    still the same problem.

    ssumit
    ssumit

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so : have you tried debug and looked at the result to see the problem ??
    or just waiting someone will supply a complete answer to resolve your problem ?
    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
    Sep 2013
    Location
    India
    Posts
    246
    currently i don't have any tool to debug the procedure. I am surprised why such a simple procedure is resulting a hang. I checked the data in table and handlers (As you said about some "unknown" conditions in the first reply)

    I am downloading data studio for debugging ..

    Meanwhile if you come across some clue, you are most welcome to post it.

    Thanks in advance.
    ssumit

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your condition handlers for "Not Found" are out of scope. They are only valid within the Begin-End block they reside in.

    Andy

  9. #9
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Thanks to all contributing in this thread..

    problem resolved.

    cause was as stated by ARWinner and tonkuma .
    ssumit

Posting Permissions

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