Problem trying to execute a while loop with insert in db2

Question

I am trying to insert entries into a table:

CREATE TABLE MYTABLE (ID INT);

BEGIN
DECLARE CNT INT DEFAULT 0;
WHILE CNT<100 DO
INSERT INTO MYTABLE(ID) VALUES(CNT);
SET CNT = CNT + 1;
END WHILE;
END

What's wrong with the loop? It won't run. The error it shows is:

Error : an unexpected token "End of starement" was found following "re cnt int default 0". Expected tokens may include "psm_semicolon>" sqlcode=-104 sqlstate=42601, driver=3.67.27 Error occured in: BEGIN DECLARE CNT INT DEFAULT 0

mao mao · Accepted Answer · 2019-12-17T16:51:53

This is a frequently asked question, you could have found it with search.

You need to tell whatever tool you use to submit the SQL statement that there is an alternative statement terminator (also known as statement separator), and then use that alternative statement terminator at the end of the SQL block , and any standalone non-compound statements.

Many people use @ to indicate the end of the block, different tools often suggest different characters.

For squirrel sql, some versions have a "Statement Separator" property on the SQL tab of 'New Session Properties' which you can set to @, and this is effective the next time you open the session. Your code would then have @ at the end of the create table statement, plus @ once after the end of the block (in the case that you have two statements, one of which is a compound block). If you have more than two statements each compound-block needs its own @ terminator at end of block, and each standalone statement (non compound) needs its own @.

The background is that when you have a compound (multi-statement) SQL block in Db2, the semi-colon is the terminator for statements inside the block, but you need a different terminator to indicate the end of the whole block. Without that terminator, you will get an SQL exception.

Different tools have different ways to specify the alternative statement terminator.

Your question does not mention the tool you use to submit the SQL, or your Db2-server platform, which can be relevant sometimes.

If you submit the SQL using the Db2 command-line-processor (at the shell command line on Windows, Linux/Unix), then you can specify the alternative statement terminator either on the db2 command line ( -td@) , or on the fly inside the file (as long as that file is submitted by the db2 CLP) that contains the SQL statements by having a line that reads --#SET TERMINATOR @ . You can use this as often as you like inside a file sent to the CLP, each with a different terminator if necessary and if impacts only the subsequent statements after that line until the next terminator is specified. This method is not usually applicable to java based tools such as squirrel sql, so use the statement separator property for that.

Problem trying to execute a while loop with insert in db2

1 Answers