CODEWITHSUNDEEP
sqldb2db2-zos
user1993412
user1993412

Reputation: 862

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

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?

Upvotes: 0

Views: 3013

Answers (2)

meg
meg

Reputation: 1

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.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48840

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

Upvotes: 0

Related Questions