Unable to add column to a table in 16.0 SP03 PL12.

[DilipVoora]

Hi Experts,

When we are trying to add column to a table (DRL/DPL) that has an unique clustered index in a database called phoenix (for which we did a project called database resizing in June 2022, followed by patching to SP03 PL12, instrumented fix, SP03 PL12 HF1 and then a one-off) we end up with an error below. As soon as we see the error we followed the SAP note 2392526 and then we were able to add the column. Now for a table with less rows like this doesn't took much tme for index creation and what could be the solution incase if we want to add a column to a table that has millions of records like a history table. Does our resizing(attachment -steps followed for resizing details) caused any problem ? What is the meaning of global placement index? Please advise.

Msg 13917, Level 16, State 1:Server 'POSB_ETHIX_PROD', Line 1:

ALTER TABLE 'AD_GB_EXCH_RATE' failed. You cannot alter a roundrobin partitioned table with a global placement index when the operation requires data copy. Drop the index and try again.

Msg 11050, Level 16, State 132:

Server 'POSB_ETHIX_PROD', Line 1:

Adaptive Server cannot process this ALTER TABLE statement due to one or more preceding errors. If there are no preceding errors, please contact SAP Technical Support.

Observation:

The table structure after adding the column and recreating the index has two partitions names - one in small and the other in capital with different ID as their suffix with same creation date but we are unable to understand the exact reason behind this cause. Attached are the details.

NOTE: The error is happening only for the existing tables in the database but not for newly created tables.

Regards,
Dilip Voora

[former_member188958]

What is the meaning of global placement index?

"placement index" is another name for a clustered index on a data-only-locking (DOL) table. In tables with the allpages (APL) lock scheme, if there is a clustered index, rows are inserted in exact key order - if necessary splitting the page and moving following rows to the new page to make room for the new row in the right spot. In DOL tables existing rows aren't moved. When inserting a new row, ASE finds the row that would preceed the new row in key order and places the new row as close as possible where it can find sufficient free space on a page.

Local vs Global is described in the docs here: at https://help.sap.com/docs/SAP_ASE/a08646e0736e4b4b968705079db4c5f5/a8fc011ebc2b10148f5eed96e951ff36....

[former_member188958]

A possible solution for this is to use the new NOT MATERIALIZED option with the ALTER TABLE ADD COLUMN command:

drop table t1
go
create table t1 (c1 int) lock datarows
go
create unique clustered index i1 on t1 (c1)
go
insert t1 select id from sysobjects
go
print "column can be added when there is only one partition..."
alter table t1 add c2 int default 2 not null
go
alter table t1 partition by roundrobin 2
go
print "column can not be added when there are multiple partitions..."
alter table t1 add c3 int default 3 not null
go
print "column can be added if we avoid the need for data copy by using the NOT MATERIALIZED option..."
alter table t1 add c3 int default 3 not null not materialized

go

[DilipVoora]

Hi Bret,

Can you explain the exact behaviour of this option with respect to the physical column that is going to be added to the table? We request you to put this in the case we opened if it is still with you.

Regards,
Dilip Voora

[former_member188958]

The first test on the PL09 system creates an index but does not partition the table,
the second test partitions the table but does not create an index.
The error is only seen in the second test on PL12, which is the only one that has both the index and partitioning.

[DilipVoora]

Thanks for looking into and the correction Bret .

[Mark_A_Parsons]

The error message (13917) appears to be related to a known limitation of the alter table/add command ...

Key items of interest from this scenario:

• from the error message: (roundrobin) partitioned table
• from the error message: global placement (aka clustered) index
• from the attachment: table is using the datarows locking scheme (ie, data-only-locking)

From Reference Manual: Commands: Alter Table: Altering Table Schemas we have the following item showing that an alter table/add is not supported for the combination of partitioned table + clustered index + data-only-locking :

--------------------

--------------------

As mentioned in the 2nd half of this manual entry the alter table/add command can only proceed if the clustered index is removed from the table.

Everything else mentioned in the question appears to be a red herring:

• database (re)sizing should not affect whether or not the alter table/add command runs in this scenario
• applying various ASE software/product patches should not affect whether or not the alter table/add command runs in this scenario
• NOTE: I don't have access to the mentioned SAP Note so can't directly comment as to why it 'works' in this scenario, unless it has something to do with a) dropping the clustered index before running the alter table/add command or b) copies data between tables (w/ target table containing the new column)

As for "The error is happening only for the existing tables in the database but not for newly created tables" ... are you saying these 'newly created tables' meet all 3 criteria - partitioned, clustered index, DOL - and you are able to successfully run an alter table/add command without any issues? as displayed in the above table, if any of the 3 criteria are missing then, yes, an alter table/add command should complete without issue

[DilipVoora]

Hi Mark,

The restrictions are referred as soon as we saw the error and it looks like it is applicable till 15.5 version as the column addition to the table that met the 3 criteria was successful. When did a repro for two patch levels and attached the results for your advise.

Repro done on two patch levels - 16.0 SP03 PL09 on RHEL 8.2 and 16.0 SP03 PL12 HF1 with one-off (ebf30601) on AIX 7.2.

******************************************************************************************************************************************************

From 16.0 SP03 PL09 on RHEL 8.2 : Refer the attchements 'SP03 PL09 - Test1 and Test2'

Scenario 1:

1. Created table, altered the locking cheme to DRL, checked the partition id, inserted 5 rows.

2. Created unique clustered index on the first column of the table and then inserted 5 rows.

3. Add column c (third one) with default as 0 and it was successful(10 rows effected).

Scenario 2:

1. Created table, inserted 2 rows, altered table for 2 partitions of round robin, inserted 3 rows.

2. Here the sp_help <table_name> showed two partitions with partition_name as 'table name with a random number'. Don't know how these numbers are generated.

3. Now altered the table scheme to DRL.

4. Added thrid column successfully (5 rows effected).

5. Noticed that the second 3 rows that were are on second partition.

6. Added 3 more rows and now they are distributed (4 on first partition and the remaining on second).

7. Inserted one more row and it is on first partition as expected.

8. Added fourth column successfully (9 rows effected).

*****************************************************************************************************************************************************

From 16.0 SP03 PL12 HF1 with one-off (ebf30601) on AIX 7.2: Refer the attchements 'SP03 PL12 HF1 with 1-off - Test1 & Test2'

Scenario 1:

1. Created table, inserted 5 rows, altered the locking cheme to DRL.

2. Create a clustered index on first column of the table.

3. Inserted 5 more rows to the table.

4. Added a new column to the table and it was successful with 10 rows effected.

Scenario 2:

1. Created table, altered the table for 2 partitions of round robin and noticed that there are two partitions with their names having 'table name with a random number.

2. Inserted 5 more rows and created a unique clustered index on the table's first column.

3. Here the 10 rows in the table are even distrubuted across two partitions.

4. Altered the table to DRL.

5. Inserted 3 more rows into the table and these got inserted to the second partition.

6. Now when I tried to add a third column to the table it failed with the error 13917 (cannot alter a roundrobin partitioned table with global palcement index).

Due to a bug in the SP03 PL09 we received an instrumented fix and then we patched to SP03 PL10, SP03 PL12, SP03 Pl12 HF1 and finally applied 1-off on top of it.

So, we assume something happened in between PL09 and the 1-off. Do you agree?

Regards,
Dilip Voora

[Mark_A_Parsons]

If the same exact test generates different results under different versions of the software, and the differences are not due to documented changes to the software, then yes it sounds like something's wrong with the software (or the software has been changed but not documented); a case would need to be opened with tech support to find out what's going on.

If the documentation does not match the software's beahaviour, again, a case would need to opened with tech support in order to get clarification.