Re: Comment on the output of the QSYS2/SYSCOLUMNS view -- MIDRANGE-L

Can somebody toss out a reason that showing the amount of storage required
is significant? If we're trying to determine the true size of a file, we
know how to adjust the DSPFFD- and API-provided column sizes.

My issue is that these values aren't compatible with other IBM-supplied
services, which brings up another question: where does this
binary-to-date/time/timestamp conversion take place? I'd think it should
take place below the OS layer (I've forgotten about vertical and horizontal
microcode). But the fact this view presents the actual storage value
suggests that's not the case.

On Sat, Sep 16, 2023 at 3:52 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

FFD returns the space necessary for displaying the value.
SysColumns returns the space needed for storing the value.

BTW a date is always stored as running number starting with January, 1
4713 BC (scaliger no).
Date Formats are only used for making the scaliger no readable.
In DDS a date format could be added, just for making the scaliger no
readable with old tools such as UPDDTA or WRKF

You may see the scaliger no if you look at the hex value:
Select Hex(YourDate)
From yourDDSOrSQLFile;

A time is stored as 3 Byte binary value. First Byte includes the hours,
second byte the minutes and third byte the seconds.
A timestamp is a composition of the Scaliger No and the binary time value
and a few bytes for the micro seconds.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i

IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so
they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Vern Hamberg via MIDRANGE-L
Sent: Saturday, 16 September 2023 11:57
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
Subject: Re: Comment on the output of the QSYS2/SYSCOLUMNS view

I went into documentation again about date-time stuff. The late Bruce
Vining really knew what this was about. He once told me his wife didn't let
him go to social events, he'd talk only about calendars.

Anyhow, here's a link with some info -
https://www.ibm.com/docs/en/i/7.5?topic=values-date. Internally, a date
is a Scaliger number, the number of days since noon on January 1, 4713 BCE.
This is an integer. There's more info here -
https://www.tondering.dk/claus/cal/julperiod.php. The first link mentions
the length in the SQLDA as the length of character representation - that
depends on the format, it says. That might be closer to what DSPFFD shows.

I created a PF using DDS, 2 date fields, one DATFMT(*YMD), the other
DATFMT(*JUL), and a time field, TIMHMS TIMFMT(*HMS) - DSPFFD reports
lengths as 8 , 6, and 8 resp., with mention of format. SYSCOLUMNS shows
lengths of 4, 4, and 3, and nothing said about format. SYSCOLUMNS is a view
over one or other of the QADB* database cross-reference files, maybe the
view leaves out the format.

Time is represented as 3 bytes, first if hours, then minutes, then seconds.

I ran the following SELECT to see internal form, thinking the hex value
might show me -
select datymd, hex(datymd), datjul, hex(datjul), timhms,
hex(timhms) from vhamberg/testdattim;

The result is here -
DATYMD HEXYMD DATJUL HEXJUL TIMHMS HEXHMS
2023-09-16 00258A2C 2023-09-16 00258A2C 05.51.17 055117

That integer for the hex values for dates is 2,460,204 - that is the
Scaliger number for today. That tondering site above shows it as
2,460,203.91, the decimal part is how much of a day has passed since noon
yesterday, so the value in the table appears to be rounded up. Or otherwise
adjusted.

Now you can invite me to social events - or not! Bruce knew SO much more
than I do!

Regards
Vern

On 9/15/2023 6:49 PM, x y wrote:

On V7R5, the output shows a date data type field has a length of 4
(which is the actual size of the binary field on the persistent
storage device) and a timestamp data type column has a length of 10
(also the actual binary size). I haven't checked a time data type yet.

AFAIK this is not consistent with DSPFD/DSPFFD and API output. Is
this variation by design or is it a miss?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.