Topic
  • 1 reply
  • Latest Post - ‏2014-01-17T18:04:22Z by G.Paulus
deepaksamal
deepaksamal
71 Posts

Pinned topic Table Lock Level

‏2014-01-16T16:07:47Z |

Hi,

i want to know how to find out if the table has been set to row level locking or page level locking??

how to change a table lock level from row level to page level OR from page level to Row level .. 

Can i set the database to have row level locking for all the table in it or i have to do it table-table ?

 

Thanks, 

Deepak Samal

  • G.Paulus
    G.Paulus
    11 Posts

    Re: Table Lock Level

    ‏2014-01-17T18:04:22Z  

    Hi Deepak Samal,

     

    DB2 LUW does not have a page level locking like DB2 z/OS has. DB2 LUW has block insert, row and table level locking. There is no option to set a default lock level for the entire database. The default lock level is row level locking. I do not konw any usefull situation/workload to set a database per default from row to a higher lock level. Why do you want to be able to set a higher leven than row by default for the entire database?

    You can alter a table to use i.e. block insert, row or table level locking using:

    ALTER TABLE ... LOCKSIZE ROW / TABLE .. http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html

     

    You can query the column LOCKSIZE in the catalog view SYSCAT.TABLES to determine the current defined locksize for a table.

    db2 "select locksize from syscat.tables where tabname='TABLE1'"

    LOCKSIZE
    --------
    R       

      1 record(s) selected.

     

    I = Block Insert (only used for MDC tables)

    R = Row Level

    T = Table Level

     

    Hope this helps and answers your question :)

     

    Best regards,

    Gerhard Paulus

    Updated on 2014-01-17T18:17:27Z at 2014-01-17T18:17:27Z by G.Paulus