Using SQL UDF

[Former Member]

As we may know SAP IQ lack INET datatype and inet_ntoa/inet_aton functions as well. I wrote small SQL function which does conversion:

CREATE OR REPLACE FUNCTION inet_ntoa (IN ipaddr UNSIGNED INT)

  RETURNS CHAR(15)

DETERMINISTIC

BEGIN ATOMIC

    DECLARE one,two,three,four TINYINT;

    DECLARE RestOfIP UNSIGNED INT;

    DECLARE ret CHAR(15);

    SET one=ipaddr/POWER(2,24);

    SET RestOfIP=ipaddr-one*POWER(2,24);

    SET two=RestOfIP/POWER(2,16);

    SET RestOfIP=RestOfIP-(two*POWER(2,16));

    SET three=RestOfIP/POWER(2,8);

    SET four=RestOfIP-(three*256);

    SET ret=one || '.' || two || '.' || three || '.' || four;

    RETURN (ret);

END

but when I use that in select like:

SELECT inet_ntoa(dstaddr) AS dstaddr FROM table - execution on 56k rows is 10sec. When I try to achieve same result but without using UDF (embed those calcs in SELECT itself) execution took only 1 sec. But SQL looks realy awful and long. Is there anything that could be done?

SELECT

CAST(tr.dstaddr/POWER(2,24) AS TINYINT) || '.' ||

CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)/POWER(2,16) AS UNSIGNED INT) || '.' ||

CAST(CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)-CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)/POWER(2,16) AS UNSIGNED INT)*POWER(2,16) AS UNSIGNED INT)/POWER(2,8) AS UNSIGNED INT) || '.' ||

CAST(CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)-CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)/POWER(2,16) AS UNSIGNED INT)*POWER(2,16) AS UNSIGNED INT)-256*CAST(CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)-CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)/POWER(2,16) AS UNSIGNED INT)*POWER(2,16) AS UNSIGNED INT)/POWER(2,8) AS UNSIGNED INT) AS UNSIGNED INT) as dstaddr FROM table

[markmumy]

This is totally normal for IQ. A sql udf must move each rrow to the SA engine for processing then back to IQ. This is the design and quite costly, as you can see. You could try a java udf or best case a C udf, though the C udf requires a license.

Mark

[Former Member]

I've tried with Java:

java class:

public class IpNum2String {

    public static String convert(long ipaddr) {

        final long one = (ipaddr & 0b11111111000000000000000000000000L) >> 24;

        final long two = (ipaddr & 0b00000000111111110000000000000000L) >> 16;

        final long three = (ipaddr & 0b00000000000000001111111100000000L) >> 8;

        final long four = ipaddr & 0b00000000000000000000000011111111L;

        return "" + one + '.' + two + '.' + three + '.' + four;

    }

}

and successfully registered it within IQ:

INSTALL JAVA

NEW

FROM FILE 'c:\\DB\IpNum2String.class';

CREATE OR REPLACE FUNCTION Jinet_ntoa( IN arg1 INT)

RETURNS VARCHAR(15)

EXTERNAL NAME 'IpNum2String.convert(J)Ljava/lang/String;'

LANGUAGE JAVA;

but results timing was even worse than using SQL UDF. Any ideas?