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
 

 

Regarding VARCHAR column during loading Table

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

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Fri Nov 14, 2008 2:36 pm    Post subject: Regarding VARCHAR column during loading Table
Reply with quote

Hi,

We have a column in Target table as VARCHAR(255) and this same field has declaration in input file as CHAR(30). Now I have to load this target table VARCHAR filed with CHAR filed.

I have declared in load card as VARCHAR and given 257 length but while loading it considers first 2 bytes of the input filed as length of the field. I have given first 2 bytes as spaces using SORT and trying to execute the JCL. Due to some problem in our mainframes, running very slowly and still it's in process.

E.g.,

Data with CHAR(30). This is starting at column 13. 2 dots is Binary field.
Code:

----+----1----+----2----+----3----+----4----+--
********************************* Top of Data *
AA        ..SECURITY COMMISSIONS-NYSE         
AB        ..SECURITY COMMISSIONS-OTC           

I changed the Binary field into Zonal and inserted 2 spaces before actual data of this CHAR(30) field. Data after massaging as below.

Code:

----+----1----+----2----+----3----+----4----
********************************* Top of Dat
AA        00001  SECURITY COMMISSIONS-NYSE 
AB        00004  SECURITY COMMISSIONS-OTC   


Now the CHAR(30) field's data is starting at 18th column. This is the load file which loads data into the table.

I would like to know whether this would be executed successfully as I have provide 2 spaces in the lenght field? If not how should we handle the VARCHAR field during the load process?
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2239
Location: @my desk

PostPosted: Fri Nov 14, 2008 2:46 pm    Post subject:
Reply with quote

Hello

Which utility are you using to load your table? Post the error messages issued by your failed job.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Fri Nov 14, 2008 3:13 pm    Post subject:
Reply with quote

Arun,

I am using DSNUPROC utility. As mentioned above, due to some problems in our CLASS, JOB is still in queue.

I wanted to confirm whether this would be executed successfully or due to spaces in the length field would it get abended?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2239
Location: @my desk

PostPosted: Fri Nov 14, 2008 3:47 pm    Post subject:
Reply with quote

ksk,

Loading a VARCHAR column would expect the first 2 bytes to be the length(in binary) of the character data to be loaded.

From the unformatted data posted above, I suspect the 2 byte data at pos-11 to be the length field. If that is the case, you just need to mention the starting position of VARCHAR data as pos-11 and there is no need to format the data. BTW, how are you creating this load file.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Fri Nov 14, 2008 5:48 pm    Post subject:
Reply with quote

Arun,

2 byte data at position 16 is the lenght field. You can see 2 spaces in these columns(16-17) and mentioned 16 as starting position for VARCHAR field in load card(actual VARCHAR filed data starts from 18th cloumn).

Below is the load FYR.

Code:

BLOT_CD                                POSITION(       1         )
CHAR(                     10) ,                                   
CMSN_PROD_CD                           POSITION(      11         )
CHAR(                      5) ,                                   
BLOT_DESC                              POSITION(      16         )
VARCHAR                                                           
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2239
Location: @my desk

PostPosted: Fri Nov 14, 2008 6:22 pm    Post subject:
Reply with quote

ksk,
ksk wrote:
2 byte data at position 16 is the lenght field.
ksk wrote:
You can see 2 spaces in these columns

Are you still using the formatted file?(file having spaces at pos-16).You should n't overlay spaces to this position. Use the original file in which you have the length field populated.
Quote:
how are you creating this load file.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Fri Nov 14, 2008 8:27 pm    Post subject:
Reply with quote

Arun,

As I have explained in my first post, target column declaration is VARCHAR(255) and original file has CHAR(30) for this field. I have formatted the original file to change CHAR(30) to VARCHAR(255) as the target column is VARCHAR(255). So, I have to use formatted file but my doubt is as length field of VARCHAR has spaces, would it work or abended?

Using SORT utility, I am creating load file. Original file is unload of some other table and this unload file had declaration

Code:

First field                    CHAR(10)
2nd field                     SMALL INT [b](S9(04) COMP)[/b]
3rd Field                     CHAR(30)


Using SORT utility I changed this file to the following format as target table has the following format.

Code:

First Field                    CHAR(10)
2nd Field                     CHAR(5)
3rd field                      VARCHAR(255)


I hope you understood my explanation. Please let me know if you need any other information.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2239
Location: @my desk

PostPosted: Fri Nov 14, 2008 8:41 pm    Post subject:
Reply with quote

ksk,

AFAIK you should provide the length field of a VARCHAR column in binary format, nomatter how you do it. thru sort or thru some other program. It'll not work with spaces/whatever value you like to give.
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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts loading a Modified Mapset Susanta CICS 2 Tue Mar 14, 2017 5:43 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm


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