Re: Tables with VARCHAR

IBM recommends allocate large enough to handle 90-95% of the data
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajq/vardatatypes.htm

I believe that one 26 byte pointer is include for every record, regardless
of how many VARxxx/LOBxxx objects are present.  This is inferred from the
MAX ROW SIZE documentation, "The maximum row buffer size is 32766 or, if a
VARCHAR, VARGRAPHIC, VARBINARY, LOB, or XML column is specified, 32740."

Yes, VARCHAR2(80) will always be full size in the record buffer presented
to your program.

Charles



On Thu, Apr 5, 2018 at 3:04 AM, Jevgeni Astanovski <[email protected]>
wrote:

> Thanks, Jon and Charles.
>
> Suspected that there is no simple answer and I need to make content
> analysis.
> So first of all I've identified the 3 fields as potential candidates for
> switching to VARCHAR.
> They are of 80, 255 and 140 length.
> Then I applied a query that Charles advised to smaller pieces of data -
> just one month instead of 12 years.
> Selected 2 one-month chunks, in the beginning and in the end of the period,
> as data usage could change also.
> Got numbers, sorted them and now I can easily find, that, for example for
> CHAR(80) field, in 99% of records actual field length is 30 or less. This
> should make sense to switch to VARCHAR2(80) ALLOCATE(30) I suppose?
> By the way - are there any "rules of thumb" for this %? Should it be 80% or
> 99% for example?
>
> I thought that I've indicated it in my question, however probably forgot -
> to write that I'm using record-level access (_Rreadx) functions.
> Do I understand correctly that for these functions ALLOCATE number has no
> effect? I mean that if record has one field VARCHAR2(80), its size will be
> ALWAYS 82 bytes in the returned buffer?
>
> And I have not fully understood if this 16-bytes pointer to "overflow" area
> is allocated to every record or only to those, where this "overflow"
> happened in reality...
> Will now make some experiments with the information obtained.
>
> Thanks again,
> J.A.
>
>
>
>
> On Wed, Apr 4, 2018 at 7:12 PM, Charles Wilt <[email protected]>
> wrote:
>
> > Yep...it depends...
> >
> > what's "pretty large char fields"?  The entire record is only 650
> > bytes...so they can't be all that large to my way of thinking..
> >
> > What's "almost empty"?
> >
> > run a
> > select length(trim(MYFLD)), count(*) as cnt
> > from myfile
> > group by  length(trim(MYFLD))
> > order by 2 desc
> >
> > If the field is CHAR(100) and most of the records have 20 chars or
> less...
> >
> > Then you might be better off with VARCHAR(100) ALLOCATE (20)
> >
> > That'd save 78 bytes for every record with less than 20 chars.
> >
> > However, one additional point to consider.  Are you using SQL or record
> > level access (RLA)?
> >
> > With RLA, the system will do two disk I/Os for every record with more
> than
> > 20 chars.  With SQL, those 2 I/Os will only be done if you ask for MYFLD
> to
> > be returned.
> >
> > (Thus the reason ALLOCATE(0) is not recommended, unless most records are
> > empty)
> >
> > Charles
> >
> >
> > On Wed, Apr 4, 2018 at 8:30 AM, Jon Paris <[email protected]>
> > wrote:
> >
> > > As is often the case - it depends.
> > >
> > > When you define a field as Varchar you can specify both a "normal"
> length
> > > and a maximum length.  Only the "normal" part is held with the main
> > record
> > > and therefore if most of your records are within that length then the
> > file
> > > size will reduce.
> > >
> > > There is however an overhead for linking to the extended space - can't
> > > recall how long it is 20 bytes is ringing a faint bell.  This topic has
> > > been discussed in the past on the Midrange list - I'd check it for you
> > but
> > > have to run out.
> > >
> > >
> > > Jon Paris
> > >
> > > www.partner400.com
> > > www.SystemiDeveloper.com
> > >
> > > > On Apr 4, 2018, at 10:05 AM, Jevgeni Astanovski <
> [email protected]>
> > > wrote:
> > > >
> > > > Hi,
> > > > Does anyone here know if switching from CHAR to VARCHAR gives any
> > > positive
> > > > effect in terms of table size?
> > > > Explain the situation briefly.
> > > > I've got an application written on ILE/C that works with rather big
> > table
> > > > (historical data since 1996). Currently the table size is 260M
> records
> > > and
> > > > PF size 170GB.
> > > > The record size is app. 650 bytes and it has some pretty large char
> > > fields,
> > > > that can be either empty or almost empty :-)
> > > >
> > > > Technically I can make a new table with VARCHARs and rewrite my
> program
> > > to
> > > > use VARCHAR instead of CHAR, but shall it reduce the table size?
> > > >
> > > > Somehow I suspect that it will only increase the table...
> > > >
> > > > TIA and regards,
> > > > Jevgeni.
> > > > --
> > > > This is the Bare Metal Programming IBM i (AS/400 and iSeries)
> (C400-L)
> > > mailing list
> > > > To post a message email: C400-L-Zwy7GipZuJhWk0Htik3J/[email protected]
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: https://lists.midrange.com/mailman/listinfo/c400-l
> > > > or email: C400-L-request-Zwy7GipZuJhWk0Htik3J/[email protected]
> > > > Before posting, please take a moment to review the archives
> > > > at https://archive.midrange.com/c400-l.
> > > >
> > >
> > > --
> > > This is the Bare Metal Programming IBM i (AS/400 and iSeries) (C400-L)
> > > mailing list
> > > To post a message email: C400-L-Zwy7GipZuJhWk0Htik3J/[email protected]
> > > To subscribe, unsubscribe, or change list options,
> > > visit: https://lists.midrange.com/mailman/listinfo/c400-l
> > > or email: C400-L-request-Zwy7GipZuJhWk0Htik3J/[email protected]
> > > Before posting, please take a moment to review the archives
> > > at https://archive.midrange.com/c400-l.
> > >
> > >
> > --
> > This is the Bare Metal Programming IBM i (AS/400 and iSeries) (C400-L)
> > mailing list
> > To post a message email: C400-L-Zwy7GipZuJhWk0Htik3J/[email protected]
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/c400-l
> > or email: C400-L-request-Zwy7GipZuJhWk0Htik3J/[email protected]
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/c400-l.
> >
> >
> --
> This is the Bare Metal Programming IBM i (AS/400 and iSeries) (C400-L)
> mailing list
> To post a message email: C400-L-Zwy7GipZuJhWk0Htik3J/[email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/c400-l
> or email: C400-L-request-Zwy7GipZuJhWk0Htik3J/[email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/c400-l.
>
>
-- 
This is the Bare Metal Programming IBM i (AS/400 and iSeries) (C400-L) mailing list
To post a message email: C400-L-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/c400-l
or email: C400-L-request-Zwy7GipZuJhWk0Htik3J/[email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/c400-l.