• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

Quick Tip: Using an SQL-based data source to access a data area

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Quick Tip: Using an SQL-based data source to access a data area

    If you've taken the plunge into IBM i 7.4, or have TR6 installed on IBM i 7.3, then you have access to a new table function that makes it possible for you to access IBM i data area values using a simple SQL statement. This means you can create a NAB data source that pulls a single "record" from a data area.

    Let's craft a simple SQL statement to illustrate the concept: Each Valence instance has a simple data area that points to the "base" instance called VVBASEINST. If you wanted to pull this data area value in via an SQL statement, you could do it with the following code:
    Code:
    SELECT DATA_AREA_VALUE
      FROM TABLE(QSYS2.DATA_AREA_INFO(
               DATA_AREA_NAME => 'VVBASEINST',
               DATA_AREA_LIBRARY => '*LIBL'))
    Notice that the library is not hard-coded here but rather is using the library list (*LIBL). So it's important that the data area exists in one of the libraries in the Valence environment you're running, otherwise the statement will error out on a user-defined function exception (CPF503E) stemming from an SQL0204 "not found" error.

    Once you have confirmed this basic statement works, you can extend the concept to say, limit the records that are returned in a data source. For example:
    Code:
    SELECT *
      FROM VVINST
     WHERE VVINSTANCE IN (SELECT DATA_AREA_VALUE
                            FROM TABLE(QSYS2.DATA_AREA_INFO(
                                     DATA_AREA_NAME => 'VVBASEINST',
                                     DATA_AREA_LIBRARY => '*LIBL')))
    You can see more tips on accessing IBM i data areas through SQL at RPGPGM.com.
    Last edited by robert.swanson; 08-05-2019, 05:09 PM.
Working...
X