DB2 NOT ATOMIC WAS Expected , how can we fix this?

Question

I m not sure , why I am getting the below error, while executing the below SQL in my SQL Editor for DB2 (DB2 for z/OS)

DECLARE GLOBAL TEMPORARY TABLE SESSION.VTI_GUI_TMP ( 
  ENCRP_PRC_RL_ID INTEGER NOT NULL,  
  PROC_SYS_CD VARCHAR(10) NOT NULL,  
  VER_KEY_SET_CD VARCHAR(10) NOT NULL,  
  TRNSLT_TP_CD VARCHAR(10) NOT NULL,  
  SET_INDEX_NUM VARCHAR(10) NOT NULL,  
  SET_MDK_DKI_NUM VARCHAR(10) NOT NULL,  
  PACKET_NUM INTEGER NOT NULL ) ON COMMIT PRESERVE ROWS; 

INSERT INTO SESSION.VTI_GUI_TMP ( 
  ENCRP_PRC_RL_ID, PROC_SYS_CD, VER_KEY_SET_CD, TRNSLT_TP_CD, 
  SET_INDEX_NUM, SET_MDK_DKI_NUM, PACKET_NUM
) 
VALUES (500159, 'DB', 'MDK', '0', '1', '001', 766473) 
UNION ALL 
(500151, 'DB', 'MDK', '0', '2', '002', 766473);

select * from SESSION.VTI_GUI_TMP; 

DROP TABLE SESSION.VTI_GUI_TMP;

I am getting the below error , while executing the above the SQL

ILLEGAL USE OF KEYWORD UNION. TOKEN FOR NOT ATOMIC WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=3.63.75 SQL Code: -199, SQL State: 42601

Error occurred in: INSERT INTO SESSION.VTI_GUI_TMP (ENCRP_PRC_RL_ID, PROC_SYS_CD, VER_KEY_SET_CD, TRNSLT_TP_CD, SET_INDEX_NUM, SET_MDK_DKI_NUM, PACKET_NUM) VALUES(500159, 'DB', 'MDK', '0', '1', '001', 766473) UNION ALL(500151, 'DB', 'MDK', '0', '2', '002', 766473)

How can we fix the above error ? I dont want to use the SELECT statement along with UNION ALL to fix. Is there any way to make this work.

I am using the below DB2 version

  SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1
  -----------
  DSN11015

What am I doing wrong?

The error message simply means the server cannot understand what you're asking it to do. Your `insert` statement is syntactically invalid; you are mixing up `insert ... values...` and `insert ... select ...` variants. Choose one and stick to it. — mustaccio, May 29 '19 at 01:58

The Impaler · Answer 1 · 2019-05-28T23:22:55.417

Can't you use two separate INSERT statements? For example:

DECLARE GLOBAL TEMPORARY TABLE SESSION.VTI_GUI_TMP (
  ENCRP_PRC_RL_ID INTEGER NOT NULL, 
  PROC_SYS_CD VARCHAR(10) NOT NULL, 
  VER_KEY_SET_CD VARCHAR(10) NOT NULL, 
  TRNSLT_TP_CD VARCHAR(10) NOT NULL, 
  SET_INDEX_NUM VARCHAR(10) NOT NULL, 
  SET_MDK_DKI_NUM VARCHAR(10) NOT NULL, 
  PACKET_NUM INTEGER NOT NULL
) ON COMMIT PRESERVE ROWS; 

INSERT INTO SESSION.VTI_GUI_TMP (
  ENCRP_PRC_RL_ID, PROC_SYS_CD, VER_KEY_SET_CD, TRNSLT_TP_CD, 
  SET_INDEX_NUM, SET_MDK_DKI_NUM, PACKET_NUM
) VALUES (500159, 'DB', 'MDK', '0', '1', '001', 766473) 

INSERT INTO SESSION.VTI_GUI_TMP (
  ENCRP_PRC_RL_ID, PROC_SYS_CD, VER_KEY_SET_CD, TRNSLT_TP_CD, 
  SET_INDEX_NUM, SET_MDK_DKI_NUM, PACKET_NUM
) VALUES (500151, 'DB', 'MDK', '0', '2', '002', 766473);

Or maybe:

INSERT INTO SESSION.VTI_GUI_TMP (
  ENCRP_PRC_RL_ID, PROC_SYS_CD, VER_KEY_SET_CD, TRNSLT_TP_CD, 
  SET_INDEX_NUM, SET_MDK_DKI_NUM, PACKET_NUM)
SELECT 500159, 'DB', 'MDK', '0', '1', '001', 766473 FROM sysibm.sysdummy1
UNION ALL
SELECT 500151, 'DB', 'MDK', '0', '2', '002', 766473 FROM sysibm.sysdummy1

Yes that would work , but I am still not sure , why i am seeing NOT ATOMIC error, and is there a way we could insert in a single statement. — user1993412, May 28 '19 at 19:12
@user1993412 Well... the second option inserts using a single SQL statement. — The Impaler, May 28 '19 at 20:18

score 0 · Answer 2 · answered Dec 12 '19 at 20:40

Since the syntax was not accurate, Db2 attempted to provide guidance on what might be expected. In the example SQL, Db2 thought you might be wanting to use a multiple-row-insert where NOT ATOMIC is expected. For the INSERT statement, the VALUES clause supports an expression, DEFAULT, and NULL (https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_insert.html). An INSERT with VALUES using UNION ALL is currently not supported. The two examples provided by @The Impaler appear to be valid syntax: 1) INSERT with VALUES using valid expressions and 2) INSERT with fullselect.

DB2 NOT ATOMIC WAS Expected , how can we fix this?

2 Answers2