I created a very simple table in db2, the prompt gave a success
message. but when I try to do a describe or select or insert or
basically anything with that table, it says the object does not exist.
i have also tried doing it by giving it's fully qualified name. also,
when I try to drop the table, it says the object does not exist. if I
try to create another table with the same name, it says the object
already exists. so far, I am unable to figure out the head and tail of
this. can anyone help me? also, the tablespaces are in normal state,
the db is working otherwise ok, there are other tables which are
working just fine. will a runstat/reorg help. i have already done
instance stop/start.

my setup is:
db2 7.1 EE running on redhat 7.3 kernel 2.4.18
ram 448 mb, hdd/concerned partition more than 3 gb free
this is a development box

regards,
dotyet

This wasn't a DECLARED GLOBAL TEMPORARY TABLE by any chance?
It would live in the SESSION schema?
So when dropping or select you would need to qualify it with
SESSION.<tablename>

Cheers
Serge

On Thu, 31 Jul 2003 10:12:41 -0400, "Serge Rielau"
<srielau@ca.eyebeem.com> wrote:

>This wasn't a DECLARED GLOBAL TEMPORARY TABLE by any chance?
>It would live in the SESSION schema?
>So when dropping or select you would need to qualify it with
>SESSION.<tablename>

Speaking of which....

It would be really, really nice if DB2 could do:

SELECT * FROM MYSCHEMA.TABLEA
INTO TEMP TABLEB ;

rather than:

DECLARE GLOBAL TEMPORARY TABLE SESSION.TABLEB
LIKE MYSCHEMA.TABLEA
ON COMMIT PRESERVE ROWS NOT LOGGED ;

INSERT INTO SESSION.TABLEB
SELECT * FROM MYSCHEMA.TABLEA ;

I became fond of this shorter syntax when I was an Informix DBA...

I know that DECLARE GLOBAL TEMPORARY TABLE is SQL-92 compliant, so its
obviously the way to go. But I wonder why the INTO TEMP is not
SQL-92?

allen

thanks for all the efforts.

No. 1)The schema is the same as I have written, i.e. the default
schema of this user.

No. 2)This is a regular user table.

No. 3)The case was all lower. I have tried the following options to
drop the table

drop digger
drop DIGGER
drop db2inst1.digger
drop DB2INST1.DIGGER
drop db2inst1.DIGGER


Now, something new which might help. I ran db2dart on the database and
it ran successfully without reporting any errors with the database. I
also ran runstats and reorg and reorgchk, but ,ALL of them somehow did
not give the tablename in their output. I have changed the diag level
to 4 and something which I think could be of importance is this: when
I try to drop the table using it's name or fully qualified name, an
entry occurs in db2diag.log,

DIA1004C: A Cursor is still active, file token "".

I have now tried the following on the database to catch the root
cause/solution

db2dart
runstats
reorg
reorgchk
restart
db2stop/db2start

I am not sure, but what I am making out of this scene is that the
table has been 'semi' created in the database, somewhere it's
respective entries are there and somewhere they are not. but can this
happen?

regards,
dotyet

Blair Adamache <badamache@2muchspam.yahoo.com> wrote in message news:<bgbahg$s08$1@hanover.torolab.ibm.com>...
> Did you create it in mixed case (i.e. with delimiters)? DB2 will fold
> identifiers and names to upper case unless you delimit.
>
> Examples:
>
> Case sensitivity and object names
> All database object names (tables, views, columns and so on) are stored
> in the catalog tables in uppercase unless the identifier is delimited.
> If you use a delimited name to create the identifier, the exact case of
> the name is stored in the catalog tables.
>
> An identifier, such as a column name or table name, is treated as case
> insensitive when used in an SQL statement unless it is delimited. For
> example, assume that the following statements are issued:
>
> CREATE TABLE MyTable (id INTEGER)
> CREATE TABLE "YourTable" (id INTEGER)
> Two tables -- MYTABLE and YourTable -- will exist.
>
> Now, the following two statements are equivalent:
>
> SELECT * FROM MyTable
> SELECT * FROM MYTABLE
> However, the second statement below will fail with TABLE NOT FOUND since
> there is no table named YOURTABLE:
>
> SELECT * FROM "YourTable" // executes without error
> SELECT * FROM YourTable // error, table not found
>
>
> dotyet wrote:
>
> > I created a very simple table in db2, the prompt gave a success
> > message. but when I try to do a describe or select or insert or
> > basically anything with that table, it says the object does not exist.
> > i have also tried doing it by giving it's fully qualified name. also,
> > when I try to drop the table, it says the object does not exist. if I
> > try to create another table with the same name, it says the object
> > already exists. so far, I am unable to figure out the head and tail of
> > this. can anyone help me? also, the tablespaces are in normal state,
> > the db is working otherwise ok, there are other tables which are
> > working just fine. will a runstat/reorg help. i have already done
> > instance stop/start.
> >
> > my setup is:
> > db2 7.1 EE running on redhat 7.3 kernel 2.4.18
> > ram 448 mb, hdd/concerned partition more than 3 gb free
> > this is a development box
> >
> > regards,
> > dotyet

dotyet <dotyet@yahoo.com> wrote:

> I created a very simple table in db2, the prompt gave a success
> message. but when I try to do a describe or select or insert or
> basically anything with that table, it says the object does not exist.
> i have also tried doing it by giving it's fully qualified name. also,
> when I try to drop the table, it says the object does not exist. if I
> try to create another table with the same name, it says the object
> already exists. so far, I am unable to figure out the head and tail of
> this. can anyone help me? also, the tablespaces are in normal state,
> the db is working otherwise ok, there are other tables which are
> working just fine. will a runstat/reorg help. i have already done
> instance stop/start.
>
> my setup is:
> db2 7.1 EE running on redhat 7.3 kernel 2.4.18
> ram 448 mb, hdd/concerned partition more than 3 gb free
> this is a development box

You do use the same connection to query the table, fire a COMMIT after the
CREATE TABLE or have auto-commit turned on?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

autocommit is on.

rgds,
dotyet

Knut Stolze <stolze@de.ibm.com> wrote in message news:<bgdcd0$6ni$3@fsuj29.rz.uni-jena.de>...
> dotyet <dotyet@yahoo.com> wrote:
>
> > I created a very simple table in db2, the prompt gave a success
> > message. but when I try to do a describe or select or insert or
> > basically anything with that table, it says the object does not exist.
> > i have also tried doing it by giving it's fully qualified name. also,
> > when I try to drop the table, it says the object does not exist. if I
> > try to create another table with the same name, it says the object
> > already exists. so far, I am unable to figure out the head and tail of
> > this. can anyone help me? also, the tablespaces are in normal state,
> > the db is working otherwise ok, there are other tables which are
> > working just fine. will a runstat/reorg help. i have already done
> > instance stop/start.
> >
> > my setup is:
> > db2 7.1 EE running on redhat 7.3 kernel 2.4.18
> > ram 448 mb, hdd/concerned partition more than 3 gb free
> > this is a development box
>
> You do use the same connection to query the table, fire a COMMIT after the
> CREATE TABLE or have auto-commit turned on?

sorry Wolfgang,

that was a typo.

the correct statement I used was

db2 "drop table digger"

sorry for the confusion.

regards,
dotyet

Wolfgang Riedel <wolfgang.riedel@development.retarus.de> wrote in message news:<3F2A7E80.755C5991@development.retarus.de>...
> dotyet wrote:
> >
> > thanks for all the efforts.
> >
> > No. 1)The schema is the same as I have written, i.e. the default
> > schema of this user.
> >
> > No. 2)This is a regular user table.
> >
> > No. 3)The case was all lower. I have tried the following options to
> > drop the table
> >
> > drop digger
> > drop DIGGER
> > drop db2inst1.digger
> > drop DB2INST1.DIGGER
> > drop db2inst1.DIGGER
> <snip>
> > > > regards,
> > > > dotyet
>
> just to be sure, you tried:
>
> drop Table DIGGER
> ^^^^^
> (with appr. privileges)?
>
> Wolfgang

In article <6c6dc4d2.0308040014.7e1ec98d@posting.google.com >,
dotyet@yahoo.com says...
> sorry Wolfgang,
>
> that was a typo.
>
> the correct statement I used was
>
> db2 "drop table digger"
>
> sorry for the confusion.


Is your problem solved already?

If not, how exactly is your tablename listed using the 'list tables
for all' command?

Gert van der Kooij <gk-ibm-db2@xs4all.nl> wrote in message news:<MPG.199f07b09c75e40c9898a7@news.xs4all.nl>.. .
> In article <6c6dc4d2.0308040014.7e1ec98d@posting.google.com >,
> dotyet@yahoo.com says...
> > sorry Wolfgang,
> >
> > that was a typo.
> >
> > the correct statement I used was
> >
> > db2 "drop table digger"
> >
> > sorry for the confusion.
>
>
> Is your problem solved already?
>
> If not, how exactly is your tablename listed using the 'list tables
> for all' command?

no my problem is still not solved. giving a 'list tables for all'
shows the table entry as any other table under that schema 'db2inst1'.
one thing which I have noticed though, i took an offline backup of the
database and dropped the existing one. then i restored the same backup
to some other containers. the problem continues. any more
suggestions!!

hth,
dotyet

In article <6c6dc4d2.0308110248.4fc45584@posting.google.com >,
dotyet@yahoo.com says...
> Gert van der Kooij <gk-ibm-db2@xs4all.nl> wrote in message news:<MPG.199f07b09c75e40c9898a7@news.xs4all.nl>.. .
> > In article <6c6dc4d2.0308040014.7e1ec98d@posting.google.com >,
> > dotyet@yahoo.com says...
> > > sorry Wolfgang,
> > >
> > > that was a typo.
> > >
> > > the correct statement I used was
> > >
> > > db2 "drop table digger"
> > >
> > > sorry for the confusion.
> >
> >
> > Is your problem solved already?
> >
> > If not, how exactly is your tablename listed using the 'list tables
> > for all' command?
>
> no my problem is still not solved. giving a 'list tables for all'
> shows the table entry as any other table under that schema 'db2inst1'.
> one thing which I have noticed though, i took an offline backup of the
> database and dropped the existing one. then i restored the same backup
> to some other containers. the problem continues. any more
> suggestions!!
>
> hth,
> dotyet
>

Does it show up in lowercase? If that's the problem you need enter the
name in lowercase, something like 'db2 drop table "db2inst"."digger"'