Unexpected token "BIGINT" while creating database (DB2 LUW)

April 9, 2024

This is a simple sql command that I want to run:

CREATE TABLE USERMNGR.AUTHORIZED_CLIENT (
    ID BIGINT  (8) DEFAULT J NOT NULL,
    AUTHORIZED_CLIENT_STATUS CHAR    (10) DEFAULT 'Y',
    CLIENT_NAME VARCHAR (40) DEFAULT 'N' NOT NULL,
    NATIONAL_CODE CHAR    (16) DEFAULT 'N' NOT NULL,
    PRIMARY KEY (ID)
);

It has some whitespaces but I don’t think they pose any problems. Running the script using IBM Data Studio, DBeaver and the DB2 CLI will give this error:

SQL Error [42601]: An unexpected token "BIGINT" was found following "ORIZED_CLIENT ( 
 ID".  Expected tokens may include:  "BINARY".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.33.31

I can’t find where’s the problem. I’ve created the database USERMNGR manually before running the script, and know that BIGINT is supported by DB2. Every unexpected token error that I’ve found on the internet had some issue that this command does not have. It’s a simple CREATE TABLE. I even removed preceding USERMNGR. and it didn’t change anything. I changed the BIGINT data type into INTERGER and it threw another error at me:

CREATE TABLE USERMNGR.AUTHORIZED_CLIENT (
    ID INTEGER  (8) DEFAULT J NOT NULL,
    AUTHORIZED_CLIENT_STATUS CHAR    (10) DEFAULT 'Y',
    CLIENT_NAME VARCHAR (40) DEFAULT 'N' NOT NULL,
    NATIONAL_CODE CHAR    (16) DEFAULT 'N' NOT NULL,
    PRIMARY KEY (ID)
);

SQL Error [42601]: An unexpected token "(" was found following "LIENT ( 
 ID INTEGER".  Expected tokens may include:  "CHECK".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.33.31

And another try:

CREATE TABLE USERMNGR.AUTHORIZED_CLIENT (
    ID INTEGER DEFAULT J NOT NULL,
    AUTHORIZED_CLIENT_STATUS CHAR    (10) DEFAULT 'Y',
    CLIENT_NAME VARCHAR (40) DEFAULT 'N' NOT NULL,
    NATIONAL_CODE CHAR    (16) DEFAULT 'N' NOT NULL,
    PRIMARY KEY (ID)
);

And the error:

SQL Error [42601]: An unexpected token "NOT" was found following "D INTEGER  DEFAULT J".  Expected tokens may include:  "<references_spec>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.33.31

I’m a newbie in DB2, know more about sql but I’m no pro there either. Am I doing something wrong here? Thanks in advance.

>Solution :

It looks like you’re encountering some syntax issues while trying to create a table in your DB2 database. Let’s see if we can fix that!

CREATE TABLE USERMNGR.AUTHORIZED_CLIENT (
    ID BIGINT DEFAULT 0 NOT NULL,
    AUTHORIZED_CLIENT_STATUS CHAR(10) DEFAULT 'Y',
    CLIENT_NAME VARCHAR(40) DEFAULT 'N' NOT NULL,
    NATIONAL_CODE CHAR(16) DEFAULT 'N' NOT NULL,
    PRIMARY KEY (ID)
);

In this corrected answer:

I removed the size specification for BIGINT, as it’s unnecessary.

Give it a shot, and let’s see if it works. I’m not entirely sure if this will fix the problem, but it’s worth a try.