Blog

Posted on

a CONCAT_WS-like on #db2fori #ibmi

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 ;
Posted on

Automating execution environment image builds with GitHub Actions

Ansible Automation Platform 2 leverages containers dubbed automation execution environments which bundle in collection, python and platform dependencies to provide predictable, self-contained automation spaces that can be easily distributed across an organization.

In addition, Red Hat Ansible Automation Platform introduced tools such as execution environment builder, used to create execution environments, and automation content navigator, used to inspect images and execute automation within execution environments. These tools themselves are also highly automatable and can be included in workflows to automatically generate environments to support the execution of automation throughout the organization.

For this demonstration, let’s cut to film where I’ll walk through a demo scenario and verify along the way that we’re on the right track. Additionally, you can fork the repository for your own proof of concept.

 

Where to go next

If you’re ready to get hands-on, we have self-paced interactive labs available to explore new Ansible Automation Platform 2 technologies. If you are a Red Hat customer, please visit the Ansible Automation Platform 2 landing page in the Red Hat Customer Portal that consolidates all our documentation and guidance available to you. Please reach out to your local Red Hat representative to assist your organization in getting started with Ansible Automation Platform 2.0

Posted on

Contributing to the Eclipse BIRT project

This video explains how you can set up your development environment to contribute to the Eclipse BIRT project.

00:00 Intro
00:40 Fork the main BIRT repository
02:00 Download the Eclipse Installer
03:10 Start Installation
05:20 Start Eclipse
05:50 Automatically configure the development environment
09:00 Running BIRT with your changes
12:00 Running Unit Tests
14:00 Making and pushing a change
16:00 Creating a Pull Request

Verified by MonsterInsights