Hello

Our environment is DB2 V9.5.

We were trying create a user defined function for datastage people to deleting the data from partitioned table. Datastage people are expecting to use it as follows,

SELECT TRUNCATE_TABLE_PARTITION('p_table_name','p_suffix' ) from sysibm.sysdummy1;

Please see the last conclusion for further details ....

Do you want the UDF to return the number of rows deleted?

Andy

Hello

Thank you very much for response.

We don't need to return anything... we just need to delete them.

Regards,
Eldho Mathew

When we run the following query.. it just have to read the table rows and delete them..

SELECT TRUNCATE_TABLE_PARTITION('p_table_name','p_suffix' ) from sysibm.sysdummy1;

Input is Table name and column name.

Thanks,

Eldho Mathew

Is there a reason why you are implementing it as a UDF, and not as a stored procedure? Stored procedures usually have fewer restrictions for the kinds of statements you can use.

content removed....
Thanks for your help.

content removed....
Thanks for your help.content removed....
Thanks for your help.
content removed....
Thanks for your help.content removed....
Thanks for your help.content removed....
Thanks for your help.

Why not do the work in a stored procedure and create a UDF wrapper around the SP?

Andy

Hello

Our data stage team demands for the same so that they can issue a query as they had in Oracle.

SELECT TRUNCATE_TABLE_PARTITION('p_table_name','p_suffix' ) from sysibm.sysdummy1

We also considered trying the following general methods..... not sure any of them will help us here.

1. IMPORT FROM /dev/null OF DEL REPLACE INTO tablename
2. ALTER TABLE <> NOT LOGGED INITIALLY WITH EMPTY TABLE;
commit;
3. CALL ADMIN_CMD('IMPORT FROM /dev/null OF DEL REPLACE INTO tablename')

Implementing the same functionality is important for our business.... let me know if you have any thoughts... thanks a lot for your help.

sample stored proc that can be used to truncate a table using import from /dev/null. See truncate.db2:

http://publib.boulder.ibm.com/infoce.../r0007633.html

Is it possible to truncate a partition within a Table?

Currently I have to do this:

• detach the partition into a separate table
• truncate the separate table
• re-attach the partition using the now empty table

But I would like give ETL team authority to remove all rows within a partition with only DELETE authority (and without logging due to large number of rows involved).

Hello Andy,

I tried as you recommended..

1. Created a stored procedure as same as UDF mentioned above.
2. Created a function to wrap up... like,

CREATE FUNCTION TRUNCATE_EDWTABLE_PARTITION(p_table_name VARCHAR(100), p_partition_suffix VARCHAR(100))
RETURNS DECIMAL
LANGUAGE SQL
MODIFIES SQL DATA
SPECIFIC TRUNCATE_EDWTABLE_PARTITION
BEGIN ATOMIC
CALL TRUNCATE_TP(p_table_name,p_partition_suffix);
RETURN 0;
END @

This gives...

/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

=> 71
In a parallel environment, do not use CALL statement in a trigger, a SQL function, a SQL method or a dynamic compound statement.

Refer:- http://publib.boulder.ibm.com/infoce.../t0011377.html

When invoking a procedure from within an SQL trigger, an SQL routine, or a dynamic compound statement the following restrictions apply:
* In partitioned database environments procedures cannot be invoked from triggers or SQL UDFs.

Please let me know if you have any suggestions... thanks

Thanks for your help.

If we cannot invoke Stored proc from UDF ... not sure if this help me.

Let me know if you have something in your mind..

content removed....
Thanks for your help.

One more question...
Can we call a db2 stored procedure in select statement?

Kind Regards,
Eldho Mathew

No you cannot.

Andy