Edit a database connection

Supports the Boolean data type	Instructs Pipeline Designer to use native Boolean data types supported by the database.
Supports the timestamp data type	Instructs Pipeline Designer to use the timestamp data type supported by the database.
Quote all in database	Enables case-sensitive table names. For example, MySQL is case-sensitive on Linux, but not case-sensitive on Microsoft Windows. If you quote the identifiers, the databases uses a case-sensitive table name.
Force all to lower-case	Enables the system to change the case of all database to lower-case.
Force all to upper-case	Enables the system to change the case of all identifiers to upper-case.
Preserve case of reserved words	Instructs Pipeline Designer to use a list of reserved words supported by the database.
The Preferred Schema name where no schema is used	For Pipeline Designer, enter the preferred schema name (for example, MYSCHEMA).
SQL Code Editor	Enter the SQL statements to execute right after connecting.

Options

Use the Options tab to add or delete parameters. Parameters enable you to control database‑specific behavior.

• To add more Parameters to the list, click Add Row.

• To Delete rows, click the Delete icon next to the row.

Pooling

Configure options in the Pooling tab to set up a connection pool and define options like the initial pool size, maximum pool size, and connection pool parameters. By default, a connection remains open for each individual report or set of reports in PUC and for each individual step in a transformation in PDI. For example, you might start by specifying a pool of ten or fifteen connections, and as you run reports in PUC or transformations in PDI, the unused connections drop off. Pooling helps control database access, especially if you have dashboards that contain many reports and require a large number of connections. Pooling can also be implemented when your database licensing restricts the number of active concurrent connections.You can take the following action in the parameters section:

• To add a new parameter, click Add Row and then enter the Parameter name and Value.

• To delete a parameter, click the Delete icon.

• To change how many parameters are shown at one time, select a new Items per page value.

• If there are multiple pages of parameters, scroll through the pages using the left and right arrow that appear under the list of parameters.

The following table shows an example of Pooling options that might be available in a typical JDBC driver. Check your driver documentation for driver-specific pooling details.

Enable Connection Pooling	Enables connection pooling.
Pool Size	Initial Set the initial size of the connection pool. Maximum Set the maximum number of connections in the connection pool.
Parameters	You can define additional custom pool parameters. Click on any parameter to view a short description of that parameter. Click Restore Defaults when to restore the default values for selected parameters.The most commonly-used parameter is validationQuery. The parameter differs slightly depending on your RDBMS connection. The basic set of Pentaho databases use the following values for validationQuery: For Oracle and PostgreSQL, use Select 1 from dual. For MS SQL Server and MySQL, use Select 1.
Description	Enter a description for your parameters.

Clustering

Use the Clustering options to cluster the database connection and create connections to data partitions in Pipeline Designer. To create a new connection to a data partition, enter a Partition ID, the Host Name, the Port, the Database Name, User Name, and Password for the connection.If you have the Pentaho Server configured in a cluster of servers, and use the Data Source Wizard (DSW) in PUC to add a new data source, the new data source will only be seen on the cluster node where the user has a session. For the new data source to be seen by all the cluster nodes, you must disable DSW data source caching. This may cause the loading of the data source list to be slower since the list is not cached.To disable the cache, navigate to the server/pentaho-server/pentaho-solutions/system folder and set the enableDomainIdCache value in the system.properties file to false.​