DB2 service for IFS listing

QSYS2.IFS_OBJECT_STATISTICS() UDTF has been delivered to IBM i 7.3 and 7.4.

The question which needs to be answered is: When there is a valid workaround by using RPG or other tooling which require extra skills do we all see it as a valid solution for all IBM i users who could benefit from the functionality asked in this RFE?

Greetings Rudi

I find it disheartening that this has been under consideration by IBM for nearly two years.

This is something that can easily be written by yourself if you're used to the IFS API's. If not, have a look at Scott Klement his examples for IFS processing.

The CEAC has reviewed this requirement and recommends that IBM view this as a medium priority requirement that should be addressed. The CEAC feels that if IBM could more quickly provide a simpler solution that can be built upon in the future, that would be preferable.

Background: The COMMON Europe Advisory Council (CEAC) members have a broad range of experience in working with small and medium-sized IBM i customers. CEAC has a key role in working with IBM i development to help assess the value and impact of individual RFEs on the broader IBM i community, and has therefore reviewed your RFE.

For more information about CEAC, see http://www.comeur.org/i4a/pages/index.cfm?pageid=3285

Dawn May - CEAC Program Manager

I wrote this a few years ago and it works great. My UDTF name is IFSDIR(,[recursion]).
It is included in our SQLiQuery.com product for IBM i, but you can probably request that it be "open sourced".

IBM has received the requirement and is evaluating it. IBM will provide a response after evaluation is complete.

I'm searching and finding examples to do this in RPG. A DB2 Service would have solved my problem instantly.

The CAAC has reviewed this requirement and recommends that IBM view this as a medium priority requirement that should be addressed.

While there are alternatives available as expressed in prior comments, the CAAC felt this support would be best provided as an integrated part of the operating system. With this being part of the operating system, the feature can be used stand-alone without additional code being added to the system; this is an important aspect for small clients being able to embrace modern features.

Background: The COMMON Americas Advisory Council (CAAC) members have a broad range of experience in working with small and medium-sized IBM i customers. CAAC has a key role in working with IBM i development to help assess the value and impact of individual RFEs on the broader IBM i community, and has therefore reviewed your RFE.

For more information about CAAC, see www.common.org/caac

For more details about CAAC's role with RFEs, see http://www.ibmsystemsmag.com/Blogs/i-Can/May-2017/COMMON-Americas-Advisory-Council-%28CAAC%29-and-RFEs/

Dawn May - CAAC Program Manager

This is something that you can easily write yourself (like most of the DB2 services).

For me this is a waste of IBM resources which are better invested on things you can't write yourself.

It would be a big plus if this service had an option to control whether the last accessed timestamp was updated when using it.

One such UDTF to read the IFS was published by RPG guru Scott Klement nine(!) years ago in 2007. Later this UDTF was updated in 2010 to include creation timestamp and check_out status.

Source code:

* Sample UDTF to read an IFS directory.
* Scott Klement 05/24/2007
*
* To compile:
* - first make sure the IFSIO_H copybook is in a QRPGLESRC
* file in your library list.
* - Also verify that the QLG_H and QP0LSTDI_H copy books
* are in a QRPGLESRC file in your library list
* *> CRTRPGMOD IFSDIR SRCFILE(QRPGLESRC) DBGVIEW(*LIST)
* *> CRTSRVPGM IFSDIR EXPORT(*ALL)
* - Run the CRTIFSDIR program (see that member for details)
*
* To call:
* This is run from an SQL SELECT statement. See the
* IFSDIRDEMO member for an example.
*
*
H THREAD(*SERIALIZE) BNDDIR('QC2LE')

/copy qp0lstdi_h

D ifsdir pr extpgm('IFSDIR')
D dirname 500A varying const
D filename 640A varying
D size 20p 0
D type 10a varying
D btime Z
D atime Z
D mtime Z
D ctime Z
D ccsid 5p 0
D owner 10a
D groupx 10a
D chkusr 10a
D n_dirname 5i 0
D n_filename 5i 0
D n_size 5i 0
D n_type 5i 0
D n_btime 5i 0
D n_atime 5i 0
D n_mtime 5i 0
D n_ctime 5i 0
D n_ccsid 5i 0
D n_owner 5i 0
D n_group 5i 0
D n_chkusr 5i 0
D Sql_State 5a
D Function 517a varying const
D Specific 128a varying const
D MsgText 70a varying
D CallType 10i 0 const
D ifsdir pi
** ------ input parameter -----
D dirname 500A varying const
** ------ one record (row) of result -----
D filename 640A varying
D size 20p 0
D type 10a varying
D btime Z
D atime Z
D mtime Z
D ctime Z
D ccsid 5p 0
D owner 10a
D groupx 10a
D chkusr 10a
** ------ null ind for input parm -----
D n_dirname 5i 0
** ------ null ind for output (row) -----
D n_filename 5i 0
D n_size 5i 0
D n_type 5i 0
D n_btime 5i 0
D n_atime 5i 0
D n_mtime 5i 0
D n_ctime 5i 0
D n_ccsid 5i 0
D n_owner 5i 0
D n_group 5i 0
D n_chkusr 5i 0
** ------ Stuff used to interface w/ SQL ------
D Sql_State 5a
D Function 517a varying const
D Specific 128a varying const
D MsgText 70a varying
D CallType 10i 0 const

/copy ifsio_h

D Open_Event PR
D Fetch_Event PR
D Close_Event PR

D sys_errno PR * ExtProc('__errno')
D errno s 10i 0 based(p_errno)
D strerror PR * ExtProc('strerror')
D errnum 10I 0 value

D CALL_STARTUP C CONST(-2)
D CALL_OPEN C CONST(-1)
D CALL_FETCH C CONST(0)
D CALL_CLOSE C CONST(1)
D CALL_FINAL C CONST(2)

D PARM_NULL C CONST(-1)
D PARM_NOTNULL C CONST(0)

D Epoch s z
D dirh s *

/free

// -----------------------------------------
// Verify that we received a directory name.
// without that, there's nothing to be done.
// -----------------------------------------

if (n_dirname = PARM_NULL);
SQL_State = '38999';
MsgText = 'Directory name is required';
return;
endif;

// -----------------------------------------
// Start all fields at NULL.
// -----------------------------------------

n_filename = PARM_NULL;
n_size = PARM_NULL;
n_atime = PARM_NULL;
n_mtime = PARM_NULL;
n_ctime = PARM_NULL;
n_type = PARM_NULL;
n_ccsid = PARM_NULL;
n_owner = PARM_NULL;
n_group = PARM_NULL;
n_btime = PARM_NULL;
n_chkusr = PARM_NULL;

// -----------------------------------------
// Open, Fetch & Close IFS directory stuff
// -----------------------------------------

select;
when CallType = CALL_OPEN; // CALL_OPEN = -1
Open_Event();

when CallType = CALL_FETCH; // CALL_FETCH = 0
Fetch_Event();

when CallType = CALL_CLOSE; // CALL_CLOSE = 1
Close_Event();

endsl;

return;
/end-free


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* Open_Event(): This is called when SQL tells us to "open"
* the "table" that we're going to return.
* We use it for opening the directory.
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P Open_Event B
D Open_Event PI

D CEEUTCO PR opdesc
D Hours 10I 0
D Minutes 10I 0
D Seconds 8F
D fc 12A options(*omit)

D junk1 s 10i 0
D junk2 s 10i 0
D secs s 8f

/free
monitor;
exsr getEpoch;
exsr openIfsDir;
on-error;
endmon;

return;

// ----------------------------------------------
// All Unix API timestamps are supplied as a count
// of the number of seconds since the "epoch"
// (which is defined as midnight, Jan 1, 1970 UTC)
//
// Since the epoch is in the UTC time zone, we
// will need to adjust it for the local computer's
// time... To get the adjustment, use CEEUTCO.
// ----------------------------------------------
begsr getEpoch;
CEEUTCO(junk1: junk2: secs: *omit);
Epoch = z'1970-01-01-00.00.00.000000'
+ %seconds(%int(secs));
endsr;


// ----------------------------------------------
// Open the IFS directory
// ----------------------------------------------
begsr openIfsDir;
dirh = opendir(%trim(dirname));
if (dirh = *null);
p_errno = sys_errno();
MsgText = 'opendir: ' + %str(strerror(errno));
SQL_State = '38998';
endif;
endsr;

/end-free
P E

*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* Fetch_Event(): This is called when SQL tells us to "fetch"
* a record from the "table". We'll use it to
* read each file from the directory.
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P Fetch_Event B
D Fetch_Event PI

D Entry ds likeds(dirent)
D based(p_entry)
D path ds likeds(Qlg_Path_Name_t)
D reqattr ds likeds(Qp0l_AttrTypes_List_t)
D attr ds likeds(Qp0l_Attr_Header_t)
D based(p_Attr)
D buf s 65535A static
D need s 10U 0
D rtn s 10U 0

D dataSize64 s 20u 0 based(p_dataSize64)
D objType s like(qp0l_ObjType_t)
D based(p_ObjType)
D CheckedOut ds likeds(qp0l_Checkout_t)
D based(p_CheckedOut)
D Auth ds likeds(qp0l_Authority_General_t)
D based(p_Auth)
/free

monitor;
exsr getNextFile;
exsr getFileInfo;
on-error;
endmon;

return;

// -------------------------------------------------
// Get the next file name from the IFS directory
// -------------------------------------------------
begsr getNextFile;

dou (filename<>'.' and filename<>'..');
p_entry = readdir(dirh);
if (p_entry = *null);
SQL_State = '02000';
return;
endif;
filename = %subst(entry.d_name:1:entry.d_namelen);
enddo;

n_filename = PARM_NOTNULL;
endsr;

// -------------------------------------------------
// Now we need info about the file. Build a DS
// describing the path name of the file, as well
// as the information attributes to request
// -------------------------------------------------
begsr getFileInfo;

path = *ALLx'00';
path.Path_Name = %trim(dirname) + '/' + filename;
path.Path_Name_delimiter = '/';
path.Path_Length = %len(%trimr(path.Path_Name));

reqattr.Number_of_ReqAttrs = 9;
reqattr.attrTypes(1) = QP0L_ATTR_OBJTYPE;
reqattr.attrTypes(2) = QP0L_ATTR_DATA_SIZE_64;
reqattr.attrTypes(3) = QP0L_ATTR_CREATE_TIME;
reqattr.attrTypes(4) = QP0L_ATTR_ACCESS_TIME;
reqattr.attrTypes(5) = QP0L_ATTR_CHANGE_TIME;
reqattr.attrTypes(6) = QP0L_ATTR_MODIFY_TIME;
reqattr.attrTypes(7) = QP0L_ATTR_CHECKED_OUT;
reqattr.attrTypes(8) = QP0L_ATTR_CCSID;
reqattr.attrTypes(9) = QP0L_ATTR_AUTH;

// -------------------------------------------------
// Ask system for information about the file
// -------------------------------------------------

if (Qp0lGetAttr( path
: reqattr
: %addr(Buf)
: %size(Buf)
: need
: rtn
: QP0L_DONOT_FOLLOW_SYMLNK ) = -1);
p_errno = sys_errno();
MsgText = filename + ': ' + %str(strerror(errno));
SQL_State = '38997';
return;
endif;

// -------------------------------------------------
// Parse the output from the API
// -------------------------------------------------

p_Attr = %addr(Buf);

dow (p_Attr <> *null);

select;
when attr.Attr_ID = QP0L_ATTR_OBJTYPE;
p_ObjType = %addr(attr.CharData);
type = %trim(objtype);
n_type = PARM_NOTNULL;

when attr.Attr_ID = QP0L_ATTR_DATA_SIZE_64;
p_DataSize64 = %addr(attr.CharData);
size = DataSize64;
n_size = PARM_NOTNULL;


when attr.Attr_ID = QP0L_ATTR_CREATE_TIME;
btime = epoch + %seconds(attr.unsData);
n_btime = PARM_NOTNULL;

when attr.Attr_ID = QP0L_ATTR_ACCESS_TIME;
atime = epoch + %seconds(attr.unsData);
n_atime = PARM_NOTNULL;

when attr.Attr_ID = QP0L_ATTR_CHANGE_TIME;
ctime = epoch + %seconds(attr.unsData);
n_ctime = PARM_NOTNULL;

when attr.Attr_ID = QP0L_ATTR_MODIFY_TIME;
mtime = epoch + %seconds(attr.unsData);
n_mtime = PARM_NOTNULL;

when attr.Attr_ID = QP0L_ATTR_CHECKED_OUT;
p_CheckedOut = %addr(attr.charData);
if (CheckedOut.Checked_Out_Flag = QP0L_CHECKED_OUT);
chkusr = CheckedOut.User_Name;
n_chkusr = PARM_NOTNULL;
else;
chkusr = '';
n_chkusr = PARM_NULL;
endif;

when attr.Attr_ID = QP0L_ATTR_CCSID;
ccsid = attr.intData;
n_ccsid = PARM_NOTNULL;

when attr.Attr_ID = QP0L_ATTR_AUTH;
p_Auth = %addr(attr.CharData);
owner = Auth.Object_Owner;
n_owner = PARM_NOTNULL;
groupx = Auth.Primary_group;
n_group = PARM_NOTNULL;
endsl;

if (attr.Next_Attr_Offset = 0);
p_Attr = *NULL;
else;
p_Attr = %addr(Buf) + attr.Next_Attr_Offset;
endif;

enddo;
endsr;

/end-free
P E

*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* Close_Event(): This is called when SQL tells us to "close"
* our "table". We'll use it to close the dir
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P Close_Event B
D Close_Event PI
/free
monitor;
callp closedir(dirh);
on-error;
endmon;
return;
/end-free
P E

Creating a new RFE based on Community RFE #90136 in product IBM i.