Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2012
    Posts
    8

    Red face Unanswered: Need to restart db2 after every 2 or 3 days

    Hi

    i am facing a problem in db2.
    Actually there is one application called TMS which runs on client side.
    The problem is that.
    in every 2 or 3 days it stopped working gives db2 sql exception.
    If we go to db2 and open table then it does not open it gives error.
    Then we restart db2 and then it works fine and so on.
    This application runs 24 hour and transaction happen every minute,so database is very large.
    So problem is that how we resolve this issue menas why problem comes in every 2 or 3 days.
    I am using wsad ibm application server and db2 8.1 as database
    Any help will be appreciate

    regards
    Amitabh

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Do not say "it stopped working gives DB2 sql exception" - tell us the SQLCODE or SQLSTATE and the message.

    You are running DB2 v8.1 - but which fixpack? and what operating-system name/version?

  3. #3
    Join Date
    Aug 2012
    Posts
    8
    Hi
    Thanks for reply
    this is the error
    com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -101, SQLSTATE: 54001, SQLERRMC: null

    Os is windows server 2003R2 service pack2

    regards
    Amitabh

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    That error -101 is "Statement is too long or complex" - and it is not a problem with DB2 - it is a problem with the application that sends this SQL.

    It is very unlikely that restarting the db2-server will make any difference - in other words, this error could happen when this SQL runs again. So you might have other symptoms.

    Get your DBA to find the text of the SQL-statement that causes this error, or get the application-support people to find it by examination of the log files at application layer. Then get the application supplier to configure the application not to generate that specific SQL statement or to work-around the problem in a different manner.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    -101 could also be due to running out of sortheap / [ address space on Windows which is 2GB by default ]. Check db2diag.log on the server side.

  6. #6
    Join Date
    Aug 2012
    Posts
    8

    Red face Need to restart db2 after every 2 or 3 days

    hi all

    i saw the db2diag.log file it is of 1.4GB so this large size could be the issue.
    if yes then what should i do.
    and can we overcome this issue by increasing STMTHEAP size in db2.
    Because it is 24X7 running application so logs get genrate very frequently so i can get exact query where the error.
    So is there any best way to solve this problem.I already told that there are many applicationrunning on this server on same db means there are 9 databases.So it is very painful for users because it stops working after 2 or 3 days.
    regards
    Amitabh

  7. #7
    Join Date
    Aug 2008
    Posts
    147
    As well as increasing the size of STMTHEAP - which deals with the SQL compiler work space sizes, follow the suggestion made earlier and identify the specific query . Is it complex?
    Also , are you maintaining the indexes, are staistics up to date?
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  8. #8
    Join Date
    Aug 2012
    Posts
    8

    Red face Need to restart db2 after every 2 or 3 days

    Hi
    thanks for reply

    me itself maintain this application.
    so can you please tell me how i find the exact query where the error.
    Regards
    Amitabh

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    My previous post should say stmtheap instead of sortheap.

    The failing statement can be dumped into the db2diag.log. Search for -101 in the db2diag.log and then check the entry following it. Rerun the sql from the command line to see if you can reproduce the error.

    If you can't identity the errors/query, rename your db2diag.log and wait for the error to reoccur. Then, attach your db2diag.log here.

  10. #10
    Join Date
    Aug 2012
    Posts
    8

    Red face Need to restart db2 after every 2 or 3 days

    hi

    now i have rename the db2diag.log file.
    Today error has come.Now one more thing when error comes then if we open table then it gives error
    which is


    [IBM][CLI Driver] SQL1221N The Application Support Layer heap
    cannot be allocated. SQLSTATE=57011

    regards
    Amitabh

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SQL1200 - SQL1299

    SQL1221N The Application Support Layer heap cannot be allocated.

    Explanation:
    The Application Support Layer heap could not be allocated. The cause of this error may be insufficient memory resources either for the database manager or the environment in which its operations are being attempted. Memory resources that can cause this error include:

    •The number of shared memory identifiers allocated in the system
    •The amount of paging or swapping space available in the system
    •The amount of physical memory available in the system
    User Response:
    One or more of the following:

    •Validate that sufficient memory resources are available to satisfy the database manager's requirements, and those of the other programs running on the system.
    •Reduce the aslheapsz configuration parameter.
    •Where appropriate, stop other programs using the system.
    sqlcode: -1221

    sqlstate: 57011

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I suspect you're hitting 2GB address space. Please attach db2diag.log.

    If you're not on the latest v8 fixpack (FP18), upgrade. Example of one APAR you might be hitting:
    IBM JR24363: EXCESSIVE MEMORY FRAGMENTATION IN DB2SYSCS ADDRESS SPACE ON WINDOWS CAUSES MEMORY ALLOCATION FAILURES - United States

    You mentioned 9 databases. Are they all running under the same instance?

  13. #13
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Depending on the amount of physical-RAM , and whether yours is a 32-bit implementation, there are two techniques available (with old versions of 32-bit db2 on Windows) to access RAM beyond 4GB and avoid the 2GB accessible address-space limit.


    One technique is the boot.ini switch /3GB.

    The other technique is AWE (using Windows AWE with DB2 to get larger bufferpools than is available in 32-bit Windows address space).

    It makes more commercial sense to move to a pure 64-bit environment, with a supported DB2 version than to mess around with hacks that prolong the life of 32-bit systems (but sometimes it's useful to have a life line)

    For the former:
    How to Set the /3GB Startup Switch in Windows

    For the latter:
    Refer to the DB2 v8.x infocenter (still online August 2008) for the DB2_AWE registry variable (since deprecated). Great care is necessary here. Professional tuning recommended.

  14. #14
    Join Date
    Aug 2012
    Posts
    8

    Need to restart db2 after every 2 or 3 days

    Hi
    yes all the databases are in same instance.
    the logs are 1.4GB of size.
    i recently rename the log file.So will attach when next time will got the error.
    Regards
    Amitabh

  15. #15
    Join Date
    Aug 2012
    Posts
    8

    Smile Need to restart db2 after every 2 or 3 days

    Hi all

    ever since i have renamed the diaglog file ,application is running without stopping.13 days have benn past for this.
    So could any one tell me this will also do some impact in this problem.
    If so then how?
    regards
    Amitabh

Posting Permissions

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