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: 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
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: 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
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: 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 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: 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 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: 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 https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.