Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27

    Unanswered: V9.5 Truncating partitioned table

    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 ....
    Last edited by mathew.eldho; 06-18-10 at 05:55.
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Do you want the UDF to return the number of rows deleted?

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27

    No... we just have to delete

    Hello

    Thank you very much for response.

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

    Regards,
    Eldho Mathew
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  4. #4
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27

    Truncate table rows..

    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
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27
    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.
    Last edited by mathew.eldho; 06-18-10 at 05:53.
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why not do the work in a stored procedure and create a UDF wrapper around the SP?

    Andy

  8. #8
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27
    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.
    Last edited by mathew.eldho; 06-16-10 at 12:52.
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27

    @ Andy

    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
    Last edited by mathew.eldho; 06-17-10 at 06:30.
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  12. #12
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27

    @ db2girl

    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..
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  13. #13
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27

    It's not possible for our DS users at the moment

    content removed....
    Thanks for your help.
    Last edited by mathew.eldho; 06-18-10 at 05:55.
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  14. #14
    Join Date
    Jan 2007
    Location
    Pune, India
    Posts
    27

    One more question..

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

    Kind Regards,
    Eldho Mathew
    Eldho Mathew
    IBM DB2 UDB LUW Software Engineer

  15. #15
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by mathew.eldho View Post
    One more question...
    Can we call a db2 stored procedure in select statement?

    Kind Regards,
    Eldho Mathew
    No you cannot.

    Andy

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •