help with SP

[db2admin]

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

[db2admin]

Re: help with SP

On Nov 1, 2:19 pm, db2admin <jag...@gmail.c omwrote:

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

[deangc]

Re: help with SP

On Nov 1, 11:20 am, db2admin <jag...@gmail.c omwrote:

' 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.

[db2admin]

Re: help with SP

On Nov 1, 2:24 pm, deangc <dean.cochr...@ gmail.comwrote:

On Nov 1, 11:20 am, db2admin <jag...@gmail.c omwrote:
>

' 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.

[Serge Rielau]

Re: help with SP

deangc wrote:

On Nov 1, 11:20 am, db2admin <jag...@gmail.c omwrote:

>> ' 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

[db2admin]

Re: help with SP

On Nov 1, 10:50 pm, Serge Rielau <srie...@ca.ibm .comwrote:

deangc wrote:

On Nov 1, 11:20 am, db2admin <jag...@gmail.c omwrote:

> ' 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