× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: Re: database search question...
  • From: rob@xxxxxxxxx
  • Date: Fri, 30 Mar 2001 09:20:36 -0500


We have an 820-23A2 for our development machine.  (It's getting a faster
processor tonight.)  We have a BPCS file with 29,450 records in it.  I ran
the following on it:
select * from cliperf/iim
where idesc like '%120V%'

Ran in subsecond response time.

Then I ran:
select count(*) from cliperf/iim
where idesc like '%120V%'
This took about 7 seconds to return the value 1,617.

Standard BPCS.  No EVI's or whatnot.  SQL was ran using the STRSQL
interactive processor.

                             Display System Status
GDISYS
                                                             03/30/01
09:03:03
 % CPU used . . . . . . . :       14.4    Auxiliary storage:
 Elapsed time . . . . . . :   00:00:01      System ASP . . . . . . :
161.6 G
 Jobs in system . . . . . :       2690      % system ASP used  . . :
84.0214
 % perm addresses . . . . :       .050      Total  . . . . . . . . :
161.6 G
 % temp addresses . . . . :       .509      Current unprotect used :
6057 M
                                            Maximum unprotect  . . :
6370 M
 System    Pool    Reserved    Max   -----DB-----  ---Non-DB---
  Pool   Size (M)  Size (M)  Active  Fault  Pages  Fault  Pages
    1      500.00    115.30   +++++     .0     .0     .0     .0
    2      521.00      2.71    5000     .0     .0     .0     .0
    3       15.00       .00       5     .0     .0     .0     .0
    4      200.00       .00     250     .0     .0    1.8    1.8
    5      300.00       .00      20     .0     .0     .0     .0

*****
Then I ran this on our main production machine.  A 730-2C6E.  Against a
file with 28,935 records.  Again, subsecond response time.  The count(*)
took about a second and returned a value of 99.
                            Display System Status                     GDIHQ
                                                            03/30/01
09:09:19
% CPU used . . . . . . . :        6.8    Auxiliary storage:
% DB capability  . . . . :        1.1      System ASP . . . . . . :
203.7 G
Elapsed time . . . . . . :   00:00:01      % system ASP used  . . :
65.2517
Jobs in system . . . . . :      12529      Total  . . . . . . . . :
203.7 G
% perm addresses . . . . :       .054      Current unprotect used :
7094 M
% temp addresses . . . . :       .451      Maximum unprotect  . . :
9123 M
System    Pool    Reserved    Max   -----DB-----  ---Non-DB---
 Pool   Size (M)  Size (M)  Active  Fault  Pages  Fault  Pages
   1     2000.00    340.11   +++++     .0     .0     .0     .0
   2     2566.57       .16   17000     .0     .0     .0     .0
   3      275.00       .00      75     .0     .0     .0     .0
   4     1750.00       .00    3500     .0    4.4   12.5   19.6
   5     1500.00       .00     100  126.2  171.8    9.8    9.8
   6      100.42       .00      10     .0     .0     .0     .0

When someone tries to tell you it's normal for a 400 to run close to 100%,
they are trying to justify being cheap.  By the way this CPU supports about
30 copies of BPCS, payroll for about 3,000, and everyone's accounting.
Currently there are 26 jobs running in QBATCH and about 408 jobs in QINTER.
About 0 C/S jobs.  We plan on upgrading this machine to an 8xx model by the
end of the year.

Rob Berendt

==================
Remember the Cole!


                                                                                
                                         
                    "Hatzenbeler, Tim"                                          
                                         
                    <thatzenbeler@clini        To:     
"'MIDRANGE-L@midrange.com'" <MIDRANGE-L@midrange.com>             
                    tech.net>                  cc:                              
                                         
                    Sent by:                   Subject:     database search 
question...                                  
                    owner-midrange-l@mi                                         
                                         
                    drange.com                                                  
                                         
                                                                                
                                         
                                                                                
                                         
                    03/29/01 04:19 PM                                           
                                         
                    Please respond to                                           
                                         
                    MIDRANGE-L                                                  
                                         
                                                                                
                                         
                                                                                
                                         






How does ebay do it?  And can the as/400 do it?  Or do I just need a super
fast computer with a ton of memory...

The question is this....

I have a product file...  About 20,000 items... And I have end users who
want to be able to preform partial word searches on the description
field...  Seems simple enough, but the problem is speed...

If I do an rpg program, that reads every line of the product file and does
a %scan function... It works, but its not very fast...

I could to an SQL with the  like '%fuzzy%' but even thats slow... and if
they gave it a real generic lookup value, it will take forever...

So my question is this, does any one have a suggestion?  My 1st thought is
something like this.   Program a word index creator, that  would run
nighlty.

Product catalog
item#,description
1234, big red car
1235, little green car

and then a lookup data base, would look like this

search word, part#
---------------------------
big,1234
car,1234
car,1235
green,1235
little,1235
red,1234

and then if someone wanted [little car], it would do a search on [little]
and [car] and if both are positive to the same key(item#), then pull that
record...

I'm hoping someone could think of a better way...  I'm hoping there is an
os/400 database command to solve this problem, so that's why i'm posting it
in this list.

If not, the index creator doesn't seem too hard.

Thanks, tim




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

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.