× 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.



Hi Birgitta,

That's interesting stuff to read. We normally don't go in this deep, but these type of changes may affect our results anyway.


Kind regards, Met vriendelijke groet,



Martijn van Breden

lead software architect







________________________________
Van: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> namens Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
Verzonden: woensdag 22 november 2023 09:40
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.



You might be interested in the following article:
Implementation changes for shared common table expressions
https://www.ibm.com/support/pages/implementation-changes-shared-common-table-expressions

Even though I don't think it was your problem, but I assume it should be known.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Martijn van Breden
Sent: Wednesday, 22 November 2023 08:47
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Different result from full outer join in ACS RunSqlScript and SQLRPGLE

I combined data by using several CTE's but no unions. I wouldn't expect that to be a problem, would you?

The query is still somewhere at the bottom of this thread.


Kind regards, Met vriendelijke groet,



Martijn van Breden

lead software architect





________________________________
Van: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> namens Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
Verzonden: woensdag 22 november 2023 08: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.



... could it be you used UNIONs and CTEs in your Query?

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i Database and Software Architect IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown) "If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson) "Learning is experience … everything else is only information!" (Albert Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Martijn van Breden
Sent: Wednesday, 22 November 2023 07:49
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Different result from full outer join in ACS RunSqlScript and SQLRPGLE

Hi all,

Just an update on this issue to keep you informed.

As some of you already mentioned it's highly unlikely that the query engine returns different results when exactly the same query is invoked from different environments. Like in my case, from within SQLRPGLE or from ACS-RSS.

Highly unlikely, but not impossible. I ran tests for IBM support and uploaded a bunch of traces, monitors and so on. The query implementation on the background was the same, but the results weren't. We were adviced to install a PTF (whilst running on 7.4 TR7) which solved the problem.

So what I've learned from this. I've you're convinced that you see what you see, but still can't believe that you see what you see, fly in an extra pair of eyes...


Kind regards, Met vriendelijke groet,



Martijn van Breden

lead software architect





________________________________
Van: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> namens Martijn van Breden <m.vanbreden@xxxxxxxxxxxxxxxxxxxxxxxxxx>
Verzonden: vrijdag 3 november 2023 17:13
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.



Hi Daniel

This has been my approach as well. Today I tested a lot of in-between results in the RPG program and in ACS as well.
My conclusion from that is still that in RPG it's doing something different when it comes to a full outer join. If it gets null values from one table the values in the other result set are set to null as well. There's probably a good explanation for this but I'm out of fresh ideas by now. Next week at Common Benelux I may have a chat with Scott Forstie or Birgitta or I'll check in with IBM on this afterwards.

Have a nice weekend!

Kind regards, Met vriendelijke groet,

Martijn van Breden
lead software architect


________________________________
Van: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> namens Daniel Gross <daniel@xxxxxxxx>
Verzonden: vrijdag 3 november 2023 06:58
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.



Hi,

been there, done that.

My approach was to first get into the debugger and stop the program before the query gets executed.

Then inspect everything - all host veriables, possible overrides, *LIBL, aliases, QTEMP and all.

After that, copy the whole query back from the source and run it in iACS - just leave the host variables in, and fill them with the exact values from the program.

One last approach is to check the user authorization - the iACS jobs are running under QUSER and then change to the signed on user - interactive jobs run directly under the signed on user profile - so that is one possible difference.

But as others pointed out - the DB engine running the query is the same for both.

HTH
Daniel


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.

--
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.


--
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 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.