IBM DB2

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

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:

-- 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):

-- 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:

-- 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