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:

 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: