Re: [IBMiOSS] Field Length using node.js

"OpenSource" <opensource-bounces@xxxxxxxxxxxx> wrote on 07/14/2016
02:03:38 PM:

From: John Yeung <gallium.arsenide@xxxxxxxxx>
To: IBMi Open Source Roundtable <opensource@xxxxxxxxxxxx>
Date: 07/14/2016 02:03 PM
Subject: Re: [IBMiOSS] Field Length using node.js
Sent by: "OpenSource" <opensource-bounces@xxxxxxxxxxxx>

<snip>

I'm afraid I am not sure which "CLI" you mean... you did refer to a
CLI API, but I can't stop seeing "command line interface" which
doesn't seem to fit the context.

I mean the SQL Call Level Interface:
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/cli/rzadpkickoff.htm
This is the API that all the language bindings use (JTOpen JDBC, Python,
Ruby, PHP, ...) to communicate with the database.

The terms "precision" and "scale" as used above are definitely the
norm for SQL. I understand the confusion, and I used to get confused
by them myself.

What made me finally grok the terms, used this way, is thinking of
scientific notation and IEEE floating point. When scientists say a
measurement is "precise to 6 digits", they mean it has 6 significant
digits. It doesn't matter whether they are talking on an astronomical
scale or a subatomic one, it's still significant digits. (And see
there, I already used the word "scale".)

So if precision is related to significant digits, then what's left for
scale to be related to is exponent. Astronomers typically deal with
big exponents, particle physicists with small ones (well, negative
ones). It's not that particle physicists need fewer digits (in other
words, it's not that they need less precision). They are just working
at a different scale. The exponent in scientific notation or floating
point essentially tells you where your (fixed number of) significant
digits are situated. And that's what scale does in a database numeric
field.

I think I'm in agreement with you on what scale and precision means, yet
that's exactly contra to how CLI uses those terms. Scale to me means "how
big is this thing" whereas precision means "how accurate is this thing".
So for a numeric or decimal type, scale would make most sense to me to
refer to the number of digits before the decimal (of course this is also
contra to what I'd like in SQL, since when you declare a numeric or
decimal type, the first value is *total* number of digits, not just the
ones before the decimal) and the precision would refer to the number of
digits after the decimal place.

For a string type, precision doesn't make much sense and would always be
zero, but the scale would refer to how many characters (or bytes) it takes
to store the string.

Same with timestamp, precision would refer to the number of sub-second
digits are in the timestamp string (pre-7.2, this will always by 6, after
can be 0-12).


In CLI though, these terms are backwards from my expectations: precision
is "how big is this thing" and scale is "how accurate is this thing."
Thus, in CLI for a CHAR(10), precision is 10 and scale is 0; for a
DECIMAL(15,5) precision is 15 and scale is 5.

See the documentation of SQLDescribeCol:
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/cli/rzadpfndecol.htm
(precision = pcbColDef, scale = pibScale).

John Y.