1

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.

  • 1
    You quote the manual for DB2 for LUW, but in the question itself you mention z/OS and iSeries; so, which is it? – mustaccio Dec 17 '16 at 13:08
  • It is only since posting this question that I have gained any understanding of this. It is an iSeries and frankly every bit of their naming is confusing to me. They have very similiar looking pages documenting the various flavors of DB2 whichever comes up on google is what I get so I've been very easily confused by what features are available to me and what documentation applies to whatever I have. Between AS/400, iSeries, z/OS, IBM i... I just want to know how to declare an array in a trigger or use a function in an execute, you know? Short answer - its z/OS and I see I quoted the wrong thing – Sarah Kemp Dec 19 '16 at 17:52
  • IBM i (in its earlier incarnations also known as i Series and AS/400, among other things) has no relation to z/OS. In IBM i the database is essentially a part of the operating system, so strictly speaking it is not DB2, although a lot of syntax and semantics are similar to those of DB2 for LUW and DB2 for z/OS. The reference for EXECUTE here shows that it expects variables in the USING clause. – mustaccio Dec 19 '16 at 18:11
  • I did eventually find the right set of documentation here: ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzintro.htm and I did see the page you linked with the info about using host variables only. I would love to understand the difference between IBM i and z/OS... You don't know where I could find info about the two, do you? I really struggle to find anything about IBM i, presumably because it is so unsearchable. – Sarah Kemp Dec 19 '16 at 18:43
3

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, ...
  • Thank you for the quick answer, do you happen to know where this limitation is coming from? The quote I included gave me hope I could rework this trigger in some way to be considered a compound (compiled) statement, where it says expressions would be allowed in the USING clause. I don't know if it doesn't work because of the DB2 version, the trigger not counting as a compound statement, or some other unknown syntax problem. – Sarah Kemp Dec 16 '16 at 18:35
  • 1
    Well, that's just semantics of dynamic SQL. EXECUTE does not (cannot) calculate an expression; it simply takes a values and copies it to the SQLCA. ibm.com/support/knowledgecenter/SSEPEK_11.0.0/sqlref/src/tpc/… – mustaccio Dec 16 '16 at 18:38
  • I love your edit, that is such a better solution than what I was doing. Thank you for your help! – Sarah Kemp Dec 19 '16 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.