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

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]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]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]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]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]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]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]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]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]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]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]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]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]https://www.mailguard.com.au

References

Visible links
1. https://www.oneteamit.com.au/
2. https://lists.midrange.com/mailman/listinfo/midrange-l
3. https://archive.midrange.com/midrange-l.
4. https://lists.midrange.com/mailman/listinfo/midrange-l
5. https://archive.midrange.com/midrange-l.
6. https://lists.midrange.com/mailman/listinfo/midrange-l
7. https://archive.midrange.com/midrange-l.
8. https://lists.midrange.com/mailman/listinfo/midrange-l
9. https://archive.midrange.com/midrange-l.
10. https://lists.midrange.com/mailman/listinfo/midrange-l
11. https://archive.midrange.com/midrange-l.
12. https://lists.midrange.com/mailman/listinfo/midrange-l
13. https://archive.midrange.com/midrange-l.
14. https://www.mailguard.com.au/