Kbase 18002: How to delete all the records of a table - DELETE DROP
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/20/1999 |
|
How to delete all the records of a table - DELETE DROP
INTRODUCTION:
=============
This knowledgebase entry provides sample codes for differents approach
to delete all the records from a table.
WHY YOU NEED TO KNOW THIS:
===========================
Sometimes the developers needs to programmatically delete all the
records of a table.
PROCEDURAL APPROACH:
====================
1) Deleting all the records using Progress 4GL DELETE statement:
FOR EACH table:
DELETE table.
END.
2) Deleting all the records using Progress/SQL DELETE statement:
DELETE FROM table.
3) Removing the table by loading a delta.df with a DROP TABLE command
and the respective CREATE TABLE command:
CREATE ALIAS DICTDB FOR DATABASE test.
DISPLAY LDBNAME("DICTDB").
RUN prodict/load_df.p ("t.df").
DELETE ALIAS DICTDB. /* Optional */
NOTES:
- The transaction size of the first program is small, because it is
one transaction for each record iteration, so the before-image file
only needs rollback information for one record at a time.
- The transaction size of the second and third program is big,
because it is just one transaction for all the records.
The before-image file will store rollback information for all the
records involved in the transaction.
In other words, if the table has one million records and is 100 Mb,
the before image will grow up at least 100 Mb.
You have to consider which would be the size of the before-image
file before use those option, since it can try to grew beyond the
maximum size (2Gb) and fail.
Ask your DBA before do this operation.
ATTEMPT TO EXTENT THE BEFORE IMAGE BEYOND THE LIMIT IS NOT
RECOMMENDED.
- Testings with V8.2C showed that the third program is faster than the
other two and that the second one than the first one.
However, since it deletes the table and creates it, it may change
the CRC of the table causing the programs to need to be compiled.
The developer or the DBA should ensure that the program is using
the correct, most up-to-date, data definition file.
- A program that uses the Progress/4GL and the Progress/SQL DELETE
statements can filter records if this is needed.
- To time these procedures you can use the ETIME function.
Example:
DEF VAR i AS INTEGER.
i = ETIME(YES).
DELETE FROM table.
DISPLAY ETIME.
NOTES ABOUT prodict/load_df.p
- The source code of the Data Dictionary routines to dump/load data
and definitions are located in dlc/src/prodict.
- Parameters required by the programs are documented at the top of
each program.
- Progress does not provide support for the modification and/or use of
use of these modules outside of the Data Dictionary.
Kbase 17590: SAMPLE CODE to Dump Load .df,.d w/ Data Dictionary routin
has a sample program to dump the definitions.
SAMPLE DATA DEFINITION FILE (.df) that DROP and ADD a table
DROP TABLE "test"
ADD TABLE "test"
DUMP-NAME "test"
ADD FIELD "test-num" OF "test" AS integer
FORMAT "->,>>>,>>9"
INITIAL "0"
ORDER 10
ADD FIELD "test-desc" OF "test" AS character
FORMAT "X(8)"
INITIAL ""
ORDER 20
ADD INDEX "idx" ON "test"
UNIQUE
PRIMARY
INDEX-FIELD "test-num" ASCENDING
.
ONLINE PROCEDURES OR UTILITIES:
- Progress Data Dictionary
- Progress Data Dictionary sources files (DLC/src/prodict).
REFERENCES TO WRITTEN DOCUMENTATION:
====================================
- Progress Language Reference manual
- Progress System Administration Reference
EAG June 1998