74147

DB2: How to get generated always as statement to work with session user

Question:

I need to get a userstamp into a table and have not managed to figure out how the GENERATED FOR EACH ROW ON UPDATE AS statement works with the SESSION_USER variable in DB2 10.5 (LUW).

Managed to get an implementation working using a function which has a fake variable for forcing the evaluation in update statements:

CREATE OR REPLACE FUNCTION XXX.CURRENT_USER( tmp varchar(128)) SPECIFIC xxx.XXX_CURRENT_USER RETURNS VARCHAR(128) CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION BEGIN RETURN session_user ; END GO CREATE TABLE xxx ( i INTEGER, t VARCHAR(128) GENERATED ALWAYS AS (XXX.CURRENT_USER(i)) )

However, would be nice have less "hacky" implementation for a basic thing like this.

For the time stamps there is that "FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP" statement but no equivalent for other register variables it seems.

Help is very much appreciated

Answer1:

Does this work?

CREATE TABLE xxx ( i INTEGER, t VARCHAR(128) WITH DEFAULT session_user );

I don't have DB2 on hand to check, but this is very similar to the syntax used in other databases (although the more typical syntax does not use WITH).

Recommend

  • Microsoft.Build.Evaluation.Project add folder and file force refresh of project in VS
  • Is a single constant value considered an expression?
  • How can I disable automatic filtering in selectize.js? Built-in / plugin / modilfy source?
  • How to properly trigger lazy load of collection in Entity Framework 5?
  • Avoiding try/catch hell in my web pages
  • Howto count a variable inside of innerHTML?
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • How to distinguish between attribute and element nodes returned from a Saxon XPathSelector
  • How do I control the soft menu button in Honeycomb?
  • Grails Transactions and the Session
  • How to find data from last week in MySQL
  • Change zIndex in HighChart
  • Cythonized function unexpectedly slow
  • Passing parameter through “window.location.href”
  • AWS-SES: Handling Bounces for Invalid ISPs
  • xtable - background colour of added rows
  • Identifier too long in Oracle
  • I am receiving HibernateException “No Hibernate Session bound to thread, and configuration does not
  • How can Delete be both a DDL and a DML statement
  • Eliminate partial duplicate rows from result set
  • pyodbc doesn't report sql server error
  • Swift: Switch statement fallthrough behavior
  • Paperclip, set path outside of rails root folder
  • Shallow update not allowed (git > 1.9)
  • Adding custom controls to a full screen movie
  • Comma separated Values
  • Matrix multiplication with MKL
  • retrieve vertices with no linked edge in arangodb
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • Understanding cpu registers
  • how does django model after text[] in postgresql [duplicate]
  • File not found error Google Drive API
  • Add sale price programmatically to product variations
  • Binding checkboxes to object values in AngularJs
  • Converting MP3 duration time
  • Net Present Value in Excel for Grouped Recurring CF
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?