How to convert 4 byte binay to integer



IBM's flagship relational database management system

How to convert 4 byte binay to integer

Postby shankar_dh » Tue Mar 18, 2014 1:40 pm

Hi All,

I am unloading the data from DB2 table using the DB2BATCH utility. Below is my SQL.
SELECT   SUM(ACCOUNT_BAL)     
        ,COUNT(ACCOUNT_ID) 
FROM     TAB600


Below is the output
----+----1----+----2---
***********************
            áÇ @    1 
00000000000046070000F00
000000000007583C0000100
-----------------------


From Position 18-21(4 byte) is my count.
Currently I am using below SORT card to convert Binary to Integer.
OUTREC=(1:C'Count is',9:18,4,BI,EDIT=(II,III,III,IIT))

I am getting the correct output.
My doubt is what is the maximum count it will accommodate in that 4 byte BINARY (i.e from position 18-21)
How much length I can give in my EDIT clause?
I am not sure how much would be the count for Production table!!

Regards,
Shankar.
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: How to convert 4 byte binay to integer using SORT

Postby skolusu » Tue Mar 18, 2014 9:51 pm

shankar_dh wrote:From Position 18-21(4 byte) is my count.
Currently I am using below SORT card to convert Binary to Integer.
OUTREC=(1:C'Count is',9:18,4,BI,EDIT=(II,III,III,IIT))

I am getting the correct output.
My doubt is what is the maximum count it will accommodate in that 4 byte BINARY (i.e from position 18-21)
How much length I can give in my EDIT clause?
I am not sure how much would be the count for Production table!!

Regards,
Shankar.


A 4 byte binary aka COMP field can accommodate -2,147,483,648 through +2,147,483,647 . But the question is why bother converting in a utility. You can use DIGITS functions to expand the count to a readable format. ie. DIGITS(COUNT(ACCOUNT_ID)) will give you a readable value.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: How to convert 4 byte binay to integer using SORT

Postby shankar_dh » Wed Mar 19, 2014 11:13 am

Thanks skolusu for your reply.
I tried with DIGITS(COUNT(ACCOUNT_ID))
It gave me 10 bytes of count.
My doubt is what if the count from DB2 table crosses 10 bytes?
Will it expand automatically ?
Because I am taking the reference of this count length in my next step. So I need to concrete length.
And also as you said "binary of 4 bytes accommodate -2,147,483,648 through +2,147,483,647"
Does not it accommodate -4294967295 to +4294967295 i.e 4 bytes of Binary can have a value of "FFFFFFFF" when you convert this to decimal you will get a value of 4294967295

Regards,
Shankar.
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: How to convert 4 byte binay to integer using SORT

Postby skolusu » Wed Mar 19, 2014 8:47 pm

shankar_dh wrote: And also as you said "binary of 4 bytes accommodate -2,147,483,648 through +2,147,483,647"
Does not it accommodate -4294967295 to +4294967295 i.e 4 bytes of Binary can have a value of "FFFFFFFF" when you convert this to decimal you will get a value of 4294967295


Shankar,

When I said "binary of 4 bytes accommodate -2,147,483,648 through +2,147,483,647" it is in regards to COBOL definition of S9(9) COMP which I assumed you are using in a COBOL program. However if you have just 9(9) COMP then you can store from 0 through 4,294,967,295.

shankar_dh wrote:I tried with DIGITS(COUNT(ACCOUNT_ID)) It gave me 10 bytes of count. My doubt is what if the count from DB2 table crosses 10 bytes?
Will it expand automatically ? Because I am taking the reference of this count length in my next step. So I need to concrete length.


You need to spend time looking into DB2 manuals which explains about the aggregate function COUNT_BIG.

I am not sure as to why this is a DFSORT question as it basically deals with DB2 functions and how the data items are defined.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post