IBM DB2 for Linux, UNIX, and Windows (LUW)

Stay organized with collections Save and categorize content based on your preferences.

Looker supports IBM's DB2 for Linux/UNIX/Windows (LUW), which is discussed by IBM on this IBM Knowledge Center page.

Looker also supports IBM's DB2 on AS400, as described on the IBM DB2 on AS400 documentation page.

To configure an IBM DB2 for LUW database with Looker, you need administrator privileges on your IBM DB2 database. Then:

Setting up one or two accounts for Looker

As a IBM DB2 admin user, set up permissions and procedures for the userid that Looker will use to connect to IBM DB2.

Setting up the user for main Looker connection

Create a user account and give it the ability to select from the relevant data tables. For example, these GRANT commands give the lookeruser user access to the orders, users and all_types tables:

GRANT SELECT ON ORDERS TO USER LOOKERUSER

GRANT SELECT ON USERS TO USER LOOKERUSER

GRANT SELECT ON ALL_TYPES TO USER LOOKERUSER

Looker recommends setting up the ability to write persistent derived tables (PDTs). You can use the connection you just set up for persistent derived tables (PDTs) or create a second user for maintaining persistent derived tables. The advantages of using a second user are described on the Connecting Looker to your database documentation page.

In this example, the same lookeruser account is used to write these tables:

GRANT ALTERIN, CREATEIN, DROPIN ON SCHEMA [SCRATCH_SCHEMA] TO USER LOOKERUSER

For a detailed explanation of this command, see this IBM document.

Create a procedure to terminate select queries

Create the LOOKER_CANCEL_ACTIVITY procedure, which lets Looker terminate select queries.

  1. Run the following code, replacing [UPPERCASE_SCHEMA_NAME] with the name of the schema where your data is stored. Do not use any quotation marks. ```none CREATE OR REPLACE PROCEDURE [UPPERCASE_SCHEMA_NAME].LOOKER_CANCEL_ACTIVITY (IN application_handle INTEGER) LANGUAGE SQL BEGIN DECLARE UOW_ID INTEGER; DECLARE ACTIVITY_ID INTEGER;

    FOR v AS cur1 CURSOR FOR SELECT UOW_ID, ACTIVITY_ID FROM TABLE(SYSPROC.MON_GET_ACTIVITY(application_handle, -1)) DO CALL WLM_CANCEL_ACTIVITY(application_handle, v.uow_id, v.activity_id); END FOR ; ENDCREATE OR REPLACE PROCEDURE LOOKER_CANCEL_ACTIVITY (IN application_handle INTEGER) LANGUAGE SQL BEGIN DECLARE UOW_ID INTEGER; DECLARE ACTIVITY_ID INTEGER;

    FOR v AS cur1 CURSOR FOR SELECT UOW_ID, ACTIVITY_ID FROM TABLE(SYSPROC.MON_GET_ACTIVITY(application_handle, -1)) DO CALL WLM_CANCEL_ACTIVITY(application_handle, v.uow_id, v.activity_id); END FOR ; END ```

  2. Grant permission for PUBLIC to terminate select queries using the LOOKER_CANCEL_ACTIVITY procedure.

    GRANT EXECUTE ON PROCEDURE [UPPERCASE_SCHEMA_NAME].LOOKER_CANCEL_ACTIVITY TO PUBLIC

Block users' ability to implicitly create schemas

Tell the IBM DB2 not to implicitly creates schemas if they don't yet exist.

REVOKE IMPLICIT_SCHEMA ON DATABASE FROM PUBLIC

Troubleshooting

If you see an error similar to SQLCODE:-551 or SQLCODE:-601, check the following possible issues:

Using SQL functions and operators with IBM DB2 for LUW

When developing using a IBM DB2 for LUW connection, you can use the SQL functions and operators listed on the IBM Knowledge Center Supported functions and administrative SQL routines and views page.

Feature support

For Looker to support some features, your database dialect must also support them.

IBM DB2 supports the following features as of Looker 23.4:

Feature Supported?
Support Level
Supported
Symmetric Aggregates
No
Derived Tables
Yes
Persistent SQL Derived Tables
Yes
Persistent Native Derived Tables
Yes
Stable Views
Yes
Query Killing
Yes
Pivots
No
Timezones
No
SSL
Yes
Subtotals
No
JDBC Additional Params
Yes
Case Sensitive
Yes
Location Type
Yes
List Type
No
Percentile
No
Distinct Percentile
No
SQL Runner Show Processes
Yes
SQL Runner Describe Table
Yes
SQL Runner Show Indexes
Yes
SQL Runner Select 10
Yes
SQL Runner Count
Yes
SQL Explain
No
Oauth Credentials
No
Context Comments
Yes
Connection Pooling
No
HLL Sketches
No
Aggregate Awareness
Yes
Incremental PDTs
No
Milliseconds
Yes
Microseconds
Yes
Materialized Views
No
Approximate Count Distinct
No

Next steps

After completing the database configuration, you can connect to the database from Looker using these directions.