The following table provides details about event types and subtypes.

Table 24-21 Event types and subtypes

Audit event	Event type value	Event subtype value	Details
System administrator security events	SYS	STR	HiRDB startup
		STP	HiRDB termination
		MOD	Auditor registration or audit trail table creation (pdmod command)
		ARM	Audit trail file deletion (pdaudrm command)
		ABG	Audit trail collection startup (pdaudbegin command, HiRDB startup)
		AEN	Audit trail collection termination (pdaudend command, HiRDB termination)
		OVW	Start of audit trail file overwriting
		CLK	Transition to consecutive certification failure account lock state
		CUL	Release of consecutive certification failure account lock state
		PLK	Transition to password-invalid account lock state
		PUL	Release of password-invalid account lock state
		SPR	Modification of an operand related to the connection security facility
		ULK	Execution of the pdacnlck command
Auditor security events	AUD	ALD	Data load to the audit trail table by the auditor (pdload command)
		ATL	Data load to the audit trail table by HiRDB (pdload command)
		ASW	Audit trail file swap (pdaudswap command)
		CRT	Audit event definition (CREATE AUDIT)
		DRP	Audit event deletion (DROP AUDIT)
		GRT	Auditor password change (GRANT AUDIT)
		ATB	Restart of the facility for automatically loading audit trail table data (pdaudatld -b command)
		ATT	Stopping of the facility for automatically loading audit trail table data (pdaudatld -t command, data loading failure)
		APT	Output to the audit log output file for JP1/NETM/Audit (pdaudput command)
Session security events	SES	CNT	Connection to HiRDB (CONNECT statement)
		ATH	User modification (SET SESSION AUTHORIZATION statement)
		DIS	Disconnection from HiRDB (DISCONNECT statement)
Privilege management events	PRV	GRT	User privilege addition (GRANT statement)
			Password change (adds information indicating a password change to the set modification type for the connection security facility)
		RVK	User privilege deletion (REVOKE statement)
Object definition events	DEF	CRT	Object definition (CREATE type SQL)
		DRP	Object deletion (DROP type SQL)
		ALT	Object modification (ALTER type SQL)
Object manipulation events	ACS	SEL	Table retrieval (SELECT statement)
		INS	Table row insertion (INSERT statement)
		UPD	Table row modification (UPDATE statement)
		DEL	Deletion of a row from a table (DELETE statement)
		PRG	Deletion of all rows from a table (PURGE TABLE statement)
		CAL	Execution of a stored procedure (CALL statement)
		LCK	Table lock control (LOCK TABLE statement)
		ASN	List creation (ASSIGN LIST statement)
		NXV	Return of the value generated by a sequence generator (NEXT VALUE expression)
Utility manipulation event	UTL	LOD	Database load utility (pdload command)
		ORG	Database reorganization utility (pdrorg command)
		EXP	Dictionary import/export utility (pdexp command)
			pddefrev command
		CST	Integrity check utility (pdconstck command)

The following table lists the operand values related to the security audit facility. The figure below that shows the data size for recording the operand values.

Table 24-22 Values of security audit facility operands

Operand	Data size	Stored value
pd_audit	1 byte	Y or N
pd_aud_file_name	Maximum 167 bytes	Name of HiRDB file system area used for audit trail files
pd_aud_max_generation_size	Maximum 4 bytes	1 to 5240
pd_aud_max_generation_num	Maximum 3 bytes	2 to 200
pd_aud_no_standby_file_opr	Maximum 10 bytes	down or forcewrite
pd_aud_async_buff_size	Maximum 7 bytes	0, 4096 to 6553600
pd_aud_async_buff_count	Maximum 4 bytes	1 to 6500
pd_aud_file_wrn_pnt	Maximum 6 bytes	0 to 100, 0 to 99
pd_audit_def_buffer_size#	Maximum 7 bytes	0 to 2000000
pd_aud_async_buff_retry_intvl#	Maximum 4 bytes	0 to 1000
pd_aud_sql_source_size#	Maximum 7 bytes	0 to 2000000
pd_aud_sql_data_size#	Maximum 7 bytes	0 to 1000000
pd_aud_auto_loading#	1 byte	Y or N

#

This operand was added through a version upgrade. If an audit trail file created in HiRDB version 08-02 or earlier is recorded in a post-version-upgrade audit trail table, the following specification values are assumed:

Operand name	Specification value assumed
pd_audit_def_buffer_size	0
pd_aud_async_buff_retry_intvl	50
pd_aud_sql_source_size	0
pd_aud_sql_data_size	0
pd_aud_auto_loading	N

Figure 24-9 Data format for output of security audit facility operand values

The following table provides details about the SQL codes or termination codes that indicate event success or failure.

Table 24-23 SQL code or termination code indicating event success or failure

Event	SQL code or termination code value	Meaning	Event success or failure	Remarks
Various SQLs	0 or greater	Success	S	--
	Negative	Failure	F
pdstart	0	Success	S	--
	4	Success	S
	8	Failure	F	An audit trail cannot be collected in the following cases: A failure occurred before the audit trail management server process started. A failure of the pdstart command caused the unit to terminate abnormally.
pdstop	0	Success	S	If a failure occurred after the audit trail management server process had terminated, a successful audit trail is output.
	4	Success	S	--
	8	Failure	F
pdmod (auditor registration, audit trail creation)	0	Success	S	--
	8	Failure	F
pdaudrm	0	Success	S	--
	8	Failure	F
pdaudbegin	0	Success	S	--
	4	Partial failure	U
	8	Failure	F	An audit trail cannot be collected in the following cases: All generations of audit trail files have been used. The pdaudbegin command failed because there is no generation of audit trail files that is in unload wait status.
pdaudend	0	Success	S	--
	8	Failure	F
pdacunlck	0	Success	S	--
	8	Failure	F
pdaudswap	0	Success	S	During overwriting of the audit trail file, the names of the audit trail files at the swapping source and swapping destination are output.
	8	Failure	F	During overwriting of the audit trail file, the name of the audit trail file at the swapping source is output. The NULL value is output for the audit trail file name at the swapping destination.
pdload	0	Success	S	--
	4	Success	S
	8	Failure	F
pddefrev	0	Success	S	--
	4	Success	S
	8	Failure	F
	12	Failure	F
pdrorg	0	Success	S	--
	4	Success	S
	8	Failure	F
pdexp	0	Success	S	--
	4	Success	S
	8	Failure	F
	12	Failure	F
pdconstck	0	Success	S	--
	4	Success	S	End of warning (there is an integrity violation)
	8	Failure	F	--
pdaudatld	0	Success	S	--
	4	Success	S	End of warning (treated as success because the facility for automatically loading data has been successfully stopped)
	8	Success	F	--

Legend:

--: Not applicable

The following table provides the information that is recorded when the connection security facility is used.

Table 24-24 Information that is output when the connection security facility is used

Facility	Event type	Event subtype	Modification type	Value that is output after modification
Permitted number of consecutive certification failures	SYS	SPR	ECNT	1-10 or NULL value
Account lock period			LCKM	10-44640, UNLIMITED, or NULL value
Minimum number of bytes for a password			MINL	1-15 or NULL value
Authorization identifier specification prohibition			USID	RESTRICT, UNRESTRICT, or NULL value
Single character-type specification prohibition			SMLN
Password change#	PRV or AUD	GRT	CPWD	NULL value

Note

The NULL value is output in the following cases:

• For a value that is to be output before or after a change, no value is found in the corresponding column in the SQL_SYSPARAMS dictionary table (for example, when a new setting is specified).
• For a NULL value
• When a value cannot be identified.

During an advance check of password character string restrictions, an audit trail is collected for each modification type. However, the NULL value is output for the value that is to be output before or after the change.

#

HiRDB cannot determine from the event type, event subtype, and SQL code only whether the password was actually changed during execution of a GRANT statement. Only when a registered user password is changed during execution of a GRANT statement is CPWD set as the modification type. The following table shows the modification types that are output when a password is changed.

Table 24-25 Modification types that are output when a password is changed

Does a password exist for the registered user?	GRANT CONNECT, DBA, or AUDIT execution result	Modification type
Password exists.	Password change	CPWD
	Password deletion	CPWD
	No password change	NULL value
Password does not exist.	Changes to Password exists.	CPWD
	Retains Password does not exist.	NULL value

Note

Users and passwords referred to here are those managed by HiRDB.

The following table shows whether audit trail table options are output.

Table 24-26 Audit trail table option output

Execution result	Failure cause	Is audit trail table option output?
Success	--	Output
Failure#	The target object does not exist.	Not output (NULL value)
	An error occurred because the target object is an audit trail table.	Output
	Error other than one listed above	Output (not output when information cannot be collected)

Legend:

--: Not applicable

#: To determine whether a table is an audit trail table when no audit trail table option is output, check the combination of the object owner and object name in the audit record entry, for example.

The following table provides access count details.

Table 24-27 Details about the access count

Access method		Access count
SQL	SELECT	Number of rows accessed (fetched until CLOSE was successful or failed) by the user on the result set using the SELECT statement. For block transfer, the number of rows in a block is treated as the access count. If multiple tables are specified for a query, the same access count is used for all the applicable tables. The access count is set to the NULL value in either of the following cases: Table is specified in a subquery (for details, see Table 24-28 Access count by subquery) Table is specified in the right-hand operand in the EXCEPT [ALL] set operation (for an example, see Figure 24-15 Output example of access count (part 6))
	INSERT	Number of rows inserted
	UPDATE	Number of rows updated
	DELETE	Number of rows deleted
Utility	pdload	Data load count per table
	pdrorg	Unload count per table and reload count per table
	pdconstck	Total number of key values resulting in limitation violation

Table 24-28 Access count by subquery

Specified location in table		Access count	Remarks
Scalar subquery	Where a value expression is permitted	NULL value	For an example, see Figure 24-16 Output example of access count (part 7)
Row subquery	Where a row value constructor is permitted	NULL value	For an example, see Figure 24-17 Output example of access count (part 8)
	SET clause in UPDATE statement	NULL value	For an example, see Figure 24-18 Output example of access count (part 9)
Table subquery	Right-hand term of IN predicate	NULL value	For an example, see Figure 24-19 Output example of access count (part 10)
	Right-hand term of qualified predicate	NULL value	For an example, see Figure 24-20 Output example of access count (part 11)
	EXISTS predicate	NULL value	For an example, see Figure 24-21 Output example of access count (part 12)
	Derived table in FROM clause	Non-NULL value#	For an example, see Figure 24-22 Output example of access count (part 13)

#

If the result of searching the derived table in the FROM clause is not returned directly to the user, the access count is the NULL value (for an example, see Figure 24-23 Output example of access count (part 14)).

Figure 24-10 Output example of access count (part 1) through Figure 24-23 Output example of access count (part 14) show output examples of the access count when the access method is SELECT.

Figure 24-10 Output example of access count (part 1)

Figure 24-11 Output example of access count (part 2)

Figure 24-12 Output example of access count (part 3)

Note: The actual table is the same as in Figure 24-11 Output example of access count (part 2).

Figure 24-13 Output example of access count (part 4)

Note: The actual table is the same as in Figure 24-11 Output example of access count (part 2).

Figure 24-14 Output example of access count (part 5)

Figure 24-15 Output example of access count (part 6)

Note: The actual table is the same as in Figure 24-14 Output example of access count (part 5).

Figure 24-16 Output example of access count (part 7)

Note: The actual table is the same as in Figure 24-14 Output example of access count (part 5).

Figure 24-17 Output example of access count (part 8)

Note: The actual table is the same as in Figure 24-14 Output example of access count (part 5).

Figure 24-18 Output example of access count (part 9)

Note: The actual table is the same as in Figure 24-14 Output example of access count (part 5).

Figure 24-19 Output example of access count (part 10)

Note: The actual table is the same as in Figure 24-14 Output example of access count (part 5).

Figure 24-20 Output example of access count (part 11)

Note: The actual table is the same as in Figure 24-14 Output example of access count (part 5).

Figure 24-21 Output example of access count (part 12)

Note: The actual table is the same as in Figure 24-14 Output example of access count (part 5).

Figure 24-22 Output example of access count (part 13)

Note: The actual table is the same as in Figure 24-14 Output example of access count (part 5).

Figure 24-23 Output example of access count (part 14)

Note: The actual table is the same as in Figure 24-14 Output example of access count (part 5).

(a) SQL data structure

The following figure shows the SQL data structure.

Figure 24-24 SQL data structure

Legend:

n: Indicates the number of SQLVARs that correspond to SQLN. For details about the structure of SQLDA (SQL descriptor area), see the HiRDB Version 9 UAP Development Guide.

Note 1: The number in parentheses ( ) indicates the size of the area (in bytes).

Note 2: The endian format to be recorded depends on the server's OS.

The HEADER area, SQLDA (SQL descriptor area), and the input data area, which comprise SQL data, are explained below.

HEADER area

The following figure shows the structure of the HEADER area. The table below shows details of the individual configuration elements.

Figure 24-25 HEADER area structure

Note: The number in parentheses ( ) indicates the size of the area (in bytes).

Table 24-29 Details of the configuration elements of the HEADER area

Level number#		Area name	Data type	Size	Description
1	1	HEADER	--	16	SQL data management information
	2	SQLDATALEN	unsigned long	4	Data size of SQL data before recording
	2	SQLARRAY	long	4	Number of array elements when data for multiple lines is specified using an array type variable. If an array type variable is not used, 1 is set.
	2	RESERVED	char	8	Area reserved by the system

Legend:

--: Not applicable

#

Indicates the inclusive relation in the HEADER area. In this case, the area indicated by level number 1 consists of areas indicated by level number 2.

SQLDA and the input data area

The following figure shows the structure of SQLDA and the input data area. For details about the structure of SQLDA (SQL descriptor area), see the HiRDB Version 9 UAP Development Guide.

Figure 24-26 Structure of SQLDA and the input data area

Legend:

n: Indicates the number of SQLVARs that correspond to SQLN.

r: Indicates the size of the input data area, which can be determined using the following formula:

input-data-area-size = SQL-data-size - HEADER-area-size - SQLDA-size

Note 1: The number in parentheses ( ) indicates the size of the area (in bytes).

Note 2: The dotted-line arrows indicate offsets from SQLDA.

(b) Input value structure

The structure of the input value that comprises the input data area differs according to the elements shown below. For details, see the relevant figure indicated in the Reference destination column.

Input value type	Is there an indicator variable?	Reference destination
Input value with a simple structure	Yes	Figure 24-27 Structure of input value with a simple structure (with indicator variables)
	No	Figure 24-28 Structure of input value with a simple structure (without indicator variables)
Input value with a repetition structure	Yes	Figure 24-29 Structure of input value with a repetition structure (with indicator variables)
	No	Figure 24-30 Structure of input value with a repetition structure (without indicator variables)
Input value in the fixed-size data format that uses an array	Yes	Figure 24-31 Structure of an input value that is a fixed-size data type and uses an array (with indicator variables)
	No	Figure 24-32 Structure of an input value that is a fixed-size data type and uses an array (without indicator variables)
Input value in the variable-size data format that uses an array	Yes	Figure 24-33 Structure of an input value that is a variable-size data type and uses an array (with indicator variables)
	No	Figure 24-34 Structure of an input value that is a variable-size data type and uses an array (without indicator variables)

When the input value has a simple structure

Figure 24-27 Structure of input value with a simple structure (with indicator variables)

Figure 24-28 Structure of input value with a simple structure (without indicator variables)

Legend:

d1 and d2: Indicate a data size. For details about data size, see Data codes and data size to be specified in the SQL descriptor area in the HiRDB Version 9 UAP Development Guide.

p1: Indicates an indicator variable size. Corresponds to SQLCOD of SQLVAR.

Note: The dotted-line arrows indicate offsets from SQLDA.

Explanation

Data and indicator variables have structures that match the data types specified in SQLCOD and SQLLEN of SQLVAR, or in SQLCOD and SQLLOBLEN of SQLVAR_LOB.

Data and indicator variables are stored in boundary-adjusted areas. Therefore, the size of each area also includes the size of the area that is generated by boundary adjustment.

For details about data description, see SQL data types and C language data description or SQL data types and COBOL language data description in the HiRDB Version 9 UAP Development Guide.

When the input value has a repetition structure

Figure 24-29 Structure of input value with a repetition structure (with indicator variables)

Figure 24-30 Structure of input value with a repetition structure (without indicator variables)

Legend:

x: Indicates the number of indicator variables.

x1 and x2: Indicate the number of data elements specified in the element count.

d3 and d4: Indicate the size of the data that corresponds to SQLSYS of SQLVAR.

Note 1: The number in parentheses ( ) indicates the size of the area (in bytes).

Note 2: The dotted-line arrows indicate offsets from SQLDA.

#1: Stores the indicator variables for the entire repetition structure.

#2: Stores the indicator variable of each element.

Explanation

When SQLXDIM of SQLVAR is 2 or greater, a number of elements is stored that equals the element count specified in the repetition specification.

The data structure matches the data type specified in SQLCOD of SQLVAR.

Data and indicator variables are stored in boundary-adjusted areas. Therefore, the size of each area also includes the size of the area that is generated by boundary adjustment.

For details about data description, see SQL data types and C language data description or SQL data types and COBOL language data description in the HiRDB Version 9 UAP Development Guide.

When the input value is a fixed-size data type and uses an array

Figure 24-31 Structure of an input value that is a fixed-size data type and uses an array (with indicator variables)

Figure 24-32 Structure of an input value that is a fixed-size data type and uses an array (without indicator variables)

Legend:

y: Indicates the number of indicator variables.

y1 and y2: Indicate the element count of the array that corresponds to SQLARRAY of the HEADER area.

d5 and d6: Indicate the size of the data that corresponds to SQLSYS of SQLVAR.

p5: Indicates the size of the indicator variable that corresponds to SQLCOD of SQLVAR.

Note: The dotted-line arrows indicate offsets from SQLDA.

Explanation

When SQLARRAY of the HEADER area is 2 or greater, a number of elements is stored that equals the element count specified in the array type.

Data and indicator variables have structures that match the data types specified in SQLCOD and SQLLEN of SQLVAR, or in SQLCOD and SQLLOBLEN of SQLVAR_LOB.

Data and indicator variables are stored in boundary-adjusted areas. Therefore, the size of each area also includes the size of the area that is generated by boundary adjustment.

For details about data description, see SQL data types and C language data description or SQL data types and COBOL language data description in the HiRDB Version 9 UAP Development Guide.

When the input value is a variable-size data type and uses an array

Figure 24-33 Structure of an input value that is a variable-size data type and uses an array (with indicator variables)

Figure 24-34 Structure of an input value that is a variable-size data type and uses an array (without indicator variables)

Legend:

y: Indicates the number of indicator variables.

y3 and y4: Indicate the element count of the array that corresponds to SQLARRAY of the HEADER area.

d7 and d8: Indicate data size.

p7: Indicates the size of the indicator variable that corresponds to SQLCOD of SQLVAR.

Note: The dotted-line arrows indicate offsets from SQLDA.

Explanation

When SQLARRAY of the HEADER area is 2 or greater, a number of elements is stored that equals the element count specified in the array type.

Data and indicator variables have structures that match the data types specified in SQLCOD and SQLLEN of SQLVAR, or in SQLCOD and SQLLOBLEN of SQLVAR_LOB.

Data and indicator variables are stored in boundary-adjusted areas. Therefore, the size of each area also includes the size of the area that is generated by boundary adjustment.

For details about the size of data and indicator variables, see Data codes and data size to be specified in the SQL descriptor area in the HiRDB Version 9 UAP Development Guide.

For details about data description, see SQL data types and C language data description or SQL data types and COBOL language data description in the HiRDB Version 9 UAP Development Guide.

(c) Output example

An example of SQL data that is output to an audit trail follows.

Data is added to an inventory table. The following table lists the embedded variables and data types used, as well as the content of the data:

Column name	Embedded variable	Data type	Data content
Product code	xpcode	char(5)	202M
Product name	xpname	char(17)	Polo shirt
Color	xcol	char(3)	Red
Unit price	xuntprc	int	3640
Quantity	xgqty	int	0
Quantity	igqty	short	-1

The following SQL statement is executed:

 
EXEC SQL INSERT INTO STOCK(PRODCODE,PRODNAME,COL,UNTPRC,SQTY)
  VALUES(:xpcode,:xpname,:xcol,:xuntprc,:xgqty:igqty);

When the SQL statement is executed, the SQL data in the audit trail is recorded as follows:

Explanation

1. Location where the HEADER area is stored.
2. Location where SQLDA is stored.
3. Location where the input data area is stored.
   The number of SQLVARs or SQLVAR_LOBs can be seen from SQLD (underlined area) of SQLDA. In this example, there are five data elements that correspond to SQLVAR or SQLVAR_LOB, and each of them is stored as follows:
   
   • The first SQLVAR shows that the data is CHAR type, 4 bytes long, and does not have an indicator variable. The data is stored as a 4-byte value in the area that is advanced by 60h (hexadecimal number) from SQLDA (32 30 32 4d).
   • The second SQLVAR shows that the data is CHAR type, 16 bytes long, and does not have an indicator variable. The data is stored as a 16-byte value in the area that is advanced by 64h (hexadecimal number) from SQLDA (83 7c 83 8d 83 56 83 83 83 63 20 20 20 20 20 20).
   • The third SQLVAR shows that the data is CHAR type, 2 bytes long, and does not have an indicator variable. The data is stored as a 2-byte value in the area that is advanced by 74h (hexadecimal number) from SQLDA (90 d4).
   • The fourth SQLVAR shows that the data is INTEGER type, 4 bytes long, and does not have an indicator variable. The data is stored as a 4-byte value in the area that is advanced by 78h (hexadecimal number) from SQLDA (00 00 0e 38).
   • The fifth SQLVAR shows that the data is INTEGER type, 4 bytes long, and has an indicator variable. The data is stored as a 2-byte value in the area that is advanced by 7ch (hexadecimal number) from SQLDA (ff ff).