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 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, ?, ?, ?)@

call cols2rows('select PRIV_WORKSPACE_SECTION_LOOKUPS PRIV_WORKSPACE_SECTION_LOOKUP, AGENT_ID from sysibmadm.snapappl fetch first 2 rows only', 50, 50, ?, ?, ?)@