Long Transaction Aborted on a SELECT

[mungdrool]

Hey,

Has anyone else encountered this situation? I am trying to do a select with a subquery and get the error message "458: Long Transaction Aborted" The info I found for this message states that it occurs inside a transaction. What transaction could I be running when I'm doing only a select? The code I'm executing is this:
set isolation to dirty read;

select mov_dt, count(*)
from rtkxtract a
where EXISTS (select DISTINCT b.bas_con_upc_no, b.mov_dt from rtkxtract b
where b.bas_con_upc_no = a.bas_con_upc_no
and b.mov_dt = a.mov_dt - DATE(1)
and b.sur_cd = '0' )
and a.sur_cd != '0'
group by mov_dt
order by mov_dt

Essentially, I'm trying to find those items in my table that switched their sur_cd values from one day to the next.

Anyone have any ideas?

Thanks,
Jeff Tendam

 

[dodge20]

I agree with you, that you shouldn't get this error on a select statement. Anyway look at this thread, see if it helps you any.
Good Luck.

thread404-255216 Dodge20
If it ain't broke, don't fix it.

 

[mungdrool]

Thanks Dodge20.

I saw that thread before I posted this message. I don't think it's the same type of problem. I'm stumped. I get this message sometimes from other selects as well. If I just rerun them, they usually work. I have a feeling something's configured wrong on our database, but I don't have a clue what. It just started happening, so maybe something changed recently, that I'm not aware of.

Anyway, thanks again for the input.

Jeff Tendam

 

[vpshriyan]

Jeff:

I also have encountered a similar behaviour in our instance. The reason seems to be unknown and to me it looks like bug in Informix software. I have started a thread as follows some time back under Informix IDS Forum:

why is it a SELECT statement writing logical log ?
thread179-341394

Regards,
Shriyan

 

[mungdrool]

Thanks for the info. I guess I'm glad to know I'm not the only one experiencing this. We're using Version 5.08 here, so it seems like this problem has been around for a while. I guess there's no way around it though.

Thanks again for the help!

Jeff Tendam

 

[breizhti]

The logical logs are coming full with temporary table.
In fact, your request is building a temp table (this is logged) and then is droping it (this is logged also).
You can check what happen if you run a tblog during the
execution of your transaction.

You will need to rewrite your request.

 

[mungdrool]

Thanks for the info. It makes sense now that I know it's creating temp tables. If it's not creating the temp tables in the DBTEMPSPACE as it should, then logging would take place. I guess I need to talk to our DBAs about getting the temp tables created in DBTEMPSPACE rather than the root dbspace. I just assumed it was using the DBTEMPSPACE, but it's apparently not.

Thanks for the help,
Jeff Tendam