Re: Those new SQL things on IBM i

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

Brain fart day.

The last few months have seen various discussions on new SQL capabilities.
Things like getting a list of active jobs or PTF group level and whatnot.

1) What are those capabilities called? Functions?

If you SELECT QSYS2.NEW_THINGY(. . .) FROM sysibm.sysdummy1 its a scalar
function.

If you SELECT * FROM QSYS2.NEW_THINGY(. . .) its a scalar function.

If you CALL QSYS2.NEW_THINGY(. . .) its a stored procedure.

3) Where can I find documentation on what's available?

Someone else can reply, but I'm sure if you google for "whats new in
TR11/TR3" you'll get a list

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.

CALL QSYS2.QCMDEXC('QSH CMD(''ls __PATH__'')') returns that information to
stdout, which I think becomes a spool file in most things you can call
adhoc sql sctrings from. so you can parse an output file.

If you SELECT * FROM QSYS2.SYSROUTINES WHERE ROUTINE_SCHEMA='QSYS2'; You
will see a list of procedures and functions in QSYS2. You could google for
the names of the ones that look appropriate.