DB2 implementation of INET_NTOA MySQL function

Here's a sample implementation of MySQL INET_NTOA function in DB2. If you are going to use this solution, you may need to ensure that valid values are passed. It doesn't make any validation of the input data provided.


CREATE OR REPLACE FUNCTION INET_NTOA (N BIGINT)
 RETURNS varchar(16)
 LANGUAGE SQL
 DETERMINISTIC
 NO EXTERNAL ACTION

BEGIN ATOMIC
DECLARE m1, tmp, i, j BIGINT default 0;
DECLARE ip varchar(16) default '';

 SET M1 = N;
 set i =0;
 WHILE (i < 4) DO   
    set j =0;
     WHILE (j < 8) DO
        if mod(m1,2) = 1 then
            set tmp = tmp + power(2, j);
        end if;
        set m1 = m1/2;
        set j = j + 1;
    END WHILE;
    set ip = tmp || '.' || ip;
    set tmp = 0;
    set i = i + 1;
 END WHILE;
 set ip = substr(ip, 1, length(ip) - 1);
 return ip;
END

Sample usage:
SELECT INET_NTOA(167773449) FROM DUAL

Output: 10.0.5.9

DB2 implementation of INET_ATON MySQL function

Here's a sample implementation of MySQL INET_ATON function in DB2. If you are going to use this solution, you may need to ensure that valid values are passed. It doesn't make any validation of the input data provided.

CREATE OR REPLACE FUNCTION INET_ATON (ip varchar(16))
 RETURNS integer
 LANGUAGE SQL
 DETERMINISTIC
 NO EXTERNAL ACTION

BEGIN ATOMIC
DECLARE a, b, c, d Integer default 0;
DECLARE N integer default 0;
declare x varchar(16);

 set x = ip;

 set a = substr(x, 1, locate('.', x));
 set x = substr(x, locate('.', x) + 1);
 set b = substr(x, 1, locate('.', x));
 set x = substr(x, locate('.', x) + 1);
 set c = substr(x, 1, locate('.', x));
 set x = substr(x, locate('.', x) + 1);
 set d = substr(x, 1);
 set N = a * power(2, 24) + b * power(2, 16) + c * power(2, 8) + d;

 return N;
END

DB2 SQL Error: SQLCODE=-1585, SQLSTATE=54048, SQLERRMC=null

Solving the subject error can be very easy or might be a bit tricky. If you know the exact query that causes this query, you have came a long way in solving it. If not, you will have to follow the steps we discuss later in this post.

The first thing I do when I have an error in DB2 is asking DB2 for more details about that error using db2 ? <> (i.e. db2 ? SQL1585N). DB2 tells "SQL1585N  A temporary table could not be created because there is no available system temporary table space that has a compatible page size."

You can be lucky and the query requires a system temp table space with page size 32K or less. Most of the times, a system temp table space is needed during the sort or joins. Creating the required table space will make your life much easier. You can check the available table spaces and their sizes using db2pd command. db2pd -d db_name -tablespace

Sometimes, you are not very lucky but you know the query. In that case, you may need to change the query. You may not be lucky at all -like me- and don't know the query causing this problem. First, we need to identify the query. Either using a snapshot or may be event monitor. Once you have got the query, get the access plan and detect the node causing the creation of temp table and try to optimize. Possible optimization may be dropping some columns from the selection list, truncate some character fields to shorter length ... etc

Finally, you will be able to make the query run successfully.
Good luck!

DB2 wm_concat equivalent, listagg

Most of the times, wm_concat aggregate function is equivalent to listagg agregate function. Although there are a few differences, you can use listagg to produce the same results of wm_concat. DB2 as of version 9.7 Fix Pack 4 has introduced listagg function. The usage is pretty simple. Consider the following example:

create table t1 (id int, name varchar2(20));
insert into t1 values (1,'a');
insert into t1 values (1,'b');
insert into t1 values (1,'c');
insert into t1 values (2,'x');
insert into t1 values (2,'y');
insert into t1 values (2,'z');

select id,wm_concat(name) from t1 group by id;

The result should something similar to:

        ID
----------
WM_CONCAT(NAME)
--------------------------------------------------------------------------------
         1
a,c,b

         2
x,y,z

Using listagg would produce the same exact results results.

However, there are a few differences between Oracle wm_concat and listagg:

1. listagg is a new function that has been added in Oracle 11g.
2. wm_concat allows distinct.
3. wm_concat is neither documented not supported. So, you should not be using wm_concat.

You should be using listagg instead of wm_concat. This is already supported by DB2 as of 9.7 FP 4. The equivalent syntax of listagg would be:

select id, listagg(distinct name, ',') within group (order by name) from t1 group by id

Another solution would be to use hierarchal queries which could be a bit more complex example. If you are interested, please post a comment and I'll respond to you.