The contents of most of the fields are explained in the descriptions of the forms (see "Layout of the forms" ). This section describes only those additional items that are output to SYSLST but not elsewhere.
Page header
SESMON <ver> | INTERVAL: 20 SECONDS | DATE: <date> | TIME: <time> | PAGE: 1 |
===================================================================================================================================
Hardware and software environment
The information on the hardware and software environment is output only once, at the beginning of and after a live migration.
>>> HARDWARE AND SOFTWARE <<< Hardware Type: S190-40 BS2000 <ver> Hostname: D016ZE09 Main Memory: 1232.0 MByte Number of CPUs: 2 Number of active CPUs: 2 HSI-Type: XS31 Configuration: S SESDCN Master DCN: Users: 0 NATL: 0 min LCTL: 0 min ===================================================================================================================================
DBH options
The currently set options and information on the DBH start time are output. The information is output again automatically after the options have been changed.
>>> OPTIONS <<<
1.DBH SESAM-DBH <ver>
DBH STARTED AT : <date> <time>
LAST RECONFIGURE/RELOAD/RESTART : <date> <time>
ACCOUNTING *OFF
ADMINISTRATOR *ANY
COLUMNS 256
CURSOR-BUFFER BUFFER-SIZE=512 , FRAME-SIZE=4
DBH-IDENTIFICATION CONFIGURATION-NAME=Z, DBH-NAME=X
DBH-TASKS 1
LINKED-IN-ATTRIBUTES CODED-CHARACTER-SET=*NONE
MSG-OUTPUT MSG =*SYSOUT, *SYSLST
OPEN-CLOSE-MSG=*NONE
MAIL=*NONE
OLD-TABLE-CATALOG 5
RECOVER-OPTIONS SYSTEM-DATA-BUFFER=1500 , USER-DATA-BUFFER=1000
TALOG-SUPPORT=*PUBLIC-DISK
CAT-ID=*STD
PRIMARY-ALLOCATION=*STD , SECONDARY-ALLOCATION=*STD
WALOG-SUPPORT=*PUBLIC-DISK
CAT-ID=*STD
PRIMARY-ALLOCATION=*STD , SECONDARY-ALLOCATION=*STD
REQUEST-CONTROL *NONE
RESTART-CONTROL BUFFER-LIMIT:SYSTEM-DATA-BUFFER=50
BUFFER-LIMIT:USER-DATA-BUFFER=50
TALOG-LIMIT=512
LOGICAL-ROLLBACK=*IMMEDIATELY
RETRIEVAL-CONTROL USER-DATA-ACCESS=1000 , INDEX-EVALUATION=2147483647
ABORT-EXECUTION=2000
SECURITY PRIVILEGE-VIOLATIONS=10
SAT-SUPPORT=*OFF
SERVICE-TASKS INITIAL=1 , MAXIMUM=1 , JOBCLASS=*STD
WORK-FILES=*PUBLIC-DISK
CAT-ID=*STD
PRIMARY-ALLOCATION=*STD , SECONDARY-ALLOCATION=*STD
RECORDS-PER-CYCLE=*STD
SESSION-LOGGING-ID SESSION-ID=*STD, START-NUMBER=0001
SPACES 100
SQL-DATABASE-CATALOG 10
SQL-SUPPORT *YES
INACTIVITY-TIME=1 , CURSORS=70
INTERNAL-SORT-LIMIT=5000
PLANS=70
SUBORDERS INITIAL=24 , MAXIMUM=24
SYSTEM-DATA-BUFFER 1536
SYSTEM-THREADS WRITE-THREADS=1
THREADS 2
TRANSACTION-SECURITY *YES
LOCK-TIME=4 , INACTIVITY-TIME=40
MAX-ISOLATION-LEVEL=*STD
LOCK-ESCALATION:NUMBER-RECORDS=4000
LOCK-ESCALATION:PERCENTAGE-RECORDS=50
LOCK-ESCALATION:NUMBER-INDEX-VALUES=1000
TRANSFER-CONTAINER INITIAL=120 , MAXIMUM=16000
USERS 24
USER-DATA-BUFFER 1536
WORK-CONTAINER INITIAL=120 , MAXIMUM=120
==================================================================================================================The CONFIGURATION form “APPLICATIONS”
>>> APPLICATIONS <<<
Application Number of Users Number of Tasks Current Applications: 1
Current Maximal / Current Maximal Applications: 64
APPLIC02 7 8 / 8
==================================================================================================================The CONFIGURATION form “PREFETCH-BUFFERS”
>>> PREFETCH-BUFFERS <<< Appl./ H I T R A T E Perc. Max. TSN T <=1KB <=2KB <=4KB <=8KB <=16KB <=32KB <=64KB >64KB Occ. Occ. TSN=1Z03 T --% --% --% --% --% --% --% --% 6% 60% ________ _ --% --% --% --% --% --% --% --% 0% 0% ________ _ --% --% --% --% --% --% --% --% 0% 0% ________ _ --% --% --% --% --% --% --% --% 0% 0% ________ _ --% --% --% --% --% --% --% --% 0% 0% . . . ==================================================================================================================
The SESDCN form “APPLICATIONS”
A maximum of 50 transaction applications are output. The output is sorted in descending order according to the number of active users (Number of User Current).
>>> APPLICATIONS <<<
Application Number of Users Number of Tasks Current Applications: 1
Current Maximal / Current Maximal Applications: 64
ANWEND01 6 1 /
==================================================================================================================The SESDCN form “CAPACITY”
>>> CAPACITY <<<
Session Interval Maximum Average
Broadcast Messages : 0 - Message Fragmentation : 0 0 Elapsed Time in
BCAM Memory Lacks : 0 - Time in Pool Lock : 4578 12 Contingency :
0.00%
Mail Messages : 7 - Users per Application : 6 6 Used Pool Containers :
554
Requests for Locks : 381 - Tasks per Application : 1 1 Free Pool Containers :
710
Pool Locks : 381 - Container per User : 3 3
Synchron Accesses : 88 - Sub TAs per User : 1 0
Asynchron Accesses : 0 - Users per Catalog : 1 1
SESAM MESSAGES:
Length <= 2K <= 4K <= 8K <= 16K <= 32K <= 64K
Session 115 1 4 0 0 0
Interval - - - - - -
==================================================================================================================The SESDCN form “OVERVIEW”
>>> OVERVIEW <<<
Master DCN : Number of SESDCNs : 1 Session Interval
Configuration : Z Number of SESAM-DBHs : 1 Number of DML-Statements: 88 -
Session Begin : <date> Number of active Users : 7 Number of Transactions : 25 -
<time> Number of active Applications: 1 Number of Remote DML : 9 -
Users : 128 Number of active PTCs : 0
NATL : 12 min
LCTL : 8 min
=================================================================================================================The SESDCN form “TRANSACTIONS”
A maximum of 200 transactions are output. They are sorted according to their duration. If a transaction is locked by a transaction that can no longer be output, this is indicated in the “Lock” column by two asterisks (**).
>>> TRANSACTIONS <<<
Session Interval Session Interval Session Interval
Deadlock Analysis : 0 - External Resets : 0 - Internal Resets : 6 -
Deadlock Found : 0 - - Administration : 0 - - Deadlock : 0 -
- Application : 0 - - NATL : 6 -
Open Transactions : 7 - LCTL : 0 -
TA-NR TAC User-ID S I/O Lock Duration Statements
1 EXAMPLE D016ZE07TSN=8GZNUSER0001 A 0 00:13:50 1
2 EXAMPLE D016ZE07TSN=8GZNUSER0002 A 0 00:13:31 1
3 EXAMPLE D016ZE07TSN=8GZNUSER0003 A 0 00:13:13 1
4 EXAMPLE D016ZE07TSN=8GZNUSER0004 A 0 00:12:52 1
5 EXAMPLE D016ZE07TSN=8GZNUSER0005 A 0 00:12:26 1
6 EXAMPLE D016ZE07TSN=8GZNUSER0006 A 0 00:12:07 1
7 SQLDB112 D016ZE07TSN=8G40SEDI63 A 0 00:03:14 2
==================================================================================================================The DBH form “I/O”
>>> I/O <<< DBH: X
Number of Spaces Cursor Files LOG Files
2 0 3
System Data Buffer User data Buffer Cursor Files
Session Interval Session Interval Session Interval
Log. Read 260 39 114 21 34 15
Log. Write 16 2 4 1 13 5
Phys. Read 82 0 18 0 5 2
Phys. Write 11 0 3 0 5 2
Hitrate 66.30% 100.00% 82.20% 100.00% 78.72% 80.00%
DA-LOG/CAT-LOG TA-LOG WA-LOG
Session Interval Session Interval Session Interval
Phys. Read 0 0 1 0 0 0
Phys. Write 6 1 8 1 0 0
4K Blocks /
Written Blocks 1.00 2.12 0.00
Spaces and Cursors
Data Log. Read Log. Write Phys. Read Phys. Write Hitrate
Name Session Interval Session Interval Session Interval Session Interval
REQUESTCUST.CATALOG SYS 240 35 9 0 74 0 7 0 67.47%
USR 100 20 2 0 17 0 2 0 81.37%
<CURSOR FILES> 34 15 13 5 5 2 5 2 78.72%
REQUESTCUST.CUST SYS 20 4 7 2 8 0 4 0 55.56%
USR 14 1 2 1 1 0 1 0 87.50%
LOG Files
Phys. Read Phys. Write 4K Blocks /
File Name Session Interval Session Interval Written Blocks
SESAMZX.TA-LOG 1 0 8 1 2.12
$ID1.REQUESTCUST.0003.D.0001 0 0 3 1 1.00
$ID1.REQUESTCUST.0003.C.0001 0 0 3 0 1.00
==================================================================================================================Field descriptions
Spaces and Cursors
This output contains a maximum of 100 entries. The entries are sorted in decreasing order according to their number of I/Os.
Name
Space name or <CURSOR-FILES>. In the case of space names, a distinction is drawn between USR and SYS.
Log. Read
Number of logical read accesses of the catalog space and user spaces and of the cursor files since the database was opened and per time period.
Log. Write
Number of logical write accesses of the catalog space and user spaces and of the cursor files since the database was opened and per time period.
Phys. Read
Number of physical read accesses of the catalog space and user spaces and of the cursor files since the database was opened and per time period.
Phys. Write
Number of physical write accesses of the catalog space and user spaces and of the cursor files since the database was opened and per time period.
Hitrate
Logical accesses of the catalog space and user spaces and of the cursor files as a percentage of the total number of accesses.
LOG Files
This output contains a maximum of 100 entries. The entries are sorted in decreasing order according to their number of I/Os.
File Name
File name of the LOG file.
Phys. Read
Number of physical read accesses of the specified LOG file (for the session and per time period).
Phys. Write
Number of physical write accesses of the specified LOG file (for the session and per time period). The I/Os for RECOVER are not included here.
4K Blocks/Written Blocks
Average number of 4-Kbyte blocks written in the case of a write request.
The DBH form “SERVICE ORDERS”
A maximum of 100 requests are output.
Detailed outputs for the requests in the User-ID / Order information block may be provided in the ALTER PARTITIONING: MOVE ROWS / CHG ROWID, RESTORE PROGRESS, RECOVER/REFRESH PROGRESS, REORG SPACE PROGRESS and UNLOAD/EXPORT/LOAD/IMPORT TABLE PROGRESS information blocks.
>>> SERVICE ORDERS <<< DBH: 8
User-ID Order TSN S Duration
D016ZE09TSN=7V3ASESUTI UNLOAD 7WP7 A 00:00:03
D016ZE09TSN=7V2ESESUTI REORG SPACE:FIRST PHASE 7WP8 A 00:00:02
D016ZE09TSN=7V4ESESUTI ALTER PARTITIONING 7WP9 A 00:00:01
D016ZE09TSN=7V2DDSQL CREATE INDEX 7WP2 A 00:00:00
D016ZE09TSN=7V2DDSQL CREATE INDEX F 00:00:00
==================================================================================================================
UNLOAD/EXPORT/LOAD/IMPORT TABLE/ALTER PARTITIONING PROGRESS:
Number of Records
TSN Table Current Total Done
7WP7 PTJDB 81856 206991 39%
==================================================================================================================
REORG SPACE PROGRESS:
Number of Blocks
TSN Space Name Current Total Done
7WP8 $SQLDB111.CME.SPACE1 752 6768 11%
==================================================================================================================
UNLOAD/EXPORT/LOAD/IMPORT TABLE/ALTER PARTITIONING PROGRESS:
Number of Records
TSN Table Current Total Done
7WP9 PTX23 730 0 0%
=================================================================================================================The DBH form “SERVICE TASKS”
>>> SERVICE TASKS <<< DBH: X
Service Tasks Maximal Current Active Free Load
1 1 0 1 0.00%
Orders Number(Session) Number(IV) Not Processed Not Fetched
10 6 0 0
Number of Used-CPU- Elapsed Size Memory Pages/
TSN Order Orders Phys. I/O Time (msec) Time (msec) Load (Pages) Extension Extension
66WI 10 30 141 5736 2.46% 515 1 256.00
===================================================================================================================================Field descriptions
A maximum of 50 service tasks are output. The output is sorted in descending order according to the amount of CPU time used (Used-CPU-Time).
Memory Extension
Number of dynamic memory extensions in the service task.
Pages/Extension
Average number of requested pages per memory extension in the service task.
The DBH form “SQL INFORMATION”
>>> SQL INFORMATION <<< DBH: X
Session Interval Plans Session Interval
SQL DML : 22 2 Accesses : 23 Sort Operations : 1 0
UPDATE : 1 0 Accesses/IV : 3
RETRIEVAL : 21 2 Generated : 3 Max. SQL Cursors : 1
SQL DDL/SSL : 0 0 Generated/IV : 1 Act. SQL Cursors : 1
SQL UTI : 0 0 Number per
DBH-Option : 70
Number of Calls Session Interval
Kernel Control : 131 30 Plan Buffer
Catalog Manager : 70 16 Number of Plans : 3
CALL DML Handler : 5 0 Size of
Authentication : 26 3 Primary Buffer : 1148000
Authorization : 9 2 Occupation of
Primary Buffer : 12000
Authentication Opt. : 24 3 Size of
Authorization Opt. : 5 1 Secondary Buffer: 32792
==================================================================================================================Field descriptions
Authentication
Number of authentication calls (for the session and per time period). Authentication calls check whether the user is allowed to work with the specified authorization key.
Authorization
Number of authorization calls for DML (for the session and per time period).
Authorization calls check whether the user is allowed to work with the desired database objects (e.g. a table) in the desired way (e.g. update them).
Authentication Opt.
Number of successful authentication optimizations (for the session and per time period).
Authorization Opt.
Number of successful authorization optimizations for DML (for the session and per time period).
The DBH form “STATEMENTS”
>>> STATEMENTS <<< DBH: X
Statements Session Interval Statements
Local DML : 5 0 Number : 0
Local SQL : 53 5 Active : 0
Remote DML: 0 0 Waiting: 0
Remote SQL: 0 0
Activity-
TAC User-ID S I/O Duration Time Type
QM124ELS SYSTEM01TSN=87L0KAASTEUG 3732 01:02:50 0.00 M
QM124ELS SYSTEM01TSN=87LXKAASTEUG L 3280 01:02:41 0.00 M
QM124ELS SYSTEM01TSN=87LYKAASTEUG 11125 01:02:25 0.00 M
KSURQTT1 SYSTEM01ANWEND01QTT1IP020000003B 141670 01:01:03 0.00 M
KSUUQCON SYSTEME01ANWEND01QCONHP0100000062 L 14015 00:58:28 0.00 M
KSUUQDEC SYSTEM01ANWEND01QDECDS010000005D L 12949 00:53:32 0.00 M
KSUUQDEL SYSTEM01ANWEND01QDELHS010000005C L 20060 00:50:20 0.00 M
===================================================================================================================================The DBH form “SYSTEM INFORMATION”
In the case of list output either the logging information or the information about the DBH session restart (separated according to AI and LBI blocks) is displayed.
List output in an ongoing DBH session:
>>> SYSTEM INFORMATION <<< DBH: W
Active Suborders Available Suborders Used Suborders
for CALL DML: 0 Maximal: 32767 Maximal: 32
for SQL DML : 1 Current: 201 Current: 1
Parallel Open Accounting : OFF
Spaces: 31 SESCOS : OFF
Open Cursors: 1 Request Control: ON
Current Percentage Maximal Percentage
Container Size Occupation Size Occupation
Work 5000 0.52% 222222 0.09%
Transfer 5000 0.11% 1000000 0.01%
Threads Service
Active Ready Free Waiting TA-Lock Task I/O
1 0 18 0 0 1 0
Valid Logging Information - Number of Blocks: 453
===================================================================================================================================List output during DBH restart:
>>> SYSTEM INFORMATION <<< DBH: W
Active Suborders Available Suborders Used Suborders
for CALL DML: 0 Maximal: 0 Maximal: 0
for SQL DML : 0 Current: 0 Current: 0
Parallel Open Accounting :
Spaces: 0 SESCOS :
Open Cursors: 0 Request Control:
Current Percentage Maximal Percentage
Container Size Occupation Size Occupation
Work 0 0.00% 0 0.00%
Transfer 0 0.00% 0 0.00%
Threads Service
Active Ready Free Waiting TA-Lock Task I/O
0 0 0 0 0 0 0
Physical Repair : Current Blocks 30 Total Blocks 90 Done 33.33%
Logical Rollback: Current Blocks 0 Total Blocks 0 Done 0.00%
===================================================================================================================================The DBH form “TASKS”
>>> TASKS <<< DBH: X
Used CPU-
TSN Threads Orders Waits/IV IOs/IV Waits/IO Time (msec)
8ENU 0 0 - - - 4647
===================================================================================================================================The DBH form “SYSTEM THREADS”
>>> SYSTEM THREADS <<< DBH: 0
WRITE THREADS
Number Blocks Written Total Blocks Written Intervall
1 5374 8
===================================================================================================================================The DBH form “TRANSACTIONS”
>>> TRANSACTIONS <<< DBH: X
Transactions Session Interval Users : 2
Number : 9 1 Open TAs : 0
Retrieval: 5 0 Group Commit : 0.00
Update : 4 1 Group Commit/IV : 0.00
Reset : 2 0 Number of Locks : 0
Number of Locks/IV: 0
TA-NR TAC User-ID S I/O Lock Duration Statements Type
1 SQLABT98 SYSTEM01QMUJBS010000004C A 55926 00:00:04 181 DML
.
.
.
Locked Transactions:
TA-NR Lock-Type Object-Type Space-Name Table-/
Index-ID
.
.
.
===================================================================================================================================Field descriptions
A maximum of 200 transactions are output. They are sorted according to their duration. If a transaction is locked by a transaction that can no longer be output, this is indicated in the “Lock” column by two asterisks (**).
Type
Transaction type
DML: DML transaction
DDL: DDL transaction
'BLANK' (blank): the transaction type is still undefined
Locked Transactions
Only transactions marked as locked in upper part of the table. More information is given here on the locked object.
TA-NR
Unique number of the locked transaction assigned by SESMON. This number can be used to assign the entry to the associated entry under TA-NR in the upper part of the table.
Lock-Type
The lock type:
shared lock
exclusive lock
preannounced exclusive lock (required)
Object-Type
The object type of the locked object:
catalog (whole database)
Space
table
record
Index
SI entry (secondary index entry)
plan (SQL access plan)
metadata (metadata of a catalog)
Space-Name
Name of the space. It indicates the space in which the locked object is located.
For the object types “space”, “table”, “record”, “index” and “SI entry” in the form
log.catalogname.spacename.For the object types “catalog” and “metadata” in the form log.catalogname.
For the object type “plan” spaces are placed here.
Table-/Index-ID (five decimal digits)
Internal table number when a table or record is locked, internal index number when an index or SI entry is locked.If the locked object is a record, the table in which the record is locked is specified here. If the locked object is an SI entry, the index containing the locked SI entry is specified. Blank spaces are placed here for all object types other than table, record, index and SI entry.
The universal user can create an assignment table that assigns the correct table name and index name to the corresponding internal table number and internal index number. There are two ways to do this:
by issuing the following SQL statements for each database (see the “ SQL Reference Manual Part 1: SQL Statements”):
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ID FROM SYS_INFO_SCHEMA.SYS_TABLES SELECT INDEX_SCHEMA,INDEX_NAME,INDEX_ID FROM SYS_INFO_SCHEMA.SYS_INDEXESby using the utility monitor to call the forms “SYS_INFO_SCHEMA,TABLES” and “SYS_INFO_SCHEMA,INDEXES” for each database (see the “ Utility Monitor” manual).