cancel
Showing results for 
Search instead for 
Did you mean: 

Union of sp_iqconnection() ,sp_iqtransaction and sp_iqcontext()

Former Member
0 Kudos

Dear all,

     How can i union of three table sp_iqconnection() and sp_iqtransaction and sp_iqcontext()

i want output in below format(add one more column for IDLE and INTENSIVE query)

if query running more then 30 minutes means its an intensive query

if user is not doing anything past 30 minutes means its idle

i want output in below format

connectio typeConnHandle  Userid   LastReqTime   LowestIQCursorState  IQthreads TempWorkspace  Nodeaddr LastIdle(in hours)cmd
IDLE10xxx17:13.4None1020010.12.12.121select * from tmp
INTENSIVE11yyy15:10:05fetching2030010.14.13.19NAselect * from sp_iqcontecx

if there is any new method please help me

Accepted Solutions (0)

Answers (2)

Answers (2)

tayeb_hadjou
Advisor
Advisor
0 Kudos

Hi,

I guess you mean one view from the 3 system procs results.

You can write query with joins between the 3 system procs on ConnHandle like a tables and add new column "Connection Info" using  CASE/WHEN to specify IDLE or INTENSIVE depending on your criteria.

To query from the system proc results as a table or view, add parenthesis as below:

select .... from sp_iqconnection() ;

Regards,

Tayeb.

Former Member
0 Kudos

Hi Tayeb,

               Can you write complete this query ?? that could make me full understanding

because i am not able to add new column and map with IDLE and INTENSIVE

Hope you will write full query

former_member185199
Contributor
0 Kudos

You should have a basic understanding of how to write a query when you work in this aera and someone pointed you the direction to go ?

Former Member
0 Kudos

Hi Dirk

i got this task that is why i am posting this.

i tried a lot but not able to make it as i wish

Former Member
0 Kudos

Hi All,

         requesting to  Please do the needful on urgent basics as it is required urgently.

Regards

abhishek

0 Kudos

Hello Abhishek,

   What you are trying to do would not be feasible with current architecture on IQ (which is massively multi-threaded process using native threads), just using output from these three stored procs. You have to check srvlog (if SQL and other logging is turned ON), .iqmsg file for the connections. Lot of information will be changing like IQthreads, for a connection during the run of a query/SQL. Also "commit" will change information stored in memory for temp workspace etc.

   The idle and intensive query/connection analysis from these stored procs as per what you mentioned will probably give you incorrect information.

Regards

Shashi

Former Member
0 Kudos

Hello Shashi,

          it is possible that is why i got this task from my senior persons.

i want simple question if i am able to add new column like connection type (IDLE/INTENSIVE)

then i will make it possible.

so dear shashi

                    my question is how can i add new column ?

as i know there is a function from this we can get when user is sitting idle

Ex.

1.     datediff(mm,LastReqTime,getdate(*)) > 30

     it will show which user is sittng idle more then 30 minutes

     means is doing nothing ,now it will be treated as IDLE.

     or LowestIQCursorState parameter from sp_iqtransaction() table ,if IDLE means its IDLE

     above will come from sp_iqconnection() system table.

2.     LowestIQCursorState parameter from sp_iqtransaction() table

     we get if FETCHING means its INTENSIVE ,if IDLE means its IDLE

3. from sp_iqcontext() ,in cmd parameter

     we get get command of particular user

     if FETCHING will means its INTENSIVE

     if IDLE means NONE

now tell me how can i union/merge above three system tables

Hope you understand this.

     wait for your revert on this

Regards

abhishek

Former Member
0 Kudos

i am talking for sybase IQ16