-
Type:
Improvement
-
Status: Closed
-
Resolution: Won't Fix
-
Affects Version/s: 4.1, 4.2
-
Fix Version/s: None
-
Component/s: Database and Persistence, Upgrades
-
Labels:
-
Environment:4.1.1v,RHEL,Tomcat,DB2
-
Bug Priority:
-
ACT Numbers:
00092259
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."