Table drop
The poster dumped a table's schema with dbschema, dropped the table, recreated it from the .sql file, and then found the old rows still present — asking whether DROP TABLE really deletes data. Replies clarified that dbschema only dumps DDL (use dbexport or UNLOAD to save data), and that DROP TABLE does remove the table, its indexes and all rows. Suggestions included verifying the drop actually succeeded (an uncommitted drop in a logged/ANSI database would roll back, though the recreate would then fail) and using TRUNCATE to empty a table. No definitive cause was confirmed, as the poster never supplied version/platform details or error messages.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Query:-
I have taken the backup of table using dbschema
dbschema -d <dbname> -t <tablename> > xyz.sql
then I dropped that table.
Recover that table using dbaccess <Database name> xyz.sql
After that when I try to count the records using select , all records are
there?
Can anybody help me, if we drop a drop, does it removes all the records from
the table? I want to drop the table including all records
Thx for all your help.
Hi Deepak,
dbschema does not take a backup, it only dumps DDL.
You would need to use dbexport, if you wanted all records as well. Dbexport
dumps the ddl and the records.
-Mark
________________________________
From: DEEPAK JOSHI <djoshih@hotmail.com>
To: ids@iiug.org
Sent: Friday, October 2, 2009 2:43:15 PM
Subject: Table drop [17303]
Query:-
I have taken the backup of table using dbschema
dbschema -d <dbname> -t <tablename> > xyz.sql
then I dropped that table.
Recover that table using dbaccess <Database name> xyz.sql
After that when I try to count the records using select , all records are
there?
Can anybody help me, if we drop a drop, does it removes all the records from
the table? I want to drop the table including all records
Thx for all your help.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Or dbaccess' UNLOAD command, if it's only one or a few tables.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Mark Jamison
Sent: Friday, October 02, 2009 3:04 PM
To: ids@iiug.org
Subject: Re: Table drop [17304]
Hi Deepak,
dbschema does not take a backup, it only dumps DDL.
You would need to use dbexport, if you wanted all records as well.
Dbexport
dumps the ddl and the records.
-Mark
________________________________
From: DEEPAK JOSHI <djoshih@hotmail.com>
To: ids@iiug.org
Sent: Friday, October 2, 2009 2:43:15 PM
Subject: Table drop [17303]
Query:-
I have taken the backup of table using dbschema
dbschema -d <dbname> -t <tablename> > xyz.sql
then I dropped that table.
Recover that table using dbaccess <Database name> xyz.sql
After that when I try to count the records using select , all records
are
there?
Can anybody help me, if we drop a drop, does it removes all the records
from
the table? I want to drop the table including all records
Thx for all your help.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Additional: the DROP SQL command does delete the table, all indexes and
all data.
-----Original Message-----
From: Everett Mills
Sent: Friday, October 02, 2009 3:07 PM
To: ids@iiug.org
Subject: RE: Table drop [17304]
Or dbaccess' UNLOAD command, if it's only one or a few tables.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Mark Jamison
Sent: Friday, October 02, 2009 3:04 PM
To: ids@iiug.org
Subject: Re: Table drop [17304]
Hi Deepak,
dbschema does not take a backup, it only dumps DDL.
You would need to use dbexport, if you wanted all records as well.
Dbexport
dumps the ddl and the records.
-Mark
________________________________
From: DEEPAK JOSHI <djoshih@hotmail.com>
To: ids@iiug.org
Sent: Friday, October 2, 2009 2:43:15 PM
Subject: Table drop [17303]
Query:-
I have taken the backup of table using dbschema
dbschema -d <dbname> -t <tablename> > xyz.sql
then I dropped that table.
Recover that table using dbaccess <Database name> xyz.sql
After that when I try to count the records using select , all records
are
there?
Can anybody help me, if we drop a drop, does it removes all the records
from
the table? I want to drop the table including all records
Thx for all your help.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
The table might not have dropped - confirm that it is dropped. You can =
also
use truncate table to quickly empty the table.
Regds,
Uday.
|------------>
| From: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|"DEEPAK JOSHI" <djoshih@hotmail.com> =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|------------>
| To: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|ids@iiug.org =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|------------>
| Date: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|10/02/2009 02:44 PM =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|------------>
| Subject: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|Table drop [17303] =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|------------>
| Sent by: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|ids-bounces@iiug.org =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
Query:-
I have taken the backup of table using dbschema
dbschema -d <dbname> -t <tablename> > xyz.sql
then I dropped that table.
Recover that table using dbaccess <Database name> xyz.sql
After that when I try to count the records using select , all records a=
re
there?
Can anybody help me, if we drop a drop, does it removes all the records=
from
the table? I want to drop the table including all records
Thx for all your help.
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
On Fri, Oct 2, 2009 at 12:43, DEEPAK JOSHI <djoshih@hotmail.com> wrote:
> Query:-
>
> I have taken the backup of table using dbschema
> dbschema -d <dbname> -t <tablename> > xyz.sql>
> then I dropped that table.
>
> Recover that table using dbaccess <Database name> xyz.sql
>
> After that when I try to count the records using select , all records are
> there?
>
> Can anybody help me, if we drop a drop, does it removes all the records
> from
> the table? I want to drop the table including all records
>
I've seen some replies not answering your question - does 'DROP TABLE' get
rid of the data?
The answer is "Yes, normally".
However, if you were in a MODE ANSI database, ran the drop statement and did
not commit the change, then your data would automagically be recovered - the
drop would be rolled back. However, then the 'CREATE TABLE' phase would
fail with table already exists. Similarly, if you ran BEGIN WORK and then
DROP TABLE in a logged database, and did not run COMMIT WORK, then the datawould be recovered by rollback of the DROP. But again, the create phase
would generate an error.
So, if the sequence you describe is accurate and you've given us all the
error messages, I'm a little puzzled.
One other way data could appear to survive - if the table was a pseudo-table
in sysmaster.
You've not given us version (though I suspect it is our friendly 7.13
instance) or platform. You may know that automatically; we do not.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Jonathan Swift<http://www.brainyquote.com/quotes/authors/j/jonathan_swift.html>;
- "May you live every day of your life."
--00c09f89925774f0de04754f2715