Result set from ADMIN_CMD

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Gregor Kovač
    #1

    Result set from ADMIN_CMD

    Hi!

    I'm trying to use ADMIN_CMD procedure in my SQL procedure like this:
    DROP PROCEDURE IMPORT_DATA#
    CREATE PROCEDURE IMPORT_DATA()
    SPECIFIC IMPORT_DATA
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE DATA_REPL_ON_ST ATUS VARCHAR(10);
    DECLARE ROWS_READ INTEGER;
    DECLARE ROWS_SKIPPED INTEGER;
    DECLARE ROWS_INSERTED INTEGER;
    DECLARE ROWS_UPDATED INTEGER;
    DECLARE ROWS_REJECTED INTEGER;
    DECLARE ROWS_COMMITTED INTEGER;
    DECLARE MSG_RETRIEVAL VARCHAR(500);
    DECLARE MSG_REMOVAL VARCHAR(500);
    DECLARE result1 RESULT_SET_LOCA TOR VARYING;

    SELECT STATUS INTO DATA_REPL_ON_ST ATUS FROM PARAMS WHERE CODE
    = 'DATA_REPL_ON' WITH UR;

    IF (DATA_REPL_ON_S TATUS = '0') THEN
    UPDATE PARAMS SET STATUS = '1' WHERE CODE = 'DATA_REPL_ON';

    CALL SYSPROC.ADMIN_C MD('IMPORT FROM /home/gregor/TABLE1.IXF OF IXF
    MESSAGES ON SERVER INSERT_UPDATE INTO EMGSYS.TABLE1') ;
    ASSOCIATE RESULT SET LOCATORS(result 1) WITH PROCEDURE
    SYSPROC.ADMIN_C MD;
    ALLOCATE rsCur CURSOR FOR RESULT SET result1;
    FETCH rsCur INTO ROWS_READ, ROWS_SKIPPED, ROWS_INSERTED,
    ROWS_UPDATED, ROWS_REJECTED, ROWS_COMMITTED, MSG_RETRIEVAL, MSG_REMOVAL;

    UPDATE PARAMS SET STATUS = '0' WHERE CODE = 'DATA_REPL_ON';
    END IF;
    END

    When I run this I get:
    SQL20397W Routine "SYSPROC.ADMIN_ CMD" execution has completed, but at least
    one error, "SQL3015", was encountered during the execution. More information
    is available.

    In db2diag.log I get:
    SQL0774N The statement cannot be executed within an ATOMIC compound
    SQL statement.

    I must use BEGIN ATOMIC, because of the UPDATE against table PARAMS.
    Any idea how to solve this ?

    Best regards,
    Kovi
    --
    -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
    | In A World Without Fences Who Needs Gates? |
    | Experience Linux. |
    -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
    Tags: None
    • Serge Rielau
      #2
      Re: Result set from ADMIN_CMD

      Gregor Kovac( wrote:
      Hi!
      >
      I'm trying to use ADMIN_CMD procedure in my SQL procedure like this:
      DROP PROCEDURE IMPORT_DATA#
      CREATE PROCEDURE IMPORT_DATA()
      SPECIFIC IMPORT_DATA
      MODIFIES SQL DATA
      NOT DETERMINISTIC
      LANGUAGE SQL
      BEGIN ATOMIC
      DECLARE DATA_REPL_ON_ST ATUS VARCHAR(10);
      DECLARE ROWS_READ INTEGER;
      DECLARE ROWS_SKIPPED INTEGER;
      DECLARE ROWS_INSERTED INTEGER;
      DECLARE ROWS_UPDATED INTEGER;
      DECLARE ROWS_REJECTED INTEGER;
      DECLARE ROWS_COMMITTED INTEGER;
      DECLARE MSG_RETRIEVAL VARCHAR(500);
      DECLARE MSG_REMOVAL VARCHAR(500);
      DECLARE result1 RESULT_SET_LOCA TOR VARYING;
      >
      SELECT STATUS INTO DATA_REPL_ON_ST ATUS FROM PARAMS WHERE CODE
      = 'DATA_REPL_ON' WITH UR;
      >
      IF (DATA_REPL_ON_S TATUS = '0') THEN
      UPDATE PARAMS SET STATUS = '1' WHERE CODE = 'DATA_REPL_ON';
      >
      CALL SYSPROC.ADMIN_C MD('IMPORT FROM /home/gregor/TABLE1.IXF OF IXF
      MESSAGES ON SERVER INSERT_UPDATE INTO EMGSYS.TABLE1') ;
      ASSOCIATE RESULT SET LOCATORS(result 1) WITH PROCEDURE
      SYSPROC.ADMIN_C MD;
      ALLOCATE rsCur CURSOR FOR RESULT SET result1;
      FETCH rsCur INTO ROWS_READ, ROWS_SKIPPED, ROWS_INSERTED,
      ROWS_UPDATED, ROWS_REJECTED, ROWS_COMMITTED, MSG_RETRIEVAL, MSG_REMOVAL;
      >
      UPDATE PARAMS SET STATUS = '0' WHERE CODE = 'DATA_REPL_ON';
      END IF;
      END
      >
      When I run this I get:
      SQL20397W Routine "SYSPROC.ADMIN_ CMD" execution has completed, but at least
      one error, "SQL3015", was encountered during the execution. More information
      is available.
      >
      In db2diag.log I get:
      SQL0774N The statement cannot be executed within an ATOMIC compound
      SQL statement.
      >
      I must use BEGIN ATOMIC, because of the UPDATE against table PARAMS.
      Any idea how to solve this ?
      I suspect that IMPORT performs a COMMIT....
      --
      Serge Rielau
      DB2 Solutions Development
      IBM Toronto Lab

      IOD Conference
      http://www.ibm.com/software/data/ondemandbusiness/conf2006/

        Comment

        • Pierre Saint-Jacques
          #3
          Re: Result set from ADMIN_CMD

          Yes, it does. Since V8 if one does not use the COMMITCOUNT parm. in the
          command, DB2 figures out a commit count of its own in the command.
          HTH, Pierre.

          --
          Pierre Saint-Jacques
          SES Consultants Inc.
          514-737-4515
          "Serge Rielau" <srielau@ca.ibm .coma écrit dans le message de news:
          4qc8mgFglbsiU3@ individual.net...
          Gregor Kovac( wrote:
          >Hi!
          >>
          >I'm trying to use ADMIN_CMD procedure in my SQL procedure like this:
          >DROP PROCEDURE IMPORT_DATA#
          >CREATE PROCEDURE IMPORT_DATA()
          >SPECIFIC IMPORT_DATA
          >MODIFIES SQL DATA
          >NOT DETERMINISTIC
          >LANGUAGE SQL
          >BEGIN ATOMIC
          > DECLARE DATA_REPL_ON_ST ATUS VARCHAR(10);
          > DECLARE ROWS_READ INTEGER;
          > DECLARE ROWS_SKIPPED INTEGER;
          > DECLARE ROWS_INSERTED INTEGER;
          > DECLARE ROWS_UPDATED INTEGER;
          > DECLARE ROWS_REJECTED INTEGER;
          > DECLARE ROWS_COMMITTED INTEGER;
          > DECLARE MSG_RETRIEVAL VARCHAR(500);
          > DECLARE MSG_REMOVAL VARCHAR(500);
          > DECLARE result1 RESULT_SET_LOCA TOR VARYING; SELECT STATUS INTO
          >DATA_REPL_ON_S TATUS FROM PARAMS WHERE CODE
          >= 'DATA_REPL_ON' WITH UR;
          >>
          > IF (DATA_REPL_ON_S TATUS = '0') THEN
          > UPDATE PARAMS SET STATUS = '1' WHERE CODE = 'DATA_REPL_ON';
          > CALL SYSPROC.ADMIN_C MD('IMPORT FROM /home/gregor/TABLE1.IXF OF
          >IXF
          >MESSAGES ON SERVER INSERT_UPDATE INTO EMGSYS.TABLE1') ;
          > ASSOCIATE RESULT SET LOCATORS(result 1) WITH PROCEDURE
          >SYSPROC.ADMIN_ CMD;
          > ALLOCATE rsCur CURSOR FOR RESULT SET result1;
          > FETCH rsCur INTO ROWS_READ, ROWS_SKIPPED, ROWS_INSERTED,
          >ROWS_UPDATED , ROWS_REJECTED, ROWS_COMMITTED, MSG_RETRIEVAL, MSG_REMOVAL;
          > UPDATE PARAMS SET STATUS = '0' WHERE CODE = 'DATA_REPL_ON';
          > END IF;
          >END
          >>
          >When I run this I get:
          >SQL20397W Routine "SYSPROC.ADMIN_ CMD" execution has completed, but at
          >least
          >one error, "SQL3015", was encountered during the execution. More
          >information
          >is available.
          >>
          >In db2diag.log I get:
          >SQL0774N The statement cannot be executed within an ATOMIC compound
          > SQL statement.
          >>
          >I must use BEGIN ATOMIC, because of the UPDATE against table PARAMS.
          >Any idea how to solve this ?
          I suspect that IMPORT performs a COMMIT....
          --
          Serge Rielau
          DB2 Solutions Development
          IBM Toronto Lab
          >
          IOD Conference
          http://www.ibm.com/software/data/ond...ness/conf2006/

            Comment

            Previous template Next
            Working...