QAEZDISK - Example Queries

[DJM014]

Hi All,

We have a scheduled job which runs RTVDSKINF on a weekly basis over
the weekend when the system is not in use.

On Monday I run PRTDSKINF a number of times to produce reports, which
I take infomation from record in a spreadsheet.

The disk infomation collected is contained in file QGPL/QAEZDISK.

Has anyone got any samples of queries they have written in Query or
SQL which produce reports of library size / growth, % disk usage,
object size etc...

If I make a copy of the data every week into another file, I would
like to be able to compare the data week by week highlighting any
growth of a individual or group of libraries / object types (all user
libs, IBM, Journal reciever etc)

Any help would be appreciated.

Thanks,

Darren.

[MrDolom...@gmail.com]

From the field DIOBNM select on the value *TDSKSPC (yes, including the
leading *) to then have the total disk space of your system in bytes
be shown in the field DIOBSZ.

In Query/400, you can then use the > Define result fields to create a
disk usage percentage for each object.

Create a field called MAXBYTES and paste in the value you found
above. Mine was 879126118400

Create another field called PCTOFMAX and paste in this formula diobsz/
maxbytes*100

Include these fields on the > Select and sequence fields

Seq Field Text
Len Dec
10 DIOBLI Object Library
10
20 DIOBNM Object
12
30 DIOBTP Object
Type 7
40 DIOBAT Object
Attribute 9
50 DICDAT Last Changed Date: format-
yymmdd 6
60 DIOBSZ Object Size
15 0

[MrDolom...@gmail.com]

Ack, posted too soon.

Include these fields on the > Select and sequence fields

Seq Field Text
Len Dec
10 DIOBLI Object Library
10
20 DIOBNM Object
12
30 DIOBTP Object
Type 7
40 DIOBAT Object
Attribute 9
50 DICDAT Last Changed Date: format-
yymmdd 6
60 DIOBSZ Object Size
15 0

70 PCTOFMAX diobsz/maxbytes*100
20 2

Then change the sort order on the > Select sort fields

Sort
Prty A/D Field Text
Len Dec
10 D DIOBSZ Object Size
15 0

To eliminate some of the non-object records, use this > Select records

Field Test Value (Field, Number, '
DIOBTP NLIST '*SYS' '*SYSDAT' '*INT'

Now you will have a listing of everything in descending order, with
both the object size and its disk usage percentage.

Good luck.

On Jan 14, 10:25 am, DJM014 <djm...@hotmail.com> wrote:

[Jonathan Ball]

I've done some of that. What I did first was to create
a duplicate of QAEZDISK into another library. Then, I
added a column to it to contain the as-of date:

alter table disklib.qaezdisk
add column asof_date date default(current_date)

Then, in the job that runs RTVDSKINF, I added a CPYF
statement:

cpyf qgpl/qaezdisk disklib/qaezdisk mbropt(*add)
fmtopt(*map *drop)

The asof_date column will default to the current date
when the cpyf command runs. I'll have to look later
(not in the office at the moment) for the query I run
that shows the file growth by object over time.

[CRPence]

FWiW: So as not to have two copies of the file, if reporting by
PRTDSKINF is completed against the current file, then an alternative to
the CPYF is to move the existing file. In the case of the added field
scenario, let the ALTER set the default current date during its copy:

RNMOBJ QUSRSYS/QAEZDISK *FILE DSKI080113
MOVOBJ QUSRSYS/DSKI080113 *FILE MYLIB
ALTER TABLE MYLIB/DSKI080113

add column asof_date date default(current_date)

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

Jonathan Ball wrote:

> DJM014 wrote:
>>
>> We have a scheduled job which runs RTVDSKINF on a weekly basis over
>> the weekend when the system is not in use.
>>
>> On Monday I run PRTDSKINF a number of times to produce reports, which

>> I take information from record in a spreadsheet.
>>
>> The disk information collected is contained in file QGPL/QAEZDISK.

>>
>> Has anyone got any samples of queries they have written in Query or
>> SQL which produce reports of library size / growth, % disk usage,
>> object size etc...
>>
>> If I make a copy of the data every week into another file, I would
>> like to be able to compare the data week by week highlighting any
>> growth of a individual or group of libraries / object types (all user

>> libs, IBM, Journal receiver etc)

[Jonathan Ball]

On Jan 14, 10:23 am, CRPence <crpe...@vnet.ibm.com> wrote:
>    FWiW: So as not to have two copies of the file, if reporting by
> PRTDSKINF is completed against the current file, then an alternative to
> the CPYF is to move the existing file.  In the case of the added field
> scenario, let the ALTER set the default current date during its copy:
>
>    RNMOBJ QUSRSYS/QAEZDISK   *FILE DSKI080113
>    MOVOBJ QUSRSYS/DSKI080113 *FILE MYLIB
>    ALTER TABLE MYLIB/DSKI080113
>      add column asof_date date default(current_date)
>
> Regards, Chuck

Doesn't adding or dropping a column (or even changing its data type)
actually do a copy operation "under the covers"?

[CRPence]

Yes. The "during its copy" means to suggest during the copy
performed by the ALTER request; I really should avoid /it/ as my high
school English teacher suggested. Note however that the ALTER can
adjust its copy activity to limit the amount of storage required, where
CPYF will effect fully two copies [excepting COMPRESS(*YES); such that
if there are a large number of deleted records, it would be better to
use CPYF than MOVOBJ & ALTER].

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

[DJM014]

> > actually do a copy operation "under the covers"?- Hide quoted text -
>
> - Show quoted text -
Thanks for all of your advise guys, it has put on the right path....

I've created a query to calculate the total size of a library (total
size of all objects + the size of the library object itself)

1st Query = Using data from QAEZDISK, calculate total bytes for all
objects in a library to an Outfile1.
DSPOBJD OBJ(QSYS/*ALL) OBJTYPE(*LIB) to an Outfile2
2nd Query = Using Outfiles 1 & 2, Add total size of all objects + the
size of the library object itself

Problem is the total I get are not the same as the ones I receive if I
do a .....

DSPLIB *PRINT
or
PRTDSKINF *LIB report

????

[CRPence]

Unless the library and objects in the library are known to have
remained unchanged [no growth nor reduction of size, and no additional
objects nor fewer objects] since the RTVDSKINF was started, the sizes
will not match the DSPLIB.

The DSPLIB includes the size of the *LIB object. Thus a custom query
would need to do the same for comparison with that feature; as is noted
was being done. Consider however that given both valid data and a query
compatible with that data, adding the library size may not be all that
important. That really depends on what the data is to be used for.

Both the PRTDSKINF and a custom query would run against the same
static data created by RTVDSKINF; i.e. both would access the output file
QAEZDISK in QUSRSYS. So when the custom query is written to match the
query used by PRTDSKINF [exactly matching, or effectively, for the same
logic], the custom query and a PRTDSKINF RPTTYPE(*LIB) LIB(LibName)
OBJ(*ALL) OBJTYPE(*ALL) SORT(*SIZE) should match for total size. Again,
the custom query must account for the *LIB size in its totals, because
the PRTDSKINF report does -- or understand that would be different.

Additionally one must consider if using arithmetic expressions will
be summations rounded [or truncated] or will be detail expressions
rounded [or truncated] and then summed? Also consider, is the K [as in
KB] division by 1024 or by 1000?

To know what the actual query was, use a detail STRDBMON in the job
producing the report; or equivalent via OpsNav database interface, or
retrieving the active query by OpsNav or the ¿QUSRJOBI? API. The query
used by the report utility seems overly complex to me.

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

DJM014 wrote:
> <<SNIP>>