Active user/connection

[casorohi]

Hi guys
Please help me out on following:

How to check the active user at any point of time to DB

I am using the following is this correct ???

DEFINE VARIABLE icount AS INTEGER INITIAL 0 NO-UNDO.
FOR EACH holautos._Servers WHERE _Server-Num <> 0:
icount = icount + _Server-CurrUsers.
END.
Disp icount. ​

Second if I want to increase the number of user/connection what should I check before increasing the user (scaling the DB) any guide line/formula for DB DB performance as well resource utilization?

OS: Linux 64 bit
OE: 102B

Thanks

 

[rzr]

tried promon ??

 

[Rob Fitzpatrick]

How to check the active user at any point of time to DB

What does this mean? Do you want to know how many users are connected?

Looking at data in _Servers will only tell you about remote clients, not self-service clients.

Promon 1 | 1 (Users | All) or R&D | 1 | 4 (Status | Processes/Clients) will show you connected users. The latter will also break down local vs. remote, etc.

If you are interested in historical usage take a look at the license file (<dbname>.lic) in the DB directory. It tracks user counts every hour, min/max/current, for batch clients/interactive/all.
KB Article: "What is the database license file?" http://progresscustomersupport-surv...2010?retURL=/apex/KnowledgeSearch&popup=false

Without knowing anything about your system there's no way to make a recommendation.

 

[casorohi]

Hi
There are two things one is total number of user connected and active users.
I want to see how many user are active at any moment. Suppose I start the DB with -n 520 -Mn 101 -Ma 5 -Mpb 90 and -m3 -Ma 5 -Mi 1 -Mpb 10, which open the a connection to DB once DB started but I want to check how out above how many user/connection are active.
Thanks

 

[RealHeavyDude]

Probably there is a slight misunderstanding.

Your commands start the primary and a secondary login broker against the database with different server settings for both brokers. That does not open any connection. At this stage the two brokers are listening for connection requests from clients on their respective ports.

At this point you need to be clear as to what a connection means for you. Without any users connected you will already see other processes (like asynchronous page writers, before and after image writers or the watch dog) connected to the database. But these are not real users.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

You also need to define what you mean by "active".

Do you just mean "connected"? Or do you want to know if they are actually doing any work?

 

[casorohi]

I want to know out of total connected users how many of them are doing any work.
thanks

 

[MaximMonin]

FOR EACH _Connect NO-LOCK WHERE _Connect-Usr NE ?
AND
(_Connect-Type EQ "REMC" OR _Connect-Type EQ "SELF" or _Connect-Type EQ "0" )
BREAK BY _Connect-Usr:


IF _Connect-PID = ? THEN
NEXT.


IF _Connect-Type EQ "SELF" OR _Connect-Device NE "" THEN
ASSIGN cClientType = "4GL Client".
ELSE
ASSIGN cClientType = "SQL-92 Client".


FIND FIRST users WHERE users.sys-name = _Connect-Name NO-LOCK NO-ERROR.
IF AVAILABLE users THEN
DO:
user-name = users.Name.
rid-user = users.rid-user.
END.
ELSE
user-name = _Connect-Name.


CREATE work-users1.
ASSIGN
work-users1.logininfo = string (_Connect-time)
work-users1.tty-name = _Connect-Device
work-users1.user-name = user-name
work-users1.rid-user = rid-user
work-users1.pid = _Connect-PID
work-users1.id-proc = _Connect-Usr.
END.

to check activity you have to scan finger utility output by PID or check _Trans system table

 

[Rob Fitzpatrick]

IF _Connect-Type EQ "SELF" OR _Connect-Device NE "" THEN
ASSIGN cClientType = "4GL Client".
ELSE
ASSIGN cClientType = "SQL-92 Client".

FIND FIRST users WHERE users.sys-name = _Connect-Name NO-LOCK NO-ERROR.

CREATE work-users1.
ASSIGN
work-users1.logininfo = string (_Connect-time)
work-users1.tty-name = _Connect-Device
work-users1.user-name = user-name
work-users1.rid-user = rid-user
work-users1.pid = _Connect-PID
work-users1.id-proc = _Connect-Usr.
END.

Should I assume this code is excerpted from an application where these tables and variables were previously defined?

 

[Rob Fitzpatrick]

I want to know out of total connected users how many of them are doing any work.
thanks

If you want to be able to query for it, you need to be able to define client "work". If I am in a transaction, is that "work"? What if I am just querying records, and not updating or locking them? If I do that and then sit and look at those records for a while, how long is it before I transition from working to not working? I guess the larger question here is: what are you trying to measure, or what problem are you trying to solve?

 

[casorohi]

Want to check how many user are conneted to DB and how many of them are acually doing some thing (active).

 

[TomBascom]

Read Rob's questions carefully.

Basically you need to figure out what you mean by "active". This is a non-trivial question. At any given instant when you look the answer is very likely to be "0". But if you want to know "who has done something within the last X seconds" the answer will be more meaningful.

Then you need to determine which "somethings" are considered to be valid. Transactions? Queries? Keystrokes? For database oriented metrics like transactions and queries you can look at VST information on a per connection basis, record values periodically and see if they have changed within whatever period you define as X above. Those users are "active" within that definition. But there may be non-database activities that you want to consider valid, like keystrokes or temp-table interactions or processing that occurs in related sessions (like on app servers) that you cannot account for so directly.

That is why knowing why you want this information or "what problem are you trying to solve?" is important.

It is not that we are trying to be difficult about it -- but this is not the easy sort of question that it superficially sounds like. It is actually quite complicated.

 

[casorohi]

Thanks Tom
There are going be increase in the traffic so I am monitoring the traffic and what I found there are always so many users are connected when there is very minimum activity is on DB. Not sure that all of them are doing anything or only few, so if I get the amount of user that are actually active at any time it’ll easy and more accurately we can predict the traffic increase effect.
Thanks

 

[Rob Fitzpatrick]

There are going be increase in the traffic

I am monitoring the traffic

more accurately we can predict the traffic increase effect

I think we're getting closer to the requirement. It sounds to me now like the core concern is around the network rather than the database. If this is the case you may want to worry less about VSTs and more about bandwidth and protocol analysis.

If you are concerned about network performance then it is relevant to discuss where it is an issue or potential issue, e.g. a user complaining about slowness or timeouts in application X, as opposed to saturation or other problems on the LAN segment of the server for application X (or Y). In other words, look at the traffic close to the locality of the perceived issue. Then compare your measurements to your stored baseline measurements (because you already have those, right? ;p).

On the other hand, if you feel this is application-related, it may be relevant to tell us something about that. It sounds like this is a fat client environment. What is your basic topology? Is it remote ABL clients talking to a single server? Their application usage matters. If they are data entry clerks then their network traffic contribution presumably will be more or less invariant with database growth. Whereas if they are running large queries and pulling back result sets to be sorted or joined on the client side, then they could cause changes in traffic patterns as your data grows.

Or is the application distributed, with other boxes running AppServers or WebSpeed transaction servers? And what kind of traffic is contributed by your SQL clients?

As Tom said, I'm not trying to be difficult. I am trying to help you arrive at a crisp, detailed definition of your problem or requirement. Honestly, "I am monitoring the traffic" and "predict the traffic increase effect" are pretty vague. More to the point, they are descriptions of investigative actions that you are taking or plan to take in response to the problems. They are not the problems or requirements themselves.