when executing db2 -td@ -f./sql/update_product.sql

I m getting following message:

DB20000I The SET SERVEROUTPUT command completed successfully.

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N "SQLSTATE" is not valid in the context where it is used. LINE
NUMBER=53. SQLSTATE=42703



The sql file has :

SET SERVEROUTPUT ON
@
CREATE OR REPLACE PROCEDURE UPLOADPRODUCTATTRIBUTES
BEGIN
DECLARE v_eisidentifier VARCHAR(100);
DECLARE v_categoryGuid DECIMAL(19,0);
DECLARE v_localeGuid DECIMAL(19,0);
DECLARE v_realmGuid DECIMAL(19,0);
DECLARE v_catchingPhrase VARCHAR(120) ;
DECLARE v_genericName VARCHAR(80);
DECLARE v_ingredients VARCHAR(900);
DECLARE v_quantity VARCHAR (60);
DECLARE v_dose VARCHAR(40);
DECLARE v_nutritionFacts VARCHAR (900);
DECLARE v_productDescription VARCHAR(900);
DECLARE v_recommendedFor VARCHAR(200);
DECLARE v_promotionMessage VARCHAR(200);
DECLARE v_message VARCHAR (4500);
DECLARE v_proudctGuid DECIMAL(19,0);
DECLARE C1 CURSOR FOR
SELECT
EISIDENTIFIER,
CATEGORY_GUID,
LOCALEGUID,
REALMGUID,
CATCHING_PHRASE,
GENERIC_NAME,
INGREDIENTS,
QUANTITY,
DOSE,
NUTIRITION_FACTS,
PRODUCT_DESCRIPTION,
RECOMMENDED_FOR,
PROMOTION_MESSAGE,
MESSAGE
FROM
TEMP_UPLOAD_PRODUCT_ATTRIBUTES;

OPEN C1;
FETCH FROM C1 into v_eisidentifier,
v_categoryGuid,
v_localeGuid,
v_realmGuid,
v_catchingPhrase,
v_genericName,
v_ingredients,
v_quantity,
v_dose,
v_nutritionFacts,
v_productDescription,
v_recommendedFor,
v_promotionMessage,
v_message;

WHILE (SQLSTATE = '00000')
DO
IF EXISTS (SELECT 1 FROM PRD_PRODUCT WHERE EISIDENTIFIER = v_eisidentifier)
THEN
SELECT GUID INTO v_productGuid FROM PRD_PRODUCT WHERE EISIDENTIFIER = v_eisidentifier;

UPDATE PRD_PRODUCT_L10N
SET CATEGORY_GUID = v_categoryGuid,
REALMGUID = v_realmGuid,
CATCHING_PHRASE =v_catchingPhrase,
GENERIC_NAME =v_genericName,
INGREDIENTS =v_ingredients,
QUANTITY=v_quantity,
DOSE=v_dose,
NUTIRITION_FACTS=v_nutritionFacts,
PRODUCT_DESCRIPTION=v_productDescription,
RECOMMENDED_FOR=v_recommendedFor,
PROMOTION_MESSAGE=v_promotionMessage,
MESSAGE=v_message
WHERE PRODUCTGUID =v_proudctGuid and LOCALGUID =v_categoryGuid;

IF NOT EXISTS (SELECT 1 FROM PRD_PRODUCT_CATEGORY WHERE CATEGORY_GUID=v_categoryGuid AND PRODUCT_GUID=v_productGuid)
THEN
INSERT INTO PRD_PRODUCT_CATEGORY (CATEGORY_GUID, PRODUCT_GUID) VALUES (v_categoryGuid,v_productGuid);
ELSE
-- do nothing
END IF;
ELSE
-- INSERT INTO --- should not be the case....
END IF;


FETCH C1 into v_productGuid,
v_categoryGuid,
v_localeGuid,
v_realmGuid,
v_catchingPhrase,
v_genericName,
v_ingredients,
v_quantity,
v_dose,
v_nutritionFacts,
v_productDescription,
v_recommendedFor,
v_promotionMessage,
v_message
;
END WHILE;
CLOSE c1;
END
@

This works for me (not sure if there are other ways):

Add the following declare variables to your existing list:

DECLARE o_SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

Add the following after your Declare Cursors:
---------------------------------------------
-- Declare exit handlers
---------------------------------------------
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE INTO o_sqlstate
FROM sysibm.sysdummy1;

Then use 0_SQLSTATE instead of SQLSTATE in your loop.

I don't see any commit statements. Is that intentional? Depending on the number of inserts, you may want to do intermediate commits.

I thought that whole procedure body might be consentrated in an UPDATE(or MERGE) statement and an INSERT statement,
without using cursor and loop.

The procedure doesn't receive any parameter,
so the procedure might be not neccesary, if you executed the two statements directly by using db2 command.

Example 1: UPDATE statement.
Example 2: INSERT statement.