= 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.1.2</version>
</dependency>
----
* Gradle (in your `build.gradle` file):

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

== 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
DB2Pool client = DB2Pool.pool(connectOptions, poolOptions);

// A simple query
client
  .query("SELECT * FROM users WHERE id='julien'")
  .execute(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 = DB2Pool.client(connectOptions, poolOptions);
----

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 = DB2Pool.client(vertx, connectOptions, poolOptions);
----

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
DB2Pool client = DB2Pool.pool(vertx, connectOptions, poolOptions);

// 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/DB2Pool.html[DB2Pool]` allows you to create a pool or a pooled client

[source,java]
----
SqlClient pooledClient = DB2Pool.client(vertx, connectOptions, poolOptions);

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

// Connection pool
DB2Pool pool = DB2Pool.pool(vertx, connectOptions, poolOptions);

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

- pool operations are not pipelined, only connection client are pipelined
- pooled client operations are pipelined

== 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
DB2Pool pool = DB2Pool.pool(vertx, connectOptions, poolOptions);

pool.getConnection(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
DB2Pool pool = DB2Pool.pool(connectionUri);

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

The URI format for a connection string is:

----
db2://<USERNAME>:<PASSWORD>@<HOSTNAME>:<PORT>/<DBNAME>
----

Currently the client supports the following parameter key words in connection uri

* host
* port
* user
* password
* dbname

Note: configuring properties 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"), 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(ar -> {
  RowSet<Row> rowSet = ar.result();
  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), 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()), 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(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(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, 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::[]