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]

On Nov 1, 2:19 pm, db2admin <[e-mail]:

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]

On Nov 1, 11:20 am, db2admin <[e-mail]:

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

On Nov 1, 2:24 pm, deangc <[e-mail]:

On Nov 1, 11:20 am, db2admin <[e-mail]:

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

deangc wrote:

On Nov 1, 11:20 am, db2admin <[e-mail]:

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

On Nov 1, 10:50 pm, Serge Rielau <[e-mail]:

deangc wrote:

On Nov 1, 11:20 am, db2admin <[e-mail]:

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