----------------------------------------------------------
--- ---
--- 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 ---
--- ---
--------------------------------------------------
扫一扫
545
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
