DB2 How to adjust table space size

DB2 How to adjust table space size

Just received a call from the customer, the program has been an error, all the business can not be done, copied a copy of the application Server (WebLogic) log, the log shows:

Warn:2009-06-18 16:24:32,421:jdbcexceptionreporter[line:77}: SQL Error: -99999, Sqlstate:null
Error:2009-06-18 16:24:32,422:jdbcexceptionreporter[line:78}: [ibm][db2][jcc][102][10040] non-automatic batch fails. Although a batch has been submitted, at least one member of the batch has an exception.
Use Getnextexception () to retrieve the exception for a specific element that has been batched.
Warn:2009-06-18 16:24:32,422:jdbcexceptionreporter[line:77}: SQL Error: -289, sqlstate:57011
Error:2009-06-18 16:24:32,422:jdbcexceptionreporter[line:78}: Error for batch element #1: DB2 SQL error:sqlcode:-289, sqlstate:57011, SQLERRMC:SMK
Warn:2009-06-18 16:24:32,423:jdbcexceptionreporter[line:77}: SQL Error: -99999, Sqlstate:null
Error:2009-06-18 16:24:32,423:jdbcexceptionreporter[line:78}: Error for batch element #2: [ibm][db2][jcc][t4][1026][ 11339] Error Checking:
Warn:2009-06-18 16:24:32,423:jdbcexceptionreporter[line:77}: SQL Error: -289, sqlstate:57011
Error:2009-06-18 16:24:32,424:jdbcexceptionreporter[line:78}: Error for batch element #2: DB2 SQL error:sqlcode:-289, sqlstate:57011, SQLERRMC:SMK
Error:2009-06-18 16:24:32,424:abstractflushingeventlistener[line:301}: Could not synchronize database state with Session
Org.hibernate.exception.GenericJDBCException:Could not execute JDBC batch update
At Org.hibernate.exception.SQLStateConverter.handledNonSpecificException (sqlstateconverter.java:103)
At Org.hibernate.exception.SQLStateConverter.convert (sqlstateconverter.java:91)
At Org.hibernate.exception.JDBCExceptionHelper.convert (jdbcexceptionhelper.java:43)
At Org.hibernate.jdbc.AbstractBatcher.executeBatch (abstractbatcher.java:253)
At Org.hibernate.engine.ActionQueue.executeActions (actionqueue.java:235)
At Org.hibernate.engine.ActionQueue.executeActions (actionqueue.java:139)
At Org.hibernate.event.def.AbstractFlushingEventListener.performExecutions (Abstractflushingeventlistener.java : 298)
At Org.hibernate.event.def.DefaultFlushEventListener.onFlush (defaultflusheventlistener.java:27)
At Org.hibernate.impl.SessionImpl.flush (sessionimpl.java:1000)
At Org.hibernate.impl.SessionImpl.managedFlush (sessionimpl.java:338)
At Org.hibernate.transaction.JDBCTransaction.commit (jdbctransaction.java:106)
At Org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit (hibernatetransactionmanager.java:575 )
At Org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit ( abstractplatformtransactionmanager.java:662)
At Org.springframework.transaction.support.AbstractPlatformTransactionManager.commit ( abstractplatformtransactionmanager.java:632)
At Org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning ( transactionaspectsupport.java:314)
At Org.springframework.transaction.interceptor.TransactionInterceptor.invoke (transactioninterceptor.java:117)
At Org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (reflectivemethodinvocation.java:166)
At Org.springframework.aop.framework.JdkDynamicAopProxy.invoke (jdkdynamicaopproxy.java:204)
At $Proxy 803.saveApply (Unknown Source)
At Com.cnnct.mp.counterservice.service.CounterServiceService.applySave (counterserviceservice.java:135)
At Com.cnnct.mp.counterservice.ejbAction.ApplySaveEjbAction.perform (applysaveejbaction.java:32)
At Com.lbs.sieaf.webcontroller.RequestProcessor.processRequest (requestprocessor.java:245)
At Com.lbs.sieaf.webcontroller.MainServlet.doProcess (mainservlet.java:65)
At Com.lbs.sieaf.webcontroller.MainServlet.doPost (mainservlet.java:49)
At Javax.servlet.http.HttpServlet.service (httpservlet.java:763)
At Javax.servlet.http.HttpServlet.service (httpservlet.java:856)
At Weblogic.servlet.internal.stubsecurityhelper$servletserviceaction.run (stubsecurityhelper.java:225)
At Weblogic.servlet.internal.StubSecurityHelper.invokeServlet (stubsecurityhelper.java:127)
At Weblogic.servlet.internal.ServletStubImpl.execute (servletstubimpl.java:283)
At Weblogic.servlet.internal.TailFilter.doFilter (tailfilter.java:26)
At Weblogic.servlet.internal.FilterChainImpl.doFilter (filterchainimpl.java:42)
At Com.lbs.sieaf.webcontroller.CSSaftyFilter.doFilter (cssaftyfilter.java:167)
At Weblogic.servlet.internal.FilterChainImpl.doFilter (filterchainimpl.java:42)
At Com.cnnct.cp.webcontroller.EncodingFilter.doFilter (encodingfilter.java:66)
At Weblogic.servlet.internal.FilterChainImpl.doFilter (filterchainimpl.java:42)
At Weblogic.servlet.internal.webappservletcontext$servletinvocationaction.run (webappservletcontext.java:3212)
At Weblogic.security.acl.internal.AuthenticatedSubject.doAs (authenticatedsubject.java:321)
At Weblogic.security.service.SecurityManager.runAs (securitymanager.java:121)
At Weblogic.servlet.internal.WebAppServletContext.securedExecute (webappservletcontext.java:1983)
At Weblogic.servlet.internal.WebAppServletContext.execute (webappservletcontext.java:1890)
At Weblogic.servlet.internal.ServletRequestImpl.run (servletrequestimpl.java:1344)
At Weblogic.work.ExecuteThread.execute (executethread.java:209)
At Weblogic.work.ExecuteThread.run (executethread.java:181)
Caused by:
Com.ibm.db2.jcc.b.ni: [ibm][db2][jcc][102][10040] Non-automatic batch failed. Although a batch has been submitted, at least one member of the batch has an exception.
Use Getnextexception () to retrieve the exception for a specific element that has been batched.
At Com.ibm.db2.jcc.b.p.a (p.java:414)
At COM.IBM.DB2.JCC.B.IH.A (ih.java:2876)
At Com.ibm.db2.jcc.b.ih.b (ih.java:2710)
At Com.ibm.db2.jcc.b.ih.executebatch (ih.java:1554)
At Org.hibernate.jdbc.BatchingBatcher.doExecuteBatch (batchingbatcher.java:48)
At Org.hibernate.jdbc.AbstractBatcher.executeBatch (abstractbatcher.java:246)
... More
Error:2009-06-18 16:24:32,426:loghelper[line:174}: 100201000| Unknown exception error occurred in Counterserviceservice.applysave Master control method Hibernate flushing:could not execute JDBC batch update; Uncategorized SQLException for SQL [insert into SMK. Mp_yearcardphoto (Certnumber, PHOTO, Phototype, Usemake, CLIENTID, Autono) VALUES (?,?,?,?,?,?)]; SQL state [NULL]; Error code [-99999]; [IBM] [DB2] [JCC] [102] [10040] Non-automatic batch fails. Although a batch has been submitted, at least one member of the batch has an exception.
Use Getnextexception () to retrieve the exception of a specific element that has been batched.; Nested exception is com.ibm.db2.jcc.b.ni: [ibm][db2][jcc][102][10040] non-automatic batch fails. Although a batch has been submitted, at least one member of the batch has an exception.
Use Getnextexception () to retrieve the exception for a specific element that has been batched.

Pass
C:\Documents and SETTINGS\ADMINISTRATOR>DB2? sql289

sql0289n failed to table space ' < tablespace name > '
To assign a new page in the
Explain:
On one or more database partitions, one of the following conditions is true:

1 assigned to this SMS table space, one of the containers has reached the maximum file size. This may be the cause of the error.
2 All containers allocated to this DMS tablespace are full. This may be the cause of the error.
3 is being rebalanced, but its progress is not enough to be able to use the newly added space.
4 redirect Recovery for too small containers.
5 All containers that are being rolled forward and allocated to this tablespace are full after a redirect is restored.
6 Skipping the Add container roll forward and all containers allocated to this tablespace are full.
7 try to create a tablespace with less than 5 extents of available extension data.
8 The automatic sizing table space has reached its maximum size and all containers are full. Alternatively, there is not enough space between the container's current size and the maximum size to expand or add, so it is not possible to automatically increase the space.
9 When you create an automatic memory table space, you use a value that does not result in the average allocation of container space as the initial value. Therefore, a higher value is used, but this value is greater than the specified maximum size.
10 The DMS table space with auto-sizing enabled does not reach its maximum size, but one of the file systems where the container resides is full and the container cannot grow.
11 The DMS table space with automatic resizing enabled does not reach its maximum size, and the file system where the tablespace resides is not full. However, the container operation (or subsequent rebalancing) is in progress and the auto-sizing feature is suspended until the operation is complete.

User response:
Perform the action that corresponds to the cause of the error:

1 Switching to DMS
Tablespace, or re-create SMS with more directories (PATH)
Tablespace, to make:
(Number of directories) >= (maximum table size/maximum file size). Note that the maximum file size is related to the operating system.
2 Adding a new container to the DMS
Table space, and when the rebalance program makes the new page available, try again
Try the operation.
3 Wait for the rebalance program to complete.
4 redirect recovery to the larger container again.
5 redirect recovery to the larger container again.
6 Execute again to allow the container to be rolled forward, or to perform a redirection restore of the larger container.
7 Resubmit CREATE tablespace
Statement to ensure that the tablespace has at least 5
Extension data blocks that are available.
8 increase the maximum size of the table space.
9 reduce the initial size of the tablespace or increase the maximum size of the table space.
10 adds a new container split set to the tablespace. Existing containers no longer grow, and when automatically resized, only those new containers in the last range of the table space are expanded.
11 wait for the operation and subsequent rebalancing is complete.

Sqlcode:-289
sqlstate:57011

Sure is the table space is full, I would like to ask how to adjust the size of the table space, a little urgent, no time to check the information on the Internet, trouble two teachers pointing

Answer:

DB2 Existing table Space expansion method
1) Example of adding a container directly:

DB2 "ALTER tablespace PAYROLL ADD (DEVICE '/DEV/RHDISK9 ' 10000)"
After adding the container DB2 will have an automatic balance process that may last for several hours!!!

2) Change the size of the existing container (this method does not trigger balance, but if the table space is built on a bare device, the bare device space is to be flushed):

DB2 "ALTER tablespace TS1 RESIZE (file '/conts/cont0 ', DEVICE '/dev/rcont1 ', file ' Cont2 ' 2000)"

Note that this way is to change the original corresponding container to size is 2000 pages

DB2 "ALTER tablespace TS1 RESIZE (all 2000)"

In this way, all the container sizes in the table space are changed to 2000 pages.

DB2 "ALTER tablespace TS1 EXTEND (file '/conts/cont0 ', DEVICE '/dev/rcont1 ', file ' cont2 ' 1000)"

This approach is to expand the corresponding container to 1000 pages, that is, add 1000 pages.

DB2 "ALTER tablespace data_ts EXTEND (all 1000)"

This means adding 1000 pages to all the containers.

Increase table space to
1, the increase is the bare equipment
ALTER tablespace TS1
ADD (DEVICE '/DEV/RHDISK9 ' 10000)
2, added is the file system
ALTER tablespace TS1
ADD (FILE '/ADSASDF/AASDFASD ' 10000)
3. Delete a container
ALTER tablespace TS1
Drop (FILE '/adsasdf/aasdfasd ', DEVICE '/DEV/RHDISK9 ')

DB2 How to adjust table space size