0

When I run the ingest utility with the delete statement it gives the number of rows inserted as 0 and doesn't show the number of rows deleted. Is there any option to show the number of rows deleted?

I have included the output message of the ingest utility and the code

output
------
Number of rows read     = 255
Number of rows inserted = 0
Number of rows rejected = 0

code
----
db2 "ingest from file mypipe format delimited(
$field1 CHAR(9),
$field2 DATE 'yyyy-mm-dd'
) 
Delete from mytable where dob = $field2"
vineeth
  • 641
  • 4
  • 11
  • 25
  • Show the command you are running. INGEST should print the number of deleted rows. https://www.ibm.com/docs/en/db2/11.5?topic=commands-ingest – data_henrik Aug 12 '21 at 12:53

1 Answers1

0

The documentation specifies initially that the summary report only contains the number of rows read, inserted , rejected. This is perhaps what you are seeing.

Quote from documentation:

Messages from the INGEST command If the utility read at least one record from the input source, the utility issues a summary of the number of rows read, inserted, and rejected (similar to the import and load utilities) and a successful completion message.

However, on the same page a later statement is:

Number of rows inserted (updated, deleted, merged) The number of rows affected by the execution of the SQL statement against the target table and committed to the database. The message says "inserted", "updated", "deleted", or "merged", depending on the SQL statement.

So the behaviour for your case seems unhelpful, and IBM could make it better by additionally including the count of rows deleted, and count of rows updated when the sole SQL statement is DELETE. I tested this behaviour with Db2-LUW v11.5.6.0".

Even when the delete statement is replaced by a MERGE with WHEN MATCHED THEN DELETE the summary report excludes the count of deleted rows. Undesirable behaviour.

If you have a support contract, you could open a ticket with IBM asking for a workaround or fix as there may be some regression here.

mao
  • 11,321
  • 2
  • 13
  • 29