Database monitor SQL table format
The following figure shows the format used to create the QSYS/QAQQDBMN performance statistics table, that is shipped with the system.
CREATE TABLE QSYS/QQQDBMN ( QQRID DECIMAL(15, 0) NOT NULL DEFAULT 0 , QQTIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , QQJFLD CHAR(46) CCSID 65535 NOT NULL DEFAULT '' , QQRDBN CHAR(18) NOT NULL DEFAULT '' , QQSYS CHAR(8) NOT NULL DEFAULT '' , QQJOB CHAR(10) NOT NULL DEFAULT '' , QQUSER CHAR(10) NOT NULL DEFAULT '' , QQJNUM CHAR(6) NOT NULL DEFAULT '' , QQUCNT DECIMAL(15, 0) , QQUDEF VARCHAR(100) , QQSTN DECIMAL(15, 0) , QQQDTN DECIMAL(15, 0) , QQQDTL DECIMAL(15, 0) , QQMATN DECIMAL(15, 0) , QQMATL DECIMAL(15, 0) , QQTLN CHAR(10) , QQTFN CHAR(10) , QQTMN CHAR(10) , QQPTLN CHAR(10) , QQPTFN CHAR(10) , QQPTMN CHAR(10) , QQILNM CHAR(10) , QQIFNM CHAR(10) , QQIMNM CHAR(10) , QQNTNM CHAR(10) , QQNLNM CHAR(10) , QQSTIM TIMESTAMP , QQETIM TIMESTAMP , QQKP CHAR(1) , QQKS CHAR(1) , QQTOTR DECIMAL(15, 0) , QQTMPR DECIMAL(15, 0) , QQJNP DECIMAL(15, 0) , QQEPT DECIMAL(15, 0) , QQDSS CHAR(1) , QQIDXA CHAR(1) , QQORDG CHAR(1) , QQGRPG CHAR(1) , QQJNG CHAR(1) , QQUNIN CHAR(1) , QQSUBQ CHAR(1) , QQHSTV CHAR(1) , QQRCDS CHAR(1) , QQRCOD CHAR(2) , QQRSS DECIMAL(15, 0) , QQREST DECIMAL(15, 0) , QQRIDX DECIMAL(15, 0) , QQFKEY DECIMAL(15, 0) , QQKSEL DECIMAL(15, 0) , QQAJN DECIMAL(15, 0) , QQIDXD VARCHAR(1000) ALLOCATE(48) , QQC11 CHAR(1) , QQC12 CHAR(1) , QQC13 CHAR(1) , QQC14 CHAR(1) , QQC15 CHAR(1) , QQC16 CHAR(1) , QQC18 CHAR(1) , QQC21 CHAR(2) , QQC22 CHAR(2) , QQC23 CHAR(2) , QQI1 DECIMAL(15, 0) , QQI2 DECIMAL(15, 0) , QQI3 DECIMAL(15, 0) , QQI4 DECIMAL(15, 0) , QQI5 DECIMAL(15, 0) , QQI6 DECIMAL(15, 0) , QQI7 DECIMAL(15, 0) , QQI8 DECIMAL(15, 0) , QQI9 DECIMAL(15, 0) , QQIA DECIMAL(15, 0) , QQF1 DECIMAL(15, 0) , QQF2 DECIMAL(15, 0) , QQF3 DECIMAL(15, 0) , QQC61 CHAR(6) , QQC81 CHAR(8) , QQC82 CHAR(8) , QQC83 CHAR(8) , QQC84 CHAR(8) , QQC101 CHAR(10) , QQC102 CHAR(10) , QQC103 CHAR(10) , QQC104 CHAR(10) , QQC105 CHAR(10) , QQC106 CHAR(10) , QQC181 CHAR(18) , QQC182 CHAR(18) , QQC183 CHAR(18) , QQC301 VARCHAR(30) ALLOCATE(10) , QQC302 VARCHAR(30) ALLOCATE(10) , QQC303 VARCHAR(30) ALLOCATE(10) , QQ1000 VARCHAR(1000) ALLOCATE(48) , QQTIM1 TIMESTAMP , QQTIM2 TIMESTAMP , QVQTBL VARCHAR(128) ALLOCATE(10) , QVQLIB VARCHAR(128) ALLOCATE(10) , QVPTBL VARCHAR(128) ALLOCATE(10) , QVPLIB VARCHAR(128) ALLOCATE(10) , QVINAM VARCHAR(128) ALLOCATE(10) , QVILIB VARCHAR(128) ALLOCATE(10) , QVQTBLI CHAR(1) , QVPTBLI CHAR(1) , QVINAMI CHAR(1) , QVBNDY CHAR(1) , QVJFANO CHAR(1) , QVPARPF CHAR(1) , QVPARPL CHAR(1) , QVC11 CHAR(1) , QVC12 CHAR(1) , QVC13 CHAR(1) , QVC14 CHAR(1) , QVC15 CHAR(1) , QVC16 CHAR(1) , QVC17 CHAR(1) , QVC18 CHAR(1) , QVC19 CHAR(1) , QVC1A CHAR(1) , QVC1B CHAR(1) , QVC1C CHAR(1) , QVC1D CHAR(1) , QVC1E CHAR(1) , QVC1F CHAR(1) , QWC11 CHAR(1) , QWC12 CHAR(1) , QWC13 CHAR(1) , QWC14 CHAR(1) , QWC15 CHAR(1) , QWC16 CHAR(1) , QWC17 CHAR(1) , QWC18 CHAR(1) , QWC19 CHAR(1) , QWC1A CHAR(1) , QWC1B CHAR(1) , QWC1C CHAR(1) , QWC1D CHAR(1) , QWC1E CHAR(1) , QWC1F CHAR(1) , QVC21 CHAR(2) , QVC22 CHAR(2) , QVC23 CHAR(2) , QVC24 CHAR(2) , QVCTIM DECIMAL(15, 0) , QVPARD DECIMAL(15, 0) , QVPARU DECIMAL(15, 0) , QVPARRC DECIMAL(15, 0) , QVRCNT DECIMAL(15, 0) , QVFILES DECIMAL(15, 0) , QVP151 DECIMAL(15, 0) , QVP152 DECIMAL(15, 0) , QVP153 DECIMAL(15, 0) , QVP154 DECIMAL(15, 0) , QVP155 DECIMAL(15, 0) , QVP156 DECIMAL(15, 0) , QVP157 DECIMAL(15, 0) , QVP158 DECIMAL(15, 0) , QVP159 DECIMAL(15, 0) , QVP15A DECIMAL(15, 0) , QVP15B DECIMAL(15, 0) , QVP15C DECIMAL(15, 0) , QVP15D DECIMAL(15, 0) , QVP15E DECIMAL(15, 0) , QVP15F DECIMAL(15, 0) , QVC41 CHAR(4) , QVC42 CHAR(4) , QVC43 CHAR(4) , QVC44 CHAR(4) , QVC81 CHAR(8) , QVC82 CHAR(8) , QVC83 CHAR(8) , QVC84 CHAR(8) , QVC85 CHAR(8) , QVC86 CHAR(8) , QVC87 CHAR(8) , QVC88 CHAR(8) , QVC101 CHAR(10) , QVC102 CHAR(10) , QVC103 CHAR(10) , QVC104 CHAR(10) , QVC105 CHAR(10) , QVC106 CHAR(10) , QVC107 CHAR(10) , QVC108 CHAR(10) , QVC1281 VARCHAR(128) ALLOCATE(10) , QVC1282 VARCHAR(128) ALLOCATE(10) , QVC1283 VARCHAR(128) ALLOCATE(10) , QVC1284 VARCHAR(128) ALLOCATE(10) , QVC3001 VARCHAR(300) ALLOCATE(32) , QVC3002 VARCHAR(300) ALLOCATE(32) , QVC3003 VARCHAR(300) ALLOCATE(32) , QVC3004 VARCHAR(300) ALLOCATE(32) , QVC3005 VARCHAR(300) ALLOCATE(32) , QVC3006 VARCHAR(300) ALLOCATE(32) , QVC3007 VARCHAR(300) ALLOCATE(32) , QVC3008 VARCHAR(300) ALLOCATE(32) , QVC5001 VARCHAR(500) ALLOCATE(32) , QVC5002 VARCHAR(500) ALLOCATE(32) , QVC1000 VARCHAR(1000) ALLOCATE(48) , QWC1000 VARCHAR(1000) ALLOCATE(48) , QQINT01 INTEGER , QQINT02 INTEGER , QQINT03 INTEGER , QQINT04 INTEGER , QQSMINT1 SMALLINT , QQSMINT2 SMALLINT , QQSMINT3 SMALLINT , QQSMINT4 SMALLINT , QQSMINT5 SMALLINT , QQSMINT6 SMALLINT , QQ1000L CLOB(2M) ALLOCATE(48) ) ; RENAME QSYS/QQQDBMN TO SYSTEM NAME QAQQDBMN; LABEL ON TABLE QSYS/QAQQDBMN IS 'Database Monitor Physical File' ; LABEL ON COLUMN QSYS/QAQQDBMN (QQRID IS 'Record ID' , QQTIME IS 'Created Time' , QQJFLD IS 'Join Column' , QQRDBN IS 'Relational Database Name' , QQSYS IS 'System Name' , QQJOB IS 'Job Name' , QQUSER IS 'Job User' , QQJNUM IS 'Job Number' , QQUCNT IS 'Unique Counter' , QQUDEF IS 'User Defined Column' , QQSTN IS 'Statement Number' , QQQDTN IS 'Subselect Number' , QQQDTL IS 'Subselect Nested Level' , QQMATN IS 'Subselect Number of Materialized View' , QQMATL IS 'Subselect Level of Materialized View' , QQTLN IS 'Library of Table Queried' , QQTFN IS 'Name of Table Queried' , QQTMN IS 'Member of Table Queried' , QQPTLN IS 'Library of Base Table' , QQPTFN IS 'Name of Base Table' , QQPTMN IS 'Member of Base Table' , QQILNM IS 'Library of Index Used' , QQIFNM IS 'Name of Index Used' , QQIMNM IS 'Member of Index Used' , QQNTNM IS 'NLSS Table' , QQNLNM IS 'NLSS Library' , QQSTIM IS 'Start Time' , QQETIM IS 'End Time' , QQKP IS 'Key Positioning' , QQKS IS 'Key Selection' , QQTOTR IS 'Total Rows' , QQTMPR IS 'Number of Rows in Temporary' , QQJNP IS 'Join Position' , QQEPT IS 'Estimated Processing Time' , QQDSS IS 'Data Space Selection' , QQIDXA IS 'Index Advised' , QQORDG IS 'Ordering' , QQGRPG IS 'Grouping' , QQJNG IS 'Join' , QQUNIN IS 'Union' , QQSUBQ IS 'Subquery' , QQHSTV IS 'Host Variables' , QQRCDS IS 'Row Selection' , QQRCOD IS 'Reason Code' , QQRSS IS 'Number of Rows Selected' , QQREST IS 'Estimated Number of Rows Selected' , QQRIDX IS 'Number of Entries in Index Created' , QQFKEY IS 'Estimated Entries for Key Positioning' , QQKSEL IS 'Estimated Entries for Key Selection' , QQAJN IS 'Estimated Number of Joined Rows' , QQIDXD IS 'Advised Key Columns' , QQI9 IS 'Thread Identifier' , QVQTBL IS 'Queried Table Long Name' , QVQLIB IS 'Queried Library Long Name' , QVPTBL IS 'Base Table Long Name' , QVPLIB IS 'Base Library Long Name' , QVINAM IS 'Index Used Long Name' , QVILIB IS 'Index Used Library Name' , QVQTBLI IS 'Table Long Required' , QVPTBLI IS 'Base Long Required' , QVINAMI IS 'Index Long Required' , QVBNDY IS 'I/O or CPU Bound' , QVJFANO IS 'Join Fan Out' , QVPARPF IS 'Parallel Pre-Fetch' , QVPARPL IS 'Parallel Pre-Load' , QVCTIM IS 'Estimated Cumulative Time' , QVPARD IS 'Parallel Degree Requested' , QVPARU IS 'Parallel Degree Used' , QVPARRC IS 'Parallel Limited Reason Code' , QVRCNT IS 'Refresh Count' , QVFILES IS 'Number of Tables Joined' ) ; LABEL ON COLUMN QSYS/QAQQDBMN (QQRID TEXT IS 'Record ID' , QQTIME TEXT IS 'Time record was created' , QQJFLD TEXT IS 'Join Column' , QQRDBN TEXT IS 'Relational Database Name' , QQSYS TEXT IS 'System Name' , QQJOB TEXT IS 'Job Name' , QQUSER TEXT IS 'Job User' , QQJNUM TEXT IS 'Job Number' , QQUCNT TEXT IS 'Unique Counter' , QQUDEF TEXT IS 'User Defined Column' , QQSTN TEXT IS 'Statement Number' , QQQDTN TEXT IS 'Subselect Number' , QQQDTL TEXT IS 'Subselect Nested Level' , QQMATN TEXT IS 'Subselect Number of Materialized View' , QQMATL TEXT IS 'Subselect Level of Materialized View' , QQTLN TEXT IS 'Library of Table Queried' , QQTFN TEXT IS 'Name of Table Queried' , QQTMN TEXT IS 'Member of Table Queried' , QQPTLN TEXT IS 'Base Table Library' , QQPTFN TEXT IS 'Base Table' , QQPTMN TEXT IS 'Base Table Member' , QQILNM TEXT IS 'Library of Index Used' , QQIFNM TEXT IS 'Name of Index Used' , QQIMNM TEXT IS 'Member of Index Used' , QQNTNM TEXT IS 'NLSS Table' , QQNLNM TEXT IS 'NLSS Library' , QQSTIM TEXT IS 'Start timestamp' , QQETIM TEXT IS 'End timestamp' , QQKP TEXT IS 'Key positioning' , QQKS TEXT IS 'Key selection' , QQTOTR TEXT IS 'Total row in table' , QQTMPR TEXT IS 'Number of rows in temporary' , QQJNP TEXT IS 'Join Position' , QQEPT TEXT IS 'Estimated processing time' , QQDSS TEXT IS 'Data Space Selection' , QQIDXA TEXT IS 'Index advised' , QQORDG TEXT IS 'Ordering' , QQGRPG TEXT IS 'Grouping' , QQJNG TEXT IS 'Join' , QQUNIN TEXT IS 'Union' , QQSUBQ TEXT IS 'Subquery' , QQHSTV TEXT IS 'Host Variables' , QQRCDS TEXT IS 'Row Selection' , QQRCOD TEXT IS 'Reason Code' , QQRSS TEXT IS 'Number of rows selected or sorted' , QQREST TEXT IS 'Estimated number of rows selected' , QQRIDX TEXT IS 'Number of entries in index created' , QQFKEY TEXT IS 'Estimated keys for key positioning' , QQKSEL TEXT IS 'Estimated keys for key selection' , QQAJN TEXT IS 'Estimated number of joined rows' , QQIDXD TEXT IS 'Key columns for the index advised' , QQI9 TEXT IS 'Thread Identifier' , QVQTBL TEXT IS 'Queried Table, Long Name' , QVQLIB TEXT IS 'Queried Library, Long Name' , QVPTBL TEXT IS 'Base Table, Long Name' , QVPLIB TEXT IS 'Base Library, Long Name' , QVINAM TEXT IS 'Index Used, Long Name' , QVILIB TEXT IS 'Index Used, Libary Name' , QVQTBLI TEXT IS 'Table Long Required' , QVPTBLI TEXT IS 'Base Long Required' , QVINAMI TEXT IS 'Index Long Required' , QVBNDY TEXT IS 'I/O or CPU Bound' , QVJFANO TEXT IS 'Join Fan out' , QVPARPF TEXT IS 'Parallel Pre-Fetch' , QVPARPL TEXT IS 'Parallel Pre-Load' , QVCTIM TEXT IS 'Cumulative Time' , QVPARD TEXT IS 'Parallel Degree, Requested' , QVPARU TEXT IS 'Parallel Degree, Used' , QVPARRC TEXT IS 'Parallel Limited, Reason Code' , QVRCNT TEXT IS 'Refresh Count' , QVFILES TEXT IS 'Number of, Tables Joined');
Parent topic:
Database monitor: Formats