= Reactive DB2 Client
:PREPARED_PARAMS: `?`

The Reactive DB2 Client is a client for DB2 with a straightforward API focusing on
scalability and low overhead.

The client is reactive and non-blocking, allowing to handle many database connections with a single thread.

*Features*

* Support for DB2 on Linux, Unix, and Windows
* Limited support for DB2 on z/OS
* Event driven
* Lightweight
* Built-in connection pooling
* Prepared queries caching
* Batch and cursor
* Row streaming
* RxJava API
* Direct memory to object without unnecessary copies
* Java 8 Date and Time
* SSL/TLS
* HTTP/1.x CONNECT, SOCKS4a or SOCKS5 proxy support

*Current limitations*

* No stored procedures support
* Some column types (e.g. BLOB and CLOB) are not supported

== Usage

To use the Reactive DB2 Client add the following dependency to the _dependencies_ section of your build descriptor:

* Maven (in your `pom.xml`):

[source,xml]
----
<dependency>
 <groupId>io.vertx</groupId>
 <artifactId>vertx-db2-client</artifactId>
 <version>4.5.0</version>
</dependency>
----
* Gradle (in your `build.gradle` file):

[source,groovy]
----
dependencies {
 compile 'io.vertx:vertx-db2-client:4.5.0'
}
----

== Getting started

Here is the simplest way to connect, query and disconnect

[source,java]
----
DB2ConnectOptions connectOptions = new DB2ConnectOptions()
  .setPort(50000)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret");

// Pool options
PoolOptions poolOptions = new PoolOptions()
  .setMaxSize(5);

// Create the client pool
Pool client = DB2Builder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .build();

// A simple query
client
  .query("SELECT * FROM users WHERE id='julien'")
  .execute()
  .onComplete(ar -> {
  if (ar.succeeded()) {
    RowSet<Row> result = ar.result();
    System.out.println("Got " + result.size() + " rows ");
  } else {
    System.out.println("Failure: " + ar.cause().getMessage());
  }

  // Now close the pool
  client.close();
});
----

== Connecting to DB2

Most of the time you will use a pool to connect to DB2:

[source,java]
----
DB2ConnectOptions connectOptions = new DB2ConnectOptions()
  .setPort(50000)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret");

// Pool options
PoolOptions poolOptions = new PoolOptions()
  .setMaxSize(5);

// Create the pooled client
SqlClient client = DB2Builder.client()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();
----

The pooled client uses a connection pool and any operation will borrow a connection from the pool
to execute the operation and release it to the pool.

If you are running with Vert.x you can pass it your Vertx instance:

[source,java]
----
DB2ConnectOptions connectOptions = new DB2ConnectOptions()
  .setPort(50000)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret");

// Pool options
PoolOptions poolOptions = new PoolOptions()
  .setMaxSize(5);
// Create the pooled client
SqlClient client = DB2Builder.client()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();
----

You need to release the client when you don't need it anymore:

[source,java]
----
client.close();
----

When you need to execute several operations on the same connection, you need to acquire a
`link:../../apidocs/io/vertx/db2client/DB2Connection.html[connection]` from a pool

You can easily get one from the pool:

[source,java]
----
DB2ConnectOptions connectOptions = new DB2ConnectOptions()
  .setPort(50000)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret");

// Pool options
PoolOptions poolOptions = new PoolOptions()
  .setMaxSize(5);

// Create the pooled client
Pool client = DB2Builder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();

// Get a connection from the pool
client.getConnection().compose(conn -> {
  System.out.println("Got a connection from the pool");

  // All operations execute on the same connection
  return conn
    .query("SELECT * FROM users WHERE id='julien'")
    .execute()
    .compose(res -> conn
      .query("SELECT * FROM users WHERE id='emad'")
      .execute())
    .onComplete(ar -> {
      // Release the connection to the pool
      conn.close();
    });
}).onComplete(ar -> {
  if (ar.succeeded()) {

    System.out.println("Done");
  } else {
    System.out.println("Something went wrong " + ar.cause().getMessage());
  }
});
----

Once you are done with the connection you must close it to release it to the pool, so it can be reused.

== Pool versus pooled client

The `link:../../apidocs/io/vertx/db2client/DB2Builder.html[DB2Builder]` allows you to create a pool or a pooled client

[source,java]
----
SqlClient client = DB2Builder.client()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();

// Pipelined
Future<RowSet<Row>> res1 = client.query(sql).execute();

// Connection pool
Pool pool = DB2Builder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();

// Not pipelined
Future<RowSet<Row>> res2 = pool.query(sql).execute();
----

- pool operations are not pipelined, only connections acquired from the pool are pipelined
- pooled client operations are pipelined, you cannot acquire a connection from a pooled client

== Pool sharing

include::pool_sharing.adoc[]

== Configuration

There are several alternatives for you to configure the client.

=== data object

A simple way to configure the client is to specify a `DB2ConnectOptions` data object.

[source,java]
----
DB2ConnectOptions connectOptions = new DB2ConnectOptions()
  .setPort(50000)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret");

// Pool Options
PoolOptions poolOptions = new PoolOptions().setMaxSize(5);

// Create the pool from the data object
Pool pool = DB2Builder.pool()
  .with(poolOptions)
  .connectingTo(connectOptions)
  .using(vertx)
  .build();

pool.getConnection()
  .onComplete(ar -> {
    // Handling your connection
  });
----

You can also configure the generic properties with the `setProperties` or `addProperty` methods. Note `setProperties` will override the default client properties.

=== Connection URI

Apart from configuring with a `DB2ConnectOptions` data object, We also provide you an alternative way to connect when you want to configure with a connection URI:

[source,java]
----
String connectionUri = "db2://dbuser:secretpassword@database.server.com:50000/mydb";

// Create the pool from the connection URI
Pool pool = DB2Builder.pool()
  .connectingTo(connectionUri)
  .using(vertx)
  .build();

// Create the connection from the connection URI
DB2Connection.connect(vertx, connectionUri)
  .onComplete(res -> {
    // Handling your connection
  });
----

The URI format for a connection string is:

----
db2://[user[:[password]]@]host[:port][/database][?<key1>=<value1>[&<key2>=<value2>]]
----

Currently, the client supports the following parameter keys:

* `host`
* `port`
* `user`
* `password`
* `database`

NOTE: Configuring parameters in connection URI will override the default properties.

== Connect retries

You can configure the client to retry when a connection fails to be established.

[source,java]
----
options
  .setReconnectAttempts(2)
  .setReconnectInterval(1000);
----

include::queries.adoc[leveloffset=1]

You can fetch generated keys by wrapping your query in `SELECT <COLUMNS> FROM FINAL TABLE ( <SQL> )`, for example:

[source,java]
----
client
  .preparedQuery("SELECT color_id FROM FINAL TABLE ( INSERT INTO color (color_name) VALUES (?), (?), (?) )")
  .execute(Tuple.of("white", "red", "blue"))
  .onComplete(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println("Inserted " + rows.rowCount() + " new rows.");
      for (Row row : rows) {
        System.out.println("generated key: " + row.getInteger("color_id"));
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
----

include::connections.adoc[]

include::transactions.adoc[]

include::cursor.adoc[]

== Tracing queries

include::tracing.adoc[]

== DB2 type mapping

Currently the client supports the following DB2 types

* BOOLEAN (`java.lang.Boolean`) (DB2 LUW only)
* SMALLINT (`java.lang.Short`)
* INTEGER (`java.lang.Integer`)
* BIGINT (`java.lang.Long`)
* REAL (`java.lang.Float`)
* DOUBLE (`java.lang.Double`)
* DECIMAL (`io.vertx.sqlclient.data.Numeric`)
* CHAR (`java.lang.String`)
* VARCHAR (`java.lang.String`)
* ENUM (`java.lang.String`)
* DATE (`java.time.LocalDate`)
* TIME (`java.time.LocalTime`)
* TIMESTAMP (`java.time.LocalDateTime`)
* BINARY (`byte[]`)
* VARBINARY (`byte[]`)
* ROWID (`io.vertx.db2client.impl.drda.DB2RowId` or `java.sql.RowId`) (DB2 z/OS only)

Some types that are currently NOT supported are:

* XML
* BLOB
* CLOB
* DBCLOB
* GRAPHIC / VARGRAPHIC

For a further documentation on DB2 data types, see the following resources:

* https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008483.html[DB2 for LUW 11.5 data types]
* https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_datatypesintro.html[DB2 for z/OS 12.0 data types]

Tuple decoding uses the above types when storing values, it also performs on the fly conversion of the actual value when possible:

[source,java]
----
pool
  .query("SELECT an_int_column FROM exampleTable")
  .execute().onSuccess(rowSet -> {
    Row row = rowSet.iterator().next();

    // Stored as INTEGER column type and represented as java.lang.Integer
    Object value = row.getValue(0);

    // Convert to java.lang.Long
    Long longValue = row.getLong(0);
  });
----

== Using Java enum types

You can map Java https://docs.oracle.com/javase/tutorial/java/javaOO/enum.html[enum types] to these column
types:

- Strings (VARCHAR, TEXT)
- Numbers (SMALLINT, INTEGER, BIGINT)

[source,java]
----
client.preparedQuery("SELECT day_name FROM FINAL TABLE ( INSERT INTO days (day_name) VALUES (?), (?), (?) )")
  .execute(Tuple.of(Days.FRIDAY, Days.SATURDAY, Days.SUNDAY))
  .onComplete(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println("Inserted " + rows.rowCount() + " new rows");
      for (Row row : rows) {
        System.out.println("Day: " + row.get(Days.class, "day_name"));
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
client.preparedQuery("SELECT day_num FROM FINAL TABLE ( INSERT INTO days (day_num) VALUES (?), (?), (?) )")
  .execute(Tuple.of(Days.FRIDAY.ordinal(), Days.SATURDAY.ordinal(), Days.SUNDAY.ordinal()))
  .onComplete(ar -> {
    if (ar.succeeded()) {
      RowSet<Row> rows = ar.result();
      System.out.println("Inserted " + rows.rowCount() + " new rows");
      for (Row row : rows) {
        System.out.println("Day: " + row.get(Days.class, "day_num"));
      }
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
----

The String type is matched with the Java enum's name returned by the `name()` method.

Number types are matched with the Java enum's ordinal returned by the `ordinal()` method and the row.get() method returns the corresponding enum's `name()` value at the ordinal position of the integer value retrieved.

== Collector queries

You can use Java collectors with the query API:

[source,java]
----
Collector<Row, ?, Map<Long, String>> collector = Collectors.toMap(
  row -> row.getLong("id"),
  row -> row.getString("last_name"));

// Run the query with the collector
client.query("SELECT * FROM users")
  .collecting(collector)
  .execute()
  .onComplete(ar -> {
    if (ar.succeeded()) {
      SqlResult<Map<Long, String>> result = ar.result();

      // Get the map created by the collector
      Map<Long, String> map = result.value();
      System.out.println("Got " + map);
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
----

The collector processing must not keep a reference on the `link:../../apidocs/io/vertx/sqlclient/Row.html[Row]` as
there is a single row used for processing the entire set.

The Java `Collectors` provides many interesting predefined collectors, for example you can
create easily create a string directly from the row set:

[source,java]
----
Collector<Row, ?, String> collector = Collectors.mapping(
  row -> row.getString("last_name"),
  Collectors.joining(",", "(", ")")
);

// Run the query with the collector
client
  .query("SELECT * FROM users")
  .collecting(collector)
  .execute()
  .onComplete(ar -> {
    if (ar.succeeded()) {
      SqlResult<String> result = ar.result();

      // Get the string created by the collector
      String list = result.value();
      System.out.println("Got " + list);
    } else {
      System.out.println("Failure: " + ar.cause().getMessage());
    }
  });
----

== Using SSL/TLS

To configure the client to use SSL connection, you can configure the `link:../../apidocs/io/vertx/db2client/DB2ConnectOptions.html[DB2ConnectOptions]`
like a Vert.x `NetClient`.

[source,java]
----
DB2ConnectOptions options = new DB2ConnectOptions()
  .setPort(50001)
  .setHost("the-host")
  .setDatabase("the-db")
  .setUser("user")
  .setPassword("secret")
  .setSsl(true)
  .setTrustStoreOptions(new JksOptions()
      .setPath("/path/to/keystore.p12")
      .setPassword("keystoreSecret"));

DB2Connection.connect(vertx, options)
  .onComplete(res -> {
    if (res.succeeded()) {
      // Connected with SSL
    } else {
      System.out.println("Could not connect " + res.cause());
    }
  });
----

More information can be found in the http://vertx.io/docs/vertx-core/java/#ssl[Vert.x documentation].


== Using a proxy

You can also configure the client to use an HTTP/1.x CONNECT, SOCKS4a or SOCKS5 proxy.

More information can be found in the http://vertx.io/docs/vertx-core/java/#_using_a_proxy_for_client_connections[Vert.x documentation].

== Advanced pool configuration

include::pool_config.adoc[]

ifeval::["java" == "java"]
include::override/rxjava3.adoc[]
endif::[]