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.
Index
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;
Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more
DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more
Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more
Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more
Sir, I can list *dta by running
SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS(‘DVSPCPV51′,’DTAARA’) ) AS X
But when I try to get value of a DTA by running
SELECT * FROM TABLE(QSYS2.DATA_AREA_INFO(‘DVSPCPV51′,’TM24HRSWT’)) as X
it return [SQL0204] DATA_AREA_INFO in QSYS2 type *N not found
I tried also
SELECT * FROM QSYS2.DATA_AREA_INFO WHERE DATA_AREA_NAME = ‘TM24HRSWT’ and DATA_AREA_LIBRARY = ‘DVSPCPV51’
It return [SQL0204] DATA_AREA_INFO in QSYS2 type *FILE not found
Do they mean I do not have some special auth to that table function or view?
Check the available SQL services for your release level by reading the SERVICES_INFO view
select SERVICE_CATEGORY “Categoria”, SERVICE_NAME “Nome servizio”, SERVICE_SCHEMA_NAME “Libreria”, SYSTEM_OBJECT_NAME “Nome sistema”,
SQL_OBJECT_TYPE “Tipo”,
EARLIEST_POSSIBLE_RELEASE concat ‘ PTF DB2 liv. ‘ concat coalesce(char(INITIAL_DB2_GROUP_LEVEL), ‘base’) “Prerequisito”,
replace(substr(EXAMPLE, 1, position(x’0D’, EXAMPLE) -1), ‘– Description: ‘, ”) “Descrizione breve”,
EXAMPLE “Descrizione completa”
from SERVICES_INFO
order by SERVICE_CATEGORY, SERVICE_NAME;
I don’t see why you are using OBJECT_STATISTICS since you can list the data areas using the DATA_AREA_INFO view.
In the last example (data areas QSAV*) I need to find the date and time of the last save