OPNQRYF and shared ODP

["Joe Teff" <jteff19@xxxxxxx>]

   The client requested that I give them a way to add "production status" 
comments to several reports. They want to be able to enter a comment and tie it 
to vendors, items, sales order and/or purchase order.
   An item can be coded to a vendor (purchased parts) or left unassigned. 
Purchase Orders will specify a vendor and item(s), but will contain a Sales 
Order Number for dropshipped items. A Sales Order will contain items and also 
possibly be related to one or more Purchase Orders for dropshipped items.
   The reports include several versions of open orders, stock status reports 
and open purchase reports. The comments may contain more than one linkage (for 
example, both item and PO or Sales Order and Vendor).
   In order to provide the most flexibility, I am building the QRYSLT statement 
in my RPGLE program and then issuing the OPNQRYF statement to select the 
appropriate comments for each line on the report. The function works great, 
except for the performance issue of opening the comments file so many times. I 
tried to do a shared open in a CL so that I could get improved open time in my 
RPG by using an open ODP. It doesn't like that. It abended because I already 
had the file open.
   I have not implemented this function yet because of the response issues. The 
users like the way it works because of the flexibility. I am considering 
changing my OPNQRYF approach to creating enough logicals to cover all of the 
possibilities. I then hope that they do not decide to add anymore data pieces 
to connect comments to.
   The SQL product is not on the system.
   Does anyone have any ideas here?  The report programs are already taking 3 - 
4 times as long to run and the comments file has very records in it.

Joe Teff
QDS
Bloomington, MN
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the Midrange System Mailing List!  To submit a new message,   *
* send your mail to "MIDRANGE-L@midrange.com".  To unsubscribe from     *
* this list send email to MAJORDOMO@midrange.com and specify            *
* 'unsubscribe MIDRANGE-L' in the body of your message.  Questions      *
* should be directed to the list owner / operator: david@midrange.com   *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *