Just as a shortcut, you can use
DECLARE STMT varchar(5000);
SET STMT = 'CREATE TABLE ' || TableParm || ...
EXECUTE IMMEDIATE stmt;
to cut out an entire SQL statement. Personally I only use prepare and execute as two statements if I am going to re-issue the dynamic statement or have parameter markers in the statement.
For insert and update you use either execute immediate or prepare and execute. If you are going to be issuing either insert or update more than once you should probably use parameter markers, prepare once and execute often.
For a cursor use something like:
DECLARE SEL_STMT varchar(5000);
DECLARE C1 CURSOR FOR S1;
SET STMT = 'SELECT ... ';
PREPARE S1 FROM SEL_STMT;
FETCH C1 INTO ...;
Just to expand your expertise: When DB2 is preparing your statement it doesn't know what datatypes you are going to be presenting. Therefore it generates a generic access path - you'll have to do an explain to determine just how it handles easch statement. But as an example if col01 is in an index DB2 might end up doing an index scan rather than an index lookup.
You can give DB2 a boost by using a typed parameter marker:
stmt = 'SELECT ? FROM table WHERE col01 = cast(? as varchar(10))';
for example - where the datatype in the cast is the datatype of col01. "This notation is not a function call, but a "promise" that the type of the parameter at run time will be of the data type specified or some data type that can be converted to the specified data type."
This might give DB2 the information it needs to give you a better access path.
Thanks for all of your help, James. BTW, do you know if there is a limitation on the # of input parameters that can be passed in one statement? I have a statement in particular that will require about 13 parameters.
I'm surprised there isn't an easier (excuse my laziness ) to build a dynamic query while doing string comparisons. Now I wish all of my comparisons were based around numbers.