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

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 !

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()

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.

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

Return to Archive home page | Return to MIDRANGE.COM home page