createConnectionForPool

Initialize parallel pool using database connection

collapse all in page

Syntax

c = createConnectionForPool(pool,DataSourceName,username,password)

Description

c = createConnectionForPool(pool,DataSourceName,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.

example

Examples

collapse all

This example uses:

Open Live Script

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);

This example uses:

Open Live Script

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. Database Explorer creates this name when you use the app to make a connection and store your credentials as part of the data source.

Username required to access the database, specified as a character vector or string scalar. If no username is required, specify an empty value "". Provide this username if you do not store it as part of the data source when using the Database Explorer app to make a connection.

Password required to access the database, specified as a character vector or string scalar. If no password is required, specify an empty value "". Provide this password if you do not store it as part of the data source when using the Database Explorer app to make a connection.

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.

Version History

Introduced in R2019a

See Also

|

Topics