IP转换的函数！

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

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

程序如下：

-- Start of generated script for 169.254.11.164-db2-DW (ccp)
`"p"u*|0g,u~6o0-- Nov-15-2007 at 17:35:12ITPUB个人空间Y&e1JK+H8R W)F
drop PROCEDURE CCP.GETIP;
.N DO.A J6~s4w0SET SCHEMA CCP ;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","CCP";

CREATE PROCEDURE CCP.GETIPITPUB个人空间,^.Lo.A].T
(IN I_IP VARCHAR(8), ITPUB个人空间_v,Sp'T^
OUT O_IP VARCHAR(20)ITPUB个人空间pB!b2z+N'u)B-O
)
#w s
eu,q
s0 LANGUAGE SQLITPUB个人空间J!O.rz)t&H1i
NOT DETERMINISTICITPUB个人空间7}!tKQ(W
CALLED ON NULL INPUTITPUB个人空间sv o#[`9f*PD%F!T
EXTERNAL ACTIONITPUB个人空间$TJz1A;]"|2opqH
OLD SAVEPOINT LEVEL
#sKJY}&u~?0 MODIFIES SQL DATAITPUB个人空间5j^D j,_Q$Ul
INHERIT SPECIAL REGISTERS
m~mF2Um7F`5L%j0 beginITPUB个人空间^&f
@I
|.~jF
/*
*Vr LO2l8N(J5b0 --auther:Z.X.TITPUB个人空间Zz3Y:FA.^6Q
--DATE:2007-11-15ITPUB个人空间#]5HI2~.v-y[
--描述，本过程可以把16进制的IP转换成一般的IP形式
PVSp7er u&Gm0 --I_IP 输入：长度为8的16进制数，如'AABBCCDD'
/j1eDZ9g)ZBlP[0 --O_IP 输出：ip地址形式如：192.168.1.0ITPUB个人空间`xxV-Rkb
*/ITPUB个人空间9P,jb~Iej1Y
declare first_IP1 varchar(3);ITPUB个人空间Uqeb/W
jjH
declare first_IP2 varchar(3);
4D ?$te,W:Y;\0B0 declare first_IP3 varchar(3);
'siv*a,U2V6y(X:u0 declare first_IP4 varchar(3);
%|q)A9CpS | Ua0 declare first_IP11 INTEGER;
eCs?1\2lD.N0 declare first_IP21 INTEGER;ITPUB个人空间B7E9_[/M8J2u^
declare first_IP31 INTEGER;
-`} [.t#}w
L^l0 declare first_IP41 INTEGER;
sd%aA]'A?nP|&u0 if(I_IP='*N0') then
]Me:I0e8v A&\ ^0 set O_IP ='本地';
q$E.n?KLzi0 return;
~|*r:H+a-if0 end if;
MlC6U/TT0 if(length(I_IP)!=8) thenITPUB个人空间"~&A;ybqLT3hY
set O_IP ='输入错误';ITPUB个人空间
kdos d1`8_c2?I
return;ITPUB个人空间&R*A#E;mF;x5M*G
end if;ITPUB个人空间5A|o&?~W!Q)P4^*Ed}
set first_IP1=Ucase(substr(I_IP,1,2));ITPUB个人空间y4b*tD(Z
set first_IP2=Ucase(substr(I_IP,3,2));ITPUB个人空间 sT2PA,itb
set first_IP3=Ucase(substr(I_IP,5,2));
0L LYj*o
v#?peB0 set first_IP4=Ucase(substr(I_IP,7,2));
o'fC!ryZ)T'AfB7O1j0 set first_IP11=INTEGER(case substr(first_IP1,1,1) when 'A' then '10' when 'B' then '11' when 'C' then '12' ITPUB个人空间(r`qOV#EZ
when 'D' then '13' when 'E' then '14' when 'F' then '15'
ASvU,VR0 when 'G' then '0' else substr(first_IP1,1,1) end)*16
#YT:du5^u*F0 +INTEGER(case substr(first_IP1,2,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'
V(S#tf`
jy:l0 when 'D' then '13' when 'E' then '14' when 'F' then '15'ITPUB个人空间TlK
k"M9m
when 'G' then '0' else substr(first_IP1,2,1) end);

m4sR"?d?Q0 set first_IP21=INTEGER(case substr(first_IP2,1,1) when 'A' then '10' when 'B' then '11' when 'C' then '12' ITPUB个人空间,x#GbY? I/t
h
when 'D' then '13' when 'E' then '14' when 'F' then '15'
tuc AW0_ H0 when 'G' then '0' else substr(first_IP2,1,1) end
$Y0?0L9ySOWWZH0 )*16ITPUB个人空间L)Z/E)@F
+INTEGER(case substr(first_IP2,2,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'ITPUB个人空间H
{/q
B4}B1rx
when 'D' then '13' when 'E' then '14' when 'F' then '15'ITPUB个人空间!C%e V3w'|5rSr
when 'G' then '0' else substr(first_IP2,2,1) end);

ITPUB个人空间0N7igN U
set first_IP31=INTEGER(case substr(first_IP3,1,1) when 'A' then '10' when 'B' then '11' when 'C' then '12' ITPUB个人空间Lx5k jTP(X,Z*z_
when 'D' then '13' when 'E' then '14' when 'F' then '15'ITPUB个人空间M*c+G!W]:E:w
when 'G' then '0' else substr(first_IP3,1,1) end
6R$j*np)z&Mi0 )*16ITPUB个人空间%vH#P,_0]/d.[
+INTEGER(case substr(first_IP3,2,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'ITPUB个人空间r)rUv&d ^l&li
when 'D' then '13' when 'E' then '14' when 'F' then '15'ITPUB个人空间:F9vv+~'^$vaI
when 'G' then '0' else substr(first_IP3,2,1) end);
f)E Y%L"_q%@%OH0 ITPUB个人空间m7TfZ8z1e.~
set first_IP41=INTEGER(case substr(first_IP4,1,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'

[
N h3f$nOz0 when 'D' then '13' when 'E' then '14' when 'F' then '15'
}D%ev{ zXW0 when 'G' then '0' else substr(first_IP4,1,1) end
W#Cr%LS v0 )*16
9c4[+hjz f.x\0 +INTEGER(case substr(first_IP4,2,1) when 'A' then '10' when 'B' then '11' when 'C' then '12'
&Lf3ex&x8s0 when 'D' then '13' when 'E' then '14' when 'F' then '15'ITPUB个人空间zg0h9s%A!`+hk!z
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));
Vsa ^3mR0end;

#SYNC 10;ITPUB个人空间1o$j-W#N^
commit;

-- End of generated script for 169.254.11.164-db2-DW (ccp)

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

_MG3o,C#?1H a0C:>db2 list applications

授权标识 应用程序名 应用程序 应用程序标识 DB 代理进程ITPUB个人空间O M5h nV.fhA%p1C {
句柄 名称 序号ITPUB个人空间#Q5G Ah5w6]
-------- -------------- ---------- ------------------------------ -------- -----ITPUB个人空间o'\s-_"x2H
CCP db2bp.exe 341 A9FE0B84.G710.033705103339 DW 1
$Q!yW9HLq"Ol4h9}#G~0CCP QuestCentral.e 325 A9FE0B84.G510.00F5C5102039 DW 1ITPUB个人空间ck K3a
y
CCP QuestCentral.e 323 A9FE0B84.G410.00F5C5102026 DW 1
)~ c KMPe;n} a?P e c0CCP QuestCentral.e 302 A9FE0B84.C10F.00F5C5100723 DW 1
c%OhbC!rs0CCP QuestCentral.e 262 A9FE0B84.I50F.00F5C5093513 DW 1
(u$\ U Cs(y0CCP QuestCentral.e 258 A9FE0B84.I20F.00F5C5093332 DW 1ITPUB个人空间Rv2Tf)g:Q%~
CCP QuestCentral.e 186 A9FE0B84.LF0E.00F5C5084450 DW 1
l7E#HR[.O-U0CCP clemlocal.exe 178 A9FE0BA6.B007.071115083616 DW 7
3}*ZQP1|Q.zJ"e0CCP QuestCentral.e 82 A9FE0B84.AE0C.00F5C5082400 DW 3ITPUB个人空间#i4] j/|tLp5I
TEST db2jccWebConta 329 A9FE0B9E.F181.071115102030 CCP_BUSI 12
'^2h;}X,d}^}(x*z0TEST QuestCentral.e 320 A9FE0B88.N00C.019845101725 CCP_BUSI 2ITPUB个人空间 fBm7v'A1JE9N5B
TEST QuestCentral.e 318 A9FE0B88.MD0C.019845101711 CCP_BUSI 1
i5|X;h-VQl|0TEST db2jccWebConta 317 A9FE0B9E.F081.071115101401 CCP_BUSI 11ITPUB个人空间X;J&@!`7ks2J5gBU
TEST QuestCentral.e 313 A9FE0B88.G20C.019845101424 CCP_BUSI 1ITPUB个人空间)eZC6J&{
TEST QuestCentral.e 309 A9FE0B88.F10B.019845101203 CCP_BUSI 1
r
?b(o7Bt0TEST QuestCentral.e 306 A9FE0B88.E20B.019845100938 CCP_BUSI 3
K-l.e1C6H%@/RF0TEST QuestCentral.e 298 A9FE0B88.A90B.019845100509 CCP_BUSI 10

/d { g3QN9j0C:>db2 list applications

授权标识 应用程序名 应用程序 应用程序标识 DB 代理进程
3@T2f:e$Ma(x0 句柄 名称 序号ITPUB个人空间|ZF\z6I
-------- -------------- ---------- ------------------------------ -------- -----ITPUB个人空间$]+vT'pn}%CD\Q-Fs
CCP db2bp.exe 341 A9FE0B84.G710.033705103339 DW 1ITPUB个人空间6y3G^ g~[8u$\p0{
CCP QuestCentral.e 325 A9FE0B84.G510.00F5C5102039 DW 1ITPUB个人空间"n l @ m;Sdn
CCP QuestCentral.e 323 A9FE0B84.G410.00F5C5102026 DW 1ITPUB个人空间 d$BfIs
CCP QuestCentral.e 302 A9FE0B84.C10F.00F5C5100723 DW 1ITPUB个人空间5d$k1k)j/C@"M9Y0NK
CCP QuestCentral.e 262 A9FE0B84.I50F.00F5C5093513 DW 1
g7m&h-P1n0CCP QuestCentral.e 258 A9FE0B84.I20F.00F5C5093332 DW 1ITPUB个人空间~4I lQ+K
CCP QuestCentral.e 186 A9FE0B84.LF0E.00F5C5084450 DW 1
a,wh@9LXap6R0CCP clemlocal.exe 178 A9FE0BA6.B007.071115083616 DW 7
iB5?~c|qDh0CCP QuestCentral.e 82 A9FE0B84.AE0C.00F5C5082400 DW 3ITPUB个人空间e
[Kxf _
TEST db2jccWebConta 329 A9FE0B9E.F181.071115102030 CCP_BUSI 13
J0aj$?6t+G!|0TEST QuestCentral.e 320 A9FE0B88.N00C.019845101725 CCP_BUSI 2
ihf _V4Hf WS0TEST QuestCentral.e 318 A9FE0B88.MD0C.019845101711 CCP_BUSI 1
n/zFq'AN$FM0TEST QuestCentral.e 313 A9FE0B88.G20C.019845101424 CCP_BUSI 1ITPUB个人空间U&N&KkA:]
TEST QuestCentral.e 309 A9FE0B88.F10B.019845101203 CCP_BUSI 1
6@(Ok2u3JeQvRH0TEST QuestCentral.e 306 A9FE0B88.E20B.019845100938 CCP_BUSI 3ITPUB个人空间j4fC/`N5GK`%u
TEST QuestCentral.e 298 A9FE0B88.A90B.019845100509 CCP_BUSI 10

ITPUB个人空间 a*H)Dl-UW
C:>db2 connect to dw

数据库连接信息

数据库服务器 = DB2/NT 8.2.0
^6_b,[NX FX0 SQL 授权标识 = DB2ADMIN
J2Bj!F)~aq:z0 本地数据库别名 = DW

ITPUB个人空间)^U N2@G*h6D]v
C:>db2 call CCP.GETIP('A9FE0B88',?)

输出参数的值ITPUB个人空间9Tm1\T[Oy"M#LV
--------------------------ITPUB个人空间:m5ci'dL:nR
参数名： O_IP
hxst#S}I'v m0 参数值： 169.254.11.136

返回状态 = 0

ITPUB个人空间,}rr)C(mgT
C:>

m#d^8S4~1W@0