= 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 1 and RxJava 2
* 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-lang-ruby</artifactId>
 <version>3.9.14</version>
</dependency>
----
* Gradle (in your `build.gradle` file):

[source,groovy]
----
dependencies {
 compile 'io.vertx:vertx-lang-ruby:3.9.14'
}
----

== Getting started

Here is the simplest way to connect, query and disconnect

[source,ruby]
----
require 'vertx-db2-client/db2_pool'

# Connect options
connectOptions = {
  'port' => 50000,
  'host' => "the-host",
  'database' => "the-db",
  'user' => "user",
  'password' => "secret"
}

# Pool options
poolOptions = {
  'maxSize' => 5
}

# Create the client pool
client = VertxDb2Client::DB2Pool.pool(connectOptions, poolOptions)

# A simple query
client.query("SELECT * FROM users WHERE id='julien'").execute() { |ar_err,ar|
  if (ar_err == nil)
    result = ar
    puts "Got #{result.size()} rows "
  else
    puts "Failure: #{ar_err.get_message()}"
  end

  # Now close the pool
  client.close()
}

----

== Connecting to DB2

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

[source,ruby]
----
require 'vertx-db2-client/db2_pool'

# Connect options
connectOptions = {
  'port' => 50000,
  'host' => "the-host",
  'database' => "the-db",
  'user' => "user",
  'password' => "secret"
}

# Pool options
poolOptions = {
  'maxSize' => 5
}

# Create the pooled client
client = VertxDb2Client::DB2Pool.pool(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,ruby]
----
require 'vertx-db2-client/db2_pool'

# Connect options
connectOptions = {
  'port' => 50000,
  'host' => "the-host",
  'database' => "the-db",
  'user' => "user",
  'password' => "secret"
}

# Pool options
poolOptions = {
  'maxSize' => 5
}
# Create the pooled client
client = VertxDb2Client::DB2Pool.pool(vertx, connectOptions, poolOptions)

----

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

[source,ruby]
----

# Close the pool and all the associated resources
pool.close()

----

When you need to execute several operations on the same connection, you need to use a client
`link:../../yardoc/VertxDb2Client/DB2Connection.html[connection]`.

You can easily get one from the pool:

[source,ruby]
----
require 'vertx-db2-client/db2_pool'

# Connect options
connectOptions = {
  'port' => 50000,
  'host' => "the-host",
  'database' => "the-db",
  'user' => "user",
  'password' => "secret"
}

# Pool options
poolOptions = {
  'maxSize' => 5
}

# Create the pooled client
client = VertxDb2Client::DB2Pool.pool(vertx, connectOptions, poolOptions)

# Get a connection from the pool
client.get_connection() { |ar1_err,ar1|

  if (ar1_err == nil)

    puts "Connected"

    # Obtain our connection
    conn = ar1

    # All operations execute on the same connection
    conn.query("SELECT * FROM users WHERE id='julien'").execute() { |ar2_err,ar2|
      if (ar2_err == nil)
        conn.query("SELECT * FROM users WHERE id='emad'").execute() { |ar3_err,ar3|
          # Release the connection to the pool
          conn.close()
        }
      else
        # Release the connection to the pool
        conn.close()
      end
    }
  else
    puts "Could not connect: #{ar1_err.get_message()}"
  end
}

----

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

== 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,ruby]
----
require 'vertx-db2-client/db2_pool'

# Data object
connectOptions = {
  'port' => 50000,
  'host' => "the-host",
  'database' => "the-db",
  'user' => "user",
  'password' => "secret"
}

# Pool Options
poolOptions = {
  'maxSize' => 5
}

# Create the pool from the data object
pool = VertxDb2Client::DB2Pool.pool(vertx, connectOptions, poolOptions)

pool.get_connection() { |ar_err,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,ruby]
----
require 'vertx-db2-client/db2_pool'
require 'vertx-db2-client/db2_connection'

# Connection URI
connectionUri = "db2://dbuser:secretpassword@database.server.com:50000/mydb"

# Create the pool from the connection URI
pool = VertxDb2Client::DB2Pool.pool(connectionUri)

# Create the connection from the connection URI
VertxDb2Client::DB2Connection.connect(vertx, connectionUri) { |res_err,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.

include::queries.adoc[]

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

[source,ruby]
----
require 'vertx-sql-client/tuple'
client.prepared_query("SELECT color_id FROM FINAL TABLE ( INSERT INTO color (color_name) VALUES (?), (?), (?) )").execute(VertxSqlClient::Tuple.of("white", "red", "blue")) { |ar_err,ar|
  if (ar_err == nil)
    rows = ar
    puts "Inserted #{rows.row_count()} new rows."
    rows.each do |row|
      puts "generated key: #{row.get_integer("color_id")}"
    end
  else
    puts "Failure: #{ar_err.get_message()}"
  end
}

----

include::connections.adoc[]

include::transactions.adoc[]

include::cursor.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,ruby]
----
pool.query("SELECT an_int_column FROM exampleTable").execute() { |ar_err,ar|
  rowSet = ar
  row = rowSet.iterator().next()

  # Stored as INTEGER column type and represented as java.lang.Integer
  value = row.get_value(0)

  # Convert to java.lang.Long
  longValue = row.get_long(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,ruby]
----
require 'vertx-sql-client/tuple'
client.prepared_query("SELECT day_name FROM FINAL TABLE ( INSERT INTO days (day_name) VALUES (?), (?), (?) )").execute(VertxSqlClient::Tuple.of(:FRIDAY, :SATURDAY, :SUNDAY)) { |ar_err,ar|
  if (ar_err == nil)
    rows = ar
    puts "Inserted #{rows.row_count()} new rows"
    rows.each do |row|
      puts "Day: #{row.get_values(:class, row.get_column_index("day_name"))}"
    end
  else
    puts "Failure: #{ar_err.get_message()}"
  end
}

require 'vertx-sql-client/tuple'
client.prepared_query("SELECT day_num FROM FINAL TABLE ( INSERT INTO days (day_num) VALUES (?), (?), (?) )").execute(VertxSqlClient::Tuple.of(:FRIDAY.ordinal(), :SATURDAY.ordinal(), :SUNDAY.ordinal())) { |ar_err,ar|
  if (ar_err == nil)
    rows = ar
    puts "Inserted #{rows.row_count()} new rows"
    rows.each do |row|
      puts "Day: #{row.get_values(:class, row.get_column_index("day_num"))}"
    end
  else
    puts "Failure: #{ar_err.get_message()}"
  end
}

----

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,ruby]
----
Code not translatable
----

The collector processing must not keep a reference on the `link:../../yardoc/VertxSqlClient/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,ruby]
----
Code not translatable
----

== Using SSL/TLS

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

[source,ruby]
----
require 'vertx-db2-client/db2_connection'

options = {
  'port' => 50001,
  'host' => "the-host",
  'database' => "the-db",
  'user' => "user",
  'password' => "secret",
  'ssl' => true,
  'trustStoreOptions' => {
    'path' => "/path/to/keystore.p12",
    'password' => "keystoreSecret"
  }
}

VertxDb2Client::DB2Connection.connect(vertx, options) { |res_err,res|
  if (res_err == nil)
    # Connected with SSL
  else
    puts "Could not connect #{res_err}"
  end
}

----

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].

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