Re: Variable length fields question

Comments/Answers in-line.

On 2014-07-30, at 10:11 AM, "Voris, John" <john.voris@xxxxxxxxxxxxx> wrote:

These VarLen( ) fields are pretty cool. Let me ask a couple of last questions to confirm my understanding about VarLen() fields. . . .

If I have an RPG pgm that was originally written to do concatenation of Fixed Length Fields in the form of . . . Result = %Trim( Alpha1 ) + %Trim( Alpha2 ) ;

And if the underlying database is changed to now store these monstrous alpha fields as VarLen() then I assume that a recompile of the RPG will NOT be optimized enough to look and say "Hey, these are already trimmed already, so drop the trim and keep going. (This is not that far-fetched. I believe we have some horrible EDI - XML builder pgms that are doing this Trim and concatenate in RPG. )

You are correct - remember, just because a field is varying does not mean that it currently has no trailing blanks - so that kind of optimization would never be performed.

Additionally if the fields are turned into VarLen( 15 ), regular string manipulation handles them like regular Fixed-Length Fields

// alpha1 VarLen(15) Value of 'A234567890 '
// alpha2 VarLen(15) Value of 'B234567890 '

Result = Alpha1 + %Trim( Alpha2 ) ;
then the concatenation results in 'A234567890 B234567890'
because the Fixed Length (aka. Allocated-Length) must be respected !

Not quite sure what you're saying but I suspect you're wrong. Normally a varying field would have "lost" its trailing spaces when loaded. i.e. alpha1 = %TrimR(dataSource);

You would therefore never need the %Trim when building the larger string - in fact it is a huge performance benefit when building XML strings. CSV, HTML, etc. SO the build would simply be:

Result = Alpha1 + Alpha2; or sometimes Result = Alpha1 + fieldPadChar + Alpha2; // When a comma or blank or whatever is needed.

The version you show would be very unusual.

Now in the DDS, when the VarLen( ) is not stated, then the entire field is Variable-Length.
( Think of a CLOB Character Large Object ) everything in the field would be variable and would never ever need %Trim()

Varlen makes it variable length. But that just specifies the characteristic of the field - there is no implicit trimming. Remember spaces are characters too! For instance:

Vary = Fixed20; // Vary has a length of 20 no matter how many spaces are at the end of Fixed20

Vary = %TrimR(Fixed20); // Length of vary depends on how many trailing blanks were in Fixed20 - it can range from 0 to 20. %Len(Vary) will tell you how "full" it is.

The DDS manual has:
"The allocated-length parameter is optional.
Use it to specify the number of bytes (two byte characters in the case of graphic fields) allocated for the field in the fixed portion of the file.
If you do not specify the allocated-length parameter, the data for this field is stored in the variable length portion of the file."

So what Birgitta says now makes sense to me about how . . .
* Decisions are made as to where to store the data
* How to manipulate the data during processing (all done under the covers in the database, of course, irregardless of RPG or SQL to even CPYF )
* What we should consider in Database design for the data-entity types. Is the column searchable like XML or doing Wildcard searchs. . . . the SQL LIKE is a wildcard search.

Don't get too tied up in the mechanics (nice as it is to know) - varying fields are very useful just in your regular programs when string building regardless of whether they ever end up in the database or not.

- John Voris

from: "Birgitta Hauser"
subject: AW: Variable length fields question

6. Varying length columns are, more efficient on the IBM i when there is a need to provide for wildcard searching. That's because DB2 UDB for i will stop searching the varying length data 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.

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

Jon Paris

www.partner400.com
www.SystemiDeveloper.com