A DB2 wishlist

After nearly five years of working with DB2 on Linux, Unix, and Windows ("LUW"), there are a number of features that I miss in the DBMS, and some annoyances that I'd like the product to get rid of. Some of these items are purely DB2 specific, others are applicable to other DBMSes, as well.

Annoyances which should be removed

• Platform-independence is often given as a market advantage for DB2. However, it's highly questionable how platform-independent it really is, given the many restrictions there are on where backups can be restored. E.g., it's not possible to have your production-DB2 be an AIX or a Linux server, and then ship backups from production to developers using DB2 on Windows. So the lack of platform-independent backup/restore is my number one DB2 annoyance.

  When the subject is brought up, someone may point you to db2look+db2move, but restoring a database using these tools is not a substitute for having platform-independent backup/restore, because:

  • db2move doesn't handle things well if generated columns/identity columns are involved.
  • db2look will sometimes create SQL in the wrong order, so that one has to manually re-arrange the order of the resulting SQL, in order to be able to run the generated SQL.
  • Not all datatypes seem to be supported by db2move; last time I tried, geospatial types were not supported, for example.
  • db2look+db2move involves more steps and more time than is reasonable
• Why doesn't DB2 provide a way to indicate that a database should automatically be activated at instance start-time?
• Why are there so many restrictions on naming? E.g.: In the year of 2017, database names should be able to be very long. The same with node names. And why are certain innocent characters like "-" illegal in several contexts? The result is that it's hard to maintain a systematic naming system (which could be that the node name matches the unqualified or fully-qualified DNS name, for example).
• Information Center annoyance: It doesn't make it clear enough that some features require (potentially very expensive) extra licensing. E.g., it's not documented (or at least very well-hidden) that federation of tables on an MSSQL server requires special licensing.
• Installing a local version of the DB2 documentation takes too much effort. There's an RFE for PDF versions of the information center; that could be a way to provide easily available offline documentation.

Features which would be nice to have

• Referential joins: This is not a DB2-specific wish, but perhaps DB2 could be a pioneer. By referential join, I mean a safer alternative to natural joins which exist in some DBMSes: Given two tables:

      CREATE TABLE parent (parent_x INT NOT NULL PRIMARY KEY)
      and
      CREATE TABLE child (parent_x INT NOT NULL REFERENCES parent(parent_x), some_attribute VARCHAR(100) NOT NULL)

  it should be able to state:
      SELECT * FROM parent REFERENTIAL JOIN child
  or (as a short-hand):
      SELECT * FROM parent REFJOIN child
  and the statements would implicitly join on the column(s) which have already been declared as making up the foreign key.

  This would be sort-of using a natural join in other DBMSes:
      SELECT * FROM parent NATURAL JOIN child
  —only safer, because the columns joined on wouldn't rely on naming conventions.

  Referential joins should only be allowed for tables which are related through foreign key constraints.

  Referential joins could (like natural joins) allow for substantially shorter query statements.

• IBM Data Studio, add a full-featured web-frontend to DB2, like Oracle's web-based Enterprise Manager. Of course, the web-interface should be carefully and systematically written in a way which prevents cross-site scripting exploits, etc. Web-based applications are so much more useful, as they don't require the hazzle of deployment and updates on workstations.
• Add a way to specify that a stored procedure should be considered as dynamic SQL, so that one doesn't have to worry about antiquated access plans lying around after significant changes to involved base tables.
• Access to creating views on base tables owned by another user, as long as the view-creator has access to the table at group level (and CREATEIN privileges at the schema level).
• CREATE OR REPLACE does not work at a group level, and that should be fixed: If a user creates a view, then another user who could also have created the same view (due to common group membership) should be allowed to run CREATE OR REPLACE.
• One should be able to define views and routines in a way where security is evaluated at invocation time instead of at definition time. For inspiration, see PostgreSQL's SECURITY INVOKER/SECURITY DEFINER. Purpose: That way, code and statements may be chunked up into reusable pieces of functionality which are easily composable and reusable. It may be a bit slower to invoke, but for data warehouse environments, this would be perfectly acceptable.
• Less dangerous LOADs: Currently, one has choose between rather slow IMPORTs (which consist of numerous INSERT statements) or LOADs which may bring whole tablespaces into jeopardy if used incorrectly. Something inbetween is needed, like a LOAD where UNRECOVERABLE is implicit, and in case it fails, then the table is automatically recreated (instead of a table being left behind in an unusable state).
• Online backup have too much collission potential, leading to nasty lock situations in a shared datawarehouse environment. LOADs should not conflict with online backups, for example.
• The documentation about active directory like authentication/authorization integration is schizophrenic and unclear: Which is better, the OS-based mechanism (DB2AUTH=OSAUTHDB), or the LDAP plugin-modules (governed by IBMLDAPSecurity.ini)? (SR 29353,040,678). My experience is that the latter seems to be on the way out and does not seem to play well with newer AD setups where the LDAP security policy has been cranked up to a high level.