仓库项目开发批处理调度程序时用到的两三个SQL

数据仓库项目在SQL代码开发完成后，要考虑程序代码间的依赖关系，统计用到的ODS源表情况，然后才能开发批处理调度程序。本文就此贴出查找程序调度依赖关系和统计用到的ODS源表的两段SQL代码。

1、 查找程序调度依赖关系

 WITH EDW_TABLE AS (SELECT DISTINCT TRIM(SUBSTR(SUBSTR(UPPER(TEXT), INSTR(UPPER(TEXT), 'EDW', 1), 30)||' ',1,INSTR(SUBSTR(UPPER(TEXT), INSTR(UPPER(TEXT), 'EDW', 1), 30)||' ', ' ', 1))) 排在前面的表 FROM USER_SOURCE WHERE (UPPER(TEXT) LIKE '%FROM%EDW%' OR UPPER(TEXT) LIKE '%JOIN%EDW%') AND UPPER(TEXT) NOT LIKE '%V_ERR_SQL%'--去除错误信息提示 AND UPPER(TEXT) NOT LIKE '%EDW_MAP%' --过滤掉MAP类特殊代码 AND UPPER(TEXT) NOT LIKE '%EDW_T99%' --过滤掉T99类特殊代码 AND UPPER(TEXT) NOT LIKE '%' || REPLACE(UPPER(NAME), 'P_T', 'EDW_T') || '%' --过滤掉如清空目标 'TRUNCATE TABLE EDW_..'或'DETETE FROM EDW_..'或'INSERT INTO EDW_..' AND UPPER(NAME) LIKE 'P_T%' --过滤掉非EDW层代码 ) SELECT DISTINCT REPLACE(REPLACE(REPLACE(排在前面的表,'EDW_','P_'),CHR(10),''),';','') 排在前面的存储过程 FROM EDW_TABLE ORDER BY 1;

说明：(1)、如果要查找再上一级的依赖关系，则将user_source的NAME值限定为第一次查找的结果，就可以得到依赖的依赖；(2)、用到的知识点有：换行符标志[CHR(10)]；INSTR函数等。

2、 统计用到的ODS源表

 SELECT DISTINCT REPLACE(REPLACE(REPLACE(TRIM(SUBSTR(SUBSTR(UPPER(TEXT), INSTR(UPPER(TEXT), 'ODS', 1), 30)||' ',1,INSTR(SUBSTR(UPPER(TEXT), INSTR(UPPER(TEXT), 'ODS', 1), 30)||' ', ' ', 1))),'EDW_','P_'),CHR(10),''),';','') 仓库用到的ODS源表清单 FROM USER_SOURCE WHERE (UPPER(TEXT) LIKE '%FROM%ODS%' OR UPPER(TEXT) LIKE '%JOIN%ODS%') AND UPPER(TEXT) NOT LIKE '%V_ERR_SQL%'--去除错误信息提示 AND UPPER(NAME) LIKE 'P_T%' --过滤掉非EDW层代码 ORDER BY 1;

说明：如果要查找用到哪些DWIF接口表，则将ODS替换成DWIF即可；REPLACE(‘字符串’,CHR(10),'')可把换行符去掉。

3、 列出程序依赖关系清单 

SELECT DISTINCT NAME 存储过程名称, CASE WHEN INSTR(UPPER(TEXT)||' ','EDW',1,1) <>0 THEN SUBSTR(SUBSTR(UPPER(TEXT)||' ',INSTR(UPPER(TEXT)||' ','EDW',1,1)),1,INSTR(SUBSTR(UPPER(TEXT)||' ',INSTR(UPPER(TEXT)||' ','EDW',1,1)),' ',1,1)-1) WHEN INSTR(UPPER(TEXT)||' ','ODS',1,1) <>0 THEN SUBSTR(SUBSTR(UPPER(TEXT)||' ',INSTR(UPPER(TEXT)||' ','ODS',1,1)),1,INSTR(SUBSTR(UPPER(TEXT)||' ',INSTR(UPPER(TEXT)||' ','ODS',1,1)),' ',1,1)-1) ELSE '不合规范的SQL-'||TEXT END 依赖的源表名 FROM USER_SOURCE WHERE (UPPER(TEXT) LIKE '%FROM%EDW%' OR UPPER(TEXT) LIKE '%JOIN%EDW%' OR UPPER(TEXT) LIKE '%FROM%ODS%' OR UPPER(TEXT) LIKE '%JOIN%ODS%') AND UPPER(TEXT) NOT LIKE '%V_ERR_SQL%'--去除错误信息提示 AND UPPER(TEXT) NOT LIKE '%EDW_MAP%' --过滤掉MAP类特殊代码 AND UPPER(TEXT) NOT LIKE '%EDW_T99%' --过滤掉T99类特殊代码 AND UPPER(TEXT) NOT LIKE '%DELETE%' --过滤掉带有DELETE的代码 AND UPPER(TEXT) NOT LIKE '%' || REPLACE(UPPER(NAME), 'P_T', 'EDW_T') || '%' --过滤掉如清空目标 'TRUNCATE TABLE EDW_..'或'DETETE FROM EDW_..'或'INSERT INTO EDW_..'==>代码中引用的原表目标表 AND UPPER(NAME) LIKE 'P_T%' --过滤掉非EDW层代码 ORDER BY NAME,2 ;

说明：
(1)、要使列出的清单准确，需要存储过程格式规范化，如表名全为大写(此处已经作了处理)、FROM后面的源表只能有一个、统一使用JOIN而非(+)(-)的样式等；
(2)、本代码只是一个引子，抛砖引玉，如在JLBANK环境下有一部分DWIF接口数据就未考虑，可根据情况适当更改；
(3)、此代码提升了工作效率，比原先对照存储过程一个个检查，整理出依赖关系，效率高了不止一个层次。