Re: SQL display_journal

Temporary workaround is to follow Birgitta's earlier suggestion of using an
earlier ending timestamp. I can tell you that the year 3069 is good, 3070
is not. The exact timestamp of errors is somewhere in the beginning of
3070 but 3069 should be 'good enough'.
I've opened up a case on this. Basically it's the sliding date of 6 digit
dates that's biting you in the butt.

On Wed, Apr 5, 2023 at 9:58 AM <smith5646midrange@xxxxxxxxx> wrote:

Typically there are about a dozen or more receivers per day for several of
our libraries.

I am writing a program that dumps the receivers to an outfile that
contains the journal detail as well as the individual fields (not just
"entry data"). There is a screen where the user (IT folks) can enter the
start and end receivers and then specify a timestamp range. I was just
providing a default timestamp so they didn't have to key one if they didn't
know / care about the approximate time that they were looking for. Then
the SQL is the same and no need for multiple if statements (or select) to
see if they entered a starting or ending timestamps or both. Fortunately
if it knows a time range, it speeds it up considerably because it skips the
receivers that are outside of the range.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob
Berendt
Sent: Wednesday, April 5, 2023 7:33 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL display_journal

If you want everything starting at the beginning to the end isn't it
rather pointless to specify a starting and ending timestamp especially when
you are selecting a single receiver?
The documentation says
If no starting timestamp is specified, *FIRST is used.
If no ending timestamp is specified, *LAST is used.
Maybe specifying dates like you are freaks it out since those dates start
in a different receiver and end in a different receiver than the one you
specify?

BTW, any attempt to try
starting_timestamp => '*FIRST',
ending_timestamp => '*LAST'
will result in:
SQL State: 22023
Vendor Code: -809
Message:[SQL0802] Data conversion or data mapping error.

On Tue, Apr 4, 2023 at 11:35 AM <smith5646midrange@xxxxxxxxx> wrote:

I am trying to pull data from a journal receiver using SQL using that
starting_timestamp and ending_timestamp parms. I was not getting any
results so I tried switching my dates to be from
0001-01-01-00.00.00.000000 to 9999-12-31-23.59.59.999999 to include

everything in the receiver.

When I use the below statement with the starting and ending timestamp,
I get
0 rows returned.

select *
from table (qsys2.display_journal ('##JOURNALS',
'IPLIBD',
object_objtype => '*FILE',
starting_receiver_name =>
'IPLIBD3277',
starting_receiver_library =>
'##JOURNALS',
ending_receiver_name =>
'IPLIBD3277',

ending_receiver_library =>
'##JOURNALS',
starting_timestamp =>
'0001-01-01-00.00.00.000000',
ending_timestamp =>
'9999-12-31-23.59.59.999999'
)
) ;



However, when I use the below statement WITHOUT the starting and
ending timestamp, I get lots of rows returned.



select *
from table (qsys2.display_journal ('##JOURNALS',
'IPLIBD',
object_objtype => '*FILE',
starting_receiver_name =>
'IPLIBD3277',
starting_receiver_library =>
'##JOURNALS',
ending_receiver_name =>
'IPLIBD3277',

ending_receiver_library =>
'##JOURNALS' )
) ;



What am I overlooking?

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


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