Application first database second

“Application first database second” said the scrum master. I had an interesting conversation with a colleague about the philosophy underpinning his development methodology. We were discussing the merits of solid data modelling underpinning the agile method. I’d been considering this topic in Agile DBA His criticism of spending an excessive time creating the ideal data model – impinged upon the project deadlines. Data Architects were spending so much time gathering and analysing requirements , they weren’t able to shift product and new functionality to customers. His mantra of “Application first database second” focused his developers on developing quickly – usually... Read more →

DB2 Tuning Toolkit – DB2 Design advisor - Ddb2advis

One of the DBA objectives is to decrease the query cost. Decreasing the query cost – improves overall performance. The db2advis recommends indexes for a query. Read the post - DB2 Tuning Toolkit – db2expln - to analyse the accesspath for a specific query. A syntax and process example: Step 1 – Create the source file Put a query in a input file called index.sql. This is just a sample query select SUBSTR(TBNAME,1,40), SUBSTR(TBCREATOR,1,10), substr(name,1,30), SUBSTR(CREATOR,1,8),substr(colnames,1,60), firstkeycard, fullkeycard, sequential_pages, density, iid, uniquerule, stats_time, colnames from sysibm.sysindexes a ORDER BY tbcreator, TBNAME, NAME; Step 2 Execute the db2advis command db2advis -d... Read more →

DB2 Query Tuning – db2expln

Db2expln analyses the accesspath for a specific DB2 query. Use db2expln for DB2 query tuning. Once you've identified a slow running query causing a bottleneck - use this process to analyse the access path. This might be part of of an overall Performace Tuning process A syntax example: db2expln -d MYDB -f myquery.sql -z \; -g -o explain.txt The db2explain output for my query is Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = No SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "DB2MY" Statement: select SUBSTR(TBNAME, 1,... Read more →

DB2 DB2_RR_TO_RS environment variable

The Environment Variable DB2_RR_TO_RS enables\disables the Repeatable Read attribute. I's enabled for DB2 performance purposes DB2 registry parameter DB2_RR_TO_RS is used to: Set a default isolation level for user tables to Read Stability (RS) isolation level. The RS level is not dissimilar to the Repeatable Read (RR) , with one difference – a page or row retrieved remains locked to the UOW end , but if another application can insert a value , that will be read if your process access the row a second time . Reduce locking the next key for inserted or changed rows Reduce deadlocks when... Read more →

Indexes in DB2

How to create a recordset of indexes in DB2 for a table? ---To list all indexes for a schema and table db2 "Select indschema,indname,uniquerule,colnames from syscat.indexes where tabschema = '' and tabname='' order by indname" Author: Jack Vamvas(http://www.dba-db2.com) Read more →