| Author |
Message
|
| MQSerious |
 Posted: Thu Feb 17, 2005 9:24 pm Post subject: [SOLVED]PROGRAM_ACT_INST: Tablespace Full |
 |
|
Acolyte
Joined: 29 Mar 2004
Posts: 68
Location: At Large
|
I have MQWF 3.5 SP 2 and DB2 8.1 on AIX.
I am unable to check-in workitems via Java API and get SQL-659 table PROGRAM_ACT_INST:
| Code: |
Error location = File=/projects/fmc/drv2/lbld/v350/aix/objopt/db2cs/fmcdbac1/fmcdbact.cxx, Line=5996,
Function=FmcDatabase::ProgramActInst::insert(const FMC_PROGRAM_ACT_INST &)
Error data = FmcSQLException, Sqlcode=-659, Sqlerrmc=, Sqlerrp=SQLRIISR, Sqlerrd[0]=-2080112630,
Sqlerrd[1]=10, Sqlerrd[2]=0, Sqlerrd[3]=0, Sqlerrd[4]=0, Sqlerrd[5]=0, Sqlwarn= , Sqlstate=5
4032 |
SQL-659 says:
| Code: |
Once a storage object has grown to its maximum size, it cannot
be extended further.
User Response:
To make existing space within the object available to store new
data, the following actions may be taken:
o Reorganize the table
o Delete unnecessary rows from the table
o Drop an index from the table
o Update rows to reduce the amount of data (a reorg may be
required after this action in order to reclaim unused
storage) |
I have tried to reorg as recommended with no success. Has anyone run into this before? Should I truncate the table or increase the pagesize? Thanks in advance for a rapid response.
Last edited by MQSerious on Tue Apr 12, 2005 11:23 am; edited 1 time in total |
|
| Back to top |
|
 |
| MQSerious |
| Posted: Thu Feb 17, 2005 9:26 pm Post subject: |
|
|
Acolyte
Joined: 29 Mar 2004
Posts: 68
Location: At Large
|
| I have about 1400 workitems and 380 notification items. |
|
| Back to top |
|
|
| MQSerious |
| Posted: Fri Feb 18, 2005 10:07 am Post subject: |
|
|
Acolyte
Joined: 29 Mar 2004
Posts: 68
Location: At Large
|
Anyone?
_________________
Regards,
MQSerious. |
|
| Back to top |
|
|
| koko |
| Posted: Fri Feb 18, 2005 10:08 am Post subject: |
|
|
Master
Joined: 26 Sep 2003
Posts: 206
|
I think you need to increase the table size here.
_________________
Thanks
Koko |
|
| Back to top |
|
|
| MQSerious |
| Posted: Fri Feb 18, 2005 10:15 am Post subject: |
|
|
Acolyte
Joined: 29 Mar 2004
Posts: 68
Location: At Large
|
How? SQL -659 states:
| Quote: |
| Once a storage object has grown to its maximum size, it cannot be extended further. |
_________________
Regards,
MQSerious. |
|
| Back to top |
|
|
| jmac |
| Posted: Fri Feb 18, 2005 10:21 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
I would open a PMR
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| MQSerious |
| Posted: Fri Feb 18, 2005 10:29 am Post subject: |
|
|
Acolyte
Joined: 29 Mar 2004
Posts: 68
Location: At Large
|
Jmac,
I have opened a PMR. Have you ever run into this situation before? The DBA says the table has 200 million rows!
_________________
Regards,
MQSerious. |
|
| Back to top |
|
|
| jmac |
| Posted: Fri Feb 18, 2005 10:46 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
| MQSerious wrote: |
| The DBA says the table has 200 million rows! |
That seems like an enormous number of rows. I don't really know that table at all but it seems like there would be 1 entry for each prgram activity.
Have you ever run the ProcessTemplateDeletionTool? This may free up some space in that table... I am not sure it will, but it is worth a shot. If you search the forum you should find the link to the tool.
GOOD LUCK
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| MQSerious |
| Posted: Fri Feb 18, 2005 10:52 am Post subject: |
|
|
Acolyte
Joined: 29 Mar 2004
Posts: 68
Location: At Large
|
Thanks, I will try running the tool.
_________________
Regards,
MQSerious. |
|
| Back to top |
|
|
| MQSerious |
| Posted: Tue Apr 12, 2005 11:27 am Post subject: |
|
|
Acolyte
Joined: 29 Mar 2004
Posts: 68
Location: At Large
|
| Seems like a block activity was causing all this. With each loop where the exit condition was not met, a record would be inserted into the activity instance table. Added a "wait" activity in the block to throttle down the loop interval thus drastically cutting down on the rate of activity instance creation. |
|
| Back to top |
|
|
| jmac |
| Posted: Tue Apr 12, 2005 11:38 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
Interesting.... did you do any analysis on the table?
It seems to me that what you are saying is that each time the block "looped" a new entry was added to the table. If that were the case, then I would assume that many of these "block" entries in the table would be in one of the "finished" states.
Just wondering.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| MQSerious |
| Posted: Tue Apr 12, 2005 12:00 pm Post subject: |
|
|
Acolyte
Joined: 29 Mar 2004
Posts: 68
Location: At Large
|
Yes, they were. I also installed SP 3 which fixes a problem with the cleanup server not really cleaning up everything. I had my cleanup server set to run between midnight and 6:00am.
ASIDE: Around the time this problem occured, the DBA had converted the MQWF DB to online backups. We had an accompanying problem with DB2 logs for MQWF ever expanding - I suspect that as the cleanup server ran trying to delete the millions of finished instances, DB2 was busy logging this act in its replay logs. |
|
| Back to top |
|
|
|
|
