Re: List IFS files in a folder using SQL (was: Those new SQL things on IBM i) -- MIDRANGE-L

Hi Alan,

I use this to delete all files in a specific folder older than x number of
days.
This will exclude subdirectories.

DCL VAR(&QSHCMD) TYPE(*CHAR) LEN(5000)
DCL VAR(&IFS_PATH) TYPE(*CHAR) LEN(60)
DCL VAR(&KEEPDAYS) TYPE(*CHAR) LEN(60)
DCL VAR(&ENVVARVAL) TYPE(*CHAR) LEN(1024)
MONMSG MSGID(CPF0000 MCH0000) EXEC(GOTO CMDLBL(ERROR))

/* Insert code here to
retrieve path and retention days from wherever you store your values, eg.
data area, table, etc. */

/* Using Qshell: */

/* Set the job environment to log messages to QSHELL_log.txt */
/* QSHELL_log.txt in the same folder. This will do a replace. */
CHGVAR VAR(&ENVVARVAL) VALUE('FILE=' *CAT +
%TRIM(&IFS_PATH) *CAT '/QSHELL_log.txt')
ADDENVVAR ENVVAR(QIBM_QSH_CMD_OUTPUT) +
VALUE(&ENVVARVAL) REPLACE(*YES)

/* Set the job environment to send errors as escape messages */
/* instead of as completion messages, which is default. */
/* QSH0005 with exit status = 0 will be sent as a completion msg.*/
/* QSH0005 with exit status > 0 will be sent as an escape msg. */
/* QSH0006 & QSH0007 will be sent as an escape message. */
ADDENVVAR ENVVAR(QIBM_QSH_CMD_ESCAPE_MSG) VALUE(Y) +
REPLACE(*YES)

/* Build Qshell command string. */
/* find in the specified folder */
/* files (-type f) */
/* older (-mtime +days) */
/* remove (exec rm {} \;) */
/* Note: mtime is the modified date/time */
CHGVAR VAR(&QSHCMD) VALUE('find ''' *CAT +
%TRIM(&IFS_PATH) *CAT ''' -type f -mtime +
+' *CAT %TRIM(&KEEPDAYS) *CAT ' -exec rm +
{} \;')

STRQSH CMD(&QSHCMD)

RETURN

ERROR:
SNDPGMMSG MSGID(CPF9897) MSGF(QCPFMSG) +
MSGDTA('Unexpected error. Please +
investigate.') TOMSGQ(*SYSOPR)

DMPCLPGM

CALL PGM(QMHMOVPM) PARM(' ' '*DIAG' +
X'00000001' '*PGMBDY' X'00000001' +
X'0000000800000000')

CALL PGM(QMHRSNEM) PARM(' ' X'0000000800000000')

ENDPGM

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144

On 21 January 2016 at 17:18, Alan Shore <ashore@xxxxxxxx> wrote:

This thread has really caught my attention - due to a MAJOR problem we
have had since the New Year
We started having Mutex waits for users in the warehouse using web service
calls to an outside company that would supply us with the correct courier,
labels etc.
This was killing us
From a couple of seconds for a response - it went to 15 to 20 minutes per
response
To cut a LOOOOOOOOOOOOOOOOOOOOOOOOOONG story short, the immediate problem
was that we were mimixing the ifs (when we don't really need to) PLUS one
ifs directory has over 7.5 million files, accumulated in 8 months

When I looked at the results of our ifs purge job, I discovered that this
particular directory was NOT being purged
We have since included other directories into our ifs purge process, and
we hope that because the warehouse does not work from Friday night to
Sunday morning, this will give us time AND the necessary access to this
large ifs directory

Now that I have explained the dilemma, does anyone know if these sql
services will have the capability of not only listing the contents, but
also being able to delete the contents with certain criteria (age etc.) of
a directory?

Does anyone have something for me to look at/read?

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
John Yeung
Sent: Thursday, January 21, 2016 5:02 PM
To: Midrange Systems Technical Discussion
Subject: List IFS files in a folder using SQL (was: Those new SQL things
on IBM i)

On Thu, Jan 21, 2016 at 3:09 PM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
wrote:

I have a project coming up in a month or so where I will want a list
of files in an IFS folder. I'm hoping I can get that file list this way.

I suppose it would be nice. I love the idea of exposing more things via
SQL (particularly when the easiest-to-use option thus far is a system API),
and it looks like IBM is on that path.

But while you wait for IFS handling to show up in the official DB2 for i
views and services (I'm guessing it will take longer than "a month or so"
for this to happen), you can do it yourself by wrapping up the appropriate
non-SQL code in UDFs or stored procedures.

If you don't want to wait, and you don't want to build your own UDFs/SPs,
you could stick solely to SQL by relying on QSYS2.QCMDEXC to execute
arbitrary CL commands (including the QSH command, to execute arbitrary
Qshell commands like ls with the > redirection operator).
You may also have to use QCMDEXC to do CPYFRMSTMF or CPYFRMIMPF.

It's the good ol' dump-and-read technique. Byzantine, especially in this
from-SQL incarnation, with probably tons of extra single-quotes to keep
track of, but doable.

John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.