How to write a DB2 loop with INSERT

Question: I’d like write a sql statement to loop through an INSERT statement and increment with a  count. The purpose is to create some test tables for load testing.

Answer: It is possible to create a loop in DB2 which loops through an incremental INSERT. This is a basic example, which can be customised for your purposes.Note the use of ATOMIC. The purpose of ATOMIC is to rollback before the call is passed back to the requestor, if there is a problem.

In this example the CNT variable increments at every INSERT, up until it is under 100000.

db2 “CREATE TABLE mytble (ID INT)”
db2 "BEGIN ATOMIC DECLARE CNT INT DEFAULT 5; WHILE CNT < 100000 DO INSERT INTO mytbl (ID) VALUES('16'); SET CNT = CNT + 1; END WHILE;END"

 Read More

Software unit testing and DB2 sql loop test code (DBA DB2)

DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis