469,902 Members | 1,939 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,902 developers. It's quick & easy.

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_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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Oct 26 '06 #1
2 6582
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/
Oct 26 '06 #2
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/
Oct 26 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by maceo | last post: by
3 posts views Thread by chettiar | last post: by
1 post views Thread by Gregor Kovač | last post: by
1 post views Thread by jefftyzzer | last post: by
2 posts views Thread by joachim.c.mueller | last post: by
reply views Thread by David Beardsley | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.