BATSQL (Batch SQL) batch command

Hints and tips

Rowset processing is automatically selected when an AUTOCOMMIT value of 2 - 10000 is specified.

For DELETE, INSERT and UPDATE processing, FM/Db2 fetches the rowset number of rows in a single fetch. For DELETE and UPDATE processing only, FM/Db2 attempts to delete or update rowset number of rows by using a single SQL statement.

For insert, each row is always inserted by using a single-row INSERT statement.

A large region size can be required when using rowset processing and a large rowset size. FM/Db2 needs sufficient storage to store the data for the specified rowset number of rows.

For delete and update processing that uses rowsets, it is possible that the delete or update operation for the rowset fails. When this failure occurs FM/Db2 tries the delete or update operation again, for every row of the rowset, using single-row processing. The error that is encountered for the rowset delete or update does not increment the error counter, however any error against a single-row delete or update does increment the error counter.

Examples

The examples refer to these table definitions:

CREATE TABLE RFM0681A."Names"
  ("Name_Identifier" INTEGER NOT NULL
  ,"Last_Name"       VARCHAR(16) NOT NULL
  ,"First_Name"      VARCHAR(16) NOT NULL
  ,"Second_Name"     VARCHAR(16) NOT NULL
  ,"Sex"             CHAR(1)     NOT NULL
  ,"Date_Of_Birth"   DATE        NOT NULL
  );

CREATE TABLE RFM0681B."Names"
  ("Name_Identifier" INTEGER NOT NULL
  ,"Date_Of_Birth"   DATE
  ,"Sex"             CHAR(1)
  ,"First_Name"      VARCHAR(16)
  ,"Second_Name"     VARCHAR(16)
  ,"Last_Name"       VARCHAR(16)
  );

Example 1

Delete all rows from the table that do not have a valid sex ('F' or 'M'). Issue a Db2® commit after every 50 successful delete operations. Stop if there are any errors.

//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DFXX,SQID=XXXXXXX')
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//DDINWHR  DD *
WHERE ("Sex" <> 'M'
  AND  "Sex" <> 'F')
/*
//SYSIN DD *
$$FILEM BATSQL TYPE=DELETE,
$$FILEM OBJIN=RFM0681A."Names",
$$FILEM AUTOCOMMIT=50,
$$FILEM MAXERRORS=1

Example 2

Update all rows from the table that have:

• A surname of 'Smith'
• A sex of 'm'

Change these rows to have:

• A surname of 'SMITH'
• A given name of 'JAMES'
• No second name
• A sex of 'M'

Issue a Db2® commit after every 100 successful update operations. Stop if there are more than 10 errors.

//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DFXX,SQID=XXXXXXX')
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//DDINSET  DD *
SET ("Last_Name", "First_Name", "Second_Name", "Sex") =
    ('SMITH',     'JAMES',      '',            'M')
/*
//DDINWHR  DD *
WHERE "Last_Name" = 'Smith'
  AND "Sex" = 'm'
/*
//SYSIN DD *
$$FILEM BATSQL TYPE=UPDATE,
$$FILEM OBJIN=RFM0681A."Names",
$$FILEM AUTOCOMMIT=100,
$$FILEM MAXERRORS=10
/*

Example 3

Insert rows into RFM0681B."Names", using a select against RFM0681A."Names" to produce a result table.

Only insert rows when the "Name_Identifier" value is less than or equal to 100,000.

//FMNDB2   EXEC PGM=FMNDB2,PARM=('SSID=DFXX,SQID=XXXXXXX')
//SYSPRINT DD SYSOUT=*
//FMNTSPRT DD SYSOUT=*
//DDININS  DD *
(
 "First_Name",
 "Second_Name",
 "Last_Name",
 "Sex",
 "Date_Of_Birth",
 "Name_Identifier"
)
/*
//DDINSEL DD *
SELECT
 "First_Name",
 "Second_Name",
 "Last_Name",
 "Sex",
 "Date_Of_Birth",
 "Name_Identifier"
FROM RFM0681A."Names"
WHERE "Name_Identifier" <= 100000
/*
//SYSIN DD *
$$FILEM BATSQL TYPE=INSERT,
$$FILEM OBJOUT=RFM0681B."Names",
$$FILEM AUTOCOMMIT=50,
$$FILEM MAXERRORS=100
/*