EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB483538: How To Use the PostgreSQL Native ODBC Driver v11.0 in MSTR Product v11.1.3

Yang Huang

Quality Engineer, Principal • MicroStrategy

In MSTR Product v11.1.3(including the iserver and Windows Workstation/Desktop), there is a PostgreSQL native driver embedded which the version is v10.03. There might be in this version of driver, if it’s the concern to use it, the option is to upgrade the driver to v11.0, this Tech Note introduces how to upgrade and configure the driver in both Windows and Linux platforms.

1. Upgrade PostgreSQL ODBC driver to v11.0 in Windows platform
The v10.03 version of PostgreSQL ODBC driver is installed along with the iserver or the Workstation/Desktop installed in a Windows machine, follow up below steps to upgrade the driver to v11.0.

  • a. Download the v11.0 driver file psqlodbc_11_00_0000-x64.zip from the PostgreSQL web site: https://download.postgresql.org/pub/odbc/versions/msi/psqlodbc_11_00_0000-x64.zip
  • b. Decompress and get the MSI file psqlodbc_x64.msi, run it to install the new version of PostgreSQL ODBC driver. Before installation the driver is shown as below in ODBC administrator
ka02R000000bs6WQAQ_0EM2R000000lc9L.jpeg
  • The driver version is 10.03.
  • c. After installation, the driver is shown as
ka02R000000bs6WQAQ_0EM2R000000lc9Q.jpeg
  • The driver has been upgraded to v11.0.
  • d. Create DSN using Strategy Connectivity Wizard. In Connectivity Wizard page, choose the “Other Relational Databases”
ka02R000000bs6WQAQ_0EM2R000000lc9f.jpeg
  • In the next page, choose the “PostgreSQL Unicode(x64)”
ka02R000000bs6WQAQ_0EM2R000000lc9k.jpeg
  • Input detailed PostgreSQL server information and click Save.
ka02R000000bs6WQAQ_0EM2R000000lc9p.jpeg
  • e. Or create DSN in ODBC administrator tools
ka02R000000bs6WQAQ_0EM2R000000lc9u.jpeg
  • f. Create data source in MTDI interface using the created DSN connecting to PostgreSQL instance


ka02R000000bs6WQAQ_0EM2R000000lc9z.jpeg

2. Upgrade PostgreSQL ODBC driver to v11.0 in Linux platform
With the MSTR iserver installed in Linux platform, the PostgreSQL Native ODBC driver installed as well which locates in folder: %INSTALL_FOLDER%/pgsql-10(in a provisioned MSTR environment from Amazon the folder is: /opt/mstr/MicroStrategy/install/pgsql-10), the driver version is v10.03, to upgrade the driver to v11.0, follow up below steps.

  • a. Download the attached zip package which contains the PostgreSQL v11.0 ODBC driver: pg_rh7.zip
  • b. Upload the pg_rh7.zip to the host which iserver locates and unzip the file, it’s recommended to put files in folder %INSTALL_FOLDER%/pgsql-11, sample command as:
    1. sudo unzip -d /opt/mstr/MicroStrategy/install/ pg_rh7.zip
  • New folder /opt/mstr/MicroStrategy/install/pgsql-11 will be created automatically
  • c. Edit ODBC.sh file which locates in %INSTALL_FOLDER%/env folder:
    1. vim /opt/mstr/MicroStrategy/env/ODBC.sh
  • Modify the line: POSTGRESQL_PATH='/opt/mstr/MicroStrategy/install/pgsql-10/lib'
  • To: POSTGRESQL_PATH='/opt/mstr/MicroStrategy/install/pgsql-11/lib'd. 
  • Edit odbcinst.ini file which locates in %INSTALL_FOLDER% folder:
    1. vim /opt/mstr/MicroStrategy/odbcinst.ini
  • Modify the “[PostgreSQL Unicode(x64)]” section to be like:


    [PostgreSQL Unicode(x64)]
    Driver=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
    Description=ODBC for PostgreSQL
    Setup=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
    FileUsage=1
    DriverUnicodeType=1[TPCH_PG]
    Driver=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
    Description=PostgreSQL Unicode(x64)
    Database=tpch
    Servername=xxx.xxx.xxx.xxx
    Port=5432
    Protocol=9.3
    ReadOnly=No
    RowVersioning=No
    ShowSystemTables=No
    ConnSettings=
    DriverUnicodeType=1


     
  • Note: the above folder needs to be replaced by correct iserver installation folder.
  • e. Edit odbc.ini to add new DSN for PostgreSQL
    1. vim /opt/mstr/MicroStrategy/odbcinst.ini
  • In “[ODBC Data Sources]” section, add new line for PostgreSQL:
     
    1. TPCH_PG=PostgreSQL Unicode(x64)
       
  • Append new section to the end of odbc.ini then add new DSN info like following:


  • [PostgreSQL Unicode(x64)]
    Driver=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
    Description=ODBC for PostgreSQL
    Setup=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
    FileUsage=1
    DriverUnicodeType=1[TPCH_PG]
    Driver=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
    Description=PostgreSQL Unicode(x64)
    Database=tpch
    Servername=xxx.xxx.xxx.xxx
    Port=5432
    Protocol=9.3
    ReadOnly=No
    RowVersioning=No
    ShowSystemTables=No
    ConnSettings=
    DriverUnicodeType=1



    Note: the above folder needs to be replaced by correct iserver installation folder
  • f. Try connectivity to use the new created DSN using below command:
    1. $: ./bin/mstrtodbcx
      - >: .cn -d pg_Native_11 -u uid -p pwd
  • g. Create data source in MTDI interface using the created DSN connecting to PostgreSQL instance
ka02R000000bs6WQAQ_0EM2R000000lcAO.png
Comment

0 comments

Details

Knowledge Article

Published:

August 9, 2019

Last Updated:

August 9, 2019