# IBM DB2

Connect to IBM DB2 for enterprise analytics

IBM DB2 is an enterprise relational database management system, widely used in large organizations for mission-critical applications. Scoop connects directly to DB2, enabling AI-powered analysis on your enterprise data.

## Connection Details

| Setting | Value |
|---------|-------|
| Default Port | 50000 |
| Connection Type | TCP/IP |
| SSL | Recommended |

## Prerequisites

Before connecting Scoop to IBM DB2:

1. **IBM DB2 server** (version 11.1 or later recommended)
2. **Read-only user** with SELECT permissions
3. **Network access** from Scoop's IP addresses

## Create a Read-Only User

Connect to your DB2 database as an administrator and run:

```sql
-- Create the user (DB2 uses OS authentication by default)
-- For DB2 on Linux/Unix, create an OS user first, then:

-- Connect to the database
CONNECT TO your_database;

-- Grant connection privilege
GRANT CONNECT ON DATABASE TO USER scoop_user;

-- Grant SELECT on all tables in a schema
GRANT SELECT ON SCHEMA your_schema TO scoop_user;

-- Or grant SELECT on specific tables
GRANT SELECT ON your_schema.your_table TO scoop_user;
```

For DB2 on Cloud (IBM Cloud Databases for DB2):
```sql
-- Create a service credential in IBM Cloud, then grant permissions
GRANT SELECT ON SCHEMA your_schema TO USER service_user;
```

## Whitelist Scoop IP Addresses

### For DB2 on IBM Cloud
1. Go to your DB2 instance in IBM Cloud
2. Navigate to **Settings** > **Allowlisting**
3. Add Scoop's IP addresses

### For On-Premises DB2
Configure your firewall to allow incoming connections on port 50000 from Scoop's IP addresses.

Also ensure DB2 is configured for TCP/IP connections:
```
db2set DB2COMM=TCPIP
db2 update dbm cfg using SVCENAME 50000
```

Contact Scoop support for the current list of IP addresses to whitelist.

## Connect in Scoop

1. Navigate to **Datasets** and click **New Dataset**
2. Select **Database** as your source
3. Choose **IBM DB2** from the database type dropdown
4. Enter your connection details:
   - **Server Host**: Your DB2 hostname
   - **Port**: 50000 (or your custom port)
   - **Database**: Your database name
   - **Username**: scoop_user
   - **Password**: The user's password
5. Click **Test Connection** to verify
6. Enter your SQL query and proceed

## Connection URL Format

If using a JDBC URL directly:

```
jdbc:db2://hostname:50000/database_name
```

With SSL:
```
jdbc:db2://hostname:50000/database_name:sslConnection=true;
```

For DB2 on z/OS:
```
jdbc:db2://hostname:port/location:currentSchema=schema;
```

## Query Syntax Notes

DB2 SQL has some unique syntax:

```sql
-- Use FETCH FIRST for limiting rows
SELECT * FROM your_table FETCH FIRST 100 ROWS ONLY

-- Date functions
SELECT CURRENT DATE, CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1

-- String concatenation uses ||
SELECT first_name || ' ' || last_name FROM employees
```

## Best Practices

- **Use database partitioning** for large tables
- **Create appropriate indexes** on filtered columns
- **Use EXPLAIN** to analyze query performance
- **Consider materialized query tables** for complex analytical queries

## Troubleshooting

### SQL30081N Communication Error
- Verify DB2 is configured for TCP/IP: `db2set -all | grep DB2COMM`
- Check that the port is correct: `db2 get dbm cfg | grep SVCENAME`
- Ensure the port is open in your firewall

### SQL1403N User Not Found
- For DB2 LUW, ensure the OS user exists
- Verify the user has been granted CONNECT privilege
- Check authentication configuration

### SQL0551N Permission Denied
- Verify SELECT was granted on the required tables
- Check schema permissions: `GRANT SELECT ON SCHEMA`
- Ensure the user can access the schema