DB2 程式設計技巧（六）：系統目錄查詢 SP/表/函數相依、修改表結構、Windows 98 安裝修正

DB2 物件相依查詢與 DDL 技巧

查看 SP 文本

SELECT TEXT FROM SYSCAT.PROCEDURES WHERE PROCNAME='PROC1';

查看表結構

DESCRIBE TABLE SYSCAT.PROCEDURES
DESCRIBE SELECT * FROM SYSCAT.PROCEDURES

表被哪些 SP 使用 (相依反查)

SELECT PROCNAME FROM SYSCAT.PROCEDURES
WHERE SPECIFICNAME IN (
  SELECT DNAME FROM SYSIBM.SYSDEPENDENCIES
  WHERE BNAME IN (SELECT PKGNAME FROM SYSCAT.PACKAGEDEP WHERE BNAME='TB_BRANCH')
);

SP 使用了哪些表

SELECT BNAME FROM SYSCAT.PACKAGEDEP
WHERE BTYPE='T' AND PKGNAME IN (
  SELECT BNAME FROM SYSIBM.SYSDEPENDENCIES
  WHERE DNAME IN (SELECT SPECIFICNAME FROM SYSCAT.PROCEDURES WHERE PROCNAME='PR_CLEAR_MATCH_DIVIDE_SHA')
);

函數被哪些 SP 使用

SELECT PROCNAME FROM SYSCAT.PROCEDURES
WHERE SPECIFICNAME IN (
  SELECT DNAME FROM SYSIBM.SYSDEPENDENCIES
  WHERE BNAME IN (
    SELECT PKGNAME FROM SYSCAT.PACKAGEDEP
    WHERE BNAME IN (SELECT SPECIFICNAME FROM SYSCAT.FUNCTIONS WHERE FUNCNAME='GET_CURRENT_DATE')
  )
);

注意：DROP FUNCTION 前必須先 DROP 所有呼叫該函數的 SP；建立 SP 前必須先建立被呼叫的 FUNCTION。

一次 ALTER TABLE 增加多欄位

db2 "ALTER TABLE tb_test ADD COLUMN t1 CHAR(1) ADD COLUMN t2 CHAR(2) ADD COLUMN t3 INT"

Windows 98 DB2 7.1 安裝修正 (JDBC/啟動失敗)

以以下內容替換 autoexec.bat：

C:\PROGRA~1\TRENDP~1\PCSCAN.EXE C:\ C:\WINDOWS\COMMAND\ /NS /WIN95
REM C:\WINDOWS\COMMAND.COM /E:32768
SET PATH=%PATH%;C:\MSSQL\BINN;C:\PROGRA~1\SQLLIB\BIN;C:\PROGRA~1\SQLLIB\FUNCTION;C:\PROGRA~1\SQLLIB\SAMPLES\REPL;C:\PROGRA~1\SQLLIB\HELP
IF EXIST C:\PROGRA~1\IBM\IMNNQ\IMQENV.BAT CALL C:\PROGRA~1\IBM\IMNNQ\IMQENV.BAT
IF EXIST C:\PROGRA~1\IBM\IMNNQ\IMNENV.BAT CALL C:\PROGRA~1\IBM\IMNNQ\IMNENV.BAT
SET DB2INSTANCE=DB2
SET CLASSPATH=.;C:\PROGRA~1\SQLLIB\JAVA\DB2JAVA.ZIP;C:\PROGRA~1\SQLLIB\JAVA\RUNTIME.ZIP;C:\PROGRA~1\SQLLIB\JAVA\SQLJ.ZIP;C:\PROGRA~1\SQLLIB\BIN
SET MDIS_PROFILE=C:\PROGRA~1\SQLLIB\METADATA\PROFILES
SET LC_ALL=ZH_CN
SET INCLUDE=C:\PROGRA~1\SQLLIB\INCLUDE;C:\PROGRA~1\SQLLIB\LIB;C:\PROGRA~1\SQLLIB\TEMPLATES\INCLUDE
SET LIB=C:\PROGRA~1\SQLLIB\LIB
SET DB2PATH=C:\PROGRA~1\SQLLIB
SET DB2TEMPDIR=C:\PROGRA~1\SQLLIB
SET VWS_TEMPLATES=C:\PROGRA~1\SQLLIB\TEMPLATES
SET VWS_LOGGING=C:\PROGRA~1\SQLLIB\LOGGING
SET VWSPATH=C:\PROGRA~1\SQLLIB
SET VWS_FOLDER=IBM DB2
SET ICM_FOLDER=信息目錄管理器

適用於 Windows 98 環境下 DB2 7.x 安裝後 JDBC 錯誤或無法啟動的情況。