I work on db2 v 8.01 for AIX.
Is it possible to write dynamic sql query inside the script like in store procedure?
I would like to be able to define variables and execute dynamic queries from within the script - something like:
DECLARE stmt VARCHAR(1000);
DECLARE table_name VARCHAR(50);
SET table_name = 'DEPT_'||deptNumber||'_T';
SET stmt = 'DROP TABLE '||table_name;
PREPARE s1 FROM stmt;
EXECUTE s1;


I can do that from within the store procedure but not in the script.

Please help

Why not just write a shell script?

Create a script with this:
SELECT 'DROP TABLE ' || name FROM sysibm.systables
WHERE name LIKE 'DEPT_%_T'
@

Execute
db2 -td@ -f script > drop.$$

Then
db2 -t -f drop.$$