MVSFORUMS.com

[satyenderd]

Could anyone help me sorting my problem of creating a table using the below query.

I am using SPUFI.

Thanks in advance.

CREATE TABLE TABLE02 AS (SELECT * FROM TLCS135);

ERROR:


DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME
SYMBOLS THAT MIGHT BE LEGAL ARE: DATA WITH <IDENTIFIER>
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 47 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'0000002F' X'00000000' SQL DIAGNOSTIC INFORMATION

[Eric.C.Bakke]

Did you check the DB2 Application SQL Manual for proper syntax? What version DB2 are you on? Are you trying to create an MQT? Or are you trying to create another table that is a clone of TLCS135...if so use the LIKE syntax...check the manual to resolve your -104.
_________________
Eric C. Bakke
Senior DB2 DBA

[satyenderd]

Thanks Eric for your response.

Eric if I am using

CREATE TABLE <TABLENAME>
LIKE <EXISTING TABLENAME>

I am getting -551 error. (Authorization Error)

At the sametime if i am trying to create a table with the mentioned syntax:

CREATE TABLE <TABLENAME>
AS (SELECT * FROM <EXISTING TABLENAME>

It is showing -104 error.


_________________
Satya

[Eric.C.Bakke]

using the MQT style of creating tables 'WITH NO DATA' clause is required if you only want a table defined in the catalog with no data which means the table isn't really an Materialized Query table. Try adding this phrase at the end of your statment before the semi-colon.

But why would you want to create a table in this fashion without specifying the IN DATABASE clause? Do you really want it to go to the default database (DSNDB04 in most shops)? You really should consider building a stogroup, database, and tablespace to put your new table in instead of having it default unless you're just needing a quick adhoc table.
_________________
Eric C. Bakke
Senior DB2 DBA

[Eric.C.Bakke]

the -551 is due to you not having authority to select from the table...you'll need someone to grant you access before you can use the 'LIKE' or 'AS' methods.
_________________
Eric C. Bakke
Senior DB2 DBA

[satyenderd]

Thanks Eric once again.

I am getting -551 ERROR. I will contact my sysadmin regardin this to grant me the previleges to create a table in Default database..


Thanks a lot for your response..
_________________
Satya