Adapters > Using the Adapter for Adabas > Adabas Writing Considerations

Adabas Writing Considerations

Topics:

The Adapter for Adabas is designed to support SQL update commands for an Adabas data source without the use of remote procedures. These topics describe the methods and rules related to write capability.

Adabas Write Adapter

Before you can use any commands that write to an Adabas data source, you must make the following changes to the Master and Access Files:

In the Master File:

  • All segments must have SEGTYPE = S0 (for the Read Adapter, SEGTYPE=S is sufficient).
  • Fields with ACTUAL format Z (zoned) must have a numeric USAGE format (P or I). Format A is supported only for reading an Adabas data source.
    Note: For the best performance, you can change ACTUAL format Z to ACTUAL format P in the Master File. In this case, you must also change the ALIAS attribute to contain the length and type. If the ALIAS is ff, and the field length is lll, the ALIAS attribute should be coded as:
    ALIAS='ff,lll,P'
 

    For example, consider the following field declaration:

    FIELD=LEAVE_DUE  ,ALIAS=AU  ,USAGE=P2  ,ACTUAL=P2  ,$
 

    You would edit this declarations as follows:

    FIELD=LEAVE_DUE  ,ALIAS='AU,2,P'  ,USAGE=P2  ,ACTUAL=P2  ,$
 
  • If two or more fields in the Master File are synonyms (they refer to the same field in the data source and, therefore, have the same ALIAS attribute), only the first field encountered in the Master File can be used in INSERT and UPDATE commands. If a synonym other than the first is used in an INSERT command, it is ignored. If one is used in an UPDATE command, processing is terminated with the following error message:
    (FOC4565) IGNORED ATTEMPT TO CHANGE NONUPDATABLE FIELD

In the Access File:

  • The segment attribute WRITE=YES indicates that values of fields from the segment may be modified.
  • If a segment has the attribute ACCESS=ADBS, you can define a unique key by adding the following attribute: 
    UNQKEYNAME=name 

    where:

    name

    Is the name of the elementary or group field to be used as the unique key.

    The UNQKEYNAME attribute does not necessarily define the key described in the ADABAS FDT (the UQ option). The adapter uses it to decide which rules to apply in an INSERT, DELETE, or UPDATE command. If the UNQKEYNAME attribute does not correspond to the key described with the UQ option in the ADABAS FDT, Adabas and the adapter may not agree on whether a segment instance is unique. This can affect the results of INSERT commands. If this attribute is not present, the adapter uses the rules for modifying a segment with a non-unique key or no key. If it is present, the adapter uses the rules for modifying a segment with a unique key. Subsequent sections describe these rules.

  • We recommend the use of CALLTYPE=FIND instead of CALLTYPE=RL.

When using the Write Adapter for Adabas, the adapter automatically sets the values of the following two options:

  • ADABAS OPEN is set to YES.
  • FETCH is set to OFF for all segments.

SQL Commands for the Adapter for Adabas

How to:

Reference:

The Adapter for Adabas supports the following commands:

  • SQL INSERT
  • SQL UPDATE

    Note: Certain types of fields listed in the Master File cannot be updated. For more information, see Fields That Cannot be Updated.

  • SQL DELETE

The adapter issues a COMMIT after each INSERT, UPDATE, or DELETE call. If the Adabas process fails, the adapter issues a ROLLBACK. User rollback of the Adabas process is not supported for SQL commands.

Reference: Obtain Counts of Rows Updated or Deleted

While processing SQL transactions, you can issue the PASSRECS command to obtain counts of rows affected by each successfully executed SQL Passthru INSERT, UPDATE, or DELETE command.

Syntax: How to Count Records Updated, Inserted or Deleted 

ENGINE ADBSINX INT SET PASSRECS {ON|OFF}

where:

ADBSINX

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

INT

Indicates that the PASSRECS setting in this command will be applied globally to all adapters that support SQL INSERT, UPDATE, and DELETE commands.

ON

Provides the number of rows affected in the application program SCB count member after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command. ON is the default value.

OFF

Provides no information after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command.

In addition, the adapter updates the &RECORDS system variable with the number of rows affected. You can access this variable using Dialogue Manager.

Reference: Fields That Cannot be Updated

The SQL UPDATE command for the following types of fields is ignored:

  • Counter fields (ALIAS=xxC).
  • ORDER fields (ALIAS=ORDER).
  • Group fields.
  • Unique key fields (fields specified by the UNQKEYNAME attributes in the Access File).

The SQL UPDATE command for the following types of fields produces an error:

  • Synonym fields.
  • Fields created from subdescriptors or superdescriptors (as defined in the Access File).

Reference: Using Synonym Fields (Not Related to Creating a Synonym)

In a Master File, two or more field declarations can refer to the same Adabas field. Each duplicate field declaration after the first is called a synonym field. Such synonym fields can be used in report commands, but cannot be used in write commands. The following actions occur as a result of using synonyms in write commands:

  • Synonym fields used in SQL INSERT are ignored.
  • Synonym fields used in SQL UPDATE cause processing to terminate and generate the message: 
    (FOC4565) IGNORED ATTEMPT TO CHANGE NONUPDATABLE FIELD

Syntax: How to Insert Records Into an Adabas Data Source 

INSERT INTO mfname [(field1, field2, ...)]
VALUES ('value1','value2', ...)

where:

mfname

Is the name of the Master File to use.

field1, field2, ....

Is an optional list of fields to be inserted. If you omit the list, the value list must contain values for all fields in all segments in the Master File as long as the Master File only specifies a single path Adabas file. If the Master File is multi-path, the field list is required and can specify only a single path. Elementary fields not included in the field list have empty values in the data source. The field list can contain only elementary fields. Groups, subdescriptors, superdescriptors, and hyperdescriptors, cannot be used in the field list.

'value1', 'value2', ...

Is the list of values to be inserted. Each alphanumeric value must be enclosed in single quotation marks. If you specify the field name list, the value list only needs to specify, in field name list order, the values for the field names supplied. At a minimum, you must supply all the key fields for all the segments in the path to the target.

If a segment is not present, default values are generated for all fields that are not supplied, and the missing path segments are inserted along with the target segment.

Reference: Rules for Inserting Records Into an Adabas Data Source

  • For a segment with a unique key:

    The key field value is used to insert the target segment. If any additional fieldname=value pairs are supplied for a segment in the path, they are used to qualify that path segment. If a segment with ACCESS=ADBS has a unique key or group of keys, only these key fields should be used in the INSERT command. All other fields should be added to the record using the UPDATE command.

  • For a segment with a non-unique key or no key:

    If you want to insert an additional record for an existing key field, you must have at least one field in addition to the key field in the field list in order to make the record unique. If you do not, the insert is rejected.

  • For segments with ACCESS=PE or MU, if a new occurrence is inserted, you must set the occurrence number (ORDER field) to 0 (zero indicates the next occurrence) or to a value greater than the number of existing occurrences. This new occurrence is always inserted after the last existing occurrence. For example, if a PE or MU segment has two existing occurrences, the next occurrence added is always the third. If the occurrence with the given number already exists, processing terminates with the following message:
    (FOC4564) THIS OCCURRENCE ALREADY EXISTS. USE UPDATE COMMAND

    You should use the UPDATE command instead of INSERT in this case.

  • For segments in which the parent segment contains ACCESS=PE, and the child segment contains ACCESS=MU without the NU option in the ADABAS FDT:
    • If an INSERT command is issued for the parent (PE) segment alone, Adabas automatically inserts an empty child (MU) segment. You can use the UPDATE command to provide values for this child.
    • If one INSERT command is issued for the parent (PE) and child (MU) segments simultaneously, the first occurrence in the child segment is inserted by the Write Adapter for Adabas using the values from the INSERT command.
  • For segments in which the parent segment has ACCESS=PE, and the child segment has ACCESS=MU with the NU option in the ADABAS FDT, the empty child is automatically suppressed by Adabas. You can use an INSERT command for the parent and child segments separately or simultaneously.

Reference: Effect of UNQKEYNAME on INSERT Actions

The UNQKEYNAME attribute in the Access File determines how the adapter presents an INSERT command to Adabas. The UQ option in the ADABAS FDT and the specific field values listed in the INSERT command determine whether Adabas actually inserts the segment instance. The following table describes how these factors affect the result of the INSERT command. Assume that the Access File specifies UNQKEYNAME=EMPLOYEE_ID and that the employee ID value EMPID005 already exists in the data source:

Result of the INSERT Command for Existing EMPLOYEE_ID EDMPID005

EMPLOYEE_ID has the UQ Option in FDT

Fields in INSERT Command

Instance Inserted

No

EMPLOYEE_ID only.

No - rejected duplicate

Yes

EMPLOYEE_ID only.

No - rejected duplicate

No

EMPLOYEE_ID plus fields with values that do not already exist.

Yes

Yes

EMPLOYEE_ID plus fields with values that do not already exist.

No - message (FOC4561), RC=198

Result of INSERT Command when EMPLOYEE_ID is not in the field list

UNQKEYNAME = EMPLOYEE_ID

Instance Inserted

Yes

No - message (FOC4563)

No

Yes, with empty EMPLOYEE_ID value.

Syntax: How to Update Field Values in an Adabas Data Source 

UPDATE mfname SET field1 ='value1' [,field2 ='value2'...]
WHERE kfield1 ='kvalue1' [AND kfield2 ='kvalue2'...]

where:

mfname

Is the name of the Master File to use.

field1, field2, ...

Are the names of the fields to be updated.

'value1', 'value2', ...

Are the new values for the updated fields, enclosed in single quotation marks.

kfield1, kfield2, ...

Are, at a minimum, all of the key fields for all the segments in the path to the target. Only one target segment is updated. You can include additional fieldname=value pairs for the target or any segment in the path. When additional fieldname=value pairs are present for the target segment, these are used for change verification protocol processing. If all target fieldname=value pairs that are present match the Adabas segment field values, the segment is updated with the SET fieldname values. The field list can contain only elementary fields. Groups, subdescriptors, superdescriptors, and hyperdescriptors, cannot be used in the field list.

'kvalue1', 'kvalue2', ...

Are the values that identify the target segment, enclosed in single quotation marks.

Reference: Rules for Updating Records in an Adabas Data Source

  • For a segment with a unique key, the key field value and any additional fieldname=value pairs are used to qualify the target segment for an update.
  • For a segment with a non-unique key, you must supply the key field. If the WHERE criteria for this type of segment, in the path or target, do not identify a unique occurrence, the first occurrence found is updated. The use of Adabas descriptors for this type of segment is highly recommended for efficiency.
  • For a segment with no key, you must supply at least one fieldname=value pair. If the WHERE criteria for this type of segment, in the path or target, do not identify a unique occurrence, the first occurrence found is updated.

Syntax: How to Delete Records From an Adabas Data Source 

DELETE FROM mfname WHERE field1 ='value1' [AND field2 ='value2'...]

where:

mfname

Is the name of the Master File to use.

field1, field2, ...

Are, at a minimum, the names of all of the key fields for all the segments in the path to the target. Only one target segment is deleted; Adabas cascades the delete to dependent segments. Additional fieldname=value pairs may be included for the target segment and any segment in the path that has no key or a non-unique key. It is recommended to use Adabas descriptor fields for any additional fieldname=value pairs.

'value1', 'value2', ...

Are the values that identify the target segment to be deleted.

Reference: Rules for Deleting Records From an Adabas Data Source

  • For a segment with a unique key, the key field value is used to delete the target segment.
  • For a segment with a non-unique key, you must supply the key field. If the WHERE criteria for this type of segment, in the path or target, do not identify a unique occurrence, the first occurrence found is deleted. The use of Adabas descriptors for this type of segment is highly recommended for efficiency.
  • For a segment with no key, you must supply at least one fieldname=value pair. If the WHERE criteria for this type of segment, in the path or target, do not identify a unique occurrence, the first occurrence found is deleted.
  • A segment occurrence with ACCESS=PE or MU is deleted from the data source except if it is the last occurrence for an ACCESS=PE segment. Adabas only deletes the last occurrence if all fields have the NU option in the FDT. If they do not all have this option, the occurrence has empty values in all fields.
  • When you delete segments that have dependent segments, the DELETED counter for the session may have an incorrect value. For a first level segment with descendants, this counter is always incorrect. For a second level segment, this counter is incorrect if there are multiple descendant segments. For a third level segment, this counter is always correct.

WebFOCUS

Feedback