--#COMMENT ----------------------------------------------------------
--#COMMENT ---                                                    ---
--#COMMENT ---     FILE:    SQL_META.HTML                         ---
--#COMMENT ---     AUTHOR:  G.BIRCHALL                            ---
--#COMMENT ---     DATE:    17/FEB/2006                           ---
--#COMMENT ---                                                    ---
--#COMMENT ---     NOTES:   This file contains some user-defined  ---
--#COMMENT ---              functions and stored procedures that  ---
--#COMMENT ---              can be used to join meta-data (i.e.   ---
--#COMMENT ---              DB2 catalogue info) to real data.     ---
--#COMMENT ---                                                    ---
--#COMMENT ---              There is one stored-procedure and     ---
--#COMMENT ---              user-defined function combination     ---
--#COMMENT ---              for each standard DB2 column type.    ---
--#COMMENT ---                                                    ---
--#COMMENT ---              The process logic goes as follows:    ---
--#COMMENT ---              - Invoke function - pass valid query  ---
--#COMMENT ---                that returns one column of the      ---
--#COMMENT ---                correct type.                       ---
--#COMMENT ---              - Function passes query to stored     ---
--#COMMENT ---                procedure.                          ---
--#COMMENT ---              - Stored procedure prepares, opens,   ---
--#COMMENT ---                fetches first row, and then closes  ---
--#COMMENT ---                the query.                          ---
--#COMMENT ---              - Stored procedure returns value got  ---
--#COMMENT ---                in fetch back to function.          ---
--#COMMENT ---              - Function returns value back to the  ---
--#COMMENT ---                calling SQL statement.              ---
--#COMMENT ---                                                    ---
--#COMMENT ---              As written, this code is desiged to   ---
--#COMMENT ---              run in DB2BATCH, because is uses two  ---
--#COMMENT ---              different stmt delimiters.  But with  ---
--#COMMENT ---              a few changes, it can run anywhere.   ---
--#COMMENT ---                                                    ---
--#COMMENT ---     WARNING: At the bottom is sample code that one ---
--#COMMENT ---              can use to update real data via meta- ---
--#COMMENT ---              data.  This code is very dangerous!   ---
--#COMMENT ---              Use with extreme care. And be aware   ---
--#COMMENT ---              that at one example can do a lot of   ---
--#COMMENT ---              damage if run.                        ---
--#COMMENT ---                                                    ---
--#COMMENT ----------------------------------------------------------



--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE FUNCTIONS & STORED PROCEDURES.   ---
--#COMMENT ---                                            ---
--#COMMENT ---    There is one user-defined function and  ---
--#COMMENT ---    stored procedure for each standard DB2  ---
--#COMMENT ---    column type.  The following items can   ---
--#COMMENT ---    be altered as desired:                  ---
--#COMMENT ---    - Length/scale of the value returned by ---
--#COMMENT ---      the DECIMAL function/procedure.       ---
--#COMMENT ---    - Length of the value returned by the   ---
--#COMMENT ---      VARCHAR function/procedure.           ---
--#COMMENT ---    - Length of input SQL query - for all   ---
--#COMMENT ---      functions/procedures.                 ---
--#COMMENT ---    - Statement delimiter - currently "!".  ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
--#SET DELIMITER !


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     BIGINT FUNCTION          ---
--#COMMENT ------------------------------------
CREATE PROCEDURE return_BIGINT (IN  in_stmt VARCHAR(4000)
                               ,OUT out_val BIGINT)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN 
   DECLARE c1 CURSOR FOR s1;
   PREPARE s1 FROM in_stmt;
   OPEN  c1;
   FETCH c1 INTO out_val;
   CLOSE c1;
   RETURN;
END!

CREATE FUNCTION return_BIGINT (in_stmt VARCHAR(4000))
RETURNS BIGINT
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN ATOMIC
   DECLARE out_val BIGINT;
   CALL    return_BIGINT(in_stmt,out_val);
   RETURN  out_val;
END!
COMMIT!


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     INTEGER FUNCTION         ---
--#COMMENT ------------------------------------
CREATE PROCEDURE return_INTEGER (IN  in_stmt VARCHAR(4000)
                                ,OUT out_val INTEGER)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN 
   DECLARE c1 CURSOR FOR s1;
   PREPARE s1 FROM in_stmt;
   OPEN    c1;
   FETCH   c1 INTO out_val;
   CLOSE   c1;
   RETURN;
END!

CREATE FUNCTION return_INTEGER (in_stmt VARCHAR(4000))
RETURNS INTEGER
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN ATOMIC
   DECLARE out_val INTEGER;
   CALL    return_INTEGER(in_stmt,out_val);
   RETURN  out_val;
END!
COMMIT!


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     SMALLINT FUNCTION        ---
--#COMMENT ------------------------------------
CREATE PROCEDURE return_SMALLINT (IN  in_stmt VARCHAR(4000)
                                 ,OUT out_val SMALLINT)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN 
   DECLARE c1 CURSOR FOR s1;
   PREPARE s1 FROM in_stmt;
   OPEN    c1;
   FETCH   c1 INTO out_val;
   CLOSE   c1;
   RETURN;
END!

CREATE FUNCTION return_SMALLINT (in_stmt VARCHAR(4000))
RETURNS SMALLINT
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN ATOMIC
   DECLARE out_val SMALLINT;
   CALL    return_SMALLINT(in_stmt,out_val);
   RETURN  out_val;
END!
COMMIT!


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     DECIMAL FUNCTION         ---
--#COMMENT ------------------------------------
CREATE PROCEDURE return_DECIMAL (IN  in_stmt VARCHAR(4000)
                                ,OUT out_val DECIMAL(31,6))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN 
   DECLARE c1 CURSOR FOR s1;
   PREPARE s1 FROM in_stmt;
   OPEN    c1;
   FETCH   c1 INTO out_val;
   CLOSE   c1;
   RETURN;
END!

CREATE FUNCTION return_DECIMAL (in_stmt VARCHAR(4000))
RETURNS DECIMAL(31,6)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN ATOMIC
   DECLARE out_val DECIMAL(31,6);
   CALL    return_DECIMAL(in_stmt,out_val);
   RETURN  out_val;
END!
COMMIT!


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     FLOAT FUNCTION           ---
--#COMMENT ------------------------------------
CREATE PROCEDURE return_FLOAT (IN  in_stmt VARCHAR(4000)
                              ,OUT out_val FLOAT)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN 
   DECLARE c1 CURSOR FOR s1;
   PREPARE s1 FROM in_stmt;
   OPEN    c1;
   FETCH   c1 INTO out_val;
   CLOSE   c1;
   RETURN;
END!

CREATE FUNCTION return_FLOAT (in_stmt VARCHAR(4000))
RETURNS FLOAT
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN ATOMIC
   DECLARE out_val FLOAT;
   CALL    return_FLOAT(in_stmt,out_val);
   RETURN  out_val;
END!
COMMIT!


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     DATE FUNCTION            ---
--#COMMENT ------------------------------------
CREATE PROCEDURE return_DATE (IN  in_stmt VARCHAR(4000)
                             ,OUT out_val DATE)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN 
   DECLARE c1 CURSOR FOR s1;
   PREPARE s1 FROM in_stmt;
   OPEN    c1;
   FETCH   c1 INTO out_val;
   CLOSE   c1;
   RETURN;
END!

CREATE FUNCTION return_DATE (in_stmt VARCHAR(4000))
RETURNS DATE
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN ATOMIC
   DECLARE out_val DATE;
   CALL    return_DATE(in_stmt,out_val);
   RETURN  out_val;
END!
COMMIT!


--#COMMENT ------------------------------------
--#COMMENT ---     TIME FUNCTION            ---
--#COMMENT ------------------------------------
CREATE PROCEDURE return_TIME (IN  in_stmt VARCHAR(4000)
                             ,OUT out_val TIME)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN 
   DECLARE c1 CURSOR FOR s1;
   PREPARE s1 FROM in_stmt;
   OPEN    c1;
   FETCH   c1 INTO out_val;
   CLOSE   c1;
   RETURN;
END!

CREATE FUNCTION return_TIME (in_stmt VARCHAR(4000))
RETURNS TIME
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN ATOMIC
   DECLARE out_val TIME;
   CALL    return_TIME(in_stmt,out_val);
   RETURN  out_val;
END!
COMMIT!


--#COMMENT ------------------------------------
--#COMMENT ---     TIMESTAMP FUNCTION       ---
--#COMMENT ------------------------------------
CREATE PROCEDURE return_TIMESTAMP (IN  in_stmt VARCHAR(4000)
                                  ,OUT out_val TIMESTAMP)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN 
   DECLARE c1 CURSOR FOR s1;
   PREPARE s1 FROM in_stmt;
   OPEN    c1;
   FETCH   c1 INTO out_val;
   CLOSE   c1;
   RETURN;
END!

CREATE FUNCTION return_TIMESTAMP (in_stmt VARCHAR(4000))
RETURNS TIMESTAMP
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN ATOMIC
   DECLARE out_val TIMESTAMP;
   CALL    return_TIMESTAMP(in_stmt,out_val);
   RETURN  out_val;
END!
COMMIT!


--#COMMENT ------------------------------------
--#COMMENT ---     VARCHAR FUNCTION         ---
--#COMMENT ------------------------------------
CREATE PROCEDURE return_VARCHAR (IN  in_stmt VARCHAR(4000)
                                ,OUT out_val VARCHAR(4000))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN 
   DECLARE c1 CURSOR FOR s1;
   PREPARE s1 FROM in_stmt;
   OPEN    c1;
   FETCH   c1 INTO out_val;
   CLOSE   c1;
   RETURN;
END!

CREATE FUNCTION return_VARCHAR (in_stmt VARCHAR(4000))
RETURNS VARCHAR(4000)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
BEGIN ATOMIC
   DECLARE out_val VARCHAR(4000);
   CALL    return_VARCHAR(in_stmt,out_val);
   RETURN  out_val;
END!
COMMIT!


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    SAMPLE TEST STATEMENTS.                 ---
--#COMMENT ---                                            ---
--#COMMENT ---    Note that the stmt delimiter is changed ---
--#COMMENT ---    to a semi-colon for this section.       ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
--#SET DELIMITER ;


--#COMMENT
--#COMMENT ----------------------------------------------
--#COMMENT ---                                        ---
--#COMMENT ---    Get list of matching tables/views,  ---
--#COMMENT ---    and also a count of number of rows  ---
--#COMMENT ---    in each table/view.                 ---
--#COMMENT ---                                        ---
--#COMMENT ----------------------------------------------
SELECT   CHAR(tabschema,8)  AS schema
        ,CHAR(tabname,20)   AS tabname
        ,return_INTEGER
         ('SELECT  COUNT(*) '  ||
          'FROM ' || tabschema || '.' || tabname
         )AS #rows
FROM     syscat.tables
WHERE    tabschema    = 'SYSCAT'
  AND    tabname   LIKE 'RO%' 
ORDER BY tabschema
        ,tabname
FOR FETCH ONLY
WITH UR;


--#COMMENT
--#COMMENT ----------------------------------------------
--#COMMENT ---                                        ---
--#COMMENT ---    Get MAX salary from all tables that ---
--#COMMENT ---    have a salary column.               ---
--#COMMENT ---                                        ---
--#COMMENT ----------------------------------------------
SELECT   CHAR(tab.tabname,20)   AS tabname
        ,DEC(return_DECIMAL
         ('SELECT MAX(' || col.colname || ')' ||
          'FROM ' || tab.tabschema || '.' || tab.tabname || ' WITH UR'
         ),9,2) AS max_salary
FROM     syscat.tables  tab
        ,syscat.columns col
WHERE    tab.tabschema  =  col.tabschema
  AND    tab.tabname    =  col.tabname
  AND    tab.tabschema  =  USER
  AND    tab.type       =  'T'
  AND    col.colname    =  'SALARY'
  AND    col.typename   =  'DECIMAL'
ORDER BY max_salary DESC
FOR FETCH ONLY
WITH UR;


--#COMMENT
--#COMMENT ----------------------------------------------
--#COMMENT ---                                        ---
--#COMMENT ---    List tables with no RUNSTATS data,  ---
--#COMMENT ---    and that currently have more than   ---
--#COMMENT ---    1,000 rows.                         ---
--#COMMENT ---                                        ---
--#COMMENT ----------------------------------------------
SELECT   CHAR(tabschema,8)  AS schema
        ,CHAR(tabname,20)   AS tabname
        ,#rows
FROM    (SELECT   tabschema
                 ,tabname
                 ,return_INTEGER(
                     ' SELECT  COUNT(*)'  ||
                     ' FROM ' || tabschema || '.' || tabname ||
                     ' FOR FETCH ONLY WITH UR'          
                  ) AS #rows
         FROM     syscat.tables tab
         WHERE    tabschema  LIKE  'SYS%'
           AND    type          =  'T'
           AND    stats_time   IS  NULL
        )AS xxx
WHERE    #rows > 1000
ORDER BY #rows DESC
FOR FETCH ONLY
WITH UR;


--#COMMENT
--#COMMENT ----------------------------------------------
--#COMMENT ---                                        ---
--#COMMENT ---    Count number of distinct department ---
--#COMMENT ---    values in tables with column.       ---
--#COMMENT ---                                        ---
--#COMMENT ----------------------------------------------
SELECT   CHAR(tab.tabname,15)   AS tabname
        ,return_SMALLINT(
            ' SELECT  COUNT(DISTINCT ' || col.colname || ')' ||
            ' FROM '  || tab.tabschema  || '.' || tab.tabname ||
            ' FOR FETCH ONLY WITH UR'
         ) AS #dept
FROM     syscat.columns col
        ,syscat.tables  tab
WHERE    col.tabschema  =  USER
  AND    col.colname   IN ('DEPTNO','WORKDEPT')
  AND    col.tabschema  =  tab.tabschema
  AND    col.tabname    =  tab.tabname
  AND    tab.type       =  'T'
FOR FETCH ONLY
WITH UR;


--#COMMENT
--#COMMENT ----------------------------------------------
--#COMMENT ---                                        ---
--#COMMENT ---    List tables that have a department  ---
--#COMMENT ---    column, and at least one row for    ---
--#COMMENT ---    the 'A00' department.               ---
--#COMMENT ---                                        ---
--#COMMENT ----------------------------------------------
SELECT   CHAR(tab.tabname,15)   AS tabname
        ,CHAR(col.colname,10)   AS colname
        ,CHAR(COALESCE(return_VARCHAR(
            ' SELECT ''Y'''  ||
            ' FROM '  || tab.tabschema  || '.' || tab.tabname ||
            ' WHERE ' || col.colname    || ' = ''A00''' ||
            ' FETCH FIRST 1 ROWS ONLY ' ||
            ' OPTIMIZE FOR 1 ROW ' ||
            ' WITH UR' 
         ),'N'),1) AS has_dept
FROM     syscat.columns col
        ,syscat.tables  tab
WHERE    col.tabschema  =  USER
  AND    col.colname   IN ('DEPTNO','WORKDEPT')
  AND    col.tabschema  =  tab.tabschema
  AND    col.tabname    =  tab.tabname
  AND    tab.type       =  'T'
FOR FETCH ONLY
WITH UR;


--#COMMENT
--#COMMENT ----------------------------------------------
--#COMMENT ---                                        ---
--#COMMENT ---    List tables that have a department  ---
--#COMMENT ---    column (that is suitably indexed),  ---
--#COMMENT ---    and that have at least one row for  ---
--#COMMENT ---    the 'A00' department.               ---
--#COMMENT ---                                        ---
--#COMMENT ----------------------------------------------
SELECT   CHAR(tab.tabname,15)   AS tabname
        ,CHAR(col.colname,10)   AS colname
        ,CHAR(COALESCE(return_VARCHAR(
            ' SELECT ''Y'''  ||
            ' FROM '  || tab.tabschema  || '.' || tab.tabname ||
            ' WHERE ' || col.colname    || ' = ''A00''' ||
            ' FETCH FIRST 1 ROWS ONLY ' ||
            ' OPTIMIZE FOR 1 ROW ' ||
            ' WITH UR' 
         ),'N'),1) AS has_dept
FROM     syscat.columns col
        ,syscat.tables  tab
WHERE    col.tabschema  =  USER
  AND    col.colname   IN ('DEPTNO','WORKDEPT')
  AND    col.tabschema  =  tab.tabschema
  AND    col.tabname    =  tab.tabname
  AND    tab.type       =  'T'
  AND    col.colname   IN
        (SELECT SUBSTR(idx.colnames,2,LENGTH(col.colname))
         FROM   syscat.indexes idx
         WHERE  tab.tabschema = idx.tabschema
           AND  tab.tabname   = idx.tabname)    
FOR FETCH ONLY
WITH UR;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    DROP FUNCTIONS & STORED PROCEDURES.     ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
DROP FUNCTION  return_BIGINT;
DROP PROCEDURE return_BIGINT;
DROP FUNCTION  return_INTEGER;
DROP PROCEDURE return_INTEGER;
DROP FUNCTION  return_SMALLINT;
DROP PROCEDURE return_SMALLINT;
DROP FUNCTION  return_DECIMAL;
DROP PROCEDURE return_DECIMAL;
DROP FUNCTION  return_FLOAT;
DROP PROCEDURE return_FLOAT;
DROP FUNCTION  return_DATE;
DROP PROCEDURE return_DATE;
DROP FUNCTION  return_TIME;
DROP PROCEDURE return_TIME;
DROP FUNCTION  return_TIMESTAMP;
DROP PROCEDURE return_TIMESTAMP;
DROP FUNCTION  return_VARCHAR;
DROP PROCEDURE return_VARCHAR;
COMMIT;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE FUNCTION & STORED PROCEDURE.     ---
--#COMMENT ---        EXECUTE IMMEDIATE EXAMPLES          ---
--#COMMENT ---                                            ---
--#COMMENT ---    This sample code uses a table function  ---
--#COMMENT ---    and related stored-procedure to execute ---
--#COMMENT ---    DML and/or DDL.  Note the following:    ---
--#COMMENT ---    - Length of stmt is currently set to    ---
--#COMMENT ---      1,000 bytes. Change if you wish.      ---
--#COMMENT ---    - The statement delimiter is "!" in the ---
--#COMMENT ---      statements used to create objects. In ---
--#COMMENT ---      sample queries it is ";".             ---
--#COMMENT ---                                            ---
--#COMMENT ---    This code is very dangerous! Use with   ---
--#COMMENT ---    extreme care.                           ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
--#SET DELIMITER !


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     CREATE S.P. & FUNCTION   ---
--#COMMENT ------------------------------------
CREATE PROCEDURE execute_immediate (IN  in_stmt     VARCHAR(1000)
                                   ,OUT out_sqlcode INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
   DECLARE sqlcode INTEGER;
   DECLARE EXIT HANDLER FOR sqlexception
       SET out_sqlcode = sqlcode;
   EXECUTE IMMEDIATE in_stmt;
   SET out_sqlcode = sqlcode;
   RETURN;
END!
COMMIT!

CREATE FUNCTION execute_immediate (in_stmt VARCHAR(1000))
RETURNS TABLE (sqltext VARCHAR(1000)
              ,sqlcode INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN ATOMIC
   DECLARE out_sqlcode INTEGER;
   CALL execute_immediate(in_stmt, out_sqlcode);
   RETURN VALUES (in_stmt, out_sqlcode);
END!
COMMIT!


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     CREATE TEST MQT TABLES    ---
--#COMMENT ------------------------------------
--#SET DELIMITER ;

CREATE TABLE fred.staff_dept1 AS
  (SELECT   dept
           ,COUNT(*) AS count_rows
   FROM     staff
   GROUP BY dept
)DATA INITIALLY DEFERRED  REFRESH IMMEDIATE;
COMMIT;

CREATE TABLE fred.staff_dept2 AS
  (SELECT   dept
           ,COUNT(*) AS count_rows
   FROM     staff
   GROUP BY dept
)DATA INITIALLY DEFERRED  REFRESH IMMEDIATE;
COMMIT;


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     REFRESH ABOVE TABLES     ---
--#COMMENT ------------------------------------
WITH temp1 AS
   (SELECT   tabschema
            ,tabname
    FROM     syscat.tables
    WHERE    tabschema   =  'FRED'
      AND    type        =  'S'
      AND    status      =  'C'
      AND    tabname  LIKE  '%DEPT%'
   )
SELECT   CHAR(tab.tabname,20)   AS tabname
        ,stm.sqlcode            AS sqlcode
        ,CHAR(stm.sqltext,100)  AS sqltext
FROM     temp1 AS tab
        ,TABLE(execute_immediate(
              'REFRESH TABLE ' ||
               RTRIM(tab.tabschema) || '.' || tab.tabname 
         ))AS stm
ORDER BY tab.tabname
WITH UR;
COMMIT;


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     SET INTEGRITY NO ACCESS  ---
--#COMMENT ------------------------------------
WITH temp1 AS
   (SELECT   tabschema
            ,tabname
    FROM     syscat.tables
    WHERE    tabschema   =  'FRED'
      AND    type        =  'S'
      AND    status      =  'N'
      AND    tabname  LIKE  '%DEPT%'
   )
SELECT   CHAR(tab.tabname,20)   AS tabname
        ,stm.sqlcode            AS sqlcode
        ,CHAR(stm.sqltext,100)  AS sqltext
FROM     temp1 AS tab
        ,TABLE(execute_immediate(
              ' SET INTEGRITY FOR ' ||
                RTRIM(tab.tabschema) || '.' || tab.tabname  ||
              ' OFF NO ACCESS'
         ))AS stm
ORDER BY tabname
WITH UR;
COMMIT;


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     DROP TABLES              ---
--#COMMENT ------------------------------------
WITH temp1 AS
   (SELECT   tabschema
            ,tabname
    FROM     syscat.tables
    WHERE    tabschema   =  'FRED'
   )
SELECT   CHAR(tab.tabname,20)   AS tabname
        ,stm.sqlcode            AS sqlcode
        ,CHAR(stm.sqltext,100)  AS sqltext
FROM     temp1 As tab
        ,TABLE(execute_immediate(
              ' DROP TABLE ' || 
                RTRIM(tab.tabschema) || '.' || tab.tabname
         ))AS stm
ORDER BY tabname
WITH UR;
COMMIT;


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     CREATE COPY #1 - BAD!!!  ---
--#COMMENT ---     DO <> RUN THIS CODE ---
--#COMMENT ---     AS IT MAY MAKE MANY MORE ---
--#COMMENT ---     TABLES THAN YOU THINK.   ---
--#COMMENT ------------------------------------
-- SELECT   CHAR(tab.tabname,20)   AS tabname
--         ,stm.sqlcode            AS sqlcode
--         ,CHAR(stm.sqltext,100)  AS sqltext
-- FROM     syscat.tables AS tab
--         ,TABLE(execute_immediate(
--               ' CREATE TABLE ' ||
--                 RTRIM(tab.tabschema) || '.' || tab.tabname  || '_C1' ||
--               ' LIKE ' || RTRIM(tab.tabschema) || '.' || tab.tabname
--          ))AS stm
-- WHERE    tab.tabschema     =  USER
--   AND    tab.tabname    LIKE  'S%'
-- ORDER BY tab.tabname
-- FOR FETCH ONLY
-- WITH UR;
-- COMMIT;

--#COMMENT
--#COMMENT ------------------------
--#COMMENT ---     DROP TABLES  ---
--#COMMENT ------------------------
WITH temp1 AS
   (SELECT   tabschema
            ,tabname
    FROM     syscat.tables
    WHERE    tabname      LIKE '%C1'
      AND    create_time     >  CURRENT TIMESTAMP - 1 HOUR
   )
SELECT   CHAR(tab.tabname,20)   AS tabname
        ,stm.sqlcode            AS sqlcode
        ,CHAR(stm.sqltext,100)  AS sqltext
FROM     temp1 As tab
        ,TABLE(execute_immediate(
              ' DROP TABLE ' || 
                RTRIM(tab.tabschema) || '.' || tab.tabname
         ))AS stm
ORDER BY tabname
FOR FETCH ONLY
WITH UR;
COMMIT;


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     CREATE COPY #2 - GOOD    ---
--#COMMENT ------------------------------------
WITH temp1 AS
  (SELECT  tabschema
          ,tabname
   FROM     syscat.tables
   WHERE    tabschema     =  USER
     AND    tabname    LIKE  'S%'
  )
SELECT   CHAR(tab.tabname,20)   AS tabname
        ,stm.sqlcode            AS sqlcode
        ,CHAR(stm.sqltext,100)  AS sqltext
FROM     temp1 tab
        ,TABLE(execute_immediate(
              ' CREATE TABLE ' ||
                RTRIM(tab.tabschema) || '.' || tab.tabname  || '_C2' ||
              ' LIKE ' || RTRIM(tab.tabschema) || '.' || tab.tabname
         ))AS stm
ORDER BY tab.tabname
FOR FETCH ONLY
WITH UR;
COMMIT;

--#COMMENT
--#COMMENT ------------------------
--#COMMENT ---     DROP TABLES  ---
--#COMMENT ------------------------
WITH temp1 AS
   (SELECT   tabschema
            ,tabname
    FROM     syscat.tables
    WHERE    tabname      LIKE '%C2'
      AND    create_time     >  CURRENT TIMESTAMP - 1 HOUR
   )
SELECT   CHAR(tab.tabname,20)   AS tabname
        ,stm.sqlcode            AS sqlcode
        ,CHAR(stm.sqltext,100)  AS sqltext
FROM     temp1 As tab
        ,TABLE(execute_immediate(
              ' DROP TABLE ' || 
                RTRIM(tab.tabschema) || '.' || tab.tabname
         ))AS stm
ORDER BY tabname
FOR FETCH ONLY
WITH UR;
COMMIT;


--#COMMENT ------------------------------------
--#COMMENT ---     CREATE COPY #3 - GOOD    ---
--#COMMENT ------------------------------------
WITH
temp0 AS
   (SELECT   RTRIM(tabschema) AS schema
            ,tabname          AS old_tabname
            ,tabname || '_C3' AS new_tabname
    FROM     syscat.tables
    WHERE    tabschema     =  USER
      AND    tabname    LIKE  'S%'
   ),
temp1 AS
   (SELECT   tab.*
            ,stm.sqlcode            AS sqlcode1
            ,CHAR(stm.sqltext,200)  AS sqltext1
    FROM     temp0 AS tab
            ,TABLE(execute_immediate(
                  ' CREATE TABLE ' || schema || '.' || new_tabname ||
                  ' LIKE '         || schema || '.' || old_tabname 
             ))AS stm
   ),
temp2 AS
   (SELECT   tab.*
            ,stm.sqlcode            AS sqlcode2
            ,CHAR(stm.sqltext,200)  AS sqltext2
    FROM     temp1 AS tab
            ,TABLE(execute_immediate(
                  ' INSERT   INTO ' || schema || '.' || new_tabname ||
                  ' SELECT * FROM ' || schema || '.' || old_tabname
             ))AS stm
   )
SELECT   CHAR(old_tabname,20) AS tabname
        ,sqlcode1
        ,sqlcode2
FROM     temp2
ORDER BY old_tabname
FOR FETCH ONLY
WITH UR;
COMMIT;

--#COMMENT
--#COMMENT ------------------------
--#COMMENT ---     DROP TABLES  ---
--#COMMENT ------------------------
WITH temp1 AS
   (SELECT   tabschema
            ,tabname
    FROM     syscat.tables
    WHERE    tabname      LIKE '%C3'
      AND    create_time     >  CURRENT TIMESTAMP - 1 HOUR
   )
SELECT   CHAR(tab.tabname,20)   AS tabname
        ,stm.sqlcode            AS sqlcode
        ,CHAR(stm.sqltext,100)  AS sqltext
FROM     temp1 As tab
        ,TABLE(execute_immediate(
              ' DROP TABLE ' || 
                RTRIM(tab.tabschema) || '.' || tab.tabname
         ))AS stm
ORDER BY tabname
FOR FETCH ONLY
WITH UR;
COMMIT;


--#COMMENT
--#COMMENT ------------------------------------
--#COMMENT ---     DROP FUNCTION & STORED-P ---
--#COMMENT ------------------------------------
DROP FUNCTION  execute_immediate;
DROP PROCEDURE execute_immediate;
COMMIT;