View previous topic :: View next topic |
Author |
Message |
satyenderd Beginner
Joined: 26 Aug 2005 Posts: 144 Topics: 73
|
Posted: Tue Jul 15, 2008 4:13 pm Post subject: CREATE TABLE AS |
|
|
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 _________________ Satya |
|
Back to top |
|
|
Eric.C.Bakke Beginner
Joined: 14 Jul 2008 Posts: 15 Topics: 0 Location: Plano, TX
|
Posted: Tue Jul 15, 2008 4:36 pm Post subject: |
|
|
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 |
|
Back to top |
|
|
satyenderd Beginner
Joined: 26 Aug 2005 Posts: 144 Topics: 73
|
Posted: Tue Jul 15, 2008 4:52 pm Post subject: CREATE TABLE AS |
|
|
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 |
|
Back to top |
|
|
Eric.C.Bakke Beginner
Joined: 14 Jul 2008 Posts: 15 Topics: 0 Location: Plano, TX
|
Posted: Tue Jul 15, 2008 4:57 pm Post subject: |
|
|
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 |
|
Back to top |
|
|
Eric.C.Bakke Beginner
Joined: 14 Jul 2008 Posts: 15 Topics: 0 Location: Plano, TX
|
Posted: Tue Jul 15, 2008 4:59 pm Post subject: |
|
|
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 |
|
Back to top |
|
|
satyenderd Beginner
Joined: 26 Aug 2005 Posts: 144 Topics: 73
|
Posted: Tue Jul 15, 2008 5:07 pm Post subject: CREATE TABLE AS |
|
|
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 |
|
Back to top |
|
|
|
|