RE: sql not returning full result set -- MIDRANGE-L

Thanks Jim,

Do you know if that will get results from a query run from iACS run SQL
Scripts.

I started the monitor for *ALL jobs and ran my query but got no results
except for my query I ran from the greenscreen against the table the
monitor produced.

Thanks
Don

Don Brown

Senior Consultant

[1]OneTeam IT Pty Ltd
P: 1300 088 400

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jim
Franz
Sent: Tuesday, 4 February 2025 1:22 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: sql not returning full result set

[2]https://www.ibm.com/docs/en/i/7.5?topic=monitor-start
jim

On Mon, Feb 3, 2025 at 8:56 PM Don Brown via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> Hi Charles,
>
> Can you point me to a description of how to set this monitor up.
>
> Thanks
> Don
>
>
>
> Don Brown
>
> Senior Consultant
>
> [1]OneTeam IT Pty Ltd
> P: 1300 088 400
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Charles Wilt
> Sent: Tuesday, 4 February 2025 3:53 AM
> To: Midrange Systems Technical Discussion <
> midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: Re: sql not returning full result set
>
> Start a DB monitor for the user or the table..
>
> Charles
>
> On Mon, Feb 3, 2025 at 10:43 AM Jim Franz <franz9000@xxxxxxxxx> wrote:
>
> > Is there a better way to see the actual sql from external systems
> > other than the SQL Plan Cache > Show statements in Navigator, plus the
> > statements in Index Advisor?
> > I'll admit I'm confusing analysis work i've done in DB2 LUW vs DB2 for
> i.
> > Jim
> >
> > On Mon, Feb 3, 2025 at 12:27 PM Jim Franz <franz9000@xxxxxxxxx>
> wrote:
> >
> > > To add to what Craig said, i would execute not just a count(*) but
> > > the exact same query from the win system to both A & B.(and as Rb
> > > mentioned - chk the joblog.
> > >
> > > Jim Franz
> > >
> > > On Mon, Feb 3, 2025 at 11:58 AM Rob Berendt
> > > <robertowenberendt@xxxxxxxxx
> > >
> > > wrote:
> > >
> > >> Craig has a good thought. I'd check the joblog involved for
> anything.
> > If
> > >> it is data such as he is thinking narrow your search down to 1 or 2
> > >> character columns so invalid packed data is out of the question.
> > >>
> > >> On Mon, Feb 3, 2025 at 11:52 AM <craig@xxxxxxxxxx> wrote:
> > >>
> > >> > Also, check to see if there is invalid data in record 582 on
> Sys B.
> > Look
> > >> > for character data below x'40'.
> > >> >
> > >> > Craig Pelkie
> > >> >
> > >> > -----Original Message-----
> > >> > From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On
> > >> > Behalf Of craig@xxxxxxxxxx
> > >> > Sent: Monday, February 3, 2025 02:59
> > >> > To: 'Midrange Systems Technical Discussion' <
> > >> midrange-l@xxxxxxxxxxxxxxxxxx
> > >> > >
> > >> > Subject: RE: sql not returning full result set
> > >> >
> > >> > What middleware?
> > >> >
> > >> > What is used for the connection from the Windows environment to
> > >> > the
> > IBM
> > >> i
> > >> > systems? (ODBC, OLE DB, .NET Provider, other)
> > >> >
> > >> > In the past, I had an issue using MSFT SQL Server Integration
> > >> > Services (SSIS), connecting to IBM i using the iSeries Access
> > >> > (correct name at
> > >> that
> > >> > time) OLE DB provider. It would cut off after several hundred
> > >> > rows. I
> > >> was
> > >> > never able to get to the bottom of it, had to adopt another
> approach.
> > >> >
> > >> > Craig Pelkie
> > >> >
> > >> > -----Original Message-----
> > >> > From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On
> > >> > Behalf Of
> > >> Don
> > >> > Brown via MIDRANGE-L
> > >> > Sent: Monday, February 3, 2025 00:18
> > >> > To: midrange-l@xxxxxxxxxxxxxxxxxx
> > >> > Cc: Don Brown <dbrown@xxxxxxxxxxxxxxxx>
> > >> > Subject: sql not returning full result set
> > >> >
> > >> > We have a problem that I have not been able to get to the bottom
> > >> > of
> > >> about
> > >> > the size of a result set returned from a sql query.
> > >> >
> > >> > A client is doing some analysis and has some middleware to return
> > >> > the result set to Azure.
> > >> >
> > >> > So the middleware sits on a Windows machine and connects to the
> > >> > two servers.
> > >> >
> > >> > My confusion is why results are different - let me explain.
> > >> >
> > >> > We are connecting to 2 IBMi systems, both at 7.3 - lets call them
> > >> > A
> > and
> > >> B
> > >> >
> > >> > If we run the query requesting a count be returned both systems
> > >> > return
> > >> the
> > >> > same count.
> > >> >
> > >> > If we run the query to return the data A will retrieve 100k+
> > >> > records
> > >> and B
> > >> > will return 581 records.
> > >> >
> > >> > Due to the network topology, I currently have only green screen
> > >> > access
> > >> but
> > >> > hopefully I will get ACS and Navigator access soon.
> > >> >
> > >> > Neither system as QAQQINI set up or any environment overrides.
> > >> >
> > >> > I just don't know what would limit the result set / amount of
> > >> > data returned from system B
> > >> >
> > >> > Any suggestions gratefully accepted.
> > >> >
> > >> > Thanks
> > >> > don
> > >> >
> > >> > Brisbane - Sydney - Melbourne
> > >> >
> > >> >
> > >> > Don Brown
> > >> >
> > >> > Senior Consultant
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > P: 1300 088 400
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > DISCLAIMER. Before opening any attachments, check them for
> > >> > viruses and defects. This email and its attachments may contain
> > >> > confidential information. If you are not the intended recipient,
> > >> > please do not
> > read,
> > >> > distribute or copy this email or its attachments but notify
> > >> > sender and delete it. Any views expressed in this email are those
> > >> > of the
> > individual
> > >> > sender
> > >> > --
> > >> > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > >> > mailing list To post a message email:
> > >> > MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
> > >> subscribe,
> > >> > unsubscribe, or change list options,
> > >> > visit:
[2][3]https://lists.midrange.com/mailman/listinfo/midrange-l
> > >> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > >> > Before posting, please take a moment to review the archives at
> > >> > [3][4]https://archive.midrange.com/midrange-l.
> > >> >
> > >> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
> > >> related
> > >> > questions.
> > >> >
> > >> >
> > >> >
> > >> > --
> > >> > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > >> > mailing list To post a message email:
> > >> > MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
> > >> subscribe,
> > >> > unsubscribe, or change list options,
> > >> > visit:
[4][5]https://lists.midrange.com/mailman/listinfo/midrange-l
> > >> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > >> > Before posting, please take a moment to review the archives at
> > >> > [5][6]https://archive.midrange.com/midrange-l.
> > >> >
> > >> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
> > >> related
> > >> > questions.
> > >> >
> > >> >
> > >> >
> > >> > --
> > >> > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > >> > mailing
> > >> list
> > >> > To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
> > >> > subscribe, unsubscribe, or change list options,
> > >> > visit:
[6][7]https://lists.midrange.com/mailman/listinfo/midrange-l
> > >> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > >> > Before posting, please take a moment to review the archives at
> > >> > [7][8]https://archive.midrange.com/midrange-l.
> > >> >
> > >> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
> > >> related
> > >> > questions.
> > >> >
> > >> >
> > >> --
> > >> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > >> mailing list To post a message email:
> MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> > >> To subscribe, unsubscribe, or change list options,
> > >> visit: [8][9]https://lists.midrange.com/mailman/listinfo/midrange-l
> > >> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > >> Before posting, please take a moment to review the archives at
> > >> [9][10]https://archive.midrange.com/midrange-l.
> > >>
> > >> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
> > related
> > >> questions.
> > >>
> > >>
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
> > subscribe, unsubscribe, or change list options,
> > visit: [10][11]https://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives at
> > [11][12]https://archive.midrange.com/midrange-l.
> >
> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
> > related questions.
> >
> >
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
> subscribe,
> unsubscribe, or change list options,
> visit: [12][13]https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> [13][14]https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
> related
> questions.
>
> --
> Message protected by MailGuard: e-mail anti-virus, anti-spam and content
> filtering.
> [14][15]https://www.mailguard.com.au
>
> References
>
> Visible links
> 1. [16]https://www.oneteamit.com.au/
> 2. [17]https://lists.midrange.com/mailman/listinfo/midrange-l
> 3. [18]https://archive.midrange.com/midrange-l.
> 4. [19]https://lists.midrange.com/mailman/listinfo/midrange-l
> 5. [20]https://archive.midrange.com/midrange-l.
> 6. [21]https://lists.midrange.com/mailman/listinfo/midrange-l
> 7. [22]https://archive.midrange.com/midrange-l.
> 8. [23]https://lists.midrange.com/mailman/listinfo/midrange-l
> 9. [24]https://archive.midrange.com/midrange-l.
> 10. [25]https://lists.midrange.com/mailman/listinfo/midrange-l
> 11. [26]https://archive.midrange.com/midrange-l.
> 12. [27]https://lists.midrange.com/mailman/listinfo/midrange-l
> 13. [28]https://archive.midrange.com/midrange-l.
> 14. [29]https://www.mailguard.com.au/
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
> subscribe, unsubscribe, or change list options,
> visit: [30]https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> [31]https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
> related questions.
>
>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: [32]https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
[33]https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content
filtering.
[34]https://www.mailguard.com.au

References

Visible links
1. https://www.oneteamit.com.au/
2. https://www.ibm.com/docs/en/i/7.5?topic=monitor-start
3. https://lists.midrange.com/mailman/listinfo/midrange-l
4. https://archive.midrange.com/midrange-l.
5. https://lists.midrange.com/mailman/listinfo/midrange-l
6. https://archive.midrange.com/midrange-l.
7. https://lists.midrange.com/mailman/listinfo/midrange-l
8. https://archive.midrange.com/midrange-l.
9. https://lists.midrange.com/mailman/listinfo/midrange-l
10. https://archive.midrange.com/midrange-l.
11. https://lists.midrange.com/mailman/listinfo/midrange-l
12. https://archive.midrange.com/midrange-l.
13. https://lists.midrange.com/mailman/listinfo/midrange-l
14. https://archive.midrange.com/midrange-l.
15. https://www.mailguard.com.au/
16. https://www.oneteamit.com.au/
17. https://lists.midrange.com/mailman/listinfo/midrange-l
18. https://archive.midrange.com/midrange-l.
19. https://lists.midrange.com/mailman/listinfo/midrange-l
20. https://archive.midrange.com/midrange-l.
21. https://lists.midrange.com/mailman/listinfo/midrange-l
22. https://archive.midrange.com/midrange-l.
23. https://lists.midrange.com/mailman/listinfo/midrange-l
24. https://archive.midrange.com/midrange-l.
25. https://lists.midrange.com/mailman/listinfo/midrange-l
26. https://archive.midrange.com/midrange-l.
27. https://lists.midrange.com/mailman/listinfo/midrange-l
28. https://archive.midrange.com/midrange-l.
29. https://www.mailguard.com.au/
30. https://lists.midrange.com/mailman/listinfo/midrange-l
31. https://archive.midrange.com/midrange-l.
32. https://lists.midrange.com/mailman/listinfo/midrange-l
33. https://archive.midrange.com/midrange-l.
34. https://www.mailguard.com.au/