× 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.


  1.  Home
  2. MIDRANGE-L
  3. April 2006

Re: AW: *Max4GB or *Max1TB

Ok, I've used Visual Explain many times in the past, but I have never used
it on a DBMON job that I allowed to run against all active system jobs.

So, I'm doing that now (mainly as practice), and I've noticed that they
queries coming from our reporting system are coming through in a fully
executable form (all arguments are valid and listed) while my ERP (JDE
OneWorld) is sending queries through with parameter markers (colx = ?).

What accounts for this difference?


"Vernon Hamberg" <vhamberg@xxxxxxxxxxx> wrote in
message news:7.0.1.0.2.20060410064214.0379ce18@xxxxxxxxxxxxxx
> Database monitor is STRDBMON/ENDDBMON. It can be run over an entire
> system or over selected jobs. Result is an outfile that has a bunch
> of different record types for each kind of information. It is what is
> used by Opsnav when you start an SQL monitor. I don't think Opsnav
> will use an existing data set, don't remember. The link
>
> http://www-03.ibm.com/servers/eserver/iseries/db2/dbmonqrys.htm
>
> has queries you can use to analyze the data, including one for the
> indexes advised most often. And this link
>
> http://www.redbooks.ibm.com/redpapers/pdfs/redp0502.pdf
>
> is an excellent resource for this monitor. A number of the authors
> are/were from the database performance group in Rochester.
>
> I found those by going to www.iseries.ibm.com/db2 and putting
> "strdbmon" in the search field. They are also there under various
> headings under support.
>
> You might also consider contacting Centerfield Technology (used to be
> called Bradenmark, product name used to be Database Essentials), a
> former employer of mine. They have a service called
> database/ASSESSMENT that has you run their command, including the
> monitor, for a while, then you send the results to them and they give
> you a report with recommendations, including possible impact of
> adding indexes. Makes a good one-shot option.
> www.centerfieldtechnology.com is the site and one of the posters to
> this list, Elvis Budimlic, works there.
>
> HTH
> Vern
>
> At 05:53 AM 4/10/2006, you wrote:
>
> >    These are all good suggestions, but one problem is that the client
did not
> >    buy the source code to the application. The vendor told them they
> >    "wouldn't need it" because the vendor would be able to take care of
any
> >    modifications that were needed.
> >
> >    Now that vendor has been sold, and few of the employees were
retained. The
> >    source code issue will be addressed today as the number one item on
my
> >    agenda with these folks. They are going to hear that they were penny
wise
> >    and pound foolish.
> >
> >    What is the database monitor Clare mentioned?
> >    --
> >
> >    Paul Nelson
> >    Arbor Solutions, Inc.
> >    708-670-6978  Cell
> >    pnelson@xxxxxxxxxx
> >    -----midrange-l-bounces@xxxxxxxxxxxx
wrote: -----
> >
> >      To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
> >      From: "Hauser, Birgitta"
<Birgitta.Hauser@xxxxxxxxxxx>
> >      Sent by: midrange-l-bounces@xxxxxxxxxxxx
> >      Date: 04/10/2006 05:03AM
> >      Subject: AW: *Max4GB or *Max1TB
> >
> >      Hi,
> >
> >      but be aware, if you create additional indexes with the same key
fields,
> >      additional access paths are created! An index with a 64K page size
will
> >      not
> >      share an access path with a logical file with a smaller page size.
> >      Additioal access paths may affect performance as soon as a record
gets
> >      updated, because all access paths on the based table must be
updated.
> >      If there are already 250 logical files (with may be 150 different
access
> >      paths), I'd reconsider to add additional access paths.
> >
> >      If you create an index first and after a logical file with the same
(or
> >      less) key fields in the same sequence the logical file will share
the
> >      access
> >      path with the index and also adopt the larger page size.
> >
> >      An SQL index will only share the access path with an other SQL
index
> >      that
> >      has the same key fields in the same sequence. An SQL index with
less key
> >      fields, but in the same sequence will NOT share an access path with
an
> >      other
> >      index.
> >
> >      An SQL index can be used with native I/O like any other keyed
logical
> >      file.
> >      That means it can specified in RPG in the F-Specs like any logical
file.
> >
> >      In this way, I'd replace keyed logical files without joins or
> >      select/omit
> >      clauses through SQL indexes.
> >
> >      After creating all the new indexes, I'd delete all existing logical
> >      files
> >      and recreate them, to allow them to share access path and the
larger
> >      page
> >      size with the new indexes.
> >
> >      Birgitta
> >
> >      "If you think education is expensive, try ignorance"
> >      (Derek Bok)
> >
> >      -----Urspru:ngliche Nachricht-----
> >      Von: midrange-l-bounces@xxxxxxxxxxxx
> >      [mailto:midrange-l-bounces@xxxxxxxxxxxx]
Im Auftrag von Clare Holtham
> >      Gesendet: Montag, 10. April 2006 10:26
> >      An: Midrange Systems Technical Discussion
> >      Betreff: Re: *Max4GB or *Max1TB
> >
> >      Not BPCS is it???
> >      Seriously, I would certainly change them all to Max1TB, it does
improve
> >      performance. But Walden is right, it is worth having SQL indexes
for the
> >      most used ones too. It's well worth just running the database
monitor
> >      over
> >      the app for a while and then creating the indexes recommended.
> >
> >      cheers,
> >
> >      Clare
> >
> >      Clare Holtham
> >      Director, Small Blue Ltd - Archiving for BPCS
> >      Web: www.smallblue.co.uk
> >      IBM Certified iSeries Systems Professional
> >      Email: Clare.Holtham@xxxxxxxxxxxxxxx
> >
> >      ----- Original Message -----
> >      From: <pnelson@xxxxxxxxxx>
> >      To: "Midrange Systems Technical Discussion"
<midrange-l@xxxxxxxxxxxx>
> >      Sent: Sunday, April 09, 2006 12:05 AM
> >      Subject: Re: *Max4GB or *Max1TB
> >
> >      >I think I'll rip through and change everything in one fell swoop.
Next
> >      > weekend is Easter, and they won't have anybody working.
> >      > --
> >      >
> >      > Paul Nelson
> >      > Arbor Solutions, Inc.
> >      > 708-670-6978  Cell
> >      > pnelson@xxxxxxxxxx
> >      >
> >      >
> >      >
> >      >
> >      > Vernon Hamberg <vhamberg@xxxxxxxxxxx>
> >      > Sent by:
midrange-l-bounces@xxxxxxxxxxxx
> >      > 04/08/2006 05:59 PM
> >      > Please respond to
> >      > Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxx>
> >      >
> >      >
> >      > To
> >      > Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxx>
> >      > cc
> >      >
> >      > Subject
> >      > Re: *Max4GB or *Max1TB
> >      >
> >      >
> >      >
> >      >
> >      >
> >      >
> >      > Hi Paul
> >      >
> >      > I searched the iSeries support site for ACCPTHSIZ and found a
couple
> >      > things. One has to do with access path sharing--
> >      >
> >      > In R410, the default for Access path size (ACCPTHSIZ) changed to
> >      > *MAX1TB rather than *MAX4GB on the CRTLF and CRTPF commands and
in
> >      > the SQL CREATE INDEX statement. Most existing access paths have
an
> >      > attribute of *MAX4GB. These access paths cannot be shared by
access
> >      > paths with an attribute of *MAX1TB. For access path sharing to
occur,
> >      > change existing access paths to ACCPTHSIZ(*MAX1TB) or specify
> >      > ACCPTHSIZ(*MAX4GB) on the new access paths.
> >      >
> >      > The other is from "The System Administrator's Companion to AS/400
> >      > Availability and Recovery"
> >      > Redbook http://www.redbooks.ibm.com/redbooks/pdfs/sg242161.pdf
> >      > section 16.14, where it says the 4-byte index is supposed to
> >      > circumvent seize locks. It also says not to mix the 2 kinds of
> >      indexes.
> >      >
> >      > HTH
> >      > Vern
> >      >
> >      > At 10:16 AM 4/8/2006, you wrote:
> >      >
> >      >>I've got a client experiencing performance problems with his
software
> >      >>package. Many of the files have a huge number of logicals built
over
> >      them
> >      >>for various purposes. One has over 250 logicals and joined
logicals.
> >      All
> >      >>of the files are defined as having an acces path size of 4GB.
> >      >>
> >      >>This system is also being hit with lots of ODBC requests that
were
> >      >>permitted to be built by the previous IT manager (windoze bigot).
> >      >>
> >      >>I know how to throttle back the ODBC impact, but should I change
the
> >      > acces
> >      >>path size to 1TB for just the logicals or both the physical and
its
> >      >>associated logicals to improve the overall performance?
> >      >>
> >      >>Thanks
> >      >>--
> >      >>
> >      >>Paul Nelson
> >      >>Arbor Solutions, Inc.
> >      >>708-670-6978  Cell
> >      >>pnelson@xxxxxxxxxx
> >      >>--
> >      >>This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
> >      > list
> >      >>To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> >      >>To subscribe, unsubscribe, or change list options,
> >      >>visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >      >>or email:
MIDRANGE-L-request@xxxxxxxxxxxx
> >      >>Before posting, please take a moment to review the archives
> >      >>at http://archive.midrange.com/midrange-l.
> >      >
> >      > --
> >      > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
> >      > list
> >      > To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> >      > To subscribe, unsubscribe, or change list options,
> >      > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >      > or email:
MIDRANGE-L-request@xxxxxxxxxxxx
> >      > Before posting, please take a moment to review the archives
> >      > at http://archive.midrange.com/midrange-l.
> >      >
> >      >
> >      > --
> >      > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
> >      > list
> >      > To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> >      > To subscribe, unsubscribe, or change list options,
> >      > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >      > or email:
MIDRANGE-L-request@xxxxxxxxxxxx
> >      > Before posting, please take a moment to review the archives
> >      > at http://archive.midrange.com/midrange-l.
> >      >
> >      >
> >
> >      --
> >      This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
> >      list
> >      To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> >      To subscribe, unsubscribe, or change list options,
> >      visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >      or email:
MIDRANGE-L-request@xxxxxxxxxxxx
> >      Before posting, please take a moment to review the archives
> >      at http://archive.midrange.com/midrange-l.
> >
> >      --
> >      This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
> >      list
> >      To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> >      To subscribe, unsubscribe, or change list options,
> >      visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >      or email:
MIDRANGE-L-request@xxxxxxxxxxxx
> >      Before posting, please take a moment to review the archives
> >      at http://archive.midrange.com/midrange-l.
> >--
> >This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> >To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> >To subscribe, unsubscribe, or change list options,
> >visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> >or email: MIDRANGE-L-request@xxxxxxxxxxxx
> >Before posting, please take a moment to review the archives
> >at http://archive.midrange.com/midrange-l.
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
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.