---------------------------------------------------------------------------------- --(c) Copyright IBM Corporation 2010 All rights reserved. -- -- -- --This sample program is owned by International Business Machines -- --Corporation or one of its subsidiaries ("IBM") and is copyrighted -- --and licensed, not sold. -- --BY ACCESSING, COPYING, OR USING THIS SAMPLE PROGRAM, YOU AGREE TO -- --THE TERMS OF THE AGREEMENT TITLED "International License Agreement -- --for Non-Warranted db2perf Programs" LOCATED IN THE FILE NAMED -- --"license.txt". -- -- -- -- db2perf_browse.db2 -- -- Steve Rees - srees@ca.ibm.com -- -- Hania El Ayoubi - elayoubi@ca.ibm.com -- -- -- -- Parameters: (Table name) -- -- -- -- call db2perf_browse( -- -- [, -- -- [, ]] )') -- -- -- -- examples -- -- CALL db2perf_browse('MON_GET_TABLE(null,null,-2)') -- -- CALL db2perf_browse('MON_GET_TABLE(null,null,-2)', -- -- 'where tabschema = ''ELAYOUBI'' ' ) -- -- CALL db2perf_browse('MON_GET_TABLE(null,null,-2)', -- -- 'where tabschema = ''ELAYOUBI'', -- -- 'my_get_table_view' ); -- -- -- -- -- -- Description: Utility procedure to browse column values from tables. -- -- Particularly useful for very wide tables (table functions) -- -- such as MON_GET_PKG_CACHE_STMT & friends. -- -- Optional 2nd argument is WHERE clause for the select. -- -- Optional 3rd argument is name of view to persist the query. -- -- -- -- Thanks to Serge Rielau & Chris Eaton for SQL which makes this much easier! -- -- -- ---------------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE db2perf_browse (IN p_tabname VARCHAR (256), IN where_clause VARCHAR(256) default '', IN view_name VARCHAR(256) default '' ) DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN DECLARE stmt_str varchar(16384); DECLARE view_str varchar(16384); DECLARE colname_stmt_str varchar(256); DECLARE schema_pattern varchar(128); DECLARE name_pattern varchar(128); DECLARE colname varchar(128); DECLARE typename varchar(128); DECLARE v_no_data integer; DECLARE colname_cursor cursor for colname_stmt; DECLARE output_cursor cursor with return to caller for output_Stmt; DECLARE continue handler for not found set v_no_data=1; if ( p_tabname like '%(%' ) then -- This is a table function. set stmt_str = 'select db2perf_t.col,db2perf_t.value from table(' || p_tabname || ') as db2perf_input, table ( values '; set name_pattern=upper(substr(p_tabname,1,locate('(',p_tabname)-1)); -- We need to know the colunms it returns, so we go query syscat.funcparms set colname_stmt_str = 'select parmname,typename from syscat.funcparms where funcname = ? ' || 'and funcschema = ''SYSPROC'' ' || 'and funcname = specificname ' || 'and rowtype in (''B'',''O'',''R'') order by ordinal'; else -- It's not a table function. if ( p_tabname like '%.%' ) then -- The name includes a schema. Break the name into schema and table name parts. set name_pattern=upper(substr(p_tabname,locate('.',p_tabname)+1)); set schema_pattern=upper(substr(p_tabname,1,locate('.',p_tabname)-1)); -- Again - we need to know the colunms it returns, so we go query syscat.columns set colname_stmt_str = 'select colname,typename from syscat.columns where tabschema = ''' || schema_pattern || ''' and tabname = ? order by colno'; else -- No schema name included, so we use CURRENT SCHEMA. set name_pattern=upper(p_tabname); set schema_pattern=current schema; set colname_stmt_str = 'select colname,typename from syscat.columns where tabschema = ''' || schema_pattern || ''' and tabname = ? order by colno'; end if; set stmt_str = 'select db2perf_t.col, db2perf_t.value from ' || p_tabname || ' as db2perf_input, table ( values '; end if; set v_no_data=0; -- Prepare and open a cursor to loop through the column names involved. prepare colname_stmt from colname_stmt_str; open colname_cursor using name_pattern; fetch colname_cursor into colname,typename; while ( v_no_data = 0 ) do set stmt_str = stmt_str || '(''' || colname || ''','; if ( TYPENAME in ('BIGINT','INTEGER','SMALLINT','CHARACTER','VARCHAR','DECIMAL','TIMESTAMP','DATE','DOUBLE' ) ) then -- These are types we know how to deal with in our simple example - we can just generate a cast to CHAR(80) set stmt_str = stmt_str || 'coalesce(cast('|| 'db2perf_input' ||'."' || colname || '" as char(80)),'''')),'; else if ( TYPENAME = ('CLOB') ) then -- For a CLOB, we do almost the same thing, except we just pick up the first 80 characters. set stmt_str = stmt_str || 'coalesce(cast(substr('|| 'db2perf_input' || '."' || colname || '",1,80) as char(80)),'''')),'; else -- Not a type that we know how to deal with, so we just display the type name & move on. set stmt_str = stmt_str || '''<' || rtrim(typename) || '>''),'; end if; end if; fetch colname_cursor into colname,typename; end while; -- At the end of each row, we inject a separator, so that the user knows when we've finished one row -- and are moving on to the next. set stmt_str = stmt_str || '( ''--------------------'',''----------------------------------------'' )) as db2perf_t(col,value)'; if where_clause <> '' then -- If the caller passed in a WHERE-clause, we apply it to the select to filter the rows -- (not the columns) that are displayed. set stmt_str = stmt_str || where_clause; end if; if view_name <> '' then -- If a view name was provided, the caller wants to hang onto this query as a view definition, -- so they can just come back to it by selecting directly from the view, instead of calling this -- again. set view_str = 'create or replace view ' || view_name || ' as ' || stmt_str; prepare view_create from view_str; execute view_create; end if; -- Sometimes it's easy to get carried away sending out lots of wide rows column-by-column. -- So we add a FF 1000 RO clause. Note that this is not part of the view definition, -- so the easiest thing to do if you want more than 1000 rows is to either change this -- stored procedure :-), or create a view and get the dataa set stmt_str = stmt_str || ' fetch first 1000 rows only'; prepare output_Stmt from stmt_str; open output_cursor; END @ CREATE OR REPLACE PROCEDURE db2perf_browse ( ) DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN DECLARE output_cursor CURSOR WITH RETURN TO CALLER FOR SELECT Usage from table ( values('db2perf_browse( [, [, ]] )') ) t(usage); open output_cursor; end @