| View previous topic :: View next topic |
| Author |
Message |
rajeshkoratti
Beginner
Joined: 14 Feb 2006
Posts: 42
Topics: 22
|
 Posted: Thu Oct 13, 2011 7:04 pm Post subject: COMP Data in VARCHAR Field |
 |
|
Hi
I am in process of doing some data cleanup. And in our shop i found out that a particular table has 2 fields, An INTEGER and a VARCHAR. The data in these fields are quite strange.
The INTEGER field has a 9 digit number and the VARCHAR has the comp representation of the Integer field. For example
| Code: | INTEGER VARCHAR (Hex ON)
37546819 03E4
2CB3 |
Now some of the rows int he table dont have a entry in the VARCHAR field and my job is to write an update spufi to update the VARCHAR field. How do i write a spufi that satisfies the above criteria.
Upon Further research i found out that the program which does the insert of this data is using a redefines to achieve this result
| Code: | 05 WS-INTEGER PIC 9(09) COMP.
05 WS-VARCHAR REDEFINES WS-INTEGER PIC X(04).
|
How can i do this with just a spufi query? Any pointers?
_________________
Thanks and Regards..
Rajesh |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu Oct 13, 2011 8:18 pm Post subject: |
|
|
rajeshkoratti,
Try this Untested Sql
| Code: |
UPDATE TABLE
SET VARCHAR_COL = 'your update value'
WHERE INTEGER_COL = 37546819
;
|
Kolusu |
|
| Back to top |
|
|
rajeshkoratti
Beginner
Joined: 14 Feb 2006
Posts: 42
Topics: 22
|
| Posted: Thu Oct 13, 2011 9:54 pm Post subject: |
|
|
Thanks Kolusu
However, I think i wasnt specific enough in my previous post. both the VARCHAR as well as the INTEGER contain the same data. The INTEGER is of 9(9) COMP and the VARCHAR is a redefines of the INETGER.
_________________
Thanks and Regards..
Rajesh |
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Thu Oct 13, 2011 11:02 pm Post subject: |
|
|
Suggest you write a little bit of code to accomplish this using the samer kind of redefines..
Your organization should want an audit trail of what was changed which would be easy to implement in the code also.
_________________
All the best,
di |
|
| Back to top |
|
|
GuyC
Supermod
Joined: 27 May 2011
Posts: 35
Topics: 0
|
| Posted: Fri Oct 14, 2011 4:32 am Post subject: |
|
|
For column i :
| Code: | EBCDIC_CHR(mod(i/power(2,24),power(2,8))) ||
EBCDIC_CHR(mod(i/power(2,16),power(2,8))) ||
EBCDIC_CHR(mod(i/power(2, 8),power(2,8))) ||
EBCDIC_CHR(mod(i ,power(2,8))) ) |
|
|
| Back to top |
|
|
rajeshkoratti
Beginner
Joined: 14 Feb 2006
Posts: 42
Topics: 22
|
| Posted: Fri Oct 14, 2011 9:36 am Post subject: |
|
|
Hello GuyC
Please could you explain your solution a bit more..
_________________
Thanks and Regards..
Rajesh |
|
| Back to top |
|
|
rajeshkoratti
Beginner
Joined: 14 Feb 2006
Posts: 42
Topics: 22
|
| Posted: Fri Oct 14, 2011 10:24 am Post subject: |
|
|
GuyC
I believe this works only for DB2 v 10
_________________
Thanks and Regards..
Rajesh |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Fri Oct 14, 2011 10:50 am Post subject: |
|
|
rajeshkoratti,
Are you asking to help setting up the SQL in the program or is this a 1 time data clean up work? If your intention is to have 37546819 in the varchar column then isn't it simple to use the CHAR function?
why can't use CHAR function directly and insert into table?
| Code: |
INSERT INTO table VALUES(37546819,CHAR(INT(37546819)));
|
will make both columns have 37546819, 37546819
Kolusu |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Fri Oct 14, 2011 1:25 pm Post subject: |
|
|
Kolusu,
I think what he wants is a 4-byte comp (binary) field in the varchar column.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Fri Oct 14, 2011 3:25 pm Post subject: |
|
|
| dbzTHEdinosauer wrote: | Kolusu,
I think what he wants is a 4-byte comp (binary) field in the varchar column. |
hmm well then he can generate the integer values in hex and use them as hex strings to insert into the table
| Code: |
INSERT INTO table VALUES(37546819,X'023CEB43');
|
|
|
| Back to top |
|
|
rajeshkoratti
Beginner
Joined: 14 Feb 2006
Posts: 42
Topics: 22
|
| Posted: Fri Oct 14, 2011 6:35 pm Post subject: |
|
|
Thanks GuyC
Your solution worked. I wrote a SQL script similar to this
| Code: | UPDATE TABLENAME
SET VARCHAR_COL = (
EBCDIC_CHR(MOD(INTEGER_COL/POWER(2,24),POWER(2,8))) ||
EBCDIC_CHR(MOD(INTEGER_COL/POWER(2,16),POWER(2,8))) ||
EBCDIC_CHR(MOD(INTEGER_COL/POWER(2,8),POWER(2,8))) ||
EBCDIC_CHR(MOD(INTEGER_COL,POWER(2,8)))
)
; |
_________________
Thanks and Regards..
Rajesh |
|
| Back to top |
|
|
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Fri Oct 14, 2011 11:07 pm Post subject: |
|
|
Suggest you also try the code from Kolusu.
I suspect that most of the people who will maintain your code will be confused by the mod/power code. . . Though it is rather neat 
_________________
All the best,
di |
|
| Back to top |
|
|
GuyC
Supermod
Joined: 27 May 2011
Posts: 35
Topics: 0
|
| Posted: Mon Oct 17, 2011 3:09 am Post subject: |
|
|
split any integer in to 4 separate bytes :
the right-most byte is : i mod 256
the tree left bytes are : i / 256
so repeating this process 3 times you get (from right to left)
i mod 256
( i / 256) mod 256
((i / 256) / 256 ) mod 256
(((i /256) / 256 ) / 256 ) mod 256
on these 4 bytes you then perform EBCDIC_CHR() |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
