Question & Answer
Question
What do the columns in the table nz_query_history contain? How are they populated?
Answer
The nz_query_history table materializes data from a number of in-memory tables. The core data here is better represented by the integrated query history process (more information on this can be found in the product documentation here:
https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.adm.doc/c_sysadm_qhist_collect_report.html)
nz_query_history still has a place as it adds additional fields on actual resource usage. If you want to know how to enable this, please see the following technote:
http://www.ibm.com/support/docview.wss?uid=swg21572029
The column definitions are as follows.
| Column | Description | Comments |
| QH_SESSIONID | Session ID | Internal ID, useful for joining |
| QH_PLANID | Plan ID | Internal ID, useful for joining |
| QH_CLIENTID | Sequential number, no customer use. | |
| QH_CLIIPADDR | Client IP Address (if known) | |
| QH_DATABASE | Database connected to | |
| QH_USER | Connected username | |
| QH_SQL | SQL text | First 63000 characters only. For full text see integrated query history tables. |
| QH_TSUBMIT | Time submitted (system time) | |
| QH_TSTART | Time execution started (system time) | |
| QH_TEND | Time query ended (system time) | |
| QH_PRIORITY | Query priority (numeric, 1-4) | |
| QH_PRITXT | Query priority (text description) | |
| QH_ESTCOST | Estimated query cost (milliseconds) | |
| QH_ESTDISK | Estimated query disk use (KB) | |
| QH_ESTMEM | Estimated memory usage (KB) | |
| QH_SNIPPETS | Number of query snippets | |
| QH_SNPTSDONE | Snippets completed | Typically the same as total snippets unless the query was aborted |
| QH_RESROWS | Number of rows returned | This is the number of row returned as a result. Stored procedures and external table operations will return just the final status typically which is what this value will refer to. |
| QH_RESBYTES | Number of bytes returned | number of bytes returned based on qh_resrows. |
| QH_CLIENT_USER_ID | Client user ID string as defined by the client system | |
| QH_CLIENT_APPLICATION_NAME | Client application name string as defined by the client system | |
| QH_CLIENT_WORKSTATION_NAME | Client workstation name string as defined by the client system | |
| QH_CLIENT_ACCOUNTING_STRING | Client accounting string as defined by the client system | |
| HOST_CPU_SECS | Host CPU seconds for this plan | |
| HOST_DISK_READ_SECS | Host Disk Read seconds for this plan | |
| HOST_DISK_WRITE_SECS | Host Disk Write seconds for this plan | |
| HOST_FABRIC_SECS | Host Fabric seconds for this plan | |
| SPU_CPU_SECS | SPU CPU seconds for this plan | Average over all blades |
| SPU_DISK_READ_SECS | SPU Disk Read seconds for this plan | Average over all blades |
| SPU_DISK_WRITE_SECS | SPU Disk Write seconds for this plan | Average over all blades |
| SPU_FABRIC_SECS | SPU Fabric seconds for this plan | Average over all blades |
| MAX_SPU_CPU_SECS | Max SPU CPU seconds for this plan | Value for the highest blade value amongst all blades. |
| MAX_SPU_DISK_READ_SECS | Max SPU Disk Read seconds for this plan | Value for the highest blade value amongst all blades. |
| MAX_SPU_DISK_WRITE_SECS | Max SPU Disk Write seconds for this plan | Value for the highest blade value amongst all blades. |
| MAX_SPU_FABRIC_SECS | Max SPU Fabric seconds for this plan | Value for the highest blade value amongst all blades. |
| SQB | Was this a short query? | |
| FIT | Failed to fit; bit pattern identifies any misfit reasons that occurred | Provides some detail on whether a query exceeded it's estimates and which estimate was exceeded. |
| PLANNER_ESTIMATE_SECS | Planner duration estimate in seconds | |
| SQB_ESTIMATE_SECS | SQB/prep duration estimate in seconds | |
| SPU_MEM_PEAK | Peak SPU memory use in pages | Value for the highest blade value amongst all blades. |
| HOST_MEM_PEAK | Peak host memory use in pages | |
| DOWNLOAD_RATE | average download rate in MB/sec if at least 1MB sent | Download refers to data coming INTO the system, not out. This will only be populated when loading data. |
| WEIGHT | average snippet weight for this plan (0.0 .. 1.0) | |
| GK_WAIT_SECS | seconds between being submitted and entering GRA. | |
| GRA_WAIT_SECS | seconds between entering GRA and entering SN (Snippet scheduler); does not include prep time | |
| SN_WAIT_SECS | sum of seconds waiting for each snippet to start | |
| EXECUTION_SECS | sum of seconds in snippet execution | |
| IDLE_SECS | sum of seconds in idle wait (for RESOURCE MAXIMUM); does not include greed | |
| ABORTED | plan aborted | Added in release 7.0. |
| SPU_DATA_DISK_READ_SECS | SPU Data Disk Read seconds for this plan | Added in release 7.0. Average over all blades |
| SPU_DATA_DISK_WRITE_SECS | SPU Data Disk Write seconds for this plan | Added in release 7.0. Average over all blades |
| SPU_TEMP_DISK_READ_SECS | SPU Temp Disk Read seconds for this plan | Added in release 7.0. Average over all blades |
| SPU_TEMP_DISK_WRITE_SECS | SPU Temp Disk Write seconds for this plan | Added in release 7.0. Average over all blades |
| MAX_SPU_DATA_DISK_READ_SECS | Max SPU Data Disk Read seconds for this plan | Added in release 7.0. Value for the highest blade value amongst all blades. |
| MAX_SPU_DATA_DISK_WRITE_SECS | Max SPU Data Disk Write seconds for this plan | Added in release 7.0. Value for the highest blade value amongst all blades. |
| MAX_SPU_TEMP_DISK_READ_SECS | Max SPU Temp Disk Read seconds for this plan | Added in release 7.0. Value for the highest blade value amongst all blades. |
| MAX_SPU_TEMP_DISK_WRITE_SECS | Max SPU Temp Disk Write seconds for this plan | Added in release 7.0. Value for the highest blade value amongst all blades. |
| SPOOLED_MB | number of mb spooled | Added in release 7.1. Possibly inaccurate. Use qh_resbytes instead. |
| DOWNLOADED_MB | number of mb downloaded | Added in release 7.1. Download refers to data coming INTO the system, not out. This will only be populated when loading data. |
| MAX_SPU_TEMP_DISK_PAGES_ALLOCATED | Max SPU Temp Disk pages allocated for this plan | Added in release 7.2. Value for the highest blade value amongst all blades. |
| SUBMIT_TIME | Timestamp when plan submitted to dbos (microseconds) | Added in release 7.2. |
| DONE_TIME | Timestamp when plan completed (microseconds) | Added in release 7.2. |
| PRIOR_PLAN | The plan id for the immediately previous related plan (sample scan, pre-broadcast) | Added in release 7.2. |
| PLANS_FOLLOW | Non-zero if related plans will follow | Added in release 7.2. |
| DHJ | True if the plan used a disk based hash join | Added in release 7.2. |
| SPU_MEM_ESTIMATE | SPU memory estimate in pages | Added in release 7.2. Estimate per blade |
| HOST_MEM_ESTIMATE | Host memory estimate in pages | Added in release 7.2. |
| PARSE_SECS | seconds spent in Postgres parse | Added in release 7.2. |
| REWRITE_SECS | seconds spent in Postgres rewrite | Added in release 7.2. |
| PLAN_CREATE_SECS | seconds spent in Postgres plan creation | Added in release 7.2. |
| PREP_SECS | seconds spent in running preps, including prep queuing | Added in release 7.2. |
| RECONSTITUTE_SECS | seconds spent loading cached transients | Added in release 7.2. |
| CACHE_SECS | seconds spent copying transients to cache | Added in release 7.2. |
| SETUP_SECS | sum of seconds in snippet setup | Added in release 7.2. |
| NULL_IO_SECS | sum of MICROseconds waiting for external I/O | Added in release 7.2. This field can overflow and become negative so treat with caution until resolved. Should be seconds, not microseconds. |
| GREED_SECS | sum of seconds delayed for balance (greed) | Added in release 7.2. |
| JOB | Job id | Added in release 7.2. |
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21982653