Skip to Main Content
IBM Data and AI Ideas Portal for Customers


This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:


Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,


Post your ideas

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

IBM Employees should enter Ideas at https://ideas.ibm.com


Status Delivered
Workspace Db2 for z/OS
Created by Guest
Created on Apr 24, 2018

Avoid synchronous preformat in SQL INSERT transactions

[Background]
The customer's system providing banking services abroad is exposed to regulatory risk by the local banking regulator.
The customer is forced to complete a specific transaction within a second basically.
This specific SQL INSERT transaction took more than 1 seconds to complete due to 'synchronous' ('sync') preformat. ('sync' preformat triggered by INSERT SQL against two tablespaces took more than a second)
Customer wants to avoid 'sync' preformat and keep these specific transactions to be completed within a second.

[Description]
Customer hopes that Db2 design will be improved so that an asynchronous preformat is triggered more timely and efficiently leading to avoidance of 'sync' preformat.
Our idea for improvement is as follows :
- Db2 utilities (REORG/LOAD/REBUILD INDEX/RECOVER TABLESPACE) creates additional preformatted pages when it is utilized, much smaller than PREFORMAT option. (for example 2 CYL or 16 CYL pages)
- By adding preformatted pages asynchronous preformat will be triggered before 'sync' preformat.
- At physical extension of VSAM LDS occurs on a pageset, limit the size of 'sync' preformat to 2 CYL instead of current 16 CYL, and make suspension time for 'sync' preformat smaller.

The reason why our customer wishes for Db2 design improvement is as follows :
Customer's SQL insert transaction took more than a second to complete due to 'sync' preformat. ('sync' preformat triggered by INSERT SQL against two tablespaces took more than a second)
The customer system has a remote system for a disaster recovery and both local and remote disk devices are always mirrored synchronously (not asynchronously), so the latency due to 'sync' preformat is likely to occur.
Please refer to PMR#55443,615,760.

They thought about a solution using the current Db2 function (PREFORMAT option of REORG/LOAD), but there were some issues to be considered as follows:
- PREFORMAT option of REORG/LOAD takes long time to complete the REORG/LOAD job depending on the size of tablespace.
PREFORMAT option would extend the elapsed time and consume more CPU time compared to REORG/LOAD without PREFORMAT option.
As a result, it could cause the night batch delay and CPU resource shortage from viewpoint of entire system.
(PREFORMAT done through REORG/LOAD PREFORMAT option is also affected by synchronous mirroring scheme used in customer's system, resulting in more CPU resource consumption and more elapsed time compared to other customer's system without synchronous mirroring)
- REBUILD INDEX and RECOVER TABLESPACE utility don't have PREFORMAT option.
- It's difficult for the customer to estimate an appropriate size of primary extent for PREFORMAT option.
The larger size makes it longer to complete REORG/LOAD (with PREFORMAT option), the smaller size can cause an immediate physical extent resulting in frequent encounter of synchronous preformat conducted in every extension.
- We can choose REORG SHRLEVEL CHANGE when using PREFORMAT option in REORG. However, it needs more disk resource for shadow datasets.
And, as for LOAD, we cannot access the tablespace when LOAD REPLACE is running.