02a - SQL (EN)

Explore data areas with SQL

Last Updated on 22 March 2020 by Roberto De Pedrini

MarkOne’s suggestions – n. 6

In the large list of functions and views of the DB2 for i Service family, there are some that allow you to manage data area objects (* DTAARA) through SQL statements.

List the data areas

To obtain a list of the data areas present in a library, you can use the OBJECT_STATISTICS table function:

select OBJNAME "Name", OBJTEXT "Description", OBJOWNER "Owner", timestamp (OBJCREATED, 0) "Creation date / time", timestamp (CHANGE_TIMESTAMP, 0) "Date / time last mod.", timestamp (LAST_USED_TIMESTAMP, 0 ) "Last use date / time", DAYS_USED_COUNT "Use day"
   from table (QSYS2 / OBJECT_STATISTICS ('QUSRSYS', '* DTAARA'))
   order by OBJNAME;

Explore the content of data areas

The DATA_AREA_INFO view and the DATA_AREA_INFO table function allow us to find the content of a data area. That is, a method equivalent to the CL RTVDTAARA command and the QWCRDTAA API.

Find the contents of a single data area:

select rtrim (DATA_AREA_VALUE) "Value", DATA_AREA_TYPE "Type", LENGTH "Length", DECIMAL_POSITIONS "Dec."
   from table (QSYS2 / DATA_AREA_INFO (DATA_AREA_LIBRARY => '* LIBL', DATA_AREA_NAME => 'QSS1MRI'));

Find the contents of a list of data areas:

select DATA_AREA_NAME "Name", rtrim (DATA_AREA_VALUE) "Value", DATA_AREA_TYPE "Type", LENGTH "Length", DECIMAL_POSITIONS "Dec.", TEXT_DESCRIPTION "Description"
   from DATA_AREA_INFO
   where DATA_AREA_LIBRARY = 'QUSRSYS' and DATA_AREA_NAME in ('QINZPCSDA', 'QIZAVRM', 'QRCLSTG', 'QSMTPQ', 'QSS1MRI', 'QTMSCCHG')
   order by DATA_AREA_NAME;

The DATA_AREA_INFO table function also allows you to find the value of the local data area (* LDA). For example let’s try to modify the contents of the * LDA with:

call qcmdexc ('CHGDTAARA DTAARA (* LDA (10 5)) VALUE (' '12345' ')');

And then we display the content with:

select rtrim (DATA_AREA_VALUE) "Value"
   from table (QSYS2 / DATA_AREA_INFO (DATA_AREA_NAME => '* LDA'));

When were the last major bailouts made?

There are some data areas that give us information on the date / time of the last execution of important saves performed on IBM i (SAVSYS, SAVCFG …). Through DB2 for i service it is easy to consult the status with a simple SQL statement:

select OBJNAME "Name", OBJTEXT "Description", timestamp (SAVE_TIMESTAMP, 0) "Save date / time"
   from table (QSYS2 / OBJECT_STATISTICS ('QSYS', '* DTAARA'))
   where OBJNAME like 'QSAV%'
   order by OBJNAME;
Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

About author

IBM i's mentor IBM Champion 2021 for Power Systems

4 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *