| 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(*) |
The List Statistics Collection Details (QDBSTLDS, QdbstListDetailStatistics) API lists additional statistics data for a single statistics collection, not returned by the List Statistics Collections (QDBSTLS, QdbstListStatistics) API. Available additional data is the list of most frequent values and the list of histogram ranges values.
The generated list output replaces any existing list in the user space.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. |
The format of the statistics collection details list to be returned. Possible format names are:
| STOV0100 | Statistics collection details list with list entries for the requested keys. |
Refer to Format of generated list and STOV0100 Output Format for more information.
The buffer containing the input parameters according to the format of input data parameter. The buffer content has to start at a 4-byte boundary.
The length of the input data buffer provided.
The format of the input data. Possible values are:
| STIV0100 | List statistics collection details input parameters. |
Refer to STIV0100 Input Format for more information.
The structure in which to return error information. For the format of the structure, see Error code parameter.
See Field Descriptions for details of the fields listed.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | CHAR(10) | ASP device name |
| 10 | 0A | CHAR(10) | File name |
| 20 | 14 | CHAR(10) | File library name |
| 30 | 1E | CHAR(10) | File member name |
| 40 | 28 | CHAR(16) | Internal statistics ID |
| 56 | 38 | CHAR(48) | Continuation handle |
| 104 | 68 | BINARY(4) | Offset to fields to return |
| 108 | 6C | BINARY(4) | Number of fields to return |
| 112 | 70 | CHAR(*) | Reserved |
| Array(*) of BINARY(4) | Keys of fields to return | ||
| CHAR(*) | Reserved | ||
Each key can only be specified once. See Field Descriptions for details of the fields listed.
| Key | Type | Description |
|---|---|---|
| 32 | CHAR(*) | Most frequent values |
| 33 | CHAR(*) | Histogram range values |
The statistics collection details list consists of:
The user area and generic header are described in User spaces. The remaining items are described in the following sections.
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(48) | Continuation handle specified |
| 128 | 80 | BINARY(4) | Offset to fields to return specified |
| 132 | 84 | BINARY(4) | Number of fields to return specified |
| 136 | 88 | BINARY(4) | Displacement to specified fields to return |
| Array(*) of BINARY(4) | Keys of fields to return specified | ||
For detailed descriptions of the fields in this table, see Field Descriptions.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | CHAR(10) | ASP device name used |
| 10 | A | CHAR(10) | File library name used |
| 20 | 14 | CHAR(10) | File member name used |
| 30 | 1E | CHAR(2) | Reserved |
| 32 | 20 | CHAR(48) | Continuation handle |
| 80 | 50 | BINARY(4) | Displacement to detail values header of most frequent values |
| 84 | 54 | BINARY(4) | Displacement to detail values header of histogram range values |
| 88 | 58 | ||
This structure contains fields describing general information for list entries of a certain kind (most frequent values or histogram range values) returned in the list data section.
See Field Descriptions for details of the fields listed.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | BINARY(4) | Offset to first detail value entry |
| 4 | 4 | BINARY(4) | Number of detail value entries |
| 8 | 8 | BINARY(4) | Length of detail value entry |
| 12 | C | BINARY(4) | Number of detail value columns |
| 16 | 10 | BINARY(4) | Displacement to format of first detail value column. |
| 20 | 14 | BINARY(4) | Length of detail value column format |
| 24 | 18 | ||
The description of a single detail value column value. 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(10) | Translation table name |
| 38 | 26 | CHAR(10) | Translation table library name |
| 48 | 30 | CHAR(1) | DDS type |
| 49 | 31 | CHAR(3) | Reserved |
| 52 | 34 | ||
The list data section returned for output format STOV0100 contains list entries as specified in the Most frequent values list entry format and the Histogram range values list entry format.
See the Header Section for additional fields that describe information common to all Most frequent value and Histogram range value list entries.
The format below describes the layout of a single most frequent value returned as a list entry in the list data section. See Field Descriptions and the Header Section for details of the fields listed.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | BINARY(8) | Count for this most frequent value |
| 0 | 0 | BINARY(4) | Displacement to first most frequent value column value |
| 0 | 0 | CHAR(4) | Reserved |
| Array of CHAR(*) | Most frequent value columns values | ||
| CHAR(*) | Reserved | ||
This format describes the layout of a single histogram range value returned as a list entry in the list data section. See Field Descriptions and the Header Section for details of the fields listed.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | BINARY(8) | Count for this histogram range value |
| 0 | 0 | BINARY(8) | Count for this histogram range's high value |
| 0 | 0 | BINARY(4) | Displacement to first histogram range value column pair |
| 0 | 0 | CHAR(4) | Reserved |
| Array of CHAR(*) | Low/High value pairs of histogram range value columns | ||
| CHAR(*) | Reserved | ||
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 value's CCSID for character type values.
Note: The CCSID here describes the CCSID of the original value, before translation using the also given translation table.
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. |
Count for this histogram range's high value. If the statistics collection key chosen for the high value of this histogram range value is estimated to occur very often, compared to the other values in this histogram range, this count will be set to the estimated number of occurrences of this key value in the file member.
Note: A value of 0 indicates, that this additional information about the high value is not available.
Count for this histogram range value. How many statistics collection key values are estimated to occur in this histogram range.
Count for this most frequent value. How often the most frequent statistics collection key value is estimated to occur in the file member.
DDS type. Data type code (corresponding to the SQL Data Type) for the value. See DDS Reference: Physical and Logical Files.
Displacement detail values header of histogram range values. Displacement to the general information for the histogram range values list entries.
Note: The displacement will be zero if no histogram range value information was returned.
Displacement to detail values header of most frequent values. Displacement to the general information for the most frequent values list entries. The displacement will be zero if no most frequent value information was returned.
Displacement to first histogram range value column pair. Displacement to the start of the array of Low/High value pairs of histogram range value columns.
Displacement to first most frequent value column value. Displacement to the start of the array of Most frequent value column values.
Displacement to format of first detail value column. Displacement to the array of format descriptions for the detail value column values (compare Number of detail value columns and Length of detail value column format).
Note: Due to performance reasons, these formats might be different from the formats of the original columns the detail value is based on. For example, long character columns might be represented in a truncated form, or varying character columns might have been converted to a fixed length form.
Note: For DATE, TIME, and TIMESTAMP columns, the detail value column values will be returned as *ISO formatted text and the column format will describe a character SQL data type of appropriate length.
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. Value field length.
File library name. The location of the file for which statistics collection details are to be listed. 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. |
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 collection details are to be listed. The file has to be an existing local, single format, physical file.
Histogram range values. The list of histogram range values. See Histogram range values list entry format for the layout of this list.
Internal statistics ID. Together with the qualified file name and member name, this represents a unique ID for the statistics collection details to be listed.
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 in the list. For a list of valid keys see Valid Keys - Request output.
Length in bytes. Length of returned column value in list entry, in bytes. This also gives the displacement to the next column value, where appropriate.
Length of detail value column format. The number of bytes for a single detail value column format. This is also the offset to the next detail value column format, for any but the last detail value column format of this kind of detail values. (See Number of detail value columns).
Length of detail value entry. The number of bytes for a single detail value list entry. This is also the offset to the next detail value list entry for any but the last detail value list entry of this kind of detail values in the list data section (See Number of detail value entries).
Low/High value pairs of histogram range value columns. The array of lower, exclusive, and upper, inclusive, histogram range column values for each column in the statistics collection key. The values are returned in the following order: Low value of first range value column, high value of first range value column, ... , low value of last range value column, high value of last range value column. The array dimension is given by the Number of detail value columns and the formats of the column values are referenced by the Displacement to format of first detail value column. Lower and upper value column always have the same format for a single column in the statistics collection key. The size of a single value column is given by the Length in bytes in the format.
Note: The column values for the low value of the first histogram range will be set to all X'00' and should be treated as undefined, representing 'negative infinity'.
Most frequent value columns values. The array of this most frequent value's column values. The array dimension is given by the Number of detail value columns and the formats of the column values are referenced by the Displacement to format of first detail value column. The size of a single value column is given by the Length in bytes in the corresponding format.
Most frequent values. The list of most frequent values. See Most frequent values list entry format for the layout of this list.
Number of detail value columns. Number of columns in the statistics key for this kind of detail values.
Number of detail value entries. Number of list entries for this kind of detail values (most frequent values or histogram range values) returned on this API call. This value will be zero, if this kind of detail value was not requested to be returned, or, if during this API call, no space was available anymore to fit any value information of this kind into the user space besides other requested detail information. Compare the Continuation handle in the Header Section, how to retrieve the remaining API information in such a case.
Number of fields to return. The number of fields to return in the list.
Offset to fields to return. Offset to the start of the array of fields to return.
Offset to fields to return specified. The offset to fields to return as specified on the call of the API.
Offset to first detail value entry. The offset to the start of the list of this kind of detail value entries within the List data section - STOV0100 Format.
Note: The offset is relative to the start of the user space. The offset is valid only if the Number of detail value entries field is not set to zero.
Offset to value of most frequent value column. The offset to the value of the most frequent value column.
Precision. The precision of the value for numeric data type values.
Radix. Whether the value precision is specified in number of binary or decimal digits for numeric data type values. The possible values are:
| 2 | Value precision is number of binary digits. |
| 10 | Value precision is number of decimal digits. |
Reserved. Reserved for future use. If this field is input, the field must be set to hexadecimal zeros.
SQL data type. The SQLTYPE of the value as explained in the DB2® for IBM® i SQL reference topic collection.
Scale. The scale of the value for numeric data type values.
Translation table library name. The library where the translation table used was located. If no translation table was used, the library name is set to all blanks. If the Translation table name field contains the special value of *LANGIDUNQ or *LANGIDSHR, then the Translation table library name field will contain the corresponding language identifier (LANGID).
Translation table name. The translation table used on the value, when the statistics collection was created. If no translation table was used, the table name is set to all blanks. The Translation table name can also have the following special values: *JOB indicates the value is retrieved from the job; *LANGIDUNQ indicates the unique weight table that corresponds to the LANGID specified in the Translation table library name field is used; *LANGIDSHR indicates the shared weight table that corresponds to the LANGID specified in the Translation table library name field is used. *NONE indicates a translate table is not used.
Note: The value is actually returned in the translated form.
User space name specified. The user space name as specified on the call of the API.
User space library name specified. The user space library name as specified on the call of the API.
| 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. |
[ Back to top | >Database and File APIs | APIs by category ]