IP转换的函数！

在数据库的开发和管理过程中，时常用到需要查询链接对应的IP地址!
一般都是用这个命令db2 list applications,但是db2的这个命令有一个不合理的地方，就是他显示IP是以16进制的形式，
所以通常需要把进程对应的“应用程序标识”的小数点前的那个字符串，转换成10进制的数来检查IP。

于是乎我写了一个小过程，可以把那个字符串直接转换成ip地址的形式，不敢独享，现公布给大家。

程序如下：

-- 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)

ok，存储过程完成!下来测试下看看!

C:>db2 list applications

授权标识 应用程序名 应用程序 应用程序标识 DB 代理进程
句柄 名称 序号
-------- -------------- ---------- ------------------------------ -------- -----
CCP db2bp.exe 341 A9FE0B84.G710.033705103339 DW 1
CCP QuestCentral.e 325 A9FE0B84.G510.00F5C5102039 DW 1
CCP QuestCentral.e 323 A9FE0B84.G410.00F5C5102026 DW 1
CCP QuestCentral.e 302 A9FE0B84.C10F.00F5C5100723 DW 1
CCP QuestCentral.e 262 A9FE0B84.I50F.00F5C5093513 DW 1
CCP QuestCentral.e 258 A9FE0B84.I20F.00F5C5093332 DW 1
CCP QuestCentral.e 186 A9FE0B84.LF0E.00F5C5084450 DW 1
CCP clemlocal.exe 178 A9FE0BA6.B007.071115083616 DW 7
CCP QuestCentral.e 82 A9FE0B84.AE0C.00F5C5082400 DW 3
TEST db2jccWebConta 329 A9FE0B9E.F181.071115102030 CCP_BUSI 12
TEST QuestCentral.e 320 A9FE0B88.N00C.019845101725 CCP_BUSI 2
TEST QuestCentral.e 318 A9FE0B88.MD0C.019845101711 CCP_BUSI 1
TEST db2jccWebConta 317 A9FE0B9E.F081.071115101401 CCP_BUSI 11
TEST QuestCentral.e 313 A9FE0B88.G20C.019845101424 CCP_BUSI 1
TEST QuestCentral.e 309 A9FE0B88.F10B.019845101203 CCP_BUSI 1
TEST QuestCentral.e 306 A9FE0B88.E20B.019845100938 CCP_BUSI 3
TEST QuestCentral.e 298 A9FE0B88.A90B.019845100509 CCP_BUSI 10

C:>db2 list applications

授权标识 应用程序名 应用程序 应用程序标识 DB 代理进程
句柄 名称 序号
-------- -------------- ---------- ------------------------------ -------- -----
CCP db2bp.exe 341 A9FE0B84.G710.033705103339 DW 1
CCP QuestCentral.e 325 A9FE0B84.G510.00F5C5102039 DW 1
CCP QuestCentral.e 323 A9FE0B84.G410.00F5C5102026 DW 1
CCP QuestCentral.e 302 A9FE0B84.C10F.00F5C5100723 DW 1
CCP QuestCentral.e 262 A9FE0B84.I50F.00F5C5093513 DW 1
CCP QuestCentral.e 258 A9FE0B84.I20F.00F5C5093332 DW 1
CCP QuestCentral.e 186 A9FE0B84.LF0E.00F5C5084450 DW 1
CCP clemlocal.exe 178 A9FE0BA6.B007.071115083616 DW 7
CCP QuestCentral.e 82 A9FE0B84.AE0C.00F5C5082400 DW 3
TEST db2jccWebConta 329 A9FE0B9E.F181.071115102030 CCP_BUSI 13
TEST QuestCentral.e 320 A9FE0B88.N00C.019845101725 CCP_BUSI 2
TEST QuestCentral.e 318 A9FE0B88.MD0C.019845101711 CCP_BUSI 1
TEST QuestCentral.e 313 A9FE0B88.G20C.019845101424 CCP_BUSI 1
TEST QuestCentral.e 309 A9FE0B88.F10B.019845101203 CCP_BUSI 1
TEST QuestCentral.e 306 A9FE0B88.E20B.019845100938 CCP_BUSI 3
TEST QuestCentral.e 298 A9FE0B88.A90B.019845100509 CCP_BUSI 10

C:>db2 connect to dw

数据库连接信息

数据库服务器 = DB2/NT 8.2.0
SQL 授权标识 = DB2ADMIN
本地数据库别名 = DW

C:>db2 call CCP.GETIP('A9FE0B88',?)

输出参数的值
--------------------------
参数名： O_IP
参数值： 169.254.11.136

返回状态 = 0

C:>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/694276/viewspace-51461/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/694276/viewspace-51461/