|
在数据库的开发和管理过程中,时常用到需要查询链接对应的IP地址!
一般都是用这个命令db2 list applications,但是db2的这个命令有一个不合理的地方,就是他显示IP是以16进制的形式,
所以通常需要把进程对应的“应用程序标识”的小数点前的那个字符串,转换成10进制的数来检查IP。
于是乎我写了一个小过程,可以把那个字符串直接转换成ip地址的形式,不敢独享,现公布给大家。
程序如下:
[php]
-- Start of generated script for 169.254.11.164-db2-DW (ccp)
-- Nov-15-2007 at 17:35:12
drop PROCEDURE CCP.GETIP;
SET SCHEMA CCP ;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","CCP";
CREATE PROCEDURE CCP.GETIP
(IN I_IP VARCHAR(8),
OUT O_IP VARCHAR(20)
)
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL ACTION
OLD SAVEPOINT LEVEL
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
begin
/*
--auther:Z.X.T
--DATE:2007-11-15
--描述,本过程可以把16进制的IP转换成一般的IP形式
--I_IP 输入:长度为8的16进制数,如'AABBCCDD'
--O_IP 输出:ip地址形式如:192.168.1.0
*/
declare first_IP1 varchar(3);
declare first_IP2 varchar(3);
declare first_IP3 varchar(3);
declare first_IP4 varchar(3);
declare first_IP11 INTEGER;
declare first_IP21 INTEGER;
declare first_IP31 INTEGER;
declare first_IP41 INTEGER;
if(I_IP='*N0') then
set O_IP ='本地';
return;
end if;
if(length(I_IP)!=8) then
set O_IP ='输入错误';
return;
end if;
set first_IP1=Ucase(substr(I_IP,1,2));
set first_IP2=Ucase(substr(I_IP,3,2));
set first_IP3=Ucase(substr(I_IP,5,2));
set first_IP4=Ucase(substr(I_IP,7,2));
set first_IP11=INTEGER(case substr(first_IP1,1,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'
when 'D' then '13' when 'E' then '14' when 'F' then '15'
when 'G' then '0' else substr(first_IP1,1,1) end)*16
+INTEGER(case substr(first_IP1,2,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'
when 'D' then '13' when 'E' then '14' when 'F' then '15'
when 'G' then '0' else substr(first_IP1,2,1) end);
set first_IP21=INTEGER(case substr(first_IP2,1,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'
when 'D' then '13' when 'E' then '14' when 'F' then '15'
when 'G' then '0' else substr(first_IP2,1,1) end
)*16
+INTEGER(case substr(first_IP2,2,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'
when 'D' then '13' when 'E' then '14' when 'F' then '15'
when 'G' then '0' else substr(first_IP2,2,1) end);
set first_IP31=INTEGER(case substr(first_IP3,1,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'
when 'D' then '13' when 'E' then '14' when 'F' then '15'
when 'G' then '0' else substr(first_IP3,1,1) end
)*16
+INTEGER(case substr(first_IP3,2,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'
when 'D' then '13' when 'E' then '14' when 'F' then '15'
when 'G' then '0' else substr(first_IP3,2,1) end);
set first_IP41=INTEGER(case substr(first_IP4,1,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'
when 'D' then '13' when 'E' then '14' when 'F' then '15'
when 'G' then '0' else substr(first_IP4,1,1) end
)*16
+INTEGER(case substr(first_IP4,2,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'
when 'D' then '13' when 'E' then '14' when 'F' then '15'
when 'G' then '0' else substr(first_IP4,2,1) end);
set O_IP=rtrim(char(first_IP11))||'.'||rtrim(char(first_IP21))||'.'||rtrim(char(first_IP31))||'.'||Rtrim(char(first_IP41));
end;
#SYNC 10;
commit;
-- End of generated script for 169.254.11.164-db2-DW (ccp)
----
[/php] |
|