Steve Rees and Hania El Ayoubi from Toronto Lab have shared this utility function written in SQL PL to browse table data in column value pair. It becomes useful when browsing wide table data. For example, if one looks at the table sysibmadm.mon_db_summary or table function mon_get_table(“SYSIBM”,”SYSTABLES”), the output is few rows but number of columns are too many and they wrap on the screen and are difficult to read.

Attached files are: db2perf_browse.txt for help and db2perf_browse.db2 for the stored procedure code:

Mark Barinstein has written another variant of the above stored procedure which takes a generic SQL statement and will return columns in column / value pair.

This procedure is col2rows.db2.

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 ''1'')), 
  (current date, current time, current timestamp, double(2), real(2), 2, dec(2), clob(''456''), blob(''456''), decfloat(2), 
      xmlparse (document ''2''))
) t(dt, tm, ts, dbl, rl, i, dec, cl, bl, df, xl)', 10, 30, ?, ?, ?)@

  Result set 1
  --------------

  COL        VAL                           
  ---------- ------------------------------
  ---------- ------------------------------ 
  DT         06.12.2010                     
  TM         16:40:07                       
  TS         2010-12-06-16.40.07.468000     
  DBL        1.0E0                          
  RL         1.0E0                          
  I          1                              
  DEC        00000000001.                   
  CL         123                            
  BL         x'313233'                      
  DF         1                              
  XL         1                
  ---------- ------------------------------ 
  DT         06.12.2010                     
  TM         16:40:07                       
  TS         2010-12-06-16.40.07.468000     
  DBL        2.0E0                          
  RL         2.0E0                          
  I          2                              
  DEC        00000000002.                   
  CL         456                            
  BL         x'343536'                      
  DF         2                              
  XL         2                

  24 record(s) selected.