VARCHAR considerations in DB2.

[Ankit Kumar Gupta]

Hi,

In COBOL-SQL program, while inserting data into a VARCHAR field, is it necessary to populate the length host variable, before insert?

Is not it that DB2 automatically stores the length?

[zprogrammer]

Hi Ankit,

Db2 automatically pads for the whole length and does not save space , which is not needed and also breaks the purpose of having a varchar field ...

So it is better to pass the length of the data before inserting into the table

[Ankit Kumar Gupta]

Db2 automatically pads for the whole length and does not save space , which is not needed and also breaks the purpose of having a varchar field ...

What re you telling here Pandora-Box, sorry I did not get you?

[zprogrammer]

For If have a FIELD VARCHAR(40) and try to insert 'Pandora' using host variable without mentioning the length of the data what happens is it pads extra 33 spaces to 'pandora' and stores in table

so there no use in having a varchar if you are going to use the 40 bytes instead if you pass the length which 7 to the length field and store only the actual data is stored

[Anuj Dhawan]

In COBOL-SQL program, while inserting data into a VARCHAR field, is it necessary to populate the length host variable, before insert?

Yes.

Is not it that DB2 automatically stores the length?

Yes and no. DB2 will pick up the length of the column defined as VARCHAR. If the data in the column is always of maximum length as of column, well that's the length (but that beats the purpose to define the column as VARCHAR), however if the data is not of same length as column-length - DB2 won't determine it its own and you've to tell it to DB2.

Hope this helps.