Re: Restricted Interactive RUNQRY -- MIDRANGE-L

Paul Tuohy writes:

>  I would highly recommend that you take a long hard serious look at
>  not removing the ability to run interactive queries. 

>  Doing this is like giving someone a gift of a Ferrari 
>  and then telling them that you have disabled the gear box 
>  so they can only drive in 1st and 2nd gear.

Brad 

I agree with Paul.

Query/400 can be misused by end users who have ZERO training in how to use it 
properly.  The simple answer to that is to provide some training, more so to 
the people who obviously need it, not to deny access to the vast majority of 
users who are not misusing it.

There is a SYSVAL you can alter to cause any query that would be excessively 
long running to be automatically cancelled.  There may be managers who will 
say ... Oh but during fiscal end we do have some long queries that need to be 
run ... well Ok, set things up so that SYSVAL can be altered just before 
fiscal end to unlimited query, then when fiscal end is finished, have it 
altered back to your normal setting.

We have an end user who NEEDS a particular report set each month.  I need to 
replace it with an RPG program & it keeps falling behind other more important 
stuff in my TO DO list.  I am giving this as an example ... in fact we have 
several copies of this scenario, some worse than others.

This particular report collects all the transactions posted for a whole 
month, of a particular kind & we have several thousand transactions per day, 
then it looks up the cost master file to get the cost of these transactions, 
and there are over a million records in the cost master file, and in my 
opinion the method by which the query does the access is VERY inefficient.  I 
have made several stabs at trying to re-write this & before getting done, 
some other priority has blown up & I have had to set it aside.

It is an extremely complicated query.  I showed the end user how it is 
getting wrong answers because the person who originally created it had some 
misconceptions about how query works.  Well problem is, the end user knows 
even less about how query works & wants to settle for whatever data he can 
get.

The original query was written by someone who was able to figure out how to 
get to the data they needed, when they were interested in a tinier sample, 
like one day's input, and knew how to change the criteria in WRKQRY then put 
it on a separate JOBQ so that if it took a while to run, it would not be 
holding up JOBQs that many other people were running.

The person now trying to run this, does not understand JOBQ switching around 
& is not a computer PC person, let alone 400.  I have offered to run the 
reports for him after each fiscal EOM & stick them in his mail box, because 
it only takes me a few minutes to do it, but he has his pride, he is going to 
learn how to work this.  We have had a few times when it totally locked up 
the AS/400, and someone else had to cancel the job, but this happening less 
often since he has learned step by step instructions I gave him how to 

WRKQRY change selection criteria to month just ended, put it on JOBQ
WAIT until it has finished executing, change selection criteria to another 
facility, put it on JOBQ, WAIT until report is done, change criteria, etc.

But for every ONE scenario like I just described, we have THOUSANDS of 
Query/400 created by end users in which we get good data out, without any 400 
performance problems, that users can run on the fly with no hassles.

We had a CLASS at Central where a consultant gave a one day presentation on 
Query & supplied the class with hand-outs explaining our file structure - how 
to find particular kinds of data.

I think that some time in the future it would be a good idea to have another 
class like that, but there's other classes needed more urgently, like for 
people who have been given a PC who do not have a whole lot of computer hands 
on experience ... WHAT STUFF is there that can help you do your work ... HOW 
do you get to the programs that should be the most helpful ... how do you 
download 400 report data into a PC spread sheet ... what 400 data is volatile 
such that you want to always download the latest story from the master files 
before you manipulate your subset ... how do you answer the idiot messages on 
the printer so you can get your reports with minimum hassle

My memory is that there were 10 people who participated in the class & of 
those 10 people 2 of them are still creating queries for us while the rest 
never got into the habit or are no longer with the company.  Before they left 
Central, there were about 3 other people from the class who were creating 
lots of queries.

I feel that a minority of co-workers could have picked up Query without a 
formal class, but for most of them, the class was essential.

They needed to be shown what could be done, how to do stuff, where to find 
documentation on how to do it, how our data base is organized, the logic of 
data flow through our data base, so that when they need some data they have 
some idea what files it should be in, which are the most commonly used files 
per applicatiion, how you can tell what is a really big file that needs care 
when accessing, the meaning of the logical paths with respect to using query 
efficiently but why you still have to specify selection criteria that the 
logical appears to be preselecting, how you can tell when there is no data in 
a file ... it is there for different tailoring options than those we have 
currently selected ... how you can tell when there is no data in a field that 
is of interest to you ... the field is for a different combination of actions 
than we are using

There were 2 class hand-outs - one on Query & one on our file structures, 
which in retrospect I think was a mistake.  We now have several employees who 
came on board after the class & most of them end up with one of the hand outs 
but not the other & they just cannot function effectively in Query without 
both.

For security reasons, only myself and a small handful of power users were 
granted authority to create & compile CL programs that essentially are 

start program
over ride print file so we can find the durn things on spool file
runqry whichever one with selection criteria yes or no
end program

then add this program to a menu

so that other users who do not have a clue about query can run them off of 
menus & either have them go to JOBQ, or run without selection.

I have a power user at a remote site who needs to learn how to do this & I 
trust him with the authority.  I just do not trust my ability to teach him 
how via remotely.  I need to SHOW him some time when we are in the same 
office, then grant the authority.

Our BPCS/400 comes with a really slick menu system & we have our security 
setup so that a whole lot more users can make changes to the menus than be 
adding programs to the collection.  This is for security training reasons ... 
learning how to change the menus is incredibly simple compared to making CL 
program for a RUNQRY or creating the query in the first place.

So person-A describes their needs to one of our power users
Person-A is an end user who not going to be able to handle query creation, 
menu maintenance, etc. but can run programs & use the data.

Power User creates the query, checks it out with person-A, they're happy, now 
they want it on one or more of our menus.  Usually it is me that creates the 
CL & puts it on one menu & lets the power users know which menu & the name of 
the CL, then they populate it onto other menus.

The vast majority of our queries never get to menus, are accessed purely off 
of WRKQRY ... we ask people to add to the end of the text description the 
initials of the person who created this query & as a general rule, COPY your 
version if you going to be significantly changing what it does ... original 
person may still NEED original verion ... we also place on the header footer 
the NAME of the Query & CL (if any) so that other people can see a query & 
rerun same one with different selection criteria, or use it as basis of COPY 
to something similar.

Al Macintyre  ���
MIS Manager Green Screen Programmer & Computer Janitor of BPCS 405 CD Rel-02 
running on AS/400 V4R3 http://www.cen-elec.com Central Industries of 
Indiana--->Quality manufacturer of wire harnesses and electrical 
sub-assemblies
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---