470,575 Members | 1,616 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,575 developers. It's quick & easy.

Calling SYSPROC.DB2LOAD within SQL stored procedure


How can one call SYSPROC.DB2LOAD procedure within a SQL stored
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.

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
set v_sqlstate = sqlstate;
set p_status = v_sqlstate || ' ' || v_sp_name || ' ' ||

-- Load table

CALL sysproc.db2load ( 1, 'declare cur cursor for SELECT * from
'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

end cs_0_0;
return v_return;
end cs_0

Apr 7 '06 #1
1 7884
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 ?



Apr 7 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by David Carver | last post: by
3 posts views Thread by harborboy76 | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.