Getting the IP address

[r***@gmail.com]

Hi Folks,

I need to get the ip address of a connection.

I've made the following query that works ok. But the result is in hexadecimal.

select substr(application_id(),1,8) from sysibm.sysdummy1 where substr(application_id(),2,5)<>'LOCAL' ;

Do anyone know how to convert the result from hexadecimal to decimal?
or
Is there any other way to get the ip address?

Thanks in advance,

[Knut Stolze]

Post by r***@gmail.com
Hi Folks,
I need to get the ip address of a connection.
I've made the following query that works ok. But the result is in hexadecimal.
select substr(application_id(),1,8) from sysibm.sysdummy1 where
substr(application_id(),2,5)<>'LOCAL' ;
Do anyone know how to convert the result from hexadecimal to decimal?
or
Is there any other way to get the ip address?

Here is an article that does pretty much the same as you did, but in C code.
We also explain how to convert the hex numbers to an IP address (and even
did a DNS lookup).

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0402greenstein/index.html

[r***@gmail.com]

I've made these two functions bellow that solved my issue.
Hope that could help anyone with the same situation.

HEXTODEC converts a two position hexadecimal number to decimal.

getip returns the ip of the connection.


select pru.getip() from sysibm.sysdummy1;

Cheers,

---------------------------------------
CREATE FUNCTION pru.HEXTODEC
(PHEXA CHARACTER(2)
)
RETURNS CHARACTER(3)
SPECIFIC pru.HEXTODEC
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE vsqlstate CHAR(5);
DECLARE vsqlcode INT;
DECLARE vDecimal CHARACTER(3);
declare vSub1 char(1);
declare vSub2 char(1);
set vSub1=substr(pHexa,1,1);
set vSub2=substr(pHexa,2,1);
set vDecimal=(
SELECT CAST(SUM(BIGINT(
CASE vSub1
WHEN 'A'
THEN '10'
WHEN 'B'
THEN '11'
WHEN 'C'
THEN '12'
WHEN 'D'
THEN '13'
WHEN 'E'
THEN '14'
WHEN 'F'
THEN '15'
ELSE vSub1
END) * POWER(16,BIGINT(2 - 1))) + SUM(BIGINT(
CASE vSub2
WHEN 'A'
THEN '10'
WHEN 'B'
THEN '11'
WHEN 'C'
THEN '12'
WHEN 'D'
THEN '13'
WHEN 'E'
THEN '14'
WHEN 'F'
THEN '15'
ELSE vSub2
END) * POWER(16,BIGINT(1 - 1))) AS CHAR(3))
from sysibm.sysdummy1);
RETURN vDecimal;
END;



CREATE FUNCTION pru.getIP ( )
RETURNS CHARACTER(15)
SPECIFIC pru.getIP
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE vsqlstate CHAR(5);
DECLARE vsqlcode INT;
declare vIP char(15);
declare vID char(8);
set vID=(
select substr(application_id(),1,8)
from sysibm.sysdummy1
where substr(application_id(),2,5)<>'LOCAL'
union
select substr(application_id(),2,5)
from sysibm.sysdummy1
where substr(application_id(),2,5)='LOCAL') ;
if vID <> 'LOCAL' then
SET vIP=(
select pru.HEXTODEC(substr(vID,1,2))||'.'|| pru.HEXTODEC(substr(vID,3,
2))||'.'|| pru.HEXTODEC(substr(vID,5,2))||'.'|| pru.HEXTODEC(
substr(vID,7,2))
from sysibm.sysdummy1);
else
set vIP=vID;
end if;
RETURN vIP;
END;