Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Posts
    6

    Unanswered: Can I use IMPORT in a Stored Procedure - truncating a table

    I plan to truncate a table using the IMPORT command, with a empty input file. I know this can be done from the command line with a "DB2 IMPORT ...." command.

    Does anybody know how I can code the IMPORT command in a SQL Stored Procedure, as a replacement for "DELETE FROM <table name>".

    Perhaps I need to do this dynamic SQL --- but I can not find any mentions about using the IMPORT command in a stored proc.

    By the way, the stored procedure is coded using nice and simple SQL Procedure Language --- it is NOT a C or Java stored procedure.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No you can not. You can only have SQL-control-statements and
    SQL-statements in an SP. IMPORT is a DBM command and not SQL.

    Sorry I cannot give you good news.

    Andy

  3. #3
    Join Date
    May 2003
    Posts
    6
    Andy -
    Thanks for the bad news! I sort of suspected that. Dynamic SQL supports only SQL and not DB commands. Darn.

    Do you have any suggestions on how I could implement a fast table delete (similar to the Oracde TRUNCATE command) --- that I could code in a stored procedure.

    Alternatively, I guess we could write a C routine. Do you if a C routine can issue DB commands.

    Thanks for the reply.

    Chris

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Chris,
    Do you want to delete all rows from the table, or just some?
    Is there any RI to the table?

    Andy


    Originally posted by cbraudy
    Andy -
    Thanks for the bad news! I sort of suspected that. Dynamic SQL supports only SQL and not DB commands. Darn.

    Do you have any suggestions on how I could implement a fast table delete (similar to the Oracde TRUNCATE command) --- that I could code in a stored procedure.

    Alternatively, I guess we could write a C routine. Do you if a C routine can issue DB commands.

    Thanks for the reply.

    Chris

  5. #5
    Join Date
    May 2003
    Posts
    6
    Andy -
    I want to delete all rows -- and there is no RI on this table.

    Thanks,

    Chris

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Chris,

    Try this:

    Create TABLE newtable like oldtable including column defaults including identity column attributes in oldtablespace;

    -- if you have a PK you will need to add it

    alter table newtable add primary key....

    drop table oldtable;

    rename table newtable to oldtable;

    Since there is no RI, you do not have to worry about foreign key constraints, nor create them.

    HTH

    Andy


    Originally posted by cbraudy
    Andy -
    I want to delete all rows -- and there is no RI on this table.

    Thanks,

    Chris
    Last edited by ARWinner; 05-16-03 at 12:57.

  7. #7
    Join Date
    May 2003
    Posts
    6
    Thanks - Those SQL statements will work in a stored proc. I believe that the drop will invalidate packages using the table.

    I'm not sure if they will become valid once the table is re-recreated or if they will need to be rebound manually.

    FYI -- the intent here is to avoid the logging incurred by "DELETE FROM". We want to blast away the table's data as fast as possible -- it is a sizable table.

    Thanks for the info!

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Chris,
    Yes the packages will be invalidated. and yes they will not become valid when you do the rename.

    What you can do to achieve what you desire is to create the table as "NOT LOGGED INITIALLY"

    Then in your SP:

    ALTER TABLE mytable ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

    COMMIT WORK;

    The alter table statement will clear the table and not log it.
    The commit is necessary to end the UOW so that any further changes
    will be logged.

    Note: if you have any delete triggers on the table they will not be fired using this method. I suspect this is what you want.

    Andy

    Originally posted by cbraudy
    Thanks - Those SQL statements will work in a stored proc. I believe that the drop will invalidate packages using the table.

    I'm not sure if they will become valid once the table is re-recreated or if they will need to be rebound manually.

    FYI -- the intent here is to avoid the logging incurred by "DELETE FROM". We want to blast away the table's data as fast as possible -- it is a sizable table.

    Thanks for the info!

  9. #9
    Join Date
    May 2003
    Posts
    6
    Andy -

    Much thanks - I've just tested this --- it is a fine approach --- we'll go with it. Cuts the delete time of a small (30,000 rows) table by over 80%!

    Chris

  10. #10
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    Great idea Andy!
    I made a note of that one - Thanks

  11. #11
    Join Date
    Apr 2004
    Posts
    1

    Not logged initially on z/OS

    I'm curious if the same method will work on UDB 7.1 for z/OS. I don't see reference in the documentation to the not logged initially option for table alter. I'm unfortunately unable to just test it, as the DBA's are still in configuration mode, and I only have access to a windows version, where it works admirably.

    Alternately, can you do the import null of dell replace into tablename in the z/OS version of UDB 7?
    Last edited by rogowar; 04-13-04 at 11:36.

Posting Permissions

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