How to write a DB2 loop with INSERT

26 August,2016 by Jack Vamvas

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

 

 

 

 

 

 

 

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to write a DB2 loop with INSERT

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright