Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2012
    Posts
    177

    Unanswered: Facing issues with the db2 table

    Hi all,

    In our setup db2 V9.7 with fixpack 4

    Facing issues with issues like:
    =======================

    SQLSTATE = 40506: Native Error Code = -1,476: Msg = [IBM][CLI Driver][DB2/NT] SQL1476N The current transaction was rolled back because of error
    "-911". SQLSTATE=40506"

    How to fix this issue, i am not able to delete, insert, the records..



    Thanks,
    laxman..

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    rollback because of lockwait or deadlock
    limit the number of deletes / unit of work
    commit more often
    Diagnosing and resolving locking problems
    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
    Sep 2012
    Posts
    177
    Hi guy,

    Thanx for you kind reply..

    There is no lockwait, deadlock is happend for the table:
    ===================================
    db2pd -db TIS_DWHD -locks wait showlocks

    Database Partition 0 -- Database TIS_DWHD -- Active -- Up 19 days 03:53:58 -- Date 12/05/2012 21:19:58

    Locks:
    Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID



    Anyhow, people are trying to insert only 600 records over the table through Datastage?

    Delete option is also not happend in there jobs...

    How to insert the record?

    Thanks,
    laxman....

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    what is being used
    import commitcount ?
    load ?
    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

  5. #5
    Join Date
    Sep 2012
    Posts
    177
    Hi guy,

    People are using commit count for inserting the records.

    Thanks,
    laxman..

  6. #6
    Join Date
    Sep 2012
    Posts
    177
    Hi anybody give me the suggestion over this?

    Thanks,
    laxman..

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    this is a programming error..
    keep the luw as small as possible
    commit when possible
    release locks..
    change isolation level..
    many settings/operation can create this situation
    look in doc : how to handle locks.. deadlocks..
    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

  8. #8
    Join Date
    Feb 2012
    Posts
    23
    Are you able to execute a job that is giving you the rollback? Maybe if you transform this load into 600 single sql insert statements you can see which inserts complete successfully and find the one that does not. Maybe there is a faulty insert statement executed during the load but the error comming back from the load command is not giving you enough detail. Another option is to have a messages log file included in your import command which you can then review on some more details on the exact cause of the rollback.

Posting Permissions

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