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:
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:
You can see more tips on accessing IBM i data areas through SQL at RPGPGM.com.
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'))
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')))