Skip to Content

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

 

CONCLUSION:

Using the above highlighted details and the mentioned steps, we can 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.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Shai Sinai

    Although NULL is commonly used in databases, it is a less common practice in SAP.

    The reason for that is that most of SAP frameworks/commands (e.g. selection screen) don’t support NULL values implicitly and special handling is required. Like in you example, selecting value EQ initial_value, whereas actual value is NULL, won’t return any results.

    Moreover, ABAP data types doesn’t support NULL values (There is no NULL-equivalent in ABAP).

    Hence, I wonder what is the use case for this requirement of maintaining NULL values?

     

    P.S.

    Many times ABAP developers are coping with the opposite case: When adding new fields to existing table (whose values are already maintained), NOT_NULL isn’t set by default, unless Initialize flag is set. This may lead to unexpected results of selections.

    The problem there is in case the field is part of include/append structure of SAP-standard table/structure. In such case, parent structure cannot be modified and its Initialize flag cannot be set.

    The solution/trick there is to use the “secret” feature in transaction SE14: Goto -> Force conversion.

    (3) 
    1. Vijay Chanchlani
      Post author

      Hello Colleague,

      Use case is that, one of the SAP Customers, wants to add a new timestamp field via a custom include to some of the database tables for data extraction purposes. These tables already have millions/billions of existing records.

      If these millions/billions of existing records are looped and default value is assigned, it’s going to be hugely time consuming and we might even get time-out related dumps. Hence, they want for the newly added fields NULL to be assigned instead. I guess, they will do an initial load for these existing records and the newly added field with correct timestamp values will help with the extraction later on.

      For reference:

      https://help.sap.com/viewer/ec1c9c8191b74de98feb94001a95dd76/7.5.9/en-US/cf21ebe3446011d189700000e8322d00.html

      Thanks and Regards,

      Vijay.

      (0) 
      1. Shai Sinai

        Thanks for the clarification.

        As far as I know, adding new field to an existing table (with existing records) shouldn’t set NOT_NULL by default, unless Initialize flag is explicitly set.

        (2) 
  2. Michelle Crapo

    Interesting.  I know at the database level – I had to set the value to null when for when it is used for an outside application.  (The outside app had access to only the Z tables that were created in a Z program.  I was told they had to have “null”.)

    I didn’t know about this shortcut or I would have used it.

    (0) 
  3. Sandra Rossi

    The shortcut NULL is almost the same as the following menu option, which adds the column “Initial” (same thing for the “key field” attribute, too):

    :

    (0) 
    1. Vijay Chanchlani
      Post author

      Hello Sandra,

      This doesn’t translate to the DB object that is created.

      Our main issue was that even after leaving the INITIALIZE checkbox unchecked, DB object was created with NOT_NULL as X automatically.

      Thanks and Regards,
      Vijay.

       

      (1) 
      1. Sandra Rossi

        Thank you for correcting me. I’m very surprised that this feature has been completely changed by SAP. A few years ago, the “initial” checkbox was to be ticked to let new columns with the NULL value. And I don’t find any official reference except maybe the note 1387135 11g: DDIC support for ALTER TABLE with DEFAULT/NOT NULL but it’s specific to Oracle. The fact that the “null values” checkbox is hidden behind a “secret okcode” is a mystery to me. And what to say for customer includes of standard tables, the clients can’t modify the checkbox at the .Include level unless a modification key is requested. I’m interested to read more about official documentation or any justification if you can find one (I see that you are a SAP employee so maybe you can get more information).

        (0) 
        1. Vijay Chanchlani
          Post author

          Landed on another Oracle specific article regarding the topic of NOT_NULL and DEFAULT, and thought it might be relevant to what you were mentioning:

          https://www.oracle.com/technetwork/articles/database/ddl-optimizaton-in-odb12c-2331068.html

          Summary of the article:

          Starting from Oracle 11g, a new optimization technique was introduced improving the performance of Data Definition Language such that when an ALTER statement is executed to add a new field with DEFAULT and NOT NULL parameters, the new column is added to the table structure almost instantaneously. The existing records of this table are NOT updated with the default values for this newly added field. However, when these existing records are retrieved, Oracle tries to replace the NULL values with the default values at that time.

          Therefore, as I mentioned, in SE11 even if we leave the INITIALIZE checkbox unchecked, the database object that is created automatically has NOT_NULL marked as X.

          The customer in my case did not want that expecting that the DDL statements will be hugely time-consuming specially in Production systems, as it will try to fill the default values for the existing records in the newly added field.

          Based on the details in the above blog we can see that this above mentioned behavior won’t be happening (Customer is on Oracle 12c).

           

          (1) 

Leave a Reply