List Statistics Collections (QDBSTLS, QdbstListStatistics) API
Required Parameter Group:
| 1 | Qualified user space name | Input | Char(20) |
| 2 | Format of output | Input | Char(8) |
| 3 | Input data | Input | Char(*) |
| 4 | Length of input data | Input | Binary(4) |
| 5 | Format of input data | Input | Char(8) |
| 6 | Error code | I/O | Char(*) |
Service Program Name: QDBSTMGR
Default Public Authority: *USE
Threadsafe: Yes
The List Statistics Collections (QDBSTLS, QdbstListStatistics) API allows to find out all of the columns and combination of columns for a given file member, which have statistics available and will optionally list those columns, not contained in any statistics collection. The generated list replaces any existing list in the user space.
Each returned list entry contains a number of different statistic data items, including the number of histogram ranges and the number of most frequent values, while detailed information for these two items can be retrieved using the List Statistics Collection Details (QDBSTLDS, QdbstListDetailStatistics) API, using the internal statistics ID returned by the QdbstListStatistics API.
The QdbstListStatistics API also allows to list statistics related attributes at the file member level, not related to a single statistics collection.
Section overview
- Authorities and Locks
- Required Parameter Group
- STIL0100 Input Format
- Valid Keys - Request Output
- Format of the Generated List
- Input Parameter Section
- Header Section
- List Data Section
- Column Description
- Field Descriptions
- Error Messages
- Related Information
Authorities and Locks
- User Space Authority
- *CHANGE
- User Space Library Authority
- *EXECUTE
- User Space Lock
- *EXCLRD
- ASP Device Authority
- *EXECUTE
- File Authority
- *OBJOPR
- File Library Authority
- *EXECUTE
- File Lock
- *SHRRD
Required Parameter Group
- Qualified user space name
- INPUT; CHAR(20)
The user space that is to receive the generated list, and the library in which it is located. The first 10 characters contain the user space name, and the second 10 characters contain the library name.
You can use these special values for the library name:
*CURLIB The job's current library or QGPL if the current library is not set. *LIBL The library list. *USRLIBL Libraries listed in the user portion of the library list.
- Format of output
- INPUT; CHAR(8)
The format of the statistics collections list to be returned. If format STOL0100 is specified, the fields that were selected by the caller will be returned for each statistics collection in the list. Possible format names are:
STOL0100 Statistics collections list with keyed return fields. Refer to Format of the Generated List and STOL0100 Output Format for more information.
- Input data
- INPUT; CHAR(*)
The buffer containing the input parameters according to the format of input data parameter. The buffer content has to start at a four-byte boundary.
- Length of input data
- INPUT; BINARY(4)
The length of the input data buffer provided.
- Format of input data
- INPUT; CHAR(8)
The format of the input data. Possible values are:
STIL0100 List statistics collections input parameters. Refer to STIL0100 Input Format for more information.
- Error code
- I/O; CHAR(*)
The structure in which to return error information. For the format of the structure, see Error code parameter.
STIL0100 Input Format
List statistics collections input parameters. See Field Descriptions for details of the fields listed.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | CHAR(10) | ASP device name |
| 10 | A | CHAR(10) | File name |
| 20 | 14 | CHAR(10) | File library name |
| 30 | 1E | CHAR(10) | File member name |
| 40 | 28 | CHAR(1) | Column option |
| 41 | 29 | CHAR(3) | Reserved |
| 44 | 2C | CHAR(48) | Continuation handle |
| 92 | 5C | BINARY(4) | Offset to fields to return |
| 96 | 60 | BINARY(4) | Number of fields to return |
| 100 | 64 | CHAR(*) | Reserved |
| Array(*) of BINARY(4) | Keys of fields to return | ||
| CHAR(*) | Reserved | ||
Valid Keys - Request Output
The keys listed below are used to determine in the STIL0100 Input Format what will be returned per list entry in the List Data Section - STOL0100 Output Format. Each key can only be specified once. See Field Descriptions for details of the fields listed.
Each list entry returned in the output format describes a single statistics collection for a specific file member and can be thought of as two groups of related keys:
Group 1: The following keys describe information at file member level and will repeat in list entries describing different statistics collections (see group 2) for the same file member:
| Key | Type | Description |
|---|---|---|
| 1 | CHAR(10) | ASP device name used |
| 2 | CHAR(10) | File name used |
| 3 | CHAR(10) | File library name used |
| 4 | CHAR(10) | File member name used |
| 9 | CHAR(26) | Current time stamp of last change |
| 10 | BINARY(8) | Current number of (undeleted) records. |
| 11 | BINARY(8) | Current number of deleted records. |
| 12 | BINARY(8) | Current total count of inserts, updates, and deletes. |
| 47 | CHAR(1) | Current block system statistics collections option. |
| 48 | BINARY(8) | Current size of statistics collections. |
Group 2: The following keys describe information at statistics collection level per file member:
| Key | Type | Description |
|---|---|---|
| 7 | CHAR(16) | Internal statistics ID |
| 46 | CHAR(*) | Statistics collection name |
| 14 | CHAR(10) | Name of creating user profile |
| 15 | CHAR(26) | Time stamp of create |
| 52 | CHAR(10) | Name of last modifying user profile |
| 53 | CHAR(26) | Time stamp of last modification |
| 16 | BINARY(4) | Number of most frequent values available |
| 17 | BINARY(4) | Number of histogram ranges available |
| 18 | CHAR(10) | Aging mode |
| 19 | CHAR(1) | Aging status |
| 22 | CHAR(1) | Translation attribute |
| 23 | BINARY(8) | Number of (undeleted) records |
| 24 | BINARY(8) | Number of deleted records |
| 25 | BINARY(8) | Total counts of inserts, updates, and deletes |
| 26 | BINARY(8) | Number of distinct values (cardinality) |
| 27 | BINARY(8) | Number of NULLs |
| 28 | BINARY(4) | Number of columns |
| 29 | Array(*) of CHAR(10) | Column names |
| 41 | Array(*) of CHAR(1) | Translation attributes |
| 30 | Array(*) of CHAR(20) | Qualified translation table names |
| 31 | Array(*) of CHAR(*) | Column descriptions |
Format of the Generated List
The statistics collections list consists of:- A user area
- A generic header
- An input parameter section
- A header section
- A list data section
The user area and generic header are described in User spaces. The remaining items are described in the following sections.
Input Parameter Section
The following information is returned in the input parameter section. For detailed descriptions of the fields in this table, see Field Descriptions.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | CHAR(10) | User space name specified |
| 10 | A | CHAR(10) | User space library name specified |
| 20 | 14 | CHAR(8) | Format of output specified |
| 28 | 1C | BINARY(4) | Length of input data specified |
| 32 | 20 | CHAR(8) | Format of input data specified |
| 40 | 28 | CHAR(10) | ASP device name specified |
| 50 | 32 | CHAR(10) | File name specified |
| 60 | 3C | CHAR(10) | File library name specified |
| 70 | 46 | CHAR(10) | File member name specified |
| 80 | 50 | CHAR(1) | Column option specified |
| 81 | 51 | CHAR(3) | Reserved |
| 84 | 54 | CHAR(48) | Continuation handle specified |
| 132 | 84 | BINARY(4) | Offset to fields to return specified |
| 136 | 88 | BINARY(4) | Number of fields to return specified |
| 140 | 8C | BINARY(4) | Displacement to specified fields to return |
| Array(*) of BINARY(4) | Keys of fields to return specified | ||
Header Section
For detailed descriptions of the fields in this table, see Field Descriptions.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | CHAR(48) | Continuation handle |
| 48 | 30 | ||
List Data Section - STOL0100 Output Format
For output format STOL0100, the list data section has the following layout, where each list entry contains the requested fields for a single statistics collection for a specific file member. See also Valid Keys - Request output and note, that the fields for each list entry will be returned in the order requested.
See Field Descriptions for details of the fields listed in the layout.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | BINARY(4) | Length of list entry |
| 4 | 4 | BINARY(4) | Number of key fields returned |
| These fields repeat, in the order listed, for each key selected. | BINARY(4) | Length of field information returned | |
| BINARY(4) | Key identifier | ||
| BINARY(4) | Length of data | ||
| CHAR(*) | Data | ||
| CHAR(*) | Reserved (padding to the next four-byte boundary) | ||
Column Description
Layout of a single returned column description, if Column descriptions were requested as output in the STIL0100 Input Format. See Field Descriptions for details of the fields listed.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | BINARY(4) | SQL data type |
| 4 | 4 | BINARY(4) | Field length |
| 8 | 8 | BINARY(4) | Length in bytes |
| 12 | C | BINARY(4) | Scale |
| 16 | 10 | BINARY(4) | Precision |
| 20 | 14 | BINARY(4) | Radix |
| 24 | 18 | BINARY(4) | CCSID |
| 28 | 1C | CHAR(1) | NULL capable |
| 29 | 1D | CHAR(1) | Has default |
| 30 | 1E | CHAR(50) | Column text |
| 80 | 50 | BINARY(4) | Ordinal position |
| 84 | 54 | ||
Field Descriptions
Aging mode. Whether the system is allowed to age or remove the statistics collection. The possible values are:
| *SYS | Refresh or removal of the resulting statistics collections will be performed automatically by the statistics manager. |
| *USER | Refresh or removal will only occur when a user requests it. |
Aging status. How current the statistics data is. The possible values are:
| '0' | There are no indications, that the statistics data needs to be refreshed. |
| '1' | There are indications, that the statistics data needs to be refreshed. |
ASP device name. The name of one auxiliary storage pool (ASP) device in the ASP group in which the library and file are located. The ASP device must have a status of 'Available'. The documented authority is required for the given ASP and the primary of the corresponding ASP group. The name can be a specific ASP device name (for an ASP with a number greater than 32), or one of the following special values:
| * | Locate the library and file in the name space for the current thread. |
| *SYSBAS | Locate the library and file in the system ASP (ASP number 1) and all basic ASPs (ASP numbers 2 through 32). |
ASP device name used. The actual auxiliary storage pool device name used, after possible resolution of special values.
CCSID. The column CCSID for character type columns.
Column descriptions. The array of detailed column descriptions in the same order as the columns were requested. The array dimension is given by the Number of Columns field. See Column description for the layout of a single column description.
Column names. The array of names of the columns within the statistics collection, in the same order as at request time. The array dimension is given by the Number of Columns field.
Column option. Which columns and combination of columns to include in the list. The possible values are:
| '0' | Do not include pseudo, single column statistics collection list entries for columns not contained in any actual statistics collection. |
| '1' | Do include pseudo, single column statistics
collection list entries for columns not contained in any actual statistics
collection.
Note: Pseudo statistics collections will be marked by having an internal statistics ID of zero. All other statistics collection related fields for such a statistics collection list entry will be undefined, if requested to be returned. |
Column text. The character string supplied with the LABEL ON SQL statement for this column.
Continuation handle (input section). The handle used to continue from a previous call to this API that resulted in partially complete information. You can determine if a previous call resulted in partially complete information by checking the Information Status variable in the generic user space header following the API call.
If the API is not attempting to continue from a previous call, this parameter must be set to blanks. Otherwise, a valid continuation value must be supplied. The value may be obtained from the list header section of the user space used in the previous call. When continuing, the first entry in the returned list is the entry that immediately follows the last entry returned in the previous call.
Continuation handle (header section). A continuation point for the API. This value is set based on the contents of the Information Status variable in the generic header for the user space. The following situations can occur:
| Information status-C | The information returned in the user space is valid and complete. No continuation is necessary and the continuation handle is set to blanks. |
| Information status-P | The information returned in the user space is valid but incomplete. The user may call the API again, starting where the last call left off. The continuation handle contains a value which may be supplied as an input parameter in later calls. |
| Information status-I | The information returned in the user space is not valid and incomplete. The content of the continuation handle is unpredictable. |
Current block system statistics collections option. Whether system initiated (automatic) statistics collection create requests are allowed for this database file member. The possible values are:
| '0' | System initiated statistics collection requests
are not blocked.
Note: This is the system default. |
| '1' | System initiated statistics collection requests are blocked. |
Current number of deleted records. The total count of deleted records in the file member at the time of the list request.
Current number of (undeleted) records. The total count of active records in the file member at the time of the list request.
Current size of statistics collections. The total amount of space in bytes used for statistics collections related data for this file member.
Current time stamp of last change. The time stamp, when the file member was last changed at the time of the list request.
Current total count of inserts, updates, and deletes. The number of insert, update, and delete operations that were recorded for the file member at the time of the list request.
Data. The data returned for the key identifier.
Displacement to specified fields to return. Displacement to the start of the array of specified fields to return.
Note: This is not the offset specified on input, but the displacement within the input parameter section. See the Offset to fields to return specified instead.
Field length. Column field length.
File library name. Where the file for which statistics collections are to be listed is located. You can use these special values for the library name, if the ASP Device Name is *:
| *CURLIB | The job's current library or QGPL if the current library is not set. |
| *LIBL | The library list. |
| *USRLIBL | Libraries listed in the user portion of the library list. |
File library name used. The actual file library name used, after possible resolution of special values.
File member name. The name of the file member to be used for the list request. This value can be a specific file member name or one of the following special values:
| *FIRST | The first member (in the order created) in the specified file. |
| *LAST | The last member (in the order created) in the specified file. |
| *ALL | All members in the specified file. |
File member name used. The actual file member name used, after possible resolution of special values.
File name. The name of the file for which statistics collections are to be listed. This can be a name of an existing local, single format, physical file. If an actual name is specified for the file library name, then you can also use the special value:
| *ALL | All local, single format, physical files in the specified library. |
File name used. The actual file name used.
Has default. Whether the column has a default value (DEFAULT clause or null capable). The possible values are:
| '0' | Column does not have a default value. |
| '1' | Column has default value. |
Internal statistics ID. Together with the qualified file name and member name this represents a unique ID for the statistics collection listed.
Note: The ID is stored in binary, non printable form in the character array.
Key identifier. The field returned. For a list of valid keys see Valid Keys - Request output.
Keys of fields to return. The list of fields to return per list entry. For a list of valid keys see Valid Keys - Request output.
Length in bytes. Column length in bytes.
Length of data. The length of the data returned for the field.
Length of field information returned. Total number of bytes returned for this field.
Length of list entry. Number of bytes returned for this list entry.
Name of creating user profile. The name of the user profile, which requested the statistics collection. The name will be *SYS for statistics collections automatically requested by the system.
Name of last modifying user profile. The name of the user profile, which updated the statistics collection data last. The name will be *SYS for statistics collections automatically refreshed by the system.
Note: Updates of statistics collection attributes will not be logged here.
NULL capable. whether the column allows NULL values or not. The possible values are:
| '0' | Column does not allow NULL values. |
| '1' | Column does allow NULL values. |
Number of columns. Number of columns within the single statistics collection.
Number of deleted records. The total count of deleted records in the file member at the time the statistics were collected.
Number of distinct values. The estimated number of distinct (non NULL) values found in the statistics collection key.
Number of fields to return. The number of fields to return for each list entry.
Number of histogram ranges available. The number of histogram ranges available for this statistics collection. The actual histogram range values can be obtained using the List Statistics Collection Details (QDBSTLDS, QdbstListDetailStatistics) API.
Number of key fields returned. Number of fields actually returned.
Number of most frequent values available. The number of most frequent values available for this statistics collection. The actual most frequent values can be obtained using the List Statistics Collection Details (QDBSTLDS, QdbstListDetailStatistics) API.
Number of NULLs. The estimated number of NULL values found in the statistics collection key.
Number of (undeleted) records. The total count of active records in the file member at the time the statistics were collected.
Number of deleted records. The total count of deleted records in the file at the time the statistics were collected.
Offset to fields to return. Offset to the start of the array of fields to return.
Offset to fields to return specified. Offset to fields to return as specified on the call of the API.
Displacement to specified fields to return. Displacement to the start of the array of specified fields to return.
Note: This is not the offset specified on input, but the displacement within the input parameter section. See the Offset to fields to return specified instead.
Ordinal position. Numeric place of the column in the file member, ordered from left to right, starting with one.
Precision. The precision of the column for numeric data type columns.
Qualified translation table names. The array of names of the translation tables that were specified, when the statistics collection was requested, in the same order as requested. The first 10 characters contain the translation table name, and the second 10 characters contain the name of the library where the table is located. The array dimension is given by the number of columns field. The Translation table name can also have the following special values: *LANGIDUNQ indicates the unique weight table that corresponds to the LANGID specified in the library name is used; *LANGIDSHR indicates the shared weight table that corresponds to the LANGID specified in the library name is used.
Note: For system initiated requests, the translation table name and the library can be set to the special value:
| *UNKNOWN | The information is not available in a suitable form. |
Radix. whether the column precision is specified in number of binary or decimal digits for numeric data types columns. The possible values are:
| 2 | Column precision is number of binary digits. |
| 10 | Column precision is number of decimal digits. |
Reserved. Reserved for future use. If this field is input, the field must be set to hexadecimal zeros.
Reserved (in STOL0100 Output format). Structure padding to guarantee alignment to the next four bytes boundary.
Scale. The scale of the column for numeric data type columns.
SQL data type. The SQLTYPE of the column as explained in the DB2® for IBM® i SQL reference topic collection.
Statistics collection name. A name unique amongst all statistics collections for the file member.
Time stamp of create. The time stamp, when the statistics collection was created.
Time stamp of last modification. The time stamp, when the statistics collection was last modified. This includes the initial create and any update of the statistics collection data.
Note: Updates to just statistics collection attributes will not be logged here.
Total count of inserts, updates, and deletes. The number of insert, update, and delete operations that were recorded for the file member at the time the statistics were collected.
Translation attribute. Indicates the type of translation used on the combination of character columns in the statistics collection key before the statistics were calculated. This attribute generalizes the information given by the the single translation attribute values returned for each column. The possible values are:
| '0' | Uniquely weighted translation. |
| '1' | Shared weight translation. |
| '9' | No translation. |
Translation attributes. The array of translation attributes for the single columns in the statistics collection key in the same order as requested. The translation attribute indicates the type of translation used on a character column before the statistics were calculated and generalizes the type of translation defined by the translation table applied to this column. The possible values for each array entry are:
| '0' | Uniquely weighted translation. |
| '1' | Shared weight translation. |
| '9' | No translation. |
User space name specified. User space name as specified on the call of the API.
User space library name specified. User space library name as specified on the call of the API.
Error Messages
| Message ID | Error Message Text |
|---|---|
| CPF0623 E | Field &1 not found in record format &2. |
| CPF1866 E | Value &1 for number of fields to return not valid. |
| CPF2105 E | Object &1 in &2 type *&3 not found. |
| CPF2113 E | Cannot allocate library &1. |
| CPF2173 E | Value for ASPDEV not valid with special value for library. |
| CPF218C E | &1 not a primary or secondary ASP. |
| CPF3141 E | Member &2 not found. |
| CPF34C0 E | Value &1 for number of fields to return parameter not valid. |
| CPF3C07 E | Error occurred while retrieving information from object &1. |
| CPF3C1D E | Length specified in parameter &1 not valid. |
| CPF3C21 E | Format name &1 is not valid. |
| CPF3C29 E | Object name &1 is not valid. |
| CPF3C36 E | Number of parameters, &1, entered for this API was not valid. |
| CPF3C39 E | Value for reserved field not valid. |
| CPF3C3B E | Value for parameter &2 for API &1 not valid. |
| CPF3C82 E | Key &1 not valid for API &2. |
| CPF3C89 E | Key &1 specified more than once. |
| CPF3CE2 E | Continuation handle not valid. |
| CPF3CF1 E | Error code parameter not valid. |
| CPF3CF2 E | Error(s) occurred during running of &1 API. |
| CPF4268 E | Object &1 in &2 type *&3 not found. |
| CPF9802 E | Not authorized to object &2 in &3. |
| CPF9803 E | Cannot allocate object &2 in library &3. |
| CPF9804 E | Object &2 in library &3 damaged. |
| CPF980B E | Object &1 in library &2 not available. |
| CPF9810 E | Library &1 not found. |
| CPF9812 E | File &1 in library &2 not found. |
| CPF9814 E | Device &1 not found. |
| CPF9820 E | Not authorized to use library &1. |
| CPF9822 E | Not authorized to file &1 in library &2. |
| CPF9825 E | Not authorized to device &1. |
| CPF9826 E | Cannot allocate file &2. |
| CPF9830 E | Cannot assign library &1 |
| CPF9872 E | Program or service program &1 in library &2 ended. Reason code &3. |
| CPFB401 E | API &3 failed with reason code &1. |
| CPFB8ED E | Device description &1 not correct for operation. |
Related Information
- the <qdbst.h> include file in library QSYSINC,
for API-related structure declarations and special value declarations.
- the <qdbstmgr.h> include file in library QSYSINC,
for the QdbstListStatistics API prototype.
- the <qdbstls.h> include file in library QSYSINC,
for the QDBSTLS API prototype.
- Cancel Requested Statistics
Collections (QDBSTCRS, QdbstCancelRequestedStatistics) API
- Delete Statistics Collections
(QDBSTDS, QdbstDeleteStatistics) API
- List Requested Statistics
Collections (QDBSTLRS, QdbstListRequestedStatistics) API
- List Statistics Collection Details
(QDBSTLDS, QdbstListDetailStatistics) API
- Request Statistics Collections
(QDBSTRS, QdbstRequestStatistics) API
- Update Statistics Collections (QDBSTUS, QdbstUpdateStatistics) API
API introduced: V5R2
[ Back to top | >Database and File APIs | APIs by category ]