Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2:: Division of an Integer Column by Interger column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 132
Location: India

PostPosted: Thu Jan 16, 2014 11:35 am    Post subject: DB2:: Division of an Integer Column by Interger column
Reply with quote

Hi,
I am working on a DB2 query which looks like given below:

Code:

SELECT A_CODE                       >> Char(3)
     , B_CODE                       >> Char(3)
     , ARTICLE                      >> Char(20)
     , AMT / QTY                    >> AMT and QTY -- data type INTEGER
 FROM ARTICLE_TABLE
  WHERE END_DT >= CURRENT DATE
    AND STAT_CODE = 'A'
ORDER BY 1,2,3


Here, I am getting extract of this query in a file and File is getting generated with LRECL=31. I am not sure why Select on AMT divided by QTY is taking 5 bytes though both columns are of data type INTEGER and are greater than 1. Can anybody please explain the reason of it? Thank you.
Back to top
View user's profile Send private message

sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Thu Jan 16, 2014 11:59 am    Post subject:
Reply with quote

Have you checked your DCLGEN for this table for AMT or QTY column, how host variables are declared?

Please consider THIS also.

How much length are you expecting and little bit of data would be good to analyze further.
Back to top
View user's profile Send private message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 132
Location: India

PostPosted: Thu Jan 16, 2014 12:04 pm    Post subject:
Reply with quote

DCLGEN is having PIC clause of S9(9) COMP. I was expecting a length of 4 bytes.

E.g. AMT 12000
QTY 12

O/P value should be 1000

AMT 1000
QTY 6

O/P value should be 166

I am not interested in Fractional part of the value after Division.
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Thu Jan 16, 2014 12:16 pm    Post subject:
Reply with quote

Could you provide how are 1000 & 166 presented in a actual file?
Back to top
View user's profile Send private message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 132
Location: India

PostPosted: Thu Jan 16, 2014 12:27 pm    Post subject:
Reply with quote

As S9(9) Comp only.
Code:

A-CODE             B-CO             ARTICLE              AMT     
3/AN               3/AN             20/AN                4/BI                 
(1-3)              (4-6)            (7-26)               (27-30)               
1----------------- 2--------------- 3------------------- 4---------------     
********************************* TOP OF DATA **********************-CAPS OFF-*
007                001              00000312                         1000     
007                001              00000313                          166     
007                002              00000314                        30000     
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Thu Jan 16, 2014 12:33 pm    Post subject:
Reply with quote

I don't see any problem here....

you are expecting length of 4 and you are getting 4 (27-30 as mentioned above).

So What is the value of 31st position in your file?

Please post the original data in the file (not file-aid view with copy book).
Back to top
View user's profile Send private message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 132
Location: India

PostPosted: Thu Jan 16, 2014 12:39 pm    Post subject:
Reply with quote

File without copybook ( Browse mode):

Code:

----+----1----+----2----+----3----+----4-
********************************* Top of
00700100000312            .....         
00700100000313            .....         
00700200000314            .....                   


View mode:

Code:

Command ===>                                     
=COLS> ----+----1----+----2----+----3-           
****** ***************************** Top of Data
000001 00700100000312                           
000002 00700100000313                           
000003 00700200000314                           
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Thu Jan 16, 2014 12:48 pm    Post subject:
Reply with quote

Hi Gagan,

Here are the way to see the actual value in the file:
1. Go to 3.4 and open your file in either Browse or View mode
2. Once opened, Issue 'HEX ON' command
3. Below ....., You can see how exactly the amount value has been stored.

Could you post that value here? Please take from 27-31 (5).
Back to top
View user's profile Send private message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 132
Location: India

PostPosted: Thu Jan 16, 2014 12:57 pm    Post subject:
Reply with quote

Code:

----+----1----+----2----+----3----+----4----+----5
----+----F----+----F----+----F----+----F----+----F
----+----1----+----2----+----3----+----4----+----5
 -------------------------------------------------
00700100000312            ...Y.                   
FFFFFFFFFFFFFF444444444444000E0                   
0070010000031200000000000000380                   
 -------------------------------------------------
00700100000313            ...D.                   
FFFFFFFFFFFFFF444444444444000C0                   
0070010000031300000000000000040                   
 -------------------------------------------------
00700200000314            .....                   
FFFFFFFFFFFFFF44444444444400730                   
0070020000031400000000000000500                   
 -------------------------------------------------
******************************** Bottom of Data **
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Thu Jan 16, 2014 1:08 pm    Post subject:
Reply with quote

Have you checked your extract JCL for output file LRECL value ?
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Thu Jan 16, 2014 1:36 pm    Post subject:
Reply with quote

As per your data,

Code:

000E
0038                    is equal to 1000


but
Code:

000E0
00380                   is equal to 256000 


Same goes for 2nd and 3rd values.
You may refer here for conversion.

So I'm sure that the value in the 31st is not belongs to AMT fields. It is just a extra/junk value in the file. So if you specify LRECL=30 in your extract JCL, then it should be fine.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Jan 21, 2014 8:56 pm    Post subject:
Reply with quote

Any questions about what format/length/layout comes out of a particular unload can be solved by looking in the generated SYSPUNCH (if it is not set to dummy).

The 31st is probably not junk but a null-indicator.
Back to top
View user's profile Send private message
sureshpathi10

Active User


Joined: 03 May 2010
Posts: 154
Location: Kuala Lumpur

PostPosted: Wed Jan 22, 2014 9:05 am    Post subject:
Reply with quote

Hi GuyC,

I'm little bit lost here....
it would be great if you explain why a Null-ind created for '/' operation.
Will it get generated automatically for all arithmetic operations?
Back to top
View user's profile Send private message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 132
Location: India

PostPosted: Wed Jan 22, 2014 10:12 am    Post subject:
Reply with quote

Hi GuyC,
Thank you very much. you found it right.

Code:

  LOAD DATA LOG NO RESUME YES INDDN SYSREC00 INTO TABLE                     
      TBLNAME                                                               
   (                                                                         
   A_CODE                              POSITION(       1         )       
   CHAR(                      3)                ,                           
   B_CO                                POSITION(       4         )       
   CHAR(                      3)                ,                           
   ARTICLE                            POSITION(       7         )       
   CHAR(                     20)                ,                           
   " "                                POSITION(      27         )       
   INTEGER                                                                   
        NULLIF(      31)='?'                                                 
   )                                                                         
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jan 22, 2014 4:18 pm    Post subject:
Reply with quote

almost all output from calculations or functions return a nullable column.
I believe you can avoid this by adding the function COALESCE() in your query :
Code:
SELECT A_CODE, B_CODE, ARTICLE
       , COALESCE(AMT / QTY , 0) as result
FROM ...
If you do not want it to be binary, you could add the function
COALESCE(char(AMT/QTY),0)=> length:11, Remove leading zeroes, left aligned
or
COALESCE(digits(AMT/QTY),0) => length:10
Back to top
View user's profile Send private message
GaganGarg

Active User


Joined: 31 Mar 2010
Posts: 132
Location: India

PostPosted: Wed Jan 22, 2014 4:38 pm    Post subject:
Reply with quote

It worked. icon_smile.gif
Thank you very much for the help.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us