IBM SQL Database import not working

Question

I am trying to import my sql schema in IBM SQL Database (sqldb_free plan)

CREATE TABLE User (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(45) NOT NULL,
  password VARCHAR(120) NULL,
  salt VARCHAR(45) NULL,
  authData VARCHAR(45) NULL,
  CreditCardId VARCHAR(120) NULL,
  DisplayUsername VARCHAR(120) NOT NULL,
  email VARCHAR(120) NOT NULL,
  cashBalance FLOAT NULL DEFAULT 0,
  city VARCHAR(45) NULL,
  country VARCHAR(45) NULL,
  dob TIMESTAMP(4) NULL,
  firstName VARCHAR(45) NOT NULL,
  lastName VARCHAR(45) NULL,
  gender TINYINT(1) NULL DEFAULT 1,
  playerPoints INT NULL DEFAULT 0,
  state CHAR(2) NULL,
  tickets INT NULL,
  userImage VARCHAR(240) NULL,
  createdAt TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revision VARCHAR(20) NULL,
  cakeReqId VARCHAR(15) NULL,
  cakeAffId VARCHAR(15) NULL,
  signUpEvent TINYINT(1) NOT NULL DEFAULT 0,
  depositEvent TINYINT(1) NOT NULL DEFAULT 0,
  SignupSource VARCHAR(20) NULL,
  TotalSpent FLOAT NULL DEFAULT 0,
  address VARCHAR(120) NULL,
  phone VARCHAR(45) NULL,
  TicketTransferrable INT NULL,
  updatedAt TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FavouriteTeam INT NULL,
  UNIQUE INDEX email_UNIQUE (email ASC),
  PRIMARY KEY (id));

And the error message is

DDL failed with message 
_ Exception. _ state = 42601; error code = -104; error Message = Error for batch element #1: An unexpected token "," was found following "NULL AUTO_INCREMENT". Expected tokens may include: "".. _CODE=-104, _STATE=42601, DRIVER=3.66.46 

How can i fix this?

Answer 1

The DDL statement have the following issues:

1. DB2 does not support the "AUTO_INCREMENT" replace with "GENERATED ALWAYS AS IDENTITY".
2. DB2 does not support the TINYINT datatype replace with INT or CHARACTER(1) depends of your data.
3. CREATE UNIQUE INDEX separately, delete "UNIQUE INDEX email_UNIQUE (email ASC)," and add a DDL index.

Following as you could fix it:

CREATE TABLE User (
  id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  username VARCHAR(45) NOT NULL,
  password VARCHAR(120) NULL,
  salt VARCHAR(45) NULL,
  authData VARCHAR(45) NULL,
  CreditCardId VARCHAR(120) NULL,
  DisplayUsername VARCHAR(120) NOT NULL,
  email VARCHAR(120) NOT NULL,
  cashBalance FLOAT NULL DEFAULT 0,
  city VARCHAR(45) NULL,
  country VARCHAR(45) NULL,
  dob TIMESTAMP(4) NULL,
  firstName VARCHAR(45) NOT NULL,
  lastName VARCHAR(45) NULL,
  gender TINYINT(1) NULL DEFAULT 1,
  playerPoints INT NULL DEFAULT 0,
  state CHAR(2) NULL,
  tickets INT NULL,
  userImage VARCHAR(240) NULL,
  createdAt TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revision VARCHAR(20) NULL,
  cakeReqId VARCHAR(15) NULL,
  cakeAffId VARCHAR(15) NULL,
  signUpEvent TINYINT(1) NOT NULL DEFAULT 0,
  depositEvent TINYINT(1) NOT NULL DEFAULT 0,
  SignupSource VARCHAR(20) NULL,
  TotalSpent FLOAT NULL DEFAULT 0,
  address VARCHAR(120) NULL,
  phone VARCHAR(45) NULL,
  TicketTransferrable INT NULL,
  updatedAt TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FavouriteTeam INT NULL,
  PRIMARY KEY (id));

CREATE UNIQUE INDEX email_UNIQUE
   ON user
   (
      email ASC
   )
;