× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. April 2018

Re: Records written to a table only visible at end of program execution, it seems.

I am experimental with a few options as suggested. At this point it seems
the RPG program may be the bottleneck now. Trying to improve it, but it
needs to use the UNIONed files and I think this makes the process resource
intensive. I am using a view on a view.

My latest test has been to merge the files into 1 file and then apply the
UDF on the merged file. This is showing the RPG program to be the
bottleneck.

I am going to try flattening the view on a view into 1 view sometime today
or tomorrow.

Darryl.


On Mon, Apr 23, 2018 at 1:39 PM, Tommy Holden <Tommy.Holden@xxxxxxxxx>
wrote:

You could just use 2 statements in place of the MERGE:

insert into <myOutputTable> a
where not exists ( select 1 from <myInputTable> b where
a.PartNumber = b.PartNumber)

update <myOutputTable> a
set a.PartNumber = 'myValue'
where exists ( select 1 from <myOutputTable> b where b.PartNumber
= 'myValue' )


Thanks,
Tommy Holden

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
a4g atl
Sent: Monday, April 23, 2018 1:32 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Records written to a table only visible at end of program
execution, it seems.

@Joep
This system is at V5R4 which does not have the MERGE option. We are going
to 7.3 shortly.

Darryl

On Fri, Apr 20, 2018 at 3:58 AM, Joep Beckeringh <
joep.beckeringh@xxxxxxxxxx
wrote:

Darryl,

Have you looked at SQL statement merge
(<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww
.ibm.com%2Fsupport%2F&data=02%7C01%7CTommy.Holden%40tmcat.com%7C21a850
55c4764c2be70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966aa39%7C0%7C0%
7C636601051709878504&sdata=Me%2BDIrf047xuSo3LtkRGBZ6DntVhhgSHCER%2F7D%
2BIt1s%3D&reserved=0
knowledgecenter/nl/ssw_ibm_i_73/db2/rbafzmerge.htm>)? Basically you
can execute either an update ('when matched') or an insert ('when not
matched').

Joep Beckeringh


Op 19-4-2018 om 20:10 schreef a4g atl:

I have a SQL RPGLE program reading from the input file and testing if
a
record with the same part number exists in the target file. The input
file has duplicates and I am trying to filter out the duplicates.

The output file is cleared with DELETE ALL up front.


The process is
1. read input file.
2. use Select count(*) counting if the part number exists in the
output file.
SELECT COUNT(*) into :COUNT from INPUTFILE WHERE PART_NO =
:PART 3. if it does not exist, I do an INSERT.
4. if it exists, I update the record with the latest values.
5 Read next input record.

I have the isolation level set to *none. The file is a new file and
is not journaled.

Problem:
The count process does not see the records already inserted so I am
getting duplicates written to the file. Its almost like the program
needs a COMMIT executed after each INSERT.

Why does the program not find the records it has written?

TIA

Darryl Freinkel.

--
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:
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists
.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=02%7C01%7CTommy.H
olden%40tmcat.com%7C21a85055c4764c2be70408d5a9489d6e%7C7bedfe4f5ee1435
29ded95e9c966aa39%7C0%7C0%7C636601051709878504&sdata=4x2QzcbgZW6x4w5GW
wYO9ErULeQTv0kInRRM%2F%2FS%2FIbA%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://na01.safelinks.protection.outlook.com/?url=
https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&
data=02%7C01%7CTommy.Holden%40tmcat.com%7C21a85055c4764c2be70408d5a948
9d6e%7C7bedfe4f5ee143529ded95e9c966aa39%7C0%7C0%
7C636601051709878504&sdata=dQ4mfV7GafzbweXIHCERngRzNz88Rs
ec3HF653lG%2BdY%3D&reserved=0.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Famzn.t
o%2F2dEadiD&data=02%7C01%7CTommy.Holden%40tmcat.com%7C21a85055c4764c2b
e70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966aa39%7C0%7C0%7C63660105
1709878504&sdata=yERWfPLgsRe5Ar3xrRtzSFMBkl0rAP%2BVmCxCe7xfdJ0%3D&rese
rved=0

--
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: https://na01.safelinks.protection.outlook.com/?url=
https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%
2Fmidrange-l&data=02%7C01%7CTommy.Holden%40tmcat.com%
7C21a85055c4764c2be70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966
aa39%7C0%7C0%7C636601051709878504&sdata=4x2QzcbgZW6x4w5GWwYO9ErULeQTv0
kInRRM%2F%2FS%2FIbA%3D&reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at https://na01.safelinks.
protection.outlook.com/?url=https%3A%2F%2Farchive.
midrange.com%2Fmidrange-l&data=02%7C01%7CTommy.Holden%40tmcat.com%
7C21a85055c4764c2be70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966
aa39%7C0%7C0%7C636601051709878504&sdata=dQ4mfV7GafzbweXIHCERngRzNz88Rs
ec3HF653lG%2BdY%3D&reserved=0.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://na01.safelinks.protection.outlook.com/?url=
http%3A%2F%2Famzn.to%2F2dEadiD&data=02%7C01%7CTommy.Holden%40tmcat.com%
7C21a85055c4764c2be70408d5a9489d6e%7C7bedfe4f5ee143529ded95e9c966
aa39%7C0%7C0%7C636601051709878504&sdata=yERWfPLgsRe5Ar3xrRtzSFMBkl0rAP
%2BVmCxCe7xfdJ0%3D&reserved=0
--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.