B The Oracle SMF Interface

The IBM System Management Facility (SMF) provides a facility for users to collect and record a variety of system and job-related information. SMF formats the information into a number of records . By creating analysis and report routines, installations can use the information in SMF records to track system usage.

The gateway service uses the standard SMF interface to write user records to SMF data sets . These user records contain gateway service accounting and information allowing gateway installation sites to charge individual users for the resources they use.

DB2 also uses SMF to collect and record data. DB2 uses record types 100, 101, and 102. Refer to your DB2 documentation for more information.

This appendix includes the following sections:

Activating SMF Records
Events Generating SMF Records
Interpreting an Oracle SMF Record
ORAFMTO Sample Formatting Program

B.1 Activating SMF Records

SMF recording is activated by updating the SMFPRMxx member of SYS1.PARMLIB to include the SYS or SUBSYS option. These options record the gateway user record type . If the SUBSYS option is used, then the SUBSYS name must match the OSDI subsystem name specified by the OSDI startup parameter. Refer to the IBM documentation on System Management Facilities for information about implementing SMF.

B.1.1 Specifying the Oracle Gateway Record Type

The default gateway user record type is 0 (zero). A zero for this parameter indicates that no SMF statistics record is to be written. You can override the default to any value between 128 and 255 by adding the SMF_STAT_RECNO (abbreviation is SMFSTRCN) to the OSDI gateway region parameter file. The SMF record number that is chosen must not be the same as the number that is used by any other z/OS software.

Oracle recommends using SFM record number 199, but any available record number between 128 and 255 may be used. Due to the differences in the report formatting programs, OSDI gateways and the MPM gateway should use different SMF record numbers.

If this parameter is not specified, or if zero is specified, then no SMF statistics connection or recording is done. This saves some CPU overhead and saves the overhead of the SMF write itself (which is mostly asynchronous work done by the SMF address space, and the in-line overhead is mostly just moving data into SMF buffers).

B.1.1.1 Using the OSDI SMF_STAT_RECNO Parameter

To override the default record type in the SMFPRMxx member, use the following syntax:

SMF_STAT_RECNO | SMFSTRCN

SMF_STAT_RECNO can be added as an OSDI parameter to the OSDI gateway region parameter data set to override the default record number 0. In the following example, 199 is the new SMF record type:

SMF_STAT_RECNO(199)

B.1.2 Starting SMF Recording

SMF recording of gateway accounting information starts automatically at startup if SMF is activated and the gateway record type is specified in the SMFPRMxx member .

Because the standard system default record types activated for SMF are 128 through 255, and because the recommended value for the gateway service (199) is within this range, many sites automatically begin SMF recording of gateway records when the gateway is installed .

If the SMF_STAT_RECNO is added or modified in the OSDI gateway region parameter file while the gateway is active, then the service must be stopped and restarted for the parameter to take effect.

B.1.3 Stopping SMF Recording

The OSDI SMF_STAT_RECNO parameter can be used to stop SMF recording for Oracle. To stop SMF recording for Oracle regardless of what your system tables specify, use:

SMF_STAT_RECNO (0)

or take the default of 0 (zero). The service must be stopped and restarted for this parameter to take effect.

B.2 Events Generating SMF Records

After SMF recording is turned on, an SMF record is written each time a user logs off (normal termination or SMFINV set to SMFNORM), provided SMF is activated when the user logs on.

SMF records are also written on an abend or cancellation of a job if SMFINV=SMFABORT.

If the z/OS system crashes, then SMF records are not written and the information is lost.

B.3 Interpreting an Oracle SMF Record

To interpret an Oracle SMF record, you first need to dump the SMF data set to a sequential data set. You can then write a program that does all of the following:

Reads the sequential data set
Selects only records with the Oracle record number
Accesses the Oracle SMF record fields using the provided DSECT
Prints the statistics

A sample program named ORAFMTO is provided in the SRCLIB library that you can customize for your installation. Refer to "ORAFMTO Sample Formatting Program" for more information .

The Assembler copy file, ORASMFO, contains DSECTS that map and document the gateway SMF record fields. The ORASMFO data set member resides in the gateway SRCLIB library.

The ORASMF file is divided into the following sections:

The standard record header section, which contains offsets and lengths of the Net and accounting sections
The correlation section
The OSDI section
The database engine section
The z/OS accounting section
The Net section (if applicable), which contains information about the network origin of clients on an Oracle Net for z/OS TCP/IP protocol network (IBM or SNS/TCPaccess)
The z/OS accounting section (if applicable)

Not all sections are present in all SMF records. For example, the z/OS accounting section is present only in SMF records for batch and TSO users. When a section is present, the SMF record header contains the correct length for that section, which might be release dependent, and the count field, which contains 1. The length field for nonexistent sections contains 0.

B.3.1 Contents of the SMF Header Section

Table C-1 contains brief descriptions for the labels in the SMF header section. For a complete layout of the contents of the SMF header section, refer to the DSECT.

Table B-1 Contents of the SMF Header Section

ORASMF0 Label	Description
`SMFHDR`	Standard SMF header
`SMFHLEN`	Total length of the SMF record
`SMFHSEG`	Segment descriptor = 0
`SMFHSIN`	SYS IND = X'80' Subsystem information to follow SYS IND = x'40' Subtype format record
`SMFHREC`	Record type default = 204 (decimal)
`SMFHTIM`	Timestamp, time (0.01 seconds since midnight)
`SMFHDAT`	Timestamp, date (0cyyydddf) c=0 for 19xx, c=1 for 20xx
`SMFHSYS`	System id
`SMFHSSI`	OSDI Subsystem id
`SMFHSUB`	Record subtype; 1 = accounting record
`SMFSRVC`	OSDI service name
`SMFSESID`	OSDI session id
`SMFHRV1`	Reserved
`SMFNETO`	Offset to Net section
`SMFACTO`	Offset to z/OS accounting section
`SMFHRV2`	Reserved
`SMFNETL`	Length of Net section
`SMFACTL`	Length of z/OS accounting section
`SMFHRV3`	Reserved

B.3.2 Contents of the SMF Correlation Section

Table B-2 contains brief descriptions for the labels in the SMF correlation section.

Table B-2 Contents of the SMF Correlation Section

ORASMF Label	Description
`SMFAUTH`	Authorization id = TSO logon id Batch userid on jobcard CICS USERID, TERM-ID,TRANS-ID, PROGRAM-ID, or OPID
`SMFCORI`	Correlation id = for TSO, logon id for batch, jobname for CICS, jobname Not valid for Oracle Net
`SMFCONN`	Connection type (TSO,BATCH,CICS,VTAM,TCP/IP,IMS)
`SMFASID`	Users address space id (not valid for Oracle Net)
`SMFOUSR`	Gateway logon id
`SMFTNAME`	Originating terminal id (if available)
`SMFPNAME`	Originating program name (if available)
`SMFGRPN`	RACF group name (if available)
`SMFJBID`	JES job identifier
`SMFENTRY`	RDR jobcard entry date (batch and TSO only). This field is equivalent to the SMF5RST field in the SMF job termination (type 5) record.
`SMFEDATE`	RDR jobcard entry date (batch and TSO only). This field is equivalent to the SMF5RSD field in the SMF job termination (type 5) record.

B.3.3 Contents of the SMF OSDI Data Section

Table B-3 Contents of the SMF OSDI Data Section

ORASMF0 Label	Description
`SMFTIM`	Beginning timestamp, time (0.01 second since midnight)
`SMFDAT`	Beginning timestamp, date (00yydddf), ending time and date in header
`SMFDTAI`	Data in
`SMFDTAO`	Data out
`SMFXMCPU`	Cross memory CPU time (TOD format)
`SMFRPCS`	RPC count
`SMFHWST`	High-water mark of storage used
`SMFINV`	Reason for invocation
`SMFNORM`	Normal termination
`SMFabort`	Clean up done

B.3.4 Contents of the SMF Database Engine Data Section

The database engine section is not applicable for Oracle Transparent Gateway for DB2.

Table B-4 Contents of the Database Engine Section

ORASMF Label	Description
`SMFLRC`	Logical read count
`SMFPRC`	Physical read count
`SMFLWC`	Logical writes
`SMFDMC`	DML COMMITs
`SMFDMR`	DML ROLLBACKs
`SMFDED`	DEADLOCKs
`SMFHDLN`	Length of SMF header

B.3.5 Contents of the SMF Oracle Net Data Section

Table B-5 Contents of the SMF Oracle Net Data Section

ORASMF Label	Description
`SMFNET`	Oracle Net section header
`SMFNETL`	Length of Net NIV information. The information contained in this section is specific to the Oracle Net driver in use. This information is variable length.
`SMFNETA`	Start of variable-length information

B.4 ORAFMTO Sample Formatting Program

A sample program, ORAFMTO, is provided with the gateway SMF interface to format gateway SMF records. ORAFMTO is an Assembler program that reads and formats SMF accounting records with the default gateway type of 199. It reads records from a variable-blocked sequential data set and writes the formatted records to a fixed block sequential data set with a logical record length of 132.

The sample ORAFMTO program is in the gateway SRCLIB library. The following members are included:

Member	Description
`ORAFMTCL`	contains sample JCL to compile and link `ORAFMTO`.
`ORAFMTGO`	contains sample JCL to run `ORAFMTO`.

ORAFMTO extracts and prints the following values from the gateway SMF records:

Table B-6 SMF Record Values

Value	Description
`SSN`	OSDI Subsystem name
`SERVICE`	OSDI Service name
`SMFAUTH`	Oracle userid assigned to the session using the database link.
`SMFCONN`	Connection type (TSO, BATCH, VTAM, TCP/IP)
`ORACLE ID`	User id assigned to the database link when the link is created. If the dblink is created without an associated userid, it defaults to the Oracle userid.
`DATE`	Start date of Oracle session
`TIME`	Start time of Oracle session
`CPU SECONDS`	Total CPU seconds used in the gateway address space. This number does not include time spent in the Oracle server address space, nor in the DB2 address space.
`LOG READS`	Not applicable to the gateway
`PHY READS`	Not applicable to the gateway
`LOG WRITES`	Not applicable to the gateway
`DMC`	Not applicable to the gateway
`DMR`	Not applicable to the gateway
`DED`	Not applicable to the gateway
`HI STG`	High-water mark of main storage used by the session

If the values are too large for the precision of their column, then they are shown as a series of asterisks.

Sample output from the ORAFMTO program:

SSN SMFAUTH SMFCONN ORACLE ID  DATE   TIME    TOTAL CPU SEC LOG READS LOG WRITES DMC DMR DDC DDR DED
--- ------- ------- -------- ------ ---------- ------------ --------- ---------- --- --- --- --- ---
ORA1 SSMITH BATCH   SCOTT    95.070 13:46:17.74       .755       152        23    6   2   1   1   0 ORA1 SSMITH BATCH   SCOTT    95.070 13:47:34.41       .193        88        23    6   2   1   1   0 ORA1 TJONES BATCH   SYSTEM   95.070 13:48:06.27       .269        95        23    6   2   1   1   0 ORA1 SSMITH BATCH   SCOTT    95.070 13:49:08.98       .084        36        10    3   2   0   0   0 ORA1 SSMITH BATCH   SCOTT    95.070 13:50:19.60       .120        21         2    1   0   0   0   0 ORA1        BATCH   SCOTT    95.070 13:52:04.67       .105        10        16    2   2   0   0   0 ORA1        BATCH   SCOTT    95.070 13:52:36.61      1.028       115         9    3   1   0   0   0 ORA1        BATCH   SCOTT    95.070 13:52:42.47       .576        71        23    5   3   1   1   0 ORA1        BATCH   SYSTEM   95.070  13:54:14.83      .110        10        16    2   2   0   0   0