Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Unanswered: SELECT from SYSIBMADM-Schema doesn't return

    Hi there,

    I hope you can help me with a problem I have regarding DB2.
    The database-user of one of our databases needs to perform a select on the table SYSIBMADM.SNAPTBSP_PART. However this is not working, at all.
    If I connect to the DB2-server with the instance-owner-user, then open up a connection to the DB2-Database and then try to select _anything_ from the table named above, db2 just doesn't do anything, the command never returns to me (I had it running for more than a hour). I don't get an error-message, I do not get something like "(0) rows returned", the command just hangs there and db2 stops responding alltogether (If I open up a second terminal and do a simple "db2 list db directory" while the select from above is running, nothing happens either). I have to kill the db2-commandline via a hard kill (i.e. kill -9), before I can connect to the db again.

    I tried to limit db2 to only fetch 2 rows, but that didn't help either. Selecting different tables is working though, selecting the same table on a different database is also no problem.
    What am I missing here? What could be the cause of this?

    I deerly hope you can help, because this is driving me crazy.


    Thanks in advance,
    Thomas


    This is what's happening on the command-line:
    db2 => connect to db01

    Database Connection Information

    Database server = DB2/AIX64 9.7.5
    SQL authorization ID = DB01IO
    Local database alias = DB01

    db2 => DESCRIBE TABLE SYSIBMADM.SNAPTBSP_PART

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    SNAPSHOT_TIMESTAMP SYSIBM TIMESTAMP 10 6 Yes
    TBSP_NAME SYSIBM VARCHAR 128 0 Yes
    TBSP_ID SYSIBM BIGINT 8 0 Yes
    TBSP_STATE SYSIBM VARCHAR 256 0 Yes
    TBSP_PREFETCH_SIZE SYSIBM BIGINT 8 0 Yes
    TBSP_NUM_QUIESCERS SYSIBM BIGINT 8 0 Yes
    TBSP_STATE_CHANGE_OBJECT_ID SYSIBM BIGINT 8 0 Yes
    TBSP_STATE_CHANGE_TBSP_ID SYSIBM BIGINT 8 0 Yes
    TBSP_MIN_RECOVERY_TIME SYSIBM TIMESTAMP 10 6 Yes
    TBSP_TOTAL_PAGES SYSIBM BIGINT 8 0 Yes
    TBSP_USABLE_PAGES SYSIBM BIGINT 8 0 Yes
    TBSP_USED_PAGES SYSIBM BIGINT 8 0 Yes
    TBSP_FREE_PAGES SYSIBM BIGINT 8 0 Yes
    TBSP_PENDING_FREE_PAGES SYSIBM BIGINT 8 0 Yes
    TBSP_PAGE_TOP SYSIBM BIGINT 8 0 Yes
    REBALANCER_MODE SYSIBM VARCHAR 30 0 Yes
    REBALANCER_EXTENTS_REMAINING SYSIBM BIGINT 8 0 Yes
    REBALANCER_EXTENTS_PROCESSED SYSIBM BIGINT 8 0 Yes
    REBALANCER_PRIORITY SYSIBM BIGINT 8 0 Yes
    REBALANCER_START_TIME SYSIBM TIMESTAMP 10 6 Yes
    REBALANCER_RESTART_TIME SYSIBM TIMESTAMP 10 6 Yes
    REBALANCER_LAST_EXTENT_MOVED SYSIBM BIGINT 8 0 Yes
    TBSP_NUM_RANGES SYSIBM BIGINT 8 0 Yes
    TBSP_NUM_CONTAINERS SYSIBM BIGINT 8 0 Yes
    TBSP_INITIAL_SIZE SYSIBM BIGINT 8 0 Yes
    TBSP_CURRENT_SIZE SYSIBM BIGINT 8 0 Yes
    TBSP_MAX_SIZE SYSIBM BIGINT 8 0 Yes
    TBSP_INCREASE_SIZE SYSIBM BIGINT 8 0 Yes
    TBSP_INCREASE_SIZE_PERCENT SYSIBM SMALLINT 2 0 Yes
    TBSP_LAST_RESIZE_TIME SYSIBM TIMESTAMP 10 6 Yes
    TBSP_LAST_RESIZE_FAILED SYSIBM SMALLINT 2 0 Yes
    TBSP_PATHS_DROPPED SYSIBM SMALLINT 2 0 Yes
    DBPARTITIONNUM SYSIBM SMALLINT 2 0 Yes

    33 record(s) selected.

    db2 => SELECT TBSP_NAME FROM SYSIBMADM.SNAPTBSP_PART FETCH FIRST 5 ROWS ONLY



    There it just stops.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have you recently installed fixpack ?
    re-executed all bind ? db2updvxx ?
    when hanging from other user db2 list applications --> get agentid
    db2 get snapshot for application agentid xxx
    check status - last executed command .....
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Who/What is the user DB01IO?

    What rights/privileges/authority does it have?

    Anything in db2diag.log?

    Is there any activity for the user under db2top?

    Andy

  4. #4
    Join Date
    Nov 2012
    Posts
    3
    Quote Originally Posted by ARWinner View Post
    Who/What is the user DB01IO?
    That would be the instance owner - for explanation:
    DB01IO = Database01InstanceOwner

    Is there any activity for the user under db2top?
    Andy
    Quote Originally Posted by przytula_guy View Post
    have you recently installed fixpack ?
    re-executed all bind ? db2updvxx ?
    when hanging from other user db2 list applications --> get agentid
    db2 get snapshot for application agentid xxx
    check status - last executed command .....
    Thank you for your answers - this helped me resolve the issue (I am fairly new to db2, so I wasn't aware of the db2top and the list applications/get snapshot). I was able to determine that the cause was some overly long SELECT statement which was interrupted at some point as it seems. This select hung there and apparantly blocked the communication. I was able to release the application and the selects are working now. I get the results I wanted
    Thank you so much - without your help I would've not found that out.


    Definetly will look into contributing here (as far as my knowledge goes ).

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by tbueter View Post
    [...]
    Thank you for your answers - this helped me resolve the issue (I am fairly new to db2, so I wasn't aware of the db2top and the list applications/get snapshot). I was able to determine that the cause was some overly long SELECT statement which was interrupted at some point as it seems. This select hung there and apparantly blocked the communication. I was able to release the application and the selects are working now. I get the results I wanted
    Thank you so much - without your help I would've not found that out.
    [....]
    What is the value of LOCKTIMEOUT for the database in question?

    ]$ db2 get db cfg for <db> | grep LOCKTIMEOUT

    If -1 there is no locktimeout so applications will wait forever. In most cases it is better to set an explicit value.
    --
    Lennart

  6. #6
    Join Date
    Nov 2012
    Posts
    3
    Quote Originally Posted by lelle12 View Post
    If -1 there is no locktimeout so applications will wait forever. In most cases it is better to set an explicit value.
    Indeed - there is no timeout set. Thanks for the hint, it probably makes sense to set the timeout, seeing the problems which can arise if there isn't one.

Posting Permissions

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