Joins on Sysmaster database

I am trying to write an SQL query on the sysmaster database to identify what sessions there are and how many locks each has. When I join say the syssessions table with syssqlstats via the session id all is fine. When I introduce a third table say syssesprof joining by the session I don't get any records retrieved even though when I query all three tables separately there is equivalent data in all three tables. Joining any two of the three tables retrieves data but not when I introduce a third. Does anyone know why this is or has anyone else come across the problem? BTW I a running IDS 7.30 on Windows NT4. Thanks Shaun Campbell

I have also encountered this bug on 7.24 UC7 Ask Informix about Bug # 82434 Lyzander In article <3725483F.1392@emltd.co.uk>, Shaun Campbell <shaun@emltd.co.uk> wrote: > I am trying to write an SQL query on the sysmaster database to identify > what sessions there are and how many locks each has. > > When I join say the syssessions table with syssqlstats via the session id > all is fine. When I introduce a third table say syssesprof joining by > the session I don't get any records retrieved even though when I query > all three tables separately there is equivalent data in all three tables. > Joining any two of the three tables retrieves data but not when I > introduce a third. > > Does anyone know why this is or has anyone else come across the problem? > BTW I a running IDS 7.30 on Windows NT4. > > Thanks > > Shaun Campbell > > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Shaun Campbell wrote: > > I am trying to write an SQL query on the sysmaster database to identify > what sessions there are and how many locks each has. > > When I join say the syssessions table with syssqlstats via the session id > all is fine. When I introduce a third table say syssesprof joining by > the session I don't get any records retrieved even though when I query > all three tables separately there is equivalent data in all three tables. > Joining any two of the three tables retrieves data but not when I > introduce a third. > > Does anyone know why this is or has anyone else come across the problem? > BTW I a running IDS 7.30 on Windows NT4. One thing you might look into is the fact that both syssessions and syssesprof are views into the sysrstcb table. If you look at the definition of the latter all the 'columns' it returns after sid are the result of the SUM() aggregate function and depend on a GROUP BY sid clause. I cannot imagine how the engine could properly resolve that with the query in syssessions which only selects the primary thread for a session. I'd suggest a 4GL or ESQL/C program to implement your report which loops on syssessions records using a cursor and performs a query against syssesprof for that session in the loop or two parallel cursors and do your own nested loop join which will be more complex to code but faster to run. Art S. Kagel

Shaun Campbell wrote: > > I am trying to write an SQL query on the sysmaster database to identify > what sessions there are and how many locks each has. > > When I join say the syssessions table with syssqlstats via the session id > all is fine. When I introduce a third table say syssesprof joining by > the session I don't get any records retrieved even though when I query > all three tables separately there is equivalent data in all three tables. > Joining any two of the three tables retrieves data but not when I > introduce a third. Addendum to my post: I suppose a stored procedure to implement the nested loop or loop and query would not be difficult either! I just tend to think of ESQL/C and 4GL first. Art S. Kagel