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

Thanks for your reply Glenn
We came across the find rm command a couple of days ago
However, we also found that this would not work on a directory if its too large - didn't say what the limit was - as apparently it collects data on the directory first

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 Glenn Gundermann
Sent: Thursday, January 21, 2016 6:13 PM
To: Midrange Systems Technical Discussion
Subject: Re: List IFS files in a folder using SQL (was: Those new SQL things on IBM i)

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.

--
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.