Skip to main content

Community

HVR 6.3 – DB2i Logging and Capture CPU Profiling Configuration

Completed

Please sign in to leave a comment.

Comments

2 comments

    Hi Satya,

    We are investigating the issue and see if we can work out some possible improvements. Is this request related to: https://support.fivetran.com/hc/en-us/community/posts/44054713980567-Request-to-have-environment-variable-to-disable-enable-DB2i-capture-log-info ?

    If so, we can treat these requests as one.

    Best regards,
    Edwin

    Hi Satya,

    You can remove capturing all this logging info by setting HVR_DB2I_JOURNAL_RECEIVER_SCAN_MESSAGE=1. From the next HVR releases this will be the default.

    For your other questions

    The Time Profiler measures elapsed (wall-clock) time that HVR spends in each kind of activity, such as SQL, network I/O, log scanning and idle waiting. It does not measure CPU time directly. 

    1. How can we use HVR_TIME_PROFILER_TRACE to identify the time used by HVR Capture?

    To enable the Time Profiler:

    -On the hub, add HVR_TIME_PROFILER_TRACE=MEDIUM as a Job System Environment Variable.
    -On the source (capture) location and the target location, add an Environment action with /Name=HVR_TIME_PROFILER_TRACE /Value=MEDIUM.
    -Activate the channel.

    The setting takes effect when the capture job starts. If the timing lines below don't appear in the capture job log after activation, restart the capture job.

    Available levels:

    • LOW: one total per category, per cycle. This is enough for an overall picture.

    • MEDIUM: categories are split by one extra detail. For example, SQL is split by operation (SELECT, INSERT, …) and Idle by reason.

    • HIGH: SQL is also split by statement label and table. This is needed to see the individual journal-read queries separately.

    Start with LOW or MEDIUM, and use HIGH only for short periods.

    1. Which entries or metrics should be reviewed?

    After each capture cycle, the capture job log shows a summary for the hub and for the agent:

    <Hub> Cycle timing breakdown is: FProc 0.08 seconds, Network-IO 1.02 seconds, SQL 0.00 seconds.

    <Agent> Cycle timing breakdown is: SQL 12.30 seconds, Log-scan 1.20 seconds, Idle 45.00 seconds, ...

    The same breakdown is also shown as statistics in the HVR User Interface (Job Breakdown)

    The most relevant categories on the Agent line are:

    • SQL: time spent running the journal-read queries against Db2 for i. This activity is what uses CPU on the IBM i. At MEDIUM or HIGH, the detail lines also show how often the queries ran:

    $HVR_TIME_PROFILER_TRACE: <Agent> SQL(operation=SELECT) called: 31 times, 0.120s.

      The number of calls per minute is a good indicator of how much query load HVR is putting on the IBM i.

    • Idle: time HVR spends waiting between polls when no new changes were found. This is controlled by HVR_SCAN_POLL.

    • Log-scan: time HVR spends processing the journal entries it has received.

    • Network-IO / FPipe / Disk-IO: time spent sending data to the hub and writing it out.

    The categories may not add up exactly to the total cycle time, because small amounts of processing aren't assigned to any category.

    1. Is there a recommended way to compare CPU usage before and after changing HVR_SCAN_POLL?

    HVR_SCAN_POLL (default 0.5 seconds) sets how long capture waits before checking the journal again after a check found no new changes. A longer interval means fewer journal queries while the system is quiet. This lowers CPU use on the IBM i, but changes can take up to that long to be picked up. During periods of continuous change activity, the setting has little effect.

    We recommend:

    -Measure a baseline with the current setting over a representative period, at least several hours.
    -From HVR side, measure the Time Profiler breakdown (SQL time, SQL call count, Idle time).
    -Change HVR_SCAN_POLL (Environment action on the capture location), activate the channel, and repeat the same measurement over a comparable period with similar workload.
    -Compare rates, not single-cycle totals, because cycle lengths vary. Useful rates are SQL calls per minute, SQL seconds per minute, Idle seconds per minute. With a longer poll interval, you should see more Idle time, fewer SQL calls during quiet periods.
    -Check capture latency in both periods, so you can confirm the CPU saving doesn't cause unacceptable delay.

    1. Does enabling the Time Profiler create extra logs or storage overhead, and how should it be disabled afterwards?

    • Logs/storage: no separate files are created. The Time Profiler adds lines to the existing job log once per cycle, and these are subject to normal log retention:

    • LOW adds two lines per cycle.

    • MEDIUM adds one line per category detail.

    • HIGH can add many lines per cycle, which is why we recommend using it only for short periods.

    • Performance overhead: negligible at LOW. Small but measurable at MEDIUM and HIGH when change volumes are high.

    • To disable: set HVR_TIME_PROFILER_TRACE=OFF on the hub (Job System Environment Variable) and on the source and target locations (Environment action), or remove those settings. Then activate the channel. If the timing lines continue to appear, restart the capture job.

    Note: an issue that could cause job crashes when the Time Profiler was enabled was fixed in HVR 6.3.0/4 (and 6.3.5/1).
    See also:
    https://app.notion.com/p/dbtlabs/Time-Profiler-38bbb38ebda781959545fa47ab8fbf43

    https://app.notion.com/p/dbtlabs/How-to-enable-Timing-Profiler-38bbb38ebda78197975ac90f771badc5

    Time Profiler - HVR 6 | Fivetran Documentation