Troubleshooting
Problem
This document describes various methods to reduce the amount of time taken to rebuild access paths.
Resolving The Problem
This document describes various methods to reduce the amount of time taken to rebuild access paths.
How can I speed up the access path rebuild operation without doing a manual IPL and controlling it from the edit rebuild access path screen? You should do one of the following:
How can I speed up the access path rebuild operation without doing a manual IPL and controlling it from the edit rebuild access path screen? You should do one of the following:
| o | Use EDTRBDAP to first change the status of the access paths to *HLD, then to *OPN. This will cause the work that has been done to be lost and when restarted will need to start from the beginning of the rebuild. Once this is done, use the SBMJOB command to issue an OPNDBF operation for *INP against each of the logical files. - For example, SBMJOB CMD(OPNDBF FILE(LIB/FILE) OPTION(*INP)). The access path rebuilds will then run at the priority of the batch job. The access path rebuilds will use the parallelism specified via system value QQRYDEGREE, QAQQINI, or CHGQRYA setting if submitting a job to rebuild the access path. See the SMP notes below for considerations before using SMP. If the QDBSRVxx jobs are allowed to rebuild the access paths, they are built at a low priority. The QDBSRVxx jobs that rebuild access paths run at a priority of 51 and this cannot be changed. The QDBSRVxx jobs will use parallelism as set by the system value QQRYDEGREE. This is set when the jobs are started (at IPL time) and can not be changed while the jobs are active. If the system value QQRYDEGREE is changed, the jobs will pick up the change at the next IPL. See the SMP notes below for considerations before turning this on. Note that for an EVI type Logical file, rather than doing the OPNDBF, you need to delete the EVI, then use a batch job to recreate the EVI.. |
| o |
Give as much memory as possible to the pool that the batch job will run in. Make sure the memory is in the pool before the batch job is submitted. How much memory the pool has at the start of the rebuild will influence how the system performs the rebuild and can have a large impact on the performance of the rebuild. The system can not change how it implements the rebuild after it starts, even if memory is added to the pool. Going to a restricted state and then putting as much memory in the base pool will also help. This will not end the jobs. Use SMP (Symmetric Multiprocessing) to speed up the rebuild. Option 26 must be installed and System Value QQRYDEGREE must be set to something other than *NONE. This options is no longer chargeable. SMP can also be turned on with CHGQRYA or QAQQINI file for user jobs rebuilding the access. |
How to monitor the rebuilds:
From IBM i Access Client Solutions you can monitor the index build by going to the
following -
Schemas -> Database -> Database Maintenance -> Index builds and index rebuilds.
This will show you complete process.
Considerations on using SMP:
1. The system can take 5-6 times the file size in temporary storage when SMP is used. If the space is not available, the index build can bring down the system if the maximum storage is reached.
2. You can limit the number of tasks that SMP uses which will reduce the amount of temporary storage with CHGQRYA and the Number of tasks parameter. There is not an algorithm to determine the amount of storage that will be used.
3. SMP must be turned on (System value QQRYDEGREE / QAQQINI or CHGQRYA) done before the rebuild begins in a user job. Changing the setting after the rebuild is going will not allow SMP to be used.
Notes:
1. If SMP is being used, it is best to run one at a time. If multiple rebuilds are going on, there can be resource contention.
2. If SMP is not installed, the number of Submit jobs should be equal to the number of processors.
For additional information on SMP building access paths, you should refer to the following URL:
From IBM i Access Client Solutions you can monitor the index build by going to the
following -
Schemas -> Database -> Database Maintenance -> Index builds and index rebuilds.
This will show you complete process.
Considerations on using SMP:
1. The system can take 5-6 times the file size in temporary storage when SMP is used. If the space is not available, the index build can bring down the system if the maximum storage is reached.
2. You can limit the number of tasks that SMP uses which will reduce the amount of temporary storage with CHGQRYA and the Number of tasks parameter. There is not an algorithm to determine the amount of storage that will be used.
3. SMP must be turned on (System value QQRYDEGREE / QAQQINI or CHGQRYA) done before the rebuild begins in a user job. Changing the setting after the rebuild is going will not allow SMP to be used.
Notes:
1. If SMP is being used, it is best to run one at a time. If multiple rebuilds are going on, there can be resource contention.
2. If SMP is not installed, the number of Submit jobs should be equal to the number of processors.
For additional information on SMP building access paths, you should refer to the following URL:
https://www.ibm.com/support/pages/sites/default/files/inline-files/0301milligan-pdf.pdf
Here is an example of forcing a rebuild in parallel without adjusting QQRYDEGREE.
CHGLF FILE(-yourlib-/-your logical file-) MAINT(*REBLD) RECOVER(*NO)
- must be MAINT(*REBLD) to pop the logical file onto EDTRBDAP
CHGLF FILE(-yourlib-/-your logical file-) MAINT(*IMMED) RECOVER(*AFTIPL) - or whatever you want for RECOVER
- quickly bounce to EDTRBDAP
change your logical file to *HLD
change your logical file to *OPN
CHGQRYA DEGREE(*MAX) - or whatever you want
OPNDBF FILE(-yourlib-/-your logical file-)) option(*ALL)
You need to make sure you do the OPNDBF in the same job that you do the CHGQRYA.
Here is an example of forcing a rebuild in parallel without adjusting QQRYDEGREE.
CHGLF FILE(-yourlib-/-your logical file-) MAINT(*REBLD) RECOVER(*NO)
- must be MAINT(*REBLD) to pop the logical file onto EDTRBDAP
CHGLF FILE(-yourlib-/-your logical file-) MAINT(*IMMED) RECOVER(*AFTIPL) - or whatever you want for RECOVER
- quickly bounce to EDTRBDAP
change your logical file to *HLD
change your logical file to *OPN
CHGQRYA DEGREE(*MAX) - or whatever you want
OPNDBF FILE(-yourlib-/-your logical file-)) option(*ALL)
You need to make sure you do the OPNDBF in the same job that you do the CHGQRYA.
[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"DB2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Historical Number
4890636
Was this topic helpful?
Document Information
Modified date:
14 November 2022
UID
nas8N1013641