CLP for DB2 Newbie / Oracle expert

[lawarner]

I am creating a script to update the DB to our newest version. In Oracle / MSQL it was easy with a script run from SQL*Plus / Query Analyzer.

But I am having problems doing the same in DB2.

Our installations group uses CLP and I cannot seem to create a 'block' to loop through the existing records to update a value with the next highest number from another table.

THANKS

 

[blom0344]

Post what code you used with Oracle/SQL server and we will have an inkling what you are trying to achieve..

Ties Blom

 

[lawarner]

This is the code in Oracle. Of course it could be a cursor as well, which I would expect it to be for DB2. Basically I want to do the equivalent of an anonymous block and tie a bunch of statements together without having a stored procedure.

DECLARE
TYPE tbl_Name IS TABLE OF mx.customerlistdefinition.customerlistname%TYPE INDEX BY BINARY_INTEGER;
TYPE tbl_Run IS TABLE OF mx.customerlistdefinition.run%TYPE INDEX BY BINARY_INTEGER;
TYPE tbl_SX IS TABLE OF mx.customerlistdefinition.sx%TYPE INDEX BY BINARY_INTEGER;
t_Name tbl_Name;
t_Run tbl_Run;
t_SX tbl_SX;
v_CLX MX.SYSTEMPARAMETERS.VALUE%TYPE;
BEGIN
SELECT CustomerListName,
Run,
SX
BULK COLLECT INTO
t_Name,
t_run,
t_SX
FROM MX.CustomerListDefinition
WHERE NVL(Run,0) <= 0;
IF t_Name.COUNT > 0 THEN
FOR i IN 1 .. t_Name.LAST LOOP
SELECT Value INTO v_CLX FROM MX.SystemParameters WHERE Parameter='NXTCLX' FOR UPDATE ;
UPDATE MX.SystemParameters SET Value = v_CLX + 1 WHERE Parameter='NXTCLX';
COMMIT;
UPDATE MX.Customerlistdefinition
SET ListOutputID = v_CLX
WHERE CustomerListName = t_Name(i)
AND Run = t_Run(i)
AND SX = t_SX(i);
COMMIT;
END LOOP;
END IF;
END;