DB2 LOAD Utility and Check Pending States

Loading data into a DB2 database using the LOAD utility has a lot of ins and outs. If a DBA has spent a lot of time working in a database without referential integrity or check constraints, then they may forget to check for tables in a check-pending state after loading data. To illustrate the points here, I’ll be using the SAMPLE database and examples you can work through to learn the concepts in detail.

Continue reading »

Example of A Clustering Low-Cardinality Index Helping Query Performance

The request from the developers was something along the lines of “Help, Ember, this query needs to perform better”. Sometimes the query I’m working on is not one that shows up as a problem from the database administrator’s perspective, but one that is especially important in some part of application functioning. In this case, this query is related to the performance of searches done on the website – a particularly problematic area on this client.

Continue reading »

Redesigning Tablespaces in an Existing Database

Like many applications, WebSphere Commerce puts all tables in USERSPACE1 unless they need larger page sizes. This actually works just fine for smaller and midrange implementations, but we have about one build a year that requires something else – either because of standards that client DBAs adhere to or because they actually are busy enough for I/O and separate buffer pools to matter. I recently got the experience to set up a framework and plan for this for a larger client and thought I’d share a few of my methods and thoughts.

Continue reading »

Example of Indexing for a Problem Delete

Go-lives bring all kinds of opportunities to find problem SQL. A new site going live or a new site design going live can put a completely different load of SQL on a database. Depending on the level of load testing done, there can be some problems still to find. This post is an example of just one problem SQL statement (in this case, a delete) found and mitigated through database level analysis.

Continue reading »