November, 2021 - Built on Power

In July, I left a position as a data manager on SQL Server (and a bit of MySQL) to start as a database developer on… Db2 … for i. I’m now going through a paradigm shift and transfer some knowledge to the platform.

I had a use case for ‘CONCAT_WS‘. Concatenate 0, 1 or more text with a separator. It nicely deals with the separator if any value is NULL (no need for some CONCAT/COALESCE salad)

So I gave it a naive try (how knows…)

create table testdata(
ref int primary key,
msg1 varchar(50) default null,
msg2 varchar(50) default null,
msg3 varchar(50) default null,
expected varchar(200) default null
)
;
insert into testdata(ref, msg1, msg2, msg3, expected)
values
(1, ‘no match’, ‘duplicated record’, ‘conversion error’, ‘no match | duplicated record | conversion error’),
(2, ‘no match’, NULL, ‘conversion error’, ‘no match | conversion error’),
(3, NULL, NULL, NULL, NULL)
;
select td.ref, td.expected,
concat_ws(‘ | ‘, td.msg1, td.msg2, td.msg3) as observed
from testdata as td
;

… Failed and no trace of such a function in the documentation. Maybe an alternative trick, then? Use an aggregate function (LISTAGG for current the task) in combination with VALUES and LATERAL JOIN (or Sub-select). Second try:

select td.ref, agg.observed, td.expected
from testdata as td
cross join lateral (
select list_agg(msg, ‘ | ‘) as observed
from (VALUES (td.msg1), (td.msg2), (td.msg3)) as tlong(msg)) as agg
;

SQL-Status: 42703
Anbietercode: -206
Nachricht: [SQL0206] Column or global variable MSG3 not found. Cause . . . . . : MSG3 was not found as a column of table *N in *N and was not found as a global variable in *N. If the table is *N, MSG3 is not a column of any table or view that can be referenced, or MSG3 is a special register that cannot be set in an atomic compound statement. Recovery . . . : Do one of the following and try the request again: — Ensure that the column and table names are specified correctly in the statement. — If this is a SELECT statement, ensure that all the required tables were named in the FROM clause. — If the column was intended to be a correlated reference, qualify the column with the correct table designator. — If the column was intended to be a global variable, qualify the name with the schema where the global variable exists or ensure the schema is in the path. — If this is a SET statement for a special register within an atomic compound dynamic statement, remove the statement or remove the ATOMIC keyword.

Nope! I did qualify my correlated columns, so why do you Db2 block it in a LATERAL? Some limitation? But with some patience, I went for an UDFs (function code at the end):

with test as (
select td.ref, td.expected,
concat_ws_allownull(‘ | ‘, td.msg1, td.msg2, td.msg3) as observed
from testdata as td
)
select test.ref, test.expected, test.observed,
case when test.expected is not distinct from test.expected
then ‘Succeed’ else ‘Failed’ end as test_result
from test
;

Yeah!

Not quite the real CONCAT_WS, but I learned a bit more about SQL on Db2 for i, had some fun and now have this tool back.

CREATE OR REPLACE FUNCTION CONCAT_WS_ALLOWNULL (
SEP VARCHAR(10) ,
S1 VARCHAR(4000) ,
S2 VARCHAR(4000) DEFAULT NULL ,
S3 VARCHAR(4000) DEFAULT NULL ,
S4 VARCHAR(4000) DEFAULT NULL ,
S5 VARCHAR(4000) DEFAULT NULL )
RETURNS VARCHAR(4000)
LANGUAGE SQL
SPECIFIC CONWSALNUL
DETERMINISTIC
BEGIN
RETURN (
SELECT LISTAGG ( Z . TXT, SEP )
FROM ( VALUES
( S1 ) , ( S2 ) , ( S3 ) , ( S4 ) , ( S5 )
) AS Z ( TXT )
WHERE Z.TXT IS NOT NULL
/* This WHERE statement helped with
* a CEE9901 Error due to too
* many message caused by NULLs on *listagg*
* input (related with SQL State 01003)
* */
) ;
END ;

Month: November 2021

How to Optimize Your IBM i Web Applications Using FastCGI #IBMi #PHP #nodejs #python

After my “400 arguments for #ibmi” session here in Dortmund, a person came up to me asking “are you the guy behind the daily newsletters?, I am new to the platform since 3 months and it really helps me keeping myself updated”. well, that made my day for sure!!

IBM i Developer Day in the news: #ibmiday

#IBMi

a CONCAT_WS-like on #db2fori #ibmi