CREATE TABLE DB2 SQLSTATE: 42601, SQLCODE: -104): DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601

Question

I am trying to create a DB2 table with the following

CREATE TABLE ACQ_FAH_DEV.fah_balance_ledger
(
    "ACTIVE" VARCHAR(10),
    INPUT_BY VARCHAR(32),
    INPUT_TIME DATE,
    AMENDED_BY VARCHAR(32),
    AMENDED_TIME DATE,
    ENTITY VARCHAR(20),
    ACCOUNT_CODE VARCHAR(20),
    ACCOUNT_NAME VARCHAR(255),
    PORTFOLIO_CODE VARCHAR(100),
    OM_LOAD_RUN_ID VARCHAR(128) NOT NULL,    
    OM_LOAD_TMST TIMESTAMP(6) NOT NULL,    
    BM_BUSINESS_INTERVAL_TYP VARCHAR(20) NOT NULL,
    BM_BUSINESS_INTERVAL_TMST TIMESTAMP(6) NOT NULL,    
    BM_BUSINESS_INTERVAL_START_END_FLAG VARCHAR(10) NOT NULL,    
    SM_SOURCE_SYSTEM_CD VARCHAR(16) NOT NULL,    
    OM_UNIQUE_ROW_ID BIGINT NOT NULL,    
    OM_USER_ID VARCHAR(100) NOT NULL,    
    OM_VERSION_ID SMALLINT NOT NULL
)
ORGANIZE BY COLUMN IN ACQ_FAH_DEV
DISTRIBUTE BY HASH(
    ACCOUNT_CODE,
    OM_VERSION_ID,
    BM_BUSINESS_INTERVAL_TYP
);

but running into this error

(SQLSTATE: 42601, SQLCODE: -104): DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=(;LOAD_RUN_ID VARCHAR;BINARY, DRIVER=4.26.14 SQLSTATE 42601: A character, token, or clause is invalid or missing. SQL0104N An unexpected token "(" was found following "LOAD_RUN_ID VARCHAR". Expected tokens may include: "BINARY".

The error seems innocuous but I am not able to see why this is failing.

What's the Db2 platform and version? What tool do you use to run this statement? — Mark Barinstein

Lennart Lennart · Accepted Answer · 2022-04-15T08:31:21

For LUW, the tablespace clause should be before the organized clause (I assume IN ACQ_FAH_DEV refers to which tablespace to put the table in). Try:

CREATE TABLE ACQ_FAH_DEV.fah_balance_ledger
(
    "ACTIVE" VARCHAR(10),
    INPUT_BY VARCHAR(32),
    INPUT_TIME DATE,
    AMENDED_BY VARCHAR(32),
    AMENDED_TIME DATE,
    ENTITY VARCHAR(20),
    ACCOUNT_CODE VARCHAR(20),
    ACCOUNT_NAME VARCHAR(255),
    PORTFOLIO_CODE VARCHAR(100),
    OM_LOAD_RUN_ID VARCHAR(128) NOT NULL,
    OM_LOAD_TMST TIMESTAMP(6) NOT NULL,
    BM_BUSINESS_INTERVAL_TYP VARCHAR(20) NOT NULL,
    BM_BUSINESS_INTERVAL_TMST TIMESTAMP(6) NOT NULL,
    BM_BUSINESS_INTERVAL_START_END_FLAG VARCHAR(10) NOT NULL,
    SM_SOURCE_SYSTEM_CD VARCHAR(16) NOT NULL,
    OM_UNIQUE_ROW_ID BIGINT NOT NULL,
    OM_USER_ID VARCHAR(100) NOT NULL,
    OM_VERSION_ID SMALLINT NOT NULL
)
IN ACQ_FAH_DEV
ORGANIZE BY COLUMN
DISTRIBUTE BY HASH(
    ACCOUNT_CODE,
    OM_VERSION_ID,
    BM_BUSINESS_INTERVAL_TYP
);

Documentation for CREATE TABLE statement

CREATE TABLE DB2 SQLSTATE: 42601, SQLCODE: -104): DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601

1 Answers