What is wrong with this SQL? Problem with Long Varchar column at create Table

[Okonita]

Hi everyone,

I have been building a set of tables for a new database and having
problem with tables having columns defined as Long Varchar. Can
someone look this over and tell me what I am doing wrong with the
create statement shown below:

Error Msg:

DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned:SQL0104N An unexpected
token "LONG_VARCHAR" was found following ",2), TX_SUB_GUARDIAN".
Expected tokens may include: "CHARACTER". SQLSTATE=42601

Create Statement:

CREATE TABLE "DB2T"."AGREEMENT" ( ID_CASE INTEGER not null, ID_PRSN
INTEGER not null, ID_AGRM INTEGER not null, CD_AGRM_TYPE INTEGER not
null, DT_AGRM TIMESTAMP not null,
TX_DOC_PATH VARCHAR(36), ID_CR INTEGER not null, TS_CR TIMESTAMP not
null, ID_UP INTEGER not null, TS_UP TIMESTAMP not null, AM_AGRMNT
DECIMAL(9,2), CD_SRVC INTEGER,

TX_SUB_GUARDIAN LONG_VARCHAR(4000),

DT_ESTIMATED_END TIMESTAMP,
ETL_INS_TS TIMESTAMP not null,
ETL_UPD_TS TIMESTAMP not null,

CONSTRAINT AGREEMENT_PK
PRIMARY KEY (ID_CASE,ID_PRSN,ID_AGRM) );
Thanking you all in advance.

Okonita

[Mark A]

<Ok*****@gmail.comwrote in message
news:11**********************@57g2000hsv.googlegro ups.com...

Hi everyone,

I have been building a set of tables for a new database and having
problem with tables having columns defined as Long Varchar. Can
someone look this over and tell me what I am doing wrong with the
create statement shown below:

Error Msg:

DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned:SQL0104N An unexpected
token "LONG_VARCHAR" was found following ",2), TX_SUB_GUARDIAN".
Expected tokens may include: "CHARACTER". SQLSTATE=42601

Create Statement:

CREATE TABLE "DB2T"."AGREEMENT" ( ID_CASE INTEGER not null, ID_PRSN
INTEGER not null, ID_AGRM INTEGER not null, CD_AGRM_TYPE INTEGER not
null, DT_AGRM TIMESTAMP not null,
TX_DOC_PATH VARCHAR(36), ID_CR INTEGER not null, TS_CR TIMESTAMP not
null, ID_UP INTEGER not null, TS_UP TIMESTAMP not null, AM_AGRMNT
DECIMAL(9,2), CD_SRVC INTEGER,

TX_SUB_GUARDIAN LONG_VARCHAR(4000),

DT_ESTIMATED_END TIMESTAMP,
ETL_INS_TS TIMESTAMP not null,
ETL_UPD_TS TIMESTAMP not null,

CONSTRAINT AGREEMENT_PK
PRIMARY KEY (ID_CASE,ID_PRSN,ID_AGRM) );
Thanking you all in advance.

Okonita

That is not the correct syntax. Do you have an SQL manual?

But you should not use long varchar anyway since it will be unsupported in
future releases. Use VARCHAR instead. If you need something bigger than
VARCHAR (which is only a few bytes less than long varchar) then use a CLOB
or BLOB.

[Serge Rielau]

Ok*****@gmail.com wrote:

TX_SUB_GUARDIAN LONG_VARCHAR(4000),

LONG VARCHAR (no "_") and no (4000).
LONG VARCHAR has a fixed length.
BUT you shouldn't us ethe LONG types they are obsolete.
What you should use is either
VARCHAR(4000) with a bigger page size (DB2 can handle up to 32KB in a
row) or use CLOB.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Okonita]

On Sep 10, 6:51 am, Serge Rielau <[e-mail]:

[e-mail] wrote:

TX_SUB_GUARDIAN LONG_VARCHAR(4000),

LONG VARCHAR (no "_") and no (4000).
LONG VARCHAR has a fixed length.
BUT you shouldn't us ethe LONG types they are obsolete.
What you should use is either
VARCHAR(4000) with a bigger page size (DB2 can handle up to 32KB in a
row) or use CLOB.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge,

Thank you very much...it worked beautifully. The DDl was generated
from the Mainframe using Squirrel tool. When I did the same thing
using db2look, there was no LONG_VARCHARs generated.

Okonita