30 June 2020
How does it works ?
Each time a dynamic statement is executed, Db2 has to check if the statement is already in the DSC (exactly the same, included spaces):
- If YES :
- Reuse the access path and execute it
- If NO :
- Prepare the statement:
- Syntax checks
- Security checks
- Access Path selection
- Execute it
- Prepare the statement:
Before Db2 12: CONCENTRATE option at statement level
Imagine the following statements and remember the statement must be exactly the same to avoid a PREPARE and DSC cache process; so:
- SELECT * FROM TT.ACCOUNT WHERE ACCOUNT_ID = ‘1475-89’
- SELECT * FROM TT.ACCOUNT WHERE ACCOUNT_ID = ‘1476-90’
will be evaluated different in a "DSC perspective", though they will inherit the same Access Path.
We have evaluated the cost to prepare this simple statement to about 40 GETPAGES; this cost can be quite high compared to statement execution cost (here about 3 or 4 GETPAGES in case of index scan with 2 or 3 matching columns).
So if you have to execute 100 dynamic statements with different ACCOUNT_ID, the cost is about GETPAGES : ( (40 + 4 ) x 100) for preparation and execution . This work is called a full prepare and can be avoided. This kind of requests also fulfilled the DSC and may have side effects on other applications: as DSC storage is not unlimited, it has to free space and throw away other statements that will have to be again prepared.
You can reduce the prepare cost if you use CONCENTRATE statement option; the statement is transformed before being saved into the DSC:
- SELECT * FROM TT.ACCOUNT WHERE ACCOUNT_ID = #
So all the execution reuse the cached statement, it is a much cheaper process called a Short Prepare (nearly like a static SQL). In this case, the cost is only 440 GETPAGES (40 + (4 X 100)) for execution, and the gain is about 90%:
Db2 12: new CONCENTRATE option at package level
Since Db2 12 FL500, a new BIND option is available CONCENTRATESTMT YES and it is also possible to alter or to create function and stored procedure with the following statement CONCENTRATE STATEMENTS WITH LITERALS. The main advantage of this new option is the transparency, no need to review the application code. You can also be more specific and add the CONCENTRATE STATEMENTS WITH LITERALS to your prepare for only one specific SQL of the package.
As it is not recommanded to rebind NULLID packages with specific BIND options, you can create a new collection NULLID_CONCENTRATE with CONCENTRATESTMT YES and evaluate the best candidates to use this collection explicitly.
Should you want to avoid a modification of your applications, you can also use the PROFILE feature to force a SET CURRENT PACKAGE PATH = ‘NULLID_CONCENTRATE’ for some type of connections (IP address, userid , primauth, …)..
Bad side effects of CONCENTRATE
In some contexts, a massive use of the CONCENTRATE statement can generate harmful side effects. Imagine the following predicate in a query:
- UPPER(REPLACE(NAME,‘éèêë’,’eeee’)) = :HV1
You can create an index to avoid a Tablespace Scan and use a matching column: CREATE INDEX X1 ON TABLE TAB1 (UPPER(REPLACE(NAME,‘éèêë’,’eeee’))) ;
But if statement concentration is active for this statement and/or package, the predicate will be:
- UPPER(REPLACE(NAME,#,#)) = :HV1
... and Db2 can’t make the link between the statement and the index, in this case you will go back to a Tablespace Scan !
Other side effects may arise with the LIKE predicate because Db2 can not make the difference between LIKE ‘JOHN%’ vs LIKE ‘%JOHN%’ , as the statement will be stored with 'LIKE #' !
Db2 12 gives you the opportunity to easily implement SQL statement concentration without application changes:
- REBIND package with CONCENTRATESTMT option
- New parameters for Create stored procedure or function
But pay attention not to generalize this feature, because sometimes Db2 has to know the real predicate value to build the optimal access path to solve your query.
Next Episode: Runtime Adaptive Index