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



Hi,

Just a few additional comments to varying length columns:

The following snippets are copied from Coding Techniques for SQL Performance
written by Kent Milligan.
Even though the use of variable length columns (VARCHAR and VARGRAPHIC) is
popular on other databases, this technique may not be the best coding method
for DB2 UDB for iSeries. Let?s carefully consider the use of VARCHAR columns
in DB2 UDB for iSeries.

If your primary goal is saving disk space, then set the ALLOCATE setting for
a VARCHAR column to ?0? when defining VARCHAR. In fact, if you don?t specify
the ALLOCATE setting, it will automatically default to ?0?.

However, if you are more concerned about improving performance than saving
space, you will instead want to set the ALLOCATE value wide enough so that
it can accommodate 90 to 95 percent of the variable length values found
within that column. This will minimize the number of times that DB2 UDB for
iSeries must examine the data stored in the overflow area of iSeries.

VARCHAR columns are, however, more efficient on iSeries when there is a need
to provide for wildcard searching. That's because DB2 UDB for iSeries will
stop searching VARCHAR columns after a wildcard string search encounters
blanks that indicate the end of available characters. With fixed-length
characters, DB2 UDB for iSeries must search to the very end of the string,
even if the string contains blanks.

Internally, DB2 UDB for iSeries will automatically set the Allocate value to
the maximum length of the variable length column whenever the column length
is 30 bytes or less.

You should also be aware that VARCHAR columns with a small allocate value
can cause performance problems if the table also contains LOB (large object)
columns and the VARCHAR column is referenced frequently. The VARCHAR and LOB
columns are all stored in the same auxiliary overflow storage area. This
means that if you reference one of the columns in that area, the database
has to page in all the overflow storage columns. In general, DB2 UDB for
iSeries tries to avoid paging in large LOB columns, but it can't be avoided
when the row contains VARCHAR columns that are stored in the same auxiliary
storage area.

If LOB columns are accessed by remote clients via middleware, such as ODBC
or JDBC, then the LOB Locator Threshold setting can impact performance. The
use of locators minimizes the amount of LOB data that DB2 UDB for iSeries
will copy to the remote client. When locators are not used (Threshold
greater than column max length), than DB2 UDB for iSeries currently always
copies the maximum length of the LOB column instead of just the actual data
length. For instance, if a 2-megabyte LOB column contains a LOB value that's
200 kilobytes in actual length, than without locators, DB2 UDB for iSeries
will copy two megabytes of data to the requester ? instead of just the 200
kilobytes containing the actual data.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von CRPence
Gesendet: Friday, 27. November 2009 23:19
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: Another Question about variable length fields

Vern Hamberg wrote:

I'll check the data - I thought I had some that was longer than
the allocation, but the fields were defined as only 10 bytes -
maybe it keeps things that short in the main space - that'd be an
interesting optimization.


There is a minimum amount of varlen storage required to effect
creation of an auxiliary SID for a column. I do not recall the
minimal length, but perhaps nearly eighteen bytes to account both
the data length and the effective address within the aux piece.
With an explicit versus defaulted ALLOCATE(0), that may force to the
aux regardless. The /porting/ documents suggest character fields
under forty bytes should be CHAR instead of VARCHAR with no
allocation, to get more similar performance results to other SQL
databases. Creating additional fields and\or creating a longer
varlen\varchar field will likely be required to get the auxiliary
segment created for the file after at least one row requires the
overflow to the aux storage area.

IIRC someone made a change to ensure that after two RGZPFM with no
data in the aux SID then it is either truncated or destroyed; after
one reorganize, the auxiliary storage area may persist to maintain
performance accommodations for update & insert activity.

Regards, Chuck

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.