error creating table

error creating table

Post by Dominic Cadorett » Wed, 21 Aug 2002 00:04:33



Hello !

I'm new to DB2 systems and I don't really understand the IDENTITY clause in
the create table.
Why this command returns me this error ?

CREATE TABLE ArcoDB2.RTSCRIPT_QUE (
 QUE_ID integer IDENTITY (1, 1) NOT NULL ,
 QUE_DATA varchar(2000) NOT NULL
)
;
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 "IDENTITY" was found following "QUE ( QUE_ID
integer".  Expected tokens may include:  "DEFAULT".  SQLSTATE=42601

Besides, this command work well :
CREATE TABLE ArcoDB2.ARCOINFO (
 FT_CID integer generated always as IDENTITY (start with 1, increment by 1)
NOT NULL ,
 APPLICATION varchar (50)  ,
 CUSTOMVERSION varchar (50)  ,
 CATEGORY varchar (50)  ,
 PARMNAME varchar (50)  ,
 PARMVALUE varchar (200)
)
;

Thanks for help !

Dominic

 
 
 

error creating table

Post by bka » Wed, 21 Aug 2002 00:32:46


Try this:

CREATE TABLE ArcoDB2.RTSCRIPT_QUE (
 QUE_ID integer not null generated always as IDENTITY (start with 1, increment
by 1),
 QUE_DATA varchar(2000) NOT NULL
)

or this:

CREATE TABLE ArcoDB2.RTSCRIPT_QUE (
 QUE_ID integer not null generated by default as IDENTITY (start with 1,
increment by 1),
 QUE_DATA varchar(2000) NOT NULL
)


> Hello !

> I'm new to DB2 systems and I don't really understand the IDENTITY clause in
> the create table.
> Why this command returns me this error ?

> CREATE TABLE ArcoDB2.RTSCRIPT_QUE (
>  QUE_ID integer IDENTITY (1, 1) NOT NULL ,
>  QUE_DATA varchar(2000) NOT NULL
> )
> ;
> 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 "IDENTITY" was found following "QUE ( QUE_ID
> integer".  Expected tokens may include:  "DEFAULT".  SQLSTATE=42601

> Besides, this command work well :
> CREATE TABLE ArcoDB2.ARCOINFO (
>  FT_CID integer generated always as IDENTITY (start with 1, increment by 1)
> NOT NULL ,
>  APPLICATION varchar (50)  ,
>  CUSTOMVERSION varchar (50)  ,
>  CATEGORY varchar (50)  ,
>  PARMNAME varchar (50)  ,
>  PARMVALUE varchar (200)
> )
> ;

> Thanks for help !

> Dominic


 
 
 

error creating table

Post by Dominic Cadorett » Wed, 21 Aug 2002 01:51:45


Ok, thanks for the advice, it works well, but can you tell me whats the
difference between the two commands ?

Dominic

> Try this:

> CREATE TABLE ArcoDB2.RTSCRIPT_QUE (
>  QUE_ID integer not null generated always as IDENTITY (start with 1,
increment
> by 1),
>  QUE_DATA varchar(2000) NOT NULL
> )

> or this:

> CREATE TABLE ArcoDB2.RTSCRIPT_QUE (
>  QUE_ID integer not null generated by default as IDENTITY (start with 1,
> increment by 1),
>  QUE_DATA varchar(2000) NOT NULL
> )


> > Hello !

> > I'm new to DB2 systems and I don't really understand the IDENTITY clause
in
> > the create table.
> > Why this command returns me this error ?

> > CREATE TABLE ArcoDB2.RTSCRIPT_QUE (
> >  QUE_ID integer IDENTITY (1, 1) NOT NULL ,
> >  QUE_DATA varchar(2000) NOT NULL
> > )
> > ;
> > 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 "IDENTITY" was found following "QUE (
QUE_ID
> > integer".  Expected tokens may include:  "DEFAULT".  SQLSTATE=42601

> > Besides, this command work well :
> > CREATE TABLE ArcoDB2.ARCOINFO (
> >  FT_CID integer generated always as IDENTITY (start with 1, increment by
1)
> > NOT NULL ,
> >  APPLICATION varchar (50)  ,
> >  CUSTOMVERSION varchar (50)  ,
> >  CATEGORY varchar (50)  ,
> >  PARMNAME varchar (50)  ,
> >  PARMVALUE varchar (200)
> > )
> > ;

> > Thanks for help !

> > Dominic

 
 
 

error creating table

Post by bka » Wed, 21 Aug 2002 03:21:00


GENERATED ALWAYS means that only incremented identity values are allowed in the
column. GENERATED BY DEFAULT uses an incremented value if you don't supply one
in an INSERT or UPDATE statement.

> Ok, thanks for the advice, it works well, but can you tell me whats the
> difference between the two commands ?

> Dominic
> > Try this:

> > CREATE TABLE ArcoDB2.RTSCRIPT_QUE (
> >  QUE_ID integer not null generated always as IDENTITY (start with 1,
> increment
> > by 1),
> >  QUE_DATA varchar(2000) NOT NULL
> > )

> > or this:

> > CREATE TABLE ArcoDB2.RTSCRIPT_QUE (
> >  QUE_ID integer not null generated by default as IDENTITY (start with 1,
> > increment by 1),
> >  QUE_DATA varchar(2000) NOT NULL
> > )


> > > Hello !

> > > I'm new to DB2 systems and I don't really understand the IDENTITY clause
> in
> > > the create table.
> > > Why this command returns me this error ?

> > > CREATE TABLE ArcoDB2.RTSCRIPT_QUE (
> > >  QUE_ID integer IDENTITY (1, 1) NOT NULL ,
> > >  QUE_DATA varchar(2000) NOT NULL
> > > )
> > > ;
> > > 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 "IDENTITY" was found following "QUE (
> QUE_ID
> > > integer".  Expected tokens may include:  "DEFAULT".  SQLSTATE=42601

> > > Besides, this command work well :
> > > CREATE TABLE ArcoDB2.ARCOINFO (
> > >  FT_CID integer generated always as IDENTITY (start with 1, increment by
> 1)
> > > NOT NULL ,
> > >  APPLICATION varchar (50)  ,
> > >  CUSTOMVERSION varchar (50)  ,
> > >  CATEGORY varchar (50)  ,
> > >  PARMNAME varchar (50)  ,
> > >  PARMVALUE varchar (200)
> > > )
> > > ;

> > > Thanks for help !

> > > Dominic

 
 
 

1. Error : create table statement is not allowed within trigger

What am I missing? I have a trigger for an insert and that
fires a stored procedure. The stored procedure fails in an
attempt to create a temporary table. The following error
is displayed.

The CREATE TABLE statement is not allowed within a
trigger.

In looking at books online, I do not see this as a illegal
command for a trigger. Is it illegal or is my syntax
invalid?

A section of the stored procedure is provided below.

Thanks in advance for any suggestions.

   CREATE PROCEDURE [SP_SEND_SCM_MAIL] AS
   BEGIN









   CREATE TABLE #texttab (
        Item varchar(50),
        ItemDescrption varchar(75),
        UnitPrice varchar(10),                  
        OrderQty varchar(10),          
        ItemTotalCost varchar(10))

2. Cursor keep runing out my memory, please help.

3. Error creating tables with MS Access 2.0

4. Some easy questions

5. Error Creating Table

6. Q: Why so long?

7. error creating table with vargraphic column -- please help!!!

8. Suggestions on supporting SQL Database from Remote location

9. create table or create trigger error

10. Creating a job in a trigger causes intermittent Can not CREATE TABLE error

11. error message: table or view existed when creating a table

12. Create table creates invisible table