By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,312 Members | 2,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,312 IT Pros & Developers. It's quick & easy.

DB2 trigger failing while calling stored procedure

P: n/a
I have the following trigger that calls a DB2 stored procedure:

DROP TRIGGER GGWU.TRI_A_MULTI_PROP@
CREATE TRIGGER GGWU.TRI_A_MULTI_PROP AFTER INSERT ON
GGWU.MULTIPLIER_PROPERTY REFERENCING NEW AS POST FOR EACH ROW MODE
DB2SQL
BEGIN ATOMIC
CALL
GGWU.PKG_MULT_PROP_INSERT(POST.C_PROPERTY_CODE,POS T.I_MULTIPLIER_ID);
END@

When I insert a row into the GGWU.MULTIPLIER_PROPERTY table I get the
following error:
SQL30020N Execution of the command or SQL statement failed because of
a
syntax error in the communication data stream that will affect the
successful
execution of subsequent commands and SQL statements: Reason Code
"0x124C"("011D")"". SQLSTATE=58009
And then disconnects me from the database.

However if I drop the trigger, insert the row, then call the stored
proc with the same data, it functions normally. Any ideas? I can put
the body of the stored procedure into the trigger if necessary (this
is the only place the stored proc is called) however it gives me the
error that the create cursor statements are invalid. Below is the
stored procedure.
Here is the stored procedure:
CREATE PROCEDURE GGWU.PKG_MULT_PROP_INSERT(
IN p_propertyCode CHAR(3),
IN p_multiplierId BIGINT
)
LANGUAGE SQL
MODIFIES SQL DATA
P1: BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE v_notFound INT DEFAULT 0;

DECLARE v_promotionId BIGINT;
DECLARE v_gameType CHAR(1);
DECLARE v_creditType CHAR(1);
DECLARE v_qualifyType CHAR(1);
DECLARE v_factor DECIMAL(8,2);

DECLARE v_multiplierDatesId BIGINT;
DECLARE v_startDate DATE;
DECLARE v_endDate DATE;
DECLARE v_monday SMALLINT;
DECLARE v_tuesday SMALLINT;
DECLARE v_wednesday SMALLINT;
DECLARE v_thursday SMALLINT;
DECLARE v_friday SMALLINT;
DECLARE v_saturday SMALLINT;
DECLARE v_sunday SMALLINT;

DECLARE v_success SMALLINT;
DECLARE v_sqlState CHAR(5);
DECLARE v_createRecord SMALLINT;
DECLARE v_currentDate DATE;
DECLARE v_exceptions SMALLINT;
DECLARE v_sql VARCHAR(500);
DECLARE v_temp CHAR(5);

DECLARE curDates CURSOR WITH HOLD FOR
SELECT I_MULTIPLIER_DATES_ID, D_START, D_END, B_MONDAY, B_TUESDAY,
B_WEDNESDAY, B_THURSDAY, B_FRIDAY, B_SATURDAY, B_SUNDAY FROM
GGWU.MULTIPLIER_DATES WHERE I_MULTIPLIER_ID = p_multiplierId FOR
READ ONLY WITH UR;

DECLARE curException CURSOR WITH HOLD FOR SELECT
I_MULTIPLIER_DATES_ID, D_EXCEPTION FROM
GGWU.MULTIPLIER_DATES_EXCEPTION
WHERE I_MULTIPLIER_ID = p_multiplierId FOR READ ONLY WITH UR;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
END;

DECLARE CONTINUE HANDLER FOR NOT_FOUND
SET v_notFound = 1;

SELECT M.I_PROMOTION_ID, M.C_GAME_TYPE, M.C_CREDIT_TYPE,
M.C_QUALIFY_TYPE, M.F_FACTOR
INTO v_promotionId, v_gameType, v_creditType, v_qualifyType,
v_factor
FROM GGWU.MULTIPLIER M WHERE M.I_MULTIPLIER_ID = p_multiplierId WITH
UR;
OPEN curDates;

FETCH curDates INTO v_multiplierDatesId, v_startDate, v_endDate,
v_monday, v_tuesday, v_wednesday, v_thursday, v_friday, v_saturday,
v_sunday;

WHILE (v_notFound = 0) DO
IF (v_startDate = v_endDate) THEN
CALL GGWU.PKG_INS_MULT_CHILD(
v_multiplierDatesId,
p_multiplierId,
v_promotionId,
p_propertyCode,
v_startDate,
v_factor,
v_qualifyType,
v_gameType,
v_creditType,
v_success,
v_sqlState
);
ELSE
SET v_currentDate = v_startDate;
WHILE (v_currentDate <= v_endDate) DO
SET v_createRecord = CASE DAYOFWEEK(v_currentDate)
WHEN 1 THEN v_sunday
WHEN 2 THEN v_monday
WHEN 3 THEN v_tuesday
WHEN 4 THEN v_wednesday
WHEN 4 THEN v_thursday
WHEN 5 THEN v_friday
WHEN 6 THEN v_saturday
WHEN 7 THEN v_sunday
ELSE 0
END;
IF (v_createRecord = 1) THEN
CALL GGWU.PKG_INS_MULT_CHILD(
v_multiplierDatesId,
p_multiplierId,
v_promotionId,
p_propertyCode,
v_currentDate,
v_factor,
v_qualifyType,
v_gameType,
v_creditType,
v_success,
v_sqlState
);
END IF;
SET v_currentDate = v_currentDate + 1 DAY;
END WHILE;
END IF;

FETCH curDates INTO v_multiplierDatesId, v_startDate, v_endDate,
v_monday, v_tuesday, v_wednesday, v_thursday, v_friday, v_saturday,
v_sunday;
END WHILE;

CLOSE curDates;

SET v_notFound = 0;

OPEN curException;

FETCH curException INTO v_multiplierDatesId, v_currentDate;

WHILE (v_notFound = 0) DO
DELETE FROM GGWU.MULTIPLIER_CHILD WHERE
I_MULTIPLIER_DATES_ID = v_multiplierDatesId AND
D_CASINO_DATE = v_currentDate;

FETCH curException INTO v_multiplierDatesId, v_currentDate;
END WHILE;

CLOSE curException;

COMMIT;

END P1

Jun 28 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Bizarre, I recommend a PMR. Maybe a bug.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 29 '07 #2

P: n/a
Did you use federated tables in the procedure called from
trigger(GGWU.PKG_MULT_PROP_INSERT) or another procedure called by the
procedure(GGWU.PKG_INS_MULT_CHILD)?

Jun 29 '07 #3

P: n/a
On Jun 29, 8:05 am, Tonkuma <tonk...@jp.ibm.comwrote:
Did you use federated tables in the procedure called from
trigger(GGWU.PKG_MULT_PROP_INSERT) or another procedure called by the
procedure(GGWU.PKG_INS_MULT_CHILD)?
Here is the body of PKG_INS_MULT_CHILD

CREATE PROCEDURE GGWU.PKG_INS_MULT_CHILD ( IN p_promotionId INTEGER,
IN p_multiplierId INTEGER,
IN p_multDatesId BIGINT,
IN p_propertyCode
CHARACTER(3),
IN p_casinoDate DATE,
IN p_factor DECIMAL(8,2),
IN p_qualifyType
CHARACTER(1),
IN p_gameType CHARACTER(2),
IN p_creditType
CHARACTER(1) ,
OUT p_success SMALLINT,
OUT p_sqlState VARCHAR(5),
OUT p_inserted SMALLINT)
LANGUAGE SQL
MODIFIES SQL DATA
------------------------------------------------------------------------
-- SQL Stored Procedure
-- p_promotionId
-- p_multiplierId
-- p_multDatesId
-- p_propertyCode
-- p_casinoDate
-- p_factor
-- p_qualifyType
-- p_gameType
-- p_creditType
------------------------------------------------------------------------
P1: BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE v_notFound INT DEFAULT 0;
DECLARE v_inserted SMALLINT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE v_temp CHAR(5);
SET v_temp = SQLSTATE;
ROLLBACK;
SET p_sqlState = v_temp;
END;

DECLARE CONTINUE HANDLER FOR NOT_FOUND
SET v_notFound = 1;

SET p_success = 0;
SET v_notFound = 0;

IF (p_gameType IS NULL) THEN

IF (p_creditType IS NULL) THEN
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'T',
'B'
);
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'S',
'B'
);
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'O',
'B'
);

INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'S',
'N'
);

INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'T',
'N'
);

INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'O',
'N'
);
SET v_inserted = 6;
ELSE
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'T',
p_creditType
);

INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'S',
p_creditType
);

INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'O',
p_creditType
);
SET v_inserted = 3;
END IF;
ELSE

IF (LOCATE('T',p_gameType) 0) THEN
IF (p_creditType IS NULL) THEN
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE ,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'T',
'B'
);
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE ,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'T',
'N'
);
SET v_inserted = v_inserted + 2;
ELSE
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE ,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'T',
p_creditType
);
SET v_inserted = v_inserted + 1;
END IF;
END IF;
IF (LOCATE('O',p_gameType) 0) THEN
IF (p_creditType IS NULL) THEN
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE ,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'O',
'B'
);
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE ,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'O',
'N'
);
SET v_inserted = v_inserted + 2;
ELSE
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE ,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'O',
p_creditType
);
SET v_inserted = v_inserted + 1;
END IF;
END IF;
IF (LOCATE('S',p_gameType) 0) THEN
IF (p_creditType IS NULL) THEN
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE ,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'S',
'B'
);
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE ,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'S',
'N'
);
SET v_inserted = v_inserted + 2;
ELSE
INSERT INTO GGWU.MULTIPLIER_CHILD (
I_PROMOTION_ID,
I_MULTIPLIER_ID,
I_MULTIPLIER_DATES_ID,
C_PROPERTY_CODE ,
D_CASINO_DATE,
F_FACTOR,
C_QUALIFY_TYPE,
C_GAME_TYPE,
C_CREDIT_TYPE)
VALUES (
p_promotionId,
p_multiplierId,
p_multDatesId,
p_propertyCode,
p_casinoDate,
p_factor,
p_qualifyType,
'S',
p_creditType
);
SET v_inserted = v_inserted + 1;
END IF;
END IF;
END IF;

SET p_inserted = v_inserted;
SET p_success = 1;
END P1

Jun 29 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.