Re: SQL changes - what is error telling me? -- MIDRANGE-L

Yes it would only produce two rows, I missed your Group By clauses in there.

with firstRecord as (SELECT ROW_NUMBER() OVER(ORDER BY JODATE DESC,
JOTIME DESC, JOSEQN DESC PARTITION BY JOOBJ, JOCTRR) as rowno, JOSEQN FROM
JRNFLALL),
lastRecord as (SELECT ROW_NUMBER() OVER(ORDER BY JODATE ASC,
JOTIME ASC, JOSEQN ASC PARTITION BY JOOBJ, JOCTRR) as rowno, JOSEQN FROM
JRNFLALL)
SELECT * from JRNFLALL
where joseqn in (SELECT joseqn FROM firstRecord where rowno = 1 UNION
SELECT joseqn from lastRecord where rowno = 1)

You may have to adjust the order by (ascending or descending) to meet the
data requirements.

-Tom Stieger

On Wed, Dec 11, 2013 at 11:49 AM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

It appears that this code may only return two rows.

Is that accurate?

I am looking for thousands of rows - one pair for each RRN in each
journaled file.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom Stieger
Sent: Wednesday, December 11, 2013 1:30 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL changes - what is error telling me?

At first glance I see two things that may need to be fixed. You aren't
explicitly casting your JODATE field, but without knowing the outfile, it
may already be in a CHAR format. Second, you are still looking for JOSEQN
IN (SELECT (JODATE || CHAR(JOTIME) || CHAR(joseqn)) ...) These data types
won't match.

Here is a possible solution:

with firstRecord as (SELECT JOSEQN FROM JRNFLALL ORDER BY JODATE DESC,
JOTIME DESC, JOSEQN DESC FETCH FIRST 1 ROW ONLY),
lastRecord as (SELECT JOSEQN FROM JRNFLALL ORDER BY JODATE ASC, JOTIME ASC,
JOSEQN ASC FETCH FIRST 1 ROW ONLY)
SELECT * from JRNFLALL
where joseqn in(SELECT * FROM firstRecord UNION SELECT * from lastRecord)
;

It should remove the need to cast and concat all the data together.

-Tom Stieger

On Wed, Dec 11, 2013 at 11:07 AM, Charles Wilt <charles.wilt@xxxxxxxxx

wrote:

That's an internal IBM error, I'd open a PMR...

Without seeing your DSPJRN command, I can only guess...but I'd guess that
something you've output is incompatible with the process you're trying to
use.

By that I mean that the journal isn't a DB file and it doesn't play one
very well on TV (or an OUTFILE) either.

Sure you can do some quick and dirty stuff with the tools provided. But
like many system functions, OUTPUT(*OUTFILE) is probably not the best way
to process it in production. Instead, there APIs are provided that are
better suited for production code.

Instead of reinventing the wheel, have you look at 3rd party solutions?
http://www.data-beam.com/dataCapture/
http://www.kisco.com/ifa/
http://www.cosynsoftware.com/index.asp?CID=product&PID=CAT
http://www.enforcive.com/iseries-audit

Charles

On Wed, Dec 11, 2013 at 1:24 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx>
wrote:

The following SQL functions well:

Insert into jrnflb4af

select * from jrnflALL
where joseqn in(
select min(joseqn) from jrnflall
group by joobj, joctrr
union
select max(joseqn) from jrnflall
group by joobj,joctrr)
order by joobj,joctrr,joseqn

I made the following changes (in CAPS) to add DATE/TIME in front of

JOSEQN:

Insert into jrnflb4af

select * from jrnflALL
where JOCTRR = 8232817 AND
joseqn in(
select min (JODATE || CHAR(JOTIME) || CHAR(joseqn)) from jrnflall
group by joobj, joctrr
union
select max (JODATE || CHAR(JOTIME) || CHAR(joseqn)) from jrnflall
group by joobj,joctrr)
order by joobj,joctrr, CHAR(joseqn)

Now I get this ugly incomprehensible error: What is this complaining
about??

6697 entries converted from journal JRNLCMIS in PDBJRNLIB.
Member JRNFLB4AF file JRNFLB4AF in JSTONE cleared.
Function error X'1720' in machine instruction. Internal dump identifier
(ID) 0201590F.
Ownership of object DBOPTRC12 in QTEMP type *FILE changed.
File DBOPTRC12 created in library QTEMP.
Object DBOPTRC12 in QTEMP type *FILE deleted.
*** DBOP open FAILED. Exception from call to SLIC! ***.
Internal failure occurred in query processor.
SQL system error.
Ownership of object QP0Z984297 in QUSRSYS type *USRSPC changed.
File QAP0ZDMP in library QTEMP already exists.
User Trace data for job 984297/JSTONE/MN001CPNBA dumped to member
QP0Z984297 in file QAP0ZDMP in library QTEMP.
28 records copied from member QP0Z984297.
1 User Trace buffer(s) deleted.
RUNSQLSTM command failed.
SQL9010 received by ETJ021 at 11300. (C D I R)
? C
Function check. SQL9010 unmonitored by ETJ021 at statement 11300,
instruction X'0072'.

Thanks!

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

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

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.