Long Running thread SYBASE IQ

[Former Member]

Hi to find the long running processes in SYBASE IQ ?. In sybase ASE , we have system table to find the long running queries (Master..syslogshold).

Do we have anything in IQ too ? . please share the queries to find the same 

[former_member197814 · Accepted solution]

Hi, Shan

Please, check following query could return the result what you want to see.

select

  IQT.Userid

  ,IQT.ConnHandle

  ,IQT.IQConnID

  ,IQX.ConnOrCurCreateTime

  ,IQT.TxnCreateTime

  ,IQT.[State]

  ,IQC.LowestIQCursorState

  ,IQX.CmdLine

from

  sp_iqconnection() IQC

  ,sp_iqtransaction() IQT

  ,sp_iqcontext() IQX

where

  IQT.ConnHandle = IQC.ConnHandle

  and IQT.ConnHandle = IQX.ConnHandle

  and IQT.[state] not in ('APPLIED', 'COMMITTED')

  and IQX.CmdLine <> 'NO COMMAND'

  and IQC.LowestIQCursorState not in ('FETCHING', 'END_OF_DATA')

order by

  IQT.TxnCreateTime

;

Best Regards,

BongSoo Jeon

[Former Member · Accepted solution]

Thanks BongSoo Jeon. Will use above query to find the long running queries . Can you please explain about iqtransaction states (COMMITTED, APPLIED , ROLLED_BACK only ).

These type of connection handles when it will get cleared from the IQ server ?.

[saroj_bagai · Accepted solution]

All transaction state have been defined in the docs:

sp_iqconnection:

sp_iqconnection Procedure - Reference: Building Blocks, Tables, and Procedures - SAP Library

sp_iqtransaction:

sp_iqtransaction Procedure - Reference: Building Blocks, Tables, and Procedures - SAP Library

Rolled_back: Transaction have been rolled back

[Former Member · Accepted solution]

Saroj,

Docs are very useful and thanks a lot . In many situations ,from sp_iqstatus output, we have noticed that  "Other Versions: close to 500 GB or 600 GB ".

During that time "ps" output from mpx iq server , gives me minimum 3 or 4 connections tells me usage of main space usage as same amount of other versions amount(ie 500 /600GB) . How to find the actual connhandle id which is holding the version space . ( All the connections will be in ACTIVE state too) .

Please let me know how to address this issue. 

number     pid        user/uid            login  last   main  temp  process/lwp

---------- ---------- ------------------- ------ ------ ----- ----- -----------

   1489447 0001705968 xxxxxxxxxxxxxx        00:10  06:38    500GB    0b -/0

    1489463 0001706012 xxxxxxxxxxxxxx        00:11  06:36   500GB    0b -/0

    1489464 0001706013 xxxxxxxxxxxxxx        00:11  06:36   500Gb   0b -/0

[markmumy · Accepted solution]

The isn't always one connection that is holding the versionspace.  If three transactions started to run queries then a load operation started, all 3 connections would hold the same version space.  That's why all your connections above report the same 500gb.  Killing one connection wouldn't free up the space.  You would need to drop all three to have it freed up.

Mark

[Former Member · Accepted solution]

thanks mark. Is there any way to find output version space for  each individual connection handles .

[markmumy · Accepted solution]

You can run sp_iqversionuse.  It will tell you, by connection how much space that connection is holding.  However, you can have 2 or more connections that are holding the same space.  Take the output above that you have.  There are three connections that are each holding 500 gb of space.  Likely, they are all holding the same version space so that if you drop one connection,the other two will still be holding the same space.  You would need to drop all three to have the version space freed.

Think of the version space almost as if it were shared.  If multiple connections started and run while version space is being increased, all connections have the ability to hold that space.  To free the space you must drop all connections that have the ability to hold that space.

Mark

[Former Member · Accepted solution]

Thanks for your reply mark. I got below error when i tired to execute the "sp_iqversionuse" command .

Msg 13424, Level 18, State 0:

SQL Anywhere Error -1090: Function 'hextoint' has invalid parameter '1' ('1226B3D05')

We are in 15.4 Version . and it's MPX node

[markmumy · Accepted solution]

I haven't seen this error from sp_iqversionuse.  Are you running this on the coordinator?

Mark

[Former Member · Accepted solution]

Yes mark. I am executing in writer node only

[markmumy · Accepted solution]

Writer or coordinator?  Those are two different server types in IQ.

If you are running this on a writer, try this on the coordinator.

Mark

[SybDBA · Accepted solution]

Hi

Try

Select * from sp_iqversionuse();

You will get the version info as suggested.

____

Regards

Pankaj