DB2 Server for VSE: System Administration

Maintaining Accounting Data

Accounting data, like any other data, can be loaded into tables and maintained by any DB2 Server for VSE 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.

Considerations for an Accounting Dbspace

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 ***.

Tables to Hold Accounting Data

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 82 shows the statements you could issue to create these three tables, here named SQLDETAIL (for termination records), SYSDETAIL (for initialization, operator and checkpoint records), and USERDETAIL (for user records).

Figure 82. 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), USERPART 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 DRDADETAIL(SQLNAME CHAR(8), ACCUSRID 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.

SQLDETAIL Table

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 jobname of the database partition
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.

SYSDETAIL Table

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 jobname of the database partition
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: The processor time used (in 300ths of a second)
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.

USERDETAIL Table

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 jobname of the database partition
USERPART: The jobname of the user partition for batch/ICCF (for rows that describe the accounting information for online users, USERPART is blank)
SQLUSER: The authorization ID that was established, explicitly or implicitly, during the connect process
USERDATA: The installation-supplied accounting data. If you have not coded an accounting exit, this column contains blanks for rows that contain accounting data for batch/ICCF users; for online users, other information is displayed. For more information, see the description of the user accounting records on page "User Records".
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 300ths of a second)
ULPAGBUF: The number of times the agent looked into a page buffer.
CENTURY: The century numbers of the dates from the user records.

DRDADETAIL Table

Each row of the DRDADETAIL table contains selected data from DRDA accounting records. The columns are described as follows:

SQLNAME: The jobname of the data partition (application server)
ACCUSRID: The access user ID of the application or interactive user (application requester) accessing the application server
SQLUSER: The authorization ID that was established, explicitly or implicitly, during the connect process
DATE: The dates from the DRDA accounting records
TIME: The times from the DRDA accounting records
LUWID: The qualified LUNAME, the sequence number, and the instance number
CENTURY: The century numbers of the dates from the DRDA accounting records.

Loading the Accounting Data

If you have created the tables described above, you can use the DBS utility to load the accounting records into the tables. For example, the commands shown below load the tables and list their contents. (The example shows ARIACC1 as the input file. Use ARIACC2 if you are loading records from the alternate accounting file.)

Figure 83. 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 USERPART 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 (DRDADETAIL) IF POS (75-78) = 'RSQL' SQLNAME 1-8 CHAR ACCUSRID 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 RECFM(FB) RECSZ(80) BLKSZ(2000) PDEV(DASD)) COMMIT WORK; SELECT * FROM SQLDETAIL; SELECT * FROM SYSDETAIL; SELECT * FROM USERDETAIL;

Converting VSAM ESDS Accounting File Records into VSAM Managed SAM Feature Records

If VSE/VSAM ESDS files are used to collect your accounting records, you will have to convert the records into VSAM managed SAM feature records before you load them into the tables. This is because for the DBSU DATALOAD function, only a local SAM file or VSAM managed SAM file can be processed as a DASD input file. Figure 84 shows sample JCL statements for converting VSAM ESDS accounting file records into VSAM managed SAM file records.

Figure 84. Job Control for Converting VSAM ESDS File Records to VSAM Managed SAM File Records

// DLBL ARIACT1,'ACCTFIL1',0,VSAM,CAT=SQLWK1,DISP=(OLD,KEEP) // DLBL ARIACT2,'ACCTFIL2',0,VSAM,CAT=SQLWK1,DISP=(OLD,KEEP) // DLBL ARIACC1,'ACCT.SAM.FILE1',0,VSAM,CAT=SQLWK1, C RECSIZE=80,RECORDS=(X,Y),DISP=(NEW,KEEP) // DLBL ARIACC2,'ACCT.SAM.FILE2',0,VSAM,CAT=SQLWK1, C RECSIZE=80,RECORDS=(X,Y),DISP=(NEW,KEEP) // EXEC IDCAMS,SIZE=AUTO REPRO INFILE(ARIACT1) - OUTFILE(ARIACC1 ENV(RECFM(FB) BLKSZ(2000) RECSZ(80)) REPRO INFILE(ARIACT2) - OUTFILE(ARIACC2 ENV(RECFM(FB) BLKSZ(2000) RECSZ(80)) /*

Note:

'ACCTFIL1' and 'ACCTFIL2' are the file ids of the VSAM ESDS accounting files used to collect accounting records.
ARIACC1 and ARIACC2 should be specified as the file name of the VSAM managed SAM file to be used as the IDCAMS REPRO command output files. After the conversion, by using the same set of DLBL statements, these files can be used as input files to the DBSU DATALOAD job control.
Set RECSZ to 80 and BLKSZ to 2000, they are the expected values for DBSU DATALOAD.
The example assumes that the VSAM managed SAM files ARIACC1 and ARIACC2 are implicitly defined to VSAM the first time they are opened.
DISP=(NEW,KEEP) indicates that the files will be reset at OPEN time. If there are still existing records in either ARIACC1 or ARIACC2 that are not yet processed, DATALOAD the records first before any new conversion.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]