Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Length field of varchar

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
deeptik

New User


Joined: 21 Mar 2006
Posts: 8

PostPosted: Wed May 21, 2008 3:21 pm    Post subject: Length field of varchar
Reply with quote

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..
Back to top
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed May 21, 2008 5:53 pm    Post subject:
Reply with quote

Quote:

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 ?


You need to populate the correct length. If it is 0, the empty string is valid in a VARCHAR field. If it is 3 then you'll get JOH. 4 will give you JOHN. If you do the following
Code:

MOVE LENGTH OF ENAME-TEXT to ENAME-LEN

then you will store 'JOHN ' stored in the datebase.

Quote:

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' ?


I am pretty sure it will be 'IAN '. Although it depends on the variable being put into the field. If you are using something like a PIC X(3) for your update you'll get that. If it is PIC X(4) or anything larger you'll get the next space in the variable updated into there.

However using SPUFI it would figure it out for you based on the data between the ' marks and the data will be 'IAN'.
Back to top
View user's profile Send private message
deeptik

New User


Joined: 21 Mar 2006
Posts: 8

PostPosted: Thu May 22, 2008 10:07 am    Post subject:
Reply with quote

This answers my question....

Thanks so much Steve !
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Wed May 28, 2008 8:05 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts outrec field outside range Danielle.Filteau SYNCSORT 10 Sat Mar 04, 2017 2:37 am
No new posts Writing on to GSAM variable length rohanthengal JCL & VSAM 6 Fri Mar 03, 2017 7:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us