Wednesday, June 5, 2019

Using Data Areas with SQL

retrieving data from data areas using 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:

 

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

11 comments:

  1. Thank you, I get what I wanted.

    ReplyDelete
  2. Do you know of a way to update the data area with sql?

    ReplyDelete
    Replies
    1. As it says above it is not possible to update a data area using SQL.

      You will have to use the CHGDTAARA command.

      Delete
  3. Hello,

    this works (in a total free RPG) under V7R3:

    exec sql
    CALL QSYS2.QCMDEXC('CHGDTAARA DTAARA(LIBNAME/DTAARANAME *ALL) +
    VALUE(''992030'')');


    PS: Thank you a lot for running this site. I got so many helpful information from here.

    ReplyDelete
  4. Edit to my answer. This is more useful. ;)

    dcl-s sqlDATE char(6) inz('');
    dcl-s sqlCMD varchar(1024) inz('');

    sqlDATE = '992040';
    sqlCMD = 'CHGDTAARA DTAARA(LIBNAME/DTAARANAME *ALL) +
    VALUE(''' + sqlDATE + ''')';

    exec sql
    CALL QSYS2.QCMDEXC(:sqlCMD);

    ReplyDelete
  5. Simon, thanks for sharing. Great read. Power 9 and 10 is rich with tools.. I have never considered reading a data area...

    ReplyDelete
  6. Good info. Thank you

    ReplyDelete
  7. Some useful info thanks

    ReplyDelete
  8. Viraj Nayanajith BaddewattageNovember 12, 2021 at 5:20 AM

    Good one thanks

    ReplyDelete
  9. I may have more than one data area with the same name in my library list. What I like about the table function is that it returns the value from the first data area in my library list. Thank you for this Simon!

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.