Customizing the connect procedure for shadow tables

You can customize the connect procedure to automate the configuration of database connections that are intended to submit only analytical queries so that these queries benefit from shadow tables without having an impact to the application logic.

To customize the connect procedure for shadow tables:

1. Identify analytic applications that can benefit from using shadow tables and determine which connection attributes you can use to identify these applications in the connect procedure. Do not use shadow tables for applications that cannot tolerate latency, such as transactional applications.
2. Connect to db2-database-name as follows:

   db2 CONNECT TO db2-database-name

3. If you want to modify an existing connect procedure, set the connect_proc database configuration parameter to NULL before you modify the code of the procedure as follows:

   db2 "UPDATE DB CFG USING CONNECT_PROC NULL"

   The value NULL must be specified in uppercase characters.
4. Create a connect procedure or modify an existing connect procedure and specify the SQL statements that perform the following actions:
   • Enable intrapartition parallelism for the connection.
   • Set the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register so that replication-maintained tables are considered by the optimizer in the processing of dynamic SQL queries.
   • Set the CURRENT REFRESH AGE special register to an appropriate value other than 0.
   • Specify a condition that uses the connections attributes that you identified in step 1 to determine whether the application connection is enabled for using shadow tables.
   The following example shows the DDL statement to create a procedure called REPL_MQT_SETUP that uses the application name as the condition to determine whether a connection is enabled to use shadow tables:

   CREATE OR REPLACE PROCEDURE DBGUEST1.REPL_MQT_SETUP()
   BEGIN
     DECLARE APPLNAME VARCHAR(128);
   
     SET APPLNAME = (SELECT APPLICATION_NAME 
       FROM TABLE(MON_GET_CONNECTION(MON_GET_APPLICATION_HANDLE(), -1)));
   
     IF (APPLNAME = 'appl-name') THEN
       CALL SYSPROC.ADMIN_SET_INTRA_PARALLEL('YES');
       SET CURRENT DEGREE 'ANY';
       SET CURRENT MAINTAINED TYPES REPLICATION;
       SET CURRENT REFRESH AGE 500;
     END IF;
   END@

   Write this DDL statement to the connect_proc.ddl file and replace appl-name with your application name. Then, create the procedure by issuing the following command:

   db2 td@ -v -f connect_proc.ddl

5. Grant the EXECUTE privilege on the customized connect procedure to all users at the current server. The following example shows how to grant the EXECUTE privilege on the REPL_MQT_SETUP procedure:

   db2 "GRANT EXECUTE ON PROCEDURE DBGUEST1.REPL_MQT_SETUP
          TO PUBLIC"

6. If you created a new connect procedure, update the connect_proc database configuration parameter to indicate the name of the new procedure name. The following example shows how to update this parameter for the REPL_MQT_SETUP procedure:

   db2 "UPDATE DB CFG FOR SHADOWDB
          USING CONNECT_PROC "DBGUEST1.REPL_MQT_SETUP"