[IQUG] versions and IQ_SYSTEM_MAIN

Baker, Chris c.baker at sap.com
Thu Jun 28 06:17:30 MST 2018

One thing that many users may not look at is the application/connections that cause the version build up.

Often this may be a reporting application/server that leaves connections open to IQ and leaves them idle until used.  Users who connect to the application from their desktop and leave it idle may be the culprit (often this is the case in a license-limited scenario for a reporting application – users grab a license by opening the application and just leaving it sit until and if they want to use it).

If you are connecting via ODBC, then one of the default connection properties is ‘autocommit=true’.  This has the effect of ONLY issuing a ‘commit’ on the connection used AFTER the query is run.  Until that time, the versions will build up, but even more importantly, the connection will use the ‘old’ version it is holding for the query – not the latest data.

To solve this, you can add the ‘autoprecommit=true’ parameter on the connection properties.  You will still get version build-up, but perhaps not as bad, and the query will run with the latest loaded/committed data when it is invoked.

You may still want to look at the idle connections causing the version build-up in the first place.


Chris Baker | Database Engineering Evangelist | CERT | PI HANA Platform Data Management | SAP
T +1 416-226-7033<tel:+1%20416-226-7033> | M +1 647-224-2033<tel:+1%20647-224-2033> | TF +1 866-716-8860<tel:+1%20866-716-8860>
SAP Canada Inc. 445 Wes Graham Way, Waterloo, N2L 6R2<x-apple-data-detectors://17/1>
c.baker at sap.com<mailto:c.baker at sap.com> | www.sap.com<http://www.sap.com/>

Conference tel: 1-866-312-7353,,9648565377#<tel:1-866-312-7353,,9648565377%23>

From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of John Anthony
Sent: Wednesday, June 27, 2018 8:58 PM
To: Alhandra, Hugo, Vodafone Group (External) <hugo.alhandra at vodafone.com>
Cc: iqug at iqug.org
Subject: Re: [IQUG] versions and IQ_SYSTEM_MAIN

From your questions - it would appear you are trying to nail down why your system main fills up. The answers to the question are in the manual and is getting further clarified here.

But but you still find operational situations when nothing much has seemingly changed and system_main fills up. Most of the time this has to do with rapid version build up triggered by a transaction that has not commit'ed. this situation can be exacerbated in a multiplex due to inter node communication / sync failures.

It would appear that as versions build up into the 100s and even 1000s - the accounting of them in system_main takes up exponentially more space. An approach to address this is to over allocate system_main but keeping tight control over version build up addresses the root cause.


On Wed, Jun 27, 2018 at 3:57 PM Alhandra, Hugo, Vodafone Group (External) <hugo.alhandra at vodafone.com<mailto:hugo.alhandra at vodafone.com>> wrote:
Thanks David,
So it’s only version metadata, not the version itself, right? As of my understanding version data will go into the user dbspace, right?
Besides 2% of main, Is there any to know the size of those structures? What metadata is stored?

Hugo Alhandra
Database Administrator
hugo.alhandra at vodafone.com<mailto:hugo.alhandra at corp.vodafone.pt>

gfi.pt<http://www.gfi.pt/>  I  gfi.pt/jobs<http://www.gfi.pt/jobs>  I  Gfi News<http://www.gfi.pt/gfi-news>

From: Rittenhouse, David <d.rittenhouse at sap.com<mailto:d.rittenhouse at sap.com>>
Sent: 27 de junho de 2018 19:26
To: Alhandra, Hugo, Vodafone Group (External) <hugo.alhandra at vodafone.com<mailto:hugo.alhandra at vodafone.com>>; iqug at iqug.org<mailto:iqug at iqug.org>
Subject: RE: versions and IQ_SYSTEM_MAIN

Hi Hugo,

IQ_SYSTEM_MAIN contains a number of system structures so it is important to get the sizing right.


     *   Freelist
     *   Checkpoint Area
     *   TLV Replay log
     *   Version metadata
     *   Catalog buffer
     *   MPX status structures

To clarify:  version metadata (about the versions stored in user-defined main space) is stored in IQ_SYSTEM_MAIN.

Below is an excerpt from the IQ Sizing Guide – whenever there are ranges specified I recommend you go with the larger of the values in the range…  so if your database is > 100GB I would make IQ_SYSTEM_MAIN at least 2% of the user-defined main DbSpace (+ a little more if it is a MPX.)

“For databases that exceed 100 GB, it is recommended that IQ_SYSTEM_MAIN be at least 8 GB for a simplex and 16 GB
for a multiplex.  IQ_SYSTEM_MAIN would typically be sized at 1-2% of the user defined main space size
(10-20 GB for a 1 TB database). If this instance is migrated to a multiplex, an additional 0.1-0.3% (1-3 GB per 1 TB)
of space should be added per node in the multiplex.  For a 4 node system with a 1 TB database, the size would be the
16 GB minimum plus 1-3 GB per node (4 nodes) in the multiplex; or 20-28 GB.”

Hope that helps,

David Rittenhouse
Centre of Excellence – Database & Data Management
M: +44 (0) 7899 948 295<tel:+44%207899%20948295> | E: d.rittenhouse at sap.com<mailto:d.rittenhouse at sap.com> | Web: www.sap.com<http://www.sap.com/>

From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> [mailto:iqug-bounces at iqug.org] On Behalf Of Alhandra, Hugo, Vodafone Group (External)
Sent: 27 June 2018 17:23
To: iqug at iqug.org<mailto:iqug at iqug.org>
Subject: [IQUG] versions and IQ_SYSTEM_MAIN

Hi folks,
I have a question regarding table level versions management in IQ 16.

1 - On IQ16 the reference manual we read
“Sizing Guidelines for Main and Temporary Stores
Three factors influence the space required for the IQ_SYSTEM_MAIN store:
Versioning – the volume of versions maintained varies.
•       Nature of data and indexes.
o   Dynamic nature of the data – the capacity to load more data at any time.”
2 - https://help.sap.com/viewer/a8937bea84f21015a80bc776cf758d50/
“To delete data when using table-level versioning, you may actually need to increase disk space by adding a dbspace to your IQ main store.”
•       3 – we observed versions really use space in user dbspace.
Can someone give a more clear explanation on how space is allocated from IQ_SYSTEM_MAIN and user dbspaces and more details on how the process works internally?
Is there any ratio regarding IQ_SYSTEM_MAIN and iq_main?

Thanks a lot,
Hugo Alhandra
Database Administrator
hugo.alhandra at vodafone.com<mailto:hugo.alhandra at corp.vodafone.pt>

gfi.pt<http://www.gfi.pt/>  I  gfi.pt/jobs<http://www.gfi.pt/jobs>  I  Gfi News<http://www.gfi.pt/gfi-news>

IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20180628/def9bc7e/attachment-0001.html>

More information about the IQUG mailing list