Day 6

The Data Dictionary, Part 4


Chapter Objectives

After you complete this chapter, you should be able to:

Automatic Table History and Change Documents

Changes to data within a table can be automatically logged. Such automatic logging of changes is called automatic table history. To turn on logging, tickmark the Log Data Changes check box on the Technical Settings screen (refer to Figure 5.4). If not already done, the Basis consultant must also specify the client number(s) for which logging is to take on the system profile parameter rec/client.

TIP
To display the rec/client setting in your system, follow the procedure used to display rdisp/bufreftime in the section titled "Buffer Synchronization" in Day 5.

Change Documents

For each insert, update, or delete to a table enabled for automatic table history, a record is created in table dbtabprt. Each record is called a change document. Each change document contains the date and time of the change, the userid of the user who made the change, the transaction code and program name used to make the change, and the type of change performed. The type of change will be INS if the record was inserted, UPD if the record was updated, or DEL if the record was deleted.

Using transaction code SCU3 or OY18 (see Figure 6.1), you can display the change documents and compare them to current values in the table. There is no difference between these two transaction codes; they both run the same program.

Figure 6.1 : The Table History transaction displays change documents from tables with automatic table history enabled.

From the Table History - Choose Function screen, you can perform the following functions:

Figure 6.2 : The fields of table HPR1007 appear before the change documents, showing the field names, primary key indicator, data type, length, column name, and description.

Figure 6.3 : The change documents of table HPR1007 appear after the field list. The primary key of each record appears first (in turquoise) followed by the data (in white).

Figure 6.4 : Here, the data in dbtabprt is compared to the current contents of each record.

The primary key appears in turquoise on the left half of the screen. The right half of the screen is divided into current values and history values. Gray lines indicate that a difference exists. To display the meaning of the codes in the left-most column, place your cursor there and press F1. This report shows that four records exist in table EKPA but no history records exist for them.

Knowing When to Use Automatic Table History

Automatic table history should be used for tables that contain critical information and when every change must be tracked without fail, regardless of the update program used. Unfortunately, updates to the table are slowed down because a record is written to dbtabprt for each record changed. In most cases when table history is needed, Change Document Objects provides a faster and more efficient alternative, and so should instead be used. For more information on Change Document Objects, see the R/3 Library (Menu path Help->R/3 Library, Basis Components, ABAP/4 Development Workbench, Extended Applications Function Library, Change Documents.)

CAUTION
If the primary key of your table is longer than 86 bytes, or if the remainder of the row is longer than 500 bytes, you cannot use automatic table history. You will get an error when you try to activate the table and changes will not be logged. The reason for these restrictions lies in table dbtabprt. The vkey field contains the key of the record that was changed and is 86 bytes long. The vdata field contains the remainder of the record and is 500 bytes long.

Summarizing the Technical Settings

The following summarizes the technical settings for buffering, table extents, and the automatic logging of updated information:

Revised and Active Versions

Two versions of a table can exist (or any DDIC object): the Revised version and the Active version. If you change a table and press the Save button without pressing Activate, you have created a Revised version of the table that contains your changes. The Active version still exists; it is the last version that was activated. ABAP/4 programs only use Active versions. The presence of Revised versions does not affect them.

The Revised version exists to enable you to prepare a change before it is needed and then activate it when it is required. It also enables you to change many objects and then activate them all simultaneously. When you activate them, the currently Active versions are discarded and your Revised versions become active and replace them.

The Revised version is displayed, if it exists, when you display a table (the Status field will contain Revised). The Application toolbar will have an Active Version button (see Figure 6.5). If you press it, the Active version will be displayed and the button on the toolbar will change to Revised Version, enabling you to press the button again and return to the previous display.

Figure 6.5 : When a table that has a Revised version is displayed, you see the Revised version by default.

Start the ScreenCam "How to Compare Revised and Active Versions" now.

To compare the Revised and Active versions:

  1. From the Dictionary: Table/Structure: Display Fields screen, choose the menu path Utilities->Version management. The Versions of Object screen is displayed. The first line contains the word mod.. This is the Revised version. The next line contains act.. This is the Active version.
  2. To compare the two versions, tickmark them both and press the Compare pushbutton. The Version Comparison for Tables screen is displayed. The changed lines are indicated in the right margin. <- only activ means that line only exists in the Active version. <- only modif means that line only exists in the Revised version.
  3. To see a summary of changes, press the Delta display button on the Application toolbar. The list is updated to contain only the differences between the two versions.

Additional Versions

Aside from the Active and Revised versions, you can also create temporary versions
of a table. To do this, choose the menu path Table->Generate version. The message Temporary version of active object stored. appears at the bottom of the window. This temporary version is kept until the table is transported into production To view the new version, choose the menu path Utilities->Version Management. The version with the highest number is the one you just created.

Discarding a Revised Version

To discard a Revised version without activating it, you must first generate a temporary version from the Active version and then restore it, as shown in the following procedure.

Start the ScreenCam "How to Discard a Revised Version" now.

To discard a Revised version and restore the Active version:

  1. From the Dictionary: Table/Structure: Change Fields screen, choose the menu path Table->Generate Version. A dialog box appears containing the message Version of >> active << object stored.
  2. Press the Continue button. You are returned to the Dictionary: Table/Structure: Change Fields screen, and the message Temporary version of activate object stored is displayed at the bottom of the window.
  3. Choose the menu path Utilities->Version management. The Versions Of Object screen is displayed. There will be a tickmark beside the word act..
  4. Remove the tickmark beside the word act..
  5. Place a tickmark beneath the Version(s) In The Version Database: line beside the highest version number.
  6. Press the Retrieve button on the Application toolbar. A dialog box appears containing the message Version nnnnn is now new revised (non-active) version. Proceed?.
  7. Press the Yes button. You are returned to the Versions Of Object screen, and the message Restored version must be activated! appears at the bottom of the window. There will be a tickmark beside the word act.. The Revised version is now the same as the Active button.
  8. To verify that the Revised version matches the Active version, place a tickmark beside the word mod.. Both the act. and mod. lines should now be tickmarked.
  9. Press the Compare button on the Application toolbar. The Version Comparison For Tables screen is displayed.
  10. Press the Delta Display button on the Application toolbar. The list should contain a line reading General attributes: Unchanged and another line that reads Fields: Unchanged. You have now confirmed that the Active and Revised versions match.
  11. Press the Back button on the Standard toolbar. You are returned to the Versions of Object screen.
  12. Press the Back button on the Standard toolbar. You are returned to the Dictionary: Table/Structure: Change Fields screen.
  13. Press the Activate button on the Application toolbar. The Status field contains Act. and the message xxxxx was activated appears at the bottom of the window.

All DDIC objects, such as domains and data elements, have Revised and Active versions. They can all be displayed and compared the same way. Objects can use only Active versions. For example, if you modify a domain and create a Revised version, data elements using it continue to use the Active version until you activate the domain.

Using the Database Utility

Within the DDIC lies a database utility tool. Using it, you can examine and modify tables at the database level.

Start the ScreenCam "How to Access the Database Utility" now.

To access the database utility:

  1. Begin at the Dictionary: Initial Screen.
  2. Enter a table name in the Object Name field.
  3. Press the Display pushbutton.
  4. Select the menu path Utilities->Database Utility. The ABAP/4 Dictionary: Utility for Database Tables screen is displayed, as shown in Figure 6.6.

Figure 6.6 : Using the ABAP/4 Dictionary: Utility for Database Tables screen, you can communicate directly with the database to display or change a table.

From here you can:

Consistency Checks

The definition of a transparent table exists in two places: the R/3 data dictionary and the database. To check for consistency between the two, from within the database utility choose the menu path Extras->Database Object->Check. The Active version of the table is compared to the database table. The Table xxxxx: Check Database Object screen will be displayed, and a message at the top of the list will indicate whether the database object is consistent (see Figure 6.7).

Figure 6.7 : A consistency check against the database confirms that the R/3 DDIC definition of table ztxlfa1 and the database definition are identical.

Inconsistencies can arise if:

You might do a consistency check if, when testing a program, you get an unusual SQL error, or if you get incorrect results from code that should work fine but inexplicably does not. Finding an inconsistency indicates that the source of the problem might lie outside of your program.

An R/3 table is in some ways like a traditional program. It exists in two forms: the "source" that you can display and modify, and the compiled form that is used at runtime, called the runtime object. The runtime object is created when you activate the table, and is also known as the nametab.

When a consistency check is performed, the nametab is compared against the database.

You can display the nametab from the database utility by choosing the menu path Extras->Runtime object->Display. The Object xxxxx: Display Active Runtime Object screen is displayed (see Figure 6.8). At the top is the time stamp of the nametab followed by the header information. It contains the table type (T for transparent), the table form in the database (again, T for transparent), the number of fields in the table, the length in bytes of the record, the number of key fields, the length of the key fields in bytes, buffering information, and more. (For detailed information on the header fields and their values, display the structure X030L.) Below the header is a list of the fields, their position in the table, the data type, length, number of decimal places, offset from the beginning of the record, external length the reference table, the check table, and more. The technical attributes of the table are completely described by the nametab.

Figure 6.8 : The nametab is the runtime object for a table. It contains all the table's technical characteristics such as field names, data types, and lengths.

Role of the Nametab in ABAP/4 Programs

As you learned on Day 2, when you create an ABAP/4 program that selects data from a table, you must code a tables statement. The tables statement makes the structure of the table known to the program. However, when you generate the runtime object for the program, the definition of the table is not embedded into it. Instead, when the program's runtime object is executed, it makes a call to the nametab to determine the table's structure at runtime. This enables you to change a table (or structure) without having to regenerate all the ABAP/4 programs that use it. They dynamically determine the table characteristics at runtime by calling the nametab.

Although you do not have to regenerate all programs every time a change is made to a table, certain changes (such as renaming or deleting a field) will require you to make changes to the ABAP/4 code. In these cases, you will need to find all programs that use it.

Start the ScreenCam "How to Perform a Where-Used List on a Table" now.

To find all programs that use a given table:

  1. Begin at the Dictionary: Initial Screen.
  2. Enter the table name in the Object Name field.
  3. Press the Where-Used List button on the Application toolbar. A Where-Used List dialog box appears.
  4. Choose the Programs radio button.
  5. Press the Continue button. The Table xxxxx in Programs screen is displayed. A list of all programs using the table appears.
  6. Double-click on a program name to display all lines of code in which the table is used.
  7. Double-click on a line to display the code starting from that line.

Checking the Consistency of the Nametab

The nametab obtains its characteristics from the data elements and domains that make up the table. It is possible for the definition of the nametab to be out of sync with the data elements and domains. For example, when you activate a change to a domain, the tables containing it must also be reactivated to pick up those changes. Although this happens automatically, in certain situations a reactivation can fail because of a database restriction or because the table contains data and must be converted. This situation can be detected by performing a consistency check on the runtime object.

To check for consistency between the nametab and the Data Dictionary objects, from within the database utility choose the menu path Extras->Runtime Object->Check. The runtime object is compared to the DDIC source objects. The Object xxxxx: Check Active Runtime Object screen will be displayed, and a message at the top of the list will indicate whether the database object is consistent. Figures 6.9 and 6.10 show an example of an inconsistency in the nametab for table ztxlfa1.

Figure 6.9 : In this screen, a consistency check indicates an error message that the record length, key length, and table alignment are inconsistent.

Figure 6.10: Scrolling down in the list shows the fields of the nametab and the reason for the inconsistency.

If a domain is changed and the table reactivation fails, an inconsistency can exist between the nametab and the domain.

In the case of Figure 6.10, the check found that the data type of field lifnr is char 10 in the nametab and int4 in the domain.

ABAP/4 programs only use the nametab. Therefore, they will not know about inconsistencies, and thus are unaffected by them in the Development system. Transporting the DDIC objects before correcting inconsistencies can cause problems during import to Q/A or production, or even cause ABAP/4 programs in these environments to produce incorrect results. The reason lies in the fact that the changes have not affected the ABAP/4 programs, and thus have not yet been tested.

Displaying the Activation Log

From within the database utility, you can display the activation log by pressing the Object Log button on the Application toolbar. This log is only generated if the activation causes SQL to be generated. (In some cases, a table change can be activated without affecting the database, for example, a change to the short text.) The log contains the sequence of steps performed and all SQL statements sent to the database during the last activation (see Figures 6.11 and 6.12).

Figure 6.11: The activation log displays the sequence of steps performed during activation and the SQL that was generated. This log shows that during the activation, the table was dropped in the data-base and re-created.

Figure 6.12: This is the remainder of the activation log shown in Figure 6.11.

To display detail information describing any message in the log, position your cursor on the message and press the F1 key.

Displaying and Modifying Storage Parameters

Pressing the Storage Parameters button from within the database utility (refer to Figure 6.1) displays the DB Utility: Transparent Tables - Database Parameters screen. Figure 6.13 shows how this screen looks when an Oracle database is installed. This screen is primarily for use by the DBA to alter the storage parameters from within R/3 after a table has been activated. It is presented here for the sake of completeness.

Figure 6.13: Database-specific parameters are shown on the DB Utility: Transparent Tables - Database Parameters screen. This screen shows that storage parameters for table ztxlfa1 have been altered after activation because the DBS and CMP lines do not match.

Database-specific information about the table's storage parameters is displayed here. The Src column indicates the source of the values. DBS means these values are the actual values in the database. CMP means these values are computed from the size category and data class. The USR line enables you to enter values for NextE, MaxE, Pf, and Pu and apply them immediately by pressing the Apply Parameters NextE, MaxE, Pf, and Pu Immediately pushbutton. Most of the time, the DBS and CMP lines will be the same, unless the storage parameters for the database table were altered manually after the table was activated.

The InitExt and NextExt columns contain the sizes (in blocks) of the initial and next extents. The MinE and MaxE columns contain the minimum and maximum number of extents allowed. The tablespace name is next, followed by the FG and Fr columns, which contain the number of freelist groups and the number of freelists in a group. (These two columns are only used if Oracle is being used in parallel processing mode.) The Pf and Pu columns contain the percentage of free space and percentage of space used in the data blocks. For more information on any column, position your cursor on it and press F1.

Although the only parameters that can be changed are NextExt, MaxE, Pf, and Pu, you must completely fill the line before pressing the Apply pushbutton. For example, to change the size of the next extent to 64 and to set the maximum number of extents to 4, first select the USR radio button. Then type 64 in the NextExt column, type 4 in the MaxE column, copy the rest of the values from the DBS line, and then press the Apply pushbutton.

Your changes are immediately applied in the database when you press the Apply pushbutton. To view the results, press the Back button to return to the ABAP/4 Dictionary: Utility for Database Tables screen, and then press the Storage Parameters button on the Application toolbar. The line having DBS in the Src column will contain the updated values.

Dropping and Re-Creating Tables in the Database

The fastest way to delete the data from a table is to "drop" it and then re-create it. To drop a table, from within the database utility press the Delete Database Table pushbutton. A dialog box will be displayed to confirm your request. The table and its contents will be deleted from the database. To create it again, press the Create Database Table pushbutton. The table will be re-created using the Active version of the table. You can use the database utility to do this instead of writing an ABAP/4 program to delete all rows.

CAUTION
Dropping a table causes all data within it to be permanently lost. It is a good idea to make a backup copy of the table before you delete it. If you copy the table manually, remember to copy both the table definition and the data.

TIP
If the table has more than one extent, dropping and re-creating a table is a fast and easy way to reorganize it. You will need to save the data to another table temporarily before dropping it, and then copy it back.

Summary

Q&A

Q
Where would a DBA go to alter the storage parameter of an activated table from within the SAP R/3 system?
A
Go to the database utility and press the Storage parameters pushbutton.

Workshop

The Workshop provides two ways for you to affirm what you've learned in this chapter. The Quiz section poses questions to help you solidify your understanding of the material covered and the Exercise section provides you with experience in using what you have learned. You can find answers to the quiz questions and exercises in Appendix B, "Answers to Quiz Questions and Exercises."

Quiz

  1. Is it possible that the definition of the table and the data elements and domains can be out of sync?

Exercise 1

  1. What is the fastest way to delete the data from a table?