Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
comp.databases.informix
+ new post
About this group
Subscribe to this group
This is a Usenet group - learn more
Current SQL from system tables
Options
There are currently too many topics in this group that display first. To make this topic appear first, remove this option from another topic.
There was an error processing your request. Please try again.
flag
   7 messages - Collapse all  -  Translate all to Translated (View all originals)
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
foredst...@gmail.com  
View profile  
 More options Oct 8 2012, 9:07 am
Newsgroups: comp.databases.informix
From: foredst...@gmail.com
Date: Mon, 8 Oct 2012 06:07:14 -0700 (PDT)
Local: Mon, Oct 8 2012 9:07 am
Subject: Current SQL from system tables
Print | Individual message | Show original | Report this message | Find messages by this author
Good day.

Please advice how to get Current SQL from system tables for particular session (like onstat shows)?

IDS 11.50.FC9W2X3


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Bartlomiej Lidke  
View profile  
 More options Oct 9 2012, 3:10 pm
Newsgroups: comp.databases.informix
From: Bartlomiej Lidke <onegrx.yv...@barg.rh.rot13.invalid>
Date: Tue, 9 Oct 2012 19:15:51 +0000 (UTC)
Local: Tues, Oct 9 2012 3:15 pm
Subject: Re: Current SQL from system tables
Print | Individual message | Show original | Report this message | Find messages by this author

foredst...@gmail.com wrote:
> Please advice how to get Current SQL from system tables for particular
> session (like onstat shows)?

if you don't want to enable sqltrace then please look at the syssqlcurses
inside sysmaster.sql file. comment sdb_iscurrent/odb_iscurrent conditions
to see statements for other sessions (taken from sysconblock.cbl_stmt)

be warned that this is not supported way

--
butthead


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
foredst...@gmail.com  
View profile  
 More options Oct 12 2012, 6:12 am
Newsgroups: comp.databases.informix
From: foredst...@gmail.com
Date: Fri, 12 Oct 2012 03:12:28 -0700 (PDT)
Local: Fri, Oct 12 2012 6:12 am
Subject: Re: Current SQL from system tables
Print | Individual message | Show original | Report this message | Find messages by this author
Thank you for response, but unfortunately the advice didn't help. The most often results are folowed. Output from onstat looks like:

Sess       SQL            Current            Iso Lock       SQL  ISAM F.E.
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain
60373      -              alr_test           DR  Not Wait   0    0    9.22  Off
60348      -              alr_test           CR  Wait 15    0    0    3.70  Off
60011      SELECT         alr_test           CR  Not Wait   0    0    9.20  Off
51176      SET OBJMODE    nis_etalon         CR  Not Wait   -525 -111 9.20  Off
46282      -              nodisca            DR  Not Wait   0    0    9.20  Off
42479      EXEC PROCEDURE emcs_klp_test      CR  Wait 120   0    0    9.28  Off
42478      SELECT         eds_test           CR  Wait 120   0    0    9.28  Off
42477      SELECT         alr_test           DR  Wait 120   0    0    9.28  Off
41407      EXEC PROCEDURE emcs_klp           CR  Wait 120   0    0    9.28  Off
41398      SELECT         emcs_klp           CR  Wait 120   0    0    9.28  Off
36360      EXEC PROCEDURE emcs_klp_test      CR  Wait 120   0    0    9.28  Off
36359      SELECT         eds_test           CR  Wait 120   0    0    9.28  Off
36358      SELECT         alr_test           DR  Wait 120   0    0    9.28  Off
34547      -              nodisca            CR  Not Wait   0    0    9.30  Off
34531      -              nodisca            CR  Not Wait   0    0    9.30  Off
25068      -              -                  -   Not Wait   0    0    9.28  Off
3231       SELECT         nodisca            CR  Not Wait   0    0    3.70  Off
3229       -              -                  -   Not Wait   0    0    3.70  Off
1840       SELECT         nodisca            CR  Wait 120   0    0    9.52  Off
1837       SELECT         nodisca            CR  Wait 120   0    0    9.52  Off
154        SELECT         sysmaster          CR  Not Wait   0    0    9.20  Off
78         SELECT         eds_ekspl          CR  Wait 120   0    0    9.28  Off
77         SELECT         eds                CR  Wait 120   0    0    9.28  Off
74         SELECT         nodisca            CR  Wait 120   0    0    9.28  Off
69         SELECT         alr_ekspl          CR  Wait 120   0    0    9.28  Off
66         SELECT         alr                CR  Wait 120   0    0    9.28  Off
63         SELECT         nodisca            CR  Wait 120   0    0    9.28  Off
61         SELECT         eds                CR  Wait 120   0    0    9.28  Off
60         SELECT         alr                CR  Wait 120   0    0    9.28  Off
54         SELECT         eds_ekspl          CR  Wait 120   0    0    9.28  Off
53         SELECT         alr_ekspl          CR  Wait 120   0    0    9.28  Off
51                        sysadmin           DR  Wait 5     0    0    -     Off
50                        sysadmin           DR  Wait 5     0    0    -     Off
47                        sysadmin           DR  Wait 5     0    0    -     Off

But query mentioned returns:

60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||

So, there is information about one session only and all rows contain empty cbl_stmt column...


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Bartlomiej Lidke  
View profile  
 More options Oct 18 2012, 5:32 pm
Newsgroups: comp.databases.informix
From: Bartlomiej Lidke <onegrx.yv...@barg.rh.rot13.invalid>
Date: Thu, 18 Oct 2012 21:37:29 +0000 (UTC)
Local: Thurs, Oct 18 2012 5:37 pm
Subject: Re: Current SQL from system tables
Print | Individual message | Show original | Report this message | Find messages by this author

foredst...@gmail.com wrote:
> But query mentioned returns:

> 60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
[...]
> So, there is information about one session only and all rows contain empty cbl_stmt column...

have you tried to select directly from sysconblock?

--
butthead


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Jason Harris  
View profile  
 More options Oct 19 2012, 3:32 am
Newsgroups: comp.databases.informix
From: Jason Harris <j...@jgharris.com>
Date: Fri, 19 Oct 2012 00:32:50 -0700 (PDT)
Local: Fri, Oct 19 2012 3:32 am
Subject: Re: Current SQL from system tables
Print | Individual message | Show original | Report this message | Find messages by this author
Hi,

Most of that info is in syssqlstat.

Why do you need it from sysmaster if it is already there in onstat output?

Good luck,

Jason


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
foredst...@gmail.com  
View profile  
 More options Oct 19 2012, 6:29 am
Newsgroups: comp.databases.informix
From: foredst...@gmail.com
Date: Fri, 19 Oct 2012 03:29:20 -0700 (PDT)
Local: Fri, Oct 19 2012 6:29 am
Subject: Re: Current SQL from system tables
Print | Individual message | Show original | Report this message | Find messages by this author
> have you tried to select directly from sysconblock?

Yes, I have tried, but without success. I can't understand such strange behaviour of this table. For instance, query without any conditions returns many rows with particular session id, but query with this particular session id in where clause doesn't return any rows at all. Can You be a little bit more detailed in Your advice?

 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Bartlomiej Lidke  
View profile  
 More options Oct 19 2012, 4:56 pm
Newsgroups: comp.databases.informix
From: Bartlomiej Lidke <onegrx.yv...@barg.rh.rot13.invalid>
Date: Fri, 19 Oct 2012 21:02:12 +0000 (UTC)
Local: Fri, Oct 19 2012 5:02 pm
Subject: Re: Current SQL from system tables
Print | Individual message | Show original | Report this message | Find messages by this author

foredst...@gmail.com wrote:
>> have you tried to select directly from sysconblock?
> Yes, I have tried, but without success. I can't understand such strange behaviour of this table.
> For instance, query without any conditions returns many rows with particular session id, but query
> with this particular session id in where clause doesn't return any rows at all. Can You be a little
> bit more detailed in Your advice?

as sysconblock is internal virtual table you may expect anything and nothing.
when I selected the other session in 'where' I was able to catch it when it
was running more complicated query:
(select count(*) from systables a, systables b, systables c) but I could not
catch it when it was running simple query (select count(*) from foo; -- foo
contains no data)

this was on 11.50uc9w1 and 11.70uc4

you can also try syssqlstat which works fine on 11.70 but is not usable on 11.50 because of
IC86488: SYSMASTER:SYSSQLSTAT SHOWS WRONG STATEMENT IN SQS_STATEMENT COLUMN
unfortunately the sqs_statement field length is only 200 but this table will give you last
statement (if the session is idle now in terms of sql activity) or currently running one

sqltrace will not give you the current but history/last statement

--
butthead


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
End of messages
« Back to Discussions « Newer topic     Older topic »

Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2013 Google