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.
Originally Posted by oracle10gsingh
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:
SET full_name = sch_name||'.'||tab_name;
-- Check whether the table exists or not
SELECT tabname INTO a
WHERE tabname = UCASE(tab_name) AND tabschema = UCASE(sch_name);
With this, you only have to consider the branching for the SET statement - and even that could simply be handled with a CASE expression.
WHERE ... AND ( tabschema = UCASE(sch_name) OR sch_name IS NULL )
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.
IF UCASE(a) = UCASE(tab_name)
-- 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;
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
SET stmt = 'CALL SYSPROC.ADMIN_CMD (?)';
PREPARE s1 FROM stmt;
EXECUTE s1 USING param;
-- Table does not exists.
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 .
Last edited by stolze; 02-14-08 at 11:19.
IBM DB2 Analytics Accelerator
IBM Germany Research & Development