Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    32

    Unanswered: DB2 Alter table does not do anything just hangs up help ?

    I am trying to alter a table by adding a new column to it .

    When the run the following command on the command line it just does not do anything and once i got this error The current transaction hs been rolled back because of a deadlock or timeout. SQL0911N

    Command: DB2 ALTER TABLE db2inst1.EMP_HC ADD SerialNoCountryNo VARCHAR(30)

    The table i am trying to add to
    Code:
    --<ScriptOptions statementTerminator=";"/>
    
    CREATE TABLE "DB2INST1"."EMP_HC" (
    		"ROW_ID" VARCHAR(50) NOT NULL, 
    		"EMP_NAME" VARCHAR(200), 
    		"EMP_TYPE" VARCHAR(50), 
    		"MANAGER" VARCHAR(200), 
    		"SERIAL_NUM" VARCHAR(20), 
    		"COUNTRY" VARCHAR(100), 
    		"REGION" VARCHAR(100), 
    		"GEO" VARCHAR(50), 
    		"CNTRY_TYPE" VARCHAR(50), 
    		"WORK_CITY" VARCHAR(100), 
    		"WORK_LOC" VARCHAR(150), 
    		"WORK_PLACE" VARCHAR(100), 
    		"MARKET" VARCHAR(20), 
    		"FUNC_VP" VARCHAR(200), 
    		"VICE_PRES" VARCHAR(200), 
    		"DIRECTOR" VARCHAR(200), 
    		"THIRD_LINE" VARCHAR(200), 
    		"SEC_LINE" VARCHAR(200), 
    		"DIRECT_REPORT" VARCHAR(200), 
    		"CNTRY_MGR" VARCHAR(200), 
    		"DEVT_EXECS" VARCHAR(50), 
    		"DEVT_ORGS" VARCHAR(150), 
    		"ORG_ID" VARCHAR(100), 
    		"FUNCTION" VARCHAR(20), 
    		"MAJOR" VARCHAR(20), 
    		"DEPT" VARCHAR(30), 
    		"IBM_START_DATE" DATE, 
    		"ROLE" VARCHAR(100), 
    		"SUB_ROLE" VARCHAR(100), 
    		"FUNDING" VARCHAR(100), 
    		"FUND_DOC_NUM" VARCHAR(100), 
    		"BILLABLE" VARCHAR(50), 
    		"DIVISION" VARCHAR(100), 
    		"UNIT" VARCHAR(100), 
    		"SEGMENT" VARCHAR(100), 
    		"FAMILY" VARCHAR(100), 
    		"DEV_TEAM" VARCHAR(100), 
    		"PROD_GROUP" VARCHAR(100), 
    		"PROD" VARCHAR(150), 
    		"PROJECT" VARCHAR(200), 
    		"PROJ_DESCR" VARCHAR(200), 
    		"DATERBAPPR" VARCHAR(150), 
    		"DATEVPFORHIRE" VARCHAR(150), 
    		"FIXEDPRICE" VARCHAR(50), 
    		"GOMPOSTDATE" VARCHAR(150), 
    		"GOMREFNO" VARCHAR(150), 
    		"HIRESTATUSRB" VARCHAR(150), 
    		"HIRESTATUSVP" VARCHAR(150), 
    		"PYDECHC" DOUBLE, 
    		"CYJANHC" DOUBLE, 
    		"CYFEBHC" DOUBLE, 
    		"CYMARHC" DOUBLE, 
    		"CYAPRHC" DOUBLE, 
    		"CYMAYHC" DOUBLE, 
    		"CYJUNHC" DOUBLE, 
    		"CYJULHC" DOUBLE, 
    		"CYAUGHC" DOUBLE, 
    		"CYSEPHC" DOUBLE, 
    		"CYOCTHC" DOUBLE, 
    		"CYNOVHC" DOUBLE, 
    		"CYDECHC" DOUBLE, 
    		"PYDECMT" VARCHAR(50), 
    		"CYJANMT" VARCHAR(50), 
    		"CYFEBMT" VARCHAR(50), 
    		"CYMARMT" VARCHAR(50), 
    		"CYAPRMT" VARCHAR(50), 
    		"CYMAYMT" VARCHAR(50), 
    		"CYJUNMT" VARCHAR(50), 
    		"CYJULMT" VARCHAR(50), 
    		"CYAUGMT" VARCHAR(50), 
    		"CYSEPMT" VARCHAR(50), 
    		"CYOCTMT" VARCHAR(50), 
    		"CYNOVMT" VARCHAR(50), 
    		"CYDECMT" VARCHAR(50), 
    		"PYDECMD" VARCHAR(100), 
    		"CYJANMD" VARCHAR(100), 
    		"CYFEBMD" VARCHAR(100), 
    		"CYMARMD" VARCHAR(100), 
    		"CYAPRMD" VARCHAR(100), 
    		"CYMAYMD" VARCHAR(100), 
    		"CYJUNMD" VARCHAR(100), 
    		"CYJULMD" VARCHAR(100), 
    		"CYAUGMD" VARCHAR(100), 
    		"CYSEPMD" VARCHAR(100), 
    		"CYOCTMD" VARCHAR(100), 
    		"CYNOVMD" VARCHAR(100), 
    		"CYDECMD" VARCHAR(100), 
    		"PYDECED" VARCHAR(20), 
    		"CYJANED" VARCHAR(20), 
    		"CYFEBED" VARCHAR(20), 
    		"CYMARED" VARCHAR(20), 
    		"CYAPRED" VARCHAR(20), 
    		"CYMAYED" VARCHAR(20), 
    		"CYJUNED" VARCHAR(20), 
    		"CYJULED" VARCHAR(20), 
    		"CYAUGED" VARCHAR(20), 
    		"CYSEPED" VARCHAR(20), 
    		"CYOCTED" VARCHAR(20), 
    		"CYNOVED" VARCHAR(20), 
    		"CYDECED" VARCHAR(20), 
    		"HRIWPYDECMT" VARCHAR(100), 
    		"HRIWCYJANMT" VARCHAR(100), 
    		"HRIWCYFEBMT" VARCHAR(100), 
    		"HRIWCYMARMT" VARCHAR(100), 
    		"HRIWCYAPRMT" VARCHAR(100), 
    		"HRIWCYMAYMT" VARCHAR(100), 
    		"HRIWCYJUNMT" VARCHAR(100), 
    		"HRIWCYJULMT" VARCHAR(100), 
    		"HRIWCYAUGMT" VARCHAR(100), 
    		"HRIWCYSEPMT" VARCHAR(100), 
    		"HRIWCYOCTMT" VARCHAR(100), 
    		"HRIWCYNOVMT" VARCHAR(100), 
    		"HRIWCYDECMT" VARCHAR(100), 
    		"HRIWPYDECHC" DOUBLE, 
    		"HRIWCYJANHC" DOUBLE, 
    		"HRIWCYFEBHC" DOUBLE, 
    		"HRIWCYMARHC" DOUBLE, 
    		"HRIWCYAPRHC" DOUBLE, 
    		"HRIWCYMAYHC" DOUBLE, 
    		"HRIWCYJUNHC" DOUBLE, 
    		"HRIWCYJULHC" DOUBLE, 
    		"HRIWCYAUGHC" DOUBLE, 
    		"HRIWCYSEPHC" DOUBLE, 
    		"HRIWCYOCTHC" DOUBLE, 
    		"HRIWCYNOVHC" DOUBLE, 
    		"HRIWCYDECHC" DOUBLE, 
    		"HRIWPYDECET" VARCHAR(20), 
    		"HRIWCYJANET" VARCHAR(20), 
    		"HRIWCYFEBET" VARCHAR(20), 
    		"HRIWCYMARET" VARCHAR(20), 
    		"HRIWCYAPRET" VARCHAR(20), 
    		"HRIWCYMAYET" VARCHAR(20), 
    		"HRIWCYJUNET" VARCHAR(20), 
    		"HRIWCYJULET" VARCHAR(20), 
    		"HRIWCYAUGET" VARCHAR(20), 
    		"HRIWCYSEPET" VARCHAR(20), 
    		"HRIWCYOCTET" VARCHAR(20), 
    		"HRIWCYNOVET" VARCHAR(20), 
    		"HRIWCYDECET" VARCHAR(20), 
    		"FUNCPYDEC" VARCHAR(10), 
    		"FUNCCYJAN" VARCHAR(10), 
    		"FUNCCYFEB" VARCHAR(10), 
    		"FUNCCYMAR" VARCHAR(10), 
    		"FUNCCYAPR" VARCHAR(10), 
    		"FUNCCYMAY" VARCHAR(10), 
    		"FUNCCYJUN" VARCHAR(10), 
    		"FUNCCYJUL" VARCHAR(10), 
    		"FUNCCYAUG" VARCHAR(10), 
    		"FUNCCYSEP" VARCHAR(10), 
    		"FUNCCYOCT" VARCHAR(10), 
    		"FUNCCYNOV" VARCHAR(10), 
    		"FUNCCYDEC" VARCHAR(10), 
    		"RLAPYDEC" VARCHAR(50), 
    		"RLACYJAN" VARCHAR(50), 
    		"RLACYFEB" VARCHAR(50), 
    		"RLACYMAR" VARCHAR(50), 
    		"RLACYAPR" VARCHAR(50), 
    		"RLACYMAY" VARCHAR(50), 
    		"RLACYJUN" VARCHAR(50), 
    		"RLACYJUL" VARCHAR(50), 
    		"RLACYAUG" VARCHAR(50), 
    		"RLACYSEP" VARCHAR(50), 
    		"RLACYOCT" VARCHAR(50), 
    		"RLACYNOV" VARCHAR(50), 
    		"RLACYDEC" VARCHAR(50), 
    		"RLDPYDEC" VARCHAR(50), 
    		"RLDCYJAN" VARCHAR(50), 
    		"RLDCYFEB" VARCHAR(50), 
    		"RLDCYMAR" VARCHAR(50), 
    		"RLDCYAPR" VARCHAR(50), 
    		"RLDCYMAY" VARCHAR(50), 
    		"RLDCYJUN" VARCHAR(50), 
    		"RLDCYJUL" VARCHAR(50), 
    		"RLDCYAUG" VARCHAR(50), 
    		"RLDCYSEP" VARCHAR(50), 
    		"RLDCYOCT" VARCHAR(50), 
    		"RLDCYNOV" VARCHAR(50), 
    		"RLDCYDEC" VARCHAR(50), 
    		"RLPYDEC" DOUBLE, 
    		"RLCYJAN" DOUBLE, 
    		"RLCYFEB" DOUBLE, 
    		"RLCYMAR" DOUBLE, 
    		"RLCYAPR" DOUBLE, 
    		"RLCYMAY" DOUBLE, 
    		"RLCYJUN" DOUBLE, 
    		"RLCYJUL" DOUBLE, 
    		"RLCYAUG" DOUBLE, 
    		"RLCYSEP" DOUBLE, 
    		"RLCYOCT" DOUBLE, 
    		"RLCYNOV" DOUBLE, 
    		"RLCYDEC" DOUBLE, 
    		"FINANCECODE" VARCHAR(100), 
    		"DIRECTKEY" VARCHAR(100), 
    		"BAND" VARCHAR(20), 
    		"BILLING" VARCHAR(100), 
    		"COUNTRYMAJORFINANCECODE" VARCHAR(100), 
    		"JOBCATEGORY" VARCHAR(100), 
    		"JOBCATEGORYPRIMARYNAME" VARCHAR(100), 
    		"RETAINID" VARCHAR(100), 
    		"SUBFUNCTION" VARCHAR(100), 
    		"SUBTYPE" VARCHAR(100), 
    		"LCPY12" DOUBLE, 
    		"LCCY01" DOUBLE, 
    		"LCCY02" DOUBLE, 
    		"LCCY03" DOUBLE, 
    		"LCCY04" DOUBLE, 
    		"LCCY05" DOUBLE, 
    		"LCCY06" DOUBLE, 
    		"LCCY07" DOUBLE, 
    		"LCCY08" DOUBLE, 
    		"LCCY09" DOUBLE, 
    		"LCCY10" DOUBLE, 
    		"LCCY11" DOUBLE, 
    		"LCCY12" DOUBLE, 
    		"LCMAJORPY12" VARCHAR(20), 
    		"LCMAJORCY01" VARCHAR(20), 
    		"LCMAJORCY02" VARCHAR(20), 
    		"LCMAJORCY03" VARCHAR(20), 
    		"LCMAJORCY04" VARCHAR(20), 
    		"LCMAJORCY05" VARCHAR(20), 
    		"LCMAJORCY06" VARCHAR(20), 
    		"LCMAJORCY07" VARCHAR(20), 
    		"LCMAJORCY08" VARCHAR(20), 
    		"LCMAJORCY09" VARCHAR(20), 
    		"LCMAJORCY10" VARCHAR(20), 
    		"LCMAJORCY11" VARCHAR(20), 
    		"LCMAJORCY12" VARCHAR(20), 
    		"LCDIVPY12" VARCHAR(20), 
    		"LCDIVCY01" VARCHAR(20), 
    		"LCDIVCY02" VARCHAR(20), 
    		"LCDIVCY03" VARCHAR(20), 
    		"LCDIVCY04" VARCHAR(20), 
    		"LCDIVCY05" VARCHAR(20), 
    		"LCDIVCY06" VARCHAR(20), 
    		"LCDIVCY07" VARCHAR(20), 
    		"LCDIVCY08" VARCHAR(20), 
    		"LCDIVCY09" VARCHAR(20), 
    		"LCDIVCY10" VARCHAR(20), 
    		"LCDIVCY11" VARCHAR(20), 
    		"LCDIVCY12" VARCHAR(20), 
    		"LCDEPTPY12" VARCHAR(20), 
    		"LCDEPTCY01" VARCHAR(20), 
    		"LCDEPTCY02" VARCHAR(20), 
    		"LCDEPTCY03" VARCHAR(20), 
    		"LCDEPTCY04" VARCHAR(20), 
    		"LCDEPTCY05" VARCHAR(20), 
    		"LCDEPTCY06" VARCHAR(20), 
    		"LCDEPTCY07" VARCHAR(20), 
    		"LCDEPTCY08" VARCHAR(20), 
    		"LCDEPTCY09" VARCHAR(20), 
    		"LCDEPTCY10" VARCHAR(20), 
    		"LCDEPTCY11" VARCHAR(20), 
    		"LCDEPTCY12" VARCHAR(20), 
    		"LCPROJPY12" VARCHAR(100), 
    		"LCPROJCY01" VARCHAR(100), 
    		"LCPROJCY02" VARCHAR(100), 
    		"LCPROJCY03" VARCHAR(100), 
    		"LCPROJCY04" VARCHAR(100), 
    		"LCPROJCY05" VARCHAR(100), 
    		"LCPROJCY06" VARCHAR(100), 
    		"LCPROJCY07" VARCHAR(100), 
    		"LCPROJCY08" VARCHAR(100), 
    		"LCPROJCY09" VARCHAR(100), 
    		"LCPROJCY10" VARCHAR(100), 
    		"LCPROJCY11" VARCHAR(100), 
    		"LCPROJCY12" VARCHAR(100), 
    		"LCMONTH" DOUBLE, 
    		"HRIWMONTH" DOUBLE, 
    		"NOTESID" VARCHAR(200), 
    		"FVPF" VARCHAR(150), 
    		"SKILLS" VARCHAR(200), 
    		"TAGS" VARCHAR(200), 
    		"DIVCY01" VARCHAR(100), 
    		"DIVCY02" VARCHAR(100), 
    		"DIVCY03" VARCHAR(100), 
    		"DIVCY04" VARCHAR(100), 
    		"DIVCY05" VARCHAR(100), 
    		"DIVCY06" VARCHAR(100), 
    		"DIVCY07" VARCHAR(100), 
    		"DIVCY08" VARCHAR(100), 
    		"DIVCY09" VARCHAR(100), 
    		"DIVCY10" VARCHAR(100), 
    		"DIVCY11" VARCHAR(100), 
    		"DIVCY12" VARCHAR(100), 
    		"HIRINGTICKETYEAR" VARCHAR(100), 
    		"HIRINGTICKETTYPE" VARCHAR(100), 
    		"HIRINGTICKETMARKET" VARCHAR(100), 
    		"HIRESTATUSPOSTING" VARCHAR(100), 
    		"HIRINGTICKETQ" VARCHAR(100)
    	)
    	DATA CAPTURE NONE 
    	IN "IMRMLARGE"
    	COMPRESS NO;

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Collect application and lock snapshots to check for incompatible locks

  3. #3
    Join Date
    Apr 2012
    Posts
    156
    Could be one of two things as the message says:
    If you suspect a true deadlock (this will return the error pretty quickly in most cases), ensure the db2detaileddeadlock event monitor is started and not full. Then determine who you are deadlocking with.

    If you suspect a normal locking issue (most likely for this case), then do the following:
    Open two sessions to the database
    Session A:
    Issue your ddl
    Session B:
    db2 list applications show detail <--Look for Lock-Wait
    Then do a snapshot on the one that is in lock wait and see who you are contending with, then force the user off that is causing you issues (if possible, depending on your shops standards).

Posting Permissions

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