Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Red face Unanswered: Truncate Table In Db2

    Hi all,

    I am trying to use IMPORT in ADMIN_CMD in a procedure as shown below

    Code:

    CREATE TABLE tab1 ( col1 INTEGER NOT NULL PRIMARY KEY, col2 VARCHAR(15) )@

    INSERT INTO tab1 VALUES ( 1, 'some data' ), ( 2, NULL )@

    --SELECT * FROM tab1 @

    CREATE PROCEDURE DELETE_DATA()
    SPECIFIC DELETE_DATA
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    BEGIN ATOMIC

    CALL SYSPROC.ADMIN_CMD('IMPORT FROM /dev/null OF IXF
    MESSAGES ON SERVER INSERT_UPDATE INTO TAB1');

    END

    CALL DELETE_DATA()@


    I get the following error:

    CALL DELETE_DATA()
    SQL0104N An unexpected token "IMPORT" was found following
    "BEGIN-OF-STATEMENT". Expected tokens may include: "DESCRIBE".
    SQLSTATE=42601

    SQL0104N An unexpected token "IMPORT" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "DESCRIBE ".

    Explanation:

    A syntax error in the SQL statement or the input command string
    for the SYSPROC.ADMIN_CMD procedure was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement or the input command
    string for the SYSPROC.ADMIN_CMD procedure that preceded the
    token that is not valid.

    As an aid, a partial list of valid tokens is provided in the
    SQLERRM field of the SQLCA as "<token-list>". This list assumes
    the statement is correct to that point.

    The statement cannot be processed.

    User Response:

    Examine and correct the statement in the area of the specified
    token.

    sqlcode : -104

    sqlstate : 42601


    Any idea what the problem might be? I ran db2updv8 against the database.

    In a nutshell, i want to truncate a table quickly (no logging) and that call should be from a procedure.

    Environment: DB2 V8 Fixpak 11 on Solaris 8 box

    Thanks in Advance,
    Newbie

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The answer to your question is in the manual.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85

    alter table

    try alter table statement wit ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE option

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Quote Originally Posted by dsusendran
    Environment: DB2 V8 Fixpak 11 on Solaris 8 box
    import/export through admin_cmd is supported in DB2 9 . earlier versions have limited functionality
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  5. #5
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Red face

    baloo99,
    "try alter table statement wit ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE option"

    any error between the ALTER and the next COMMIT will cause the table to be placed in DROP PENDING. It is too dangerous.

    Any other suggestions?

    Thanks rahul_s80, n_i

    Thanks,
    Newbie

  6. #6
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    why you cannot commit immediate after alter table?

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by baloo99
    why you cannot commit immediate after alter table?
    Because that will switch off the NOT LOGGED state.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Here we described another alternative with your own stored procedure: http://www.ibm.com/developerworks/db...ein/index.html
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by n_i
    Because that will switch off the NOT LOGGED state.
    This may not be an issue in the current scenario because the only goal seems to be to truncate the table. But there will always be the possibility that the client application crashes between the CALL and the COMMIT (or network connection is lost or whatever). The only way to prevent that would be to use auto-commit.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Nov 2010
    Posts
    21
    try:

    import from /dev/null of del replace into TABNAME

    regards,
    Harsh Verma

Posting Permissions

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