Overview

B97BUTLT reads data from a sequential dataset and inserts, updates, or deletes appropriate records in the Adabas Audit Data Retrieval database.

You can find a tailored JCL for this utility in the BETA97.CNTL in member B97BUTLT.

SYSIN

Use DD SYSIN to specify which data should be inserted, updated, or deleted. The record format of the DD SYSIN dataset must be fixed.

Downloading and uploading data

The corresponding download utility is B97DLOAD. For example, you can download definitions from an Adabas Audit Data Retrieval database by using B97DLOAD and then upload this data into another Adabas Audit Data Retrieval database using B97BUTLT. To do this, specify the output dataset created by B97DLOAD (DD IRMPRINT) as input dataset of B97BUTLT (DD SYSIN).

SMF records

Changes in the Adabas Audit Data Retrieval database are logged in SMF records of subtype 51 if the writing of these records has been activated.

Syntax for insert

A BUTLT statement that inserts a new record consists of the command INSERT TABLE tablename, which is followed by the name-value pairs for the fields of the record:

INSERT TABLE tablename
field_1 = "value"
field_2 = "value"
field_n = "value"

Each name-value pair (fieldname = "value") must be on a separate line. The INSERT keyword is optional and may be omitted.

TABLE tablename
field_1 = "value"
field_2 = "value"
field_n = "value"

Syntax for delete

A BUTLT statement that deletes one or more records consists of the command DELETE TABLE tablename, which is followed by a WHERE condition:

DELETE TABLE tablename
WHERE
field_a operator "value" [AND|OR field_b operator "value" ...]

The WHERE condition contains one expression (fieldname operator "value") or several AND/OR connected expressions, which control the selection of the records that are to be deleted.

Syntax for update

A BUTLT statement that updates one or more records consists of the command UPDATE TABLE tablename, which is followed by one or several name-value pairs and a WHERE condition:

UPDATE TABLE tablename
field_1 = "value"
field_2 = "value"
field_n = "value"
WHERE
field_a operator "value" [AND|OR field_b operator "value" ...]

Each name-value pair (fieldname = "value") must be on a separate line.

The WHERE condition contains one expression (fieldname operator "value") or several AND/OR connected expressions, which control the selection of the records that are to be updated.

General syntax rules

The following rules apply to all BUTLT statements (INSERT, UPDATE, and DELETE):

• A BUTLT statement can contain the following elements:
  • The INSERT, TABLE, DELETE, UPDATE or WHERE keywords
  • Name-value pairs (fieldname = "value")
  • One or more expressions (fieldname operator "value") for the WHERE condition
• Keywords, operators, table names and field names must be written in upper case.
• Keywords, operators, table names and field names must be separated by one or more blanks.
• The keywords INSERT, DELETE, UPDATE and WHERE must begin in column 1. If INSERT is omitted, then TABLE must begin in column 1.

The following applies to name-value pairs:

• Each name-value pair (fieldname = "value") must be on a separate line and must begin in column 2 or higher.
• The equal sign (=) is optional and may be omitted.
• Values must be enclosed in quotation marks ("value").
• If a value extends beyond the end of the line, place closing quotation marks ( " ) followed by a blank and a continuation sign ( ‑ ) at the end of the first line and place beginning quotation marks ( " ) in column 2 or higher of the next line.

The following applies to WHERE conditions:

• A WHERE condition consists of the WHERE keyword on a separate line, which is followed by one or more AND/OR connected expressions (fieldname operator "value").
• Expressions must begin in column 2 or higher.
• Values must be enclosed in quotation marks ("value").
• Field name, operator and value must be on the same line.
• Do not code a continuation sign.

Example

+------------------------------------------------------------------------+
|UPDATE TABLE LDR |
| LTITLE = "THIS IS THE " - |
| "NEW TITLE OF MY LIST" |
|WHERE |
| FORM EQ "REJ" AND |
| EXT EQ "TRADE" |
+------------------------------------------------------------------------+

Important

The values of BYTE and FLAG fields must be specified using the external format. The corresponding language-dependent values must be specified in English.

Use the database dictionary (Option D.2) to find out about legal values.

Operators for the WHERE condition

Operator	Function
EQ or = or ==	equal
LIKE	like (interprets % and * as wildcards)
NE or ^=	not equal to
UNLIKE	unlike (interprets % and * as wildcards)
GT or >	greater than
GE or >=	greater than or equal to
LT or <	less than
LE or <=	less than or equal to
AND	Boolean AND
OR	Boolean OR
IN	May be used as abbreviated form for OR connection if the field name is identical This statement: field IN (value1,value2) is equivalent to: field EQ value1 OR field EQ value2

Note on SASOVLN and IDXOVLN

Following are the corresponding values for the Line No. field, which is used in index definitions (IDXOVLN) and search argument definitions (SASOVLN):

This value ...	Corresponds to ...
0	MERGE
n	line number n
32768	ANY
65535	LAST

CONTROL statement

You can use CONTROL statements in DD SYSIN to control the program behavior of B97BUTLT, for example:

• Logging of BUTLT statements in DD IRMLOG
• Processing of the CDATE, CTIME and CUSER fields when inserting new records
• Program behavior if the insertion of a new record fails because of RC=142 (Duplicate key)

Normally, the CONTROL statement is placed at the beginning of DD SYSIN.

Syntax of the CONTROL statement

CONTROL statements must use the following syntax:

• CONTROL statements are introduced by CONTROL START and ended by CONTROL END. The CONTROL keyword must begin in column 1. The CONTROL keyword and START or END must be separated by one blank.
• The keyword of the CONTROL statement (PROTOCOL, CHG_INFO or PROCESS) must begin in column 2. The equal sign (=) must be in column 11. The value must begin in column 13.

  *V--+----1=-V-+----2----+
  keyword = value

• One or more CONTROL statements can be coded between CONTROL START and CONTROL END.

PROTOCOL = ALL

By default, the batch utility B97BUTLT logs in DD IRMLOG all statements that lead to an error and the accompanying error messages.

Use the CONTROL statement PROTOCOL = ALL to log all statements in DD IRMLOG, including those ending with RC=0.

*V--+----1=-V-+----2----+
CONTROL START
PROTOCOL = ALL
CONTROL END

CHG_INFO = OLD

The fields CDATE, CTIME, and CUSER contain the date, time, and user ID of the last modification of the record. By default, these fields are updated by B97BUTLT when inserting a new record. This means that the CDATE, DTIME, and CUSER fields of an uploaded record refer to the modification of the record by B97BUTLT.

When uploading records (INSERT) use the CONTROL statement CHG_INFO = OLD, if the values uploaded to these fields are to be preserved.

*V--+----1=-V-+----2----+
CONTROL START
CHG_INFO = OLD
CONTROL END

DATEMASK = mask

DATEMASK = mask specifies the date format that is used in the BUTLT statements. Legal values are the date formats that are supported by BSA (see online option P.2).

Specifying DATEMASK is required only in the following cases:

• BUTLT statements use YYYY-MM-DD (International), but the system options (S.2) specify a different date mask.
• BUTLT statements use DD/MM/YYYY (British), but the system options (S.2) specify a different date mask.

Specifying DATEMASK is not required when:

• The date mask of the BUTLT statements and of the system options (S.2) is identical
• The BUTLT statements use one of the following date formats:
  • MM/DD/YYYY or MM/DD/YY (American)
  • DD.MM.YYYY or DD.MM.YY (Continental)
  • YYYY.DDD or YY.DDD (Julian)

*V--+----1=-V-+----2----+
CONTROL START
DATEMASK = YYYY-MM-DD
CONTROL END

PROCESS = AUTOUPD

Coding the CONTROL statement PROCESS = AUTOUPD causes the following program behavior during INSERT: If inserting a new record fails because a record is already present under this key (BQL infocode 142 - Duplicate key), the program will automatically branch into UPDATE mode and update the corresponding record.

*V--+----1=-V-+----2----+
CONTROL START
PROCESS = AUTOUPD
CONTROL END

Autoupdate requirements and limitations:

• A value must be explicitly coded for each key field.
• Key fields cannot be updated.

RC_KDUPL = level

By default, the occurrence of BQL infocode 142 leads to B97BUTLT ending with RC=20. (BQL infocode 142 (Duplicate key) means: Insertion has failed because a record with the same key already exists.) You can use RC_KDUPL to define a different return code for this error case. Legal values are:

RC_KDUPL = OKAY          (corresponds to RC=0)
RC_KDUPL = WARNING     (corresponds to RC=4)
RC_KDUPL = ERROR          (corresponds to RC=8)

Notes:

RC_KDUPL = level cannot be used in combination with PROCESS = AUTOUPD. If both are coded, RC_KDUPL = level will be ignored.

RC_KDUPL takes effect only if BQL infocode 142 is the only error that occurred during the insertion of records. If other errors have occurred in addition to or instead of BQL infocode 142, RC_KDUPL = level has no effect, and the program will end with RC=20.

*V--+----1=-V-+----2----+
CONTROL START
RC_KDUPL = level
CONTROL END

Return codes

0	The program terminated normally.
4	The program did not find any data to be inserted, updated, or deleted; the program terminated normally.
20	One of the following errors occurred: Missing DD statement Error while opening the database or a dataset Error during command execution Syntax error in DD SYSIN Logical error in DD SYSIN (e.g. referenced definition not found) Check DD IRMLOG to find out what caused the error.
24	The subsystem is not available.
32	One of the following errors occurred: Invalid security level (ADM required) The batch utility was started using SIGNON=NO, but the master subsystem is active

IRMLOG

DD IRMLOG contains a summary activity log.

DD IRMLOG also logs all definitions that lead to an error. You can edit and then reuse this log as input for B97BUTLT. The program will ignore all messages in the log.

+---------------------------------------------------------------------------------------------------------+
|IRM1561I PROGRAM: B97BUTLT VERSION: V7R2M00 PTFLVL: ptflvl COMPILED: date,time |
|IRM4000I B97BUTLT UPLOAD UTILITY STARTED - DATE: date, TIME: time |
|IRM4100I ************************************************************************************* |
|IRM4008I ADMINISTRATOR SECURITY LEVEL SET FOR USER B97ADM |
|IRM4100I ------------------------------------------------------------------------------------- |
|UPDATE TABLE SYS |
| SYSTITLE = "THIS IS THE NEW TITLE OF MY DEFINITION " - |
| "BUT IF TOO LONG IT WILL LEAD TO A VALUE CONVERSION ERROR" |
|WHERE |
| SSID EQ "B97P" |
|IRM3566E - 6 VALUE CONVERSION ERROR - VALUE OF FIELD SYSTITLE COULD NOT BE CONVERTED TO INTERNAL FORMAT|
|IRM4003E COMMAND ERROR RC( 20) |
|IRM4100I ------------------------------------------------------------------------------------- |
|IRM4998I 1 SYS - COMMANDS PROCESSED (IN ERROR: 1) |
|IRM4998I 1 total - COMMANDS PROCESSED (IN ERROR: 1) |
|IRM4100I ************************************************************************************* |
|IRM4999I B97BUTLT UPLOAD UTILITY ENDED - DATE: date, TIME: time, RC: 0020 |
+---------------------------------------------------------------------------------------------------------+

Authorization

Following the standard security check for the BUE function, the ADM authorization (profile B97.ssid.ADM) of the submitting user is checked. If the user has the access authorization ACCESS=READ, the specified INSERT, UPDATE and DELETE commands are carried out without individual security check. If the user does not have this authorization, the authorization is checked before each individual command.

Modifying the table IGR requires that the submitter has ADM authorization and that the started task is stopped (SIGNON=NO).