Changing the length of a key field in a table

[srinivasa_raghavachar]

Hi,

I want to increase the length of the field from 2 to 4 in a standard SAP table and deliver it to the customers. This field is a key field in table. This field from this table is also used in view and view clusters.

What is the implication of changing the length to the customers. The customers would have already data in this field and they should not loose any data. Will the existing data for customers remain at length 2 or do they have to do some conversion?

Regards,

Srini.

Edited by: Srinivasa Raghavachar on Feb 7, 2008 12:45 PM

[amit_khare]

Check this [Thread|;

[Former Member]

hi,

The database table can be adjusted to the changed definition in the ABAP Dictionary in three different

ways:

By deleting the database table and creating it again. The table on the database is deleted, the inactive

table is activated in the ABAP Dictionary, and the table is created again on the database. Data

existing in the table is lost.

By changing the database catalog (ALTER TABLE). The definition of the table on the database is

simply changed. Existing data is retained. However, indexes on the table might have to be built again.

By converting the table. This is the most time-consuming way to adjust a structure.

If the table does not contain any data, it is deleted in the database and created again with its new

structure. If data exists in the table, there is an attempt to adjust the structure with ALTER TABLE. If the

database system used is not able to do so, the structure is adjusted by converting the table.

Field 1 Field 2, Field 3

NUMC,6 CHAR 8 CHAR, 60

Field 1 Field 2 Field 3

NUMC,6 CHAR, 8 CHAR,30

The following example shows the steps necessary during conversion.

Starting situation: Table TAB was changed in the ABAP Dictionary. The length of field 3 was reduced

from 60 to 30 places.

The ABAP Dictionary therefore has an active (field 3 has a length of 60 places) and an inactive (field 3

still has 30 places) version of the table.

The active version of the table was created in the database, which means that field 3 currently has 60

places in the database. A secondary index with the ID A11, which was also created in the database, is

defined for the table in the ABAP Dictionary.

The table already contains data.

Step 1: The table is locked against further structure changes. If the conversion terminates due to an

error, the table remains locked. This lock mechanism prevents further structure changes from being

made before the conversion has been completed correctly. Data could be lost in such a case.

Step 2: The table in the database is renamed. All the indexes on the table are deleted. The name of the

new (temporary) table is defined by the prefix QCM and the table name. The name of the temporary

Step 3: The inactive version of the table is activated in the ABAP Dictionary. The table is created on the

database with its new structure and with the primary index. The structure of the database table is the

same as the structure in the ABAP Dictinary after this step. The database table, however, does not

contain any data.

The system also tries to set a database lock for the table being converted. If the lock is set, application

programs cannot write to the table during the conversion.

The conversion is continued, however, even if the database lock cannot be set. In such a case

application programs can write to the table. Since in such a case not all of the data might have been

loaded back into the table, the table data might be inconsistent.

You should therefore always make sure that no applications access the table being converted

during the conversion process.

Step 4: The data is loaded back from the temporary table (QCM table) to the new table (with MOVECORRESPONDING).

The data exists in the database table and in the temporary table after this step.

When you reduce the size of fields, for example, the extra places are truncated when you reload the

data.

Since the data exists in both the original table and temporary table during the conversion, the storage

requirements increase during the process. You should therefore verify that sufficient space is available in

the corresponding tablespace before converting large tables.

There is a database commit after 16 MB when you copy the data from the QCM table to the original

table. A conversion process therefore needs 16 MB resources in the rollback segment. The existing

database lock is released with the Commit and then requested again before the next data area to be

converted is edited.

When you reduce the size of keys, only one record can be reloaded if there are several records whose

key cannot be distinguished. It is not possible to say which record this will be. In such a case you should

clean up the data of the table before converting.

Step 5: The secondary indexes defined in the ABAP Dictionary for the table are created again.

Step 6: The temporary table (QCM table) is deleted.

Step 7: The lock set at the beginning of the conversion is deleted.

If the conversion terminates, the table remains locked and a restart log is written.

Caution: The data of a table is not consistent during conversion. Programs therefore should not access

the table during conversion. Otherwise a program could for example use incorrect data when reading the

table since not all the records were copied back from the temporary table. Conversions therefore

should not run during production! You must at least deactivate all the applications that use tables to

be converted.

You must clean up terminated conversions. Programs that access the table might otherwise run

incorrectly. In this case you must find out why the conversion terminated (for example overflow of the

corresponding tablespace) and correct it. Then continue the terminated conversion.

Since the data exists in both the original table and temporary table during conversion, the storage

requirements increase during conversion. If the tablespace overflows when you reload the data from the

temporary table, the conversion will terminate. In this case you must extend the tablespace and start the

conversion in the database utility again.

If you shorten the key of a table (for example when you remove or shorten the field length of key fields),

you cannot distinguish between the new keys of existing records of the table. When you reload the data

from the temporary table, only one of these records can be loaded back into the table. It is not possible

to say which record this will be. If you want to copy certain records, you have to clean up the table

before the conversion.

During a conversion, the data is copied back to the database table from the temporary table with the

ABAP statement MOVE-CORRESPONDING. Therefore only those type changes that can be executed

with MOVE-CORRESPONDING are allowed. All other type changes cause the conversion to be

terminated when the data is loaded back into the original table. In this case you have to recreate the old

state prior to conversion. Using database tools, you have to delete the table, rename the QCM table to

its old name, reconstruct the runtime object (in the database utility), set the table structure in the

Dictionary back to its old state and then activate the table.

If a conversion terminates, the lock entry for the table set in the first step is retained. The table can no

longer be edited with the maintenance tools of the ABAP Dictionary (Transaction SE11).

A terminated conversion can be analyzed with the database utility (Transaction SE14) and then

resumed. The database utility provides an analysis tool with which you can find the cause of the error

and the current state of all the tables involved in the conversion.

You can usually find the precise reason for termination in the object log. If the object log does not

provide any information about the cause of the error, you have to analyze the syslog or the short dumps.

If there is a terminated conversion, two options are displayed as pushbuttons in the database utility:

After correcting the error, you can resume the conversion where it terminated with the Continue

adjustment option.

There is also the Unlock table option. This option only deletes the existing lock entry for the table .

You should never choose Unlock table for a terminated conversion if the data only exists in the

temporary table, i.e. if the conversion terminated in steps 3 or 4. table for table TAB is therefore QCMTAB.

Hope this is helpful,Do reward.

[Former Member]

Hi,

When we shorten the key of a table, for example when you shorten the field length of key field, you cannot distinguish between the new keys of existing records of the table. When you reload the data from temporary table, only one of these records can be loaded back into the table.

Can anyone tell me, why only one record is loaded back into the table?

[Former Member]

Hi,

While changing the length of the key field you will get a syntax error.

To correct and proceed in se11 go to Utilities->Database Object->Database Utility . There activate and adjust the database. There is a radio button there saying Save Data. This prevents any data loss due to the table key change.

Regards,

Renjith Michael.

[Former Member]

Hi ,

Kindly note teh points below:

1 . You can change the size from 2 to 4 and for automacity you should change the corresponding domain.

2. As its a SAP table you also need to check its impact on other things for example , if that field is used as an parameter for any standard function module then running that function module will result in dump.

3. Also, see what other tables are using that domain and look for the check table.

4. Overall , you need to have a very deep analysis on its impact before changing the standard SA[P tables key fields.

Regards,

Uma Dave