create table in Db2 for iSeries

Question

I have a table structure like this in sql server:

    CREATE TABLE [dbo].[taname](
[ID] [char](7) NOT NULL,
[SOURCE] [char](14) NOT NULL,
[TARGET] [char](14) NOT NULL,
[ID1] [char](100) NULL,
)

this similar table I'm trying to create in DB2:

      CREATE TABLE schema.taname(
ID char(7) NOT NULL,
SOURCE char(14) NOT NULL,
TARGET char(14) NOT NULL,
ID1 char(100) NULL --error is here
);

However, I'm getting error in "ID":

Keyword NULL not expected. Valid tokens: AS NO FOR NOT FILE WITH CCSID CHECK LOGGED UNIQUE COMPACT. Cause . . . . . : The keyword NULL was not expected here. A syntax error was detected at keyword NULL. The partial list of valid tokens is AS NO FOR NOT FILE WITH CCSID CHECK LOGGED UNIQUE COMPACT. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

Processing ended because the highlighted statement did not complete successfully

I would like to create table similar to SQL Server and allow NULL in the ID field. How can I correct this?

Charles Charles · Accepted Answer · 2018-01-30T19:13:50

NULL is the default... you can just leave it off...

CREATE TABLE schema.taname(
  ID char(7) NOT NULL,
  SOURCE char(14) NOT NULL,
  TARGET char(14) NOT NULL,
  ID1 char(100)
);

alternatively, specify the DEFAULT clause...

CREATE TABLE schema.taname(
  ID char(7) NOT NULL,
  SOURCE char(14) NOT NULL,
  TARGET char(14) NOT NULL,
  ID1 char(100) DEFAULT NULL
);

create table in Db2 for iSeries

1 Answers