Results 1 to 1 of 1
  1. #1
    Join Date
    Dec 2002
    Posts
    123

    Unanswered: Severe problem with stored procedures

    Hi,

    I'm having some serious issues when users try to access 3 stored procedures from our application...procedures 2 and 3 are called on one web page and procedure 1 is called on another page. Basically we have about 100-200 people log in to our app and they call the sprocs with their specific parameters... the last time the 3 sprocs were called resulted in db2sysc using up 100% of the CPU.. here's the basic layout of the procedures.. just by looking at the layout, can you tell me if you can see where the problem lies? Is it because I have a call to

    CALL COLLECT_TTD (parentId, parentType, startTime, endTime, interval, curtailId)
    in two of the procedures? Would that result in this infinite loop? or could it be something else? I don't see any problems when I execute them from the command line...thanks!



    1. First Sproc ---

    CREATE PROCEDURE NRG1.COLLECT_TTP ( IN parentId INTEGER, IN parentType INTEGER, IN startTime TIMESTAMP, IN endTime TIMESTAMP, IN interval INTEGER)
    RESULT SETS 1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    BEGIN

    DECLARE currentDate TIMESTAMP;
    DECLARE curtailId INTEGER DEFAULT 0;
    DECLARE selectStatement VARCHAR(1000);
    DECLARE selectTable VARCHAR(128) DEFAULT 'SESSION.TMPTLPVALUES';

    SET currentDate = CURRENT TIMESTAMP;

    IF iscurtailmentday (currentDate) > 0 THEN
    SET curtailId = getcurtid (currentDate);
    END IF;

    CALL COLLECT_TTD (parentId, parentType, startTime, endTime, interval, curtailId);

    --get sum of data from above only if parent <> 3 (3 = SITE)
    IF parentType <> 3 THEN
    CALL COLLECT_TOTAL(parentId, parentType, parentId, startTime, endTime, interval, selectTable, selectTable);
    END IF;

    SET selectStatement = 'SELECT parentId, parentType, id, readingVal, readingDateTime FROM SESSION.TmpTLPValues
    WHERE parentId = ? AND parentType = ? AND id = ?';

    PREPARE s1 FROM selectStatement;
    BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR s1;
    OPEN c1 USING parentId, parentType, parentId;
    END;
    END
    @



    2. Second procedure ---


    CREATE PROCEDURE NRG1.COLLECT_FVL ( IN parentId INTEGER, IN parentType INTEGER, IN startTime TIMESTAMP, IN endTime TIMESTAMP)
    RESULT SETS 1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL

    BEGIN
    DECLARE currentDate TIMESTAMP;
    DECLARE selectStatement VARCHAR(1000);
    DECLARE selectTmpStatement VARCHAR(1000);
    DECLARE insertStatement VARCHAR(1000);
    DECLARE curtailId INTEGER;
    DECLARE curtStartTime TIMESTAMP;
    DECLARE curtEndTime TIMESTAMP;
    DECLARE fslValue DOUBLE;
    DECLARE startInterval TIMESTAMP;
    DECLARE endInterval TIMESTAMP;
    DECLARE interval INTEGER DEFAULT 5; -- 5 = 1 HOUR

    SET currentDate = CURRENT TIMESTAMP;
    SET curtailId = getcurtid (currentDate);
    SET curtStartTime = getcurtstarttime (curtailId);
    SET curtEndTime = getcurtendtime (curtailId);

    CALL COLLECT_TTD(parentId, parentType, curtStartTime, curtEndTime, interval, curtailId);

    --get the highest hourly total
    SET selectStatement = 'SELECT SUM(readingVal) FROM SESSION.TMPTLPVALUES WHERE readingDateTime > ? AND readingDateTime <= ?
    GROUP BY HOUR(readingDateTime) ORDER BY SUM(readingVal) DESC FETCH FIRST ROW ONLY';

    PREPARE s1 FROM selectStatement;
    BEGIN
    DECLARE c1 CURSOR FOR s1;
    OPEN c1 USING curtStartTime, curtEndTime;
    FETCH c1 INTO fslValue;
    CLOSE c1;
    END;

    SET insertStatement = 'INSERT INTO SESSION.TMPTLPVALUES (PARENTID, PARENTTYPE, ID, READINGVAL, READINGDATETIME)'
    || ' VALUES ('|| CHAR(parentId) || ', ' || CHAR(parentType) || ', ' || CHAR(parentId) || ', ?, ? ' || ')';

    SET startInterval = startTime;
    SET endInterval = incr_stamp(startInterval, interval);

    PREPARE s2 FROM insertStatement;
    WHILE (endInterval <= endTime) DO
    BEGIN
    EXECUTE s2 USING fslValue, endInterval;

    SET startInterval = endInterval;
    SET endInterval = incr_stamp(endInterval, interval);
    END;
    END WHILE;

    SET selectTmpStatement = 'SELECT parentId, parentType, id, readingVal, readingDateTime FROM SESSION.TmpTLPValues
    WHERE parentId = ? AND parentType = ? AND id = ?';

    PREPARE s3 FROM selectTmpStatement;
    BEGIN
    DECLARE c3 CURSOR WITH RETURN FOR s3;
    OPEN c3 USING parentId, parentType, parentId;
    END;
    END
    @



    3. Third Stored Procedure ---


    CREATE PROCEDURE NRG1.COLLECT_CGL ( IN parentId INTEGER, IN parentType INTEGER, IN startTime TIMESTAMP, IN endTime TIMESTAMP)
    RESULT SETS 1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL

    BEGIN
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE errorCode INTEGER;
    DECLARE currentDate TIMESTAMP;
    DECLARE selectStatement VARCHAR(1000);
    DECLARE selectTmpStatement VARCHAR(1000);
    DECLARE curtailId INTEGER;
    DECLARE sid INTEGER;
    DECLARE interval INTEGER DEFAULT 5; -- 5 = 1 HOUR
    DECLARE hasNext INTEGER DEFAULT 1;
    DECLARE selectTable VARCHAR(128) DEFAULT 'READINGS';
    DECLARE insertTable VARCHAR(128) DEFAULT 'SESSION.TMPGROUPLOADVALUES';

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET hasNext = 0;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SET errorCode = SQLCODE;

    DECLARE GLOBAL TEMPORARY TABLE TMPGROUPLOADVALUES (
    PARENTID INTEGER,
    PARENTTYPE INTEGER,
    ID INTEGER,
    READINGVAL DOUBLE,
    READINGDATETIME TIMESTAMP
    ) NOT LOGGED IN USERTMP_TBLSP;

    --get all sites that are a part of current curtailment session that belong to ESP Energy Coalition
    SET selectStatement = 'SELECT siteId FROM Site WHERE isactive=''t'' AND parentid IN ( SELECT accountid FROM
    Account WHERE isactive=''t'' AND parentid = ? ) AND siteid IN ( SELECT siteid FROM
    SiteCurtailment WHERE curtid = ?) ORDER BY siteid';

    SET currentDate = CURRENT TIMESTAMP;
    SET curtailId = getcurtid (currentDate);

    PREPARE s1 FROM selectStatement;

    BEGIN
    DECLARE c1 CURSOR FOR s1;
    OPEN c1 USING parentId, curtailId;
    FETCH c1 INTO sid;

    WHILE (hasNext = 1) DO
    --collect sum of hourly readings for each site
    CALL COLLECT_TOTAL(parentId, parentType, sid, startTime, endTime, interval, selectTable, insertTable);
    FETCH c1 INTO sid;
    END WHILE;

    CLOSE c1;
    END;

    --get the total of all sitea
    CALL COLLECT_TOTAL(parentId, parentType, parentId, startTime, endTime, interval, insertTable, insertTable);

    SET selectTmpStatement = 'SELECT parentId, parentType, id, readingVal, readingDateTime FROM SESSION.TmpGroupLoadValues
    WHERE parentId = ? AND parentType = ? AND id = ?';

    PREPARE s2 FROM selectTmpStatement;
    BEGIN
    DECLARE c2 CURSOR WITH RETURN FOR s2;
    OPEN c2 USING parentId, parentType, parentId;
    END;
    END
    @
    Last edited by db2user; 09-10-08 at 12:11.

Posting Permissions

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