IBM Mainframe Forum

Hi All,

I am unloading the data from DB2 table using the DB2BATCH utility. Below is my SQL.


Below is the output


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 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.

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 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.