Posted: Thu Jun 28, 2012 12:54 pm Post subject: Integer to Varchar
Hi,
I have integer variable X data in a flat file from 10-14 positions and 15th is occupied by Null indicator.Now I want to load this data into a DB2 table but X is defined as Varchar(20) nullable. How do I change my Load cards to fit the data?
I tried this in my load cards but didn't work
X position (10:5) varchar NULLIF(15)='?'
If you want this more quickly, you could copy the file and make the needed changes to the data and then load this new file. NOT a solution, but alllows you to load the data to db2.
Then, continue with the diagnosis of the "real" solution. . . _________________ All the best,
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
Posted: Fri Jun 29, 2012 10:38 am Post subject:
one of the side benefits of this particular thread is: (wait for it!)
the solution to having a file with fields in a format different than the datatype of the column
is
learning this scripting language
so that the appropriate values are populated in the column.
i don't know, but i would not be surprised if
you could cast a display numeric to a integer column
or as we are doing here
casting replace(char(5),' ','') to varchar(20)
to which utility do these control cards belong? _________________ Dick Brenholtz
American living in Varel, Germany
one of the side benefits of this particular thread is: (wait for it!)
the solution to having a file with fields in a format different than the datatype of the column
is
learning this scripting language
so that the appropriate values are populated in the column.
i don't know, but i would not be surprised if
you could cast a display numeric to a integer column
or as we are doing here
casting replace(char(5),' ','') to varchar(20)
Joined: 07 Oct 2010 Posts: 7 Topics: 0 Location: india
Posted: Tue Jul 03, 2012 1:24 pm Post subject:
Please do as below:
Step1: Reformat your input file as per the syspunch by using SORT utility.
Code:
OPTION ZDPRINT
INREC BUILD=(1,12,SFF,TO=ZD,LENGTH=12)
SORT FIELDS=(1,12,A),FORMAT=ZD
SUM FIELDS=(1,12,ZD)
OUTREC FIELDS=(1,12,ZD,EDIT=(SIIIIIIII.IT),SIGNS=(,-)
Step2: Change the Syspunch to load only char data; so you need to advance the position of your varchar field by 2(length field) and use CHAR instead of VARCHAR for this field.
And do NULLIF(15)='?" ; where 15 will be the end of the VARCHAR field after the length.
Step3: Run the load jcl.
Hope it will work for you. Please let me know if any question. Thanks.
Please do as below:
Step1: Reformat your input file as per the syspunch by using SORT utility.
Code:
OPTION ZDPRINT
INREC BUILD=(1,12,SFF,TO=ZD,LENGTH=12)
SORT FIELDS=(1,12,A),FORMAT=ZD
SUM FIELDS=(1,12,ZD)
OUTREC FIELDS=(1,12,ZD,EDIT=(SIIIIIIII.IT),SIGNS=(,-)
Step2: Change the Syspunch to load only char data; so you need to advance the position of your varchar field by 2(length field) and use CHAR instead of VARCHAR for this field.
And do NULLIF(15)='?" ; where 15 will be the end of the VARCHAR field after the length.
Step3: Run the load jcl.
Hope it will work for you. Please let me know if any question. Thanks.
Hi,
I have a question on this.
1) What is this lenght 12?
OPTION ZDPRINT
INREC BUILD=(1,12,SFF,TO=ZD,LENGTH=12)
SORT FIELDS=(1,12,A),FORMAT=ZD
SUM FIELDS=(1,12,ZD)
OUTREC FIELDS=(1,12,ZD,EDIT=(SIIIIIIII.IT),SIGNS=(,-)
2) How do I change SYSPUNCH to load only char data.Because I was assuming that SYSPUNCH will tell from which position to take data from the input file.Can we also tell from which position to load data into Db2 field?Can you please provide me the example?
3) So for my req, from where I do need to load integer value from 10-13 into varchar field of 20,can you specify the syspunch change for this?
Joined: 07 Oct 2010 Posts: 7 Topics: 0 Location: india
Posted: Thu Jul 05, 2012 4:09 pm Post subject:
OPTION ZDPRINT
INREC BUILD=(1,12,SFF,TO=ZD,LENGTH=12)
SORT FIELDS=(1,12,A),FORMAT=ZD
SUM FIELDS=(1,12,ZD)
OUTREC FIELDS=(1,12,ZD,EDIT=(SIIIIIIII.IT),SIGNS=(,-)
Consider the above as an example; What I meant; please reformat the input file as per the table structure in ur case - convert from integer to character and move this field by 2 position forward;
use
OUTREC FIELDS=(10,2X,12:10,5,ZD,EDIT=(SIIII),SIGNS=(,-)
(Not tested)
because varchar fields start with 2 byte length followed by the actual data and end by a character for NULLIF.
And then you need to change the syspunch while loading:
for example: for varchar column would be defined as below in the table:
ws_column POSITION (10) VARCHAR NULLIF(17) = '?'
Before loading change the above syspunch like below:
ws_column POSITION (12) CHAR(5) NULLIF(17)='?'
POSITION(12): because varchar columns start with length of the field which takes 2 byte length (S9(4) COMP).
OPTION ZDPRINT
INREC BUILD=(1,12,SFF,TO=ZD,LENGTH=12)
SORT FIELDS=(1,12,A),FORMAT=ZD
SUM FIELDS=(1,12,ZD)
OUTREC FIELDS=(1,12,ZD,EDIT=(SIIIIIIII.IT),SIGNS=(,-)
Consider the above as an example; What I meant; please reformat the input file as per the table structure in ur case - convert from integer to character and move this field by 2 position forward;
use
OUTREC FIELDS=(10,2X,12:10,5,ZD,EDIT=(SIIII),SIGNS=(,-)
(Not tested)
because varchar fields start with 2 byte length followed by the actual data and end by a character for NULLIF.
And then you need to change the syspunch while loading:
for example: for varchar column would be defined as below in the table:
ws_column POSITION (10) VARCHAR NULLIF(17) = '?'
Before loading change the above syspunch like below:
ws_column POSITION (12) CHAR(5) NULLIF(17)='?'
POSITION(12): because varchar columns start with length of the field which takes 2 byte length (S9(4) COMP).
I had tried but was not allowed to format the input.Anyhow thanks for your solution delta.I had applied Nascar's solution and it worked very well
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
