Chapter 4: Executing Remote Procedure Calls

sp_ps

Description

Returns detailed status information on specified Open Server threads.

Syntax

server_name... sp_ps [loginame | ’spid’]

Parameters

loginame: is the user’s login name.

spid: is 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.

Examples

Example 1

1>DCSERVER...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.

Usage

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.

The following table summarizes the information sp_ps returns:

**Table 4-2: Information returned (sp_ps)**
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.

The following table summarizes the results returned as rows with these columns:

**Table 4-3: Format of information returned (sp_ps)**
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