Db2 z/OS New idea for DECFLOAT in COBOL

Roy Boxwell

Db2 z/OS New idea for DECFLOAT in COBOL
Hi all!



Well actually I am pretty sure it is old but I have just been bitten by
it...






Thank you for submitting your idea in the IBM Analytics Aha Ideas Portal:
DB24ZOS-I-974 Support DECFLOAT in COBOL


DECFLOAT was introduced in DB2 9 and has just recently been enhanced to be
usable in indexes. However the datatype is not supported in COBOL and
FORTRAN and only "tolerated" in REXX (as a DOUBLE and then without NAN, SNAN
or INFINITY). As a mainframe COBOL programmer I do not want to have to write
C, PL/1 or Assembler to SELECT decfloat columns. Please add DECFLOAT as a
new datatype for at least COBOL. Personally I do not use FORTRAN and REXX
can wait as far as I am concerned but I need COBOL as soon as possible!





I would appreciate as many votes as physically possible!!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur
Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



Attachments

  • smime.p7s (5.1k)

Michael Hannan

RE: Db2 z/OS New idea for DECFLOAT in COBOL
(in response to Roy Boxwell)

Roy,

DECFLOAT is fancy thing allowing some quite small or large numbers to be exact in Decimal, if we had enough significant digits. However I don't recommend it for design of tables. The datatype is very underprivileged, in that so many restrictions on use of DB2 facilities exclude DECFLOAT.  I know little about processing it in COBOL, not my area, but it could be cast to something else supported first by your SQL query.

DECFLOAT looks mostly like a trouble maker to me. For scientific numeric quantities, we normally should work out a precision we require and use binary FLOAT(8), or FLOAT(4), or FLOAT(16). Exactness is not a requirement for measurement numbers, rounding can be tolerated.

Things that do require exact numbers can use Packed Decimal up to 31 digits, or BIGINT, etc. It is hard for me to think of an application that really needs to store DECFLOAT, but DB2 wants to use for comparison of mixed datatypes due to exactness of the comparison.

Others may think of a need for storing data as DECFLOAT. I understand well that encryption and decryption techniques may well require exact calculations, but still maybe not a reason to store numbers in DECFLOAT.

Would like to hear it. If DECFLOAT had no restrictions, I would be more than happy with it.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jun 19, 2019 - 02:54 PM (Europe/Berlin)

Roy Boxwell

AW: Db2 z/OS New idea for DECFLOAT in COBOL
(in response to Michael Hannan)
Indeed! DECFLOAT is really not that well supported… a little bit like TIMESTAMP with TIMEZONE…
I would never use it but my customers…..as it *is* a preferred beast on LUW
CAST is no help as you, obviously, lose all the data that is held in the darn thing. I will probably stick with CAST ing it to be CHAR(42) for now so that they are „processed but ignored“ – Who knows, perhaps IBM labs will actually prove how agile they are and add it to COBOL which, nowdays, is also agile of course!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

Von: Michael Hannan [mailto:[login to unmask email]
Gesendet: Mittwoch, 19. Juni 2019 14:51
An: [login to unmask email]
Betreff: [DB2-L] - RE: Db2 z/OS New idea for DECFLOAT in COBOL


Roy,

DECFLOAT is fancy thing allowing some quite small or large numbers to be exact in Decimal, if we had enough significant digits. However I don't recommend it for design of tables. The datatype is very underprivileged, in that so many restrictions on use of DB2 facilities exclude DECFLOAT. I know little about processing it in COBOL, not my area, but it could be cast to something else supported first by your SQL query.

DECFLOAT looks mostly like a trouble maker to me. For scientific numeric quantities, we normally should work out a precision we require and use binary FLOAT(8), or FLOAT(4), or FLOAT(16). Exactness is not a requirement for measurement numbers, rounding can be tolerated.

Things that do require exact numbers can use Packed Decimal up to 31 digits, or BIGINT, etc. It is hard for me to think of an application that really needs to store DECFLOAT, but DB2 wants to use for comparison of mixed datatypes due to exactness of the comparison.

Others may think of a need for storing data as DECFLOAT. Would like to hear it. If DECFLOAT had no restrictions, I would be more than happy with it.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

Michael Hannan

RE: AW: Db2 z/OS New idea for DECFLOAT in COBOL
(in response to Roy Boxwell)

Roy,

No, it is not obvious to me that cast necessarily loses all the data in the darn thing. Yes loses precision and rounding perhaps, I see double Float is limited to 8 bytes 64 bits, where DECFLOAT can be 16 bytes. However what is all that extra precision for (max precision of 31 digits)? Nothing can be measured that accurately. Seems like useless precision. If the number is just a set of digits not measuring anything, then DECFLOAT is an unsuitable way to store it.

Supposing I could recite the first 31 digits of Phi, being 3.14159265358979 32384626 4338327950288 is that useful for anything? Whoops just over 31 digits. Only to show off if I in fact remembered it (I knew the first bit but cheated for the last bit). Nah, it is no earthly use.

Just because a feature exists in DB2, does not mean we should use it.  It is about sensible use, surely? I can see the problem for third party products to support it much.

I suppose you might want a Catalogue query to show the Frequent Value Stats in readable display form. I wrote SQL code to translate internal INTEGER,  SMALLINT, and even FLOAT values stored in a LOW2KEY or Freq Value COLVALUE to displayable format, but must admit I did not see a need to support DECFLOAT. Could just dump it as hex string.

I would strongly recommend not using DECFLOAT for any column having predicates, that might need to be indexed.

Just because we have Scrollable Cursors, does not mean its a good idea to use them. Just because we have index compression possible, does not mean we should use it.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Roy Boxwell

AW: Db2 z/OS New idea for DECFLOAT in COBOL
(in response to Michael Hannan)
I do agree but the customers and users do not!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

> On 19 Jun 2019, at 16:16, Michael Hannan <[login to unmask email]> wrote:
>
> Roy,
>
> No, it is not obvious to me that cast necessarily loses all the data in the darn thing. Yes loses precision and rounding perhaps, I see double Float is limited to 8 bytes 64 bits, where DECFLOAT can be 16 bytes. However what is all that extra precision for (max precision of 31 digits)? Nothing can be measured that accurately. Seems like useless precision. If the number is just a set of digits not measuring anything, then DECFLOAT is an unsuitable way to store it.
>
> Supposing I could recite the first 31 digits of Phi, being 3.14159265358979 32384626 4338327950288 is that useful for anything? Whoops just over 31 digits. Only to show off if I in fact remembered it (I knew the first bit but cheated for the last bit). Nah, it is no earthly use.
>
> Just because a feature exists in DB2, does not mean we should use it. It is about sensible use, surely? I can see the problem for third party products to support it much.
>
> I suppose you might want a Catalogue query to show the Frequent Value Stats in readable display form. I wrote SQL code to translate internal INTEGER, SMALLINT, and even FLOAT values stored in a LOW2KEY or Freq Value COLVALUE to displayable format, but must admit I did not see a need to support DECFLOAT. Could just dump it as hex string.
>
> I would strongly recommend not using DECFLOAT for any column having predicates, that might need to be indexed.
>
> Just because we have Scrollable Cursors, does not mean its a good idea to use them. Just because we have index compression possible, does not mean we should use it.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
Attachments

  • smime.p7s (3.9k)

Michael Hannan

RE: AW: Db2 z/OS New idea for DECFLOAT in COBOL
(in response to Roy Boxwell)

I can agree that casting to CHAR(42) is as good an option as any, for displaying exact value. You certainly won't want to be doing arithmetic with it hopefully, nor column comparisons.

Shorter DECFLOAT (8 bytes) is good for storing values like 0.000021 without any rounding errors, or 9.000063, or 0.000001, however DEC(15, x) or even DEC(31,x) is usually a better choice, and should cover 99.99% of needs. We have to assume that 3rd party products displaying an average CPU cost of 0.000001, already have an extreme loss of precision, with only 1 significant digit. DECFLOAT could range from 0.0000000000014 to 14E15. Why? I don't know what else DECFLOAT is really good for.

If I measure the cost of very cheap SQL operations, I have to execute them millions or large numbers  of times, to avoid huge loss of precision.

One would never need a very long DECFLOAT (34 digits), I think. Useless precision. However IBM uses it. To compare an Integer, Smallint, Bigint, Packed Decimal to a char string, the Char string is first converted to DECFLOAT(34) as explained in the SQL manual under Implicit cast from String data. This allows for an exact equals match to be detected in some cases (maybe won't work for all Float Values compared to a string). This causes no end of bad access path performance problems. In old days, such a comparison was banned. That was better. Normally people will not code an equals predicate, but a range join predicate comparing  a numeric column to a CHAR column will not be able to use Merge Scan Join and I saw the almost cartesian product like performance. What a disaster that was.

Never allow implicit conversions like that in an application! Do a sensible conversion materialized to a work file if using such a column for a join predicate. One needs good join equals predicates matched, not just range predicate Theta joins.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd