3

Hi I try to insert data from a script into a DB2 table which has an identity column. I read that using the syntax:

INSERT INTO SOME_TABLE OVERRIDING SYSTEM VALUE ...

would work, but it doesn't. Here is the exact query:

INSERT INTO SCHEMA.MYTABLE OVERRIDING SYSTEM VALUE (
     IDENTITY_COLUMN
    ,SOMEVALUE
    ,CREATEDTIME
    ,UPDATEDTIME
) VALUES (
     2
    ,'656566'
    ,'2012-07-12 16:25:34'
    ,NULL
);

I get the following error:

SQL0104N  An unexpected token "VALUE" was found following "DE OVERRIDING 
SYSTEM".  Expected tokens may include:  "<space>".  SQLSTATE=42601

Thank you!

mao
  • 11,321
  • 2
  • 13
  • 29
user2343647
  • 633
  • 6
  • 17
  • 1
    The answer depends on the exact table definition and the DB2 version and platform, which you chose not to share with us. `OVERRIDING SYSTEM VALUE` clause seems to be valid only in DB2 for i. – mustaccio Oct 17 '13 at 18:09
  • However, IBM i uses 7 byte error codes (typically formatted AAAxxxx, 3 alpha & 4 digits (now hex)) so an 8 character error code comes from somewhere else. – WarrenT Oct 18 '13 at 02:34
  • @WarrenT This a perfectly fine Error code that you get when running the db2 command line code. The equivalent sql error code is -104. – Peter Schuetze Oct 21 '13 at 18:31
  • @Peter Schuetze : Can you describe what you mean by "the db2 command line code" when discussing DB2 for i? I'm not aware of any "db2 command line" on i. – user2338816 Mar 29 '14 at 07:47
  • @user2338816 That is a perfectly fine error code in db2 LUW – Peter Schuetze Apr 09 '14 at 12:55
  • @mustaccio: It's a standard SQL feature, see e.g. `ISO/IEC 9075-2:2016(E) 14.11 `. Db2 being quite standards compliant, I don't think it matters which version and platform this is about. – Lukas Eder Dec 03 '21 at 09:28

1 Answers1

0

In case you're using Db2 for i, the clause is located after the column list, syntactically, see the documentation as well as the SQL standard specification ISO/IEC 9075-2:2016(E) 14.11 <insert statement>. So, write:

INSERT INTO SCHEMA.MYTABLE (
     IDENTITY_COLUMN
    ,SOMEVALUE
    ,CREATEDTIME
    ,UPDATEDTIME
) 
OVERRIDING SYSTEM VALUE -- Put it here
VALUES (
     2
    ,'656566'
    ,'2012-07-12 16:25:34'
    ,NULL
);
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • This syntax `OVERRIDING SYSTEM VALUE` is available only on the Db2-for-i (as/400) legacy platform. It is not available on either Db2-LUW or Db2-Z/OS at currently supported versions in 2021. – mao Dec 03 '21 at 09:50
  • @mao: Yes, sure, but given that the mistake was a simple syntactic one, I suspect that it doesn't really matter in this case? As I mentioned, it's also the syntax as defined in the SQL standard, and at least PostgreSQL, H2 also support it. – Lukas Eder Dec 03 '21 at 09:58