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
 

 

Data Type and Size

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

New User


Joined: 27 Apr 2009
Posts: 22
Location: mumbai

PostPosted: Fri Aug 21, 2015 2:34 pm    Post subject: Data Type and Size
Reply with quote

Hi,

I have a requirement to change the column(its Revenue column) to accommodate max digits DB2 table. So we decided to change the column size to decimal 18,2 if the column is already defined as Decimal.But few column are defined as FLoat 8.Is float 8 is greater than Decimal 18,2. i mean how many digits can Float 8 can hold.The same way we have columns defined as Integer 4 ,is Integer 4 can hold more digits than decimal 18,2.

I want to know how many digits can Float 8 ,Integer 4 can hold.I google this but not able to decide.Please help


Thanks,
Chezhian
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Aug 21, 2015 3:49 pm    Post subject: Reply to: Data Type and Size
Reply with quote

I'm certain that these field-types are described accurately in a DB2 manual or three.

I'd like to see the reasoning behind blindly having sixteen digits in front of the decimal place. Do you know how big that is? 9,999,999,999,999,999.

Do you know what impact that is going to have on performance?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Aug 21, 2015 8:15 pm    Post subject:
Reply with quote

Hello,

Bill has valid questions, please answer.

Quote:
I google this but not able to decide.Please help


Look at this, I found over Google.

Code:
DB2 can use both DECIMAL and FLOAT data types to store non-integer numeric data. But the two are not equivalent. In general, use DECIMAL instead of FLOAT whenever you can. The main problem with floating point numbers is that they are not precise. DECIMAL values are precise. In other words, a FLOAT value will be an approximate value whereas a DECIMAL value will be an exact value. At times, if 100 percent precision is not required, you might want to use floating point numbers to save on storage. DB2 provides a parameter to size the floating point column; n where the data type specification is FLOAT(n). If n is between 1 and 21, this is a single precision floating point number and the column will require 4 bytes of storage; if n is between 22 and 53, this is a double precision floating point number and it will require 8 bytes to store it. A single precision floating-point number is a short (32 bits) floating-point number. A double precision floating-point number is a long (64 bits) floating-point number. For DECIMAL columns, the byte count is calculated as INTEGER(p/2)+1; where p is the precision of the DECIMAL column. So, a DECIMAL(10,2) column will require (10/2)+1 bytes = 6 bytes. An approximation of the same number could be stored in a FLOAT(21) column that would require only 4 bytes of storage. For very large or very small numbers, though, you will have to use FLOAT columns. This is so because there is a limit of 31 on decimal precision. The following outlines the largest and smallest values that can be supported using DECIMAL and FLOAT data types:

•  Smallest FLOAT value is about -7.2**75
•  Largest FLOAT value is about 7.2**75
•  Smallest positive FLOAT value is about 5.4**-79
•  Largest negative FLOAT value is about -5.4**-79
•  Smallest DECIMAL value is 1 – 10**31
•  Largest DECIMAL value is 10**31 - 1


Note that the values for floating point numbers are approximations. The ** is used to indicate “raised to the power of”.


If you are moving the between platforms, there is an additional concern when using FLOAT. Mainframes use an IBM standard whereas other platforms use different standards. Since floating point numbers are imprecise to begin with this may not be a problem. However, if you want to make sure that a particular column will be exactly the same value regardless of platform, then floating point is not the way to go. There is a third, newer option called DECFLOAT. Introduced in DB2 9 for z/OS, DECFLOAT is a combination of the two data types discussed here, or a decimal floating-point data type. Specified as DECXFLOAT(n), where the value of n can be either 16 or or 34, representing the number of significant digits that can be stored. If the n is not specified, then the DECFLOAT column can represent 34 significant digits.A decimal floating-point value is an IEEE 754r number with a decimal point. The maximum precision is 34 digits and the range of a DECFLOAT number is as follows:

A DECFLOAT(16) value can range from a low of:
-9.999999999999999×10**384

to a high of:
9.999999999999999×10**384And a DECFLOAT(34) value can range from a low of:-9.999999999999999999999999999999999 ×10**6144

to a high of:
9.999999999999999999999999999999999 ×10**6144

In addition, the DECFLOAT data type can be used to represent several special values that represent "non-number numbers," as follows:

•Infinity - a value that represents a number whose magnitude is infinitely large.
•Quiet NaN - a value that represents undefined results which does not cause an invalid number condition. NaN is not a number.
•Signaling NaN - a value that represents undefined results which will cause an invalid number condition if used in any numerical operation.



So decimal-floating point values can be more flexible and precise with the ability to range lower and higher than floating point values (or decimal values). However, before using DECFLOAT be careful and plan ahead. If you use COBOL programs to operate on your DB2 for z/OS data because there is no way to specify the SQL DECFLOAT data type in COBOL.
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 Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm


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