Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2012
    Posts
    5

    Unanswered: DB2 9.5 Admin User has better performance than other users

    Hello all,

    This is my first post in this forum. Please move this if I have posted in the wrong location. Thank you in advance for any help that anyone chooses to provide!

    I am running DB2 9.5 FixPack 5 and connecting with a 3rd party GIS application (ArcGIS Desktop 10.0). Independent of any changes that I make in the GIS application, I notice that performance is drastically increased when I grant a user the "Database administrator authority" from the Authorities user properties.

    However, it is not acceptable for our situation that every user who connects to the database via the 3rd party application is granted the administrator role.

    My question is, what function does the "Database administrator authority" have that could increase performance so much, or on the contrary what setting for a non-admin user could reduce performance greatly?

    I have tried to take apart the admin authority and just grant the individual roles such as the following, but they have no effect on performance on their own:

    •BINDADD
    •CONNECT
    •CREATETAB
    •CREATE_EXTERNAL_ROUTINE
    •CREATE_NOT_FENCED_ROUTINE
    •IMPLICIT_SCHEMA
    •QUIESCE_CONNECT
    •LOAD

    That was obtained from:
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    If anyone has any suggestions or even a better explanation of how DB2 admin authority/permission could alter performance, please feel free to respond or ask me for additional information. Thanks!

    -Matt-

    Config:
    DB2 v9.5.500.784 Build s091123 FixPack5 JDK 1.5.0
    Server: Win Server 2008 R2 64bit
    Client: Win 7 64bit
    3rd party GIS App: Esri ArcGIS Desktop 10

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by MGZ Maps View Post
    I notice that performance is drastically increased when I grant a user the "Database administrator authority" from the Authorities user properties.
    How do you measure that?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2012
    Posts
    5
    How do you measure that?
    Hello n_i, thanks for your response. I should define performance.

    In our situation, performance is the speed at which spatial data draws on the map in the ArcGIS Desktop application. As soon as admin is granted, performance picks up tremendously for that user - then as soon as it is revoked, the drawing slows to a crawl.

    A follow up question that I have is, what does dbadm grant in regard to accessing the spatial extender (if anything)?

    Thanks

    -Matt-

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't know about the spatial extender, but I doubt there is a direct relationship between permissions and performance. Firstly, just to confirm that we are talking about the DB2 DBADM authority, not something that is specific to ArcGIS. Secondly, the speed of drawing something in the user interface of a 3d party application does not necessarily relate to the database performance.

    Can you look at the DB2 metrics and compare them when running with and without the DBADM authority?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Dec 2012
    Posts
    5
    I doubt there is a direct relationship between permissions and performance.
    I would normally agree...but since I can see an immediate change in performance without altering anything else, it is clearly at least related in this situation.

    confirm that we are talking about the DB2 DBADM authority, not something that is specific to ArcGIS.
    That is correct, I am specifically referring to granting or revoking the DB2 DBADM authority. No changes in ArcGIS.

    the speed of drawing something in the user interface of a 3d party application does not necessarily relate to the database performance
    Yes you are right, there are many operations within the 3rd party application that are not related to db performance, but as I stated the only variable that changes is a grant/revoke DB2 DBADM authority.

    As soon as that authority is revoked, draw performance decreases greatly and even moving the mouse around the map becomes sluggish when trying to edit features.

    Can you look at the DB2 metrics and compare them when running with and without the DBADM authority?
    Yes, I have looked at how long it takes for certain queries to execute, and the time it takes for the exact operation as admin is much faster (3x to 7x faster). I just don't understand why that would happen?

    --------------------------------------------------------------------------

    With admin

    SQLPrepareW( )
    <--- SQL_SUCCESS Time elapsed - +1.287000E-003 seconds

    SQLBindParameter( hStmt=1:8, iPar=1, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_SBIGINT, fSQLType=SQL_BIGINT, cbColDef=0, ibScale=0, rgbValue=&06d6edb0, cbValueMax=0, pcbValue=&07647ab8 )
    ---> Time elapsed - +1.200000E-005 seconds

    SQLBindParameter( )
    <--- SQL_SUCCESS Time elapsed - +2.400000E-005 seconds


    Without admin

    SQLPrepareW( )
    <--- SQL_SUCCESS Time elapsed - +4.013700E-002 seconds

    SQLBindParameter( hStmt=1:8, iPar=1, fParamType=SQL_PARAM_INPUT, fCType=SQL_C_SBIGINT, fSQLType=SQL_BIGINT, cbColDef=0, ibScale=0, rgbValue=&07169000, cbValueMax=0, pcbValue=&0773f728 )
    ---> Time elapsed - +9.000000E-006 seconds

    SQLBindParameter( )
    <--- SQL_SUCCESS Time elapsed - +1.400000E-005 seconds

    --------------------------------------------------------------------------

    I do appreciate your time in responding.

    -Matt-

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Having DBADM improves performance (due to less access to the catalog tables / catalog cache) , but I don't expect to see a very big difference.

    Found the following APARs:
    IBM IZ12145: USERS SUBMITTING QUERIES THAT DO NOT HAVE DBADM AUTHORITY MAY CA USE POSSIBLE CONTENTION ON SYSIBM.SYSUSERAUTH SLOWING DOWN QUERY - United States
    IBM IZ96021: CACHED QUERY MAY GET RE-COMPILED WITH EVERY EXECUTION IF METHOD IS REFERENCED - United States


    First one is fixed in FP1+, so it doesn't apply.

    Not sure if the second one is related to your problem or not, collect snapshots to confirm. Or if you can upgrade, do it and see if this makes any difference.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    Having DBADM improves performance (due to less access to the catalog tables / catalog cache)
    This is interesting. Can you please elaborate?
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by n_i View Post
    This is interesting. Can you please elaborate?
    I had a similar issue in the past and was told that having DBADM improves performance because db2 doesn't need to access (or needs a lot less access) catalog tables / cache. Sorry, don't know any additional details. Catalog services team would be able to clarify / confirm this.

  9. #9
    Join Date
    Dec 2012
    Posts
    5
    Thank you db2girl for your research and for those links. I am installing Fix Pack 10 on a 9.5 test machine tonight/tomorrow and I will certainly share with the forum the results.

    In this same database, there is also an additional issue that may or may not be related. After the ArcGIS application has closed and terminated its connection, the connection remains in the database and becomes orphaned. This in turn places orphaned locks on various rows, layers, tables, etc that were being accessed via that connection.

    That behavior only occurs when connected as a non-admin user.

    I already know how to clear out the orphaned locks and orphaned connections and go on with my business. However, I have not discerned why this is happening or if this behavior is related to my DB2 DBADM performance issue.

    Once again thank you everyone for your input.

    -Matt-

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    IZ96021 / upgrade should resolve your performance problem - I checked the APAR and customer who reported this problem was using ArcGIS.

  11. #11
    Join Date
    Dec 2012
    Posts
    5

    Thumbs up

    IZ96021 / upgrade should resolve your performance problem - I checked the APAR and customer who reported this problem was using ArcGIS.
    Thank you for the followup db2girl. You are correct.

    After installing DB2 9.5 Fix Pack 10 this issue was resolved. There is now identical performance in the 3rd party ArcGIS application regardless of DB2 DBADM authority.

    Furthermore there are no orphaned connections being created after Fix Pack 10 is installed. It must have been related because the orphaned connection issue is also resolved.

    For anyone who has this issue in the future, we needed to upgrade to DB2 9.5 Fix Pack 10 for Server as well as Fix Pack 10 for Spatial Extender.

    Thanks again!

    -Matt-
    Last edited by MGZ Maps; 12-19-12 at 17:15.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •