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
 

 

VARCHAR to CHAR conversion

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

New User


Joined: 20 Jul 2006
Posts: 30

PostPosted: Wed Jan 27, 2010 10:08 am    Post subject: VARCHAR to CHAR conversion
Reply with quote

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

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 27, 2010 12:08 pm    Post subject:
Reply with quote

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

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Jan 27, 2010 1:37 pm    Post subject:
Reply with quote

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

New User


Joined: 20 Jul 2006
Posts: 30

PostPosted: Wed Jan 27, 2010 5:06 pm    Post subject: Reply to: VARCHAR to CHAR conversion
Reply with quote

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

Code:
10    DCB0765-TRN-EXT-CDE-DESC.               
   49 DCB0765-TRN-EXT-CDE-DESC-LEN         
                         PIC S9(4)                USAGE COMP. 
   49 DCB0765-TRN-EXT-CDE-DESC-TEXT         
                        PIC X(120).       

Also, I have not run SPUFI, I have entered some text direclty in the field of the table by editing it.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Jan 27, 2010 5:23 pm    Post subject:
Reply with quote

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

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Wed Jan 27, 2010 5:25 pm    Post subject:
Reply with quote

Quote:
Also, I have not run SPUFI, I have entered some text direclty in the field of the table by editing it.


Hi lvbntapasvi,

How can you edit a table without using QMF/SPUFI or Other DB2 tools.
Not sure how we can do that
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jan 28, 2010 4:06 pm    Post subject:
Reply with quote

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

New User


Joined: 20 Jul 2006
Posts: 30

PostPosted: Tue Feb 02, 2010 9:51 am    Post subject: Reply to: VARCHAR to CHAR conversion
Reply with quote

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

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Feb 02, 2010 2:06 pm    Post subject:
Reply with quote

In normal situations any conversion is handled by DB2.
Back to top
View user's profile Send private message
prashant_2420

New User


Joined: 06 Jan 2010
Posts: 2
Location: Oregon

PostPosted: Sat Feb 06, 2010 5:26 am    Post subject: Reply to: VARCHAR to CHAR conversion
Reply with quote

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!
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 File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Conversion of a VB file to FB. isssal DFSORT/ICETOOL 1 Mon Oct 31, 2016 10:23 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts EBCDIC to ASCII conversion using OCOPY mistah kurtz All Other Mainframe Topics 5 Tue Sep 06, 2016 2:26 pm


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