DROP TABLE (if exist that is) anybody ?
Patrick Bielen
How can you check if a table exists before running the drop table command ?
The reason why i ask is because i'm creating queries with the operation
navigator tool, but it breaks when the table does not exists.
for example...
DROP TABLE ARTIKELEN.OMZET2004;
CREATE TABLE ARTIKELEN.OMZET2004
AS (SELECT ARNR33, ORAT33
FROM ODLIBF46L.AROM
WHERE FMKD33 = 'STF' AND MGNR33 = 'MHZ'
AND JAAR33 = 2004 AND ORAT33 > 0
ORDER BY ARNR33) WITH DEFINITION;
So the table should been dropped before it is re-created,
but the query breaks when the table is already deleted, so
i would like to check for the table before doing the drop.
On other databases you can use exist keyword, but seems
the SQL on the OPNAV-tool does not support it.
Any ideas or tips ?
In java or jython it's not a problem, cause i can use try-catch
statements there, but i do not know if something like that exists
when using SQL ?
Best Regards,
Patrick
Jonathan Ball
From the semi-colons at the end of your statements, it
appears you're doing this in the Navigator SQL Script
processor. That's good. Put your statements in a SQL
stored procedure, and add some error handling:
CREATE PROCEDURE BLD_TABLE LANGUAGE SQL
BEGIN
DECLARE v_sqlcode INT;
DECLARE CONTINUE HANDLER FOR
SQLSTATE '01532'
SQLSTATE '42704'
SET v_sqlcode = SQLCODE;
DROP TABLE ARTIKELEN.OMZET2004;
CREATE TABLE ARTIKELEN.OMZET2004
AS (SELECT ARNR33, ORAT33
FROM ODLIBF46L.AROM
WHERE FMKD33 = 'STF' AND MGNR33 = 'MHZ'
AND JAAR33 = 2004 AND ORAT33 > 0
ORDER BY ARNR33) WITH DEFINITION;
END
You can then either call the procedure from the SQL
script window with a SQL call statement
call bld_table;
or you can call it from a CL command line, or from
within another HLL program. If you call it as an
ordinary OS/400 program object rather than as a SQL
procedure call, be sure to specify a 1-byte parameter
for a return code (that you don't need to examine), or
else you'll get a pointer-not-found error; e.g.,
call pgm(bld_table) parm(' ')
But if you do a SQL call, you don't need to specify the
return code parameter.
Look for the iSeries Redbook "Stored Procedures,
Triggers and User Defined Functions" on the IBM iSeries
information center website for more information on
error handling.
As an alternative, you could write a CL program that
- receives the file name as a parameter
- does a Delete File (DLTF) command on the file
- monitors for object-not-found with MONMSG
Then:
- register the CL program with DB2 as an external
stored procedure
- do a SQL CALL statement to the drop-file stored
procedure before issuing your CREATE TABLE
statement
Patrick Bielen
Cool, this works, and indeed i first get the parameter-error :-)
But tell me, can that parameter also be used to pass a parameter
for a value that needs to be selected ?
Like for example
parameter year
SELECT * FROM ARBS WHERE YEAR = year
??? is that possible ?
Best Regards
Patrick