DB2 Server for VSE & VM: Database Administration

Appendix C. Querying the Status of an Application (VM Only)

SQLQRY is implemented as a CMS immediate command and enables you to query the status of the application that you are currently running on the user machine. It is initialized by the DB2 Server for VM resource adapter when the first database statement is processed, and can be issued while your application is running. Since it is implemented as a CMS immediate command, it can be used even when your application is not accepting other input. See the VM/ESA: CMS User's Guide manual for more information about CMS immediate commands.

The SQLQRY command is particularly useful if problems arise while you are switching between application servers. In these cases, use SQLQRY to determine the application server to which you are currently connected.

Notes:

1. You can only enter the SQLQRY command from the user machine after the resource manager has been loaded and while an application is running.

2. You cannot use the SQLQRY command if you are using the SYNChronous(YES) option with the SQLINIT EXEC, if you are not receiving CP messages (for example, if you specified CP SET MSG OFF), or if your application has locked the keyboard. See SQLINIT EXEC for information on the SYNChronous(YES) option.

The following information is displayed at the terminal when you enter the SQLQRY command:

EXTNAME

displays the user ID of the application requester to which you are currently connected. It also displays the CMS Work Unit number, if CMS Work Units are in use.

RDBMS

displays the name, class, and release level (version, release, and modification level) of the application server being accessed. If the Protocol(DRDA) or Protocol(AUTO) option is specified with the SQLINIT EXEC and the SQLQRY command is issued before handshaking (the process of establishing a connection) is completed, "n/a" will be displayed for both the class and release level of the application server. If the Protocol(SQLDS) option is specified with the SQLINIT EXEC and the SQLQRY command is issued, "SQLDS/VM" will be displayed for the application server class and "n/a" will be displayed for the application server release level, because handshaking does not take place. See SQLINIT EXEC for information on the Protocol parameter.

STATUS

displays the communication state. COMM indicates that the Work Unit sent an SQL statement to the database machine and has been waiting for a reply since the time shown. APPL indicates that the Work Unit returned control to the application at the time shown. VRA indicates that the VM Resource Adapter is processing your request.

LUWID

displays the logical unit of work identifier, which uniquely identifies an LU6.2 conversation. Its value is netid.luname.instance_number.sequence_number, where netid and luname are up to 8 characters long, instance_number is 12 characters long, and sequence_number is 4 characters long. LUWID is only used for conversations that use the AUTO and DRDA Protocol options. If the middle portion of the LUWID contains *IDENT, then the application server is a local one or is in a TSAF collection; in these cases, no LU name and TPN are displayed. If TCP/IP is being used, the LUWID has the format IPADDRESS.PORT.INSTANCE_NUMBER, where IPADDRESS is 8 characters long, PORT is 4 characters long, and INSTANCE_NUMBER is 12 characters long.

LU

displays the logical unit name, if the access is through VTAM.

TPN

displays the transaction program name. Its character and hexadecimal versions are both displayed. For a DB2 application server, this is the same as the resource ID.

TCPIP

displays the IP address of the target host system. It is only displayed when TCP/IP is being used.

PORT

displays the port number of the target application server. It is only displayed when TCP/IP is being used.

Example

Figure 64 displays sample output from an SQLQRY command issued in a VM/ESA environment with Protocol(AUTO) and two active CMS Work Units.

Figure 64. Sample Output from SQLQRY in a VM Environment with Protocol(AUTO) and CMS Work Units

+--------------------------------------------------------------------------------+
|11:09:51  * MSG FROM SQLUSR6 : Status of Server Conversations on |2000-09-20.    |
|11:09:51  * MSG FROM SQLUSR6 : EXTNAME = SQLUSR6.1                              |
|11:09:51  * MSG FROM SQLUSR6 : RDBMS  = SQLRDB1            SQLDS/VM |V6.1.0      |
|11:09:51  * MSG FROM SQLUSR6 : STATUS = COMM   TIME = |2000-09-20.11:09:43       |
|11:09:51  * MSG FROM SQLUSR6 : LUWID = IBMNET01.*IDENT.45F2ABCD236D42.0001      |
|11:09:51  * MSG FROM SQLUSR6 :                                                  |
|11:09:51  * MSG FROM SQLUSR6 : EXTNAME = SQLUSR6.2                              |
|11:09:51  * MSG FROM SQLUSR6 : RDBMS  = IBMSTLDB2          DB2      V2.3.0      |
|11:09:51  * MSG FROM SQLUSR6 : STATUS = APPL   TIME = |2000-09-20.11:07:32       |
|11:09:51  * MSG FROM SQLUSR6 : LU = STLMVS04     TPN = "6DB     (X'07F6C4C2')   |
|11:09:51  * MSG FROM SQLUSR6 : LUWID = IBMNET01.TORLU001.45F2ABCD236DFE.0001    |
|11:09:51  * MSG FROM SQLUSR6 :                                                  |
|11:09:51  * MSG FROM SQLUSR6 : EXTNAME = SQLUSR6.3                              |
|11:09:51  * MSG FROM SQLUSR6 : RDBMS  = SQLMACGM           SQLDS/VM  |V7.1.0     |
|11:09:51  * MSG FROM SQLUSR6 : STATUS = COMM   TIME = |2000-09-20.11:07:32       |
|11:09:51  * MSG FROM SQLUSR6 : TCPIP = 9.21.23.32       PORT = 8030             |
|11:09:51  * MSG FROM SQLUSR6 : LUWID = G9151720.L372.B1622DADEF8A               |
|                                                                                |
+--------------------------------------------------------------------------------+

You can have multiple active CMS Work Units in a user machine, each accessing an application server. In this example, information is displayed for two CMS Work Units.

EXTNAME contains the user ID of the application requester, concatenated with the CMS Work Unit number.

Because Protocol(AUTO) is used, a unique LUWID is assigned to each conversation.

• Work Unit #1:
  • Accesses a SQL/DS V3.3.0 application server called SQLRDB1. Since no LU name or TPN are displayed, the application server must be a local one or is in a TSAF collection. For the same reason, the middle portion of the LUWID is *IDENT.
  • Has a STATUS of COMM, indicating that it must have sent an SQL statement to the application server and has been waiting for a reply (since 11:09:43, as indicated by TIME).
• Work Unit #2:
  • Accesses a DB2* V2.3.0 application server called IBMSTLDB2. The LU name and TPN are displayed because the application server is in a VTAM network. The DB2 application server uses the default DRDA TPN of X'07F6C4C2'.
  • Has a STATUS of APPL, indicating that it has already returned control to the application (at 11:07:32, as indicated by TIME).
• Work Unit #3:
  • Accesses a DB2 Server for VM |Version 7 Release 1 application server called SQLMACGM. The HOST name and SERVICE port connection are displayed because the application server is in a TCP/IP network.