News
Abstract
QSQPRCED() statement level control of QAQQINI options
Content
The QSQPRCED() API (SQLP0410 format) has been extended to include optional statement level controls for a subset of the QAQQINI query options.
The controls added to the API are:
QSQPRCED API information:
Parallel degree. Specifies the parallel processing option that can be used when running database queries and database file keyed access path builds, rebuilds, and maintenance in the job. The specified parallel processing option determines the types of parallel processing allowed. There are two types of parallel processing: Input/Output (I/O) parallel processing. With I/O parallel processing, the database manager uses multiple tasks for each query to do the I/O processing. The central processor unit (CPU) processing is still done serially. Symmetric Multiprocessing (SMP). SMP assigns both CPU and I/O processing to tasks that run the query in parallel. Actual CPU parallelism requires a system with multiple processors. SMP can only be used if the system feature, DB2 Symmetric Multiprocessing, is installed. Use of SMP parallelism can affect the order in which records are returned.
The possible values are :
| I | Also known as *IO. Any number of tasks can be used. SMP parallel processing is not allowed. |
| M | Also known as *MAX. Like *MAX, with the value xxx indicating the ability to specify an integer percentage value 1 - 200. The query optimizer determines the parallel degree for the query using the same processing as is done for *MAX. Once determined, the optimizer adjusts the actual parallel degree used for the query by the percentage given. This option provides the user the ability to override the parallel degree used to some extent without having to specify a particular parallel degree under *NUMBER_OF_TASKS. |
| N | Also known as **NUMBER_OF_TASKS. Indicates the maximum number of tasks that can be used for a single query. The number of tasks will be capped off at either this value or the number of disk arms associated with the table. It is not recommended that this option be used if running SQE. The SQE optimizer will attempt to use this degree and override many of the normal costing mechanisms. For SQE instead use *OPTIMIZE with a percentage. |
| O | Also known as *OPTIMIZE. Any number of tasks for: - I/O or SMP parallel processing of the query - Database file keyed access path build, rebuild, or maintenance. SMP parallel processing is used only if the system feature, DB2 Symmetric Multiprocessing for IBM i, is installed. Use of parallel processing and the number of tasks used is determined by: - the number of processors available in the system - the job share of the amount of active memory available in the pool in which the job is run - whether the expected elapsed time for the query or database file keyed access path build or rebuild is limited by CPU processing or I/O resources. The query optimizer chooses an implementation that minimizes elapsed time based on the job share of the memory in the pool. |
| S | Also known as *SYSVAL. Set to the current system value QQRYDEGREE. |
| Z | Also known as *NONE. No parallel processing is allowed for database query processing or database table index build, rebuild, or maintenance. |
Parallel degree value. Indicates the nn value when Parallel Degree is set to 'N' (*NUMBER_OF_TASKS nn), 'M' (*MAX nnn), or 'O' (*OPTIMIZE nnn).
Prevent additional conflicting locks. This is a statement level query option, that can be used to direct the operating system to favor a request for an exclusive, no read lock over new requests to lock the object for reading.
The possible values are :
| Y | When YES (Y) is chosen, any new requests for these lower-level read locks will be kept behind the exclusive lock request and could surface to applications as the table is unavailable for use for querying. This control applies to the following operations: ALTER TABLE (Add, Alter or Drop Column), CREATE TRIGGER, LOCK TABLE, and RENAME TABLE. |
| N | When a job requests an exclusive lock on an object, do not prevent concurrent jobs from acquiring additional locks on the object. |
Pseudo open check host variables. This option can be used to allow SQE to check the selectivity of the host variable values at pseudo open time. If the new set of host variable values require a different plan to perform well, SQE will re-optimize the query. This option is most appropriate when there is considerable variability in the selectivity of host variable in the queries predicates.
The possible values are :
| Y | Also known as *YES. The optimizer will always check host variable selectivity at pseudo open time. Note: If the REOPTIMIZE_ACCESS_PLAN INI option is set to *ONLY_REQUIRED then this INI option has no effect. |
| N | Also known as *NO. The optimizer does not check host variables for selectivity changes once in pseudo-open. |
| O | Also known as *OPTIMIZE. The optimizer will determine when a host variable selectivity should be checked. In general, the optimizer will monitor the query and if after a certain number of runs it determines that there is no advantage to checking host variable selectivity at pseudo open time, it will stop checking. Full opens do normal plan validation. |
SQL deflate table. This is a statement level query option, that specifies whether an SQL CREATE TABLE, CREATE INDEX or CREATE VIEW statement should be implemented by creating only a portion of the table (i.e. deflated) because there is a strong likelihood that the table will never be used.
The possible values are :
| Y | This option affects the creation of SQL tables, views and indexes. When this option is chosen, DB2 for i will create only the file level objects. The remainder of the table, view or index will be automatically created when the object is used. |
| N | Database objects are created using their normal, documented behavior. |
SQL statement compression maximum. This is a statement level query option, that specifies the compression maximum setting, which is used when statements are prepared into a package. The integer value represents the number of times that a statement is compressed before the access plan is removed to create more space in the package. Executing the SQL statement resets the count for that statement to 0. The valid Integer values are 1 - 255.
The following shows the format of the function template parameter for the SQLP0410 format.
| Offset | Type | Field | |
|---|---|---|---|
| Dec | Hex | ||
| 0 | 0 | CHAR(1) | Function |
| 1 | 1 | CHAR(10) | SQL package name |
| 11 | B | CHAR(10) | SQL package library name |
| 21 | 15 | CHAR(10) | Main program name |
| 31 | 1F | CHAR(10) | Main program library name |
| 41 | 29 | CHAR(18) | Statement name |
| 59 | 3B | CHAR(18) | Cursor name |
| 77 | 4D | CHAR(1) | Open options |
| 78 | 4E | CHAR(1) | Using clause for describe |
| 79 | 4F | CHAR(1) | Commitment control |
| 80 | 50 | CHAR(3) | Date format |
| 83 | 53 | CHAR(1) | Date separator |
| 84 | 54 | CHAR(3) | Time format |
| 87 | 57 | CHAR(1) | Time separator |
| 88 | 58 | CHAR(3) | Naming option |
| 91 | 5B | CHAR(1) | Decimal point |
| 92 | 5C | BINARY(2) | Blocking factor |
| 94 | 5E | BINARY(2) | Scrollable option |
| 96 | 60 | BINARY(2) | Position option |
| 98 | 62 | BINARY(4) | Relative record |
| 102 | 66 | BINARY(4) | Number of rows for multiple row statement |
| 106 | 6A | CHAR(1) | Direct map |
| 107 | 6B | CHAR(1) | Reuse SQLDA |
| 108 | 6C | CHAR(1) | Name check |
| 109 | 6D | CHAR(1) | Use pointers |
| 110 | 6E | CHAR(1) | WITH HOLD |
| 111 | 6F | CHAR(18) | User-defined field |
| 129 | 81 | CHAR(10) | Close file name |
| 139 | 8B | CHAR(10) | Close library name |
| 149 | 95 | CHAR(1) | Reopen |
| 150 | 96 | CHAR(1) | Use performance area |
| 151 | 97 | CHAR(1) | Reserved |
| 152 | 98 | BINARY(2) | Maximum Scale |
| 153 | 99 | CHAR(1) | Maximum Precision |
| 155 | 9B | CHAR(1) | Minimum Divide Scale |
| 156 | 9C | BINARY(4) | Statement text CCSID |
| 160 | A0 | PTR(SYP) | SQL-package system pointer |
| 176 | B0 | PTR(SYP) | Main-program system pointer |
| 192 | C0 | CHAR(10) | Sort sequence table name |
| 202 | CA | CHAR(10) | Sort sequence library name |
| 212 | D4 | CHAR(10) | Language identifier |
| 222 | DE | CHAR(1) | Allow copy of data |
| 223 | DF | CHAR(1) | Allow blocking |
| 224 | E0 | BINARY(4) | Offset to statement text length and statement text |
| 228 | E4 | CHAR(1) | Hex literal option |
| 229 | E5 | CHAR(1) | Statement length type |
| 230 | E6 | BINARY(2) | Extended User-defined field length |
| 232 | E8 | BINARY(4) | Offset to Extended User-defined field |
| 236 | EC | BINARY(4) | Length of additional fields |
| 240 | F0 | BINARY(4) | Connection handle |
| 244 | F4 | CHAR(1) | Autocommit option |
| 245 | F5 | CHAR(1) | Extended indicator option |
| 246 | F6 | CHAR(2) | Reserved |
| 248 | F8 | BINARY(4) | Cursor index |
| 252 | FC | BINARY(4) | Statement index |
| 256 | 100 | BINARY(4) | Length of extended cursor name |
| 260 | 104 | BINARY(4) | Offset to extended cursor name |
| 264 | 108 | BINARY(4) | Length of extended statement name |
| 268 | 10C | BINARY(4) | Offset to extended statement name |
| 272 | 110 | CHAR(1) | Concurrent access resolution |
| 273 | 111 | CHAR(1) | Prevent additional conflicting locks |
| 274 | 112 | CHAR(1) | SQL deflate table |
| 275 | 113 | CHAR(1) | Parallel degree |
| 276 | 114 | BINARY(4) | Parallel degree value |
| 280 | 118 | BINARY(4) | SQL statement compression maximum |
| 284 | 11C | CHAR(1) | Pseudo open check host variables |
| 285 | 11D | CHAR(3) | Reserved |
| 288 | 120 | BINARY(4) | Length of CURRENT CLIENT_USERID special register |
| 292 | 124 | BINARY(4) | Offset to CURRENT CLIENT_USERID special register |
| 296 | 128 | BINARY(4) | Length of CURRENT CLIENT_WRKSTNNAME special register |
| 300 | 12C | BINARY(4) | Offset to CURRENT CLIENT_WRKSTNNAME special register |
| 304 | 130 | BINARY(4) | Length of CURRENT CLIENT_APPLNAME special register |
| 308 | 134 | BINARY(4) | Offset to CURRENT CLIENT_APPLNAME special register |
| 312 | 138 | BINARY(4) | Length of CURRENT CLIENT_PROGRAMID special register |
| 316 | 13C | BINARY(4) | Offset to CURRENT CLIENT_PROGRAMID special register |
| 320 | 140 | BINARY(4) | Length of CURRENT CLIENT_ACCTNG special register |
| 324 | 144 | BINARY(4) | Offset to CURRENT CLIENT_ACCTNG special register |
| * | * | BINARY(2) or BINARY(4) | Statement length |
| * | * | CHAR(*) | Statement text |
| * | * | CHAR(*) | Extended user-defined field |
| * | * | CHAR(*) | Extended cursor name |
| * | * | CHAR(*) | Extended statement name |
| * | * | CHAR(*) | CURRENT CLIENT_USERID special register |
| * | * | CHAR(*) | CURRENT CLIENT_WRKSTNNAME special register |
| * | * | CHAR(*) | CURRENT CLIENT_APPLNAME special register |
| * | * | CHAR(*) | CURRENT CLIENT_PROGRAMID special register |
| * | * | CHAR(*) | CURRENT CLIENT_ACCTNG special register |
Was this topic helpful?
Document Information
Modified date:
21 January 2020
UID
ibm11168288