SQL error message when creating procedure containing three cursors

85 pts.
Tags:
DB2 SQL
SQL
I have created a procedure containing 3 cursor. It gives me the below error and when I add @delimiter ++; at start and end it gives message that "command processed" but doesn't show output. Any suggestions?
[Error Code: -104, SQL State: 42601]  DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=mrec;ame


Software/Hardware used:
I am using DBvisualizer
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • rockstar1
    ohhkey thank you
    85 pointsBadges:
    report
  • AjitK29
    Can you paste your code here?
    2,545 pointsBadges:
    report
  • rockstar1
    ya sure...i will paste it tomorrow

    85 pointsBadges:
    report
  • rockstar1
    Here is my code.Still it gives error.Any suggestions plz,

    CREATE PROCEDURE P()

    LANGUAGE SQL

    BEGIN

    -- EXEC SQL

    DECLARE C1 CURSOR FOR

         SELECT M."Server_Name",M."Timestamp",AVG(M."Memory_Usage_Percentage")as "AVG_Memory_Usage_Percentage",MAX(M."Memory_Usage_Percentage")as        "MAX_Memory_Usage_Percentage"
         FROM ITMUSER."NT_Memory_64" M
         where INT(SUBSTR(M."Timestamp", 2, 2)) =15 AND INT(SUBSTR(M."Timestamp", 4, 2)) =10
         Group by M."Server_Name",M."Timestamp"

    mrec C1%ROWTYPE
    OPEN C1
    LOOP

        FETCH C1 INTO mrec
        EXIT WHEN C1%NOTFOUND
        DECLARE C2 CURSOR FOR     
         SELECT AVG(C."%_User_Time") as "AVG_CPU_Usage",MAX(C."%_User_Time") as "MAX_CPU_Usage"
             From ITMUSER."NT_Processor" C
             Where C."Server_Name" = mrec.M."Server_Name" AND C."Timestamp"=mrec.M."Timestamp"
        
        crec C2%ROWTYPE 
        OPEN C2
        LOOP

            FETCH C2 into crec
            EXIT WHEN C2%NOTFOUND     
            DECLARE C3 CURSOR FOR     
                 SELECT AVG(P."%_Usage") as "AVG_Paging"
                 FROM ITMUSER."NT_Paging_File" P
                    Where P."Server_Name"=crec.C."Server_Name" AND P."Timestamp"=crec.C."Timestamp"

                prec C2%ROWTYPE
            OPEN C3
            LOOP
                FETCH C3 into prec
                EXIT WHEN C3%NOTFOUND
                  DBMS_OUTPUT.PUT_LINE(mrec.M."Server_Name" || mrec.M."Timestamp" || mrec.M."Memory_Usage_Percentage"||mrec.M."Memory_Usage_Percentage" || crec.C."%_User_Time" || crec.C."%_User_Time" || prec.P."%_Usage" ||)
         
            END LOOP
            CLOSE C3

        END LOOP
        CLOSE C2

    END LOOP
    CLOSE C1

    ENDAdd your reply...
    85 pointsBadges:
    report
  • AjitK29
    Did you test the individual statements before composing the stored procedure?
    2,545 pointsBadges:
    report
  • rockstar1
    ya ..I did.but its giving error in first place only.
    See I have attached it below.

    /-----------------------------------------------------------------------------------/
    CREATE OR REPLACE PROCEDURE P()

    LANGUAGE SQL;
    BEGIN
    -- EXEC SQL
    DECLARE CURSOR C1 for
         SELECT M."Server_Name",M."Timestamp",AVG(M."Memory_Usage_Percentage")as "AVG_Memory_Usage_Percentage",MAX(M."Memory_Usage_Percentage")as "MAX_Memory_Usage_Percentage"
         FROM ITMUSER."NT_Memory_64" M
         where INT(SUBSTR(M."Timestamp", 2, 2)) =15 AND INT(SUBSTR(M."Timestamp", 4, 2)) =10
         Group by M."Server_Name",M."Timestamp";
    mrec C1%TYPE;
    OPEN C1;
    LOOP
        FETCH C1 INTO mrec;
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE( mrec.M."Server_Name" || mrec.M."Timestamp" || mrec.M."Memory_Usage_Percentage" || mrec.M."Memory_Usage_Percentage" );
    END LOOP;
    CLOSE C1;
    END;

    CALL P();
    /--------------------------------------------------------------------/
    It gives error like this =

    [Error Code: -104, SQL State: 42601]  DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;RE P()
    LANGUAGE SQL;<space>, DRIVER=4.13.127
    I am stuck here only from 3 days.
    85 pointsBadges:
    report
  • AjitK29
    Refer this thread. Hope it will help.
    http://www.dbvis.com/forum/message.jspa?messageID=15420
    2,545 pointsBadges:
    report
  • rockstar1
    ya..thank you

    85 pointsBadges:
    report
  • rockstar1
    hey ...can anyone help me with one query.
    I want to print output of same stored procedure mentioned above  using DBMS_OUTPUT.PUT_LINE,but Its giving error in DB2 Dbvisualizer.I have also tried using SET SERVEROUTPUT ON,but didnt worked out.Any suggestion plz.
    85 pointsBadges:
    report
  • carlosdl
    DBMS_OUTPUT is an Oracle package, and SET SERVEROUTPUT ON is an Oracle's SQL*Plus command.

    I don't think they are available on DB2.
    84,805 pointsBadges:
    report
  • pgrozev
    You should run yourself a DBMON for the job. Once you have the output file, you could do a select to it and go to COL QQI8 for SQL errors encountered. You might find something else occurring prior to or after the SQL -104. Also, I'd run it for both cases where you use/don't use @delimiter ++. Double check your SQL codes with the DB Performance and Query optimization Redbook. You could find more details regarding the errors you get. I'll look into the query to see if there are any issues with it's composition.
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: