@Satid Singkorapoom, I used this as the baseline for inclusion. I handled the null last use date with coalesce. IfNull only allows for the test value and the single option.
SELECT SYSTEM_TABLE_SCHEMA,INDEX_NAME,SYSTEM_TABLE_NAME,
IFNULL(INDEX_TEXT, ' ') TEXT,INDEX_SIZE,
COALESCE(LAST_USED_TIMESTAMP,
CREATE_TIMESTAMP,
TIMESTAMP('0001-01-01-00.00.00.000000')) LAST_USE,
TIMESTAMPDIFF (16, CHAR(CURRENT TIMESTAMP –
COALESCE(LAST_USED_TIMESTAMP,
CREATE_TIMESTAMP,
TIMESTAMP('0001-01-01-00.00.00.000000')))) LAST_USE_DAYS
FROM QSYS2.SYSPARTITIONINDEXES
WHERE LEFT(SYSTEM_TABLE_NAME,2) = 'GL'
AND SYSTEM_TABLE_SCHEMA IN ('LIBRARY1','LIBRARY2','LIBRARY3')
AND TIMESTAMPDIFF (16, CHAR(CURRENT TIMESTAMP –
COALESCE(LAST_USED_TIMESTAMP,
CREATE_TIMESTAMP,
TIMESTAMP('0001-01-01-00.00.00.000000'))))
>= 1900
ORDER BY SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, INDEX_NAME;
------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX
------------------------------
Original Message:
Sent: Wed February 14, 2024 09:05 PM
From: Satid Singkorapoom
Subject: A Logical File Takes Up Space or Not?
Dear Davis
One last point I would like to make here is that I realize that Index Evaluator tool GUI is limited in its use on a table at a time. One more efficient way for you to get a system-wide list of potentially useless keyed LFs/indexes is to do a metadata query to QSYS2.SYSINDEXSTAT ( https://www.ibm.com/docs/en/i/7.4?topic=views-sysindexstat ). A sample query is here :
select index_name, index_schema, table_schema, table_name, owning_index_type , index_create_timestamp, last_query_use, last_used_timestamp, index_size
from qsys2.sysindexstat
where last_query_use is NULL and last_used_timestamp is NULL or <last_query_use and last_use_timestamp are more than, say, 300 days into the past>
I do not know how to write the last part above and would be much obliged if any kind soul would help chime in on this.
------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
Original Message:
Sent: Wed February 14, 2024 08:21 AM
From: David Taylor
Subject: A Logical File Takes Up Space or Not?
@Birgitta Hauser, yes, this is step one. I will outline the steps to include where is the LF used and when was the last time each related program was called. Once I know both the file and the programs consuming it are no longer in use, then it will go on the delete list. I greatly appreciate all the valuable input from all y'all. To document the process and the justification for each file will take time, but the savings in disk space and processing time will be well worth it. I plan to do this in groups to be less disruptive to normal workflow.
------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX
Original Message:
Sent: Wed February 14, 2024 02:16 AM
From: Birgitta Hauser
Subject: A Logical File Takes Up Space or Not?
... but before you delete this logical file make sure it is not used in any program in composition with nativa I/O. The Last Query Used (and the other) columns shows only the use by SQL.
------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964
Original Message:
Sent: Tue February 13, 2024 09:36 PM
From: Satid Singkorapoom
Subject: A Logical File Takes Up Space or Not?
Dear David
>>>> Does this file really take up that much space? Will I recoup that space by deleting this obsolete object? <<<<
The GLSYLF's size is about 8 GB which is large and you declare 8 keyed fields from the base table GLBFPF. The last thing to check is whether GLSYLF's attribute "Access Path Maintenance" is set to *IMMED or not? If so, then its size is as accurate as it is.
To check if this GLSYLF is obsolete or not before you decide to delete it, you can display its usage statistics in IBM i ACS GUI --> Schemas section and display all tables in a library in which the table GLBFPF resides and right-click GLBFPF and select Work With > Indexes from the pop-up menu and you will see what is called Index Evaluator window that looks similar to this :
The index can be considered obsolete only if all of "Last Query Use" + "Last Query Statistics Use" + "Last Used" timestamps show blank or dates too long ago into the past. If any one of these shows a recent timestamp, then it is still used. (Normally, I personally would care less on Last Query Statistics Use timestamp, especially when my machine uses fast SSD). There is no equivalent command in 5250 session to display this type of information.
------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
Original Message:
Sent: Tue February 13, 2024 08:10 AM
From: David Taylor
Subject: A Logical File Takes Up Space or Not?
Here is an example. The source is defined with K fields. The object's description shows a size of 8455790592, as does QSYS2.OBJECT_STATISTICS. Does this file really take up that much space? Will I recoup that space by deleting this obsolete object?
Source:
A R GLBREC PFILE(GLBFPF)
A TEXT('G/L BALANCE FORWARD +
A LOGICAL VIEW')
A K COMPNY
A K ACCTYR
A K ACCTMO
A K GEN
A K SUB
A K DIV1
A K CUSACC
A K DIV2
A S LGRTYP COMP(EQ 'ACT')
Object:
Display Object Description - Full
Library 1 of 1
Object . . . . . . . : GLSYLF Attribute . . . . . : LF
Library . . . . . : RNGLIB001 Owner . . . . . . . : RNGOWNER
Library ASP device . : *SYSBAS Library ASP group . : *SYSBAS
Type . . . . . . . . : *FILE Primary group . . . : *NONE
Storage information:
Size . . . . . . . . . . . . . . . . : 8455790592
Offline size . . . . . . . . . . . . : 6442524672
Associated space size . . . . . . . : 8192
Optimum space alignment . . . . . . : YES
Freed . . . . . . . . . . . . . . . : NO
Compressed . . . . . . . . . . . . . : INELIGIBLE
Object ASP number . . . . . . . . . : 1
Object overflowed . . . . . . . . : NO
Object ASP device . . . . . . . . . : *SYSBAS
Object ASP group . . . . . . . . . . : *SYSBAS
More...
Press Enter to continue.
F3=Exit F12=Cancel
------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX
Original Message:
Sent: Mon February 12, 2024 09:23 PM
From: Satid Singkorapoom
Subject: A Logical File Takes Up Space or Not?
Dear David
It depends on whether that logical file is intended to serve as an equivalent of an SQL view or an SQL index (which contains what is called access path in native IBM i term). In SQL world, a view is distinct from an index but in IBM i native world, a LF can serve any one or both purposes at the same time.
Basically, a LF that contains no access path is generally small in size. It's the access path that can be small or large depending on the content of the base Physical file/Table. In DDS that defines a LF, a "K" designates a key field which means an access path.
An access path is not data as it exists in a binary tree structure and cannot be read in the same way you read a data row from a table. Use DSPFD command to see if an LF has any access path or not.
------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
Original Message:
Sent: Mon February 12, 2024 05:17 PM
From: David Taylor
Subject: A Logical File Takes Up Space or Not?
I see this in the IBM documentation.
A logical file contains no data, but it defines record formats for one or more physical files. You can create various logical files and describe their record formats and access paths using data description specifications (DDS).
But when I look at a group of LF/File objects in the QSYS2.OBJECT_STATISTICS(MYLIBRARY','*FILE') SQL table function, I see OBJSIZE for the logical files. Yes, I searched the Web and this discussion group, but the search terms come up with too many haystacks. I read through the recent discussion about offline size for logical files and did not find anything to help. Many thanks for all your input.
------------------------------
David Taylor
Sr Application Developer
Range Resources
Fort Worth TX
------------------------------