Re: Database server job (QZDASOINIT) performance anomaly

jacko wrote:
On Jan 3, 4:33 pm, "Stefano P." <procs...@xxxxxxxxxxxxxxxxxxxxxxx>
wrote:
We have several Windows servers (2003) around the country that all issue
the same set of SQL queries against our iSeries; only the value of a
location parameter changes from one remote server to another.
Following an IPL of the iSeries, the Windows server in one location
hangs when running the queries. Queries that used to run in a couple of
minutes, and that continue to run for that duration in the other remote
locations, now run 15 minutes or longer, sometimes for hours. Nothing
has changed on any of the Windows servers.
May something has changed on iSeries server...

The last time this happened (we only IPL a couple of times a year),
IPLing twice a year may be a good reason to use ipl itself to apply ptf:
sometimes sql ptfs may change sql behavior a lot :-|
(I've opened several "pmr" related to sql problems after some ptfs...)

the
sysadmin for the remote location experiencing the slowdown moved the
functionality to a "virtual server". It was subsequently moved back to
the original server, and it ran fine until the most recent IPL on Monday.
If iSeries is at V5R4 then it could be rather normal:
after every ipl you loose the "plan cache".
And you loose all temporary indexes that at V5R4 may be built
"automatigically".
You have to wait for the system to rebuild those indexes and to "build"
plan cache again. For the indexes, as they are in Index Advisor, it
could be better to build them instead of let the system do it for you...

I'm not sure where to start with a performance analysis of this
particular server's QZDASOINIT job - I glanced at the performance
subsection in the system management section of the iSeries information
center, and it was like drinking from a firehose. Any suggestions?
Look at the "Database" section:
if at V5R4 than Index Advisor and Plan Cache may help you very quickly ;-)
If you are at V5R3 or less I'd collect database monitors for those
queries and check them with Visual Explain and the related queries on
dbmon itself.

Thanks in advance.
HTH
Stefano P.

--
"Niuna impresa, per minima che sia,
può avere cominciamento e fine senza queste tre cose:
e cioè senza sapere, senza potere, senza con amor volere"
[Anonimo fiorentino, XIV sec.]

(togliere le "pinzillacchere" dall'indirizzo email ;-)

Jon, curious, how you running these, TELNET (port 23), GUI ?? Batch
or Interactive Wonder if the app is getting an exclusive LCKW.

The Windows server establishes (I believe) an OLE-DB (but it could be ODBC) connection to the iSeries. It then passes the complex dynamic SQL statement to the database server program (QZDASOINIT), which seemingly returns the result set to the remote server.

I think I may have solved the problem. Based on Stefano's comments, I did a web search on "plan cache", and that led me to an IBM Information Center page, which confirmed that dynamic SQL statements' access plans are cleared from the plan cache during an IPL. So, I copied the server's SQL statement and embedded it in an SQLRPGLE program, which declares a cursor for the query, opens it, then closes it. The next time they fired up the server connection to the iSeries, it ran the query in the expected 2-4 minutes.

The ideal solution, I think, would be for me to code their SQL query as a stored procedure, and they would get rid of their dynamic SQL statement passed to the iSeries, and replace it with a call to the stored procedure. The SP would contain the access plan, and that would persist across IPLs. That won't happen, for internal political reasons. The next-best solution is to keep this "dummy" program containing their SQL query in a cursor definition, and perhaps call that program in an autostart job when the system IPLs.
.

Relevant Pages

  • Re: Database server job (QZDASOINIT) performance anomaly
    ... Following an IPL of the iSeries, the Windows server in one location ... hangs when running the queries. ... IPLing twice a year may be a good reason to use ipl itself to apply ptf: ...
    (comp.sys.ibm.as400.misc)
  • Re: Database server job (QZDASOINIT) performance anomaly
    ... the same set of SQL queries against our iSeries; only the value of a location parameter changes from one remote server to another. ... Queries that used to run in a couple of minutes, and that continue to run for that duration in the other remote locations, now run 15 minutes or longer, sometimes for hours. ...
    (comp.sys.ibm.as400.misc)
  • Re: MSDE replacing Access2000 in VB Application
    ... I agree with Mary but wanted to point out that the next version of MSDE (Sql ... server 2005 express) is supposed to support the cross tab queries (PIVOT I ...
    (microsoft.public.sqlserver.msde)
  • Re: form-based parameters in a query (access 2003 - ADP)
    ... Oh and if you're using an ADP, your saved queries are on the server as well, not just the tables. ... Alan wrote: ... Create the SQL text and plop in the value of Region just before you execute it. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Linked Tables in Access
    ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ... Even if the Jet Expression Service processes a query on the client, does SQL ...
    (microsoft.public.access.adp.sqlserver)