Oracle® Transparent Gateway for DB2 Installation and User's Guide
10g Release 1 (10.1) for IBM z/OS (OS/390)
Part No. B13531-01
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 different 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.
The following topics are included:
SMF recording is activated by updating the SMFPRM
xx 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 abut implementing SMF.
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 Corporation 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 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).
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:
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, the service must be stopped and restarted for the parameter to take effect.
or take the default of 0 (zero). The service must be stopped and restarted for this parameter to take effect.
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.
If the z/OS system crashes, then SMF records are not written and the information is lost.
To interpret a 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 a 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.
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
|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 (.01 seconds since midnight)|
|SMFHDAT||Timestamp, date (0cyyydddf) c=0 for 19xx, c=1 for 20xx|
|SMFHSSI||OSDI Subsystem id|
|SMFHSUB||Record subtype; 1 = accounting record|
|SMFSRVC||OSDI service name|
|SMFSESID||OSDI session id|
|SMFNETO||Offset to Net section|
|SMFACTO||Offset to z/OS accounting section|
|SMFNETL||Length of Net section|
|SMFACTL||Length of z/OS accounting section|
Table 9-2 contains brief descriptions for the labels in the SMF correlation section.
Table B-2 Contents of the SMF Correlation Section
|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.|
Table B-3 Contents of the SMF OSDI Data Section
|SMFTIM||Beginning timestamp, time (.01 second since midnight)|
|SMFDAT||Beginning timestamp, date (00yydddf), ending time and date in header|
|SMFXMCPU||Cross memory CPU time (TOD format)|
|SMFHWST||High-water mark of storage used|
|SMFINV||Reason for invocation|
|SMFabort||Clean up done|
The database engine section is not applicable for Oracle Transparent Gateway for DB2.
Table B-5 Contents of the SMF Oracle Net Data Section
|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|
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:
|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
|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 any of the values are too large for the precision of their column, then they are shown as a series of asterisks.
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