Looker supports IBM’s LUW — DB2 for Linux/Unix/Windows, which is discussed by IBM here.
Looker also supports connecting to a DB2 Warehouse on Cloud (dashDB), as described on this documentation page.
To configure a LUW DB2 database for Looker, you need administrator privileges on your DB2 database. Then:
- Set up one or two accounts for Looker
- Create a procedure to terminate select queries
- Block user’s ability to implicitly create schemas
Setting Up One or Two Accounts for Looker
As a DB2 admin user, set up permissions and procedures for the userid that Looker will use to connect to 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
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
LOOKER_CANCEL_ACTIVITY procedure, which lets Looker terminate select queries.
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
Grant permission for
PUBLICto terminate select queries using the
GRANT EXECUTE ON PROCEDURE [UPPERCASE_SCHEMA_NAME].LOOKER_CANCEL_ACTIVITY TO PUBLIC
Block Users’ Ability to Implicitly Create Schemas
Tell the DB2 not to implicitly creates schemas if they don’t yet exist.
REVOKE IMPLICIT_SCHEMA ON DATABASE FROM PUBLIC
If you see an error similar to
SQLCODE:-601, check the following possible issues:
- Your permissions are not set up correctly.
- The procedure
LOOKER_CANCEL_ACTIVITYis not set up correctly.
- The users do not have permission to use the
Using SQL Functions and Operators with IBM LUW DB2
When developing using a IBM LUW DB2 connection, you can use the SQL functions and operators listed on this IBM DB2 documentation page.
Looker’s ability to provide some features depends on whether the database dialect can support them.
In the current Looker release, IBM LUW DB2 supports the following Looker features:
After completing the database configuration, you can connect to the database from Looker using these directions.