--#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;