Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: UDF begin or begin atomic?

    I'm in trouble. I've created a new UDF for my client and I used a test-server which was already upgraded to v9.7.2. to develop & test this thing. In the coming period production will continue to run on v9.5.5.
    Being a former mainframe-cobol programmer I was very happy with the "select into" syntax and I used that a lot. DB2 allows you to use this syntax when you code a "begin" instead of a "begin atomic". Fair enough, no problem.

    Now I need to roll-out my new UDF to the first V9.5 test server and it fails The 9.5 server does not accept a "begin" without the atomic keyword and when you code atomic the "select into" syntax is not accepted.
    I know that the v9.7 compilers were changed due to the oracle-compatibility, but I use the "good-old" IBM-syntax.

    Did I miss something?
    Is this change announced?
    Can this be solved without code changes?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Don't know about DB2 for z/OS, but on what is called "distributed platforms" DB2 9.7 offers two types of functions: inlined (those with BEGIN ATOMIC) and compiled (those with just BEGIN). DB2 9.5 only knows one type of functions, which are equivalent to inlined. The range of SQL statements supported in an inlined (or dynamic) context is limited; SELECT .. INTO is not among them. This is well documented in the manuals for the corresponding DB2 versions.

    You may want to consider using the SET statement:

    SET myVar = (SELECT myCol FROM ... WHERE ...);

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dr_te_z View Post
    I know that the v9.7 compilers were changed due to the oracle-compatibility, but I use the "good-old" IBM-syntax.
    Are you sure that all versions of DB2 z/OS support "SELECT INTO" for UDF's?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Marcus_A View Post
    Are you sure that all versions of DB2 z/OS support "SELECT INTO" for UDF's?
    No no, I was refering to my former life in which I coded COBOL with inline SQL. I never coded native SP's in z.

    b.t.w. Serge Rielau was kind enough to respond:

    Db2 9.7 introduced the concept of "compiled" SQL UDF (and triggers).
    A compiled SQL UDf is characterized by BEGIN .. END.
    Within such a UDF you can use (with few semantic exception) the whole set of SQL PL and SQL statements.

    BEGIN ATOMIC .. END is "inline" and hence macro expanded and thus more limitted in what it can do.

    Now in your case replace SELECT c1, c2 INTO a, b FROM T with
    SET (a, b) = (SELECT c1, c2 FORM T);
    That is allowed in inline SQL UDF and shoudl have teh same semantics with one exception:
    SELECT INTO will not change the target variables if no row is found.
    SET will assign NULLs

    Cheers
    Serge
    Last edited by dr_te_z; 12-16-10 at 17:46. Reason: added answer from Serge

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •