• Subject: OPNQRYF INCORROUT - Incorrect number of records selected
  • From: Dave Murvin <davem@xxxxxxxx>
  • Date: Thu, 22 Jul 1999 20:44:43 -0700
  • Organization: DRM Enterprises, Inc

One of my clients is having problems with OPNQRYF not always selecting
the correct number of records.  Sometimes it selects about ten records
(more often than not), and sometimes it selects 800+ records.  The 800+
records is the correct selection. I can submit the job one time and it
selects 10 records, submit the same job a second time and it selects
800+ records. The joblogs look identical.  If I run it interactively, it
might select 10 records 3 times in a row, then select 800+ records, then
go back to selecting 10 records. They are on V4R3 cum PTF C8279430.

I checked the APARS and SA75673 (PTF SF53242) seems close, but the
problem queries are not using ALWCPYDTA(*OPTIMIZE).  They do not have
this PTF installed, but I will suggest that they install it anyway.
Could this PTF solve additional problems with OPNQRYF, bitmaps and S/O
indexes?

Problem query ( I am hand typing so hope it comes out all right):

CHGVAR    VAR(&QRYSLT) VALUE('LID *EQ "CL" *AND LWHS *EQ "' *CAT &WHSE
*CAT &WHSE *CAT '" *AND HSTORE *EQ 1'')

OVRDBF     FILE(ECLL33)  SHARE(*YES)
OPNQRYF FILE((ECLL33 *FIRST IPE500CL) (ECH)) FORMAT(ECLL33 IPE500CL)
QRYSLT(&QRYSLT) KEYFLD(LPRCON *DESCEND) (LWHS) (LORD) (LLINE))
JFLD((HORD LORD)) MAPFLD((LPRCON HSTORE))


File ECLL33 is a multiple member logical file.  The first member is what
we want and it seems to be selecting the first member.
FILE ECH is a multiple member physical file.  The first member is what
we want and it seems to be selecting the first member.
Field HSTORE is in the ECH file and is being mapped into the ECLL33 file
for ease of reporting.

I have tried specifically selecting the first member of each of these
files and in the OVRDBF - no help.
I have run the job in debug mode with a query time limit of zero to see
what's going on.  All query optimizer statements are selecting the
correct members of the files.  2 Access paths were used for bitmap
processing of file ECLL33 - both access paths selected have select omit
statements.

Additional considerations:
They have just completed a Y2K conversion this past weekend (programs
and files), but I don't see how this could cause the problem.  They have
been on this V4R3 release and PTF level for several weeks and we have
not seen this problem  using the old programs (with identical OPNQRYF
statements since 11/98) and files (at least no one has mentioned it
before).  I have looked at the file descriptions and the members we are
interested in all appear to have been created first.

A question on how the *FIRST member is selected.  Does it look at just
the date created or does it look at the member level identifier?  I
could not find a time created for the members other than the last part
of the level identifier.  When I check the level identifier, the desired
members are the first members, although a couple of members were created
on the same date.

Any suggestions would be appreciated.  I will be working at a different
client Friday, but will be checking for the "golden" reply in the
evening.

Thanks in advance for your help.

Dave Murvin
DRM Enterprises, Inc.
davem@drme.com



+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---


This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 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 here. If you have questions about this, please contact [javascript protected email address].