WRITE statements

Syntax

WRITE[U] expression {ON | TO} [file.variable,] record.ID[ON ERROR statements] [LOCKED statements] 
[THEN statements] [ELSE statements]
WRITEV[U] expression {ON | TO} [file.variable,] record.ID, field#[ON ERROR statements] [LOCKED statements] 
[THEN statements] [ELSE statements]

Description

Use WRITE statements to write new data to a record in an InfoSphere® DataStage® file. The value of expression replaces any data previously stored in the record.

Table 1. Effects of WRITE Statements
Use this statement... To do this...
WRITE Write to a record.
WRITEU Write to a record, retaining an update record lock.
WRITEV Write to a field.
WRITEVU Write to a field, retaining an update record lock.

If expression evaluates to the null value, the WRITE statement fails and the program terminates with a run-time error message.

file.variable specifies an open file. If file.variable is not specified, the default file is assumed (for more information on default files, see the OPEN statement. If the file is neither accessible nor open, the program terminates with a run-time error message, unless ELSE statements are specified.

The system searches the file for the record specified by record.ID. If the record is not found, WRITE creates a new record.

If file.variable, record.ID, or field# evaluates to the null value, all WRITE statements (WRITE, WRITEU, WRITEV, WRITEVU) fail and the program terminates with a run-time error message.

The new value is written to the record, and the THEN statements are executed. If no THEN statements are specified, execution continues with the statement following the WRITE statement. If WRITE fails, the ELSE statements are executed; any THEN statements are ignored.

When updating a record, the WRITE statement releases the update record lock set with a statement. To maintain the update record lock set by the READU statement, use a WRITEU statement instead of a WRITE statement.

The WRITE statement does not strip trailing field marks enclosing empty strings from expression. Use the MATWRITE statements statement if that operation is required.

Tables

If the file is a table, the effective user of the program must have SQL INSERT and UPDATE privileges to read records in the file. For information about the effective user of a program, see the AUTHORIZATION statement.

If the OPENCHK configurable parameter is set to TRUE, or if the file is opened with the OPENCHECK statement, all SQL integrity constraints are checked for every write to an SQL table. If an integrity check fails, the WRITE statement uses the ELSE clause. Use the ICHECK function to determine what specific integrity constraint caused the failure.

NLS Mode

WRITE and other BASIC statements that perform I/O operations map internal data to the external character set using the appropriate map for the output file.

InfoSphere DataStage substitutes the file map's unknown character for any unmappable character. The results of the WRITE statements depend on the following:

  • The inclusion of the ON ERROR clause
  • The setting of the NLSWRITEELSE parameter in the uvconfig file
  • The location of the unmappable character

The values returned by the STATUS function and the results are as follows:

Table 2. Values and results
STATUS Value and Results ON ERROR and Parameter Setting Unmappable Character Location
3 The WRITE fails, no records written.

4 The WRITE fails, no records written.

 ON ERROR Record ID

Data
Program terminates with a run-time error message. No ON ERROR, and

NLSWRITEELSE = 1

 Record ID or data
Program terminates with a run-time error message.

Record is written with unknown characters; lost data.

 No ON ERROR, NLSWRITEELSE = 0 Record ID

Data

Use the STATUS function after a WRITE statement is executed, to determine the result of the operation, as follows:

0
The record was locked before the WRITE operation.
-2
The record was unlocked before the WRITE operation.
-3
The record failed an SQL integrity check.
-4
The record failed a trigger program.
-6
Failed to write to a published file while the subsystem was shut down.
-4
The record was rejected by an SQL trigger.

The ON ERROR Clause

The ON ERROR clause is optional in WRITE statements. Its syntax is the same as that of the ELSE clause. The ON ERROR clause lets you specify an alternative for program termination when a fatal error is encountered during processing of the WRITE statement.

If a fatal error occurs, and the ON ERROR clause was not specified or was ignored (as in the case of an active transaction), the following occurs:

  • An error message appears.
  • Any uncommitted transactions begun within the current execution environment roll back.
  • The current program terminates.
  • Processing continues with the next statement of the previous execution environment, or the program returns to the command prompt.

A fatal error can occur if any of the following occur:

  • A file is not open.
  • file.variable is the null value.
  • A distributed file contains a part file that cannot be accessed.

If the ON ERROR clause is used, the value returned by the STATUS function is the error number.

The LOCKED Clause

The LOCKED clause is optional, but recommended. Its format is the same as that of the ELSE clause.

The LOCKED clause handles a condition caused by a conflicting lock (set by another user) that prevents the WRITE statement from processing. The LOCKED clause is executed if one of the following conflicting locks exists:

  • Exclusive file lock
  • Intent file lock
  • Shared file lock
  • Update record lock
  • Shared record lock

If the WRITE statement does not include a LOCKED clause, and a conflicting lock exists, the program pauses until the lock is released.

If a LOCKED clause is used, the value returned by the STATUS function is the terminal number of the user who owns the conflicting lock.

The WRITEU Statement

Use the WRITEU statement to update a record without releasing the update record lock set by a previous READU statement (see the READ statements). To release the update record lock set by a READU statement and maintained by a WRITEU statement, you must use a RELEASE statement, WRITE statements, MATWRITE statements, or WRITEV statement. If you do not explicitly release the lock, the record remains locked until the program executes the STOP statement. When more than one program or user could modify the same record, use a READU statement to lock the record before doing the WRITE or WRITEU.

If expression evaluates to the null value, the WRITEU statement fails and the program terminates with a run-time error message.

The WRITEV Statement

Use the WRITEV statement to write a new value to a specified field in a record. The WRITEV statement requires that field# be specified. field# is the number of the field to which expression is written. It must be greater than 0. If either the record or the field does not exist, WRITEV creates them.

If expression evaluates to the null value, null is written to field#, provided that the field allows nulls. If the file is an SQL table, existing SQL security and integrity constraints must allow the write.

The WRITEVU Statement

Use the WRITEVU statement to update a specified field in a record without releasing the update record lock set by a previous READU statement (see the READ statement). The WRITEVU syntax is like that of the WRITEV and WRITEU statements.

If expression evaluates to the null value, null is written to field#, provided that the field allows nulls. If the file is an SQL table, existing SQL security and integrity constraints must allow the write.

Remote Files

If in a transaction you try to write to a remote file over and the write statement fails, the transaction is rolled back, and the program terminates with a run-time error message.

Example

CLEAR
DATA "ELLEN","KRANZER","3 AMES STREET","CAMBRIDGE"
DATA "MA","02139","SAILING"
OPEN '','SUN.MEMBER' TO FILE ELSE
   PRINT "COULD NOT OPEN FILE"
   STOP
END
PRINT "ENTER YOUR FIRST NAME"
INPUT FNAME
PRINT "ENTER YOUR LAST NAME"
INPUT LNAME
PRINT "ENTER YOUR ADDRESS (PLEASE WAIT FOR PROMPTS)"
PRINT "STREET ADDRESS"
INPUT STREET
PRINT "ENTER CITY"
INPUT CITY
PRINT "ENTER STATE"
INPUT STATE
PRINT "ENTER ZIP CODE"
INPUT ZIP
PRINT "ENTER YOUR INTERESTS"
INPUT INTERESTS
RECORD<1>=LNAME
RECORD<2>=FNAME
RECORD<3>=STREET
RECORD<4>=CITY
RECORD<5>=STATE
RECORD<6>=ZIP
RECORD<7>=1989
RECORD<8>=INTERESTS
WRITE RECORD TO FILE, 1111
PRINT
EXECUTE 'LIST SUN.MEMBER LNAME WITH FNAME EQ ELLEN'

This is the program output:

ENTER YOUR FIRST NAME
?ELLENENTER YOUR LAST NAME
?KRANZERENTER YOUR ADDRESS (PLEASE WAIT FOR PROMPTS)
STREET ADDRESS
?3 AMES STREETENTER CITY
?CAMBRIDGEENTER STATE
?MAENTER ZIP CODE
?02139ENTER YOUR INTEREST
?SAILING
SUN.MEMBER  LAST NAME.
1111 KRANZER

1 records listed.
Parent topic: BASIC Statements and Functions