Re: S/ 36 stuff on the AS/400 -- MIDRANGE-L

schmuel@bigfoot.com writes:

>    3) PDM enhancements .. "I like POP" ...
>  
>     eg. I don't see how you can do an object string search on "part" of a 
>  library
>     ...eg..  all members with "*cash*" in the 25 search mode..
>     (yes, I acknowledge that the actual search is neat)

This is my 3rd post to midrange-L in reply to Steven questions ... I still 
have not seen my first post bounce back on the list & I am seeing people 
replying to other posts that I have not yet seen, so I do not know if 
midrange_L got clogged up with relation to the dual sending problem of a few 
days ago, or if it is AOL delivery that is clogged.

There are SEARCH options available with many languages/400 & there are 
probably simpler ways to do things than those I have encountered so far or 
figured out.

Some of this stuff I ought to know better than I do know, so this is a 
worthwhile review topic.

When you are in PDM SEU in some member, put the cursor on the top line where 
you do commands & do an F1 & it tells you about all the commands available 
there ... likewise where there are line numbers down left side, you can put 
cursor there & F1 to see all the commands that can go there.  There are an 
awful lot of options ... if you know just one of them .. key in beginnings of 
command, put cursor there, F1 & get at the help for that command.

When you are in RPG source or CL source, you can have cursor on a line & F4 
prompt for help.  With RPG you get like the template of where all the fields 
are that you might key into for that RPG command that you are on.  With CL 
you get all the parameters that can be used with that CL & you can cursor on 
any one of them & F1 to get details on your options there.  The help support 
to a programmer/400 is extremely detailed.

One of the up top commands in PDM is FIND the next instance of some STRING & 
this is not cursor sensitive, so I can put F CASH there & it will find me all 
cases where the words "cash" "Cash" "CASH" or something with *CASH embedded 
are to be found anywhere within that member, then F16 to find the next one

If you do the letter F on top (short for FIND) then F1, you will get into the 
help specifically for this & you can see that you can search for something in 
some specific columns, or perhaps you want to change every place it says 
"Cash" to become "Euro" instead & you can do that.

There is also a manual on PDM, SEU, etc./400 that lists all this stuff in 
nice reference

There is an option, but I have not used it, so I not remember how exactly you 
get into it, in which you can set up a convoluted F etc. string, much like 
you used to be able to do in VASP/36 POP to go through an entire library of 
members looking for certain things & depending on what it finds, taking 
certain actions.  I am not too keen on this kind of approach since it implies 
blindly changing stuff when I not sure what I am going to find.

Some of the investigations that I conduct into our software can also be done 
to a certain degree by non-programmers who know how to create query/400 & I 
want those individuals to become more versatile with query/400, so I have 
been populating a library that is accessible by query/400 with some IBM dumps 
of various views of our data base.

My people can run a query, off of a menu, that lists all the queries we have 
avaialble to their use, sorted by the description text title, then take 
another menu option that is really RUNQRY where they fill in blank of what it 
is they want to run.  If they F20 beyond the basic data, they can see how 
popular this query is - how often their co-workers are running which ones.

I have a query in which I trace all places some 400 object is used / called / 
touched by other 400 objects ... for example ... users have a problem with 
this report ... I have the name of the report object, like REPORT123O - 
whatever & I want to know what programs output to that object, so I run 
X_TRACE (the name of my query) REPORT123O (or whatever it is) & I get a list 
of all programs that use it.  Or we had a case of garbage managing to get 
into some file, so I ran X-TRACE to list for me all programs that add to that 
file or update it.  There are IBM tools but they can be cumbersome difficult 
to read reports ... I decided to go with a homebrew tool that I can use & my 
system helpers can also use.

We can run a query looking at statistics about our files - how many records 
they have, how many coded for deletion, when was the last time someone 
updated this file, how long ago was it backed up.

Management can run a query, off of a menu, that identifies what software got 
added, changed recently.

Within query/400 we can do a variety of searches against the data that has 
got there from whatever source ... regular data base application files, or 
IBM DSP this or that to an *OUTFILE

We can say to list contents of fields that are LIKE something or NOT like 
that something for example
'9%' says to select all values that start with character 9 - the percent sign 
is a filler
'9_0000%' is a selection in which the underline means we do not care what is 
in the 2nd character, but the rest has got to match 9 in first column & 0000 
in 3-6
'%E_A%' means any time find an E followed 2 characters later by an A - select 
it
A bunch more examples are given in the Query/400 manual

Remember that you can use this on the spur of the search impulse via 
RUNQRY *N some file name F4 & at the bottom put *YES for selection - you 
could change a little higher to get a print out if desired, but I usually do 
inquiry to narrow my search ... if you do a selection & want to change it, 
just F12 back to selection criteria & adjust there to different or narrower 
search.

So in your example, if you had software objects in a file that you were doing 
a query/400 search of, the LIKE criteria would be something like '%cash%' 
although I would want to check sensitivity to upper/lower case.

Sometimes when I am working with files generated by packaged software, I am 
not 100% familiar with the contents of all the fields, and even though I have 
access to the source code for the file layouts, that is not always friendly 
because a lot of entries REFER to some other object to get the details, so 
the source is not all in one place & when I do DSPFFD to get at the layout of 
external description, there often is more detail there than I want.

So, what I have come to find is the most ME FRIENDLY is to have 2 sessions 
open at the same time looking at the same file.  
One session has WRKQRY looking at the file layout from perspective of what 
the fields are NAMED
Two session has RUNQRY *N looking at the file contents from perspective of 
typical population there

In my post # 1 I mentioned DPSPFM as a way to dump a physical file to your 
screen ... at this point you name the physical, not use any logical ... at 
first glance this data may appear to be garbage, but if you try out the 
commands F24 F10 F10 F24 F10 F11 you will find that at different "pictures" 
the F24 options list is not identical so there are multiple paths to multiple 
"pictures"  If you remember what packed means, some stuff will become more 
obvious.  I think this access method really is only friendly when looking at 
alphabetical data.

If you know the logicals, I think STRDFU is much more illuminating, so long 
as you very careful not to change anything you not intended to.

STRSQL is potentially one of the most dangerous tools/400 in the hands of 
someone unfamiliar with what they doing, and just exploring ... you can use 
it to update 100% records in some file & not realize what you just did

For a beginner/400 but experienced/36 I suggest you limit yourself to SELECT 
in which essentially you are saying "Use SQL to SHOW me some stuff in these 
files, but do not change anything."

STRSQL & enter ... it gives you screen conceptually similar in appearance to 
CALL QCMD & F10 except here you be doing SQL commands rather than CL commands

Key the word SELECT & press F4
it is looking for file ... with cursor on file line do F4
you get list of libraries in library list
find some library & put a 1 to select it & press enter
there will be a short delay while it gets at list of all files in that library
find some file & put a 1 to select it

after you got one file, you can F4 to get another one

after you have got some library file etc. you not need to go through the 
select process ... you see the format ... you can later key in part of the 
story & F1 to get the rest of the story

move cursor to line below file selection then F4
it is giving you list of all fields that are in the files you specified
you can put 1 2 3 4 5 on various lines to select the sequence you want them, 
just like we do in query/400

now move cursor a couple lines below file selection to
WHERE since we want to only select those records that have some value in some 
field where we know something about the data there

for example
SSTAT NE 'E' AND SOFAC = '20'
means let's only look at the records in which field SSTAT is something other 
than 'E'
except I deliberately gave you some wrong information - press enter & you get 
ERROR - put cursor on error message & F1 for more info

I do not have the funny symbol on my home PC keyboard that you need to use 
for NOT in front of the = (it is above the digit 6 on my IBM keyboard at the 
office)

Remember F6 if you need more lines

Exit my mini-tutorial from SQL via F3 F3 then 1 at exit screen, then next 
time you STRSQL you can F9 to remember where you left off in your earlier 
explorations

There's manuals on this & it also has the search LIKE functionality.

I think SQL is conceptually like Query/400 except query/400 is more 
user-friendly & security-friendly 

The value as a 36 guy exploring this is that you get comfortable with how 
some of the SQL functionality is, so that later you can write RPG programs 
that get data via SQL as an alternative to CHAIN or READ.

An important difference to remember is that when you use RPG to access your 
data base, you are reading the ENTIRE RECORD of some file at a time into your 
program with each CHAIN or READ - all the fields in that record.

With SQL you are picking & choosing which FIELDS to input ... you can 
simultaneously grab selected fields from a bunch of records in different 
files, or you could say (in SQL embedded in RPG) ... look at this one field 
in this file in ALL records of the file, that have some value in some other 
field, and JUST give me the grand total, or the count, or the largest value.
 
I do not use it very often, but in RPG/400 there is a string search 
capability.

Basically you populate some field with the data you want to search for, then 
you use a variation of LOKUP & you get back some substring #s & you can also 
use sub-string & concatenation within RPG to manipulate the data that it is 
finding.

Again I am hoping that this posting, trying to address one of your many 
questions, has given you several mini-tutorials for exploring 400 that will 
be interesting to a 36 guy.

MacWheel99@aol.com (Alister Wm Macintyre) (Al Mac)
AS/400 Data Manager & Programmer for BPCS 405 CD Rel-02 mixed mode (twinax 
interactive & batch) @ http://www.cen-elec.com Central Industries of 
Indiana--->Quality manufacturer of wire harnesses and electrical 
sub-assemblies - fax # 812-424-6838

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