By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,418 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

help with SP

P: n/a
Hello all,

I written simple stored procedure to delete in chunks and getting
error while creating it
can someone help what could be the issue ?

=================================================

CREATE PROCEDURE CHUNK_DELETE (
IN in_schema VARCHAR(40),
IN in_tbname VARCHAR(100),
IN in_rows INT )
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_dynSQL VARCHAR(1000) ;
DECLARE SQLSTATE CHAR(5) DEFAULT
'00000' ;
DECLARE rows_deleted INT default 1 ;
SET v_dynSQL = 'DELETE FROM ( SELECT 1 FROM ' || in_schema || '.'
||
in_tbname ||
' WHERE LOCALE = ''de_DE'' FETCH FIRST ' ||
in_rows ||
' ROWS ONLY )';
PREPARE v_stmt1 FROM v_dynSQL;
REPEAT
EXECUTE v_stmt1;
GET DIAGNOSTICS rows_deleted = ROW_COUNT;
COMMIT;
UNTIL rows_deleted = 0
END REPEAT ;
END @

================================================== ==

thanks

Nov 1 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Nov 1, 2:19 pm, db2admin <jag...@gmail.comwrote:
Hello all,

I written simple stored procedure to delete in chunks and getting
error while creating it
can someone help what could be the issue ?

=================================================

CREATE PROCEDURE CHUNK_DELETE (
IN in_schema VARCHAR(40),
IN in_tbname VARCHAR(100),
IN in_rows INT )
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_dynSQL VARCHAR(1000) ;
DECLARE SQLSTATE CHAR(5) DEFAULT
'00000' ;
DECLARE rows_deleted INT default 1 ;
SET v_dynSQL = 'DELETE FROM ( SELECT 1 FROM ' || in_schema || '.'
||
in_tbname ||
' WHERE LOCALE = ''de_DE'' FETCH FIRST ' ||
in_rows ||
' ROWS ONLY )';
PREPARE v_stmt1 FROM v_dynSQL;
REPEAT
EXECUTE v_stmt1;
GET DIAGNOSTICS rows_deleted = ROW_COUNT;
COMMIT;
UNTIL rows_deleted = 0
END REPEAT ;
END @

================================================== ==

thanks
sorry, i forgot to mention error
I am getting following error

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0440N No authorized routine named "||" of type "FUNCTION" having
compatible arguments was found. LINE NUMBER=10. SQLSTATE=42884
thanks

Nov 1 '07 #2

P: n/a
On Nov 1, 11:20 am, db2admin <jag...@gmail.comwrote:
' WHERE LOCALE = ''de_DE'' FETCH FIRST ' ||
in_rows ||
' ROWS ONLY )';
You're trying to concatenate an INT with some strings. CHAR(in_rows)
should do it.
Nov 1 '07 #3

P: n/a
On Nov 1, 2:24 pm, deangc <dean.cochr...@gmail.comwrote:
On Nov 1, 11:20 am, db2admin <jag...@gmail.comwrote:
' WHERE LOCALE = ''de_DE'' FETCH FIRST ' ||
in_rows ||
' ROWS ONLY )';

You're trying to concatenate an INT with some strings. CHAR(in_rows)
should do it.
Thanks a lot dean
That worked.

Nov 1 '07 #4

P: n/a
deangc wrote:
On Nov 1, 11:20 am, db2admin <jag...@gmail.comwrote:
>> ' WHERE LOCALE = ''de_DE'' FETCH FIRST ' ||
in_rows ||
' ROWS ONLY )';

You're trying to concatenate an INT with some strings. CHAR(in_rows)
should do it.

Further I don't think you can COMMIT; within BEGIN ATOMIC.
Interesting QA test though... hmmmm

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Nov 2 '07 #5

P: n/a
On Nov 1, 10:50 pm, Serge Rielau <srie...@ca.ibm.comwrote:
deangc wrote:
On Nov 1, 11:20 am, db2admin <jag...@gmail.comwrote:
> ' WHERE LOCALE = ''de_DE'' FETCH FIRST ' ||
in_rows ||
' ROWS ONLY )';
You're trying to concatenate an INT with some strings. CHAR(in_rows)
should do it.

Further I don't think you can COMMIT; within BEGIN ATOMIC.
Interesting QA test though... hmmmm

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Yes,
You are probably right.
That is the whole idea of this stored procedure to commit every no. of
rows.
i will change it to simple BEGIN

Nov 3 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.