create procedure ex_cur(
	out p_sqlcode int,
	out p_sqlmsg varchar( 500 ),
	out p_cnt int
) specific ex_cur
p1: begin 
	declare sqlcode int default  0 ;
	declare sqlstate char( 5 ) default '00000';
	declare v_tablename varchar( 50 ) default '';
	declare v_schema varchar( 128 );
	declare v_table varchar( 128 );
	declare v_qualifiedname varchar( 128 );
	declare v_ok int default  0 ;
	declare v_cnt int default  0 ;
	declare v_stmt_text varchar( 500 ) default '';
	--
	declare c_dn cursor with hold
		--with return to caller
		for
		select substr(rtrim(creator)||'.'||rtrim(name), 1 , 50 ),creator,name as table_name
			from sysibm.systables where creator not in ('SYSIBM','SYSCAT') with ur;
	--
	declare exit handler for sqlexception
		begin
			get diagnostics exception  1  p_sqlmsg=message_text;
			values sqlcode into p_sqlcode;
			set p_sqlmsg=v_stmt_text||CHR( 13 )||CHR( 10 )||p_sqlmsg;
		end;
	declare exit handler for not found
		begin
			values sqlcode into p_sqlcode;
			set p_sqlmsg='Finished';
		end;
	set p_sqlmsg='';
    open c_dn;
    set p_cnt= 0 ;
	declare global temporary table capacity (
	    rowcount bigint,
		tableschema varchar( 128 ),
		tablename varchar( 128 )
	) on commit preserve rows with replace;
    loop
		fetch c_dn into v_qualifiedname,v_schema,v_table;
		set p_cnt=p_cnt+ 1 ;
		begin
			--
			set v_stmt_text='insert into session.capacity values'
				||'((select count(*) from "'||rtrim(v_schema)||'"."'||rtrim(v_table)||'"),?,?)';
			prepare stmt from v_stmt_text;
			execute stmt into v_cnt using v_schema,v_table;
			commit;
		end;
    end loop;				
	close c_dn;	    	
end p1
@
commit@

call ex_cur(?,?,?)@
commit@

    loop
		fetch c_dn into v_qualifiedname,v_schema,v_table;
		set p_cnt=p_cnt+ 1 ;
		begin
			--
			set v_stmt_text='insert into session.capacity values'
				||'((select count(*) from "'||rtrim(v_schema)||'"."'||rtrim(v_table)||'"),?,?)';
			prepare stmt from v_stmt_text;
			execute stmt into v_cnt using v_schema,v_table;
			commit;
		end;
    end loop;				

    loop
		fetch c_dn into v_qualifiedname,v_schema,v_table;
		set p_cnt=p_cnt+ 1 ;
		--begin
			--
			--set v_stmt_text='insert into session.capacity values'
			--	||'((select count(*) from "'||rtrim(v_schema)||'"."'||rtrim(v_table)||'"),?,?)';
			--prepare stmt from v_stmt_text;
			--execute stmt into v_cnt using v_schema,v_table;
			--commit;
		--end;
    end loop;				

call ex_cur(?,?,?)

  Value of output parameters
  --------------------------
  Parameter Name  : P_SQLCODE
  Parameter Value :  100 

  Parameter Name  : P_SQLMSG
  Parameter Value : Finished

  Parameter Name  : P_CNT
  Parameter Value :  118 

  Return Status =  0 

declare exit handler for not found
begin
   values sqlcode into p_sqlcode;
   set p_sqlmsg='Finished';
end;