Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    58

    Unanswered: Help with script syntax please

    The following DB2 commands execute ok in a .sql file run with
    the following command "db2 +c -td@ -sf bridge_040.sql" but do not
    produce the results I need:

    BEGIN ATOMIC
    FOR V AS
    SELECT (SELECT NEXTID FROM MSTRDATA.COUNTERS WHERE COUNTER='foo') AS DEVICEID,
    DRIVEID, DRIVENUMBER, PVID, BOOTFLAG, TEAMID, IOCOUNT FROM MSTRESRC.TEMPIODATA DO
    IF V.IOCOUNT = 0 THEN
    INSERT INTO MSTRESRC.FOOBAR (DEVICEID, DRIVEID, DRIVENUMBER, PVID, BOOTFLAG, TEAMID)
    VALUES(V.DEVICEID, V.DRIVEID, V.DRIVENUMBER, V.PVID, V.BOOTFLAG, V.TEAMID) ;
    UPDATE MSTRDATA.COUNTERS SET NEXTID=V.DEVICEID+1 WHERE COUNTER='foo' ;
    END IF;
    END FOR;
    END@


    Since it is an ATOMIC block, the counter never gets updated. If I
    remove the word ATOMIC, I can't get past syntax errors. It does
    not need to be run atomically as I intend to shutdown the web
    application while applying this bridge, but the DB2 documentation
    is not correct as it says I dont need the word ATOMIC.

  2. #2
    Join Date
    Aug 2003
    Posts
    58
    After poking around looking at other threads I discovered the secret
    inrediant I needed was the syntax for variables. Working solution below:

    BEGIN ATOMIC
    DECLARE IDCOUNT INTEGER ;
    SET IDCOUNT = 0 ;
    FOR V AS
    SELECT (SELECT NEXTID FROM MSTRDATA.COUNTERS WHERE COUNTER='foo') AS DEVICEID,
    DRIVEID, DRIVENUMBER, PVID, BOOTFLAG, TEAMID, IOCOUNT FROM MSTRESRC.TEMPIODATA DO
    IF V.IOCOUNT = 0 THEN
    INSERT INTO MSTRESRC.FOOBAR (DEVICEID, DRIVEID, DRIVENUMBER, PVID, BOOTFLAG, TEAMID)
    VALUES(V.DEVICEID+IDCOUNT, V.DRIVEID, V.DRIVENUMBER, V.PVID, V.BOOTFLAG, V.TEAMID) ;
    SET IDCOUNT = IDCOUNT + 1 ;
    UPDATE MSTRDATA.COUNTERS SET NEXTID=V.DEVICEID+IDCOUNT WHERE COUNTER='foo' ;
    END IF;
    END FOR;
    END@

Posting Permissions

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