Hi , I have written this SP

CREATE PROCEDURE PRAGS.TESTOT ( )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare variable
DECLARE ENDTABLE INT DEFAULT 0;
DECLARE vitem_objid INT DEFAULT 0;
DECLARE num_rows BIGINT DEFAULT 0;
DECLARE vs_itemi CHAR(12);
DECLARE vs_idcng CHAR(12);

-- Declare cursor
DECLARE cursor1 CURSOR FOR
SELECT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM PRAGS.OBJECT_DATA AS OBJECT_DATA INNER JOIN PRAGS.PART_LIST AS PART_LIST
ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.S_DC='RD';

-- Declare handler
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET ENDTABLE = 1;

OPEN cursor1;

SET ENDTABLE = 0;
FETCH FROM cursor1 INTO vitem_objid, vs_itemi, vs_idcng;
WHILE ENDTABLE = 0 DO
SELECT COUNT(*) INTO num_rows FROM PRAGS.AVNTMITEM AVNTMITEM JOIN PRAGS.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN PRAGS.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC WHERE ITM_ID = vs_itemi AND EC_MC_ID = vs_idcng;

IF num_rows >= 1 THEN
UPDATE PRAGS.OBJECT_DATA SET Q_PMSYNC = 'Y' WHERE ITEM_OBJID = vitem_objid AND S_IDCNG = vs_idcng;
END IF;

FETCH FROM cursor1 INTO vitem_objid, vs_itemi, vs_idcng;
END WHILE;
CLOSE cursor1;
END P1


Now the first select query

"SELECT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM PRAGS.OBJECT_DATA AS OBJECT_DATA INNER JOIN PRAGS.PART_LIST AS PART_LIST
ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.S_DC='RD';"

will return about 2.5 million records.
How can i improve the performance or is there any way if i can process the records in batches?

Thanks
Prashant.

Your best bet for performance is to let DB2 do the work.

All you need is an update statement that uses the EXISTS clause.

Andy

Is thsi what you are talkinh about?
UPDATE PRAGS.OBJECT_DATA
SET Q_PMSYNC='Y'
WHERE EXISTS
(SELECT *
FROM (SELECT ITEM_OBJID AS ITEM_OBJID
, ITM_ID AS PN
, EC_MC_ID AS EC
FROM PRAGS.AVNTMITEM AVNTMITEM
JOIN PRAGS.AVENTACI AVENTACI
ON AVNTMITEM.OID = AVENTACI.OID_MITEM
JOIN PRAGS.AVNTEC AVNTEC
ON AVNTEC.OID = AVENTACI.OID_EC
JOIN PRAGS.PART_LIST PART_LIST
ON PART_LIST.S_ITEMI = AVNTMITEM.ITM_ID
) AS TEMP_PNEC
WHERE
OBJECT_DATA.ITEM_OBJID = TEMP_PNEC.ITEM_OBJID
AND OBJECT_DATA.S_IDCNG = TEMP_PNEC.EC
AND OBJECT_DATA.S_DC = 'RD'
)
;

Which will be better performance wise?
Is there any way i can still improve the SQL query for performance?

Thanks.

From you original post, you want to update every row of PRAGS.OBJECT_DATA (Q_PMSYNC='Y') when there is at least one row that in select * FROM PRAGS.AVNTMITEM AVNTMITEM JOIN PRAGS.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN PRAGS.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC WHERE ITM_ID = PRAGS.OBJECT_DATA.vs_itemi AND EC_MC_ID = PRAGS.OBJECT_DATA.vs_idcng;

This would be a simple update statement like this:

UPDATE PRAGS.OBJECT_DATA as O
SET Q_PMSYNC='Y'
WHERE EXISTS
(select 1 FROM PRAGS.AVNTMITEM AVNTMITEM JOIN PRAGS.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN PRAGS.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC WHERE ITM_ID = O.vs_itemi AND EC_MC_ID = O.vs_idcng)

If you have the proper indexes on the tables in the subselect, then this would be the optimal statement.

Andy

EXISTS will ignore the selected column list.
Note: This is documented on "DB2 Universal Database for OS/390 and z/OS SQL Reference Version 7 or later".
But, it is not ducumented explicitly on DB2 for LUW. I guessed that DB2 for LUW will also ignore the selected list.

So, I think it will be better to use "*".
Because, if specified constant, column or expression. I might consider the meaning of the selected list.
(Other people may have different opinion.)