0

I've got a table with the following:

Columns:
---
URL [nullable, e.g. "https://dba.stackexchange.com/"]
APPFK [nullable, e.g. 654654]
LINKTYPE ["Page", "Link", or "App"]

Check Constraints:
---
(URL IS NULL AND LINKTYPE <> "Link") OR (URL IS NOT NULL AND LINKTYPE = "Link")
(APPFK IS NULL AND LINKTYPE <> "App") OR (APPFK IS NOT NULL AND LINKTYPE = "App")

Since LinkType is calculable based on the nullability of URL and APPFK, I figured this was a perfect opportunity for a computed/generated/virtual column.

So, I tried to run:

alter table MYLIB.MYTABLE
add column testcol VARCHAR(4) generated always as
(
    CASE
        WHEN URL IS NOT NULL THEN 'LINK'
        WHEN APPFK IS NOT NULL THEN 'APP'
        ELSE 'PAGE'
    END
)

Annnd DB2 spat this back at me:

SQL State: 42601 Vendor Code: -104 Message: [SQL0104] Token ( was not valid. Valid tokens: IDENTITY. Cause . . . . . : A syntax error was detected at token (. Token ( is not a valid token. A partial list of valid tokens is IDENTITY. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token (. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.

I tried running the first example in this DB2 documentation, but it gave me the same error.

What's going wrong, here? Am I doing something wrong? Is the documentation wrong? Is it just because our DB2 is outdated (we're running Version 7, Revision 1), and if so is there a workaround?

2 Answers 2

1

Db2 versions for different platforms tend to have different features and statement syntax details, so you should always consult documentation for your exact database version and platform. If you do so, you will see that you can only use a limited selection of expressions for generated columns -- either an identity or a row change timestamp.

Instead of a generated column I suggest you simply create a view with your CASE expression for an extra column.

3
  • view is a good work-a-round. But I don't see there being a "data constancy issue" "only populated on insert" otherwise row change timestamp wouldn't work in 7.2+ and Db2 LUW wouldn't support the OP's original syntax.
    – Charles
    Nov 16, 2020 at 22:33
  • Row change timestamp is a slightly different thing, and a recent addition, despite using the same syntax. But otherwise yes, you're right.
    – mustaccio
    Nov 16, 2020 at 22:38
  • Augh, I was afraid of that. The view's probably not going to fly, unfortunately, since my coworkers assert it's too much complexity to create a view instead of a column. I guess I'll just settle the the check constraints enforcing the data to match.
    – Sarov
    Nov 17, 2020 at 14:27
1

What platform and version of Db2?

The documentation you linked to is for Db2 for Linux/Unix/Windows (aka LUW).

The db2-midrange tag is for Db2 for IBM i, whose CREATE TABLE documentation shows that only the following columns can be generated..

  • identity
  • row change timestamp
  • row-begin
  • row-end column
  • transaction-start-ID
  • generated expression

Note: The last four are relatively new and were basically added to support temporal tables in the most recent version of Db2 for IBM i (7.4)

That last one might seem like what you're trying but digging deeper, an "generated-expression" is one of the following "non-deterministic-expression"

  • DATA CHANGE OPERATION (flag indicating Insert/Update/Delete)
  • special-register
  • built-in-global-variable
3
  • 2
    The OP says "we're running Version 7, Revision 1"
    – mustaccio
    Nov 16, 2020 at 22:29
  • So even upgrading wouldn't give us the generated columns I want, huh? Dang.
    – Sarov
    Nov 17, 2020 at 14:28
  • I'm afraid not, if you really want to store the value...have you considered a before trigger to ensure the value is what your want?
    – Charles
    Nov 17, 2020 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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