sql - 如何在 DB2 数据库中查找 char 0x1A (→)

Question

最近，DB2 团队进行了数据库升级，2 天后又因为与 Application 的版本不兼容而再次降级。同时在少数数据库表中插入了 0x1A (→) 字符。如何在所有数据库表中找到该字符。任何想法请帮忙。

标签： sqldb2

Answer 1

你可以做这样的事情

CREATE TABLE FIND_VALUE (
    TABSCHEMA  VARCHAR(128) NOT NULL
,   TABNAME    VARCHAR(128) NOT NULL
,   COLNAME    VARCHAR(128) NOT NULL
,   ROW_COUNT   BIGINT
)
@
BEGIN
    FOR C AS cur CURSOR WITH HOLD FOR
        SELECT 'INSERT INTO FIND_VALUE SELECT '
            || '''' || TABSCHEMA || ''',''' || TABNAME || ''',''' || COLNAME || ''', COUNT(*) FROM '
            || '"' ||  TABSCHEMA || '"."' || TABNAME || '"'
            || ' WHERE "' || COLNAME || '" LIKE ''%' || x'1A' || '%''' AS S
        FROM SYSCAT.COLUMNS C JOIN SYSCAT.TABLES T USING ( TABSCHEMA, TABNAME )
        WHERE C.CODEPAGE > 0 
        AND TABSCHEMA NOT IN ('SYSIBM','SYSTOOLS')
        AND T.TYPE IN ('T','S')
        ORDER BY
            TABSCHEMA, TABNAME, COLNAME
    DO       
          EXECUTE IMMEDIATE C.S;
          COMMIT;
    END FOR;
END