Result set from ADMIN_CMD

[Gregor Kova�]

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_STATUS 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_LOCATOR VARYING;

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

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

CALL SYSPROC.ADMIN_CMD('IMPORT FROM /home/gregor/TABLE1.IXF OF IXF
MESSAGES ON SERVER INSERT_UPDATE INTO EMGSYS.TABLE1');
ASSOCIATE RESULT SET LOCATORS(result1) 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 ?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

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_STATUS 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_LOCATOR VARYING;

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

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

CALL SYSPROC.ADMIN_CMD('IMPORT FROM /home/gregor/TABLE1.IXF OF IXF
MESSAGES ON SERVER INSERT_UPDATE INTO EMGSYS.TABLE1');
ASSOCIATE RESULT SET LOCATORS(result1) 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/

[Pierre Saint-Jacques]

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" <sr*****@ca.ibm.coma écrit dans le message de news:
4q************@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_STATUS 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_LOCATOR VARYING; SELECT STATUS INTO
DATA_REPL_ON_STATUS FROM PARAMS WHERE CODE
= 'DATA_REPL_ON' WITH UR;

IF (DATA_REPL_ON_STATUS = '0') THEN
UPDATE PARAMS SET STATUS = '1' WHERE CODE = 'DATA_REPL_ON';
CALL SYSPROC.ADMIN_CMD('IMPORT FROM /home/gregor/TABLE1.IXF OF
IXF
MESSAGES ON SERVER INSERT_UPDATE INTO EMGSYS.TABLE1');
ASSOCIATE RESULT SET LOCATORS(result1) 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/