topic Re: Unable to get delete statement in Qlik Replicate

Unable to get delete statement

SkippyTheMagnificent — Tue, 24 May 2022 21:28:17 GMT

All

Had a replicated table get suspended today with the following error:

Failed to get delete statement for table SIPMiner.RESULTS, stream position 411;637889683643563270;20220419144516826916|0000001b.347cb822.00000002.0000.01.0000:10873.2961722.16

How do I troubleshoot and resolve this so I can unsuspend the table without losing any data?

Appreciate the help

Nathan

Re: Unable to get delete statement

KellyHobson — Tue, 24 May 2022 22:21:13 GMT

Hey @SkippyTheMagnificent ,

What is your source and target endpoint? If you are able, can you attach a diagnostic package or a log at the time of the error to this thread?

Ref: https://community.qlik.com/t5/Knowledge/How-to-collect-Diagnostics-Package-from-Qlik-Replicate/ta-p/1815056

Great handle name by the way!

Best,

Kelly

Re: Unable to get delete statement

lyka — Tue, 24 May 2022 22:46:19 GMT

Hi Nathan,

unfortunetaly the only way to resume from a suspended table is to reload.

what i think you can can try to do is to create a separate task just for this table and start from timestamp.

thanks
lyka

Re: Unable to get delete statement

Heinvandenheuvel — Wed, 25 May 2022 13:11:12 GMT

If the task gets to the 'get delete statement' it has already gotten the metadata for the table.

My guess is that the metadata was not useable, possibly because a PK was expected but no PK was found?

Was the (target) table (metadata) changed behind Replicate's back - maybe while the task was running?

Did this ever work for any row? I don't think this is triggered by a surprised data values but I suppose that could be with funny characters in the key column values perhaps - wag!

Can you isolate the table in a test/dev task and run with maximum logging for details?

You may need repctl dumpmetadata output to figure this out.

Hein.

Re: Unable to get delete statement

SkippyTheMagnificent — Wed, 25 May 2022 13:48:19 GMT

Source is Oracle 19c. Target is SQL Server 2019. Table is 'Results'

Diag package attached.

Re: Unable to get delete statement

SkippyTheMagnificent — Wed, 25 May 2022 13:49:40 GMT

This is disappointing. 800m record table. Would really like to understand what went wrong before I reload to see if there are things that can be done to prevent the same issue from occurring again.

Nathan

Re: Unable to get delete statement

Steve_Nguyen — Wed, 25 May 2022 13:54:38 GMT

@SkippyTheMagnificent

- look like your task is using Logstream, so the CDC come from the parent task.

- the full load come from the source. so the error look like from CDC.

- so best to run with source_capture , target_apply, target_load to get more information , if issue happen again.

- make sure you are latest kit, 2021.11 sp5 or new 2022.5 kit

Re: Unable to get delete statement

SkippyTheMagnificent — Wed, 25 May 2022 13:56:59 GMT

No known changes to either source or target table. Defintions still match. Been working for weeks. Over 11m DML statements applied before the error occurred. No errors in the attrep_apply_exceptions table either.

Not used the repctl command before. Where can I find some documentation on it?

Re: Unable to get delete statement

SkippyTheMagnificent — Wed, 25 May 2022 14:05:52 GMT

Running 2021.5.0.863 build.

I'd agree issues was with CDC.

Re: Unable to get delete statement

Steve_Nguyen — Wed, 25 May 2022 14:32:03 GMT

@SkippyTheMagnificent

1, kit look ok.

2. try to set trace to get more info.

3. repctl command are only for export and import / if you look for API, look into QEM API.

Re: Unable to get delete statement

SkippyTheMagnificent — Wed, 25 May 2022 20:35:06 GMT

I've increased logging (trace level) and reloaded the table. Guess we'll see what I get if it happens again.

Re: Unable to get delete statement

Dana_Baldwin — Wed, 25 May 2022 20:50:52 GMT

Hi @SkippyTheMagnificent

I'm afraid I don't have any further technical info besides what was already shared, but..

I too appreciate your user name! My brother is a stage hand with a nickname of Skip, which is short for "Skippy the Wonder Dog" due to his incredible problem solving skills 🙂

Re: Unable to get delete statement

SkippyTheMagnificent — Thu, 26 May 2022 14:27:47 GMT

Issue happened again last night. Here are the 2 diag package for the task and the log stream.

Issue occurred at 5/26 @ 12:53 am. Logging level = 'trace' for source_capture , target_apply, target_load

Re: Unable to get delete statement

SkippyTheMagnificent — Fri, 27 May 2022 15:04:57 GMT

Issue occurred again. Had verbose logging turned on this time. What is going on? Can anyone help me identified why this keep failing with the same error.

@lyka @Steve_Nguyen @Dana_Baldwin @KellyHobson @Heinvandenheuvel

Re: Unable to get delete statement

lyka — Fri, 27 May 2022 15:13:22 GMT

Hi @SkippyTheMagnificent

Ill review the files you attached. I suggest opening a support case to further troubleshoot this so that the files you upload will be only visible to support and your organization

Thanks

Lyka

Re: Unable to get delete statement

KellyHobson — Fri, 27 May 2022 15:24:25 GMT

Hey @SkippyTheMagnificent ,

I agree with @lyka to open a support ticket, so we can look at TARGET_APPLY with trace or verbose logging. It may helpful to isolate SIPMiner.RESULTS to a separate task as well.

Thanks,

Kelly