Open SQL Statements and Cluster Databases (SAP Library

Open SQL Statements and Cluster Databases

Cluster databases are relational databases, defined in the ABAP Dictionary, that have a special use in ABAP. In principle, you can use Open SQL to access cluster databases.

However, to use SQL statements properly with cluster databases, you must take into account their special structure (refer to Structure of a Cluster Database).

For example, there is little sense in reading the fields CLUSTR and CLUSTID using the SELECT statement, or changing them using UPDATE. These fields contain the coded data clusters, which can only properly be dealt with using EXPORT TO DATABASE and IMPORT FROM DATABASE.

You should only use the Open SQL statements UPDATE, MODIFY, and DELETE if the runtime of the corresponding data cluster statements is too long. Do not use the Open SQL statement INSERT at all in cluster databases.

You can use Open SQL statements to maintain the cluster database. For example, you can use SELECT statements to search the cluster database for particular clusters, using information from the user data fields (see example in the section Storing Data Objects in Cluster Databases). This is not possible with the IMPORT FROM DATABASE statement.

Example

PROGRAM SAPMZTS5.
DATA COUNT TYPE I VALUE 0.
TABLES INDX.
SELECT * FROM INDX WHERE RELID = 'HK'
                   AND   SRTF2 = 0
                   AND   USERA = SY-UNAME.
DELETE FROM DATABASE INDX(HK) ID INDX-SRTFD.
IF SY-SUBRC = 0.
    COUNT = COUNT + 1.
  ENDIF.
ENDSELECT.
WRITE: / COUNT, 'Cluster(s) deleted'.

This example program deletes all data clusters in the area "HK" from table INDX that have the name of the current user in the field USERA. The SELECT statement fills the field SRTFD in the table work area INDX, which is used in the DELETE statement. By specifying SRTF2 = 0 in the WHERE clause, you ensure that each data cluster is only processed once.

Caution

Do not confuse the Open SQL DELETE statement with the DELETE statement used for data clusters (refer to Deleting Data Clusters from Cluster Databases). Always delete all of the lines in a data cluster, not just a selection.

The following example demonstrates how you can change the name and area of a data cluster in a database table using the Open SQL statement UPDATE. This task would have required considerably more effort using the cluster statements EXPORT, IMPORT, and DELETE.

Example

PROGRAM SAPMZTS5.

TABLES INDX.

DATA DIRTAB LIKE CDIR OCCURS 10 WITH HEADER LINE.

UPDATE INDX SET RELID = 'NW'
            SRTFD = 'Internal'
                               WHERE RELID = 'HK'
                               AND   SRTFD = 'Table'.

WRITE: / 'UPDATE:',
/ 'SY-SUBRC:', SY-SUBRC,
/ 'SY-DBCNT:', SY-DBCNT.

IMPORT DIRECTORY INTO DIRTAB FROM DATABASE
INDX(NW) ID 'Internal'.

WRITE: / 'IMPORT:',
/ 'SY-SUBRC:', SY-SUBRC.

This example changes the data cluster stored in the cluster database in the example from the section Saving Database Objects in Cluster Databases. If the data cluster exists when the program is run, and no other errors occur in the UPDATE statement, the output is as follows:

UPDATE:

SY-SUBRC: 0

SY-DBCNT: 6

IMPORT:

SY-SUBRC: 0

The UPDATE statement changes the six lines of the database table INDX that belong to the specified data cluster. Afterwards, the IMPORT DIRECTORY statement finds the data cluster in the area "NW" under the name "Internal".