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:
**FREE // program i.d. ……. MYSRVPGM // function ……….. Read Various Source with User Defined Functions to rtn via SQL // // History: // 219.03.04 Created ctl-opt debug option(nodebugio:srcstmt) datfmt(iso-) fixnbr(zoned:inputpacked) indent('| ') timfmt(iso.) truncnbr(yes) expropts(resdecpos) stgmdl(*TERASPACE) copyright('| MYSRVPGM 2019.03.04') nomain; // 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'); user char(10); email char(90); end-ds; 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 // Deterministic // --- Function TSTDTAARA was created in MYLIBRARY. // // To use the function in SQL: // select * from table(MYLIBRARY/tstdtaara()) as tmp dcl-proc GETDTAARA export; dcl-pi GETDTAARA; // Table Function Input Parameters p_user char(10); p_email char(90); // Null Indicator for each Input Parameter p_user_NI int(5); p_email_NI int(5); // DB2SQL Style Parameters p_SQLState char(5); p_FunctionName char(517); p_SpecificName char(128); p_SQLMsgText varchar(70); // UDTF Call Type p_TFCallType int(10); end-pi; Monitor; p_SQLState = SQLStateOK; // Function Open if p_TFCallType = UDTF_Open; In(e) dtaara; NullData = %Error; FirstFetch = *on; // Function Fetch -> Return data elseif p_TFCallType = UDTF_Fetch; if FirstFetch; FirstFetch = *off; if not NullData; p_user = dtaara.user; p_email = dtaara.email; p_user_NI = NOTNULL; p_email_NI = NOTNULL; Else; p_user_NI = ISNULL; p_email_NI = ISNULL; endif; else; p_SQLState = ENDOFTABLE; endif; // Function Close -> Cleanup Work elseif p_TFCallType = UDTF_Close; *inlr = *On; EndIf; On-Error; *inlr = *On; p_SQLState = UDTF_ERROR; Endmon; Return; end-proc;
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 Install IBM Access Client Solutions (ACS)
IBM i Data Obfuscation – Making Data Foggy Murky and Squinty
How to copy IBM i IFS folder
IFS File system error occurred. Error number 3021?
How to rename IFS Files
How to rename Fresche (BCD) Presto Library – XL_PRESTO
Using TURNOVER in an IBM i iASP Environment
Preparing to implement IBM i iASP (Independent Auxiliary Storage Pool)
How to move IFS from *SYSBAS into iASP (Independent ASP)