QSYS2.Health_Environmental_Limits ()

Procedure definition:

CREATE PROCEDURE QSYS2.HEALTH_ENVIRONMENTAL_LIMITS(
       IN ARCHIVE_OPTION INTEGER,
       IN NUMBER_OF_LIMITS_ARCHIVE INTEGER,
       IN LIMIT_SCHEMA VARCHAR(258),
       IN LIMIT_TABLE VARCHAR(258))
       DYNAMIC RESULT SETS 1
       LANGUAGE C
       SPECIFIC QSYS2.HEALTH_ENVIRONMENTAL_LIMITS
       NOT DETERMINISTIC
       MODIFIES SQL DATA
       CALLED ON NULL INPUT
       EXTERNAL NAME 'QSYS/QSQHEALTH(ENVIRON)'
       PARAMETER STYLE SQL; 	

Service Program Name: QSYS/QSQHEALTH

Default Public Authority: *USE

Threadsafe: Yes

IBM i release

This procedure was added to IBM® i 6.1.

Parameters

Archive_Option

(Input) The type of operation to perform for the DB2 for i Health Center activity detail.

The supported values are:

• 1 = Query only, no archive action is taken
• 2 = Archive only
• 3 = Create archive and archive
• 4 = Query the archive

Note: Option 1 produces a new result set. Options 2 and 3 simply use the results from the last Query option. Option 3 fails if the archive exists.

Number_Of_Limits_Archive

(Input) The number of rows to save per object health limit.

The archive can be used to recognize trends over time. To have meaningful historical comparisons, choose the row count size carefully. This argument is ignored if the Archive_Option is 1 or 4.

Limit_Schema

(Input) The schema that contains the database activity archive.

This argument is ignored if the Archive_Option is 1.

Limit_Table

The table that contains the database activity archive.

This argument is ignored if the Archive_Option is 1.

Authorities

To query an existing archive, *USE object authority is required for the Limit_Schema and Limit_Table. To create an archive, *CHANGE object authority is required for the Limit_Schema. To add to an archive, *CHANGE object authority is required for the Limit_Table.

When Archive_Option is 1 or 3, *USE object authority is required for the Object_Schema and for any objects which are indicated by Object_Name. When an object is encountered and the caller does not have *USE object authority, an SQL0462 warning is placed in the job log. The object is skipped and not included in the procedure result set.

Result Set

When Archive_Option is 1 or 4, a single result set is returned.

The format of the result is as follows. All these items were added for IBM i 6.1.

QSYS2.Health_Environmental_Limits() result set format:

"TIMESTAMP" TIMESTAMP NOT NULL, 
LIMIT VARCHAR(2000) ALLOCATE(20) DEFAULT NULL, 
HIGHWATER_MARK_VALUE FOR COLUMN HIMARK BIGINT DEFAULT NULL, 
WHEN_VALUE_WAS_RECORDED FOR COLUMN TIMEHIT TIMESTAMP NOT NULL,
PERCENT DECIMAL(5, 2) DEFAULT NULL, 
JOB_NAME VARCHAR(28) ALLOCATE(20) DEFAULT NULL, 
"CURRENT_USER" FOR COLUMN CUSER VARCHAR(128) ALLOCATE(10) DEFAULT NULL,
JOB_TYPE VARCHAR(26) ALLOCATE(20) DEFAULT NULL, 
MAXIMUM_VALUE FOR COLUMN MAXVAL BIGINT DEFAULT NULL,
JOB_STATUS VARCHAR(13) DEFAULT NULL, 
CLIENT_WRKSTNNAME FOR COLUMN "WRKSTNNAME" VARCHAR(255) DEFAULT NULL,
CLIENT_APPLNAME FOR COLUMN "APPLNAME" VARCHAR(255) DEFAULT NULL,
CLIENT_ACCTNG FOR COLUMN "ACCTNG" VARCHAR(255) DEFAULT NULL,
CLIENTROGRAMID FOR COLUMN "PROGRAMID" VARCHAR(255) DEFAULT NULL,
CLIENT_USERID FOR COLUMN "USERID" VARCHAR(255) DEFAULT NULL,
WHEN_LIMITS_ESTABLISHED FOR COLUMN TIMESET TIMESTAMP NOT NULL,
INTERFACE_NAME FOR COLUMN INTNAME VARCHAR(127) ALLOCATE(10) DEFAULT NULL,
INTERFACE_TYPE FOR COLUMN INTTYPE VARCHAR(63) ALLOCATE(10) DEFAULT NULL,
INTERFACE_LEVEL FOR COLUMN INTLEVEL VARCHAR(63) ALLOCATE(10) DEFAULT NULL,
LIMIT_ID INTEGER DEFAULT NULL

LABEL ON COLUMN <result set>
( "TIMESTAMP" IS 'Timestamp', 
	LIMIT IS 'Limit', 
	HIGHWATER_MARK_VALUE IS 'Largest             Value', 
	WHEN_VALUE_WAS_RECORDED IS 'Timestamp           When                Recorded', 
	PERCENT IS 'Percent', 
	JOB_NAME IS 'Job                 Name', 
	"CURRENT_USER" IS 'Current             User', 
	JOB_TYPE IS 'Job                 Type', 
	MAXIMUM_VALUE IS 'Maximum             Value', 
	JOB_STATUS IS 'Job                 Status', 
	CLIENT_WRKSTNNAME IS 'Client              Workstation         Name', 
	CLIENT_APPLNAME IS 'Client              Application         Name', 
	CLIENT_ACCTNG IS 'Client              Accounting          Code', 
	CLIENTROGRAMID IS 'Client              Program             Identifier', 
	CLIENT_USERID IS 'Client              User                Identifier', 
	WHEN_LIMITS_ESTABLISHED IS 'Timestamp           Limits              Established', 
	INTERFACE_NAME IS 'Interface           Name' , 
	INTERFACE_TYPE IS 'Interface           Type', 
	INTERFACE_LEVEL IS 'Interface           Level', 
	LIMIT_ID IS 'Limit               ID' );  

Limit Detail

The supported Database Health Center Environmental limits can be seen on any machine by executing this query:

SELECT * FROM QSYS2.SQL_SIZING WHERE SIZING_ID BETWEEN 18200 AND 18299;

Error Messages

None

Usage Notes

None

Related Information

None

Example results in System i® Navigator: