• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Truncate on the As400 DB2

Jane_d

Member
Hi,
We would like to clear out many tables, some very large files in a test environment and are looking for a DB2 alternative to the Oracle sql 'Truncate Table' Command.
It is important that the space is immediatly released after the table has been cleared.

There are several postings on the web about using the command
"ALTER TABLE YourTable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE"

any thoughts? DOes any one use this command? Does this mean that the logging is disabled only for this alter table command? Any other considerations or thoughts about using this command?

your thoughts are appreciated
JDE 810 Tools 8.94
As400 V5R3
 

ebearman

Member
Jane,

Ken is correct that CLRPFM is the best command to truncate a file on AS400/iSeries/System i. If you do not have access to the command line for some reason and only have SQL access, you could run the CLRPFM through SQL. Here is an example:
Call QSYS.QCMDEXE('QSYS/CLRPFM FILE(TESTLIB/MYFILE)', 0000000032.00000)
The number after the comma with the leading zeroes is the number of characters after QCMDEXE and must be exact.
 
Hi,

Are we talking about DB2/400 (green screen on iSeries/AS400)
or about UDB/DB2 (another IBM db that runs on Windows,
Linux and UNIX)?

Even if their names sound similar, they're completely
different beasts.
 
Top