Hello Craig, Jim Sartain asked me to reply to your recent questions:
 
>> SQLMON has capability to find the root cause of a locking problems.
>> SQLMON will programmatically examine each session that is holding locks,
>> Therefore it will be faster to resolve performance bottlenecks on the system
 
>  Does this mean each incoming connection will show up as a session
>  (i.e. appears in a :SHOWJOB)?  At the TCU on 1/20 I got the impression
>  the incoming connection would not show up as a job/session.  If the
>  latter is the case, will SQLMON be able to tell us the nodename and/or
>  IP of the incoming connection?
 
In the most recent versions of ALLBASE/SQL (G.0 and G.1), incoming client
connections will not show up as a job/session (they are not visible in a
:SHOWJOB).  Only the listener process shows up, for example:
 
   :showjob
 
   JOBNUM  STATE IPRI JIN  JLIST    INTRODUCED  JOB NAME
 
   #J4     EXEC        10S LP       THU  2:25P  HPDARPA,MANAGER.SYS
 
The listener process spawns new PINs for the incoming client connections:
 
   :showproc;job=#j4
 
   PRI  CPUTIME   STATE  JOBNUM  PIN  (PROGRAM) STEP
 
   C152  0:00.240  WAIT   J4      73   :HPDALSTN '-l ARPA'
   B149  0:18.586  WAIT   J4        77   (HPDALSTN.PUB.SYS) -l ARPA
   C152  0:00.409  WAIT   J4          79   (HPDADVR.PUB.SYS) 11000086,0,ARPA
   C153  0:00.325  WAIT   J4          80   (HPDADVR.PUB.SYS) 11000087,0,ARPA
   C152  0:00.020  WAIT   J4          86   (HPDADVR.PUB.SYS) 11000088,0,ARPA
 
For example, PINs 79 and 80 are the server connection processes that have
been spawned to accommodate two incoming client connections.  PIN 86 will be
used for the next client connection (the listener process always keeps an
extra PIN available to improve the performance of each connection).
 
 
SQLMON does not provide the nodename and/or IP of incoming client connections
(but I would like to discuss this idea with you in just a moment).  I can see
how the wording on the enhancement list is ambiguous, but the actual
enhancement is the following:
 
   SQLMON will have a new screen called LOCK ROOT.  It will enable the
   DBA to quickly and easily identify the single server connection that
   is blocking the most number of other server connections.  This session
   the "root cause" of the biggest locking problem for the database.  The
   DBA may wish to issue a TERMINATE USER or a a TERMINATE QUERY for this
   particular session to "unhang" the database.  The enhancement improves
   the DBA's ability to quickly identify the offending server connection.
   The type of information provided about server connections is not
   changing, however.
 
   Also, please be aware that this functionality has not yet been submitted
   to G.1, and there is a possibility that it will be provided on a future
   release instead of G.1.
 
SQLMON does not currently provide any client information per se.  It does
provide the following information about each server connection: USER@ACCOUNT,
PROGRAM NAME, PIN, CID.  For example, the OVERVIEW PROGRAM screen might show:
 
   REFRESH = 10                  OVERVIEW PROGRAM               SESSIONS = 2
      CID   PIN  USER@ACCT          STATUS          XID  ISO  PRI  LABEL
 
   PROGRAM NAME = HPDADVR.PUB.SYS
        1    79  MGR@DDB             Idle
        2    80  MGR@DDB             Idle         20493   RR  127
 
 
For PC clients, the PROGRAM NAME is always HPDADVR.PUB.SYS.  The USER@ACCOUNT
comes from the odbc.ini file.  Today, it is possible to uniquely identify each
PC connection by assigning a unique USER@ACCOUNT in each odbc.ini file.
 
The PIN matches the PIN of the showproc statement above.  Because of
multiconnect functionality, it is possible for a a single PC to have
multiple server connections to the same database.  The CID uniquely identifies
each server connection (one PIN may have several CID's).
 
I would like to hear more from you about the use of the nodename and/or
IP in SQLMON.  Were you aware that you could use the USER@ACCOUNT from the
odbc.ini file to uniquely identify each PC client?  If not, do you think that
this is an adequate solution for you today?  Do you think that other customers
would value this information?  If so, how do you think that we should "spread
the word" (where would you expect to see this documented?).
 
If SQLMON is enhanced to provide additional client information, what info
would you like to see?  Would you rather have the nodename, the IP, or do you
need both?  Is there other client information that would be valuable?
Do you think that other customers would value this enhancement?  Should it be
a high, medium, or low on our list of potential enhancements?  Why?
 
I'd also be interested in knowing how much you use SQLMON, for example do you
consider yourself to be more of a power user or a novice user?  What screens
do you normally use?  On what screens would you want to see the new client
information?  Also, what version of SQLMON are you using today?
 
Thanks for your message.  I'm looking forward to hearing your answers, and
any other comments you have on this subject.
 
Best Regards,
Leslie-Anne
 
[log in to unmask]