DB2动态搜索

Question

我需要创建search (如'%-99‘或like’%NON%‘)-there for all tables in DB2 in SYS in all columns -请帮助--这是测试代码-但我有DB2数据库错误:错误42703DB2/LINUXX8664 SQL0206N " where“在使用的上下文中无效。

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_DINAMIC_TEST_SEARCH ( TABSET VARCHAR(128)，TABSCHEMA VARCHAR(128)，TABNAME VARCHAR(128)，COLUMN_NAME VARCHAR(128)，ROW_COUNT BIGINT ) ON COMMIT PRESERVE NOT LOGGED；

BEGIN
FOR C AS CUR CURSOR WITH HOLD FOR
    SELECT 'INSERT INTO SESSION.TEMP_DINAMIC_TEST_SEARCH SELECT ''' 
    ||''' ,''' || TABLE_SCHEM || ''',''' || TABLE_NAME || ''',''' || COLUMN_NAME || ''', COUNT( DISTINCT(' || COLUMN_NAME || ')) FROM '
    || '"' ||  TABLE_SCHEM || '"."' || TABLE_NAME|| '"' ||WHERE|| '"' || COLUMN_NAME || '"' || LIKE || '"' ||'%-99'|| '"' || AS S
    FROM SYSIBM.SQLCOLUMNS
    WHERE TABLE_SCHEM = 'DWD' AND TABLE_NAME LIKE 'T_X_%'
    WITH UR
DO
      EXECUTE IMMEDIATE C.S;
      COMMIT;
END FOR;
END

Answer 1

使用了错误的表达式。

试试这个：

SELECT 
   'INSERT INTO SESSION.TEMP_DINAMIC_TEST_SEARCH SELECT ''' 
|| ''' ,''' || TABLE_SCHEM || ''',''' || TABLE_NAME || ''',''' || COLUMN_NAME || ''', COUNT( DISTINCT(' || COLUMN_NAME || ')) FROM '
|| '"' ||  TABLE_SCHEM || '"."' || TABLE_NAME|| '" WHERE "' || COLUMN_NAME || '" LIKE ''%-99''' AS S
FROM SYSIBM.SQLCOLUMNS
WHERE TABLE_SCHEM = 'DWD' AND TABLE_NAME LIKE 'T_X_%'
/*
AND 
(
   TYPE_NAME LIKE '%CHAR' 
OR TYPE_NAME LIKE '%GRAPHIC'
OR TYPE_NAME LIKE '%CLOB'
)
*/
WITH UR

顺便说一句，只为字符串列生成INSERT SELECT语句是值得的，如注释掉的行所示。

尽量不要在Db2 for LUW中使用SYSIBM模式中的表-它们没有文档记录，并且它们的内容可能会在没有任何通知的情况下随着新的版本/修复包而发生变化。取而代之的是SYSCAT视图-在本例中是SYSCAT.COLUMNS视图。