MicroStrategy ONE

Create DBConnection statement

Creates a new DBConnection.

Copy 
CREATE DBCONNECTION "dbconnection_name" MDXDB DEFAULTLOGIN "default_login_name" 
 [URL "dbconnection_url"] [DSI "dbconnection_dsi"] [CATALOG "dbconnection_catalog"] [DRIVERMODE (MULTIPROCESS | MULTITHREADED)] [MAXQUERYEXEC 
 query_seconds] [MAXCONNATTEMPT conn_seconds] [TIMEOUT timeout_seconds] [IDLETIMEOUT idle_seconds] [CHARSETENCODING (NONUTF8 | UTF8)] [MAXCANCELATTEMPT <number_of_seconds>] [TABLEPREFIX "<table_prefix>"] [ADDITIONALCONNSTRING "<additional_conn_string>"];

or

Copy 
CREATE DBCONNECTION "dbconnection_name" ODBCDSN "odbc_data_source_name" DEFAULTLOGIN "default_login_name" [DRIVERMODE (MULTIPROCESS | MULTITHREADED)] [EXECMODE 
 (SYNCHRONOUS | ASYNCHRONOUS (CONNECTION | STATEMENT))] [USEEXTENDEDFETCH 
 (TRUE | FALSE)] [USEPARAMQUERIES (TRUE | FALSE)] [MAXCANCELATTEMPT cancel_seconds] [MAXQUERYEXEC query_seconds] [MAXCONNATTEMPT conn_seconds] [TIMEOUT timeout_seconds] [IDLETIMEOUT idle_seconds] [CHARSETENCODING (NONUTF8 | UTF8)] [UNIXCHARSETENCODING 
 (NONUTF8 | UTF8)] [TABLEPREFIX "table_prefix"] [ADDITIONALCONNSTRING "<additional_conn_string>"];

or

Copy 
CREATE DBCONNECTION "<dbconnection_name>" XQUERY DEFAULTLOGIN "<default_login>" [AUTHENTICATIONPARAMS "<query_authentication_parameters>"] [CHARSETENCODING (NONUTF8 | UTF8)] [UNIXCHARSETENCODING (NONUTF8 | UTF8)] [DRIVERMODE (MULTIPROCESS | MULTITHREADED)] [MAXCANCELATTEMPT <number_of_seconds>] [MAXQUERYEXEC <number_of_seconds>] [MAXCONNATTEMPT <number_of_seconds>] [TABLEPREFIX "<table_prefix>"] [TIMEOUT <number_of_seconds>] [IDLETIMEOUT <number_of_seconds>] [ADDITIONALCONNSTRING "<additional_conn_string>"];

where:

  • "dbconnection_name" is the name of the database connection to be created, of type string.

  • MDXDB indicates that an MDX database connection is to be created.

  • DEFAULTLOGIN "default_login_name" is the name of the default database login to be used with the connection, of type string.

  • URL "dbconnection_url" is the URL of the database connection, of type string.

  • DSI "dbconnection_dsi" is the DSI of the database connection, of type string.

  • CATALOG "dbconnection_catalog" is the MDX catalog to be used with the database connection, of type string.

  • DRIVERMODE is the database driver mode:

    • MULTIPROCESS indicates that the database uses multi-process mode.

    • MULTITHREADED indicates that the database uses multi-threaded mode.

  • MAXCANCELATTEMPT cancel_seconds is the maximum amount of time the query engine waits for a successful attempt to cancel a query, of type integer, where 0 and -1 indicate no limit.

  • MAXQUERYEXECquery_seconds is the maximum amount of time a single pass of SQL can execute on the database, of type integer, where 0 and -1 indicate no limit.

  • MAXCONNATTEMPTconn_seconds is the maximum amount of time Intelligence Server waits to connect to the database, of type integer, where 0 and -1 indicate no limit.

  • TIMEOUTtimeout_seconds is the amount of time an active database connection can remain open and cached on Intelligence Server to be re-used for additional jobs, of type integer, where -1 indicates no limit.

  • IDLETIMEOUT idle_seconds is the amount of time an inactive connection to the database remains cached until it is terminated, of type integer, where -1 indicates no limit.

  • ODBCDSN "odbc_data_source_name" is the ODBC data source name, of type string.

    Command Manager does not verify whether this DSN exists on the Intelligence Server machine.

  • EXECMODE indicates the execution mode for the database connection:

    • SYNCHRONOUS indicates that one statement exexutes at a time.

    • ASYNCHRONOUS CONNECTION indicates that all statements allocated within the connection should be able to run SQL asynchronously.

    • ASYNCHRONOUS STATEMENT indicates that for each statement, the asynchronous mode is explicitly set.

  • USEEXTENDEDFETCH indicates whether the database connection enables Intelligence Server to fetch block  of data from the database into memory (TRUE), instead of row by row (FALSE).

  • USEPARAMQUERIES indicates whether the database connection enables Intelligence Server to pass data to the database in blocks of data (TRUE), instead of row by row (FALSE).

  • CHARSETENCODING indicates whether to use UTF-8 or non-UTF8 encoding.

  • UNIXCHARSETENCODING indicates whether to use UTF-8 or non-UTF8 encoding on UNIX.

  • TABLEPREFIX "table_prefix" is the table prefix used with the database connection, of type string.

  • ADDITIONALCONNSTRING "<additional_conn_string>" contains additional connection information other than those of connection string, this parameter can be applied for all kinds of standalone DBConnection.

  • AUTHENTICATIONPARAMS "<query_authentication_parameters>" is the authentication parameters used by XQUERY DBConnection.

Example

Copy 
CREATE DBCONNECTION 
 "DBConn1" ODBCDSN "SQLSERVER_WH" DEFAULTLOGIN "DBLogin1" 
 DRIVERMODE MULTIPROCESS EXECMODE ASYNCHRONOUS STATEMENT MAXCANCELATTEMPT 
 10 MAXQUERYEXEC 20 TIMEOUT 50 IDLETIMEOUT 100;