Appendix E: Frequently Asked Questions

This appendix collects the questions that come up repeatedly in DB2 training classes, online forums, and real-world projects. Answers are deliberately concise; cross-references point you to the chapter with full treatment.

Installation and Setup

Q1. What is the difference between Db2 Community Edition, Standard Edition, and Advanced Edition?

Community Edition is free for development and limited production use (4 cores, 16 GB RAM). Standard Edition adds production licensing for larger deployments. Advanced Edition includes advanced features: column-organized tables (BLU Acceleration), workload management, and advanced compression. For learning and development, Community Edition covers everything in this book. See Chapter 1.

Q2. Can I run Db2 LUW on my laptop for learning?

Yes. The Docker image is the fastest path: it runs on Windows, macOS, and Linux. See Appendix C for step-by-step instructions. Expect about 2 GB of RAM usage for a basic instance with one database.

Q3. How do I connect to DB2 for z/OS from my workstation?

You need network connectivity to the mainframe and a DB2 Connect license (or the free IBM Data Server Driver for JDBC/ODBC). Use JDBC with a connection string like jdbc:db2://host:port/database. See Chapter 1 and Appendix C for details.

Q4. What client tools do you recommend for DB2?

DBeaver (free, cross-platform) for general SQL work. IBM Data Studio (free) for stored procedure debugging and visual explain. For z/OS, Zowe CLI is excellent for command-line interaction. See Appendix C.

Q5. What page size should I use when creating a database?

32 KB for most modern workloads. It reduces B+ tree height, improves range scan performance, and allows wider rows. Use 4 KB only if you have a large number of very narrow tables with point-lookup access patterns. See Chapter 4.

SQL Questions

Q6. What is the difference between CHAR and VARCHAR?

CHAR is fixed-length (always padded to the declared length). VARCHAR is variable-length (stores only actual data plus a 2-byte length prefix). Use VARCHAR for columns where length varies. Use CHAR for fixed-length codes (e.g., state codes, currency codes). See Chapter 3.

Q7. How do I get the auto-generated identity value after an INSERT?

Use IDENTITY_VAL_LOCAL() for the value generated in the current session. Better yet, use SELECT ... FROM FINAL TABLE (INSERT INTO ...) to get the generated value in a single round trip. See Chapter 5.

Q8. Why does my query return different results with UNION vs. UNION ALL?

UNION eliminates duplicate rows; UNION ALL keeps them. UNION ALL is faster because it skips the sort/distinct step. Use UNION ALL unless you specifically need deduplication. See Appendix A for the set theory background.

Q9. How does NULL behave in DB2?

NULL represents an unknown value. Any comparison with NULL yields UNKNOWN (not TRUE or FALSE). Use IS NULL / IS NOT NULL to test for NULL. Aggregate functions (SUM, AVG, etc.) ignore NULLs. COUNT(*) counts all rows; COUNT(column) excludes NULLs. See Chapter 3.

Q10. What is the difference between FETCH FIRST n ROWS ONLY and OPTIMIZE FOR n ROWS?

FETCH FIRST n ROWS ONLY physically limits the result set---the application receives at most n rows. OPTIMIZE FOR n ROWS is a hint to the optimizer that you expect to fetch n rows, but the full result set is still available. Use both together for best results with pagination queries. See Chapter 12.

Q11. How do I write a recursive query in DB2?

Use a recursive Common Table Expression (CTE). The CTE has an anchor member (base case) and a recursive member joined with UNION ALL. See Appendix B for syntax and Chapter 6 for examples.

Q12. Can DB2 do regular expression matching?

Yes. On LUW 11.1+, use REGEXP_LIKE(), REGEXP_SUBSTR(), REGEXP_REPLACE(), and REGEXP_INSTR(). On z/OS v12+, REGEXP functions are also available. See Chapter 6.

Q13. How do I do an upsert (insert or update) in DB2?

Use the MERGE statement. It matches rows between a source and target, updating matched rows and inserting unmatched ones. See Appendix B for syntax.

Administration

Q14. How often should I run RUNSTATS?

After any significant data change---bulk loads, large deletes, major INSERT activity. As a baseline, run RUNSTATS at least weekly on active tables. On LUW, enable automatic RUNSTATS (AUTO_RUNSTATS ON) and let DB2 decide. On z/OS, schedule RUNSTATS in your batch maintenance window. See Chapter 11.

Q15. How often should I REORG?

When clustering degrades below 80% or overflow/fragmentation exceeds 20%. Monitor CLUSTERRATIO in catalog statistics and use REORGCHK (LUW) or review RUNSTATS output (z/OS). Do not REORG on a fixed schedule without checking whether it is needed. See Chapter 11.

Q16. What is the difference between IMPORT and LOAD on LUW?

IMPORT executes INSERT statements under the SQL engine---triggers fire, constraints are checked live, and the operation is fully logged. LOAD bypasses the SQL engine, writing pages directly---it is much faster but does not fire triggers, may put the table in CHECK PENDING state, and has different logging options. Use IMPORT for small data sets with triggers; use LOAD for bulk data. See Chapter 10.

Q17. How do I back up a DB2 LUW database?

Use BACKUP DATABASE. Offline backup does not require archival logging. Online backup requires LOGARCHMETH1 to be configured. Incremental and delta backups reduce backup time for large databases. See Chapter 15.

Q18. What does "log full" (SQLCODE -964) mean and how do I fix it?

The transaction log is full. Immediate action: identify and resolve long-running transactions (db2pd -db DBNAME -transactions). Then increase LOGFILSIZ, LOGPRIMARY, or LOGSECOND. For chronic issues, review application commit frequency. See Chapter 14.

Q19. What is CHECK PENDING status and how do I clear it?

CHECK PENDING means DB2 cannot guarantee referential or check constraint integrity (typically after a LOAD or SET INTEGRITY operation). Clear it with SET INTEGRITY FOR tablename IMMEDIATE CHECKED. This scans the table and enforces constraints. See Chapter 11.

Q20. How do I monitor DB2 LUW in real time?

Use db2top for interactive monitoring, db2pd for point-in-time snapshots, and MON_GET_* table functions for programmatic access. For enterprise monitoring, IBM Data Server Manager or third-party tools (Datadog, Dynatrace) provide dashboards and alerting. See Chapter 16.

Performance

Q21. My query is slow. Where do I start?

1. Get the access plan: EXPLAIN on LUW, EXPLAIN PLAN or DSN STMTCACHE on z/OS.
2. Check estimated vs. actual cardinalities. Large discrepancies indicate stale or missing statistics.
3. Run RUNSTATS on the involved tables.
4. Look for table scans on large tables where an index scan would be better.
5. Check for lock contention with db2pd -locks or -DISPLAY THREAD. See Chapter 12.

Q22. How do I read an EXPLAIN plan?

Start at the bottom (data access) and work upward (return to application). Each operator shows estimated cost and cardinality. Look for TBSCAN (table scan) vs. IXSCAN (index scan), NLJOIN (nested-loop join) vs. HSJOIN (hash join) vs. MSJOIN (merge join). The rightmost child of a join is the inner table. See Chapter 12.

Q23. When should I create an index?

When a query has a selective WHERE clause on columns that are not indexed, or when you need to avoid a sort for ORDER BY. Do not over-index: each index adds cost to INSERT, UPDATE, and DELETE operations. Use the DB2 Design Advisor or db2advis for recommendations. See Chapter 8.

Q24. What is the difference between CLUSTERRATIO and CLUSTERFACTOR?

Both measure how well the data is physically ordered to match the index. CLUSTERRATIO is a percentage (0-100); above 80% is "well-clustered." CLUSTERFACTOR is a decimal (0-1) used by the optimizer. On z/OS, CLUSTERRATIO is stored in SYSINDEXES. On LUW, both are in SYSCAT.INDEXES. See Chapter 8.

Q25. How much memory should I give buffer pools?

As much as you can afford. On LUW, enable Self-Tuning Memory (STMM) and let DB2 allocate between buffer pools, sort heap, package cache, and lock list. On z/OS, allocate based on working set analysis: monitor GETPAGE and hit ratio. Target 95%+ hit ratio for index buffer pools, 80%+ for data. See Chapter 9.

Q26. What is RID list processing and why does it fail?

When DB2 uses multiple indexes on the same table, it builds a list of Record IDs (RIDs) and intersects or unions them. If the RID list exceeds available memory (the RID pool), DB2 falls back to a table scan. Increase the MAXRIDNUM or SORTHEAP parameter, or improve the query to use a single matching index. See Chapter 12.

Q27. Why is my INSERT performance degrading over time?

Common causes: (1) The table is nearly full and DB2 is searching for free space (increase PCTFREE or REORG). (2) Index maintenance costs are growing. (3) Lock escalation due to high concurrency. (4) Transaction log I/O is the bottleneck. See Chapter 13.

High Availability

Q28. What is HADR and how does it work?

High Availability Disaster Recovery (HADR) is LUW's log-shipping replication. The primary database ships log records to one or more standby databases, which replay them continuously. Failover can be automatic (with a cluster manager) or manual. Synchronous mode guarantees zero data loss; asynchronous modes offer lower latency at the cost of potential data loss. See Chapter 17.

Q29. What is DB2 data sharing on z/OS?

Data sharing allows multiple DB2 subsystems (members) in a Parallel Sysplex to share the same data simultaneously. It uses the Coupling Facility for inter-member communication, group buffer pools, and lock management. It provides both high availability (member failure is transparent) and horizontal scalability. See Chapter 18.

Q30. What is Db2 pureScale?

pureScale is LUW's shared-disk clustering technology, analogous to z/OS data sharing. Multiple members share the same storage, with a Cluster Caching Facility (CF) for coherency. It provides continuous availability and near-linear read scalability. See Chapter 18.

Q31. How do I handle planned maintenance without downtime?

On z/OS: online REORG with SHRLEVEL CHANGE, rolling maintenance across data sharing members. On LUW: HADR with automatic client reroute, or pureScale rolling updates. See Chapters 17 and 18.

Application Development

Q32. Should I use static or dynamic SQL?

Static SQL (embedded in programs, bound into packages) gives plan stability and avoids runtime compilation cost. Dynamic SQL (prepared at runtime) gives flexibility. On z/OS, static SQL is preferred for high-volume OLTP. On LUW, dynamic SQL with statement concentrator and package caching is the norm. See Chapter 19.

Q33. How do I call a DB2 stored procedure from Java?

Use JDBC CallableStatement:

CallableStatement cs = conn.prepareCall("CALL SCHEMA.PROC_NAME(?, ?)");
cs.setInt(1, inputValue);
cs.registerOutParameter(2, Types.INTEGER);
cs.execute();
int result = cs.getInt(2);

See Chapter 20.

Q34. What is SQL PL?

SQL PL (SQL Procedural Language) is DB2's procedural extension to SQL for writing stored procedures, functions, and triggers. It supports variables, conditionals (IF/CASE), loops (WHILE/FOR/LOOP), error handling (DECLARE HANDLER), and cursors. See Chapter 21.

Q35. How do I handle errors in a DB2 application?

Check SQLCODE after every SQL statement in embedded SQL. In JDBC, catch SQLException and inspect getErrorCode() (SQLCODE) and getSQLState() (SQLSTATE). In stored procedures, use DECLARE HANDLER for specific conditions. See Chapter 19.

Q36. What is the difference between a table function and a scalar function?

A scalar function returns a single value for each input row (used in SELECT list or WHERE clause). A table function returns a result set (used in the FROM clause with TABLE()). See Chapter 21.

Security

Q37. What are the DB2 authority levels?

On LUW: SYSADM (full control), SYSCTRL (instance control, no data access), SYSMAINT (maintenance), SYSMON (monitoring), DBADM (database admin), SECADM (security admin). On z/OS: SYSADM, SYSCTRL, DBADM, DBCTRL, DBMAINT, plus package and plan authorities. See Chapter 22.

Q38. How do I implement row-level security?

On LUW: use Row and Column Access Control (RCAC) with CREATE PERMISSION and CREATE MASK. On z/OS v11+: use row permissions and column masks similarly. Row permissions define which rows a user can see; column masks define what value is returned for a column. See Chapter 22.

Q39. How do I encrypt data in DB2?

DB2 native encryption encrypts data at rest at the database level (LUW 11.1+) or table space level (z/OS v12+). Use ENCRYPT DATABASE or create encrypted table spaces. For column-level encryption, use the ENCRYPT() and DECRYPT_CHAR() built-in functions, though native encryption is preferred. See Chapter 22.

Q40. How do I audit database access?

On LUW: use db2audit for instance-level auditing, or audit policies (CREATE AUDIT POLICY) for fine-grained control. On z/OS: use the audit trace (IFCID records) and DB2 Audit Management Expert. See Chapter 22.

Migration and Compatibility

Q41. Can I migrate from Oracle to DB2 without rewriting all my SQL?

DB2 LUW offers an Oracle compatibility mode (DB2_COMPATIBILITY_VECTOR=ORA) that supports Oracle-style data types (NUMBER, VARCHAR2), PL/SQL syntax (CREATE OR REPLACE PACKAGE), dual-table queries, ROWNUM, NVL, DECODE, and more. It handles 60-80% of common Oracle SQL without changes. The remaining 20-40% requires manual conversion, particularly for complex PL/SQL packages, Oracle-specific hints, and proprietary built-in functions. IBM provides the IBM Database Conversion Workbench (DCW) to automate analysis. See Chapter 35.

Q42. Can DB2 LUW connect to DB2 for z/OS?

Yes. Use IBM Db2 Connect (licensed product) or the free IBM Data Server Driver for DRDA connectivity. Applications can access z/OS data transparently using standard JDBC or ODBC connections. Three-part names (location.schema.table) enable cross-platform queries. See Chapter 26.

Q43. How do I upgrade from one DB2 LUW version to another?

LUW supports in-place upgrade for consecutive major versions (e.g., 10.5 to 11.1, 11.1 to 11.5). Run db2iupgrade to upgrade the instance and UPGRADE DATABASE for each database. Always back up before upgrading, test in a non-production environment first, and review the incompatibility list in the IBM Knowledge Center. See Chapter 36.

Q44. What is the difference between EBCDIC and ASCII in DB2?

z/OS uses EBCDIC encoding by default; LUW uses ASCII or UTF-8. This affects character sort order (EBCDIC sorts lowercase before uppercase, opposite of ASCII) and data comparison. When connecting cross-platform via DRDA, DB2 performs automatic CCSID conversion. Be aware of potential sort-order surprises in ORDER BY results. See Chapter 26.

Data Types and Schema Design

Q45. Should I use INTEGER or BIGINT for primary keys?

Use INTEGER unless you expect more than 2.1 billion rows. INTEGER uses 4 bytes; BIGINT uses 8 bytes. The difference adds up in indexes and join operations. For surrogate keys with IDENTITY columns, INTEGER is sufficient for most OLTP tables. Use BIGINT for logging tables, event streams, or any table that might exceed 2 billion rows over its lifetime. See Chapter 3.

Q46. What is DECFLOAT and when should I use it?

DECFLOAT (decimal floating-point) provides exact decimal arithmetic without the rounding artifacts of DOUBLE, while supporting a much wider range than DECIMAL. Use it for financial calculations that require exact decimal results but where the precision requirements exceed DECIMAL(31,s). Standard financial applications should stick with DECIMAL. See Chapter 3.

Q47. How do I store JSON data in DB2?

On Db2 LUW 11.1+, store JSON in VARCHAR, CLOB, or BLOB columns, and use built-in JSON functions (JSON_VALUE, JSON_TABLE, JSON_QUERY, JSON_ARRAY, JSON_OBJECT) to query and manipulate it. On z/OS v12+, JSON support is available through the JSON_VAL function and the Db2 JSON collection interface. For schema-flexible data, JSON is a pragmatic alternative to XML. See Chapter 31.

Q48. What is the maximum row size in DB2?

The maximum row size depends on page size: approximately 4,005 bytes for 4 KB pages, 8,101 bytes for 8 KB pages, 16,293 bytes for 16 KB pages, and 32,677 bytes for 32 KB pages. LOB columns (CLOB, BLOB, DBCLOB) are stored separately and do not count against this limit---only a LOB descriptor (approximately 68-312 bytes) is stored in the row. See Chapter 4.

Distributed and Cloud

Q49. Can I run DB2 in the cloud?

Yes. IBM offers Db2 on Cloud (fully managed) and Db2 Warehouse on Cloud. Db2 Community Edition runs on any cloud VM or container platform (AWS, Azure, GCP) using Docker. For z/OS, IBM Wazi (Z Development and Test) provides cloud-hosted z/OS environments. See Chapter 33.

Q50. What is Db2 Warehouse?

Db2 Warehouse is an analytics-optimized deployment of Db2 with BLU Acceleration (column-organized tables), in-memory processing, and built-in spatial and time-series capabilities. Available as a Docker container, on-premises appliance, or managed cloud service. It shares the same SQL engine as Db2 LUW but is configured for analytical workloads. See Chapter 27.

These questions represent the starting points. Each answer is intentionally brief; the referenced chapters provide the full context, examples, and best practices.