Re: DISPLAY_JOURNAL, INTERPRET a DATE column

I think IBM did this weird low level change from 4 to 10 on DSPPFM, etc
because they really wanted to get date fields out and the older versions of
RPG couldn't handle the 4 byte date but could handle it as a character.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Peter Dow
Sent: Monday, May 9, 2022 4:24 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: DISPLAY_JOURNAL, INTERPRET a DATE column

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


I remember when data fields first came out, and a friend and I were
trying to figure out how much space data fields were actually taking
up. As you said, it's 4 bytes, but it's converted to a formatted 10
bytes at a pretty low level. For example, DSPPFM shows it as 10, just
like your DSPFFD shows. I would expect an RPG program reading the file
as a program-defined file would also have it as 10.

There might be some API that shows it as 4 bytes, or returns a date as 4
bytes, but I don't know of one off hand.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx

/
On 5/9/2022 12:43 PM, Charles Wilt wrote:

CORE_VALUE FOR COLUMN PKCORV DECIMAL(9,2) NOT NULL DEFAULT 0 ,
SHIPPER_NUMBER FOR COLUMN PKSHP# CHAR(9) NOT NULL DEFAULT ' ' ,
receive_date for column rcvdate date,
counter_person_name for column ctrpsnname varchar(20)
allocate(20) not null default ' ')

DSPFFD shows
Data Field Buffer Buffer Field Column
Field Type Length Length Position Usage Heading
PKCORV PACKED 9 2 5 297 Both Core Value
PKSHP# CHAR 9 9 302 Both Packing
Slip
RCVDATE DATE 10 10 311 Both Receive
Date
CTRPSNNAME CHAR 20 22 321 Both Counter
Person Name

select column_name, ordinal_position, data_type, length, numeric_scale,
storage
from qsys2.syscolumns col

CORE_VALUE 35 DECIMAL 9 2 5
SHIPPER_NUMBER 36 CHAR 9 9
RECEIVE_DATE 37 DATE 4 4
COUNTER_PERSON_NAME 38 VARCHAR 20 22

I'm well aware that Db2 stores a date internally as 4-bytes but surfaces

it

outside the DB as 10-bytes.

I expected the ENTRY_DATA blob from DISPLAY_JOURNAL() to be the internal
4-byte format which could then be INTERPRET( as DATE). As pointed out in
the post by Darren, INTERPRET() is documented as expecting 4 bytes for a
date.
https://www.ibm.com/docs/en/i/7.4?topic=functions-interpret

I suspect I'll need to ask IBM ... but I thought I'd throw this out here

in

case somebody else has already asked. And to maybe help the next guy.

Charles


On Mon, May 9, 2022 at 1:19 PM Peter Dow<petercdow@xxxxxxxxx> wrote:

Hi Charles,

What are the field definitions for the file whose journal entries you
are looking at? At least the RECEIVE_DATE definition plus the fields
before and after it. What does DSPFFD show for their buffer positions?

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx /

On 5/9/2022 9:53 AM, Charles Wilt wrote:

on a v7.4 box that's reasonably current on PTFs...

Using the QSYS2.DISPLAY_JOURNAL() UDTF to look at the journal entries

for a

specific table...

I expected this to work..
, interpret(substr(entry_data, 297, 5)as DECIMAL(9,2)) as CORE_VALUE
, interpret(substr(entry_data, 302, 9)as CHAR(9)) as SHIPPER_NUMBER
, interpret(substr(entry_data, 311, 4)as DATE) as RECEIVE_DATE
, interpret(substr(entry_data, 315, 22)as VARCHAR(20)) as
COUNTER_PERSON_NAME

However, I get a cast error... looking at the entry data from pos 311

on,

, substr(entry_data, 311) as hmm
I see
F0F0F0F160F0F160F0F100014040404040404040404040404040404040404040
So it seems the date data type is surfaced as a CHAR(10) in ENTRY_DATA

as

this works
, interpret(substr(entry_data, 297, 5)as DECIMAL(9,2)) as CORE_VALUE
, interpret(substr(entry_data, 302, 9)as CHAR(9)) as SHIPPER_NUMBER
, interpret(substr(entry_data, 311, 10)as char(10)) as RECEIVE_DATE
, interpret(substr(entry_data, 321, 22)as VARCHAR(20)) as
COUNTER_PERSON_NAME

Am I missing something?

Thanks!
Charles

--
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
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:https://amazon.midrange.com

--
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.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
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.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com