Re: SQL display_journal

If you are writing something to dump journals into a file for analysis you
might want to check this out from Seven Wolk:

*Steven Wolk is the CTO for PC Richard & Son, an over 100-year-old family
owned and operated chain of 66 appliance and electronics superstores
located throughout New York, New Jersey, Connecticut, and Pennsylvania.
Steve is looking forward to presenting a very unique session on his DMPJRN
and CVTJRNDTA commands at the COMMON 2013 Annual Meeting and Exposition in
Austin, Texas. If you enjoyed this article, please attend his session, as
there will be guaranteed fun and surprises*

I believe he still allows it to be distributed.


--
Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On Wed, Apr 5, 2023 at 8:57 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.