on 01-30-2015 4:58 AM
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 type | ConnHandle | Userid | LastReqTime | LowestIQCursorState | IQthreads | TempWorkspace | Nodeaddr | LastIdle(in hours) | cmd |
IDLE | 10 | xxx | 17:13.4 | None | 10 | 200 | 10.12.12.12 | 1 | select * from tmp |
INTENSIVE | 11 | yyy | 15:10:05 | fetching | 20 | 300 | 10.14.13.19 | NA | select * from sp_iqcontecx |
if there is any new method please help me
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
i am talking for sybase IQ16
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.