DB2 Server for VM: System Administration
Accounting data, like any other data, can be loaded into tables and
maintained by any DB2 Server for VM facility. The following sections
describe how to set up dbspaces to hold accounting records and present an
example. You will have to modify the example tables to meet your own
installation's requirements.
Setting up a database for accounting data involves the same activities that
would be done for any data application:
- Adding and acquiring a dbspace
- Creating tables for the accounting data
- Creating views on those tables
- Creating indexes on those tables.
Because accounting data is usually read-only, it is most suited for a
private dbspace. When it is in a private dbspace, multiple users are
able to read it as long as the tables are not being loaded. (If they
are being loaded, users get an immediate notification that a load is taking
place in the form of a negative SQLCODE).
Also, because the data is read-only and because its source is a sequential
file, it is a candidate for a nonrecoverable dbspace. For information
on the advantages and disadvantages of this type of storage, see Nonrecoverable Storage Pools.
The size of the dbspace depends on a number of factors. The key
considerations are:
- The number of accounting records you want to keep online
- The row length of the records
- The index space requirements.
When you have determined these factors, you can estimate the size of the
dbspace needed by using the formulas in Appendix B, Estimating Database Storage.
To estimate the rate at which your installation generates accounting
records, use the accounting facility for a trial period (a day or a
week). Or, you can try to make an initial estimate using the method
shown on page ***.
One approach to organizing accounting records is to place them in four
separate tables:
- One to hold the termination records, which summarize the resources
consumed during an entire session of the database manager.
- One to hold the initialization, operator, and checkpoint records, which
describe the overhead resources consumed by the database manager
processes.
- One to hold user records, which describe the resources consumed by
individual users.
- One to hold remote access records, which contain the LUWID. The
records also contain the user ID and datetime value that can be used to match
with the regular user records.
Figure 76 shows the statements you could issue to create these four
tables, here named SQLDETAIL (for termination records), SYSDETAIL (for
initialization, operator and checkpoint records), USERDETAIL (for user
records), and REMDETAIL (for remote user records).
Figure 76. Example of DBS Utility Commands to Create Accounting Tables
CREATE TABLE SQLDETAIL(SQLNAME CHAR(8),
DATE CHAR(6),
TIME CHAR(6),
RUNTIME INTEGER,
DASDIO INTEGER,
LPAGBUFF INTEGER,
CENTURY CHAR(2) ) IN SQLDBA.ACCTNG;
CREATE TABLE SYSDETAIL(SQLNAME CHAR(8),
TYPE CHAR(8),
DATE CHAR(6),
TIME CHAR(6),
RUNTIME INTEGER,
CPUTIME INTEGER,
LPAGBUFF INTEGER,
CENTURY CHAR(2) ) IN SQLDBA.ACCTNG;
CREATE TABLE USERDETAIL(SQLNAME CHAR(8),
CPUSER CHAR(8),
SQLUSER CHAR(8),
USERDATA CHAR(16),
DATE CHAR(6),
TIME CHAR(6),
PNAME CHAR(8),
ATIME INTEGER,
CPUTIME INTEGER,
ULPAGBUF INTEGER,
CENTURY CHAR(2) ) IN SQLDBA.ACCTNG;
CREATE TABLE REMDETAIL(SQLNAME CHAR(8),
CPUSER CHAR(8),
SQLUSER CHAR(8),
DATE CHAR(6),
TIME CHAR(6),
LUWID VARCHAR(27),
CENTURY CHAR(2) ) IN SQLDBA.ACCTNG;
|
| Note: | If you have accounting tables defined from an earlier release, you can use
the ALTER TABLE statement to add the CENTURY column to
your existing tables.
|
The information for all the columns in the tables is loaded directly from
the accounting records. These tables are described in detail
below.
Each row of the SQLDETAIL table contains selected data from one
termination accounting record, and represents one session of the database
manager. The following information is inserted into the SQLDETAIL
columns:
- SQLNAME
- The VM user ID of the database machine
- DATE
- The dates from the termination records
- TIME
- The times from the termination records
- RUNTIME
- The time, in seconds, from startup to shutdown
- DASDIO
- The total number of DASD I/Os for the database manager session
- LPAGBUFF
- The total number of times that the database manager looked at a page
buffer
- CENTURY
- The century numbers of the dates from the termination records.
Each row of the SYSDETAIL table contains selected data from one
initialization, operator or checkpoint accounting record. The following
information is inserted into its columns:
- SQLNAME
- The VM user ID of the database machine
- TYPE
- INIT is inserted if the row describes an initialization record, and SYSTEM
is inserted if the row describes an operator or checkpoint record
- DATE
- The dates from the operator/checkpoint or initialization records
- TIME
- The times from the operator/checkpoint or initialization records
- RUNTIME
- If the value in TYPE is INIT, this value shows the amount of time for the
initialization process to finish (in seconds); if the value in TYPE is
SYSTEM, this value contains binary zeros
- CPUTIME
- This column contains the processor time used (in milliseconds)
- LPAGBUFF
- The number of times the agent (represented by the accounting record)
looked into a page buffer
- CENTURY
- The century numbers of the dates from the initialization records.
Each row of the USERDETAIL table contains selected data from one user
accounting record, either from a local or a remote processor. The
following information is inserted into its columns:
- SQLNAME
- The VM user ID of the database machine
- CPUSER
- The VM user ID of the user machine accessing the application server (for
VSE guests in batch and ICCF environments, it contains the job name of the
user partition)
- SQLUSER
- The authorization ID that was established, explicitly or implicitly,
during the connect process
- USERDATA
- The installation-supplied accounting data. If no accounting exit
was coded to supply installation-dependent data, you can omit this column when
defining the table. If you use the VM/ESA operating system, the first 4
bytes of this column
contain the CMS work unit ID. If you have your own accounting exit,
and it uses the first 4 bytes of this column, it will write over the CMS work
unit ID. If you have VSE guest sharing, this column contains the
following information for online environments:
- The CICS transaction ID
- The CICS terminal operator ID (if available)
- The CICS terminal ID (if available)
- Any data you supply through your own cancel exit.
- DATE
- The dates from the user records
- TIME
- The times from the user records
- PNAME
- The name of the package that was last active for the application
- ATIME
- The active time (that is, the time that the user was connected to an
agent) in seconds
- CPUTIME
- The processor time used (in milliseconds)
- ULPAGBUF
- The number of times the agent looked into a page buffer.
- CENTURY
- The century numbers of the dates from the user records.
Each row of the REMDETAIL table contains selected data from one remote
user accounting record. The columns are described as follows:
- SQLNAME
- The VM user ID of the database machine (application server)
- CPUSER
- The VM user ID of the user machine accessing the application server (for
VSE guests in batch and ICFF environments, the job name of the user partition)
- SQLUSER
- The authorization ID that was established, explicitly or implicitly,
during the connect process
- DATE
- The dates from the remote user records
- TIME
- The times from the remote user records
- LUWID
- The qualified LUNAME, the sequence number, and the instance number
- CENTURY
- The century numbers of the dates from the remote user records.
If you have created the tables described above, you can use the DBS utility
to load the accounting records into the tables, as shown in Figure 77.
The ARIACC1 file contains the accounting records. The file can be
the VM system accounting file. The DBS utility selects the DB2 Server
for VM records from the file by using positions 75-78 to identify the
records.
Figure 77. Example DBS Utility Commands to Load Accounting Tables
CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;
SET ERRORMODE CONTINUE;
DATALOAD TABLE(SQLDETAIL) IF POS(75-78)='TSQL'
SQLNAME 1-8 CHAR
DATE 41-46 CHAR
TIME 47-52 CHAR
RUNTIME 61-64 FIXED
DASDIO 65-68 FIXED
LPAGBUFF 69-72 FIXED
CENTURY 73-74 CHAR NULL IF POS(73-74) = 0
DATALOAD TABLE(SYSDETAIL) IF POS(75-78)='ISQL'
SQLNAME 1-8 CHAR
TYPE 17-24 CHAR
DATE 41-46 CHAR
TIME 47-52 CHAR
RUNTIME 61-64 FIXED
CPUTIME 65-68 FIXED
LPAGBUFF 69-72 FIXED
CENTURY 73-74 CHAR NULL IF POS(73-74) = 0
DATALOAD TABLE(SYSDETAIL) IF POS(75-78)='OSQL'
SQLNAME 1-8 CHAR
TYPE 17-24 CHAR
DATE 41-46 CHAR
TIME 47-52 CHAR
RUNTIME 61-64 FIXED
CPUTIME 65-68 FIXED
LPAGBUFF 69-72 FIXED
CENTURY 73-74 CHAR NULL IF POS(73-74) = 0
DATALOAD TABLE(SYSDETAIL) IF POS(75-78)='CSQL'
SQLNAME 1-8 CHAR
TYPE 17-24 CHAR
DATE 41-46 CHAR
TIME 47-52 CHAR
RUNTIME 61-64 FIXED
CPUTIME 65-68 FIXED
LPAGBUFF 69-72 FIXED
CENTURY 73-74 CHAR NULL IF POS(73-74) = 0
DATALOAD TABLE(USERDETAIL) IF POS(75-78)='USQL'
SQLNAME 1-8 CHAR
CPUSER 9-16 CHAR
SQLUSER 17-24 CHAR
USERDATA 25-40 CHAR
DATE 41-46 CHAR
TIME 47-52 CHAR
PNAME 53-60 CHAR
ATIME 61-64 FIXED
CPUTIME 65-68 FIXED
ULPAGBUF 69-72 FIXED
CENTURY 73-74 CHAR NULL IF POS(73-74) = 0
DATALOAD TABLE(REMDETAIL) IF POS(75-78)='RSQL'
SQLNAME 1-8 CHAR
CPUSER 9-16 CHAR
SQLUSER 17-24 CHAR
DATE 25-30 CHAR
TIME 31-36 CHAR
LUWID 37-63 CHAR
CENTURY 73-74 CHAR NULL IF POS(73-74) = 0
INFILE(ARIACC1)
COMMIT WORK;
SELECT * FROM SQLDETAIL;
SELECT * FROM SYSDETAIL;
SELECT * FROM USERDETAIL;
SELECT * FROM REMDETAIL;
|
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]