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:
- 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 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
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 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.
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
PUBLIC
to terminate select queries using theLOOKER_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:
- Your permissions are not set up correctly.
- The procedure
LOOKER_CANCEL_ACTIVITY
is not set up correctly. - The users do not have permission to use the
LOOKER_CANCEL_ACTIVITY
procedure.
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. In Looker 22.6, 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.