Re: How to grant limited access to BPCS user? Query/400

["Genyphyr Novak" <novakg@xxxxxxxx>]

Very good points. One small suggestion about queries bringing your system to
its knees during any type of 'month end' information gathering orgy by the
end-users. . .

The same techniques used to tune SQL for BPCS can be used to tune Query/400
queries (they use the same query optimizer as SQL does to make the decisions
about choice of access path to get at the data the user is requesting).

So, one thing to do which is fairly easy, is to run STRDBG UPDPROD *YES
against their session, and ask them to invoke any evil system-killing
queries (supposing that this is one of the queries they run over and over
again each month to make this effort worth it). You just need to run the
query long enough for the record selection to start (this means the
optimizer has made any decisions about how to access the data) and then you
can SysReq 2 on it. Then, view their job log. If any access paths (new
logicals) are suggested, they will be listed in the 2nd level text of the
Query optimizer messages you see. You can build simple SQL indexes (STRSQL,
CREATE INDEX and prompt with F4 - very easy) or use DDS to make traditional
logicals to match what the optimizer suggests. Then ask them to re-run the
query to verify the results. It can help matters tremendously.

So, if you can't write report programs because the 'powers that be' are
forcing you to live with users running Query/400 reports instead, this is
one way to at least lessen the impact of the queries on the system as a
whole.

Thanks
Genyphyr Novak

-----Original Message-----
From: MacWheel99@aol.com <MacWheel99@aol.com>
To: BPCS-L@midrange.com <BPCS-L@midrange.com>
Date: Friday, February 25, 2000 2:11 PM
Subject: Re: How to grant limited access to BPCS user? Query/400


Khalid

A lot of good points have been made about the pros & cons of putting
Query/400 in the hands of general users. Different philosophies at different
companies lead to different risks & concerns ... I have been denied access
to
As/Set, and I now let queries that drive the 400 to its knees be identified
as the mass users as priorities to re-write in RPG/400 ... down the road I
want to try to have Corporate love for Query/400 incorporated into the
sizing
questionairre & when we increase our # of users license, that should
automatically trip an update to the sizing questionairre reccommendations,
which it does not now do.

We now have about 2 days every month that no one gets any work done on the
400, out of our close to 50 users, because everything has been locked up by
inefficient queries that need to access several large files batch mode in
inquiry sub-system ... 6 months or so ago this was perhaps 1/2 day per
month.
18 months ago I saw this coming but no one else was concerned.  You see the
trend?

Have you budgeted to get the faster processor & massive additional memory
for
the 400 that is needed for large scale Query/400 usage ... we do run a lot
of
Query via JOBQ, but it is sufficiently user-hostile to accomplish this, that
only our most experienced Query users actually do so, when working at WRKQRY
& I have not yet figured out how to do it at RUNQRY or CL from BPCS menu ...
ideally I want the users to run query from CL ... key in their selection
criteria for this run ... then have the whole thing go to JOBQ.

Another thing I want to learn is if I can have an on-line Query/400 CL from
a
user in QINTER switch to QBATCH for the duration of the Query, or even get
RUNQRY & WRKQRY in a different sub-system.

Initially you were concerned about the security issues for people in some
departments SEEING data that they ought not to.

The next threat I see looming are where consultants are increasingly
swearing
by SQL but not having a sense of which people it is inappropriate to be
teaching SQL to ... so ordinary users ask consultants how to solve business
problems & they are told  STRSQL then key in this complicated string of
stuff
the user has no idea what it means & what the significance is of EL TYPO &
then is experimenting with this neat stuff when I stumble over that fact.
In
this scenario, users could CHANGE data they have no business messing with &
not be the wiser, and no one able to figure out how the data got corrupted
because it was done outside of BPCS audit trails.

SSA Help Line won't tell ME (Company Programmer) command strings to delete
garbage records, but our consultants do tell my end users command strings to
get into SQL - perhaps I am asking the wrong people for my help.

I am extremely happy that my new boss understands that there are risks to
the
usage of DFU SQL QUERY and any other tool to access or change BPCS data
outside of the system ... they ALL have risks ... different kinds & scales.

Have your users been delivered of high quality BPCS education & easy access
to BPCS documentation which they do in fact USE?  If not, then they will
generate bogus & inefficient queries that essentially corrupt 400
performance
for all users, corrupt queries that were in fact A-Ok, & make major
corporate
decisions based on this incorrect information.  Have you reviewed the
quality
of the BPCS documentation in the hands of the users who will be creating
queries, so that they understand how to exclude BPCS soft-deleted records,
use efficient access paths, and the flow of data from file to file?
Query/400 creators need much higher quality BPCS documentation than is
needed
by people merely running stuff off BPCS menus.

Al Macintyre  ©¿©
http://www.cen-elec.com MIS Manager Programmer & Computer Janitor
BPCS 405 CD Rel2+ OS/400 V4R3 mixed mode
Our lemon/400 has been upgraded to 170 with about 400 Meg of memory & hungry
for more
When in doubt, read the manual, assuming you can find the right one.
+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---


+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---