QSYS2.Health_Database_Overview ()

Procedure definition:

CREATE PROCEDURE QSYS2.HEALTH_DATABASE_OVERVIEW(
       IN ARCHIVE_OPTION INTEGER,
       IN OBJECT_SCHEMA VARCHAR(258),
       IN NUMBER_OF_ITEMS_ARCHIVE INTEGER,
       IN OVERVIEW_SCHEMA VARCHAR(258),
       IN OVERVIEW_TABLE VARCHAR(258))
       DYNAMIC RESULT SETS 1
       LANGUAGE C
       SPECIFIC QSYS2.HEALTH_DATABASE_OVERVIEW
       NOT DETERMINISTIC
       MODIFIES SQL DATA
       CALLED ON NULL INPUT
       EXTERNAL NAME 'QSYS/QSQHEALTH(OVERVIEW)'
       PARAMETER STYLE SQL; 

Service Program Name: QSYS/QSQHEALTH

Default Public Authority: *USE

Threadsafe: Yes

IBM i release

This procedure was added to IBM® i in V5R4M0.

Parameters

Archive_Option

(Input) The type of operation to perform for the Db2 for i Health Center overview 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.

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.

Number_Of_Items_Archive

(Input) The number of rows to archive.

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.

Overview_Table

(Input) The table that contains the database overview archive.

This argument is ignored if the Archive_Option is 1.

Authorities

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

Result Set

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

The format of the result is as follows.

QSYS2.Health_Database_Overview () result set format:

"TIMESTAMP" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
SCHEMAS BIGINT NOT NULL , 
GRP01 CHAR(1) DEFAULT NULL , 
TABLES BIGINT NOT NULL , 
PARTITIONED_TABLES FOR COLUMN TABLESRT BIGINT NOT NULL , 
DISTRIBUTED_TABLES FOR COLUMN TABLES_DST BIGINT NOT NULL , 
MATERIALIZED_QUERY_TABLES FOR COLUMN TABLES_MAT BIGINT NOT NULL , 
PHYSICAL_FILES FOR COLUMN TABLESHY BIGINT NOT NULL , 
SOURCE_FILES FOR COLUMN TABLES_SRC BIGINT NOT NULL , 
GRP02 CHAR(1) DEFAULT NULL , 
VIEWS BIGINT NOT NULL , 
LOGICAL_FILES FOR COLUMN VIEWS_LGL  BIGINT NOT NULL , 
GRP03 CHAR(1) DEFAULT NULL , 
BINARY_RADIX_INDEXES FOR COLUMN INDEXES_BI BIGINT NOT NULL , 
EVI_INDEXES FOR COLUMN INDEXES_EV BIGINT NOT NULL , 
GRP04 CHAR(1) DEFAULT NULL , 
PRIMARY_KEY_CONSTRAINTS FOR COLUMN CSTSRI   BIGINT NOT NULL , 
UNIQUE_CONSTRAINTS FOR COLUMN CSTS_UNQ   BIGINT NOT NULL , 
CHECK_CONSTRAINTS FOR COLUMN CSTS_CHK   BIGINT NOT NULL , 
REFERENTIAL_CONSTRAINTS FOR COLUMN CSTS_RI    BIGINT NOT NULL , 
GRP05 CHAR(1) DEFAULT NULL , 
EXTERNAL_TRIGGERS FOR COLUMN TRGS_EXT   BIGINT NOT NULL , 
SQL_TRIGGERS FOR COLUMN TRGS_SQL   BIGINT NOT NULL , 
INSTEAD_OF_TRIGGERS FOR COLUMN TRGS_INSTD BIGINT NOT NULL , 
GRP06 CHAR(1) DEFAULT NULL , 
ALIASES BIGINT NOT NULL , 
DDM_FILES BIGINT NOT NULL , 
GRP07 CHAR(1) DEFAULT NULL , 
EXTERNALROCEDURES FOR COLUMN PROCS_EXT  BIGINT NOT NULL , 
SQLROCEDURES FOR COLUMN PROCS_SQL  BIGINT NOT NULL , 
GRP08 CHAR(1) DEFAULT NULL , 
EXTERNAL_SCALAR_FUNCTIONS FOR COLUMN FUNCS_EXTS BIGINT NOT NULL , 
EXTERNAL_TABLE_FUNCTIONS FOR COLUMN FUNCS_EXTT BIGINT NOT NULL , 
SOURCE_SCALAR_FUNCTIONS FOR COLUMN FUNCS_SRCS BIGINT NOT NULL , 
SOURCE_AGGREGATE_FUNCTIONS FOR COLUMN FUNCS_SRCA BIGINT NOT NULL , 
SQL_SCALAR_FUNCTIONS FOR COLUMN FUNCS_SQLS BIGINT NOT NULL , 
SQL_TABLE_FUNCTIONS FOR COLUMN FUNCS_SQLT BIGINT NOT NULL , 
GRP09 CHAR(1) DEFAULT NULL , 
SEQUENCES BIGINT NOT NULL , 
SQLACKAGES FOR COLUMN SQLPKGS    BIGINT NOT NULL , 
USER_DEFINED_DISTINCT_TYPES FOR COLUMN UDTS BIGINT NOT NULL , 
JOURNALS BIGINT NOT NULL , 
JOURNAL_RECEIVERS FOR COLUMN JRNRCV BIGINT NOT NULL , 
"SCHEMA" VARCHAR(258) ALLOCATE(10) NOT NULL  

LABEL ON COLUMN <result set>
( "TIMESTAMP" IS 'Timestamp' , 
	SCHEMAS IS 'Schemas' , 
	GRP01 IS 'Tables' , 
	TABLES IS 'Non-partitioned     tables' , 
	PARTITIONED_TABLES IS 'Partitioned         tables' , 
	DISTRIBUTED_TABLES IS 'Distributed         tables' , 
	MATERIALIZED_QUERY_TABLES IS 'Materialized        query               tables' , 
	PHYSICAL_FILES IS 'Physical            files' , 
	SOURCE_FILES IS 'Source              files' , 
	GRP02 IS 'Views' , 
	VIEWS IS 'Views' , 
	LOGICAL_FILES IS 'Logical             files' , 
	GRP03 IS 'Indexes' , 
	BINARY_RADIX_INDEXES IS 'Binary              radix               indexes' , 
	EVI_INDEXES IS 'Encoded             vector              indexes' , 
	GRP04 IS 'Constraints' , 
	PRIMARY_KEY_CONSTRAINTS IS 'PRIMARY KEY         constraints' , 
	UNIQUE_CONSTRAINTS IS 'UNIQUE              constraints' , 
	CHECK_CONSTRAINTS IS 'CHECK               constraints' , 
	REFERENTIAL_CONSTRAINTS IS 'Referential         constraints' , 
	GRP05 IS 'Triggers' , 
	EXTERNAL_TRIGGERS IS 'External            triggers' , 
	SQL_TRIGGERS IS 'SQL                 triggers' , 
	INSTEAD_OF_TRIGGERS IS 'INSTEAD OF          triggers' , 
	GRP06 IS 'Aliases' , 
	ALIASES IS 'Aliases' , 
	DDM_FILES IS 'DDM                 files' , 
	GRP07 IS 'Procedures' , 
	EXTERNALROCEDURES IS 'External            procedures' , 
	SQLROCEDURES IS 'SQL                 procedures' , 
	GRP08 IS 'Functions' , 
	EXTERNAL_SCALAR_FUNCTIONS IS 'External            scalar              functions' , 
	EXTERNAL_TABLE_FUNCTIONS IS 'External            table               functions' , 
	SOURCE_SCALAR_FUNCTIONS IS 'Source              scalar              functions' , 
	SOURCE_AGGREGATE_FUNCTIONS IS 'Source              aggregate           functions' , 
	SQL_SCALAR_FUNCTIONS IS 'SQL                 scalar              functions' , 
	SQL_TABLE_FUNCTIONS IS 'SQL                 table               functions' , 
	GRP09 IS 'Miscellaneous' , 
	SEQUENCES IS 'Sequences' , 
	SQLACKAGES IS 'SQL                 packages' , 
	USER_DEFINED_DISTINCT_TYPES IS 'User-defined        distinct            types' , 
	JOURNALS IS 'Journals' , 
	JOURNAL_RECEIVERS IS 'Journal             receivers' , 
	"SCHEMA" IS 'Schema              mask' ) ; 

Error Messages

Usage Notes

None

Related Information

None

Examples