Q-Replication Apply program stopped

Gopalan Venkatramani

Q-Replication Apply program stopped

A table was recreated with REC_ID which was primarily the generated always column from the source. Someone tried to insert values in this table without a value passed to REC_ID column and eventually the apply program got broke. I know what INSERT they have tried from the table IBMQREP_EXCEPTIONS. Now I need to clear this and start the apply program again. How can I achieve this ? 

Kindly assist...

Thanks

Glenn

Edited By:
Gopalan Venkatramani[Organization Members] @ Aug 17, 2017 - 02:13 PM (America/Eastern)

Gautam Sahu

Q-Replication Apply program stopped
(in response to Gopalan Venkatramani)
If you want to inactivate the sub for this table and bring up apply then
follow these steps -
1. Set the column STATE to 'I' in the TARGETS table for this table.
2. Start apply.

Regards,
Gautam

On Thu, 17 Aug 2017 at 11:41 PM, Gopalan Venkatramani <[login to unmask email]>
wrote:

> A table was recreated without the REC_ID which was primarily the generated
> always column from the source. Someone tried to insert values in this table
> and eventually the apply program got broke. I know what INSERT they have
> tried from the table IBMQREP_EXCEPTIONS. Now I need to clear this and start
> the apply program again. How can I achieve this ?
>
> Kindly assist...
>
> Thanks
>
> Glenn
>
> -----End Original Message-----
>
--
Cheers,
Gautam
Mo - +91 9880712917
If you want something you've never had then you have to do something you've
never done. Thomas Jefferson

Gopalan Venkatramani

RE: Q-Replication Apply program stopped
(in response to Gautam Sahu)

 

Is there a way I can discard those INSERT and start the apply program including that table ?  Because this is going to happen in future.

Thank you 

Glenn

Gautam Sahu

Q-Replication Apply program stopped
(in response to Gopalan Venkatramani)
Can you tell me what error did you get for the insert?

From your earlier mail I understand that you removed the column REC_ID from
the table. Did you remove it from the target table and the subscription?

Gautam



On Fri, 18 Aug 2017 at 10:05 AM, Gopalan Venkatramani <[login to unmask email]>
wrote:

>
>
> Is there a way I can discard those INSERT and start the apply program
> including that table ? Because this is going to happen in future.
>
> Thank you
>
> Glenn
>
> -----End Original Message-----
>
--
Cheers,
Gautam
Mo - +91 9880712917
If you want something you've never had then you have to do something you've
never done. Thomas Jefferson

Gopalan Venkatramani

RE: Q-Replication Apply program stopped
(in response to Gautam Sahu)

Gautham - Thanks for your response. Let me explain. 

Both source and target has the REC_ID column.

 In source REC_ID is generated always column.

In target REC_ID is just the INT data type matching the source.

The replication was working perfectly fine. 

One application got pointed to target and did a InSERT. So while INSERT the application did not insert the value for rec_id assuming it’s generated always. 

Because of this the whole apply got down. So I just want to discard this Insert and start the apply program. I can provide you the INSERT error. But I do see the InSert from the ecceptiin table that I mentioned above.

Glenn

Pete Suhner

RE: Q-Replication Apply program stopped
(in response to Gopalan Venkatramani)

Hi Glenn,

fixing errors on a replication target table always involves manual analysis of the data and correlation between source and target to figure out the differences. Obviously, the exception table might help. I'd say that removing these records from the target table should suffice, but I'm not a QRep specialist. Thus, other listers might want to clarify this point.

As you mention that "this is going to happen in the future": I would strongly advise to fix the root cause of the issue. 

From a conceptual point of view, replicas should only be modified by the replication process (i.e. consider them as staging tables from a business process point of view). If you want to "replicate" data into a table which is also modified by the application, then consider other mechanisms (e.g. data ingestion).

As far as I understand, it was simply the application connecting to the wrong database. Thus you could implement proper security profiles (i.e. use segregate users and ensure that only the replication user is allowed to modify data on the target table) - done.

In case the business process is implemented so that both, the application and the replication process are writing to the replica table, then this design should be corrected. Depending on the application implementation, aliases/synonyms might help with keeping the efforts to a minimum.

Best regards,

Pete Suhner
IDUG Board of Directors
IBM Champion for Analytics

Gautam Sahu

Q-Replication Apply program stopped
(in response to Gopalan Venkatramani)
You can ask apply to ignore a transaction or a range of transactions by
using the *skiptrans* option of *asnqapp* or *asnqacmd* command. Search for
the string 'Prompting a Q Apply program to ignore transactions', you should
get the link where it is explained.

You should also take steps so that such a situation doesn't arise in
future.

From my understanding of the problem here is what you can do -

Alter the column REC_ID to an IDENTITY column with GENERATED BY DEFAULT on
the target side. This will ensure that QREP does not face any issue while
inserting a row on the target.

I assume this is your replication key.

Since an application is inserting into this table at the target you need to
make sure that the value generated by DB2 at source and target are
different. One easy way is to have an odd and even combination.

Hope this helps.

Gautam

On Fri, 18 Aug 2017 at 11:56 AM, Gopalan Venkatramani <[login to unmask email]>
wrote:

> Gautham - Thanks for your response. Let me explain.
>
> Both source and target has the REC_ID column.
>
> In source REC_ID is generated always column.
>
> In target REC_ID is just the INT data type matching the source.
>
> The replication was working perfectly fine.
>
> One application got pointed to target and did a InSERT. So while INSERT
> the application did not insert the value for rec_id assuming it’s generated
> always.
>
> Because of this the whole apply got down. So I just want to discard this
> Insert and start the apply program. I can provide you the INSERT error. But
> I do see the InSert from the ecceptiin table that I mentioned above.
>
> Glenn
>
> -----End Original Message-----
>
--
Cheers,
Gautam
Mo - +91 9880712917
If you want something you've never had then you have to do something you've
never done. Thomas Jefferson

Gopalan Venkatramani

RE: Q-Replication Apply program stopped
(in response to Gautam Sahu)
asnqapp apply_server=targetdb apply_schema=ASN
skiptrans="Q1;0000:0000:0000:0000:51a1,
Q2;0000:0000:0000:0000:51b0-0000:0000:0000:0000:51c0"

Gautam - I found the syntax. But which table will have these Q values ? I checked in Target_Schema.IBMQREP_SPILLEDROW. There are 15 rows with the table that got failed SPILLQ and the MQMMSGID. 
            

         SPILLQ                                                                                          MQMSGID

IBMQREP.SPILL.MODELQ.0.54.1                      x'414D5120514D54475420202020202020C0F48D5928017B20'

 I mean is this MQMSGID is what I need to skip ? or need to get from some other table ?

 

Gopalan Venkatramani

DB2 LUW