topic Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ? in Qlik Replicate

[SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

iti-attunity-sup — Mon, 01 Apr 2024 00:47:01 GMT

Hello.

My Customer is using SAP Sybase ASE as a source.
In following prerequisites, it is stated that "trunc log on chkpt" option need to be false.
https://help.qlik.com/en-US/replicate/November2023/Content/Replicate/Main/SAP%20Sybase%20ASE/prereq_sap_sybase_ase.htm#ar_sybaseds_2343061694_1630969

Would by tell me the reason why "trunc log on chkpt" option need to be false?

Best Regards.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

john_wang — Mon, 01 Apr 2024 00:58:44 GMT

Hello Team,

Thanks for reaching out to Qlik Community!

Qlik Replicate is transaction log based product. It retrieves data changes from various database transaction log. To make sure the data integration the transaction log must be available prior to Qlik Replicate process them rather than being truncated or purged.

In Sybase ASE source endpoint, setting "trunc log on chkpt" to false is used to disable Sybase's automatic log truncation (i.e. truncate on checkpoint) for the database.

In place of Sybase's automatic log truncation the Replicate task will be configured to periodically
call the "attrep_dumpaction" stored procedure which executes a "dump transaction with truncate_only" command to do the truncation.

Hope this helps.

John.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

iti-attunity-sup — Thu, 04 Apr 2024 02:29:59 GMT

Hello John-san

Thank you for your reply.
I have another quetion about this topic.
If it is need to post as new case, please tell me.

Because "second truncation point" is enabled, my customer think if "trunc log on chkpt" set true, log does not be truncated before update "second truncation point".

So, Would tell me concern about if "trunc log on chkpt" set true ?

Best regards.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

john_wang — Thu, 04 Apr 2024 02:37:49 GMT

Hello @iti-attunity-sup ,

You are welcome to post questions here always 🙂

However seems I did not get the question well this time. would you please elaborate or provide a sample to help understand the concern.

thanks,

John.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

iti-attunity-sup — Thu, 04 Apr 2024 02:49:20 GMT

Hello John-san.

Thank you for your reply.

My customer want to execute Qlik Replicate task in state of setting "trunc log on chkpt" true.
So, is there any problem to set "trunc log on chkpt" true.

Best Regards.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

john_wang — Thu, 04 Apr 2024 02:57:08 GMT

Hello @iti-attunity-sup ,

thanks for the update - setting "trunc log on chkpt" to True means Sybase database can automatic truncate its transaction log (i.e. truncate on checkpoint) for the database and causes error and/or leads data missing potential risk. This scenarios is not supported.

Regards,

John.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

iti-attunity-sup — Thu, 04 Apr 2024 05:07:00 GMT

Hello John-san
Thank you for your reply.

Qlik Replicate is set "secondary truncation point" as following
https://help.qlik.com/en-US/replicate/November2023/Content/Replicate/Main/SAP%20Sybase%20ASE/remove_truncation_point.htm#ar_sybaseds_2343061694_1632136

So, this prevent truncation of unread transaction logs.
I recognized that this setting is given priority over setting "trunc log on chkpt" true.

Base on above mentioned,
I would like to know problem about setting "trunc log on chkpt" true.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

iti-attunity-sup — Thu, 11 Apr 2024 00:21:45 GMT

Hello John-san
It's been a week, how is this situation?

Best Regards.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

john_wang — Thu, 11 Apr 2024 00:57:02 GMT

Hello @iti-attunity-sup ,

Seems I did not catch up the previous comments very well. Sorry for the delay.

>> Base on above mentioned,
>> I would like to know problem about setting "trunc log on chkpt" true.

If I understand correctly, you are meaning what's the impact of setting "trunc log on chkpt" to true?

If yes, as we explained, setting "trunc log on chkpt" to True means Sybase database can automatic truncate its transaction log (out of Qlik Replicate control), this is NOT what we expected. Instead, we have to disable the automatic truncation to make sure the transaction logs are available until Replicate consume them. Certainly we have to make sure the transaction logs can be clean up, this is done by endpoint settings Dump the log & Advance truncation point options.

Feel free to let me know if you need any additional information,

John.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

iti-attunity-sup — Thu, 11 Apr 2024 01:25:17 GMT

Hello John-san
Thank you for your reply.

>setting "trunc log on chkpt" to True means Sybase database can automatic truncate its transaction log
I recognize that by setting "secondary truncation point", Sybase database doesn't truncate unread transaction log.
(Qlik Replicate is setting "secondary truncation point")

And I recognize that setting "secondary truncation point" is given priority over setting "trunc log on chkpt" to true.

What I want to know is following,
Though "secondary truncation point" is set, why "trunc log on chkpt" need to be set true.

Best Regards.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

iti-attunity-sup — Thu, 11 Apr 2024 01:41:14 GMT

Hello John-san

Sorry, following is mistake.
>Though "secondary truncation point" is set, why "trunc log on chkpt" need to be set true.

Correct quetions is following,
Though "secondary truncation point" is set, why "trunc log on chkpt" need to be set false.

Best Regards.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

john_wang — Thu, 11 Apr 2024 01:51:38 GMT

Hello @iti-attunity-sup ,

>> Though "secondary truncation point" is set, why "trunc log on chkpt" need to be set false.

trunc log on chkpt is Sybase database itself behavior, it's totally out of Qlik Replicate control and it's higher priority than Qlik Replicate operations. So in summary:

1- turn off Sybase itself auto-trunc log function

2- on behalf of Sybase, call the secondary truncation point option via Qlik Replicate

Regards,

John.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

iti-attunity-sup — Thu, 11 Apr 2024 08:02:54 GMT

Hello John-san
Thank you for your reply.

In our environment, I tried following.
・While stop task, executed DML in state of setting "trunc log on chkpt" true.
・After that execute checkpoint.

In this state, I resumed task, but error didn't occur.

I recognize that as specification, "secondary truncation point" is given priority over setting "trunc log on chkpt" to true on Sybase database.

So, Is "trunc log on chkpt" need to be false?
If error occurs, I want to know the case.

Best Regards.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

john_wang — Thu, 11 Apr 2024 08:49:56 GMT

Hello @iti-attunity-sup ,

Maybe a simple sanity test cannot trigger any error but a regression test or heavy pressure test with volume data will show us the significant difference.

Thanks,

John.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

iti-attunity-sup — Fri, 12 Apr 2024 09:32:01 GMT

Hello John-san
Thank you for your reply.

My customer and I recognize following as Sybase specification
・By setting "secondary truncation point", Sybase database doesn't truncate unread transaction log.
・"secondary truncation point" is given priority over setting "trunc log on chkpt" to true.

Is this wrong ?
I would like you to answer Yes or No.

Best Regards.

Re: [SAP Sybase ASE Source] Why does "trunc log on chkpt" option need to be false ?

john_wang — Fri, 12 Apr 2024 09:38:49 GMT

Hello @iti-attunity-sup ,

I'm guessing I did not get the question well this time... however the scenario of setting "trunc log on chkpt" to true is NOT supported.

Regards,

John.