Determining if asynchrony optimization is applied to a query

To determine if asynchrony optimization is applied to a given query, you can use one of several methods to check the access plan for the query and check for a specific operator.

You can check any of the following outputs for the query in question:
  • db2exfmt output
  • Visual explain output
  • db2expln output
Each output shows asynchronous query requests in the access plan as ATQ. The 'origin' property in the description of the ATQ shows 'Asynchrony'.
The following plan fragment shows how the ATQ operator is used and shows its detailed properties. 
                          1.6e+06 
                          HSJOIN 
                          (   2) 
                          4213.74 
                            131 
                    /--------+--------\
                40000                  1000 
               HSJOIN                 SHIP   
               (   3)                 (  10) 
               1122.26                427.733 
                 117                    14 
           /------+-----\               |
       1000              1000          1000 
      ATQ          		   ATQ         NICKNM: NEWTON   
      (   4)            (   7)        S1_NN07 
      511.795           532.669 
        16                101 
        |                 |
       1000              1000 
      SHIP              SHIP   
      (   5)            (   8) 
      478.773           499.887 
        16                101 
        |                 |
       1000              1000 
 NICKNM: NEWTON    NICKNM: NEWTON   
      S2_NN02           S3_NN15 

- show the origin of the ATQ as ASYNCHRONY:
	4) TQ    : (Table Queue)
		Cumulative Total Cost: 		511.795
		Cumulative CPU Cost: 		2.79486e+06
		Cumulative I/O Cost: 		16
		Cumulative Re-Total Cost: 	68.9489
		Cumulative Re-CPU Cost: 	1.72372e+06
		Cumulative Re-I/O Cost: 	0
		Cumulative First Row Cost: 	30.8308
		Cumulative Comm Cost:		18.2176
		Cumulative First Comm Cost:	0
		Estimated Bufferpool Buffers: 	16
		Remote communication cost:	538.297

		Arguments:
		---------
		JN INPUT: (Join input leg)
			OUTER
		LISTENER: (Listener Table Queue type)
			FALSE
		TQMERGE : (Merging Table Queue flag)
			FALSE
		TQORIGIN: (Table Queue Origin type)
			ASYNCHRONY
		TQREAD  : (Table Queue Read type)
			READ AHEAD
		TQSEND  : (Table Queue Write type)
			DIRECTED
		UNIQUE  : (Uniqueness required flag)
			FALSE
If you do not find any ATQ operators in the access plan for a query, verify that the following conditions are met:
  • The system is a federated system that is enabled for DPF.
  • The query accesses nicknames on a data source that is accessed through a fenced wrapper.
  • Asynchrony is enabled using the database manager configuration parameter FEDERATED_ASYNC, the special register CURRENT FEDERATED ASYNCHRONY, or the bind option FEDERATED_ASYNCHRONY.
  • The server option DB2_MAX_ASYNC_REQUESTS_PER_CONNECTION is set to a non-zero value for the nicknames at each server.