sybase iq dropping versioning connection problem

[Former Member]

Hi,

I am not able drop the connection which is causing versioning in sybase iq 15.3.

when i fire the command sp_iqtransaction the user is visible but when i fire sp_iqconnection the user is not visible.

In sp_iqtransaction the user state is committed.

I tired "drop connection connhandle" but it is showing invalid

How to drop this connection......Please help

Thanks in advance

Santosh Negalur

[tayeb_hadjou · Accepted solution]

Hi Santosh,

This could happen when a previous (older) transaction is still active.

You need to find out the oldest active transaction and see if its transaction time (TxnCreateTime) is older than the commited one (in your question):

select * from sp_iqtransaction() order by TxnCreateTime, State ;

To clear the version of that comitted transaction, previous active transaction should commit first.

I can easily reproduce the behavior :

1-

Time t1,  connection c1 : issue an update or insert

NOT commit (keep it active)

NOT disconnect

2-

10 seconds later ..

Time t2, connection c2 : issue another transaction

Commit

Disconnect c2 .

You will see the connection c2 (ConHandle) still there in sp_iqtransaction but not in sp_iqconnection.

That is expected (as per versionning logic).

Check if you are in this situation.

Hope this helps.

Regards,

Tayeb.

[Former Member · Accepted solution]

Hi Tayeb,

I am in this situation only.

Now i am able to drop that connection.

Thanks a lot for your suggestion.

Regards,

Santosh Negalur

[SybDBA · Accepted solution]

Hi Tayeb,

Greetings of the day !!

I have some doubt it the o/p of the sp_iqtransaction is

What exactly mean of these columns

MainTableKBCr,MainTableKBDr,TempTableKBCr,TempTableKBDr,TempWorkSpaceKB

(please don't refer docs which I already gone though )

I mean how much space get released once the transaction get committed ?

Thanks & regards

pankaj

[Former Member]

Thanks Mark.

[markmumy]

Santosh,

When this happens, please collect the following information and open an incident report/case:

-- select @@version

-- sp_iqcontext

-- sp_iqconnection

-- sp_iqtransaction

-- sp_iqstatus

-- copy of the IQ cfg file

-- pstack - need 3 pstack outputs at 1 minute intervals

Usually, a connection should drop.  When it doesn't, likely that thread is holding on some OS resource.  When we terminate the thread, the OS won't actually drop it because it is waiting on a resource.  The pstack data will help confirm where it is handing.

Mark