|
Am 02.11.2023 um 21:21 schrieb Martijn van Breden <m.vanbreden@xxxxxxxxxxxxxxxxxxxxxxxxxx>:--
Hi Charles
Thank you for your reply . I would say exactly the same. It shouldn't matter whether you execute a statement in a RPG program or via ACS although I ran into a problem before which turned out to be caused by a storage model difference between ACS and my application environment.
The fact of the matter right now is that is does make a difference for me and that the application is wrong... I really doubt that there is a copy difference, I checked it over and over again with a colleague and most of the variables are introduced via host variables which I also checked and double checked.
To me that leaves the option of differences in query settings. I looked to STRDBMON and got it to work and monitored both queries. The outcome was a bunch of mostly undescribed columns in a file which indeed showed differences, but even uncle Google wouldn't explain me what these columns were actually about.
I also did a PRTSQLINF for the program and didn't see shocking things there but a one on one comparison with settings in ACS can't be made.
So I'm really open to all of your bright ideas!
Kind regards, Met vriendelijke groet,
Martijn van Breden
lead software architect
________________________________
Van: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> namens Charles
Wilt <charles.wilt@xxxxxxxxx>
Verzonden: donderdag 2 november 2023 17:29
Aan: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Onderwerp: Re: Different result from full outer join in ACS
RunSqlScript and SQLRPGLE
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
What you think you're seeing, a difference running the same SQL
statement in ACS vs embedded in RPG, is not what's actually happening.
It's simple really, both ACS and RPG simply pass a statement to the DB.
The DB actually does the work.
Now the differences in the run-time defaults, may mean a given query
gets processed differently; ie. indexes used, the time it takes to
return. But the results will be exactly the same.
So what does this mean? It means that the query isn't actually the same.
From personal experience, there's likely a copy/paste induced difference.
I've even seen non-printable characters ignored by ACS cause an issue
with embedding the statement in RPG.
Now having said that, I'll say the above is true 99.99999% of the time.
The other 0.00001%, a bug in the OS causes the difference in run-time
environments to surface different results.
Note this isn't RPG vs ACS, it's just that the defaults settings
*FIRSTIO vs. *ALLIO, memory available to the QZDASOINIT job vs. interactive...ect.
If you configure the environments the same, you'd get the same
right/wrong answer from each tool.
If you run a DB monitor and capture each invocation, you might be able
to tell where the statement is different.
Or you've got something to hand to IBM and ask them what is going on.
HTH,
Charles
On Thu, Nov 2, 2023 at 8:07 AM Martijn van Breden <--
m.vanbreden@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi midrange experts 🙂
I have a inexplicable difference between the outcome of a query when
run in SQLRPGLE and ACS-RSS.
It's a query that determines differences between two tables in two
different libraries. The table definitions are identical. To get to
the tables in the right libraries I create two aliases in qtemp first.
I select a couple of columns left and right and do a full outer join
on three columns to get what's only in the left table, in both tables
and only in the right table. From that I determine the differences.
I developed this using ACS -RSS and experimented until it worked fine.
Then I built it into a function in an SQLRPGLE-program. It worked
quite nice except for one thing.
That is that it did not see differences on the column that I use for
the full outer join. When I use exactly the same query (execpt for
the
into-clause) in ACS it does see those differences.
After adding some more columns (for counting options left and right)
and a lot of debugging I found out that the full outer join in RPG is
not returning the rows that are in only one of the tables, where in
ACS, it does return them. I then dove into the SQL "Set option"
statement assuming that there was some kind of
null-value-behaviour-setting that was messing up my results. However,
I can't find anything that explains what I'm experiencing
At the bottom you see the query:
The DMOF table has MENAM as a header key (from another table) and
TASKB, MTASK as primary key. Differences between other columns (like
OTFMT and
TSKLV) are determined per MTASK row if they exist on the left and
right table. At the end I'm rounding up to see if there are
differences per MENAM group.
If Left has the values
MENAM
X
MTASK
A
B
And Right has
MENAM
X
MTASK
A
B
C
D
Then in ACS-RSS I see LftOpt = 2 and RgtOpt = 4 and MTASK_Dif = '1'
which is correct In SQLRPGLE I see LftOpt = 2 and RgtOpt = 2 and
MTASK_Dif = '0' which is wrong
Does anyone have an idea what is causing this different behaviour? Is
RPG influencing the execution of the query?
Just to clutter your mail boxes :-)
-- leftData from DMOF_Left alias and RightData from DMOF via
DMOF_Right in another library with leftData as (
select '1' as Found_Lft,
TASKB as TASKB_Lft,
MENAM as MENAM_Lft,
MTASK as MTASK_Lft,
MAWNR as MAWNR_Lft,
OTFMT as OTFMT_Lft,
OPTYP as OPTYP_Lft,
TSKLV as TSKLV_Lft,
OTTGT as OTTGT_Lft,
OPACT as OPACT_Lft,
MOKTX as MOKTX_Lft,
MSHTP as MSHTP_Lft
from Dmof_left
),
RightData as (
select '1' as Found_Rgt,
TASKB as TASKB_Rgt,
MENAM as MENAM_Rgt,
MTASK as MTASK_Rgt,
MAWNR as MAWNR_Rgt,
OTFMT as OTFMT_Rgt,
OPTYP as OPTYP_Rgt,
TSKLV as TSKLV_Rgt,
OTTGT as OTTGT_Rgt,
OPACT as OPACT_Rgt,
MOKTX as MOKTX_Rgt,
MSHTP as MSHTP_Rgt
from Dmof_Right
),
-- These are the join columns MENAM. TASKB and MTASK
AnzMods as (
select coalesce(MENAM_Lft, MENAM_Rgt) as MENAM_All,
coalesce(TASKB_Lft, TASKB_Rgt) as TASKB_All,
coalesce(MTASK_Lft, MTASK_Rgt) as MTASK_All,
-- for counting options
case
when Found_Lft is null then 0
else 1
end as LftCount,
case
when Found_Rgt is null then 0
else 1
end as RgtCount,
-- for getting a proper sequence (not used here)
case
when MAWNR_Lft is null then '99' || MAWNR_Rgt
when MAWNR_Rgt is null then MAWNR_Lft || '00'
else MAWNR_Lft || MAWNR_Rgt
end as MAWNR_Seq,
-- if MTASK is only present in one table I give it a '1' otherwise a '0'
case
when ifnull(Found_Lft, '0') != ifnull(Found_Rgt, '0')
then '1'
else '0'
end as MTASK_Mod,
-- if present on both sides detemine differences for other columns
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(MAWNR_Lft, '') != ifnull(MAWNR_Rgt, '')
then '1'
else '0'
end as MAWNR_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(OTFMT_Lft, '') != ifnull(OTFMT_Rgt, '')
then '1'
else '0'
end as OTFMT_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(OPTYP_Lft, '') != ifnull(OPTYP_Rgt, '')
then '1'
else '0'
end as OPTYP_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(TSKLV_Lft, '') != ifnull(TSKLV_Rgt, '')
then '1'
else '0'
end as TSKLV_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(OTTGT_Lft, '') != ifnull(OTTGT_Rgt, '')
then '1'
else '0'
end as OTTGT_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(OPACT_Lft, '') != ifnull(OPACT_Rgt, '')
then '1'
else '0'
end as OPACT_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(MOKTX_Lft, '') != ifnull(MOKTX_Rgt, '')
then '1'
else '0'
end as MOKTX_Mod,
case
when ifnull(Found_Lft, '0') = '1'
and ifnull(Found_Rgt, '0') = '1'
and ifnull(MSHTP_Lft, '') != ifnull(MSHTP_Rgt, '')
then '1'
else '0'
end as MSHTP_Mod
from LeftData LD
-- This appears to be the source of the differences, in RPG the
left-only and right-only rows don't show up
full outer join RightData RD
on MENAM_Lft = MENAM_Rgt
and TASKB_Lft = TASKB_Rgt
and MTASK_Lft = MTASK_Rgt
)
-- wrap up on group level -> if at least one difference is found in a
line of the group, the group is different
select
Sum(LftCount) as LftOpt,
Sum(RgtCount) as RgtOpt,
max(MTASK_Mod) as MTASK_Dif,
max(MAWNR_Mod) as MAWNR_Dif,
max(OTFMT_Mod) as OTFMT_Dif,
max(OPTYP_Mod) as OPTYP_Dif,
max(TSKLV_Mod) as TSKLV_Dif,
max(OTTGT_Mod) as OTTGT_Dif,
max(OPACT_Mod) as OPACT_Dif,
max(MOKTX_Mod) as MOKTX_Dif,
max(MSHTP_Mod) as MSHTP_Dif,
'0' as found, '0' as Error
from AnzMods where Menam_All = :MENAM_INM
group by menam_all;
Thanks in advance,
Kind regards, Met vriendelijke groet,
Martijn van Breden
lead software architect
E-mail disclaimer<https://www.pantheon-automatisering.nl/disclaimer/>
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post
a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
