Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    72

    Unanswered: truncate stored procedure

    Hi All,
    i am trying to create a stored porcedure for truncating a table which i got from IBM website .stored procedure gets created but doesnt get executed here is the syntax of the stored procedure

    Database - DB2 UDB ENTERPISE SERVER EDITION 8.2.3
    O/S - IBM AIX 5.3
    CREATE PROCEDURE truncate(IN sch_name VARCHAR(130),IN tab_name VARCHAR(130))
    LANGUAGE SQL
    ---------------------------------
    -- SQL Stored Procedure truncate
    ---------------------------------
    BEGIN

    DECLARE stmt VARCHAR(1000);
    DECLARE param VARCHAR(1000);
    DECLARE full_name VARCHAR(1000);
    DECLARE a VARCHAR(130);

    IF sch_name IS NULL
    THEN
    SET full_name = tab_name;

    -- Check whether the table exists or not
    SELECT tabname INTO a
    FROM SYSCAT.TABLES
    WHERE tabname = UCASE(tab_name);

    ELSE
    SET full_name = sch_name||'.'||tab_name;

    -- Check whether the table exists or not
    SELECT tabname INTO a
    FROM SYSCAT.TABLES
    WHERE tabname = UCASE(tab_name) AND tabschema = UCASE(sch_name);

    END IF;

    IF UCASE(a) = UCASE(tab_name)
    THEN
    -- Uncomment one of the following statements depending on the
    -- platform on which the sample is run.
    SET param = 'IMPORT FROM /dev/null OF DEL REPLACE INTO '||full_name;

    SET stmt = 'CALL SYSPROC.ADMIN_CMD (?)';

    PREPARE s1 FROM stmt;
    EXECUTE s1 USING param;

    ELSE

    -- Table does not exists.

    END IF;

    END @


    when i run this procedure it gives me following error using
    Call dsedw.truncate(NULL,'X') where x is table name
    SQL0104N-an expected token import was found following "begin of statement"
    expected token may include describe .

    if i gave schema name instead of using null then also the error remains the same .

    appreciate your response.

    Thanks & Regards,

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by oracle10gsingh
    Hi All,
    i am trying to create a stored porcedure for truncating a table which i got from IBM website .stored procedure gets created but doesnt get executed here is the syntax of the stored procedure

    Database - DB2 UDB ENTERPISE SERVER EDITION 8.2.3
    O/S - IBM AIX 5.3
    CREATE PROCEDURE truncate(IN sch_name VARCHAR(130),IN tab_name VARCHAR(130))
    LANGUAGE SQL
    ---------------------------------
    -- SQL Stored Procedure truncate
    ---------------------------------
    BEGIN

    DECLARE stmt VARCHAR(1000);
    DECLARE param VARCHAR(1000);
    DECLARE full_name VARCHAR(1000);
    DECLARE a VARCHAR(130);

    IF sch_name IS NULL
    THEN
    SET full_name = tab_name;

    -- Check whether the table exists or not
    SELECT tabname INTO a
    FROM SYSCAT.TABLES
    WHERE tabname = UCASE(tab_name);
    You are aware that the UCASE screws things up if you have delimited schema/table names? Therefore, I wouldn't do that in such cases.

    ELSE
    SET full_name = sch_name||'.'||tab_name;

    -- Check whether the table exists or not
    SELECT tabname INTO a
    FROM SYSCAT.TABLES
    WHERE tabname = UCASE(tab_name) AND tabschema = UCASE(sch_name);
    Same here. Also, I would simplify the logic and use the CURRENT SCHEMA special register instead - or combine both table-existence checks into a single query dealing with the case where "sch_name" is NULL:
    Code:
    SELECT ...
    FROM   ...
    WHERE ... AND ( tabschema = UCASE(sch_name) OR sch_name IS NULL )
    With this, you only have to consider the branching for the SET statement - and even that could simply be handled with a CASE expression.

    END IF;

    IF UCASE(a) = UCASE(tab_name)
    THEN
    -- Uncomment one of the following statements depending on the
    -- platform on which the sample is run.
    SET param = 'IMPORT FROM /dev/null OF DEL REPLACE INTO '||full_name;
    Yet another approach (much simpler, too!) would be to just ignore the existence check completely. Just construct the IMPORT command, inject the schema/table name (including NULL handling for schema names), and then execute the command. IMPORT itself will check the existence of the table anyway and return an error if the table cannot be found. The reason for deferring to IMPORT is also that there are other checks required like verifying that the caller actually has all the necessary privileges for the import operation.

    SET stmt = 'CALL SYSPROC.ADMIN_CMD (?)';

    PREPARE s1 FROM stmt;
    EXECUTE s1 USING param;

    ELSE

    -- Table does not exists.

    END IF;

    END @


    when i run this procedure it gives me following error using
    Call dsedw.truncate(NULL,'X') where x is table name
    SQL0104N-an expected token import was found following "begin of statement"
    expected token may include describe .
    You are using the ADMIN_CMD procedure. In DB2 LUW V8, this procedure did not support the IMPORT command. http://publib.boulder.ibm.com/infoce...n/r0012547.htm So you have to move to version 9 (where it is supported), or you use an external procedure that invokes the import/load command like described here: http://www.ibm.com/developerworks/db...ein/index.html
    Last edited by stolze; 02-14-08 at 11:19.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2007
    Posts
    72
    hi ,,

    i am trying to execute the truncate stored procedure which is writtten in C from IBM website for DB2 UDB V8 .here is the URL( http://www.ibm.com/developerworks/db...ein/index.html) given to me by stolze .
    i have a XL compiler in my machine and i precompiling the functions.sqc using the db2 compile successfully but i compiling using the xl compiler i am not able to do that .

    Help me i just my truncate stored procedure to work fine rest of the stored procedures mentioned in this URL is not required to me .

    Regards,

Posting Permissions

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