How Does Fivetran Calculate the tbl_id Value in the HVR Enroll File for Db2 for LUW?

Question

The HVR enroll file displays a tbl_id value, for example, 720901, for a specific table. However, this value doesn't match any value visible in Db2 for LUW. How does Fivetran calculate the tbl_id value in the HVR enroll file?

Environment

• HVR 6
• Source: Db2 for LUW

Answer

We calculate the tbl_id value in the HVR enroll file by combining the following two identifiers from the Db2 for LUW database:

• tbspaceid: The tablespace ID where the table resides.
• tbl_id: The unique identifier for the table within the database.

We convert these values into hexadecimal (HEX) format, concatenate them, and then convert the result into a single decimal number, which we store as the tbl_id value in the enroll file.

Example calculation

To understand the calculation, do the following:

1. Start with the tbl_id from the enroll file. For example, 720901.
2. Convert this value from decimal to HEX. For example, 720901 converts to B0005 in HEX format.
3. Split the HEX value into two parts:
   • The first portion, 000B, represents the tbspaceid.
   • The second portion, 0005, represents the tbl_id.
4. Convert each part back to decimal:
   • 000B in HEX converts to 11 in decimal, which is the tbspaceid value.
   • 0005 in HEX converts to 5 in decimal, which is the tbl_id value.

For this example, the enroll file’s tbl_id value of 720901 corresponds to:

• tbspaceid=11
• tbl_id=5

Reverse calculation

You can calculate the enroll file’s tbl_id value from known tbspaceid and tbl_id values by doing the following:

1. Convert the tbspaceid and tbl_id to HEX format. For example, the tbspaceid value 11 converts to 000B in HEX, and the tbl_id value 5 converts to 0005 in HEX.
2. Join the two HEX values into a single string. For example, combining 000B and 0005 results in 000B0005.
3. Convert the joined HEX string back to decimal. For example, 000B0005 in HEX converts to 720901 in decimal.