What is wrong with this SQL Create statement?

[p***@hotmail.com]

Hi all, I am getting this error when creating the following table and have not been able to find what I am doing wrong here. Could someone give me a second look-see? Here is the error and following is the create statement:

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

Complete Create Statement:
CREATE TABLE "DB2P"."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, CD_ADPTN_SUB_TYPE INTEGER, ID_PRSN_FTHR INTEGER, ID_PRSN_MTHR INTEGER, ID_BSNS1 INTEGER, ID_PRVD_ORG1 INTEGER, ID_BSNS2 INTEGER, ID_PRVD_ORG2 INTEGER, CD_AGRM_FNL CHAR(1), DT_AGRM_END TIMESTAMP, ID_TKLR INTEGER, ID_TKLR2 INTEGER, CD_PHYSCL_PNTS INTEGER, CD_EMTNL_PNTS INTEGER, CD_BHVRL_PNTS INTEGER, AM_CHLD_RATE DECIMAL(9,2), AM_ADMIN_FEE DECIMAL(9,2), AM_EXCPTNL DECIMAL(9,2), DT_AMEND TIMESTAMP, AM_ADDITIONAL DECIMAL(9,2),

AM_AMND DECIMAL(9,2), 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) );

I thank you all in advance

Okonita

[c***@hotmail.com]

I am new in db2.
Anyway, Let's try.....

TX_SUB_GUARDIAN LONG_VARCHAR(4000),

Change to

TX_SUB_GUARDIAN VARCHAR(4000),

...?

[p***@hotmail.com]

calvinscy, Thanks for your response...I am new to UDB also.
Are you saying there is no such LONG_VARCHAR data type for DB2 LUW? A few books and articles make references to LONG VARCHAR. I just have not been able to find example where it was used increating any type of UDB object. Do you have example where LONG_VARCHAR was used in a create statement?


Thanks
Okonita

[c***@hotmail.com]

Never use a long_varchar before...
Have you try "LONG VARCHAR" ... without underscore?

Anyway, I tried to search long_varchar on the IBM Db2 help.
But not find anything useful...

[Knut Stolze]

Post by p***@hotmail.com
calvinscy, Thanks for your response...I am new to UDB also.
Are you saying there is no such LONG_VARCHAR data type for DB2 LUW? A few
books and articles make references to LONG VARCHAR. I just have not been
able to find example where it was used increating any type of UDB object.
Do you have example where LONG_VARCHAR was used in a create statement?

The data type is LONG VARCHAR and not LONG_VARCHAR - and it shouldn't be
used any longer. If you need strings exceeding the VARCHAR limits, use a
CLOB.