Re: Database server job (QZDASOINIT) performance anomaly

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 ;-)
.

Relevant Pages

  • SQL 2005 Express Connection Problem
    ... The remote site is not a company ... this remote site and try to log in I get this error: ... An error has occurred while establishing a connection to the server. ... When connecting to SQL Server 2005, this failure may be caused by the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Advice Needed: Asynchronous Replication/Mirroring to central o
    ... Looking for a SQL Server replication book? ... geographically-dispersed remote locations. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2005 Express Connection Problem
    ... sql2005 support auto attaching a database in the connect string. ... The remote site is not a company ... An error has occurred while establishing a connection to the server. ... When connecting to SQL Server 2005, this failure may be caused by the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Database server job (QZDASOINIT) performance anomaly
    ... Following an IPL of the iSeries, the Windows server in one location ... hangs when running the queries. ... sometimes sql ptfs may change sql behavior a lot:-| ...
    (comp.sys.ibm.as400.misc)
  • Re: C# - connection to a remote SQL server
    ... remove server ... want to authenticate with trusted auth or sql auth. ... storing a token for access to the remote pc. ... understand a lot about nt authentication and tokens. ...
    (microsoft.public.dotnet.languages.csharp)