Hello
Thanks a lot for your help.
Our DS people would like to remove the table data by calling a function (can be any other objects as well) in a SELECT query.
SELECT TRUNCATE_TABLE_ PARTITION('p_ta ble_name','p_su ffix' ) from sysibm.sysdummy 1;
This is a partitioned system in DB2 V9.5. We have already tried most of the truncating features and we could not success in this case..
1. Delete from table - Not possible to include in a DB2 function.
2. IMPORT FROM /dev/null OF DEL REPLACE INTO tablename - Not possible to include in a function.
3. ALTER TABLE <> NOT LOGGED INITIALLY WITH EMPTY TABLE; - Not possible to include this in a function.
4. Truncate table is one option.. it's available only in DB2 V9.7
5. Try to create a stored procedure and wrap it up using function.. However, this is not supported in DB2 partitioned environment.
sp> db2 -td@ -f tf.sql
DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:
SQL0270N Function not supported (Reason code = "71"). LINE NUMBER=7. SQLSTATE=42997
6. Any way to call the stored procedure in a select query... No idea if there is any way..
Please let me know if you have any clue with the same... my requirement is remove the PARTitioned table data from a select query.
Kind Regards,
Eldho Mathew
Thanks a lot for your help.
Our DS people would like to remove the table data by calling a function (can be any other objects as well) in a SELECT query.
SELECT TRUNCATE_TABLE_ PARTITION('p_ta ble_name','p_su ffix' ) from sysibm.sysdummy 1;
This is a partitioned system in DB2 V9.5. We have already tried most of the truncating features and we could not success in this case..
1. Delete from table - Not possible to include in a DB2 function.
2. IMPORT FROM /dev/null OF DEL REPLACE INTO tablename - Not possible to include in a function.
3. ALTER TABLE <> NOT LOGGED INITIALLY WITH EMPTY TABLE; - Not possible to include this in a function.
4. Truncate table is one option.. it's available only in DB2 V9.7
5. Try to create a stored procedure and wrap it up using function.. However, this is not supported in DB2 partitioned environment.
sp> db2 -td@ -f tf.sql
DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returned:
SQL0270N Function not supported (Reason code = "71"). LINE NUMBER=7. SQLSTATE=42997
6. Any way to call the stored procedure in a select query... No idea if there is any way..
Please let me know if you have any clue with the same... my requirement is remove the PARTitioned table data from a select query.
Kind Regards,
Eldho Mathew