Ensure values of the newly added fields to the database table in SE11 for the existing records are left as NULL and a default value is not filled in them.

INTRODUCTION:

This blog post highlights with details how to ensure that NULL values are assigned to the the newly added fields for the existing records in the database tables, so that huge amount of time is not wasted to fill them with default values. This requirement is even more relevant for Production systems where the database tables already have millions of existing records.

Issue statement:

While adding a new field to the Standard tables, we want the values of the newly added field for all the existing records in the table to be marked as NULL.

However, even if INITIALIZE is left unchecked for this newly added field, the corresponding database object that is generated, automatically has NOT_NULL as X.

Because of this, for all the existing records, system assigns default value of 0 or ‘ ‘, which we don’t want since all the older records will be looped and assigned the value 0. This process will be hugely time consuming in the Production systems since the tables have millions/billions of existing records.

Example:

Solution:

Using INCLUDE structure:

1. Add the field to the Include structure

2. Type ‘NULL’ into the OK code field and press enter

3. Double click on the field

4. On the pop-up, tick “null values allowed”. If you don’t type NULL in the OK code and press enter, check box for “null values allowed” won’t appear in the pop-up.

5. Confirm pop-up

6. Save include

7. Now call SE11 and go to the table in change mode

8. Type ‘NULL’ into OK code field and press enter

9. Double click on the ‘.INCLUDE’ of the corresponding include

10. On the pop-up tick “null values allowed”

11. Confirm pop-up

12. Save table.

13. Now activate include and table. Database Object shows that NOT_NULL has not been marked as X for this newly added field.

14. For confirmation that for the existing records in the table, NULL has been assigned to the newly added field:

Write a query in ST04–> SQL command editor

select count(*) from DFKKKO

9,234

select count(*) from DFKKKO where ZZTEST = 0

0

select count(*) from DFKKKO where ZZTEST IS NULL

9,234

Using APPEND structure:

1. SE11 –> Table –> Display –> Append Structure

2. Add the field to the Append structure

3. Type ‘NULL’ into the OK code field and press enter

4. Double click on the field

5. On the pop-up tick “null values allowed”

6. Confirm pop-up

7. Save an activate the table. Database Object shows that NOT_NULL has not been marked as X for this newly added field.

8. For confirmation that for the existing records in the table, NULL has been assigned to the newly added field:

Write a query in ST04–> SQL command editor

select count(*) from DFKKKO

9,234

select count(*) from DFKKKO where ZZTEST2 = 0

0

select count(*) from DFKKKO where ZZTEST2 IS NULL

9,234