topic Re: Identifying a table by its number in Qlik Replicate

Identifying a table by its number

MoeE — Fri, 01 Dec 2023 00:13:49 GMT

Hi,

Quick question. I recently came across this message in the logs and I want to identify table 66.

[ASSERTION ]W: The 'UPDATE' for table 66 at stream position '8102;638364590427443750;20220115171419693140|000006ef.a3d1a7d2.00000001.005f.01.0000:530782.348716.16' changes PK to a PK already in use

It's from an Oracle source endpoint, and I was wondering if table 66 means the 66th table loaded in the Replicate task. So for example, if I started counting from table 1 mysql.columns_priv as shown in the image?

Of course I've also increased ASSERTION to verbose levels and am just currently waiting for the message to reappear to identify the table, but I would like to know how to identify it using the number alone.

Kind regards,

Mohammed

Re: Identifying a table by its number

Michael_Litz — Fri, 01 Dec 2023 00:18:56 GMT

Hi @MoeE

If you have the task log from when the full load was completed the tables are named and numbered so a search through it should reveal the table name.

Thanks,
Michael

Re: Identifying a table by its number

Dana_Baldwin — Fri, 01 Dec 2023 16:00:38 GMT

Hi @MoeE

If you don't have the task log from the full load, you can also get the table ID from file task_tables.sqlite in the ..\data\tasks\<TaskName> folder. You will need to download DB Browser for SQLite in order to open the file:

Downloads - DB Browser for SQLite (sqlitebrowser.org)

Once open, click on the Browse Data tab, and in the Table drop down, select tables_status. Find your table in the Name column and note the medadata_id.

Re: Identifying a table by its number

john_wang — Sat, 02 Dec 2023 02:50:52 GMT

Hello @MoeE ,

Please allow me to summarize the methods how to find out the "table ID", or table's medadata_id in a task:

1- Find out the relevant information from a complete task log file - No special logging level is needed. The default logging level, Information, is good enough.

(if the task log file was roll overed, or part of the files are purged then it's not applied)

A sample of the task log file:

2023-11-30T23:52:36:257733 [SORTER ]I: Reload for table Id 2 is requested (sorter_transaction.c:2473)
2023-11-30T23:52:36:257733 [SORTER ]I: Start collecting changes for table id = 2 (sorter_transaction.c:2348)
2023-11-30T23:52:36:265252 [TASK_MANAGER ]I: Start loading table 'SCOTT'.'TESTEPOCH' (Id = 2) by subtask 2. Start load timestamp 00060B60A3E936C0

2- Query the relevant information from the repository file "task_tables.sqlite" directly. See above @Dana_Baldwin 's comment.

I hope this helps.

Regards,

John.

Re: Identifying a table by its number

jude47 — Tue, 05 Dec 2023 09:51:18 GMT

The other system is easy since the length of what you type in defines what you're looking for. Bury the search commands inside an =if(len(QuestionCode)=4,Search for ICAO,if(len(QuestionCode)=3, Search for APC,Error message))

Re: Identifying a table by its number

MoeE — Wed, 06 Dec 2023 00:01:23 GMT

Thanks, this was very useful

Re: Identifying a table by its number

MoeE — Fri, 08 Dec 2023 04:27:36 GMT

Hi @john_wang @Dana_Baldwin ,

I discovered this can also be found by downloading the diagnostics package and then opening the tables_status json with a text editor. No need to download anything.

Regards,

Re: Identifying a table by its number

john_wang — Fri, 08 Dec 2023 04:31:29 GMT

Hello @MoeE ,

Definitely you are right! thank you so much for your great support!

And we can also get the total tables number within the task and other helpful information as well, including FL and CDC runtime information etc.

Best Regards,

John.