QSYS2.Health_Size_Limits ()

Procedure definition:

CREATE PROCEDURE QSYS2.HEALTH_SIZE_LIMITS(
       IN ARCHIVE_OPTION INTEGER,
       IN REFRESH_CURRENT_VALUES INTEGER,
       IN OBJECT_SCHEMA VARCHAR(258),
       IN OBJECT_NAME VARCHAR(258),
       IN NUMBER_OBJECTS_LIMIT_TO_ARCHIVE 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_SIZE_LIMITS
       NOT DETERMINISTIC
       MODIFIES SQL DATA
       CALLED ON NULL INPUT
       EXTERNAL NAME 'QSYS/QSQHEALTH(SIZE)'
       PARAMETER STYLE SQL; 		

Service Program Name: QSYS/QSQHEALTH

Default Public Authority: *USE

Threadsafe: Yes

IBM i release

This procedure was added to IBM® i V5R4M0.

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.

Refresh_Current_Values

(Input) This option directs how the archive operation is done. This option is only valid with archive options 2 and 3.

The supported values are:

• 0 = No. Indicates that we capture the activity on the entire set of specified schemas and objects.
• 1 = Yes. Indicates that we only refresh the activity of the objects previously captured (based on the short names).
• 2 = None. Use the results from the prior call. A call must have been performed in this job before using this option

Object_Schema

(Input) The target schema or schemas for this operation. A single schema name can be entered. The ‘%' character can be used to direct the procedure to process all schemas with names that start with the same characters which appear before the ‘%'. When this parameter contains only the ‘%' character, the procedure processes all schemas within the database.

This name also affects the items refreshed if Refresh_Current_Values = 1.

Object_Name

(Input) The target object name for this operation. Only the ‘%' character is treated as a wildcard since an underscore is a valid character in a name. The name must be delimited, if necessary, and case sensitive.

This name also affects the items refreshed if Refresh_Current_Values = 1.

Number_Objects_Limit_to_Archive

(Input) The number of objects to save for each size limit.

Number_Of_Limits_Archive

(Input) The number of rows to save per object size 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 V5R4M0.

QSYS2.Health_Size_Limits() result set format:

"TIMESTAMP" TIMESTAMP NOT NULL,
LIMIT VARCHAR(2000) ALLOCATE(20) DEFAULT NULL,
CURRENT_VALUE FOR COLUMN "VALUE" BIGINT DEFAULT NULL,
PERCENT DECIMAL(5, 2) DEFAULT NULL, OBJECT_SCHEMA FOR COLUMN BSCHEMA VARCHAR(128) ALLOCATE(10) DEFAULT NULL,
OBJECT_NAME FOR COLUMN BNAME VARCHAR(128) ALLOCATE(20) DEFAULT NULL,
OBJECT_TYPE FOR COLUMN BTYPE VARCHAR(24) ALLOCATE(10) DEFAULT NULL,
SYSTEM_OBJECT_SCHEMA FOR COLUMN SYS_DNAME VARCHAR(10) ALLOCATE(10) DEFAULT NULL,
SYSTEM_OBJECT_NAME FOR COLUMN SYS_ONAME VARCHAR(10) ALLOCATE(10) DEFAULT NULL,
MAXIMUM_VALUE FOR COLUMN "MAXVALUE" BIGINT DEFAULT NULL,
LIMIT_ID INTEGER DEFAULT NULL,
PARTITION_NAME FOR COLUMN MBRNAME    VARCHAR(10) ALLOCATE(10) DEFAULT NULL,
"SCHEMA" VARCHAR(258) ALLOCATE(10) DEFAULT NULL,
OBJECT VARCHAR(258) ALLOCATE(10) DEFAULT NULL,
"REFRESH" INTEGER DEFAULT NULL  

LABEL ON COLUMN <result set>
( "TIMESTAMP" IS 'Timestamp', 
	LIMIT IS 'Limit', 
	CURRENT_VALUE IS 'Current             Value', 
	PERCENT IS 'Percent', 
	OBJECT_SCHEMA IS 'Object              Schema', 
	OBJECT_NAME IS 'Object              Name', 
	OBJECT_TYPE IS 'Object              Type', 
	SYSTEM_OBJECT_SCHEMA IS 'System              Object              Schema', 
	SYSTEM_OBJECT_NAME IS 'System              Object              Name', 
	MAXIMUM_VALUE IS 'Maximum             Value', 	
	LIMIT_ID IS 'Limit               ID',
	PARTITION_NAME IS 'Partition           Name', 
	"SCHEMA" IS 'Schema              Mask', 
	OBJECT IS 'Object              Mask', 
	"REFRESH" IS 'Refresh'); 

Limit Detail

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

SELECT * FROM QSYS2.SQL_SIZING WHERE SIZING_ID BETWEEN 15000 AND 15999;

Error Messages

Usage Notes

None

Related Information

None