× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi, Vern,
It has always been documented that Db2i uses 8 bytes internally for a Timestamp, and 4 bytes for a date.  And, the "database" has always "materialized" those date/time fields as some number of character positions, for consumption by HLLs, etc.
IIRC, the "binary" representation for a date in that storage is actually just the rightmost 4 bits of each digit of the zoned decimal representation... (sort of like packed decimal without the sign.)

Just saying ...
Mark


On Monday, March 25, 2019, 12:02:14 PM EDT, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx> wrote:

To add to confusion or clarity - not sure - the SQL catalog SYSCOLUMNS
has the actual storage length -

select * from syscolumns where table_name = 'your-table-with-date-column';

You'll see something different.

I though I've seen this information gathered together somewhere - maybe
DBU - maybe one of the IBM i services.

HTH
Vern

On 3/25/2019 10:12 AM, Mark Waterbury wrote:
  Paul,
Date fields in Db2i have always occupied 4 bytes internally. When the date field is materialized by the database, it is converted from its internal binary form to and appears as 10 decimal character (zoned decimal) positions.
This is true whether a database file is defined by DDS using e.g. CRTPF or a table is defined via SQL DDL.
Hope that helps,
Mark S. Waterbury

      On Monday, March 25, 2019, 11:08:44 AM EDT, Therrien, Paul via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:
 
  This may not be too important, but seems to be an anachronism.

I have a table that is built with SQL DDL and one of the fields is a date field defined as:
PIPRJSDT      DATE      NOT NULL DEFAULT CURRENT_DATE;

When I Interrogate the file with DSPFFD I see the date field as taking 10 positions:

                        Data        Field      Buffer  Buffer          Field      Column
  Field          Type      Length  Length  Position        Usage    Heading
  PIPRJSDT  DATE          10      10            63                  Both      Project
                                                                                                                  Ship
                                                                                                                  Date
    Field text  . . . . . . . . . . . . . . . :  Projected ship date
    Date Format . . . . . . . . . . . . . . . :  *ISO
    Default value . . . . . . . . . . . . . . :
        CURRENT_DATE
    Coded Character Set Identifier  . . . . . :    37

When I interrogate the field using the SYSCOLUMNS I see this:
Column_Name Data Type    Length  Storage
PIPRJSDT        DATE                  4                4

I suppose this is expected, but I wasn't expecting this.

I found this while trying to generate a file layout using SYSCOLUMNS to excel and was trying to create a file layout listing column starting positions.


Paul

+++++ This email and related attachments may contain confidential information intended exclusively for the addressee. Unauthorized use, disclosure or distribution of this material is prohibited. If you received this message in error, please advise the sender and delete all copies of it. Content is provided by the individual sender and does not necessarily reflect the views of the Company. Though sender believes this transmission to be virus-free, it is the recipient's responsibility to ensure that it is.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.