Main Content

createConnectionForPool

Initialize parallel pool using database connection

Description

example

c = createConnectionForPool(pool,datasource,username,password) initializes a parallel pool by creating a database connection on each worker in the pool using a data source, user name, and password.

Note

If you use an ODBC data source for the database connection, each worker in the pool must have the required ODBC driver installed and a configured ODBC data source. Otherwise, an error occurs when you import data from the database.

Examples

collapse all

Using an ODBC database connection, access a database using a parallel pool (requires Parallel Computing Toolbox™). Import data from multiple SQL queries in parallel.

Each worker in the pool has the ODBC driver installed. Also, each worker has a configured ODBC data source. For more information, see the configureODBCDataSource function.

Start the parallel pool.

pool = gcp;
Starting parallel pool (parpool) using the 'local' profile ...
Connected to the parallel pool (number of workers: 6).

Initialize the parallel pool using an ODBC data source. This data source configures an ODBC driver to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password.

datasource = "MS SQL Server Auth";
username = "";
password = "";
c = createConnectionForPool(pool,datasource,username,password);

Define the SQL queries.

sqlqueries = ["SELECT * FROM invoice" ...
    "SELECT * FROM inventorytable" ...
    "SELECT * FROM producttable"];

Parallelize data access using the query basket by calling the parfor function.

For each worker, retrieve the database connection object, execute the SQL queries, and import data locally.

parfor i = 1:length(sqlqueries)

    conn = c.Value;

    results = fetch(conn,sqlqueries(i));

    allresults{i} = results;

end

Display the results for all queries. The cell array contains three tables, one for each set of query results.

allresults
allresults = 1×3 cell array
    {10×5 table}    {13×4 table}    {15×5 table}

Close the parallel pool.

delete(pool);

Using a JDBC database connection, access a database using a parallel pool (requires Parallel Computing Toolbox™). Import data from multiple SQL queries in parallel.

To initialize a parallel pool with a JDBC database connection, you must configure a JDBC data source. For more information, see the databaseConnectionOptions function.

Start the parallel pool.

pool = gcp;
Starting parallel pool (parpool) using the 'local' profile ...
Connected to the parallel pool (number of workers: 6).

Initialize the parallel pool using the JDBC data source MSSQLServerJDBCAuth. This data source configures a JDBC driver to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password.

datasource = "MSSQLServerJDBCAuth";
username = "";
password = "";
c = createConnectionForPool(pool,datasource,username,password);

Define the SQL queries.

sqlqueries = ["SELECT * FROM invoice" ...
    "SELECT * FROM inventorytable" ...
    "SELECT * FROM producttable"];

Parallelize data access using the query basket by calling the parfor function.

For each worker, retrieve the database connection object, execute the SQL queries, and import data locally.

parfor i = 1:length(sqlqueries)

    conn = c.Value;

    results = fetch(conn,sqlqueries(i));

    allresults{i} = results;

end

Display the results for all queries. The cell array contains three tables, one for each set of query results.

allresults
allresults = 1×3 cell array
    {10×5 table}    {13×4 table}    {15×5 table}

Close the parallel pool.

delete(pool);

Input Arguments

collapse all

Parallel pool, specified as a parallel.Pool (Parallel Computing Toolbox) object.

Data source name, specified as a character vector or string scalar. Specify the name of an existing data source.

Example: "myDataSource"

Data Types: char | string

User name required to access the database, specified as a character vector or string scalar. If no user name is required, specify an empty value "".

Data Types: char | string

Password required to access the database, specified as a character vector or string scalar. If no password is required, specify an empty value "".

Data Types: char | string

Output Arguments

collapse all

parallel.pool.Constant, specified as a parallel.pool.Constant (Parallel Computing Toolbox) object. The Value property of the parallel.pool.Constant object is available only on workers.

Introduced in R2019a