On Sat, 9 Dec 1995 01:04:45 EST Jeff Kell wrote:
>Subject: QUERY and TPI
>
>This will be slanted toward QUERY and Superdex, although I presume it may
>also affect other TPI products and possibly the forthcoming HP b-tree access.
>
>Some months ago we went "live" with a Superdex index on one master dataset,
>adding a b-tree type index to the existing master dataset key item. To set
>the scenario, the dataset was a course master in our student records system
>and the purpose of the index was to provide handy access to all sections of
>a course, or all courses in a department, etc., which wasn't previously
>possible. We had some programs doing sequential reads (!) to emulate this
>access, and other smarter programs loading a table with record numbers to
>do the same, but bottom line was b-tree type access was easier.
>
>We were pleasantly surprised when Query responded in kind to generic key
>access, finding the masters by values like "<term><department>@" to get
>all courses in a given department. Everything was wonderful. Until...
>
>We had a batch job that "cleans" out completed semesters from the active
>database. It bombed off when trying to find "old" courses by doing a
>query FIND COURSE.NUMBER < term. When this wasn't a TPI key field, but
>just a master key, it worked fine. And our existing programs that do this
>type of search (doing it sequentially) continue to work fine. But QUERY
>fails, leading me to believe there's some "TPI awareness" built into QUERY.
>
>Can someone elaborate on this a bit? I'm rather confused, and the existing
>documentation is rather vague with respect to TPI access. Neither HP nor
>the TPI vendor seems to elaborate on this much (though I admit I haven't
>memorized the manuals). Is there an easy way to differentiate the access
>to the item to Query so that it does a serial read when necessary as
>opposed to a DBFind on this TPI indexed master data item?
>
>I specifically would like to hear the Superdex answer; but other TPIs and/or
>HP's coming b-tree specs would be welcome too for the general readership.
>
>Jeff Kell <[log in to unmask]>
Since COMPUSERVE masks my name and only seems to say "Bradmark Technologies,
Inc" let me say my name is Tim Joseph, Bradmark Technical Support. I am the
escalation person for HP3000 products.
I have contact Jeff Kell directly to inititate a dialog to completely resolve
the issue that Jeff has highlighted and to answer any other questions about
QUERY and TPI SUPERDEX.
Since the issue has been raised on the HP3000-List, let me see if I can provide
a specific answer to what I think Jeff has experienced. I sort of wanted to
wait to speak with Jeff and make sure I'm answering his question, but prompt
response of a QUERY-TPI-SDX question with an "always a good idea" answer takes
precedence. If something of further interest comes from conversations with
Jeff, maybe we can post that info also.
Almost always a good idea rule number one: "name the INDEX different than the
IMAGE item name" or as we used to say at VESOFT - and I believe they still do -
"seems like." As you may be aware, when you define a TPI index, you specify an
index name and the IMAGE item(s) which are used to construct the index key
value. In the simplest case the entire IMAGE item becomes the INDEX key value.
In other cases substrings, concatenation of several items or substrings of items
(concatenated / composite), or keyword parsing may be used to construct the
INDEX key value - among others.
Current versions of Query knows about INDEXING.
One thing that it supports is
>FIND indexname ="argument for a DBFIND mode 1 on that index"
Since SUPERDEX is quite flexible in what can be done from MODE 1 DBFIND this is
a nice window into INDEXING. The interface supports the "=" equal sign on the
find command when using an INDEX. If you specify anything other than the equal
sign with an INDEX, query comlains "INVALID RELATIONAL OPERATOR". However, the
equal sign is fine for SUPERDEX since most all types of arguments can be
provided. For example, to find all less than or equal a value:
>FIND indexname = "<=value"
Or to find a customer named Billy Richards when naming a KEYWORDED index:
>FIND indexname = "~bill@ and rich@;"
Here the tilda "~" and semi-colon ";" delimit an argument which is treated as
though DBFIND mode 12 had been called. DBFIND mode 12 is a relational find and
has SQL parsing, thus a relational list of all entries containing keywords
beginning with BILL is created and then "ANDed" with RICH@ to produce a
relational list of just those entries which contain BILL@ and [log in to unmask] This is
subsequently traversed via DBGET mode 5.
In the case which Jeff mentions the command is:
>query FIND COURSE.NUMBER < term
INVALID RELATIONAL OPERATOR
I believe the INDEX name may be the same as the IMAGE item name.
If not then there are some other possible issues, too verbose for here.
This causes the problem of encountering the restriction were QUERY is explicity
parsing such that if an INDEX is utilized on the FIND command, the operator must
be "=" equal sign.
Assuming you have a path named X10-INDEX on an item named X10-ITEM.
Then a FIND on the item works:
>FIND X10-ITEM < 5
USING SERIAL READ
5 ENTRIES QUALIFIED
However a FIND on the index fails due to the "<":
>FIND X10-INDEX < 5
INVALID RELATIONAL OPERATOR
The correct syntax to utilize the index with an argument of "less than five" is:
>FIND X10-INDEX = "<5"
We can look under the hood by using the built in SUPERDEX trace function which
is activated via SETJCW SITRACE=1:
>b=testdb
PASSWORD = >>
MODE = >>1
DBOPEN base = TESTDB, mode=1 MON, DEC 11, 1995, 12:48 PM
Base=TESTDB.QUERY.ATSDX ID=$1 Version 4.1.27 (1e0a5f)
TurboIMAGE Version: C.0609
SITRACE = 1
DBINFO ID=$1, mode=803
>FIND X10-INDEX = "<5"
DBINFO ID=$1, mode=811, qual=X10-INDEX,
DBINFO ID=$1, mode=204, qual=X10-INDEX
DBINFO ID=$1, mode=821, qual=X10-INDEX
DBINFO ID=$1, mode=811, qual=X10-INDEX,1
DBINFO ID=$1, mode=812, qual=X10-INDEX,1
DBINFO ID=$1, mode=833, qual=1,X10-INDEX
DBINFO ID=$1, mode=202, qual=1
DBLOCK ID=$1, mode=6, LockDescr=0001 0011 0001 0000 ....................@@
DBFIND ID=$1, dset = 1, mode=1
item = 2711 2020 2020 2020 '. ;
arg. = 3C35 2020 2020 2020 <5
DBGET ID=$1, dset = 1, mode=5
list = 0001 0001 0000 0000 ...................................................
buf. = 3020 2020 2020 2020 0 5 6 7 8
DBGET ID=$1, dset = 1, mode=5
list = 0001 0001 0000 0000 ...................................................
buf. = 3120 2020 2020 2020 1 5 6 7 8
DBGET ID=$1, dset = 1, mode=5
list = 0001 0001 0000 0000 ...................................................
buf. = 3220 2020 2020 2020 2 5 6 7 8
DBGET ID=$1, dset = 1, mode=5
list = 0001 0001 0000 0000 ...................................................
buf. = 3320 2020 2020 2020 3 5 6 7 8
DBGET ID=$1, dset = 1, mode=5
list = 0001 0001 0000 0000 ...................................................
buf. = 3420 2020 2020 2020 4 5 6 7 8
DBUNLOCK ID=$1
5 ENTRIES QUALIFIED
>
Just a brief description of the DBFIND trace shows:
DBFIND ID=$1, dset = 1, mode=1
item = 2711 2020 2020 2020 '. ;
arg. = 3C35 2020 2020 2020 <5
A little decoding shows: Item = HEX 2711, which is decimal 10001, which is the
TPI index number of the INDEX named X10-INDEX. Baseid $1, dataset is 1 (query
uses number), mode = 1, etc.
Regards
P.S. I hope this formats OK. Compuserve asks: reformattable or send as shown.
Seems like send as shown causes really long lines, so I'm trying reformattable.
Excuses in advance if this post is not well formatted. It looks fine on my
screen. Gee, I guess I should get a real INTERNET account...
|
