Re: Stored proceudre SQL reuse ODP?

I didn't know you were calling from Net.Data, so that's more difficult.

Maybe you could call a routing procedure and then have it call the stored procedure instance for that customer and have a separate stored procedure for each customer.

If the SQL statements in the procedure are being handled by SQE, then it's less of an issue.

I'd really suggest attending the iSeries SQL Performance workshop in July to understand the performance implications:
ibm.com/servers/eserver/iseries/service/igs/db2performance.html

David Wilson wrote:
Thanks Kent. I did call IBM support earlier today and sent them some comm traces and DB monitor output.

Can you elaborate on the "one connection per customer"? How would I force that to happen?

The stored procs are called remotely from a Net.Data macro on a Web server (partition) to the remote database on another partition.

Many thanks,

David Wilson

"Kent Milligan" <kmill@xxxxxxxxxxxxxx> wrote in message news:446644e7$1@xxxxxxxxxxxxxxxxxxxx
Sounds like a defect that should be reported to IBM Support.

I'd probably also suggest having one connection per customer in the future to get the best performance.

David Wilson wrote:
Sorry for my misspelling - s/b Stored procedure SQL reuse ODP?

"David Wilson" <david_wilson@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:44636ef1$0$8839$afc38c87@...
I have a stored procedure written in ILE RPG/free that uses SQL to select data. A cursor to the result set is returned by the stored proc. Within the procecdure, based on a parameter passed in, the library list is changed prior to the SQL execution. Our database services multiple customers by implementing a common database schema in separate libraries (collections), by customer. After "pointing" to the set of data for the current requester (customer), I then execute the SQL satement.

What I am experiencing is that even though I am pointing to files for customer "A", a previous ODP, it appears, for customer "B" (from a previous stored proc call) is being used.

All of this is happening in remote server jobs (QRWTSRVR) in QUSRWRK - as we are calling the stored proc from a Web server LPAR to our production database LPAR.

I hope this is clear. My question is: Is there any way to keep the previously opened ODP from being used in this stored procedure?

(Note: I am not using PREPARED SQL statements)

Thanks,

David




--
Kent Milligan
ISV Enablement - System i
kmill@xxxxxxxxxxxxxxxx (spam trick) GO HAWKEYES!!
ibm.com/iseires/db2
(opinions stated are not necessarily those of my employer)



--
Kent Milligan
ISV Enablement - System i
kmill@xxxxxxxxxxxxxxxx (spam trick) GO HAWKEYES!!
>>> ibm.com/iseires/db2
(opinions stated are not necessarily those of my employer)
.

Relevant Pages

  • Re: OOP - a question about database access
    ... >>and project so much better in SQL DBMSes than in ODBMSes, ... >>100x more bytes from the database, just because you want your objects to ... > a related invoice.. ... > assoication from the customer to the invoice collection and have done ...
    (comp.object)
  • Re: Suggestoins for Local DB to use with Shareware
    ... I use Access for all my small installations. ... Even if the customer doesn't have Access installed, ... The DB should fully support SQL. ... a .NET v1.1 app that used the ubiquious NorthWind database in either MS ...
    (borland.public.delphi.non-technical)
  • Re: newbe question
    ... the application I should send the database information as well. ... backup of the database to my customer. ... If the customer installed sql server - what tools does he need to be ... The prime example here is collations. ...
    (microsoft.public.sqlserver.server)
  • Re: help :Deploying a preconfigured database
    ... This may leave the issue that the restored / attached database at the ... database then using the Transfer Wizard in SQL EM to copy across everything ... When the customer adds users the users ... > server and have at the end a traight forward running database copy of what ...
    (microsoft.public.sqlserver.setup)
  • Re: Is there any product for detecting compromised log files/procedure
    ... On one of our websites - when a customer ... that data within our sql server was invoking this malware since just ... database, using HTMLEncode to guarantee that data from the database is ... Always HTMLEncode character data ...
    (microsoft.public.sqlserver.programming)