Re: Does environment variable QIBM_QBASEYEAR affect timestamp variables?

On the sliding date range - documentation on the environmental variable
says that if it is not present, the 1940 version is used. That suggests to
me that pre-7.5 still use 1940, although Michael's example suggests either
that his 7.4 is using 1970 or that using a timestamp doesn't care about
that sliding date - as I would expect - I mean, timestamps are supposed to
be used to represent dates outside that 100-year window. DSPJRN is
restricted to the sliding window interpretation because of its parameters,
but the API and the service should not be, IMHO.


Vern


On Wed, 5 Apr, 2023 at 9:27 AM, Rob Berendt <robertowenberendt@xxxxxxxxx>
wrote:


To: midrange systems technical discussion

Thank you.

I've opened case TS012653923 for IBM i 7.5. IBM just replied that they are
able to duplicate it and will pursue further.

It does surprise me that earlier releases have changed to the later sliding
date.

Apparently all releases have and the environment variable is simply there
on 7.5 in case you want to revert to the old sliding date.

On Wed, Apr 5, 2023 at 10:15 AM Michael Mayer <michael.mayer@xxxxxxxxxxxxx
<mailto:michael.mayer@xxxxxxxxxxxxx>>
wrote:

Ran this on a V7R4 OS ..

select *
from table (qsys2.display_journal ('QSYS',
'QAUDJRN',
starting_receiver_name => 'QAUDJR2117',
starting_receiver_library => '#MXJRN',
ending_receiver_name => 'QAUDJR2117',
ending_receiver_library => '#MXJRN',
starting_timestamp => '0001-01-01-00.00.00.000000',
ending_timestamp => '3070-12-31-23.59.59.999999<

http://3070-12-31-23.59.59.999999>'

)) ;

Received:
[cid:image001.png@01D9679E.629C6290]

Then ran this:
select *
from table (qsys2.display_journal ('QSYS',
'QAUDJRN',
starting_receiver_name => 'QAUDJR2117',
starting_receiver_library => '#MXJRN',
ending_receiver_name => 'QAUDJR2117',
ending_receiver_library => '#MXJRN',
starting_timestamp => '0001-01-01-00.00.00.000000',
ending_timestamp => '2039-12-31-23.59.59.999999<

http://2039-12-31-23.59.59.999999>'

)) ;

Hundreds of lines of results returned:

Then ran this
select *
from table (qsys2.display_journal ('QSYS',
'QAUDJRN',
starting_receiver_name => 'QAUDJR2117',
starting_receiver_library => '#MXJRN',
ending_receiver_name => 'QAUDJR2117',
ending_receiver_library => '#MXJRN',
starting_timestamp => '0001-01-01-00.00.00.000000',
ending_timestamp => '2040-12-31-23.59.59.999999<

http://2040-12-31-23.59.59.999999>'

)) ;

Also hundreds of lines of results returned.










Respectfully,
Michael Mayer
IBM i on Power System Admin
ERMCO-ECI
2225 Industrial Rd
Dyersburg, Tennessee 38024
Office and OnCall: 731.676.4318
Cell: 518.641.8906
Email: michael.mayer@xxxxxxxxxxxxx<mailto:michael.mayer@xxxxxxxxxxxxx
<mailto:michael.mayer@xxxxxxxxxxxxx<mailto:michael.mayer@xxxxxxxxxxxxx>>
https://www.ermco-eci.com<http://www.ermco-eci.com><

https://www.ermco-eci.com/><www.ermco-eci.com/>>

IBM i Personal Blog: https://ibmireference.blogspot.com<

http://ibmireference.blogspot.com><

https://ibmireference.blogspot.com/><https://ibmireference.blogspot.com/

"Success is not final. Failure is not fatal. It is the courage to

continue

that counts".


[cid:image002.gif@01D9679E.629C6290]





From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:

midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Rob

Berendt
Sent: Wednesday, April 5, 2023 7:07 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx

<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>

Subject: Does environment variable QIBM_QBASEYEAR affect timestamp
variables?

Environment: IBM i 7.5, TR 1, cume 22321, Hiper 22, db 3.

While looking at a different thread in which a gentleman tried to use all
inclusive timestamps to get beginning to end I noticed something odd.
He was using a sql service with parameters like
starting_timestamp => '0001-01-01-00.00.00.000000',
ending_timestamp => '9999-12-31-23.59.59.999999<

http://9999-12-31-23.59.59.999999>'

and it wasn't finding anything.
I suggested he just follow the doc for that function, leave those blank

and

it would work. However I did delve into this further.
The starting timestamp was fine.
It was the ending timestamp.
If I used '3069-12-31-23.59.59.999999<http://3069-12-31-23.59.59.999999>'

it worked fine.

If I used '3070-12-31-23.59.59.999999<http://3070-12-31-23.59.59.999999>'

I would get

SQL State: 42616, Vendor Code: -443, Message: [SQL0443] ENDING_TIMESTAMP

IS

EARLIER THAN STARTING TIMESTAMP

https://www.ibm.com/support/pages/node/6579221?mhsrc=ibmsearch_a&mhq=QIBM%26lowbar%3BQBASEYEAR

<

https://www.ibm.com/support/pages/node/6579221?mhsrc=ibmsearch_a&mhq=QIBM%26lowbar%3BQBASEYEAR

Will someone on an older OS which still supports this function try 2039
then try 2040?
select *
from table (qsys2.display_journal ('QSYS',
'QAUDJRN',
starting_receiver_name => 'Q633900051',
starting_receiver_library => 'QSYS',
ending_receiver_name => 'Q633900051',
ending_receiver_library => 'QSYS',
starting_timestamp => '0001-01-01-00.00.00.000000',
ending_timestamp => '3070-12-31-23.59.59.999999<

http://3070-12-31-23.59.59.999999>'

)) ;
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:

MIDRANGE-L@xxxxxxxxxxxxxxxxxx><mailto:

MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<<

https://lists.midrange.com/mailman/listinfo/midrange-l<>

https://lists.midrange.com/mailman/listinfo/midrange-l><

https://lists.midrange.com/mailman/listinfo/midrange-l>>

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx><mailto:

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>>

Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<<

https://archive.midrange.com/midrange-l<>

https://archive.midrange.com/midrange-l>.<

https://archive.midrange.com/midrange-l>.>

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:

support@xxxxxxxxxxxxxxxxxxxx><mailto:

support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx>> for

any subscription related questions.

The information in this email may be confidential. It is intended only

for

the person(s) named above. If you are not the intended recipient, please
notify the sender and do not review, distribute, or duplicate this email.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:

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<mailto:

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<mailto:

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