Prev: Error formatting event log
Next: DB2 and extended ASCII table
From: Gregor Kova? on 26 Oct 2006 10:40
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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
From: Serge Rielau on 26 Oct 2006 13:13
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/ondemandbusiness/conf2006/
From: Pierre Saint-Jacques on 26 Oct 2006 14:15
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(a)ca.ibm.com> a ?crit dans le message de news:
4qc8mgFglbsiU3(a)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/ondemandbusiness/conf2006/

 | 
Pages: 1
Prev: Error formatting event log
Next: DB2 and extended ASCII table