User Guide Getting Started Help Center Documentation Community Training
Looker
  
English
Français
Deutsch
日本語
IBM DB2 for Linux, UNIX, and Windows (LUW)

Looker supports IBM’s DB2 for Linux/Unix/Windows (LUW), which is discussed by IBM here.

Looker also supports IBM’s DB2 on AS400, as described on this documentation page, and connecting to a DB2 Warehouse on Cloud (dashDB), as described on this 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 and give it the ability 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

Allowing Use of Regular Derived Tables (Recommended)

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

    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 this IBM DB2 documentation page.

Feature Support

Looker’s ability to provide some features depends on the database dialect’s ability to support them. In the current Looker release, IBM DB2 supports the following Looker features:

Next Steps

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

Top