When the option PROFILE is enabled during an application program's run, statistical data on the processed SQL statements may be obtained. To obtain results, however, the table SYSPROFILE (system table) of the LOCALSYSDBA must have been created when initializing the database. If this has not been done, the table has to be created first.
Definition of the table:
EXEC SQL
CREATE TABLE SYSPROFILE (
USERNAME CHAR (18) KEY,
PROGNAME CHAR (18) KEY,
MODNAME CHAR (18) KEY,
LANGUAGE CHAR (12) KEY,
LINENO FIXED ( 7) KEY,
PARSEID CHAR (12) BYTE KEY,
STMBEGIN CHAR (40),
RUNDATE DATE,
RUNCOUNT FIXED (10),
SECONDS FIXED (12,3) )
END-EXEC.
For every SQL statement, the beginning of statement, date of runtime, number of calls and accumulated realtime is entered into the table. The time consists of the time taken by the processing of a statement within an application program including all data conversions and time needed by the ADABAS kernel. The time needed to enter this information into the table SYSPROFILE, however, is not included. The key of a row consists of the following specifications: user name, program name, module name, language of the application program, and line number of the statement in the application program related to the source and the internal parseid. The parseid was integrated into the key in order to be able to distinguish dynamic statements. With the enabled TRACE option, the time required for writing the trace to a file affects the profiling. Therefore, it is not convenient to activate the PROFILE and TRACE options at the same time.
The entries to the SYSPROFILE table are made within the transactions of the application program. Therefore, they are only stored in the table when the application program issues a COMMIT WORK.
When the option is enabled, old entries made for username, program name, and language are always deleted at the beginning of the program. After the run, the table LOCALSYSDBA.SYSPROFILE may be looked up by means of QUERY and the stored data may be evaluated. The entries remain in the table until they are deleted explicitly or the program is restarted with the PROFILE option.