EXECUTE stmt USING expression

Question

I am writing an INSTEAD OF trigger on a view. I need to split a string into 4 40 character long parts to insert into separate columns. I want to use substr() to break the variable up into these parts, but when I try to create the trigger I get the error:

SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable SUBSTRING not found.
Cause . . . . . : SUBSTRING was not found as a column of table *N in *N and was not found as a global variable in *N. If the table is *N, SUBSTRING is not a column of any table or view that can be referenced.
Recovery . . . : Do one of the following and try the request again:
-- Ensure that the column and table names are specified correctly in the statement.
-- If this is a SELECT statement, ensure that all the required tables were named in the FROM clause.
-- If the column was intended to be a correlated reference, qualify the column with the correct table designator.
-- If the column was intended to be a global variable, qualify the name with the schema where the global variable exists or ensure the schema is in the path.

This page indicates that I can use an expression in my USING clause as long as it is within a compound SQL statement. As I understand it, the SQL between BEGIN and END in a trigger would be considered a compound SQL statement.

"An expression other than host-variable can only be used when the EXECUTE statement is used within a compound SQL (compiled) statement."

CREATE OR REPLACE TRIGGER MYLIB.INFO_INSERT
INSTEAD OF INSERT ON MYLIB.INFO
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN
    DECLARE... 
    ...
    set insert_into_info = 'INSERT INTO MYLIB.' || info || ' (
      CICUST, CIINF1, CIINF2, CIINF3, CIINF4
    ) VALUES (' || NEW_ID || ', ?, ?, ?, ? )';

    PREPARE s1 from insert_into_info;
    EXECUTE s1 using substring(paddedComments, 1, 40), substring(paddedComments, 41, 80), 
        substring(paddedComments, 81, 120), substring(paddedComments, 121, 160);
END;

I have found examples for Oracle of using functions in the USING clause, but no iSeries or DB2 examples, although there are enough hints in the descriptions of the EXECUTE statement to keep me hoping. I am running z/OS 7.2 if that is relevant.

I've been searching around but haven't managed to figure this out; can anyone get me going in the right direction? I did also try to do this with an array so I wouldn't have to break up the string at the end, but I could not manage to get an array type declared and used within this trigger either.

Answer 1

EXECUTE ... USING requires host variable names, not expressions, while SUBSTRING(whatever) is an expression. You will need to declare four host variables, assign results of the SUBSTRING(...) expressions to them, then use those variables in the EXECUTE statement.

Alternatively, you could move expressions to the dynamic statement itself:

set insert_into_info = 'INSERT INTO MYLIB.' || info || ' (
      CICUST, CIINF1, CIINF2, CIINF3, CIINF4, CISAD1, CISAD2,
      CISCTY, CISSTA, CISZIP, CISCRY
    ) VALUES (' || NEW_ID || ', substring(?, 1, 40), ' ||
    'substring(?, 41, 80)' || ... 
...
EXECUTE s1 using paddedComments, paddedComments, ...