VARCHAR to CHAR conversion

lvbntapasvi · New User Joined: 20 Jul 2006 Posts: 14

Hi,

In my DB2 table, I have a VARCHAR field which is 120 bytes long (length not inlcuded). The first 40 bytes of this field should be moved into a MAP field which is defined as CHAR X(40). I am manually editing the table field to have data. But while doing an XPEDITOR on the program this field is showing junk.

I have 2 questions here,

1) How do we enter data into a VARCHAR field?
2) How can we move the VARCHAR data into CHAR field?

Thanks in advance.

dick scherrer · Posted: Wed Jan 27, 2010 12:08 pm Post subject:

Hello,

Possibly there is something i misunderstand, but the way to get data into a varchar column or get data from a varchar column is to move the data to or from the host variables for the column. . .

Bharath Bhat · Posted: Wed Jan 27, 2010 1:37 pm Post subject:

Show us the declaration of the host variable in your program. Also tell us how are you editing table data. Did you run SPUFI/QMF to verify the data that you edited?

lvbntapasvi · New User Joined: 20 Jul 2006 Posts: 14

Hi Bharat, this is how the host variable is defined for the VARCHAR field,

Bharath Bhat · Posted: Wed Jan 27, 2010 5:23 pm Post subject:

Is the program abending or giving unexpected results? or are you just wondering why XPEDITOR is showing junk?
If you are looking at the variable DCB0765-TRN-EXT-CDE-DESC in xpeditor, it will display the first two bytes as junk. But, DCB0765-TRN-EXT-CDE-DESC-TEXT might still contain the expected values.

bhairon singh rathore · New User Joined: 19 Jun 2008 Posts: 66 Location: banglore

GuyC · Posted: Thu Jan 28, 2010 4:06 pm Post subject:

probably editing thru fileaid.

if varchar-len > 0
move varchar-text(1:varchar-len) to map-field
end-if

Another possible cause is unicode/ebcdic conversion with columns defined for bit data.

lvbntapasvi · New User Joined: 20 Jul 2006 Posts: 14

Hi Guyc, we tried populating the CHAR field thru the suggestion given by you. But, it didnt work. Any idea about the UTF8 and EBCDIC conversion if we need to do it?

GuyC · Posted: Tue Feb 02, 2010 2:06 pm Post subject:

In normal situations any conversion is handled by DB2.

prashant_2420 · New User Joined: 06 Jan 2010 Posts: 2 Location: Oregon

Hi lvbntapasvi,

If you know that first 40 bytes are required,

And you are using COBOL to achieve this:
you can directly use
MOVE DCB0765-TRN-EXT-CDE-DESC-TEXT(1:40) TO MAP-FIELD (this answers your question 2)
You can use a loop if you want it for all the rows.

To answer qusetion 1, you can set the length variable of the VARCHAR field to the max limit of characters you can have. Then, move the actual value (string or any other variable as per your requirement) in the TEXT part of the VARCHAR field.

If you want to use DB2:
you can use SET command as follows:
ALTER TABLE SET MAP-FIELD = SUBSTR(DCB0765-TRN-EXT-CDE-DESC,1,40)
This command will set MAP-FIELD with first 40 bytes of DCB0765-TRN-EXT-CDE-DESC field.

Hope I understood your problem correctly and this helps!