Basic 2010 Database Programming

Getting Your Feet Wet With DB2 2010

DB2 2010 isn't a single product you just install and walk away from. It's a family of database engines, and the experience you have depends entirely on which flavor you're running — LUW (Linux, Unix, Windows), z/OS, or the Express-C edition. Most people starting out hit the LUW version, so that's where I'll focus. I've been wrestling with these systems for years, and the learning curve is steeper than people expect because the tooling assumes you already know SQL at a professional level. When people say "basic database programming" with DB2 2010, they usually mean writing SQL statements, stored procedures in SQL PL or Java, and connecting to the database through JDBC or ODBC. It sounds straightforward until you realize DB2 does things differently than SQL Server or MySQL, and those differences will bite you if you don't catch them early. Start with the command line processor, also known as db2cmd. You open a terminal, run db2 connect to your_database, and you're in. From there, you can execute SQL directly. It's the fastest way to learn because you see results immediately. I learned more about DB2's behavior in one afternoon of random queries than I did reading the documentation for two weeks.

The database object model is schema-based by default. Every table, view, and procedure lives inside a schema, and if you don't specify one, it goes into the current schema associated with your authorization ID. That's different from MySQL, where everything just sits in the database. You'll create schemas with CREATE SCHEMA myschema AUTHORIZATION db2admin, and then all your work happens inside that namespace.

Tables, Columns, and the Integer Trap

Creating a table in DB2 2010 follows standard SQL syntax, but there are gotchas. The data types have subtle differences. For example, DB2 has SMALLINT, INT, and BIGINT, but it also has DECIMAL and NUMERIC as distinct types, whereas some databases treat them as identical. If you're migrating code from another platform, watch for that. Here's what a basic table definition looks like: CREATE TABLE employees ( emp_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, hire_date DATE DEFAULT CURRENT DATE, salary DECIMAL(10,2) )

The GENERATED ALWAYS AS IDENTITY clause is DB2's way of handling auto-increment columns. It's clean and efficient, but unlike MySQL's AUTO_INCREMENT, you can't easily reset it. If you delete all rows and want to restart the sequence at 1, you need to issue an ALTER TABLE ... ALTER COLUMN ... RESTART WITH 1 statement. I learned that the hard way on a development database when someone dropped and recreated a table three times in a week and the IDs ended up at 47,000 something. Indexes in DB2 work similarly to other databases, but the optimizer behaves differently. DB2 uses a cost-based optimizer that considers statistics heavily. If you create a table and start querying it without running RUNSTATS, the optimizer is essentially guessing. On a fresh database with no statistics, queries against even small tables can take noticeably longer than they should. Run RUNSTATS ON TABLE your_schema.your_table WITH DISTRIBUTION AND DETAILED INDEXES ALL after any significant data load, and you'll see query performance improve dramatically. This isn't optional maintenance — it's required if you want predictable performance.

Stored Procedures: Where Things Get Real

DB2 2010 supports stored procedures written in SQL PL, which is DB2's own procedural extension to SQL. It's not T-SQL, and it's not PL/SQL. The syntax borrows from both but has its own quirks. A basic procedure that inserts a row and returns the generated ID: CREATE PROCEDURE add_employee ( IN p_first_name VARCHAR(50), IN p_last_name VARCHAR(50), IN p_salary DECIMAL(10,2), OUT p_emp_id INTEGER ) SPECIFIC add_employee_detailed LANGUAGE SQL BEGIN ATOMIC INSERT INTO employees (first_name, last_name, salary) VALUES (p_first_name, p_last_name, p_salary) SET p_emp_id = IDENTITY_VAL_LOCAL(); END

The IDENTITY_VAL_LOCAL() function is important here. It returns the most recent identity value generated for the current connection. If you use FINAL GET DIAGNOSTICS instead, you'll get the same result, but IDENTITY_VAL_LOCAL is simpler and less error-prone. I've seen people use both in the same codebase and wonder why they get different results when multiple connections are active simultaneously. Calling the procedure looks like this: CALL add_employee('John', 'Doe', 75000.00, ?)

The question mark is the placeholder for the output parameter. You bind to it from your application code.

JDBC Connections and the Driver Situation

For application-level programming, JDBC is the standard approach. DB2 2010 ships with its own JDBC driver — the Type 4 pure Java driver. You'll need the db2jcc4.jar file on your classpath. The connection string format is: jdbc:db2://hostname:50000/database_name Replace 50000 with whatever port your DB2 instance is listening on. The default for LUW is 50000, but if the database administrator changed it, you'll need to find out what it actually is. There's no way to connect without knowing the port, and unlike some databases, DB2 doesn't have a well-known service discovery mechanism that works out of the box.

One thing that trips people up: DB2's JDBC driver uses prepareCall for stored procedures with output parameters, not prepareStatement. If you use the wrong method, you'll get cryptic errors about missing parameter markers. Here's the correct pattern: CallableStatement cs = conn.prepareCall("{? = CALL add_employee(?, ?, ?)}"); cs.registerOutParameter(1, Types.INTEGER); cs.setString(2, "Jane"); cs.setString(3, "Smith"); cs.setDecimal(4, new java.math.BigDecimal("82000.00")); cs.execute(); int newId = cs.getInt(1); This works, but notice the syntax difference. The call string uses {? = CALL ...} because DB2 treats the output parameter as a return value in this context. Other databases might use {CALL ...} with separate registerOutParameter calls. Don't mix up the two patterns.

A Problem I Actually Faced

There was a project where we were loading about 2 million rows into a DB2 2010 table using bulk inserts through JDBC. The insert loop was straightforward — batch them in groups of 500, let JDBC handle the batching. Everything worked fine for the first 800,000 rows, and then the connection just hung. No exception, no timeout, nothing. The process was stuck but not dead. We watched it for 45 minutes and it didn't move. The issue turned out to be a lock escalation. DB2 was holding row-level locks on the inserted data while waiting for the transaction to complete, and somewhere in the middle of the load, the lock manager decided to escalate from row locks to a table lock. But the escalation was happening on a table that was also being queried by another process, and neither side would give way. The deadlock detection kicked in but chose the wrong victim because of how the statistics were configured. The fix wasn't elegant. We switched from individual batched inserts to DB2's LOAD utility, which bypasses the transaction log entirely for the initial load phase. The command was simple:

LOAD FROM employee_data.del OF DEL INSERT INTO employees NONRECOVERABLE The NONRECOVERABLE option means the loaded data isn't protected by the transaction log, which is fine for a one-time bulk load into a development table. After the load completed, we ran RUNSTATS and the entire operation that had been hanging finished in about 12 minutes. The JDBC batch approach would have taken hours, assuming it ever completed at all.

Triggers and the Silent Failure Mode

Triggers in DB2 2010 are powerful but unforgiving. If a trigger fails, the entire transaction that fired it rolls back. This includes any changes made by the statement that activated the trigger, plus any changes already made in the same transaction. There's no partial rollback. If your trigger has a bug and throws an exception, you lose everything in that transaction, not just the trigger's changes. I once wrote a trigger that checked whether a salary value was within an acceptable range. The check logic was correct, but I accidentally referenced a column name that didn't exist yet because I was deploying the trigger before the column was added. The trigger compiled fine — DB2 validates trigger source code at creation time, not at reference resolution time in this case — but when the first insert fired, the entire transaction rolled back with a SQLCODE of -438. The error message was technically accurate but not helpful for someone who hadn't seen that specific code before. The workaround is to test triggers in isolation before attaching them to production tables. Create a test table, add the trigger, insert a few rows, and verify the behavior. Then move it. It adds time upfront but saves hours of debugging later.

Backup and Recovery Basics

DB2 2010 supports several backup strategies. The simplest is a offline backup, which requires taking the database out of service: db2 backup database your_database For online backups, where the database stays available, you need to enable archive log mode first. That's a configuration change that affects the entire instance:

db2 update db cfg for your_database using LOGARCHMETH1 DISK:/path/to/archive Once that's set, you can run db2 backup database your_database online and the database remains operational. The backup goes to the archive log directory you specified. This is critical for production systems where downtime isn't an option, but it does add complexity to your storage planning because the archive log directory can grow quickly if you're not rotating old logs. If you skip enabling archive log mode and try to do an online backup, DB2 will refuse with a clear error. I've seen this happen because the database administrator assumed the setting was already enabled from a previous configuration. Always verify with db2 get db cfg for your_database before attempting an online backup.

What DB2 2010 Does Poorly

Let me be direct about the limitations. DB2 2010's administrative tooling, particularly DB2 Control Center and the older Command Editor, is slow and unstable compared to what you get with competing platforms. The GUI tools crash frequently, especially when dealing with large schemas that have hundreds of tables. Don't rely on them. Use the command line and script everything. Another problem: DB2 2010 doesn't support some SQL features that newer versions of PostgreSQL and MySQL have had for years. Window functions were introduced in DB2 9.7, so if you're on an earlier 2010-era fix pack, you might be working without LATERAL joins, MERGE statements in some configurations, or even proper FETCH FIRST syntax depending on your fix pack level. Check your fix pack before you design around features that might not exist. The licensing cost is also a significant factor. DB2 Express-C is free for development and small production use, but it has a 4-core CPU limit and 16 GB of memory limit. If your application grows beyond that, you're looking at commercial licensing that can be expensive. For a small team or startup, this is a real constraint that affects architecture decisions. PostgreSQL handles this scenario more gracefully if cost is a concern.

Migration Considerations

If you're moving from another database to DB2 2010, don't assume your SQL will work as-is. Even standard SQL constructs behave differently. The SUBSTRING function, CONCAT, date arithmetic, and even basic comparison operators can have different semantics. Write a migration test suite that compares results row-by-row between the source and target databases. Automated diff tools catch differences that manual inspection misses. The most common failure point is around character set handling. DB2 supports various codesets, and if your source database uses UTF-8 while your DB2 database uses a single-byte codeset, you'll lose data on insertion. Verify the codeset on both sides before migrating any data. Use db2 get db cfg and look for the Database codeset and Database territory values.

Learning Resources That Actually Help

The IBM Knowledge Center is the official documentation, and it's comprehensive but not always easy to navigate. The Redbooks from IBM are better structured for learning — search for "IBM DB2 10 for Linux, Unix, and Windows Administration" even though it says 10, much of the foundational material applies to the 2010-era 9.7 codebase. For hands-on practice, the Express-C edition is free and downloadable from IBM's website. Install it locally, create a database, and start writing procedures. The best way to learn DB2 is to break things in a development environment and watch what happens when you do. Stack Overflow has a decent collection of DB2-specific questions, but the answer quality varies. Always verify answers against the official documentation, because community answers sometimes reflect behavior from different fix packs or even different DB2 versions. What works on DB2 9.7 fix pack 6 might not work on fix pack 9.