unable to select, delete the data and unable to drop the table, response says Long transaction detected

Hi, I am unable to delete the data and unable to drop the table as well. Every time it says Long transaction aborted. Please help on this ------------------------------ Suresh Goud B ------------------------------

Suresh: You do not have enough logical logs defined. Any transaction or combination of transactions that span more than the percent of logical log space documented in the LTXHWM parameter in your ONCONFIG file will trigger the oldest of the open transactions to roll back with a Long Transaction error. In addition, you appear to: either not have sufficient memory on the system to add more to the server to process concurrent requests, or you are using a memory limited "Edition" of Informix (such as the Innovator-C Edition which is limited to 2GB of memory or Developer Edition which is limited to 16GB) you have SHMTOTAL set to a positive value that is too low to allow additional memory to be allocated ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

The basic answer is that you probably need more logical logs. But only 2 rows in the table seems like you shouldn't need many logs. How many logical logs do you have (onstat -l | tail)? Does it work if you don't use "drop storage", which you won't need if you plan on dropping the table anyway? Does the table have any unusual data types? Is it TimeSeries related (I see "tsinstance" within the table name). The insufficient memory when selecting the data is odd. Does it work without the "limit 1"? What version of Informix are you using? Is there any sort of replication in use? ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Art, I have come across this issue, The table is filled with so much of data(log). When I am trying to delete/drop it says "Long transaction detected". even I am unable to select the same. I dont have idea about this table. I am thinking its timeseries log. Could you please help me to delete the data in it. So that it releases lot of space on the disk. My goal is to the release the memory. ------------------------------ Suresh Goud B ------------------------------ Suresh: You do not have enough logical logs defined. Any transaction or combination of transactions that span more than the percent of logical log space documented in the LTXHWM parameter in your ONCONFIG file will trigger the oldest of the open transactions to roll back with a Long Transaction error. In addition, you appear to: either not have sufficient memory on the system to add more to the server to process concurrent requests, or you are using a memory limited "Edition" of Informix (such as the Innovator-C Edition which is limited to 2GB of memory or Developer Edition which is limited to 16GB) you have SHMTOTAL set to a positive value that is too low to allow additional memory to be allocated ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Post your onstat -g seg, onstat -l, and onstat -d reports and we will try to help. Arr ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Here is the response of given commands ------------------------------ Suresh Goud B ------------------------------

OK, I see several serious problems here: You have allocated 100 add-on virtual segments sized from 3MB to 215MB (the engine doubles the size of each added segment every 16th segment. That is very likely slowing your server's processing! You need to increase your SHMVIRTSIZE from its current setting which looks like it is 105472 to 2304952 and set SHMADD to 230495. Then restart the server as soon as you can. Your total memory allocation is 6GB which is probably why the allocation of additional memory failed. I imagime this machine only has 8GB of total memory and Windows is using at least 2GB. You only have six logical logs each 13MB in size. The size is probably OK, but, as I said, you do not have enough logical logs to be able to complete the transaction you need to. I cannot guess how many logs you will need, but it is MANY more that the default of six. You can use onparams or the SQL API function "add log" to do that. All of your current logical logs reside in the root dbspace which is not a good thing. You have created a log dbspace, llog, but it is currently completely unused and does not contain any logical logs. I would create all of the new logs there then drop the original logs that were created in the root dbspace by the server's initialization procedure. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hello for your delete i think this may work, if possible Put database on nologging mode or change table mode to raw, delete, then put it in standard mode ------------------------------ John Smith ------------------------------

Usualy, changing database to non-logging is not possible in production environments. altering table mode to RAW can be a solution, but in a HDR cluster, then you should restore secondary from primary to get rid of original table in secondary. This is a typical business case for new feature I requested months ago: https://ibm-data-and-ai.ideas.ibm.com/ideas/INFX-I-421 ------------------------------ Vicente Salvador Cubedo ------------------------------