create procedure cols2rows(
  in p_sel_stmt clob(2m)
, in p_collen   int
, in p_vallen   int
--, out p_rc      int
--, out p_msg     varchar(128)
--, out p_stmt    clob(2m)
)
LANGUAGE SQL
DYNAMIC RESULT SETS  1 
BEGIN
 declare p_rc      int;
 declare p_msg     varchar( 128 );
 declare p_stmt    clob(2m);
 declare SQLCODE        int;
 declare l_SQLTYPE_ID   int;
 declare l_SQLTYPE      varchar( 128 );
 declare l_SQLLENGTH    int;
 declare l_SQLSCALE     int;
 declare l_SQLNAME_DATA varchar( 128 );
 declare l_SQLTYPEF     varchar( 128 );
 declare l_colexpr      clob(2m) default '';
 declare l_valexpr      clob(2m) default '';
 declare l_colid        int default  0 ;
 declare l_collen	varchar( 10 );
 declare l_vallen	varchar( 10 );
 declare l_colname      varchar( 128 );
 declare l1             RESULT_set_LOCATOR VARYING;

 declare c_out cursor with return for s_out;

 declare EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
   GET DIAGNOSTICS EXCEPTION  1  p_msg = MESSAGE_TEXT;
   set p_rc = SQLCODE;
 END;

 set l_collen=trim(char(coalesce(p_collen,  10 ))), l_vallen=trim(char(coalesce(p_vallen,  80 )));
 -- construct case clauses
 set p_stmt = 'describe '||p_sel_stmt;
 set l_colexpr = 'when 0 then cast(repeat(''-'', '||l_collen||') as char('||l_collen||'))';
 set l_valexpr = 'when 0 then cast(repeat(''-'', '||l_vallen||') as char('||l_vallen||'))';
 call SYSPROC.ADMIN_CMD(p_stmt);
 associate result set locator (l1)
 with procedure SYSPROC.ADMIN_CMD;
 allocate c1 cursor for result set l1;
 open c1;
 fetch c1 into l_SQLTYPE_ID, l_SQLTYPE, l_SQLLENGTH, l_SQLSCALE, l_SQLNAME_DATA;
 while (SQLCODE!= 100 ) do
   set l_colid = l_colid+ 1 ;
   set l_colname = 
   case l_SQLTYPE_ID
     when  404  -- BLOB
       then '''x''''''||hex(a.'||l_SQLNAME_DATA||')||'''''''''
     when  988  -- XML
       then 'xmlserialize(a.'||l_SQLNAME_DATA||' as clob(4000))'
     else 'a.'||l_SQLNAME_DATA
   end;
   set l_colexpr=l_colexpr||' '||'when '||rtrim(char(l_colid))||' then cast('''||l_SQLNAME_DATA||''' as char('||l_collen||'))';
   set l_valexpr=l_valexpr||' '||'when '||rtrim(char(l_colid))||' then cast('||l_colname||' as char('||l_vallen||'))';
   fetch c1 into l_SQLTYPE_ID, l_SQLTYPE, l_SQLLENGTH, l_SQLSCALE, l_SQLNAME_DATA;
 end while;
 close c1;

 -- construct select statement
 set p_stmt=
   'with '
 ||'  a as (select s.*, rownumber() over() rn_ from ('||p_sel_stmt||') s) '
 ||', t (i) as (values 0 union all select i+1 from t where i<'||trim(char(l_colid))||') '
 ||'select '
 ||'  case t.i '||l_colexpr||' end as col '
 ||', case t.i '||l_valexpr||' end as val '
 ||'from a, t '
 ||'order by a.rn_, t.i';
 -- execute this statement
 prepare s_out from p_stmt;
 open c_out;
 set p_rc= 0 ;
END;

call cols2rows('select * from table(values 
  (current date, current time, current timestamp, double(1), real(1), 1, dec(1), clob(''123''), blob(''123''), decfloat(1), xmlparse (document ''<d><n>1</n></d>''))
, (current date, current time, current timestamp, double(2), real(2), 2, dec(2), clob(''456''), blob(''456''), decfloat(2), xmlparse (document ''<d><n>2</n></d>''))
) t(dt, tm, ts, dbl, rl, i, dec, cl, bl, df, xl)',  10 ,  80 );