Length field of varchar

deeptik · New User Joined: 21 Mar 2006 Posts: 8

Hi,

My question is that while using embedded sql in cobol application program to insert data into a varchar field , is it necessary to populate the length host variable as well..or does Db2 automatically store the actual length(without the trailing spaces) .

Will the query fail if we don't populate the level 49 length variable or say we populate it with incorrect length ?

To make my question more clear , below is the table and corr queries. I could hv tried them myself but since I don't have access to compile DB2 pgms in my installation I will put them in this forum....

1) EMP TABLE (ENAME VARCHAR(50)
EID CHAR(7));

Corresponding dclgen

10 ENAME.
49 ENAME-LEN PIC S9(4) USAGE COMP.
49 ENAME-TEXT PIC X(50).
10 EID pic x(7).

If I need to insert Name = 'JOHN' and if I populate ENAME-TEXT='JOHN' and

1)ENAME-LEN = 4, value of ENAME stored in my table = 'JOHN' ?
2)ENAME-LEN = 3, will ENAME stored = 'JOH' or 'JOHN'
3)ENAME-LEN =0 (i.e I never populate this length) , what will be the value of ENAME ?

Basically, does value populated in length host variable determine the content of TEXT to be stored ( Well I guess the answer is a NO but still want to confirm) or is it only that you store the length for your own reference and and if it is stored wrongly DB2 can actually do nothing about it ?

2) Update condition. Suppose my current value in the database before update is 'JOHN'. Now I want to update 'JOHN' to 'IAN' but I forget to update the length host variable , what will be the value now in my database...will it be 'JIAN' ?

Thanks in advance..

stodolas · Posted: Wed May 21, 2008 5:53 pm

deeptik · New User Joined: 21 Mar 2006 Posts: 8

This answers my question....

Thanks so much Steve !

Suresh Ponnusamy · Posted: Wed May 28, 2008 8:05 pm

Hi Deeptik,

To added to Steve points,

I)

MOVE LENGTH OF ENAME-TEXT to ENAME-LEN will move the total length of the variable ENAME-TEXT to ENAME-LEN.

The main purpose of using VARCHAR field in DB2 is to conserve the memory space.

Suppose the length of the ENAME-TEXT is 100 and we are moving only 4 bytes to the data. If we use the above statement then, the 49 length field
ENAME-LEN will have 100 bytes. But we are moving only 4 bytes. In this case the remaining bytes will be filled up by spaces. Actual usage of VARCHAR in this place to conserve the memory.

So before moving the length to ENAME-LEN field, we need to calculate the actual length of the contents and that should be moved to the Length field.

2) Update condition. Suppose my current value in the database before update is 'JOHN'. Now I want to update 'JOHN' to 'IAN' but I forget to update the length host variable , what will be the value now in my database...will it be 'JIAN' ?

Please check.

The previous value in the Text fields is JOHN
The length in the length field is 4
The current value is IAN
But the length in the length field is 4

In this case if we move IAN to Text variable then, the value would be IANN, since Alphanumeric move is from Left to right. It will replace the first 3 characters.

Hope this helps.