[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

QZDASOINIT, iSeries ODBC weird behavior

Posted on 2012-09-17
3
Medium Priority
?
1,316 Views
Last Modified: 2012-09-19
We have recently updates our OS from 5.4 to 6.1, and I have found that when the users run an ODBC  program using Microsoft EXCEL , these jobs  QZDASOINIT take too much CPU utilization and seems to do anything, so I need to cancel those.

I wonder if something has changed from one version to the newer one.
0
Comment
Question by:Apolo Victores
3 Comments
 
LVL 36

Accepted Solution

by:
Gary Patterson earned 2000 total points
ID: 38407696
I've seen queries that perform worse (sometimes much worse) under V6R1 than they did under V5R4, so it is certainly possible.  

IF you haven't already, I suggest you take a look at the V6R1 Memo To Users, and check out the various database-related changes that occurred in V6R1 that could have an impact.  There were a significant number of SQL-related changes, and some of them could have a performance impact.  "Database Changes' start on page 20.

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzaq9/rzaq9.pdf

A few notes:

1) The V6R1 SQL Query Engine seems to rely on database statistics more than V5R4 SQE did.  Make sure statistics collection is enabled if the query uses the SQE, and if specific statistics collection is advised after this query runs, then make manual requests to gather the required stats.

2) I've seen cases where new indexes were advised after the V6R1 upgrade.  Take a look at the Index Advisor, or run a SQL Performance Monitor when the problem queries are running and see if indexes are advised.  If so, test them to determine if building them is warranted.

3) Make sure that you are up to date on database group PTF's

The following documents explain how to view statistics and index advice, enable statistics collection, manually request specific statistics, view the SQL Plan Cache, run an SQL Performance Monitor, etc.

This redbook was written for V5R3, but still contains a lot of good information about diagnosing DB2 for IBM i performance issues:

http://www.redbooks.ibm.com/redbooks/pdfs/sg246654.pdf

This is the DB2 for IBM i V6R1Database Performance and Query Optimization guide:

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/rzajq/rzajq.pdf

If you want help with a specific query, post an SQL Performance Monitor file extract or an SQL Plan Cache snapshot extract.  Let me know if you need instructions for tathering that information.

- Gary Patterson
0
 
LVL 27

Expert Comment

by:tliotta
ID: 38412973
Is there any indication that the same queries run differently after the second or third submission? You might delete any old SQL packages for Excel queries to force them to be recreated.

What hardware model is your system? How much memory? Can you paste the text copied from a DSPSYSSTS screen?

Tom
0
 

Author Closing Comment

by:Apolo Victores
ID: 38413677
THANK YOU!!!  it is really helpful!!!
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will be showing you how to subnet the easiest way possible for IPv4 (Internet Protocol version 4). This article does not cover IPv6. Keep in mind that subnetting requires lots of practice and time.
In the below post we have mentioned the best hosting type for startups. Also, check out some of the superlative web hosting companies that are proposing affordable web hosting solutions to host your startup website.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month19 days, 11 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question