News
Abstract
Predicates are pushed down to MongoDB side to reduce the data transform From DB2 v11.5.4.
Content
From Db2 v11.5.4, we push down as many as filters for MongoDB client driver server type to reduce the data transform from MongoDB to Db2. To use this feature, customers do not need to do anything else. You use the same statement and federation server handles that itself.
Table 1 shows filters that could be pushed down to MongoDB side.
Table 1 Filters that could be pushed down
|
SQL filter |
MongoDB filter |
MongoDB Filter Description |
|
= |
Filters.eq(String fieldName, TItem value) |
Creates a filter that matches all documents where the value of the field name equals the specified value. |
|
> |
Filters.gt(String fieldName, TItem value) |
Creates a filter that matches all documents where the value of the given field is greater than the specified value. |
|
>= |
Filters.gte(String fieldName, TItem value) |
Creates a filter that matches all documents where the value of the given field is greater than or equal to the specified value. |
|
< |
Filters.lt(String fieldName, TItem value) |
Creates a filter that matches all documents where the value of the given field is less than the specified value. |
|
<= |
Filters.lte(String fieldName, TItem value) |
Creates a filter that matches all documents where the value of the given field is less than or equal to the specified value. |
|
<> |
Filters.ne(String fieldName, TItem value) |
Creates a filter that matches all documents where the value of the field name does not equal the specified value. |
|
OR |
Filters.or(Iterable<Bson> filters) |
Creates a filter that performs a logical OR of the provided list of filters. |
|
AND |
Filters.and(Iterable<Bson> filters) |
Creates a filter that performs a logical AND of the provided list of filters. |
|
NOT |
Filters.not(Bson filter) |
Creates a filter that matches all documents that do not match the passed in filter. |
|
IN |
Filters.in(String fieldName, TItem... values) |
Creates a filter that matches all documents where the value of a field equals any value in the list of specified values. |
How to use it?
1. Create federation objects like Wrapper, Server, and Nicknames for the MongoDB documents. For more information, refer to Configuring access to NoSQL data sources through NoSQL wrapper.
create wrapper JAVA library 'libdb2qgjava.so' options(unfenced_wrapper_class 'com.ibm.federation.wrapper.unfenced.RestServiceWrapper');
create server JSERV type mongodriver version 2.54 wrapper JAVA options(host 'xxxx', port 'xxxx', dbname 'fvtdb', log_path '/tmp/');
create user mapping for user server JSERV OPTIONS(remote_authid 'xxxx', remote_password 'xxxx');
create nickname exam(id int options(jpath '$._id'), score double options(jpath '$.doub'), correct_tiem time options(jpath '$.correct_time')) for server JSERV options (doc 'exam');
create nickname students(id int options(jpath '$._id'), name varchar(20) options(jpath '$.name')) for server JSERV options (doc 'students');
2. Run the queries and the pushdown happens then.
select * from students where (id <8 or name = 'Gojko Adzic') and id>2
3. If you want to know what predicates are pushed down, you could check the access plan or log file to see what predicates are pushed down.
The plan of the example statement could be as followings. The operators pushed down to MongoDB is indicated as RPD in the plan.
RETURN
(1)
|
RPD
(2)
From log file reply.log, you see the pushdowned predicates. You see all predicates are pushed down.
SELECT (column(STUDENTS.NAME)), (column(STUDENTS.ID))
FROM STUDENTS
WHERE (operator(< constant(2) column(STUDENTS.ID))), (operator(OR operator(< column(STUDENTS.ID) constant(8)) operator(= column(STUDENTS.NAME) constant(Gojko Adzic))))
Total predicates: 2
Was this topic helpful?
Document Information
Modified date:
15 July 2020
UID
ibm11117779