Beyond Excel: VBA and Database Manipulation

May 28 2013   1:23PM GMT

SQL, UDFs, and *DTAARA (Data Areas)

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Getting data from IBM’s iSeries into XL is normally no different than other platforms.  There are exceptions.  The iSeries’ multi-member files, such as source files, are problematic for SQL.  So are data areas.  This post deals with simple Data Areas.

To read simple Data Areas from SQL we need a little help from IBM’s iSeries.  We need a “stored procedure” which we must create, which means we must program on the iSeries. This post assumes you have access to your iSeries via a “green screen”.  If you don’t know what I’m talking about, bale now because this won’t make any sense to you.

 

Procedure:

I’m going to use an example to help explain things. In this example imagine we have a data area containing a numeric ID.  The steps to create this stored procedure are:

  • Code CL source to pass parameters and retrive data area’s contents
  • Create a CL Module from the code source
  • Create a Service Program from the CL Module.
  • Code an SQL Script to create a stored procdure using the Service Program
  • Run the SQL script

The first step is to write the CL source.  Let’s put it in member GetMyData in QGPL/QCLSRC.  Here is the example:

    PGM        PARM(&ID)                                                                                      
    DCL        VAR(&ID) TYPE(*DEC) LEN(10 0)                                                                                
    RTVDTAARA  DTAARA(MYDATA) RTNVAR(&ID)                                                                                                       
    ENDPGM

The next step is to create the CL Module.  At a command prompt type:

   CRTCLMOD  MODULE(QGPL/GetMyData) SRCFILE(QGPL/QCLSRC)

After the above executes succesfully, type this command:

  CRTSRVPGM SRVPGM(QGPL/GetMyData) MODULE(QGPL/GetMyData) EXPORT(*ALL)

Next we write our SQL Script.  Create source member GetMyData in QGPL/QDDSSRC:

  Create Function
    GetMyData()  
    Returns DECIMAL(10, 0)
    Language            CL
    Specific            GetMyData
    Deterministic
    No SQL
    Returns Null on Null Input
    No External Action
    Not Fenced
    External Name       'QGPL/GetMyData(GetMyData)'
    Parameter Style     SQL;

Lastly, we run the SQL script from a command line:

  RUNSQLSTM SRCFILE(QGPL/QDDSSRC) SRCMBR(GetMyData)

Now all that is left to do is test it.  Using command STRSQL to start an SQL session, run this SQL statement:

  Select GetMyData() From SYSIBM/SYSDUMMY1

SYSIBM/SYSDUMMY1 is a special one record file we can use for this sort of thing.

 

Summary:

As you can see, there’s not a lot of work involved once you know how.  And if you look carefully, you can see many possibilities for doing things far beyond reading simple data areas!

 

References:

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • fneurieser
    Thanks a lot for this quick and short procedure.

    kind regards
    Franz-Georg
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: