Our first problem is, SQL doesn’t know what a *DTAARA is.
SQL reads data from Tables but our gloriously quirky IBM i Box has other places to secretly store data. Data Areas are just one of these tricky little blocks of data that other machines don’t know about – think of them as a single row table which can be created, updated and deleted from the command line. Data areas contain long strings of data. They are not delimited.
The answer is simple, using a SQL Table Function aka User Defined Table Function (UDTF).
In this case we will write a simple RPGLE procedure – which is readable via a SQL User Defined Function – to retrieve the value from a data area.
Once we have the value, we can do whatever we want with it.
Obviously this idea would also work with reading other non-table data types.. perhaps IFS? Journal Receivers? Messages Queues? But let’s dive into these later…my brain
Lets create a simple data area:
TEXT('SAMPLE: Example Data Area for DTRAUDF1 Code Sample')
This gives us an imaginatively named data area called “USEREMAIL” in library “PROJEX4I”
It contains character data to a max length of 100 and is initialized with a default value of a ten char ‘user name‘ and then 90 chars of ‘email address‘:
I found a nice old code example from Ted Holt back in May 2004, and since plagiarism is the highest form flattery, I’ve quickly stolen the code base and made a few minor tweaks.
This RPGLE service program will read my sample data area and return the username and email address to the caller. Obviously you could update this example to align with any data area layout you want:
Here I have this procedure defined in a service program called @SRVUDF:
// program i.d. ……. MYSRVPGM
// function ……….. Read Various Source with User Defined Functions to rtn via SQL
// 219.03.04 Created
datfmt(iso-) fixnbr(zoned:inputpacked) indent('| ') timfmt(iso.)
copyright('| MYSRVPGM 2019.03.04')
// UDTF call parameter constants
dcl-s UDTF_FirstCall int(10) inz(-2);
dcl-s UDTF_Open int(10) inz(-1);
dcl-s UDTF_Fetch int(10) inz(0);
dcl-s UDTF_Close int(10) inz(1);
dcl-s UDTF_LastCall int(10) inz(2);
// SQL States
dcl-c SQLSTATEOK '00000';
dcl-c ENDOFTABLE '02000';
dcl-c UDTF_ERROR 'US001';
// NULL Constants
dcl-c ISNULL -1;
dcl-c NOTNULL 0;
dcl-ds dtaara qualified dtaara('MYLIBRARY/USEREMAIL');
dcl-s FirstFetch ind;
dcl-s NullData ind;
// GETDTAARA - Read a Data Area and return via UDF like a single rcd file
// To create an SQL function:
// create or replace function MYLIBRARY/tstdtaara() returns table(name char(10),
// email Char(90)) External Name 'MYLIBRARY/MYSRVPGM(GETDTAARA)'
// Language RPGLE Disallow Parallel No SQL Parameter Style DB2SQL
// --- Function TSTDTAARA was created in MYLIBRARY.
// To use the function in SQL:
// select * from table(MYLIBRARY/tstdtaara()) as tmp
dcl-proc GETDTAARA export;
// Table Function Input Parameters
// Null Indicator for each Input Parameter
// DB2SQL Style Parameters
// UDTF Call Type
p_SQLState = SQLStateOK;
// Function Open
if p_TFCallType = UDTF_Open;
NullData = %Error;
FirstFetch = *on;
// Function Fetch -> Return data
elseif p_TFCallType = UDTF_Fetch;
FirstFetch = *off;
if not NullData;
p_user = dtaara.user;
p_email = dtaara.email;
p_user_NI = NOTNULL;
p_email_NI = NOTNULL;
p_user_NI = ISNULL;
p_email_NI = ISNULL;
p_SQLState = ENDOFTABLE;
// Function Close -> Cleanup Work
elseif p_TFCallType = UDTF_Close;
*inlr = *On;
*inlr = *On;
p_SQLState = UDTF_ERROR;
This program will be called multiple times by the SQL Function. This is explained by the OPEN, FETCH and CLOSE in the GETDTAARA subprocedure.
Once this is compiled as a service program we just need to create an SQL function to evoke that function:
create or replace function MYLIBRARY/TSTDTAARA() returns
table(name char(10), email Char(90))
External Name 'MYLIBRARY/MYSRVPGM(GETDTAARA)'
then every time we read using this function it will return the values we have defined just as if it were reading a single row table:
select * from table(MYLIBRARY/TSTDTAARA ()) as tmp
And thats that.
Now lets expand the same bit of code to add the ability to return any data area as a single long string – Blog in Progress
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.
How to refresh the Pitney Bowes CODE1 Address Database on IBM I without using FTP
Update IBM i IFS Objects Data Change Timestamp
How to read a Data Area (*DTAARA) using IBM i SQL
How to update RDi – Rational Developer for IBM ‘i’ Programmers
‘device file does not contain an entry for screen size’
Funky IBM i Email Validation Program using SQL Regex
+Happy New Year 2018
#IBMi Java – Avoiding the QSH “Press ENTER to end terminal session” message
Handling Fat screens in RPG with IBM i API’s QsnQryModSup and QuiLngTx