How to adjust the table space size in DB2

I have encountered a similar problem. I have read a detailed article to share it with you and express my gratitude to the author of the original article. The content of the post is as follows:

 

How to adjust the table space size in DB2

After receiving a call from the customer, the program keeps reporting errors, and all services cannot be done. Copy a log of the Application Server (WebLogic). The log shows:

Warn: 16:24:32, 421: jdbcexceptionreporter [Line: 77}: SQL error:-99999, sqlstate: NULL
Error: 16:24:32, 422: jdbcexceptionreporter [Line: 78}: [IBM] [DB2] [JCC] [102] [10040] non-automatic batch processing failure. Although the batch processing has been submitted, at least one exception occurred to a member of the batch processing.
Use getnextexception () to retrieve exceptions of specific elements that have been processed in batches.
Warn: 16:24:32, 422: jdbcexceptionreporter [Line: 77}: SQL error:-289, sqlstate: 57011
Error: 16:24:32, 422: jdbcexceptionreporter [Line: 78}: Error for batch element #1: DB2 SQL error: sqlcode:-289, sqlstate: 57011, sqlerrmc: SMK
Warn: 16:24:32, 423: jdbcexceptionreporter [Line: 77}: SQL error:-99999, sqlstate: NULL
Error: 16:24:32, 423: jdbcexceptionreporter [Line: 78}: Error for batch element #2: [IBM] [DB2] [JCC] [T4] [1026] [11339] error check:
Warn: 16:24:32, 423: jdbcexceptionreporter [Line: 77}: SQL error:-289, sqlstate: 57011
Error: 16:24:32, 424: jdbcexceptionreporter [Line: 78}: Error for batch element #2: DB2 SQL error: sqlcode:-289, sqlstate: 57011, sqlerrmc: SMK
Error: 16:24:32, 424: abstractflushingeventlistener [Line: 301}: cocould not synchronize database state with Session
Org. hibernate. Exception. genericjdbcexception: cocould 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.exe cutebatch (abstractbatcher. Java: 253)
At org.hibernate.engine.actionqueue.exe cuteactions (actionqueue. Java: 235)
At org.hibernate.engine.actionqueue.exe cuteactions (actionqueue. Java: 139)
At org. hibernate. event. Def. abstractflushingeventlistener. extends mexecutions (abstractflushingeventlistener. Java: 298)
At org. hibernate. event. Def. defaultflusheventlistener. onflush (defaflusheventlistener. 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 $ proxy803.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.exe cute (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.exe cute (webappservletcontext. Java: 1890)
At weblogic. servlet. Internal. servletrequestimpl. Run (servletrequestimpl. Java: 1344)
At weblogic.work.executethread.exe cute (executethread. Java: 209)
At weblogic. Work. executethread. Run (executethread. Java: 181)
Caused:
Com. IBM. db2.jcc. B. Ni: [IBM] [DB2] [JCC] [102] [10040] faults in non-automatic batch processing. Although the batch processing has been submitted, at least one exception occurred to a member of the batch processing.
Use getnextexception () to retrieve exceptions of specific elements that have been processed in batches.
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.exe cutebatch (ih. Java: 1554)
At org. hibernate. JDBC. batchingbatcher. doexecutebatch (batchingbatcher. Java: 48)
At org.hibernate.jdbc.abstractbatcher.exe cutebatch (abstractbatcher. Java: 246)
... 39 more
Error: 16:24:32, 426: loghelper [Line: 174}: 100201000 | counterserviceservice. unknown exception in the control method of applysave. hibernate flushing: cocould 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] faults in non-automatic batch processing. Although the batch processing has been submitted, at least one exception occurred to a member of the batch processing.
Use getnextexception () to retrieve exceptions of specific elements that have been processed in batches .; Nested exception is com. IBM. db2.jcc. B. Ni: [IBM] [DB2] [JCC] [102] [10040] non-automatic batch processing failure. Although the batch processing has been submitted, at least one exception occurred to a member of the batch processing.
Use getnextexception () to retrieve exceptions of specific elements that have been processed in batches.

Pass
C:/Documents and Settings/Administrator> DB2? Sql289

Sql0289n cannot be in the tablespace "<tablespace Name>"
.
Explanation:
On one or more database partitions, one of the following conditions is true:

1. One of the containers allocated to this SMS tablespace has reached the maximum file size. This may be the cause of this error.
2. All containers allocated to this DMS tablespace are full. This may be the cause of this error.
3. A new balance is in progress, but the progress is not enough to use the new space.
4. Redirection recovery is being performed for small containers.
5. After the redirection is restored, all containers that are rolling forward and allocated to this tablespace are full.
6. The container is being rolled forward and the containers allocated to the tablespace are full.
7. Try to create a tablespace with less than five available extended data blocks.
8. Automatically resize the tablespace to its maximum size and all containers are full. Alternatively, there is not enough space between the current container size and the maximum size to expand or add, so it is impossible to automatically increase the space.
9 when creating an automatic storage tablespace, the average container space allocated value is used as the initial value. Therefore, a high value is used, but this value is greater than the specified maximum size.
10 if the DMS tablespace with automatic resizing enabled does not reach its maximum size, but one of the file systems in which the container is located is full, the container cannot increase.
11. The DMS tablespace with automatic resizing enabled does not reach its maximum size, and the file system where the tablespace is located is not full. However, container operations (or subsequent rebalancing) are in progress and the automatic resizing function is temporarily suspended until the operation is completed.

User response:
Perform the operation corresponding to the cause of the error:

1 switch to DMS
Tablespace, or recreate the 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. Add a new container to DMS
Tablespace, and try again when the rebalancing program makes the new page available
Try this operation.
3. Wait until the rebalancing program is completed.
4. Execute redirection recovery for large containers again.
5. Execute redirection recovery for large containers again.
6. Execute the rollback operation again to allow adding containers, or perform redirection recovery for large containers.
7. resubmit create tablespace
Statement to ensure that the tablespace has at least 5
Available extended data blocks.
8. Increase the maximum size of the tablespace.
9. Reduce the initial size of the tablespace or increase the maximum size of the tablespace.
10. Add the new container split set to the tablespace. The existing container will not increase any more. When the size is adjusted automatically, only the new containers in the last range of the tablespace will be extended.
11. Wait until the operation and subsequent rebalancing are completed.

Sqlcode:-289
Sqlstate: 57011

I'm sure the table space is full. I want to ask how to adjust the table space size. It's a little urgent. I don't have time to go online to check the information. Please give me some advice from the two teachers.

[B] Answer: [/B]

DB2's existing tablespace resizing Method
1) Example of directly adding a container:

DB2 "alter tablespace payroll add (device '/dev/rhdisk9' 10000 )"
After adding a container, DB2 will have an automatic balance process, which may last for several hours !!!

2) change the size of the existing container (this method does not trigger the balance, but if the tablespace is created on the bare device, the space of the bare device will be expanded ):

DB2 "alter tablespace ts1 resize (File '/conts/cont0' 2000, devic'/dev/rcont1 '2000, file 'cont2' 2000 )"

Note that this method is to change the size of the original container to 2000 pages.

DB2 "alter tablespace ts1 resize (all 2000 )"

This method is to change the size of all containers in the tablespace to 2000 pages.

DB2 "alter tablespace ts1 extend (File '/conts/cont0' 1000, devic'/dev/rcont1 '1000, file 'cont2' 1000 )"

This method expands the corresponding containers by 1000 pages, that is, by 1000 pages.

DB2 "alter tablespace data_ts extend (all 1000 )"

This method adds 1000 pages to all containers.
 

From: http://bbs.51cto.com/archiver/tid-593658.html