Sumanth wrote:
Thanks Serge.
For this to be executed within the application code would require the
application-db2-user to have alter privileges.. is it a good practice?
Also is truncate being planned for future DB2 implementations.
Eventually. I'm not sure whether a hypothetical TRUNCATE will only
require DELETE privileges.
It's a pretty big gun to unrecoverably wipe a table, and ignore triggers..
After all I assume you do not want to simple have TRUNCATE be a
"DELETE FROM T", right?
Interstingly I tried to see if you can work around it, but DB2 is
quite paranoid at this particluar point:
db2 => --#SET TERMINATOR $
db2 => DROP SPECIFIC PROCEDURE TRUNCATE
db2 (cont.) => $
DB20000I The SQL command completed successfully.
db2 => CALL SYSPROC.SET_ROUTINE_OPTS('DYNAMICRULES BIND')
db2 (cont.) => $
Return Status = 0
db2 => CREATE PROCEDURE TRUNCATE(IN tabschema VARCHAR(128),
db2 (cont.) => IN tabname VARCHAR(128))
db2 (cont.) => SPECIFIC TRUNCATE
db2 (cont.) => BEGIN
db2 (cont.) => DECLARE txt VARCHAR(1000);
db2 (cont.) => SET txt = 'ALTER TABLE "' || tabschema || '"."'
db2 (cont.) => || tabname || '" ACTIVATE NOT LOGGED'
db2 (cont.) => || ' INITIALLY WITH EMPTY TABLE';
db2 (cont.) => EXECUTE IMMEDIATE txt;
db2 (cont.) => COMMIT;
db2 (cont.) => END
db2 (cont.) => $
DB20000I The SQL command completed successfully.
db2 => CALL SYSPROC.SET_ROUTINE_OPTS(CAST(NULL AS VARCHAR(1)))
db2 (cont.) => $
Return Status = 0
db2 => GRANT EXECUTE ON PROCEDURE TRUNCATE TO JOE
db2 (cont.) => $
DB20000I The SQL command completed successfully.
db2 => --#SET TERMINATOR ;
db2 => call truncate('SRIELAU', 'A3');
SQL0549N The "ALTER" statement is not allowed for "package" "P7300390"
because the bind option DYNAMICRULES RUN is not in effect for the "package".
SQLSTATE=42509
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab