Explore data areas with SQL

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;