----------------------------------------------------------
---                                                    ---
---     FILE:    HTM_JSQL.HTML                         ---
---     AUTHOR:  G.BIRCHALL                            ---
---     DATE:    13/SEP/2006                           ---
---     NOTES:   The following code creates the DB2    ---
---              user-defined functions that use the   ---
---              java code in the JAVA_CODE file.      ---
---              There are four types of function:     ---
---                                                    ---
---               - Scalar functions  that return a   ---
---                 single value (different types).    ---
---               - Table functions  that return a    ---
---                 single column of data (different   ---
---                 data types), plus an INTEGER row-  ---
---                 number value.                      ---
---               - Table functions  that return more ---
---                 one column of data (VARCHAR only), ---
---                 plus an INTEGER row-number value.  ---
---               - Table function  that transposes   ---
---                 the columns returned by a query    ---
---                 into separate rows.                ---
---                                                    ---
---     INPUT/OUTPUT LENGTHS:                          ---
---     I have set the input and output lengths for    ---
---     the functions as follows:                      ---
---     #1  INPUT (SQL STMT) is 4,000 bytes.           ---
---     #2  OUTPUT (VARCHAR VALUES) is 254 bytes.      ---
---     These values can be changed as desired. For    ---
---     the output length, make sure the change the    ---
---     java code (i.e. length test) to match.         ---
---                                                    ---
---     USAGE NOTES:                                   ---
---     #1  Save this file on your own machine.        ---
---     #2  Remove the few lines of HTML code at the   ---
---         top and bottom of this file.               ---
---     #3  After compiling the java code (see the     ---
---         HTM_JAVA file) run the commands below.     ---
---     #4  If everything tests out, you are ready     ---
---         to go.  If not, don't ask me, because I    ---
---         don't know much about java and DB2.        ---
---                                                    ---
----------------------------------------------------------


--------------------------------------------------
---                                            ---
---    CREATE SINGLE-DATA-COLUMN SCALAR FTNS   ---
---                                            ---
---    These functions return one data value.  ---
---    There is one function for each major    ---
---    DB2 data type.                          ---
---                                            ---
--------------------------------------------------

DROP FUNCTION get_Smallint;
DROP FUNCTION get_Integer;
DROP FUNCTION get_Bigint;
DROP FUNCTION get_Double;
DROP FUNCTION get_Decimal;
DROP FUNCTION get_Varchar;
COMMIT;

CREATE FUNCTION get_Smallint(VARCHAR(4000))
RETURNS SMALLINT
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!get_Smallint'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC 
READS SQL DATA
FENCED;
COMMIT;

CREATE FUNCTION get_Integer(VARCHAR(4000))
RETURNS INTEGER
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!get_Integer'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC 
READS SQL DATA
FENCED;
COMMIT;

CREATE FUNCTION get_Bigint(VARCHAR(4000))
RETURNS BIGINT
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!get_Bigint'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC 
READS SQL DATA
FENCED;
COMMIT;

CREATE FUNCTION get_Double(VARCHAR(4000))
RETURNS DOUBLE
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!get_Double'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC 
READS SQL DATA
FENCED;
COMMIT;

CREATE FUNCTION get_Decimal(VARCHAR(4000))
RETURNS DECIMAL(31,6)
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!get_Decimal'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC 
READS SQL DATA
FENCED;
COMMIT;

CREATE FUNCTION get_Varchar(VARCHAR(4000))
RETURNS VARCHAR(254)
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!get_Varchar'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC 
READS SQL DATA
FENCED;
COMMIT;


--------------------------------------------------
---                                            ---
---    CREATE SINGLE-DATA-COLUMN TABLE FTNS    ---
---                                            ---
---    These functions return one column of    ---
---    data, plus a row-number field. There    ---
---    is one function for each major DB2      ---
---    data type.                              ---
---                                            ---
--------------------------------------------------

DROP FUNCTION tab_Smallint;
DROP FUNCTION tab_Integer;
DROP FUNCTION tab_Bigint;
DROP FUNCTION tab_Double;
DROP FUNCTION tab_Decimal;
DROP FUNCTION tab_Varchar;
COMMIT;

CREATE FUNCTION tab_Smallint (VARCHAR(4000))
RETURNS TABLE  (row_number   INTEGER
               ,row_value    SMALLINT)
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_Smallint'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;

CREATE FUNCTION tab_Integer (VARCHAR(4000))
RETURNS TABLE  (row_number  INTEGER
               ,row_value   INTEGER)
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_Integer'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;

CREATE FUNCTION tab_Bigint  (VARCHAR(4000))
RETURNS TABLE  (row_number  INTEGER
               ,row_value   BIGINT)
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_Bigint'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;

CREATE FUNCTION tab_Double (VARCHAR(4000))
RETURNS TABLE  (row_number INTEGER
               ,row_value  DOUBLE)
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_Double'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;

CREATE FUNCTION tab_Decimal (VARCHAR(4000))
RETURNS TABLE  (row_number  INTEGER
               ,row_value   DECIMAL(31,6))
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_Decimal'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;

CREATE FUNCTION tab_Varchar (VARCHAR(4000))
RETURNS TABLE  (row_number  INTEGER
               ,row_value   VARCHAR(254))
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_Varchar'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;


--------------------------------------------------
---                                            ---
---    CREATE MULTI-DATA-COLUMN TABLE FTNS     ---
---                                            ---
---    These functions return between two and  ---
---    five columns of data (VARCHAR only),    ---
---    plus a row-number column.               ---
---                                            ---
--------------------------------------------------

DROP FUNCTION tab_2Varchar;
DROP FUNCTION tab_5Varchar;
DROP FUNCTION tab_10Varchar;
COMMIT;

CREATE FUNCTION tab_2Varchar (VARCHAR(4000))
RETURNS TABLE  (num_cols      SMALLINT
               ,row_number    INTEGER
               ,row_value01   VARCHAR(254)
               ,row_value02   VARCHAR(254))
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_2Varchar'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;

CREATE FUNCTION tab_5Varchar (VARCHAR(4000))
RETURNS TABLE  (num_cols      SMALLINT
               ,row_number    INTEGER
               ,row_value01   VARCHAR(254)
               ,row_value02   VARCHAR(254)
               ,row_value03   VARCHAR(254)
               ,row_value04   VARCHAR(254)
               ,row_value05   VARCHAR(254))
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_5Varchar'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;

CREATE FUNCTION tab_10Varchar (VARCHAR(4000))
RETURNS TABLE  (num_cols      SMALLINT
               ,row_number    INTEGER
               ,row_value01   VARCHAR(254)
               ,row_value02   VARCHAR(254)
               ,row_value03   VARCHAR(254)
               ,row_value04   VARCHAR(254)
               ,row_value05   VARCHAR(254)
               ,row_value06   VARCHAR(254)
               ,row_value07   VARCHAR(254)
               ,row_value08   VARCHAR(254)
               ,row_value09   VARCHAR(254)
               ,row_value10   VARCHAR(254))
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_10Varchar'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;


--------------------------------------------------
---                                            ---
---    CREATE TRANSPOSE-OUTPUT TABLE FUNCTION  ---
---                                            ---
---    This function takes the output columns  ---
---    returned by a query, and transposes     ---
---    the data into rows  one row per        ---
---    column of output (per row).             ---
---                                            ---
--------------------------------------------------

DROP FUNCTION tab_Transpose;
DROP FUNCTION tab_Transpose_4K;
COMMIT;

CREATE FUNCTION tab_Transpose (VARCHAR(4000))
RETURNS TABLE  (row_number     INTEGER
               ,num_cols       SMALLINT
               ,col_num        SMALLINT
               ,col_name       VARCHAR(128)
               ,col_type       VARCHAR(128)
               ,col_length     INTEGER
               ,col_value      VARCHAR(254))
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_Transpose'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;

CREATE FUNCTION tab_Transpose_4K (VARCHAR(4000))
RETURNS TABLE  (row_number     INTEGER
               ,num_cols       SMALLINT
               ,col_num        SMALLINT
               ,col_name       VARCHAR(128)
               ,col_type       VARCHAR(128)
               ,col_length     INTEGER
               ,col_value      VARCHAR(4000))
LANGUAGE JAVA
EXTERNAL NAME 'Graeme1!tab_Transpose_4K'
PARAMETER STYLE DB2GENERAL
NO EXTERNAL ACTION
NOT DETERMINISTIC
DISALLOW PARALLEL
READS SQL DATA
FINAL CALL
FENCED;
COMMIT;


--------------------------------------------------
---                                            ---
---    TEST FUNCTIONS - SCALAR                 ---
---                                            ---
--------------------------------------------------

------------------------------------------
---    TEST VARIOUS SCALAR FUNCTIONS   ---
------------------------------------------
SELECT   workdept AS dept
        ,empno
        ,salary
        ,DEC(get_Decimal(
            ' SELECT   salary'                            ||
            ' FROM     employee'                          ||
            ' WHERE    workdept = ''' || workdept || '''' ||
            ' AND      empno    > ''' || empno    || '''' ||
            ' ORDER BY empno' )
            ,9,2) AS next_sal
        ,lastname
        ,CHAR(get_Varchar(
            ' SELECT   lastname'                          ||
            ' FROM     employee'                          ||
            ' WHERE    workdept = ''' || workdept || '''' ||
            ' ORDER BY lastname' ),15)
            AS min_name
        ,get_Smallint(
            ' SELECT count(*)'                            ||
            ' FROM employee'                              ||
            ' where workdept = ''' || workdept || ''' ')
            AS #rows
FROM     employee
WHERE    salary  < 36000
ORDER BY workdept
        ,empno;


------------------------------------------
---    TEST WORKING WITH NULL VALUES   ---
------------------------------------------
SELECT   id
        ,name
        ,salary
        ,DEC(get_Decimal(
            ' SELECT   salary'             ||
            ' FROM     staff'              ||
            ' WHERE    id > '  || CHAR(id) ||
            ' ORDER BY id'
        ),9,2) AS next_sal
        ,comm
        ,DEC(get_Decimal(
            ' SELECT   comm'               ||
            ' FROM     staff'              ||
            ' WHERE    id > '  || CHAR(id) ||
            ' ORDER BY id'
        ),9,2) AS next_com
FROM     staff
WHERE    name LIKE 'S%'
ORDER BY id;


------------------------------------------
---    GET COUNT OF EMPLOYEES IN SAME  ---
---    DEPTARTMENT AS CURRENT ROW      ---
------------------------------------------
SELECT   workdept AS dept
        ,empno
        ,salary
        ,get_Integer(
            ' SELECT count(*)'     ||
            ' FROM employee'       ||
            ' where workdept = ''' || workdept || ''' ')
            AS #rows
FROM     employee
WHERE    salary  < 35500
ORDER BY workdept
        ,empno;


------------------------------------------
---    TEST NUMERIC SCALAR FUNCTIONS   ---
------------------------------------------
SELECT   workdept AS dept
        ,empno
        ,salary
        ,get_Integer(
            ' SELECT SUM(salary)'  ||
            ' FROM employee')
            AS sum_salary_int
        ,get_Bigint(
            ' SELECT SUM(salary)'  ||
            ' FROM employee')
            AS sum_salary_big
        ,get_Double(
            ' SELECT SUM(salary)'  ||
            ' FROM employee')
            AS sum_salary_dbl
FROM     employee
WHERE    salary  < 36000
ORDER BY workdept
        ,empno;


--------------------------------------------------
---                                            ---
---    TEST FUNCTIONS  TABULAR                ---
---                                            ---
--------------------------------------------------

------------------------------------------
---    TEST ALL OF THE SINGLE-COLUMN   ---
---    TABLE FUNCTIONS                 ---
------------------------------------------
SELECT   SMALLINT(sss.row_number) AS row#
        ,sss.row_value            AS col_sml
        ,iii.row_value            AS col_int
        ,bbb.row_value            AS col_big
        ,ddd.row_value            AS col_dbl
        ,DEC(eee.row_value,8,2)   AS col_dec
        ,CHAR(vvv.row_value,10)   AS col_var
FROM     TABLE(tab_Smallint(
            ' SELECT edlevel '        || 
            ' FROM   employee '       ||
            ' WHERE  salary  < 36000'
         )) AS sss
        ,TABLE(tab_Integer(
            ' SELECT salary '         || 
            ' FROM   employee '       ||
            ' WHERE  salary  < 36000'
         )) AS iii
        ,TABLE(tab_Bigint(
            ' SELECT salary '         || 
            ' FROM   employee '       ||
            ' WHERE  salary  < 36000'
         )) AS bbb
        ,TABLE(tab_Double(
            ' SELECT salary '         || 
            ' FROM   employee '       ||
            ' WHERE  salary  < 36000'
         )) AS ddd
        ,TABLE(tab_Decimal(
            ' SELECT salary '         || 
            ' FROM   employee '       ||
            ' WHERE  salary  < 36000'
         )) AS eee
        ,TABLE(tab_Varchar(
            ' SELECT lastname '       || 
            ' FROM   employee '       ||
            ' WHERE  salary  < 36000'
         )) AS vvv
WHERE    sss.row_number = iii.row_number
  AND    sss.row_number = bbb.row_number
  AND    sss.row_number = ddd.row_number
  AND    sss.row_number = eee.row_number
  AND    sss.row_number = vvv.row_number
ORDER BY 1;


------------------------------------------
---    SELECT ALL MATCHING ROWS (EMPNO ---
---    COL ONLY) FROM EMPLOYEE TABLE   ---
------------------------------------------
SELECT   row_number         AS row#
        ,CHAR(row_value,15) AS data
FROM     TABLE(tab_Varchar(
            ' SELECT empno'           ||
            ' FROM   employee '       ||
            ' WHERE  salary  < 36000'
         )) AS ttt
ORDER BY 1;


------------------------------------------
---    SELECT FIRST 2 COLUMNS FROM THE ---
---    MATCHING ROWS IN EMPLOYEE TABLE ---
------------------------------------------
SELECT   num_cols              AS cols
        ,SMALLINT(row_number)  AS row#
        ,CHAR(row_value01,15)  AS data1
        ,CHAR(row_value02,15)  AS data2
FROM     TABLE(tab_2Varchar(
            ' SELECT empno'           ||
            '       ,firstnme'        || 
            ' FROM   employee '       ||
            ' WHERE  salary  < 36000'
         )) AS ttt
ORDER BY 1;


------------------------------------------
---    SELECT FIRST FIVE COLUMNS & ROWS---
---    FROM EMP_PHOTO TABLE            ---
------------------------------------------
SELECT   num_cols              AS cols
        ,SMALLINT(row_number)  AS row#
        ,CHAR(row_value01,15)  AS data1
        ,CHAR(row_value02,15)  AS data2
        ,CHAR(row_value03,15)  AS data3
        ,CHAR(row_value04,15)  AS data4
        ,CHAR(row_value05,15)  AS data5
FROM     TABLE(tab_5Varchar(
            ' SELECT *'               || 
            ' FROM   emp_photo'       ||
            ' FETCH FIRST 5 ROWS ONLY'
         )) AS ttt
ORDER BY 1;


------------------------------------------
---    SELECT FIRST TEN COLUMNS & ROWS ---
---    FROM STAFF TABLE                ---
------------------------------------------
SELECT   num_cols              AS cols
        ,SMALLINT(row_number)  AS row#
        ,CHAR(row_value01,10)  AS data01
        ,CHAR(row_value02,10)  AS data02
        ,CHAR(row_value03,10)  AS data03
        ,CHAR(row_value04,10)  AS data04
        ,CHAR(row_value05,10)  AS data05
        ,CHAR(row_value06,10)  AS data06
        ,CHAR(row_value07,10)  AS data07
        ,CHAR(row_value08,10)  AS data08
        ,CHAR(row_value09,10)  AS data09
        ,CHAR(row_value10,10)  AS data10
FROM     TABLE(tab_10Varchar(
            ' SELECT   *'                 ||
            ' FROM     staff'             ||
            ' ORDER BY id'                ||
            ' FETCH FIRST 10 ROWS ONLY '
         )) AS ttt
ORDER BY 1;


------------------------------------------
---    SELECT TEN COLUMNS & ALL ROWS   ---
---    FROM ALL TABLES WHERE EMPNO     ---
---    EQUALS '000140'                 ---
------------------------------------------
WITH
search_values (search_column
              ,search_type
              ,search_length
              ,search_value) AS
  (VALUES  ('EMPNO'
           ,'CHARACTER'
           ,6
           ,'000140')
  ),
list_tables AS
  (SELECT   val.*
           ,tab.tabschema
           ,tab.tabname
   FROM     search_values  val
           ,syscat.tables  tab
           ,syscat.columns col
   WHERE    tab.tabschema  =  USER
     AND    tab.type       =  'T'
     AND    col.tabschema  =  tab.tabschema
     AND    col.tabname    =  tab.tabname
     AND    col.colname    =  val.search_column
     AND    col.typename   =  val.search_type
     AND    col.length     =  val.search_length
   ),
make_queries AS
   (SELECT   tab.*
            ,' SELECT * ' ||
             ' FROM '     || tabschema || '.' || tabname ||
             ' WHERE '    || search_column || ' = ''' ||
                             search_value  || '''' 
             AS tabquery
    FROM     list_tables tab
   ),
run_queries AS
   (SELECT   qqq.*
            ,ttt.*
    FROM     make_queries qqq
            ,TABLE(tab_10Varchar(tabquery)) AS ttt
   )
SELECT   CHAR(tabname,15)      AS tab_name  
        ,num_cols              AS cols
        ,SMALLINT(row_number)  AS row#
        ,CHAR(row_value01,12)  AS data01
        ,CHAR(row_value02,12)  AS data02
        ,CHAR(row_value03,12)  AS data03
        ,CHAR(row_value04,12)  AS data04
        ,CHAR(row_value05,12)  AS data05
        ,CHAR(row_value06,12)  AS data06
        ,CHAR(row_value07,12)  AS data07
        ,CHAR(row_value08,12)  AS data08
        ,CHAR(row_value09,12)  AS data09
        ,CHAR(row_value10,12)  AS data10
FROM     run_queries
ORDER BY search_column
        ,search_type
        ,search_length
        ,search_value
        ,tabschema
        ,tabname
        ,row_number
FOR FETCH ONLY
WITH UR;


------------------------------------------
---    JOIN MATCHING ROWS IN STAFF     ---
---    TABLE TO PRIOR ROWS             ---
------------------------------------------
SELECT   id
        ,name
        ,comm
        ,row_number
        ,CHAR(row_value,10) AS row_value
FROM     (SELECT   *
          FROM     staff
          WHERE    id  <  70
         )AS s1
LEFT OUTER JOIN
          TABLE(tab_Varchar(
             ' SELECT  comm'     ||
             ' FROM    staff s2' ||
             ' WHERE   s2.id < ' || CHAR(s1.id)
          ))AS tt
ON        1 = 1
ORDER BY id
        ,row_number
FOR FETCH ONLY
WITH UR;


------------------------------------------
---    LIST ALL "EMPNO" VALUES THAT    ---
---    EXIST IN MORE THAN 3 TABLES     ---
------------------------------------------
WITH
make_queries AS
  (SELECT   tab.tabschema
           ,tab.tabname
           ,' SELECT EMPNO ' ||
            ' FROM '     || tab.tabschema || '.' || tab.tabname 
             AS sql_text
   FROM     syscat.tables  tab
           ,syscat.columns col
   WHERE    tab.tabschema  =  USER
     AND    tab.type       =  'T'
     AND    col.tabschema  =  tab.tabschema
     AND    col.tabname    =  tab.tabname
     AND    col.colname    =  'EMPNO'
     AND    col.typename   =  'CHARACTER'
     AND    col.length     =   6
   ),
run_queries AS
   (SELECT   qqq.*
            ,ttt.*
    FROM     make_queries qqq
            ,TABLE(tab_Varchar(sql_text)) AS ttt
   )
SELECT   CHAR(row_value,10)                   AS empno
        ,COUNT(*)                             AS #rows
        ,COUNT(DISTINCT tabschema || tabname) AS #tabs
        ,CHAR(MIN(tabname),18)                AS min_tab
        ,CHAR(MAX(tabname),18)                AS max_tab
FROM     run_queries
GROUP BY row_value
HAVING   COUNT(DISTINCT tabschema || tabname) > 3
ORDER BY row_value
FOR FETCH ONLY
WITH UR;


--------------------------------------------------
---                                            ---
---    TEST FUNCTION - TRANSPOSE               ---
---                                            ---
--------------------------------------------------

------------------------------------------
---    TRANSPOSE SELECTED COLUMNS AND  ---
---    ROWS IN THE STAFF TABLE         ---
------------------------------------------
SELECT   SMALLINT(row_number)  AS row#
        ,col_num               AS col#
        ,CHAR(col_name,10)     AS col_name
        ,CHAR(col_type,10)     AS col_type
        ,CHAR(col_value,20)    AS col_value
FROM     TABLE(tab_Transpose(
            ' SELECT   id, name, comm, comm * 2  ' ||
            ' FROM     staff  '                    ||
            ' WHERE    id < 70'                    ||
            ' FOR FETCH ONLY WITH UR'
         )) AS ttt
ORDER BY row_number
        ,col_num
FOR FETCH ONLY
WITH UR;


------------------------------------------
---    TRANSPOSE FIRST FIVE ROWS IN    ---
---    THE STAFF TABLE (ALL COLUMNS)   ---
------------------------------------------
SELECT   row_number
        ,num_cols
        ,col_num
        ,CHAR(col_name,15)  AS col_name
        ,CHAR(col_type,15)  AS col_type
        ,col_length
        ,CHAR(col_value,20) AS col_value
FROM     TABLE(tab_Transpose(
            ' SELECT *'                 ||
            ' FROM   staff'             ||
            ' FETCH FIRST 5 ROWS ONLY')
         ) AS XXX
ORDER BY row_number
        ,col_num;


------------------------------------------
---    TRANSPOSE FIRST FIVE ROWS IN    ---
---    THE STAFF TABLE (ONE COLUMN)    ---
------------------------------------------
SELECT   row_number
        ,num_cols
        ,col_num
        ,CHAR(col_name,15)  AS col_name
        ,CHAR(col_type,15)  AS col_type
        ,col_length
        ,CHAR(col_value,20) AS col_value
FROM     TABLE(tab_Transpose(
            ' SELECT id'                 ||
            ' FROM   staff'              ||
            ' FETCH FIRST 5 ROWS ONLY')
         ) AS XXX
ORDER BY row_number
        ,col_num;


------------------------------------------
---    TRANSPOSE FIRST FIVE ROWS IN    ---
---    THE STAFF TABLE (3 COLUMNS)     ---
------------------------------------------
SELECT   row_number
        ,num_cols
        ,col_num
        ,CHAR(col_name,15)  AS col_name
        ,CHAR(col_type,15)  AS col_type
        ,col_length
        ,CHAR(col_value,20) AS col_value
FROM     TABLE(tab_Transpose(
            ' SELECT id, comm, ''ABC'' ' ||
            ' FROM   staff'              ||
            ' FETCH FIRST 5 ROWS ONLY')
         ) AS XXX
ORDER BY row_number
        ,col_num;


------------------------------------------
---    TRANSPOSE FIRST FIVE ROWS IN    ---
---    THE EMP_PHOTO TABLE (ALL COLS)  ---
------------------------------------------
SELECT   row_number
        ,num_cols
        ,col_num
        ,CHAR(col_name,15)  AS col_name
        ,CHAR(col_type,15)  AS col_type
        ,col_length
        ,CHAR(col_value,20) AS col_value
FROM     TABLE(tab_Transpose(
            ' SELECT * '                 ||
            ' FROM   emp_photo'          ||
            ' FETCH FIRST 5 ROWS ONLY')
         ) AS XXX
ORDER BY row_number
        ,col_num;


------------------------------------------
---    SELECT "EMPPROJACT" TABLE ROWS  ---
---    WHERE EMPNO = '000150'          ---
------------------------------------------
SELECT   *
FROM     empprojact
WHERE    empno  = '000150';


------------------------------------------
---    TRANSPOSE ROWS IN "EMPPROJACT"  ---
---    TABLE WHERE EMPNO = '000150'    ---
------------------------------------------
SELECT   SMALLINT(row_number)         AS row#
        ,col_num                      AS col#
        ,CHAR(col_name,13)            AS col_name
        ,CHAR(col_type,10)            AS col_type
        ,col_length                   AS col_len
        ,SMALLINT(LENGTH(col_value))  AS val_len
        ,SUBSTR(col_value,1,20)       AS col_value
FROM     TABLE(tab_Transpose(
            ' SELECT   *'                    ||
            ' FROM     empprojact'           ||
            ' WHERE    empno  = ''000150''' 
         )) AS ttt
ORDER BY 1,2;


------------------------------------------
---    SELECT AND TRANSPOSE ALL ROWS   ---
---    & COLUMNS IN ALL TABLES WHERE   ---
---    EMPNO = '000150'                ---
------------------------------------------
WITH
make_queries AS
  (SELECT   tab.tabschema
           ,tab.tabname
           ,' SELECT   *' ||
            ' FROM '      || tab.tabname ||
            ' WHERE empno  = ''000150'''
             AS sql_text
   FROM     syscat.tables  tab
           ,syscat.columns col
   WHERE    tab.tabschema  =  USER
     AND    tab.type       =  'T'
     AND    col.tabschema  =  tab.tabschema
     AND    col.tabname    =  tab.tabname
     AND    col.colname    =  'EMPNO'
     AND    col.typename   =  'CHARACTER'
     AND    col.length     =   6
   ),
run_queries AS
   (SELECT   qqq.*
            ,ttt.*
    FROM     make_queries qqq
            ,TABLE(tab_Transpose(sql_text)) AS ttt
   )
SELECT   SUBSTR(tabname,1,11)         AS tab_name  
        ,SMALLINT(row_number)         AS row#
        ,col_num                      AS col#
        ,CHAR(col_name,13)            AS col_name
        ,CHAR(col_type,10)            AS col_type
        ,col_length                   AS col_len
        ,SMALLINT(LENGTH(col_value))  AS val_len
        ,SUBSTR(col_value,1,20)       AS col_value
FROM     run_queries
ORDER BY 1,2,3;


------------------------------------------
---    SELECT AND TRANSPOSE ALL ROWS   ---
---    & COLUMNS IN ALL TABLES WHERE   ---
---    ANY SIX-BYTE CHARACTER COLUMN   ---
---    HAVE VALUE = '000150'           ---
------------------------------------------
WITH
search_values (search_type,search_length,search_value) AS
  (VALUES     ('CHARACTER',6,'000150')
  ),
list_columns AS
  (SELECT   val.search_value
           ,tab.tabschema
           ,tab.tabname
           ,col.colname
           ,ROW_NUMBER() OVER(PARTITION BY val.search_value
                                          ,tab.tabschema
                                          ,tab.tabname
                              ORDER BY     col.colname ASC)  AS col_a
           ,ROW_NUMBER() OVER(PARTITION BY val.search_value
                                          ,tab.tabschema
                                          ,tab.tabname
                              ORDER BY     col.colname DESC) AS col_d
   FROM     search_values  val
           ,syscat.tables  tab
           ,syscat.columns col
   WHERE    tab.tabschema  =  USER
     AND    tab.type       =  'T'
     AND    tab.tabschema  =  col.tabschema
     AND    tab.tabname    =  col.tabname
     AND    col.typename   =  val.search_type
     AND    col.length     =  val.search_length
   ),
make_queries (search_value
             ,tabschema
             ,tabname
             ,colname
             ,col_a
             ,col_d
             ,sql_text) AS
   (SELECT   tb1.*
            ,VARCHAR(' SELECT *' ||
                     ' FROM '    || tabname ||
                     ' WHERE '   || colname || ' = ''' ||
                                    search_value  || '''' 
                     ,4000)
    FROM     list_columns tb1
    WHERE    col_a  = 1
    UNION ALL
    SELECT   tb2.*
            ,mqy.sql_text ||
             ' OR '  || tb2.colname   || 
             ' = ''' || tb2.search_value  || '''' 
    FROM     list_columns tb2
            ,make_queries mqy
    WHERE    tb2.search_value  =  mqy.search_value
      AND    tb2.tabschema     =  mqy.tabschema
      AND    tb2.tabname       =  mqy.tabname
      AND    tb2.col_a         =  mqy.col_a + 1
   ),
run_queries AS
   (SELECT   qqq.*
            ,ttt.*
    FROM     make_queries qqq
            ,TABLE(tab_Transpose_4K(sql_text)) AS ttt
    WHERE    col_d = 1
   )
SELECT   SUBSTR(tabname,1,11)         AS tab_name  
        ,SMALLINT(row_number)         AS row#
        ,col_num                      AS col#
        ,CHAR(col_name,13)            AS col_name
        ,CHAR(col_type,10)            AS col_type
        ,col_length                   AS col_len
        ,SMALLINT(LENGTH(col_value))  AS val_len
        ,SUBSTR(col_value,1,20)       AS col_value
FROM     run_queries
ORDER BY 1,2,3;


------------------------------------------
---    LIST QUERIES GENERATED AND RUN  ---
---    IN THE ABOVE QUERY              ---
------------------------------------------
WITH
search_values (search_type,search_length,search_value) AS
  (VALUES     ('CHARACTER',6,'000150')
  ),
list_columns AS
  (SELECT   val.search_value
           ,tab.tabschema
           ,tab.tabname
           ,col.colname
           ,ROW_NUMBER() OVER(PARTITION BY val.search_value
                                          ,tab.tabschema
                                          ,tab.tabname
                              ORDER BY     col.colname ASC)  AS col_a
           ,ROW_NUMBER() OVER(PARTITION BY val.search_value
                                          ,tab.tabschema
                                          ,tab.tabname
                              ORDER BY     col.colname DESC) AS col_d
   FROM     search_values  val
           ,syscat.tables  tab
           ,syscat.columns col
   WHERE    tab.tabschema  =  USER
     AND    tab.type       =  'T'
     AND    tab.tabschema  =  col.tabschema
     AND    tab.tabname    =  col.tabname
     AND    col.typename   =  val.search_type
     AND    col.length     =  val.search_length
   ),
make_queries (search_value
             ,tabschema
             ,tabname
             ,colname
             ,col_a
             ,col_d
             ,sql_text) AS
   (SELECT   tb1.*
            ,VARCHAR(' SELECT *' ||
                     ' FROM '    || tabname ||
                     ' WHERE '   || colname || ' = ''' ||
                                    search_value  || '''' 
                     ,4000)
    FROM     list_columns tb1
    WHERE    col_a  = 1
    UNION ALL
    SELECT   tb2.*
            ,mqy.sql_text ||
             ' OR '  || tb2.colname   || 
             ' = ''' || tb2.search_value  || '''' 
    FROM     list_columns tb2
            ,make_queries mqy
    WHERE    tb2.search_value  =  mqy.search_value
      AND    tb2.tabschema     =  mqy.tabschema
      AND    tb2.tabname       =  mqy.tabname
      AND    tb2.col_a         =  mqy.col_a + 1
   )
SELECT   SUBSTR(sql_text,1,130) AS sql_text
FROM     make_queries
WHERE    col_d = 1;


--------------------------------------------------
---                                            ---
---    END SQL STATEMENTS                      ---
---                                            ---
--------------------------------------------------