How to Truncate Table in DB2 and creating empty table

How to create a emtyp file in DB2, i am trying to truncate the table using load import form /dev/null of del replace schema.tablename, but it keeps saying path not found or no table to read.
Database: DB2
OS: Windows
kumarjeevanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

elfe69Commented:
Delete and create the table again, use:

DROP TABLE schema.tablename;
CREATE TABLE schema.tablename (...);
0
kumarjeevanAuthor Commented:
I dont want to drop and re-create simple delete all rows without logging, in mysql i could use truncate table command, to acheive this, but DB2 does not support this, i read through the solutions here and found out that you could trick DB2 and acheive same benefits as Truncate Table by using load import / replace command, i am unable to use this command because its not able to locate the empty table, please help
0
Kent OlsenData Warehouse Architect / DBACommented:
Hello kumarjeevan,

DROP TABLE / CREATE TABLE in sequence will empty the table very quickly.  If you have other objects tied to the table (like indexes, triggers, views, etc.) this method becomes a lot of work as you'll have to regenerate all of these objects.


If you've got sufficient privilege, the ALTER TABLE statement is perhaps the fastest way to empty the table without affecting the other objects.

ALTER TABLE mytable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;


And of course, you can always use the DELETE command.  This will empty the table, but on modest or large data volumes will be slower than the ALTER TABLE statement above.

DELETE FROM mytable;

If you're going to use this method, it will go faster if you disable logging.

--  Turn off autocommit
ALTER TABLE mytable ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM mytable;
COMMIT;



Good Luck,
Kent
0
elfe69Commented:
Try replacing /dev/null by nul, which represents the equivalent to /dev/null in Windows, maybe it will be accepted this way:

load import from nul of del replace schema.tablename
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.