Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2014
    Posts
    1

    Unanswered: We are getting slower and slower response times on dropping user tables in UDB

    bicdax101(tedpi01)/opt/udb/tedpi01: db2 "select count (*) from J133926.ZCLOSEIMPACT with ur"

    1
    -----------
    1273916

    1 record(s) selected.


    We used to be able to do this a few seconds.
    Now it takes anywhere from 1 minute and 10 minutes to do this. This is slowing down ad-hoc and production jobs.

    I am concerned that it is taking longer and longer. And maybe it will just start clocking, instead of dropping the tables.



    Example -1 : delete a user table from UDB, via Aqua Studio (1.2m rows)

    drop table zCloseImpact /* user id = j133926 */

    0 record(s) affected

    [Executed: 1/13/14 2:35:18 PM CST] [Execution: 25m 11s]



    Example -1 : these tables each contain 15 rows of data; executed as pass through from SAS into UDB;

    NOTE: Deleting UD_USERV.Z15ACCTS (memtype=DATA).
    NOTE: Deleting UD_USERV.Z15ACCT2 (memtype=DATA).
    2577
    2578 GOPTIONS NOACCESSIBLE;
    2579 %LET _CLIENTTASKLABEL=;
    2580 %LET _CLIENTPROJECTPATH=;
    2581 %LET _CLIENTPROJECTNAME=;
    2582 %LET _SASPROGRAMFILE=;
    2583
    2584 ;*';*";*/;quit;

    NOTE: PROCEDURE DATASETS used (Total process time):
    real time 10:14.15
    cpu time 0.00 seconds

    any one please share your insight on this issue

    thanks in advance,
    Shanoj

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Do you run REORG and RUNSTAT periodically, don't you?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    my question would be why do you have user tables that you are constantly dropping? Are they created for each user as part of your application? I can't think of one good reason to be doing such.
    As mentioned before looks like you have some clean up to do within your system. Seems you may have problems with the catalog tables due to the amount of activity you have done thus far.

Posting Permissions

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