Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: DB2 Error : SQL1224N

    Hi,
    I have a DB2 procedure which process few million records. It executes inserts and updates on a table through dynamic SQL. Procedure gets executed for small data volumes. But when I execute large data volumes (close to a million or more), I get the following error.
    SQL1224N The database manager is not able to accept new requests, has terminated all requests in progress,or has terminated the specified request because of an error or a forced interrupt. SQLSTATE=55032 - FAILURE
    /

    Can anyone please suggest what changes can be done in the code and session level to avoid this error . Can we set a commit interval for the transactions done in the procedure..? Is it better execute table lock statement. Please suggest.

    Thanks,
    Tom

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you have to get the real error
    this message indicates, you have been forced for some other reason..
    maybe in diaglog some hints
    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
    Apr 2013
    Posts
    4
    Hi, Thanks for the reply. Just checked the db2 diaglog for the error.It looks like there are many locks on the table. Is it good to execute a table level lock statement, prior to the execution of INSERT/UPDATE/DELETE on the table ? Please suggest. The error is enclosed.


    LEVEL: Warning
    PID : 8902910 TID : 87705 PROC : db2sysc 0
    INSTANCE: phz7rtf2r NODE : 000 DB : RETLWD02
    APPHDL : 0-11333 APPID: *LOCAL.phz7rtf2r.567895072324
    AUTHID : hn35492
    EDUID : 87705 EDUNAME: db2agent (RETLWD02) 0
    FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:1
    MESSAGE : ADM5501I DB2 is performing lock escalation. The affected application
    is named "db2bp", and is associated with the workload name
    "SYSDEFAULTUSERWORKLOAD" and application ID
    "*LOCAL.phz7rtf2r.567895072324" at member "0". The total number of
    locks currently held is "24834", and the target number of locks to
    hold is "12417". The current statement being executed is "INSERT INTO xxxxxxxx "

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    to avoid lock escalation : you could lock the table in share .. while running a specific batch
    if limits are hit, agents could be forced..
    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
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    issuing a commit every Nth insert/update would be a better idea.

  6. #6
    Join Date
    Apr 2011
    Posts
    2
    WLM seems to be doing a good job here ...

Posting Permissions

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