|
View previous topic :: View next topic
|
| Author |
Message |
ksk
Active User
.jpg)
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
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 |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Hello
Which utility are you using to load your table? Post the error messages issued by your failed job. |
|
| Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
ksk
Active User
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
ksk
Active User
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
|
|
