Troubleshooting Tips for Alter Operations

Alter operations on replicated tables might result in errors.

The following problems illustrate common issues with performing alter operations on replicated tables:

Problem: You receive an error that the replicate is not defined after running the following command:
```
cdr alter -o test:tab
Error:Replicate(s) not defined on table test:.tab
```
The owner name is missing from the table name, test:tab.
Solution: Include the table owner name, for example:
```
cdr alter -o test:user1.tab
```

Problem: You receive an error that the replicated table is in alter mode after running the following command:

> insert into tab values(1,1);

19992: Cannot perform insert/delete/update operations on a replicated table
while the table is in alter mode
Error in line 1 Near character position 27
>

The table (tab) is in alter mode. DML operations cannot be performed while the table is in alter mode.

Solution: Wait for the table to be altered and then issue the DML operation. If no alter statement is in progress against the table, then unset alter mode on the table using the cdr alter --off command. For example:

cdr alter --off test:user1.tab

You can check the alter mode status using the oncheck -pt command. For example:

$ oncheck -pt db1:user1.t1

TBLspace Report for db1:user1.t1

    Physical Address             1:63392
    Creation date                02/01/2011 16:02:00
    TBLspace Flags               400809     Page Locking
                                            TBLspace flagged for replication
                                            TBLspace flagged for CDR alter mode
                                            TBLspace use 4 bit bit-maps
    Maximum row size               4
...

Problem: How can you tell if a replicate is a mastered replicate?
Solution: You can check the alter mode status using the oncheck -pt command. For example:
```
oncheck -pt test:nagaraju.tab
```

Problem: How can you tell if a replicate is a mastered replicate?

Solution: When you execute the cdr list repl command, it shows that the REPLTYPE is Master for master replicates. For example:

$cdr list repl
CURRENTLY DEFINED REPLICATES
-------------------------------
REPLICATE: rep2
STATE: Active ON:delhi
CONFLICT: Timestamp
FREQUENCY: immediate
QUEUE SIZE: 0
PARTICIPANT: test:nagaraju.tab12
OPTIONS: transaction,ris,ats,fullrow
REPLTYPE: Master

REPLICATE: rep1
STATE: Active ON:delhi
CONFLICT: Timestamp
FREQUENCY: immediate
QUEUE SIZE: 0
PARTICIPANT: test:nagaraju.tab
OPTIONS: transaction,ris,ats,fullrow

In the above output, rep1 is defined as a non-master replicate and rep2 is defined as master replicate.

Problem: An alter operation on a replicated table fails.
For example:
```
$dbaccess test -

Database selected.

> alter table tab add col4 int;

19995: Enterprise Replication error encountered while setting alter mode. See
message log file to get the Enterprise Replication error code
Error in line 1Near character position 27
>
```
The message log output is:
```
12:36:09 CDRGC: Classic replicate rep1 found on the table test:nagaraju.tab
12:36:09 CDRGC:Set alter mode for replicate rep1
12:36:09 GC operation alter mode set operation on a replicated table failed:
Classic replicate(s) (no mastered dictionary) found on the table.
```
Solution: The above message shows that there is a classic replicate, rep1, defined on the table (tab). Adding a new column to a replicated table is allowed when only master replicates are defined for the table.
To perform the above alter operation, first convert the classic replicate to a master replicate. You can convert the replicate definition of rep1 to a master replicate by issuing the following command:
```
cdr remaster -M g_delhi rep1 "select * from tab"
```
Now look at the cdr list repl output:
```
$cdr list repl
CURRENTLY DEFINED REPLICATES
-------------------------------
REPLICATE: rep1
STATE: Active ON:delhi
CONFLICT: Timestamp
FREQUENCY: immediate
QUEUE SIZE: 0
PARTICIPANT: test:nagaraju.tab
OPTIONS: transaction,ris,ats,fullrow
REPLTYPE: Master

REPLICATE: rep2
STATE: Active ON:delhi
CONFLICT: Timestamp
FREQUENCY: immediate
QUEUE SIZE: 0
PARTICIPANT: test:nagaraju.tab12
OPTIONS: transaction,ris,ats,fullrow
REPLTYPE: Master

REPLICATE: Shadow_4_rep1_GMT1112381058_GID100_PID29935
STATE: Active ON:delhi
CONFLICT: Timestamp
FREQUENCY: immediate
QUEUE SIZE: 0
PARTICIPANT: test:nagaraju.tab
OPTIONS: transaction,ris,ats,fullrow
REPLTYPE: Shadow
PARENT REPLICATE: rep1
```
You can see that repl1 has been converted to a master replicate. You can also see that a new replicate definition, Shadow_4_rep1_GMT1112381058_GID100_PID29935, was also created against the table (tab1). Notice the last two fields of the output for Shadow_4_rep1_GMT1112381058_GID100_PID29935:
```
REPLTYPE: Shadow
PARENT REPLICATE: rep1
```
The Shadow attribute indicates that this replicate is a shadow replicate, and PARENT REPLICATE: rep1 shows that this is a shadow replicate for the primary replicate rep1. Notice that the Master attribute is not present for this replicate definition. This shadow replicate is actually the old non-master replicate. The cdr remaster command created a new master replicate, rep1, for the table tab and converted the old non-master replicate (rep1) to a shadow replicate for the new master replicate.
This table is not yet ready to be altered because there is still a non-master replicate, Shadow_4_rep1_GMT1112381058_GID100_PID29935, defined for the table, tab. You must wait for Shadow_4_rep1_GMT1112381058_GID100_PID29935 to be deleted automatically by Enterprise Replication after all the data queued for this shadow replicate is applied at all the replicate participants. This process can take some time. Alternatively, if you are sure that there is no data pending for this old non-master replicate, then you can issue the cdr delete repl command against Shadow_4_rep1_GMT1112381058_GID100_PID29935.

After making sure that Shadow_4_rep1_GMT1112381058_GID100_PID29935 no longer exists, you can attempt the ALTER TABLE tab add col4 int; statement against the table.