Archives of the TeradataForum

Message Posted: Thu, 27 Mar 2009 @ 01:37:40 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Ronald-

It sounds like you are on your way but below is an article that may help.

Use "blocker" to identify lock conflict and "tran" to drill down to identify the session holding the lock.

-dave.clark

------------------------------------

Below is a procedure to convert the session number from the lokdisp utility output into the same format that you see in querysessn or pmon

Here is sample lokdisp output which shows a session number of 12,22532

     > > start lokdisp
     start lokdisp
     Started 'lokdisp' in window 1
       at Wed Aug 15 14:31:48 2001


     Input Supervisor Command:
     > > root@ada# cnsterm 1

     Attempting to connect to CNS...completed.
     HelloLOCK DISPLAY UTILITY Command String Syntax:

          Help or ?
          TRan     [ProcId Uniq1 Uniq2] | [ALL]
          Db       [DBname] | [ALL]
          TAble    [DBname.Tablename] | [ALL]
          ROWRange [DBname.Tablename TypeAndIndex] | [ALL]
          ROWHash  [DBname.Tablename TypeAndIndex, RowHash1 RowHash2] | [ALL]
          Blockers [TRAN [ProcId Uniq1 Uniq2] | [ALL]] | [LIMIT [Number] | [NONE]]
          Quit

     -> Please enter your selection from the list:
     > > tran a
     tran a
     - The following amps are available:

          0     1

     -> Which amp(s) do you want to request on
     (S=Sampling/A=all/C=cancel/Q=quit):
     > > A

                           GRANTED LOCK REQUEST(S):

     Tran: 16298 01520CB0
     Host:  1076 Session:    12,22532 Mode: WR   User: EPDWC021

     Database: DBC                             Table: SESSIONTBL

     Row Hash1: 53984,33413

To convert this number into the format that qrysessn uses do the following:

Suppose we get session number 12,22532.

Multiply the first part before the comma (12) by 65536 then add to that the number after the comma (22532).

     (12 * 65536) + 22532 = 808964

Here is sample qrysessn output:

     Host   Session  PE     DBC User ID
     -----  -------  -----  -----------
        52   808964  16298  EPDWC021

Additional Information/Comments:

Two UNIX scripts have been created to do the conversion for you they are avaiable on all of the DBS team test systems in /usr/bin for Teradata employees only (see attached).

     babbage# lokdisp2qrysessn 17,58714

     The session 17,58714 in qrysessn format is: 1172826
     The session in hexadecimal is 11E55A

     babbage# qrysessn2lokdisp 1172826

     The session 1172826 in lokdisp format is: 17,58714
     The session in hexadecimal is 11E55A

     -------------------------------------------------------------------
     lokdisp2qrysessn
     -------------------------------------------------------------------

     #!/bin/ksh
     if [ "$1" == "" ] || [ "$2" != "" ];then
     echo " "
     echo "usage: lokdisp2qrysessn "
     echo "example: lokdisp2qrysessn 17,58714 (no space after the comma)"
     echo " "
     echo "The output for this example would look like this:"
     echo " "
     echo "The session 17,58714 in qrysessn format is: 1172826"
     else
     INPUT=`echo "$1"`
     INPUT1=`echo "$1" | cut -f1 -d","`
     INPUT2=`echo "$1" | cut -f2 -d","`
     ANSWER=`echo "$INPUT1 * 65536 + $INPUT2" | bc`
     HEX=`echo "obase=16\nibase=10\n$ANSWER" | bc`
     echo " "
     echo "The session $1 in qrysessn format is: $ANSWER"
     echo "The session in hexadecimal is $HEX"
     echo " "


     fi

     -------------------------------------------------------------------
     qrysessn2lokdisp
     -------------------------------------------------------------------

     #!/bin/ksh
     if [ "$1" == "" ] || [ "$2" != "" ];then
     echo " "
     echo "usage: qrysessn2lokdisp "
     echo "example: qrysessn2lokdisp 1172826"
     echo " "
     echo "The output for this example would look like this:"
     echo " "
     echo "The session 1172826 in lokdisp format is: 17,58714"
     echo " "
     else
     INPUT=`echo "$1"`
     INPUT1=`echo "$1 / 65536" | bc`
     INPUT2=`echo "$1 - 65536 * $INPUT1" | bc`
     HEX=`echo "obase=16\nibase=10\n$INPUT" | bc`
     ANSWER=`echo "$INPUT1"`
     echo " "
     echo "The session $1 in lokdisp format is: $ANSWER,$INPUT2"
     echo "The session in hexadecimal is $HEX"
     echo " "
     fi

	<Prev	Next>		<<First	<Prev	Next>	Last>>