add

The EGL add statement places a record in a file, message queue, or database; or places a set of records in a database.


Syntax diagram for the add statement
record name
Name of the I/O object to add: a DLISegment, indexed, MQ, relative, serial, or SQL record
usingPCB pcbName
Option allowing you to specify the name of a PCB, as defined in your PSB record, to use instead of the default PCB.
with #dli{ dliStatement }
Option that allows an explicit DL/I ISRT call, as described in DL/I support. Leave no space between #dli and the left brace.
with #sql{ sqlStatement }
Option that allows an explicit SQL INSERT statement. Leave no space after #sql.
SQL dynamic array name
The name of a dynamic array of SQL records. The elements are inserted into the database, each at the position specified by the element-specific key values. The operation stops at the first error or when all elements are inserted.

An example is as follows:

	if (userRequest == "A")
		try 
			add record1;
		onException
			myErrorHandler(12);
		end
	end

The behavior of the add statement depends on the record type. For details on DL/I processing, see DLISegment record. For details on SQL processing, see SQL record.

DLISegment record

The add statement generates a DL/I ISRT statement. In DL/I, this insert takes place at the current position in the database—wherever that might be. You may control this positioning by, for example, explicitly setting keys in your EGL program (as shown in the example below), by using a set record position statement, by using a get statement, or by creating qualified SSAs through the #dli directive.

The following example adds an order to the customer database:
	//create instances of the records
	myCustomer CustomerRecord;
	myLocation LocationRecord;
	myOrder    OrderRecord;

	//build a segment search argument
	myCustomer.customerNo = "005001";
	myLocation.locationNo = "000022";

	//fill the fields in the order record
	fillOrder(myOrder);
	
	//add the new order record
	try
		add myOrder;
		onException
			myErrorHandler(2);
	end
This add statement will generate the following pseudo-DL/I code:
ISRT STSCCST (STQCCNO = :myCustomer.customerNo) 
     STSCLOC (STQCLNO = :myLocation.locationNo) 
     STPCORD
Qualified segment search arguments (SSAs) for customer and location identify the parent segments for the new order segment. DL/I will add the new order segment in a position determined by the keyItem field for that segment, the orderDateNo (STQCODN).
To capture "soft" I/O errors like "noRecordFound", use a set-values block when you create the program to set the program property throwNrfEofExceptions to yes. In the error handler function, test for the errors with the "is" or the "not" operator:
	if (myOrder is unique)
		...
	end
Possible runtime errors include the following:
  • unique if an order segment already exists with the specified key (no duplicates allowed)
  • duplicate if an order segment exists with the same key, but duplicates are allowed
DL/I also supports the use of path calls on add statements. This means that you can add parent segments for all segment levels between the lowest level segment you are adding and the root. In the following example, DL/I will add a new customer and location at the same time that it adds a new order:
add myCustomer, myLocation, myOrder;
EGL will generate the following pseudo-DL/I code from this statement:
ISRT STSCCST*D (STQCCNO = :myCust.customerNo) 
     STSCLOC (STQCLNO = :myLocation.locationNo) 
     STPCORD

Indexed record

When you add an indexed record, the key in the record determines the logical position of the record in the file. Adding a record to a file position that is already in use results in the hard I/O error UNIQUE or (if duplicates are allowed) in the soft I/O error DUPLICATE.

MQ record

When you add an MQ record, the record is placed at the end of the queue. This placement occurs because the add statement invokes one or more MQSeries® calls:

Relative record

When you add a relative record, the key item specifies the position of the record in the file. Adding a record to a file position that is already in use, however, results in the hard I/O error UNIQUE.

The record key item must be available to any function that uses the record and can be any of these:
  • An item in the same record
  • An item in a record that is global to the program or is local to the function that is running the add statement
  • A data item that is global to the program or is local to the function that is running the add statement

Serial record

When you add a serial record, the record is placed at the end of the file.

If the generated program adds a serial record and then issues a get next statement for the same file, the program closes and reopens the file before executing the get next statement. A get next statement that follows an add statement therefore reads the first record in the file. This behavior also occurs when the get next and add statements are in different programs, and one program calls another.

It is recommended that you avoid having the same file open in more than one program at the same time.

On CICS® for z/OS®, a single program cannot include a combination of add and get next statements for the same spool file. This restriction also applies when the add and get next statements are in different programs, and one program calls the other. Similarly, an add that follows a get or get next statement will add a record to the beginning of the file.

For IMS™ BMP or z/OS batch, if you add a variable-length serial record to a file associated with GSAM and the record length is longer than the physical file, DL/I returns a blank status code. Data is truncated, but no message is issued because the situation cannot be detected.

For IMS/VS, you must associate a serial record with an alternate PCB (a TP PCB in the PSB). The IMS message header (length, ZZ field, and transaction code) is automatically added to each record written to the message queue. An EGL add statement for a serial record assigned to a message queue results in an ISRT call to the message queue. If an error occurs and the record is assigned to a multiple segment message queue and associated with the express alternate PCB, any records already added are committed, even if an explicit close statement has not occurred. If it is important that these records are not committed, include an additional express alternate PCB in the PSB and associate the file with the additional express alternate PCB.

SQL record

Some error conditions are as follows:
  • You specify an SQL statement of a type other than INSERT
  • You specify some but not all clauses of an SQL INSERT statement
  • You specify an SQL INSERT statement (or accept an implicit SQL statement) that has any of these characteristics--
    • Is related to more than one SQL table
    • Includes only host variables that you declared as read only
    • Is associated with a column that either does not exist or is incompatible with the related host variable
The result is as follows when you add an SQL record without specifying an explicit SQL statement:
  • The format of the generated SQL INSERT statement looks like this:
      INSERT INTO tableName
        (column01, ... columnNN)
        values (:recordItem01, ... :recordItemNN)
  • The key value in the record determines the logical position of the data in the table. A record that does not have a key is handled in accordance with the SQL table definition and the rules of the database.
  • As a result of the association of record items and SQL table columns in the record part, the generated code places the data from each record item into the related SQL table column.
  • If you declared a record item to be read only, the generated SQL INSERT statement does not include that record item, and the database management system sets the value of the related SQL table column to the default value that was specified when the column was defined.
An example that uses a dynamic array of SQL records is as follows:
  try
    add employees;
    onException
      sysLib.rollback();
  end
Feedback
(C) Copyright IBM Corporation 2000, 2005. All Rights Reserved.