Hope to be on V7R3 by March 1, give or take a week.
JOB_INFO on hold for now.
Paul
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Monday, February 05, 2018 12:12 PM
To: Midrange Systems Technical Discussion
Subject: RE: Order of output for QUSLJOB API
Ok, with 7.2 and above they added some stuff to the syntax to these UDTF's to do some of the selection in the call to the UDTF.
Basically they allowed you to do some of the selection via these ways and This_Column => 'This'
and That_Column => 'That'
versus doing it all after the UDTF call with your own where clause.
Sometimes you have to look up the pre 7.2 syntax. Sometimes it's hard to find and you just have to know that. Tried to find it in the 7.1 Knowledge Center.
Oh, my bad. 7.1 doesn't support JOB_INFO at all.
You can plainly see that at
http://ibm.biz/DB2foriServices You might have to dabble with one of the other services. Or just upgrade.
Or write using an API, crying all the time realizing how much easier it would be if they were upgraded.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 02/05/2018 11:07 AM
Subject: RE: Order of output for QUSLJOB API
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Rob,
That worked fine on my V7R3 LPAR, syntax error on V7R1 LPAR.
Is it possible this will not work on a V7R1 LPAR.
V7R3 R&D LPAR
SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER => 'MSROBOT',
JOB_STATUS_FILTER => '*OUTQ')) X
where substr(job_name,16,10) = 'DEFREVENUE'
order by JOB_End_time desc
SELECT statement run complete.
V7R1 Production LPAR.
SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER => 'MSROBOT',
JOB_STATUS_FILTER => '*OUTQ')) X
where substr(job_name,16,10) = 'DEFREVENUE'
order by JOB_End_time desc
Token => was not valid. Valid tokens: ) ,.
Paul
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Monday, February 05, 2018 10:49 AM
To: Midrange Systems Technical Discussion
Subject: RE: Order of output for QUSLJOB API
You know you can still use a WHERE clause, and an ORDER BY clause, on a
table function even if it has it's own special selection criteria, right?
So, it's quite easy to combine these:
SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER => 'MSROBOT',
JOB_STATUS_FILTER => '*OUTQ')) X
select * from qgpl.jobinfo where substr(job_name,16,10) = 'DEFREVENUE'
order by JOB_E00001 desc FETCH FIRST 1 ROWS only
Into
SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER => 'MSROBOT',
JOB_STATUS_FILTER => '*OUTQ')) X where
substr(job_name,16,10) = 'DEFREVENUE'
order by JOB_End_time desc
FETCH FIRST 1 ROWS only
Unless you've found it a real performance boost to sort/select a temporary
file versus doing it from a UDTF.
And I have a small criticism about using QGPL to hold temporary files, and
naming them close enough to the UDTF that I had to take a second look to
see what was going on. If IBM comes out with a new UDTF or some such
thing named qgpl/jobinfo you could be hosed. Think of the RUNSQL fun we
had a few releases back.
Oh and <expletive deleted> 10 character column names and using DSPFFD to
look them up IOW, don't use JOB_E00001 instead of JOB_END_TIME.
Rob Berendt
midrange.com
