Uploaded image for project: 'Service Packs and Hot Fixes'
  1. Service Packs and Hot Fixes
  2. MNT-12117

DB2, request modification for DB schema/data changes to be handled using DB stored procedures

    Details

    • Bug Priority:
      Category 3
    • ACT Numbers:

      00092259

      Description

      Customer has been customizing upgrade (to 4.1.x) process to fit with their DB2 implementation restrictions.

      In doing so, the "have come across some sub optimal and problematic implementation choices to mimic stored procedure behaviour in the SQL.

      See the following example
      --ASSIGN:min_tx_ms=min_tx_ms
      SELECT min(commit_time_ms) as min_tx_ms from alf_transaction;

      --FOREACH alf_acl_change_set.id system.upgrade.alf_acl_change_set.batchsize
      UPDATE alf_acl_change_set
      SET
      commit_time_ms = ${min_tx_ms} + id
      WHERE
      id >= ${LOWERBOUND} AND id <= ${UPPERBOUND}
      AND commit_time_ms < ${min_tx_ms}

      This maps to something like

      SELECT min(commit_time_ms) as min_tx_ms from alf_transaction;

      SELECT MAX(id) AS upper_limit FROM alf_acl_change_set;

      UPDATE alf_acl_change_set
      SET
      commit_time_ms = 1364242618346 + id
      WHERE
      id >= 0 AND id <= 9999
      AND commit_time_ms < 1364242618346
      ;

      Executed by a Java method: SchemaBootstrap.executeScriptFile()

      It's quite problematic having what is effectively a stored procedure that should be managed at the database level (not by Java), leveraging/parsing what effectively are comment statements."

      They would like "some feedback on why this is the case? Is it feasible to get an enhancement request raised so that DB schema/data changes are handled using DB stored procedures, not with procedural code wrapped around SQL in such a fashion from Java.

      The benefit of this is that DBA's can manipulate SQL that is their domain of responsibility. This is obviously applicable for larger Enterprises."

        Attachments

          Structure

            Activity

              People

              • Assignee:
                closedissues Closed Issues
                Reporter:
                jsoria Jennie Soria [X] (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Structure Helper Panel