Re: IBM i ACS rss plugin -- MIDRANGE-L

Hi Rob

you ask some good questions and make some good suggestions. this is on a
system we look after for someone else so we are trying to keep it as "low
touch" as possible.

Ideally we want to keep as much work off the server as possible and
minimise what we create on there, but creating a view is not a bad idea at
all. I will definitely check that out.

I haven't logged a call on the cldownload plugin as I semi-expected that
would not work, but I suppose that would be a reasonable thing to do.

On Wed, Sep 21, 2016 at 11:21 PM, Rob Berendt <rob@xxxxxxxxx> wrote:

Have you tried reporting your issue with the subselect on the cldownload?

Not saying this is the ideal way, but you could create a view on the IBM i
with some of that. This would allow the download plugin to work in it's
rudimentary form. At least until they fix the cldownload or implement the
RFE.

Is there way to do the SQL grunt work on the server? I am thinking in the
vein of an ODBC connection in which, instead of doing your select and
stuff on the PC, you pass a string up with openquery()

Search the list for the phenomenal difference in performance openquery()
can make.

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: Evan Harris <auctionitis@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 09/20/2016 06:29 PM
Subject: Re: IBM i ACS rss plugin
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

Hi Rob

it is possible I am using the wrong tool; the problem I see is that the
alternative you suggest is equally not the right tool (though I
appreciated
the attempt to provide an alternative).

Consider the following SQL statement which I want to run and collect the
output for:

SELECT
(SELECT HOST_NAME FROM QSYS2.SYSTEM_STATUS_INFO) as HOST_NAME,
DATE(COLLECTED_TIME) as AS_AT_DATE,
PTF_GROUP_NAME,
MAX(PTF_GROUP_DESCRIPTION) as DESCRIPTION,
MAX(PTF_GROUP_LEVEL) as LEVEL
FROM QSYS2.GROUP_PTF_INFO
WHERE PTF_GROUP_STATUS = 'INSTALLED'
GROUP BY (SELECT HOST_NAME FROM QSYS2.SYSTEM_STATUS_INFO),
DATE(COLLECTED_TIME), PTF_GROUP_NAME
ORDER BY PTF_GROUP_NAME DESC

I can't run this using the download or the cldownload plugin; the
cldownload allows me to run an sql string but chokes on the subselect
(excuse me if I have the terminology incorrect, but feel free to correct
me). The cldownload plug in also less then optimal as it won;t use an SQL
file, it requires the string to be entered.

I suppose it is possible that I could get rid of the host name column, but
as I am going to collect this data from a number of systems I am goign to
have to perform some other contortions somewhere. I could write a program
or script on the i to run the SQL and output to a file and then download
it, but it seems to me that using the rss plugin is a reasonable
substitute
in theory.

Basically I have a number of SQL services queries I want to run, and maybe
some performance collection queries later on, and trying to run them using
the download tools is awkward to say the least.

I took your advice and submitted an RFE, here it is here:
http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=94807
<https://protect-eu.mimecast.com/s/mexVBILogNXfY>

On Sep 20, 2016 11:14 PM, "Rob Berendt" <rob@xxxxxxxxx> wrote:

Submit an rfe to add an /output option at
https://www.ibm.com/developerworks/rfe/execute?use_case=chan
geRequestLanding&BRAND_ID=352&PROD_ID=1476
Let me know if you do and I'll vote for it.

But, you may be using the wrong tool. Perhaps you should be using data
transfer instead
/PLUGIN=download

Granted, the dtfx logic is a little "different"
...
[SQL]
Select=cmpny, cmpnam
Where=
JoinBy=
GroupBy=
Having=
OrderBy=cmpny
MissingFields=0
...

But if you're burying this into a batch file or something I think you

can

pick up on this really quickly. And it will get the job done.

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: Evan Harris <auctionitis@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 09/19/2016 10:03 PM
Subject: Re: IBM i ACS rss plugin
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

OK, so adding /autorun=1 causes the script to run but I can't find an
option to direct the output anywhere
Adding a bogus parameter to the request caused the command to display

the

valid options
The current command line I am using is:
java -jar acsbundle.jar /SYSTEM=TESTBOX /PLUGIN=rss
/file=\sql\getcheckdata.sql /autorun=1

On Tue, Sep 20, 2016 at 1:40 PM, Evan Harris <auctionitis@xxxxxxxxx>
wrote:

Just some additional information:
- The Run SQL Scripts box comes up with the script contents in it
- If I click the Run button it runs OK so it doesn't seem to be a

syntax

error

On Tue, Sep 20, 2016 at 1:38 PM, Evan Harris <auctionitis@xxxxxxxxx>
wrote:

I have an "=" sign in there; not sure what happened with the cut and

paste

On Tue, Sep 20, 2016 at 12:41 PM, Bdietz400 <bdietz400@xxxxxxxxx>

wrote:

Make sure you have "/PLUGIN=RSS". You show a "-" dash below not

equal

sign.

--
Bryan

On Sep 19, 2016, at 8:02 PM, Evan Harris <auctionitis@xxxxxxxxx>

wrote:

Hi

I was prompted by Rob's thread of a couple of weeks ago to explore

this

plugin in a little more.

All I have been able to manage so far is to get the SQL plug in to

load,

with my SQL in the window, but I have not been able to get it to

run.

What

I would ultimately like to do is get it to run and output it to a

file

on

my PC.

The command line I am using is:

java -jar acsbundle.jar /system=testbox /plugin-rss

/file/sql.myscript.sql

I have also tried it using the acslaunch... executable with the

same

result.

Anyone managed to get any further than this ?

--

Regards
Evan Harris
--
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.

--

Regards
Evan Harris

--

Regards
Evan Harris

--

Regards
Evan Harris
--
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.

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