Calling SYSPROC.DB2LOAD within SQL stored procedure

UDBDBA

Hi:

How can one call SYSPROC.DB2LOAD procedure within a SQL stored
procedure.
I get the 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: SQL0418N A statement contains a use of a parameter marker
that is not valid. LINE NUMBER=48. SQLSTATE=42610.

Thanks for your help.

Vijay
The code is given below:

create procedure prod.cursorload (out p_status varchar(300))
language sql
specific prod.cursorload0
modifies sql data

-- compound statements begin here
cs_0: begin

-- variable declaration
declare v_return integer default -1;
declare v_statusmsg varchar(300) default 'Procedure Error Terminated';
declare v_sqlstate char(5) default '00000';
declare sqlstate char(5) default '00000';

-- handler declaration
cs_0_0: begin
declare continue handler for not found
set v_sqlstate = '00000';
declare exit handler for sqlexception, sqlwarning
begin
set v_sqlstate = sqlstate;
set p_status = v_sqlstate || ' ' || v_sp_name || ' ' ||
v_statusmsg;
end;

-- Load table

CALL sysproc.db2load ( 1, 'declare cur cursor for SELECT * from
stage.tableA',
'load from cur of cursor modified by norowwarnings insert into
prod.tableA nonrecoverable ',
?, '', ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL) ;

set v_return = 0;
set p_status = v_sqlstate || ' ' || v_sp_name || ' Normal
completion';

end cs_0_0;
return v_return;
end cs_0
@

Apr 7 '06 #1

UDBDBA

I got the stored procedure compiled by doing the following:

-- Load variable declaration
declare v_version_number INTEGER default 1;
declare v_cursor_statement VARCHAR(32672);
declare v_load_command VARCHAR(32672);
declare v_sqlcode INTEGER default -1;
declare v_sqlmessage VARCHAR(2048) default '';
declare v_rows_read BIGINT default -1 ;
declare v_rows_skipped BIGINT default -1;
declare v_rows_loaded BIGINT default -1;
declare v_rows_rejected BIGINT default -1;
declare v_rows_deleted BIGINT default -1;
declare v_rows_committed BIGINT default -1;
declare v_rows_part_read BIGINT default -1;
declare v_rows_part_rejected BIGINT default -1;
declare v_rows_part_partitioned BIGINT default -1;
declare v_mpp_load_summary VARCHAR(32672) default NULL;
CALL sysproc.db2load ( v_version_number, 'declare cur cursor
for SELECT * from QA.tableA',
'load from cur of cursor modified by norowwarnings insert into
PROD.tableA nonrecoverable ',
v_sqlcode, v_sqlmessage, v_rows_read, v_rows_skipped,
v_rows_loaded, v_rows_rejected, v_rows_deleted, v_rows_committed,
v_rows_part_read, v_rows_part_rejected,
v_rows_part_partitioned, v_mpp_load_summary) ;

Is this the correct and only way todo it ?

Thanks.

Vijay

Apr 7 '06 #2