Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 27
Isolation, savepoints, access paths, and platform context
A compact Db2 mental model built from cross-engine experiments in concurrency, physical row identity, plan control, and cloud deployment.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
Db2 Cursor Stability is a read-committed isolation model with a specific locking history: a cursor protects the row at its current position while previously read rows may change after it advances.
02
A savepoint creates a rollback boundary inside a transaction without publishing changes to other sessions. Rolling back to it preserves earlier work while releasing or retaining resources according to engine rules.
03
Db2 RID concepts belong beside Oracle ROWID, PostgreSQL CTID, and other physical locators: useful for execution and diagnosis, but unsafe as permanent application identity.
04
Db2 optimization profiles and related plan controls address a problem shared with Oracle baselines and other hint mechanisms: stabilizing execution while the underlying cost model or schema is repaired.
05
Managed Db2 offerings package engine, infrastructure, administration, and service limits differently. Product evaluation must separate SQL behavior from the responsibilities transferred to the provider.
06
A useful Db2 comparison reproduces the same schema, transactions, predicates, and concurrent timing on each engine, then explains differences through documented isolation and storage behavior.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
A concurrency test should record when locks are acquired and released as a cursor advances, then compare the observed anomaly boundary with other isolation levels.
-- Session A
SET CURRENT ISOLATION CS;
SELECT id, balance FROM account ORDER BY id;
-- Keep the cursor open and advance it while Session B updates rows.Rollback to a savepoint undoes work after the marker while preserving the outer transaction. Verify lock and cursor behavior for the deployed Db2 release.
SAVEPOINT before_batch ON ROLLBACK RETAIN CURSORS;
UPDATE account SET balance = balance - 10 WHERE id = 1;
ROLLBACK TO SAVEPOINT before_batch;
COMMIT;Db2 explain tables preserve optimizer choices and operator estimates. Capture statement text, schema, statistics timestamp, and configuration with the plan.
CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,CURRENT USER);
EXPLAIN PLAN FOR SELECT * FROM account WHERE customer_id = 42;
-- Format with db2exfmt for the current explain schema.Edition, service level, compatibility settings, registry variables, and database configuration can explain behavior that SQL text alone cannot.
SELECT service_level, fixpack_num FROM TABLE(sysproc.env_get_inst_info());
SELECT name, value FROM sysibmadm.dbcfg
WHERE name IN ('cur_commit','locktimeout','logarchmeth1');08
Optional deep dives with the complete experiments and product-version context behind this guide.