Using Data Areas with SQL

The latest technology refresh, IBM i 7.3 TR6, and the new release. IBM i 7.4, brought us a new view and table function that allows us to retrieve information from data areas. The closest thing we have had to this before is the Retrieve Data Area command, RTVDTAARA, but these gives us more than just the value held in the data area. The only down side with these being a view and table function is I cannot update the data area using them.

They both have the same name, DATA_AREA_INFO, and returned columns have the same names. The only difference is that the view has two additional columns. If I wanted to retrieve the information for just one data area I would use the table function. The view will list all data areas that fit the selection criteria. I could still get the information for the one data area using the view, but it is faster using the table function.

I am not going to list all the columns in this post. Click on the links at the bottom of this post to go to IBM's documentation to get the complete list.

 

DATA_AREA_INFO view

If I wanted to list all of the data areas in a library, along with their contents, or if I wanted to search the data areas for a particular snippet of data, I would use the DATA_AREA_INFO view.

In its simplest form I could use this to get a list of all the data areas I am authorized to and in libraries I am also authorized to too.

01 SELECT * FROM QSYS2.DATA_AREA_INFO

But this will give me a list of all the data areas in this IBM i partition, which is too much information. Therefore, let me narrow this down to a list of the data areas in my personal library:

01 SELECT DATA_AREA_NAME,DATA_AREA_TYPE, 02 LENGTH,DECIMAL_POSITIONS, 03 DATA_AREA_VALUE,TEXT_DESCRIPTION 04 FROM QSYS2.DATA_AREA_INFO 05 WHERE DATA_AREA_LIBRARY = 'MYLIB'

I have selected some of the columns, I am not going to describe what each one contains as the long names adequately describe them. As I given the name of the library in the WHERE clause it seems redundant to include it in the results.

DATA_AREA_NAME DATA_AREA_TYPE LENGTH DECIMAL POSITIONS TESTDA1 *CHAR 100 - TESTDA2 *DEC 12 2 DATA_AREA_VALUE TEXT_DESCRIPTION This is for testing Test data area 1 123.45 Test data area 2

DECIMAL_POSITIONS contains a null for character columns, as they do not have decimal places.

DATA_AREA_VALUE is a variable character column that is 2,000 characters long. I have only shown a small part of it above. If I was to use it in a program I would use the CAST to redefine it with a shorter length.

I think that the way I will be using this view is when I know there is a data area that contains a value and I cannot remember what it is called. An example would be the data area that contains the version and release that this IBM i partition is using. I can remember that the data area starts with "V7R3", therefore, I want to search all libraries and my SQL statement would be:

01 SELECT DATA_AREA_LIBRARY,DATA_AREA_NAME 02 FROM QSYS2.DATA_AREA_INFO 03 WHERE DATA_AREA_VALUE LIKE 'V7R3%'

The LIKE clause is the wildcard that will return any data area where its value starts with "V7R3".

As I remembered there are two data areas that contain this information:

DATA_AREA_LIBRARY DATA_AREA_NAME QGPL QSS1MRI QUSRSYS QSS1MRI

 

DATA_AREA_INFO table function

If I want to retrieve the contents of a data area it is more efficient to use the DATA_AREA_INFO table function as I pass the name of the data area and the library it is in as parameters. Therefore, Db2 for i does not have to search for the data area as it does with the view.

01 SELECT DATA_AREA_VALUE 03 FROM TABLE(QSYS2.DATA_AREA_INFO( 04 DATA_AREA_NAME => 'TESTDA1', 05 DATA_AREA_LIBRARY => '*LIBL'))

Lines 3 - 5: This where I need to define the table function and the parameters I am passing to it.

The above statement returns the value from the data area:

DATA_AREA_VALUE This is for testing

All of the examples I have given so far are OK if I am using a SQL client to retrieve the data from the data area(s). But I want a "real world" scenario using this in a RPG program.

Here is an example retrieving the contents of the first data area: TESTDA1.

01 **free 02 dcl-s Value char(100) ; 03 exec sql SELECT DATA_AREA_VALUE 04 INTO :Value 05 FROM TABLE(QSYS2.DATA_AREA_INFO( 06 DATA_AREA_NAME => 'TESTDA1', 07 DATA_AREA_LIBRARY => '*LIBL')) ; 08 *inlr = *on ;

Line 1: I always use totally free RPG.

Line 2: I have defined this value, VALUE, to contain the contents of the data area.

Lines 3 – 7: This is the SQL statement to retrieve the contents of the data area.

Line 3: I am only interested in the column, DATA_AREA_VALUE.

Line 4: I am retrieving the contents of the data area into the program variable, Value.

Line 5 – 7: This is my definition for the DATA_AREA_INFO table function, for the data structure TESTDA1.

After the SQL statement is executed Value contains: "This is for testing"

TESTDA2 is a decimal (numeric) data area.

01 **free 02 dcl-s Value packed(12:2) ; 03 exec sql SELECT DATA_AREA_VALUE 04 INTO :Value 05 FROM TABLE(QSYS2.DATA_AREA_INFO( 06 DATA_AREA_NAME => 'TESTDA2', 07 DATA_AREA_LIBRARY => '*LIBL')) ; 08 *inlr = *on ;

Line 2: This time Value is a packed variable that matches the definition of the data area.

All I changed in the SQL statement was the name of the data area, TESTDA2.

After the SQL statement has executed Value contains: 0000000123.45

I have to admit I was surprised that the value retrieved from the character DATA_AREA_VALUE was moved into the packed decimal Value without error.

There are three other data areas I have not yet mentioned:

1. *LDA local data area
2. *GDA group data area
3. *PDA program initialization parameter data area for prestart jobs

Here I am going to work with the *LDA. I have used it to put various pieces of "standard" information that applies to this user and job, and any other jobs it may submit to batch. For example:

• The language to display information to the user
• Date format to present the date to the user
• Default printer
• User's default company

This is my example *LDA. I am sure you can determine where the above data elements start and end in the *LDA below.

Display Data Area Data area . . . . . : *LDA Type . . . . . . . : *CHAR Length . . . . . . : 1024 Text . . . . . . . : *LDA for Job 513547/SIMON/QPADEV0001 Value Offset *...+....1....+....2....+ 0 'ENUSAPRT01 001

The program, below, is pretty much the same as the other two I have shown. The only difference is that I have used substring, SUBSTR, to parse the pieces of data I want from the DATA_AREA_VALUE column.

01 **free 02 dcl-s Language char(2) ; 03 dcl-s DateFormat char(3) ; 04 dcl-s DefaultPrinter char(10) ; 05 dcl-s DefaultCompany char(3) ; 06 exec sql SELECT SUBSTR(DATA_AREA_VALUE,1,2), 07 SUBSTR(DATA_AREA_VALUE,3,3), 08 SUBSTR(DATA_AREA_VALUE,6,10), 09 SUBSTR(DATA_AREA_VALUE,16,3) 10 INTO :Language, 11 :DateFormat, 12 :DefaultPrinter, 13 :DefaultCompany 14 FROM TABLE(QSYS2.DATA_AREA_INFO( 15 DATA_AREA_NAME => '*LDA', 16 DATA_AREA_LIBRARY => '*LIBL')) ; 17 dsply (Language + ', ' + DateFormat + ', ' + 18 %trimr(DefaultPrinter) + ', ' + 19 DefaultCompany) ; 20 *inlr = *on ;

Lines 2 – 5: Definitions for the variables that will contain the data extracted from DATA_AREA_VALUE.

Lines 6 – 9: Substring-ing (is that a real word?) the data in DATA_AREA_VALUE.

Lines 10 - 13: These are the variables that the substrung (is that a real word too?) data will be placed into.

Line 15: I give the data area's name as *LDA just as I would any other data area.

Lines 17 – 19: The Display operation code, DSPLY, is only here so I can display the results I retrieved. Which is:

DSPLY EN, USA, PRT01, 001

 

Would I use either of the DATA_AREA_INFO?

I think the view will be useful when I need to find that data area that contains something like the next order number, and a way to compare the values of the same named data area in different libraries.

As I convert my thinking, and my code, from just doing things in RPG versus incorporating SQL, I can see myself using the table function to retrieve data, rather than use the conventional RPG way to retrieve from data areas. I just think it is a shame there is not a way to update data areas just using SQL.

 

You can learn more about this from the IBM website:

• DATA_AREA_INFO table function
• DATA_AREA_INFO view

 

This article was written for IBM i 7.4 and 7.3 TR6.