MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2013

Re: SQL changes - what is error telling me?



fixed

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.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact