Data Area (*DTAARA) using IBM i SQL

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.

So, how do we use some squirrel magic to read a *DTAARA via SQL?

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:

CRTDTAARA DTAARA(PROJEX4I/USEREMAIL)                           
           TYPE(*CHAR)                                       
           LEN(100)                                          
           VALUE('LITTENN   nick.litten@projex.com')         
           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:
 //  2015.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 2015.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)'
 Language RPGLE
 Disallow Parallel
 No SQL
 Parameter Style
 DB2SQL Deterministic 

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