Usage Monitoring Databases

This section lists the data definitions that comprise the SiteAnalyzer and SmartMode Usage Monitoring Databases, and provides an explanation of the column values.

In this section: SMQUERY Database SMFROMS Database SMCOLUMNS Database SMRELATIONS Database SMBYS Database SMFUNCTIONS Database SMREQUESTS Database SMRPCS Database

SMQUERY Database

MVS name: MASTER.DATA(SMQUERY)

VM name: SMQUERY MASTER

Contains the analysis of the query. This the main record created for each request that is monitored or governed. A unique index is also created for the SMQUERY database.

Column	Value	Description
SMKEY	Alphanumeric, length=40	The request key.
SMDATE	Alphanumeric, length=8	The date in the format 'YYYYMMDD'.
SMTIME	Alphanumeric, length=6	The time in the format 'hhmmss'.
SMUSERID	Alphanumeric, length=30	The user identifier that made the connection to the server.
SMMREID	Alphanumeric, length=48	The user identifier connected to the MR Domain. The value of the field will be blank if the RES_ANALYZER value in the WebFOCUS console is not set to Yes. Setting this value to Yes will cause MR to send the GKE %MRE ID MR_userid with the request.
SMGKEID	Alphanumeric, length=48	The user identifier that used with the GKE %USERID subcommand. This field will be blank if the subcommand is not issued.
SMCONNID	Alphanumeric, length=48	The user identifier that made the connection to the Reporting Server.
SMGROUP	Alphanumeric, length=48	The security group identifier used for the current user ID.
SMSERVER	Alphanumeric, length=48	The server name as defined in the GKESET FOCEXEC file.
SMSERVTYPE	Integer	The server type as defined in the GKESET FOCEXEC file. 0 = FOCUS
SMCOLLECT	Alphanumeric, length=1	0 = Usage Monitor is turned on in GKTABLE 1 = Unable to estimate 2 = Cancelled 3 = ADVISE
SMALLROWS	Alphanumeric, length=1	0 = Incomplete result set 1 = Complete result set 2 = Incomplete, resource limited by the data engine.
SMCORRQRY	Alphanumeric, length=1	Indicates whether this is a correlated request. 0 = No 1 = Yes
SMSETALL	Alphanumeric, length=1	Indicates whether SET ALL is used. 0 = No 1 = Yes
SMELAPTIME	Integer	The amount of elapsed time used by the request in seconds.
SMCPUTIME	Integer	The amount of CPU time used by the request in milliseconds.
SMROWS	Integer	The number of result rows returned to client.
SMIOS	Integer	The number of input/output operations used by the server to satisfy the request.
SMRECLIMIT	Integer	The maximum number of result rows that has been set by the request or by the server. The value is zero if there is no limit.
SMHOLDFILE	Alphanumeric, length=8	The name of the HOLD file used.
SMRPCNAME	Alphanumeric, length=67	The remote procedure name if the request originated in a remote procedure.
SMUNIONS	Integer	The number of UNION statements in the request.
SMUNIONALLS	Integer	The number of UNION ALL statements in the request.
SMNUMFROMS	Integer	The number of data types used in the request. Also, the number of rows entered in SMFROMS database.
SMNUMCOLUMNS	Integer	The number of columns used in the request. An asterisk (*) will be counted as 1.
SMNUMREQUESTS	Integer	The number of rows stored in the SMREQUESTS database, based on 72 characters per line of the original request.
SMNUMRELATIONS	Integer	The number of relational clauses in the request.
SMNUMGROUPBYS	Integer	The number of GROUP BYs in the request.
SMNUMORDERBYS	Integer	The number of ORDER BYs in the request.
SMNUMBYS	Integer	The number of FOCUS BYs in the request.
SMNUMFUNCTIONS	Integer	The number of functions used in the request.
SMNUMREMOTES	Integer	The number of remote servers used in the request.
SMREADLIMIT	Integer	The read limit value.
SMVIEWSEG	Integer	The view segment number.
SMVIEWFLD	Integer	The view field number.
SMVIEWIDX	Integer	The view index number, or 0 for none.
SMCACHESIZE	Integer	The size of cache in 4K pages.
SMQUERYTYPE	Integer	The query type: 0=SQL 1=Table 2=TableF 8=Modify 25=Graph 28=Analyse 32=Match 66=Maintain
SMHYPERFOC	Alphanumeric, length=1	Reserved.
SMEXTSORT	Alphanumeric, length=1	Indicates whether external sort was used. 0=No 1=Yes
SMLIVE	Alphanumeric, length=1	Indicates whether request is interactive, batch, or network. T=Interactive N=Network S=Batch
SMOUTPUT	Alphanumeric, length=1	Indicates whether output is online or offline. 0=Online 1=Offline
SMSCREEN	Alphanumeric, length=1	Indicates whether set screen is used. 0=No 1=Yes
SMSUNAME	Alphanumeric, length=8	The name used in USE FOCUS command.
SMRECTYPE	Alphanumeric, length=1	Indicates the request type: S=SQL Select E=Execute C=Create (SQL) D=Drop (SQL) R=Alter (SQL) U=Update (SQL) I=Insert Into (SQL) D=Delete (SQL) T=Table (FOCUS) F=TableF (FOCUS) M=Match File (FOCUS) G=Graph (FOCUS) Y=Modify (FOCUS) N=Maintain
SMCONNADDR	Alphanumeric, length=32	Indicates the connection addressed. Not used.
SMCONNTYPE	Integer	Indicates the connection type. Not used.
SMRPCLNO	Integer	The RPC line number of the request stack run.
SMROWWIDTH	Integer	The total byte count of row retrieval.
SMRPCKEY	Alphanumeric, length=40	The key of a single RPC.
SMRPCNUM	Integer	The level of an RPC call.
SMHOLDFORMAT	Integer	The format type indicator. (See SAHOLDFORMAT.)
SMHOLDTYPE	Integer	The hold type indicator. (See SAHOLDTYPE.)
SMREMARKS	Alphanumeric, length=20	The comment available for the administrator's use.
SMRECORDS	Integer	The value of RECORDS read from a FOCUS database.
SMGETPAGES	Integer	The number of GETPAGES issued by DB2. This is for the FOCUS product and not used by iWay products.
SMMODE	Integer	The mode of execution. This is for the FOCUS product.
SMMDIFYTP	Integer	The type of MODIFY command. 0 if not a MODIFY.
SMPOOLTBL	Integer	Indicates wither POOL TABLES is enabled and in use.
SMJOBNAME	Alphanumeric, length=8	The name of the FOCUS batch job.
SMFOCUSREL	Alphanumeric, length=16	The release of FOCUS or iWay.
SMFEXLOC	Alphanumeric, length=254	The physical location of focexec. Value will be blank for an ad-hoc query.
SABANDWIDTH	Integer	The computed value of SMROWS * SMROWWIDTH.
SAMO	Alphanumeric, length=2	The numeric value of the month of the request.
SAYEAR	Alphanumeric, length=4	The numeric value of the year of the request.
SAMONTH	Alphanumeric, length=9	The character value of the month of the request.
SACONNTYPE	Alphanumeric, length=7	Indicates the connection method. Not used.
SAHOLDFORMAT	Alphanumeric, length=8	Indicates type of hold file being created: 1 DIF 2 ALPHA 3 LOTUS 4 WP 5 IFPS 6 SLYK 7 CALC 8 FOCUS 9 DOC 10 BINARY 11 POSTSC 12 PS 13 ROLLUP 14 HTML 15 HTMTAB 16 COMMA 17 FUSION 18 GIF 19 BMP 20 WMF 21 EXCEL 22 GRAPH 23 SCREEN 24 FXF 25 VIEWER 26 CLIP 27 RTF 28 PDF 29 EXL4 30 EXL2K 31 NOTES 32 TABS 33 FXSL 34 INTERNAL 35 WK1 36 DBASE 37 DBASEIII 0 UNKNOWN
SAHOLDTYPE	Alphanumeric, length=8	Indicates the kind of HOLD being performed: 1 HOLD 2 PCHOLD 3 SAVE 4 SAVB 0 UNKNOWN
SACPUTIME	Integer	The amount of CPU time used by the request in seconds.

SMFROMS Database

MVS name: MASTER.DATA(SMFROMS)

VM name: SMFROMS MASTER

Contains the list of databases that are queried against. This is always created as any request must have at least one data source.

Column	Value	Description
SMKEY	Alphanumeric, length=40	The request key.
SMSEGNUM	Integer	The sequence number.
SMNAME	Alphanumeric, length=66	The database name or data type name used in the request.
SMDBMS	Alphanumeric, length=8	The name of the interface type for passthru or the value EDA when using Master/Access files.
SMSUFFIX	Alphanumeric, length=8	The engine type of the source (for example, FOC, SQLDS, or DB2).
SMALIAS	Alphanumeric, length=66	The name defined by the USE command.
SMMORE	Alphanumeric, length=1	1=MORE FILE
SMUSE	Alphanumeric, length=1	1=via USE
SMSU	Alphanumeric, length=1	1=SU 0=ALLOCATED
SMSUNAME	Alphanumeric, length=8	The name of the FDS Server.
SMDBNAME	Alphanumeric, length=254	Physical name including path of the database. On MVS it is the dataset name of the database. This is for non-relational databases only.
SMMASLOC	Alphanumeric, length=254	Location of the MASTER used for the database. Blank if the request used Direct Passthru.

SMCOLUMNS Database

MVS name: MASTER.DATA(SMCOLMNS)

VM name: SMCOLMNS MASTER

Contains information about the columns used in queries. A select * against a relational database using passthru will not have a record added to SMCOLUMNS.

Column	Value	Description
SMKEY	Alphanumeric, length=40	The request key.
SMSEGNUM	Integer	The sequence number.
SMNAME	Alphanumeric, length=66	The database name or data type name used in the request.
SMCOLUMN	Alphanumeric, length=66	The column or field name used in the request.
SMDISTROWS	Alphanumeric, length=1	Indicates whether DISTINCT was used for this column. 0 = No 1 = Yes
SMALLCOLS	Alphanumeric, length=1	Indicates whether a SELECT * or FOCUS PRINT was used. 0 = No 1 = Yes
SMDEFINES	Alphanumeric, length=1	Indicates what type of temporary field is created (the default is relational). 0 = Unknown 1 = Real 2 = Permanent define 3 = Temporary define 4 = Compute
SMLITERAL	Alphanumeric, length=32	The literal used for column.
SMFORMAT	Alphanumeric, length=8	The format of the field or column.
SMUSAGE	Integer	The Master File usage of field or column. 0 = Unknown 1 = Integer 2 = Float 3 = Double 4 = Pack 5 = Zone 6 = Alpha 7 = Hex 8 = Exponent 9 = Date 11 = Text 12 = DBCS 13 = BLOB 14 = CLOB 15 = Time 16 = Datetime 23 = JDE_PACK
SMSIZE	Integer	The length of column in bytes.
SMDEC	Integer	The number of places to the right of the decimal.
SMIDXKEY	Alphanumeric, length=1	Is this an indexed column in a Master File? 0=No 1=Yes
SATYPE	Alphanumeric, length=16	Indicates the type of field: 1 Real field defined in the MFD 2 Field DEFINEd in the MFD 3 Field DEFINEd in the procedure 4 Field COMPUTEd in the procedure 0 Unknown
SAUSAGE	Alphanumeric, length=8	Indicates the format of the field: 1 INTEGER 2 FLOAT 3 DOUBLE 4 PACK 5 ZONE 6 ALPHA 7 HEX 8 EXPONENT 9 DATE 11 TEXT 12 DBCS 13 BLOB 14 CLOB 15 TIME 16 DATETIME 23 JDE_PACK 0 UNKNOWN

SMRELATIONS Database

MVS name: MASTER.DATA(SMRELTNS)

VM name: SMRELTNS MASTER

Tracks the operations used on the column. This will be updated only if the request has a where or if test.

Column	Value	Description
SMKEY	Alphanumeric, length=40	The request key.
SMSEGNUM	Integer	The sequence number.
SMOPERATOR	Alphanumeric, length=8	The operator used in a clause, such as '<' or '=.'
SMAOPERATOR	Alphanumeric, length=1	The arithmetic operator used in a clause, such as '+' or '*.'
SMANDOR	Alphanumeric, length=3	The connector in or between clauses.
SMRNAME	Alphanumeric, length=66	The database name or data type name used in the request.
SMRCOLUMN	Alphanumeric, length=66	The right column or field name used in the relation.
SMLNAME	Alphanumeric, length=66	The database name or data type name used in the request.
SMLCOLUMN	Alphanumeric, length=66	The left column or field name used in the relation.
SMNOT	Alphanumeric, length=1	Indicates whether the NOT keyword was used. 0 = No 1 = Yes
SMALL	Alphanumeric, length=1	Indicates whether the ALL keyword was used. 0 = No 1 = Yes
SMANY	Alphanumeric, length=1	Indicates whether the ANY keyword was used. 0 = No 1 = Yes
SMLITERAL	Alphanumeric, length=32	The literal used.
SMEXISTS	Alphanumeric, length=1	Indicates whether the EXISTS keyword was used. 0 = No 1 = Yes
SMRELTYPE	Alphanumeric, length=8	Indicates whether a WHERE, HAVING, or IF was used in this relation clause.
SMCORRELATED	Alphanumeric, length=1	Indicates whether this relation caused a correlation. 0 = No 1 = Yes
SMCORRCOLUMN	Alphanumeric, length=1	Indicates whether a column used caused a correlation. 0 = No 1 = Yes

SMBYS Database

MVS name: MASTER.DATA(SMBYS)

VM name: SMBYS MASTER

Contains the BY or ORDER BY column Name or number.

Column	Value	Description
SMKEY	Alphanumeric, length=40	The request key.
SMSEGNUM	Integer	The sequence number.
SMNAME	Alphanumeric, length=66	The database name or data type name used in the request.
SMCOLUMN	Alphanumeric, length=66	The column or field name used as the target of the SORT BY or GROUP BY.
SMCOLNBR	Alphanumeric, length=4	The column number used in an SQL ORDER BY.
SMORDER	Alphanumeric, length=1	A = Ascending sort D = Descending sort
SMBYTYPE	Alphanumeric, length=1	Indicates whether sorting or grouping was performed on the column. B = Sort G = SQL Group

SMFUNCTIONS Database

MVS name: MASTER.DATA(SMFNCTNS)

VM name: SMFNCTNS MASTER

Tracks functions used on columns. This is updated if the request used a function such as MIN, MAX, SUM or AVE.

Column	Value	Description
SMKEY	Alphanumeric, length=40	The request key.
SMSEGNUM	Integer	The sequence number.
SMALLROWS	Alphanumeric, length=1	Indicates whether '*' was used.
SMFUNCTION	Alphanumeric, length=12	The function used.
SMNAME	Alphanumeric, length=66	The database name or data type name used in the request.
SMCOLUMN	Alphanumeric, length=66	The column or field name used.
SMLITERAL	Alphanumeric, length=32	The literal used.

SMREQUESTS Database

MVS name: MASTER.DATA(SMREQSTS)

VM name: SMREQSTS MASTER

Contains the actual query that was monitored.

Note: SMREQUESTS is not populated with data in this release.

Column	Value	Description
SMKEY	Alphanumeric, length=40	The request key.
SMRPCNUM	Integer	The RPC number.
SMSEGNUM	Integer	The sequence number.
SMSQLLINE	Alphanumeric, length=72	A line of the request.

SMRPCS Database

MVS name: MASTER.DATA(SMRPCS)

VM name: SMRPCS MASTER

Contains information on any stored procedure or focexec execution if RPC monitoring is enabled.

Note: SMRPCS is not populated with data in this release.

Column	Value	Description
SMRPCKEY	Alphanumeric, length=40	Key for a single remote procedure.
SMRPCNUM	Integer	Remote procedure sequence in an execution chain.
SMRPCNAME	Alphanumeric, length=67	Name of an executed remote procedure.
WFRPCNAME	Alphanumeric, length=66	Name of an executed WebFOCUS remote procedure.
SMDATE	Alphanumeric, length=8	The date of the RPC run in format 'YYYYMMDD'.
SMTIME	Alphanumeric, length=6	The start time of the RPC run.
SMRPCNAME	Alphanumeric, length=66	Long name of an executed remote procedure. This is the physical name of the RPC.
SMUSERID	Alphanumeric, length=30	The user identifier that made the connection to the Reporting Server.
SMMREID	Alphanumeric, length=48	The user identifier that connected to the MR Domain.
SMGKEID	Alphanumeric, length=48	The user identifier that used with the GKE %USERID subcommand. This field will be blank if the subcommand is not issued.
SMCONNID	Alphanumeric, length=48	The user identifier that made the connection to the Reporting Server.
SMGROUP	Alphanumeric, length=48	The group identifier of the userid that made the connection to the Reporting Server. This will be blank for MR requests or if group identification is not available.
SMSERVER	Alphanumeric, length=48	SMSERVER is the server name used during the configuration process.
SMSERVERTYPE	Integer	The type of server. 1= Full Function server 2= WebFOCUS Reporting server 3=DM server.
SMCPUTIME	Integer	The total CPU time for the RPC in milliseconds.
SMELAPTIME	Integer	The total wall clock time in seconds.
SMIOS	Integer	The total IO for this RPC.
SMROWS	Integer	The total rows returned to the client.
SMROWWIDTH	Integer	The byte count of the widest row returned.
SMCONNADDR	Alphanumeric, length=32	The connection address.
SMCONNTYPE	Integer	The connection type. 1=TCP 2=SNA 3=IPX
SMRPCNUM	Integer	The parent SMRPCNUM. 0=Root RPC
SMJOINFLD	Alphanumeric, length=1	Join field, the element needed to join this database to SMQUERY database.
SMFEXLOC	Alphanumeric, length=254	Physical location of the rpc that was executed. Blank if the rpc was sent to the server from a remote location.
SMBASEDIR	Alphanumeric, length=254	WebFOCUS BASEDIR value sent with the request. Blank if the request was not sent from the WebFOCUS CGI or SERVLET.
SMAPPPATH	Alphanumeric, length=254	WebFOCUS app path value sent with the request. Blank if the rpc was not sent from the WebFOCUS CGI or SERVLET.
SMDOMAIN	Alphanumeric, length=254	WebFOCUS Domain name that the request originated from. Blank if the rpc was not sent from the WebFOCUS CGI or SERVLET.
SMWFRPCNAME	Alphanumeric, length=66	The descriptive or logical name of MR request. Blank if the rpc was not sent from the WebFOCUS CGI or SERVLET.
SMINCLUDED	Integer	Indicate whether the RPC was used on a -INCLUDE command or not. 1=Yes 0=No
SMRCSCHEDID	Alphanumeric, length=66	ReportCaster Scheduler ID.
SMLIVE	Alphanumeric, length=1	How the request is run. 0=interactive 1=batch 2=network 3=deferred
SMRCPROCID	Alphanumeric, length=66	ReportCaster process ID and name used to summit job.
SABANDWIDTH	Integer	The computed value of SMROWS * SMROWWIDTH.
SMCLIENTTYPE	Alphanumeric, length=32	The application that submitted the request: DMC=Data Management Console RMAT=Resource Management Administration Tool Resource Management=Resource Management pages of the Web Console
SMREFERERURL	Alphanumeric length=256	Referring URL of web page where request originated from.
SAMO	Alphanumeric, length=2	The numeric value of the month the request was executed.
SAYEAR	Alphanumeric, length=4	The numeric value of the year the request was executed.
SAMONTH	Alphanumeric, length=9	The character name of the month the request was executed.
SACONNTYPE	Alphanumeric, length=7	Indicates connection method used when the request was executed: 1 - TCP 2 - SNA 3 - IPX 0 - Unknown
SACPUTIME	Integer	The total CPU time for the RPC (in seconds).