Quiz -- Chapter 4: Setting Up Your DB2 Environment

Test your understanding of DB2 installation, configuration, and environment setup. Answers appear at the end.

Question 1. What is the recommended and fastest method for setting up a Db2 LUW development environment on any operating system?

A) Native installation using the Db2 installer wizard B) Docker container using the official ibmcom/db2 image C) Compiling Db2 from source code D) Installing via the operating system's package manager (apt, yum)

Question 2. On Db2 LUW, what is the relationship between an instance and a database?

A) They are the same thing -- each database is an instance B) An instance is the engine process that can manage one or more databases C) A database can contain multiple instances D) Instances are only used on z/OS, not on LUW

Question 3. Which command creates a new Db2 LUW instance?

A) CREATE INSTANCE B) db2inst1 create C) db2icrt D) db2start

Question 4. On DB2 for z/OS, what is the primary mechanism for configuring system-level parameters such as buffer pool sizes and thread limits?

A) The db2 UPDATE DBM CFG command B) DSNZPARM (system parameters, also known as zparms) C) The DB2 registry variables D) The database configuration file (db2cfg.ini)

Question 5. When creating a database on Db2 LUW, the PAGESIZE parameter determines:

A) The size of the database on disk B) The maximum number of pages in the buffer pool C) The fundamental unit of I/O and the maximum row width the database can store D) The size of each log file

Question 6. Which of the following is NOT a valid tablespace type in Db2 LUW?

A) Regular tablespace B) System temporary tablespace C) User temporary tablespace D) Index-only tablespace

Question 7. The LOGFILSIZ configuration parameter on Db2 LUW controls:

A) The maximum number of log files B) The size of each individual log file in 4 KB pages C) The total log space available for the database D) The maximum size of a single SQL statement

Question 8. On DB2 for z/OS, SPUFI is used for:

A) Monitoring system performance B) Interactively entering and executing SQL statements through ISPF C) Compiling COBOL programs with embedded SQL D) Managing JCL job submissions

Question 9. What information is required to establish a JDBC connection to a Db2 LUW database? (Select the most complete answer.)

A) Hostname and database name only B) Hostname, port number, and database name C) Hostname, port number, database name, and credentials (user/password) D) Only the database name, if cataloged locally

Question 10. The DB2 Command Line Processor (CLP) is invoked by typing which command?

A) db2cmd B) db2 C) clp D) db2sql

Question 11. When you run docker run --privileged -e LICENSE=accept -e DBNAME=testdb ibmcom/db2, the --privileged flag is required because:

A) Db2 requires root access to the host filesystem B) Db2 needs to modify kernel parameters for shared memory and semaphores C) Docker requires it for all database containers D) The Db2 license agreement mandates privileged mode

Question 12. On Db2 LUW, the difference between Database Manager Configuration (DBM CFG) and Database Configuration (DB CFG) is:

A) DBM CFG applies to a single database; DB CFG applies to all databases B) DBM CFG applies to the instance level; DB CFG applies to a specific database C) They are synonyms for the same configuration D) DBM CFG is for z/OS only; DB CFG is for LUW only

Question 13. DSNTEP2 on DB2 for z/OS is:

A) A real-time monitoring tool B) A batch SQL execution program that reads SQL from SYSIN C) A buffer pool management utility D) The DB2 installation program

Question 14. Which of the following buffer pool configurations follows best practices for separating workloads?

A) A single large buffer pool (BP0) for all data B) Separate buffer pools for data, indexes, temporary work, and LOBs C) One buffer pool per table D) Buffer pools are only relevant on z/OS, not on LUW

Question 15. After creating tables and loading data, which operation should you run to ensure the DB2 optimizer has accurate information for query planning?

A) REORG B) REBIND C) RUNSTATS D) RECOVER

Question 16. A developer reports that Db2 will not start after a Docker container restart, with an error about insufficient shared memory. The most likely cause is:

A) The database is corrupted and needs to be restored from backup B) The Docker container's memory limit is too low for the configured buffer pool sizes C) The Db2 license has expired D) The port 50000 is already in use

Question 17. On DB2 for z/OS, the EDM pool (Environmental Descriptor Manager pool) stores:

A) Database backup information B) Cached plans, packages, and DBDs (database descriptors) C) User authentication tokens D) Active log records

Question 18. Which Db2 LUW configuration parameter controls the number of primary log files?

A) LOGFILSIZ B) LOGPRIMARY C) LOGSECOND D) LOGPATH

Question 19. To connect DBeaver to a Db2 LUW database, which JDBC driver type is typically used?

A) Type 1 (JDBC-ODBC Bridge) B) Type 2 (Native CLI) C) Type 4 (Pure Java, thin driver) D) Type 3 (Network Protocol)

Question 20. Zowe CLI allows mainframe developers to:

A) Install DB2 for z/OS from a desktop computer B) Interact with z/OS resources including DB2 from a modern command-line interface C) Replace ISPF entirely for all mainframe operations D) Run Db2 LUW commands against a z/OS database

Question 21. The db2 GET DB CFG FOR MERIDIAN command:

A) Creates a database named MERIDIAN B) Displays the current database configuration parameters for the MERIDIAN database C) Resets the MERIDIAN database configuration to defaults D) Exports the MERIDIAN database to a configuration file

Question 22. Which is the correct order of operations when setting up a new Db2 LUW environment from scratch?

A) Create database, create instance, start instance, create tables B) Create instance, start instance, create database, create tablespaces, create tables C) Install Db2, create tables, create database, create tablespaces D) Start instance, create database, create instance, create tables

Question 23. On DB2 for z/OS, the CTHREAD parameter controls:

A) The maximum number of active log data sets B) The maximum number of concurrent allied threads (connections) C) The number of CPU threads allocated to DB2 D) The thread priority of DB2 address spaces

Question 24. When troubleshooting a "connection refused" error when connecting to Db2 LUW, which of the following should you check FIRST?

A) Whether the database tables have been created B) Whether the Db2 instance is running and listening on the expected port C) Whether RUNSTATS has been executed D) Whether the buffer pools are correctly sized

Question 25. The AUTO_RUNSTATS configuration parameter, when enabled, causes DB2 to:

A) Automatically restart the instance if it crashes B) Automatically collect table and index statistics when they become stale C) Automatically reorganize fragmented tables D) Automatically increase log file sizes when needed

Answer Key

1. B. Docker is the fastest path to a working Db2 environment. A single docker run command creates a fully functional instance in minutes, with no complex installation steps. Native installation (A) works but takes significantly longer and varies by platform.

2. B. An instance is the DB2 engine process (background processes, memory structures) that manages one or more databases. You create an instance first, then create databases within it. This is a fundamental architectural concept in Db2 LUW.

3. C. The db2icrt (DB2 Instance Create) command creates a new instance. db2start (D) starts an existing instance but does not create one. CREATE INSTANCE is not valid SQL.

4. B. DSNZPARM parameters (also called zparms or system parameters) are the primary configuration mechanism for DB2 for z/OS. They are defined during installation and can be modified via the -SET SYSPARM command. The DBM CFG and DB CFG concepts (A) are specific to Db2 LUW.

5. C. The page size determines the fundamental I/O unit size and constrains maximum row width. A 4 KB page can hold rows up to approximately 4,005 bytes; a 32 KB page can hold rows up to approximately 32,677 bytes. Choosing the right page size at database creation time is critical because it cannot be changed later.

6. D. Db2 LUW supports regular, system temporary, and user temporary tablespaces. There is no dedicated "index-only" tablespace type, though you can specify a separate tablespace for indexes using the INDEX IN clause of CREATE TABLE.

7. B. LOGFILSIZ specifies the size of each log file in 4 KB pages. The total log capacity is determined by LOGFILSIZ multiplied by (LOGPRIMARY + LOGSECOND). For example, LOGFILSIZ 8192 means each log file is 32 MB.

8. B. SPUFI (SQL Processor Using File Input) is an ISPF-based tool for entering and executing SQL statements interactively on DB2 for z/OS. It is the z/OS equivalent of the DB2 CLP on LUW.

9. C. A JDBC connection requires the hostname, port number, database name, and credentials. The standard JDBC URL format is jdbc:db2://hostname:port/dbname. While you could theoretically use OS authentication (no explicit password), the complete answer includes credentials.

10. B. The db2 command invokes the DB2 Command Line Processor. You can use it interactively (db2 then type commands) or in single-command mode (db2 "SELECT * FROM table"). db2cmd (A) opens a DB2 command window on Windows but is not the CLP itself.

11. B. Db2 requires the ability to configure shared memory segments and semaphores, which are kernel-level operations. The --privileged flag grants the container the necessary permissions to make these modifications. This is a specific Db2 requirement, not a general Docker or licensing requirement.

12. B. The Database Manager Configuration governs the instance (all databases under that instance share these settings). The Database Configuration governs a specific database. Parameters like authentication type are at the DBM level; parameters like LOGFILSIZ are at the DB level.

13. B. DSNTEP2 is a batch SQL execution program that reads SQL statements from the SYSIN DD and executes them against DB2 for z/OS. It is commonly used in JCL jobs for automated SQL execution, similar to running db2 -tvf script.sql on LUW.

14. B. Best practices call for separating data, indexes, and temporary workloads into different buffer pools. This prevents temporary operations (sorts, joins) from flushing frequently accessed data pages from the cache. One buffer pool per table (C) is excessive and creates management overhead.

15. C. RUNSTATS collects statistics about data distribution, cardinality, and index structure that the optimizer uses to choose efficient access paths. Without current statistics, the optimizer may choose suboptimal plans. REORG (A) reorganizes physical storage; REBIND (B) regenerates access plans; RECOVER (D) restores from backup.

16. B. When buffer pool sizes configured in DB2 exceed the container's memory limit, Db2 cannot allocate the required shared memory and fails to start. The solution is either to increase the Docker memory limit or reduce buffer pool sizes. This is one of the most common Docker-related setup issues.

17. B. The EDM pool caches database descriptors (DBDs), plans, packages, and dynamic SQL statements. It is a critical memory area on z/OS -- if undersized, DB2 must repeatedly reload these structures from the directory, degrading performance.

18. B. LOGPRIMARY specifies the number of primary log files. LOGFILSIZ (A) specifies the size of each file. LOGSECOND (C) specifies additional log files allocated on demand when primary logs fill. Together, these parameters determine total log capacity.

19. C. DBeaver uses the Type 4 (pure Java) JDBC driver, which communicates directly with the Db2 server over TCP/IP without requiring a local Db2 client installation. This makes it the simplest connectivity option for tools like DBeaver, IntelliJ, and Eclipse.

20. B. Zowe CLI is a modern open-source command-line tool that lets developers interact with z/OS resources -- including submitting jobs, managing datasets, and querying DB2 -- from their desktop terminal. It does not replace ISPF entirely but provides a developer-friendly alternative for many common tasks.

21. B. The GET DB CFG command displays current database configuration parameter values. It is one of the most frequently used commands for DBAs diagnosing configuration issues or verifying settings after changes.

22. B. The correct order is: create an instance (db2icrt), start the instance (db2start), create a database (CREATE DATABASE), create tablespaces, then create tables. You cannot create a database without a running instance, and you cannot create tables without a database.

23. B. CTHREAD (Connected Threads) controls the maximum number of concurrent allied (connected) threads on DB2 for z/OS. It is a critical capacity parameter -- setting it too low causes connection rejections during peak periods; setting it too high wastes resources.

24. B. "Connection refused" almost always means the Db2 instance is not running or not listening on the expected port. Check with db2 GET DBM CFG | grep SVCENAME to verify the port, and db2start to ensure the instance is running. Table existence (A), statistics (C), and buffer pools (D) are irrelevant to connection establishment.

25. B. AUTO_RUNSTATS monitors table modification activity and automatically triggers statistics collection when a table's statistics become stale (typically after a significant percentage of rows have been modified). This helps the optimizer maintain accurate cardinality estimates without manual DBA intervention.