Return detailed status information on specified Open Server threads.
sp_ps [loginame | ’spid’]
The user’s login name.
The internal identification number of the thread to report on. You can obtain the spid from the output of a previous sp_who or sp_ps call. By default, all threads are listed.
1>execute utility...sp_ps
2>go
spid Login Name Host Name Program Name Task Type ...
---- ---------- --------- ------------ -------------- ...
1 SERVER TASK ...
2 SERVER TASK ...
3 SERVER TASK ...
4 SERVICE TASK ...
11 hiram SITE HANDLER TASK ...
14 bud sonoma isql CHILD TASK ...
... Status Sleep Event Sleep Label Current Command ...
... ------- ----------- ----------- --------------- ...
... runnable 369448 NETWORK HANDLER ...
... sleeping 369544 MSG AVAILABLE CONNECT HANDLER ...
... sleeping 369640 MSG AVAILABLE DEFERRED HANDLER ...
... runnable 0 SCHEDULER ...
... sleeping 369736 MSG AVAILABLE ...
... running 416480 ...
... Blocked Run Current Stack Net Net
... By Ticks Priority Origin Writes Reads
... ------- ------ -------- -------- ------ -----
... 0 0 8 2794336 0 0
... 0 0 8 2810792 0 0
... 0 0 8 2827184 0 0
... 0 0 15 2843576 0 0
... 0 0 8 2859968 2 7
... 0 0 8 2909208 3 0
This example shows isql output from the sp_ps procedure. For printing purposes, the report was split where indicated by ellipses.
sp_ps reports the detailed status of a specified server thread or all current Open Server threads. The information is useful for debugging during application development.
loginame and spid are character string parameters. When using isql to execute sp_ps as a remote procedure call from an Adaptive Server, surround the spid in quotes to avoid a syntax error.
If you do not specify loginame or spid, sp_ps lists all current threads.
Table 4-1 summarizes the information sp_ps returns:
Type of information |
Meaning |
|---|---|
spid |
The internal thread number of the thread. |
Login Name |
The name of the logged in user. Applies only to client threads. |
Host Name |
For a client task, this is the name of the client’s machine. For site handlers and server-to-server RPC connections, this is the name of the remote Adaptive Server. |
Program Name |
The name of the client application program. |
Task Type |
The type of thread. The legal values are NETWORK, CLIENT, SERVER, SITE HANDLER, CHILD, SERVICE, and UNKNOWN. |
Status |
The current status of the thread. The legal values for this column are running, runnable, sleeping, sick, free, stopped, spawned, terminal, and unknown. The one “running” task is the thread that is executing sp_ps. |
Sleep Event |
The event that will cause a sleeping thread to become runnable. |
Sleep Label |
A character string label that describes the sleep event. |
Current Command |
A character string that describes the state of the thread. The contents of this column are set by the srv_thread_props routine. |
Blocked By |
(Not currently used. |
Run Ticks |
(Not currently used. |
Current Priority |
The priority at which the thread is running. |
Stack Origin |
The address in memory where the thread’s stack begins. |
Net Writes |
The number of network writes since the thread started. This number applies only to site handler and client threads. |
Net Reads |
The number of network reads since the thread started. This number applies only to site handler and client threads. |
Table 4-2 summarizes the results returned as rows with these columns:
Column name |
Datatype |
Length |
|---|---|---|
spid |
CS_INT_TYPE |
4 |
Login Name |
CS_CHAR_TYPE |
SRV_MAXNAME |
Host Name |
CS_CHAR_TYPE |
SRV_MAXNAME |
Program Name |
CS_CHAR_TYPE |
SRV_MAXNAME |
Task Type |
CS_CHAR_TYPE |
SRV_MAXNAME |
Status |
CS_CHAR_TYPE |
SRV_MAXNAME |
Sleep Event |
CS_INT_TYPE |
4 |
Sleep Label |
CS_CHAR_TYPE |
SRV_MAXNAME |
Current Command |
CS_CHAR_TYPE |
SRV_MAXNAME |
Blocked By |
CS_INT_TYPE |
4 |
Run Ticks |
CS_INT_TYPE |
4 |
Current Priority |
CS_INT_TYPE |
4 |
Stack Origin |
CS_INT_TYPE |
4 |
Net Writes |
CS_INT_TYPE |
4 |
Net Reads |
CS_INT_TYPE |
4 |
