PGNP Native OLE DB Provider For PostgreSQL, Greenplum .
CATA2020PGNP Native OLE DB Provider forPostgreSQL, Greenplum, RedshiftDeveloper’s ManualThis document contains description of various PGNP features, use cases andprogramming techniques. It is written for professional developers.Konstantin IzmailovCopyright 2006-2019. All Rights Reserved.10/1/2020
This document is property of PGNP team. Neither a part nor the entire document can be reproduced, changed,distributed or published without prior written permission of owners. Please contact us if you have anyquestions: support@pgoledb.com.Page 2
Table of Contents1Introduction . 62Installation and Product Activation . 732.1.1UI based installation. 72.1.2Unattended/silent installation . 102.1.3Activation on a computer behind a firewall (in DMZ) . 11Provider’s Features . 123.1Database Transformation . 123.1.1Transforming MS SQL 2000 database into a PostgreSQL database using DTSWizard . 123.1.2Transforming MS SQL 2005, 2008, 2012 database into a PostgreSQL database using DTSWizard . 153.2Transactions . 153.2.1Local Transactions . 153.2.2Distributed Transactions . 173.2.3PostgreSQL Nested Transactions . 183.3Linked Servers . 193.3.1Create Linked Server using SQL Server Wizard . 193.3.2Create Linked Server using SQL Server Stored Procedures . 213.3.3Viewing and changing Linked Server RPC status. 223.3.4Running Linked Server in a separate process (out-of-proc). 223.4Replication with SQL Server 2000 . 233.4.1Configuring Publisher, Subscribers and Distributor . 233.4.2Creating publication . 243.4.3Create Snapshot . 273.4.4Adding Subscribers . 283.4.5Synchronize . 323.5Replication with SQL Server 2005/2008/2012 . 343.5.1Configure SQL Server as Distributor. 343.5.2Configure the publisher to use a specified distribution database . 343.5.3Create Linked Server . 343.5.4Create the snapshot publication . 353.5.5Create the snapshot subscription . 353.5.6Deleting subscription and publication . 373.5.7Create publication for transactional replication . 37Page 3
3.5.83.6Generating reports in SQL Server Reporting Services. 403.7Two phase commit protocol (2PC). 423.7.1Configuring DTC . 423.7.2Starting DTC Service . 423.7.3Enabling prepared transactions in PostgreSQL . 423.7.4Troubleshooting issues with 2PC . 423.8Configuring OLE DB connection in BIDS . 433.8.2Configuring Source and Destination . 44The Query Optimizer . 453.9.1Simple query substitution . 463.9.2Template based substitution . 473.9.3Exact Match scenario: optimizing ROLAP cube . 483.9.4Optimizing metadata retrieval . 493.10“Hinting” statements . 513.10.1Copying table from SQL Server to Postgres in DTSWizard . 513.10.2Tweaking Data Flow in SSIS Package. 523.10.3Using comments to change Extended Properties parameters per statement . 53Programming with the Provider. 544.1Connection String. 544.1.1Main String parameters . 544.1.2Extended Properties. 544.1.3Parameter BULK METHOD . 584.1.4Parameters for Redshift . 584.1.5Deprecated and not supported parameters . 584.2Data type mapping between PostgreSQL and OLE DB . 584.3Internal Stored Procedures . 604.3.1Get License Information . 604.3.2Refresh Metadata Cache . 604.3.3Check license . 604.3.4Publish comment into PGNP Profiler log . 614.45FastLoad feature . 433.8.13.94Create subscription for transactional replication . 38Known Issues. Error! Bookmark not defined.Appendix A. Utilities. 62Page 4
5.1CreateIndex . 625.2DropIndex. 675.3PGNP Profiler (1.3.x and later) . 715.3.1User interface explained . 715.3.2Main actions in the profiler. 725.3.3Collecting trace from remote computers . 725.3.4Filtering messages in the trace . 745.3.5Format of PGL file. 765.4PGNPUpdate (1.4.x and later) . 805.4.1Working in Normal mode . 805.4.2Working in Activation mode . 815.4.3Granting permissions to OLE DB provider for special users . 825.4.4Using from command line . 846Appendix B. Handling ISequentialStream in the OLEDB provider. 867Appendix C. Samples . 8687.1C# Samples . 867.2C Samples . 887.3Delphi 7 Samples . 89Appendix D. Time zones conversion . 89Page 5
1 IntroductionThe PGNP Native OLE DB Provider exposes powerful low-level OLEDB interfaces to Windows applications connecting toPostgreSQL, Greenplum and Redshift databases. The provider can help you achieve performance and flexibility that arenot available via either ODBC driver or .NET Provider: Rich metadata (advanced schema and cursors)Databases transformation supportLinked ServersReplicationDatabase Reverse EngineeringBulk importThe Developer’s Manual describes the Provider functionality and gives examples of the Provider usage. It is intended foruse by software developers, system administrators and users of the OLE DB applications.The supported operating systems are: Windows 2000 with MDAC 2.8 SP1, Windows XP, Windows Server 2003, WindowsServer 2008, Windows Server 2012, Vista, Windows 7, Windows 8; both 32-bit and 64-bit.The PGNP Provider works with the following versions of PostgreSQL database: PostgreSQL 8.0 and later, Greenplum 3.0and later, EnterpriseDB Advanced Server 8.3 and later, Redshift. It may work on earlier versions of the correspondingdatabases but we have not tested those configurations, or Provider might not support them.The Postgres and Greenplum Providers are available in two editions: Desktop Edition (DE) and Server Edition (SE).Following table summarizes differences:Postgres DEPostgres SEIntended for use from desktop applications connecting toPostgres databases only. This edition may not be as fast and asscalable as SE on the very large rowsets (several million rows).Intended for use from servers (IIS, SSAS, SSIS, SSRS, linkedservers, etc.) connecting to Postgres databases only. Optimizedfor extremely large rowsets, supports two phase commitprotocol (DTC enlistment), and provides better integration withSSIS.Greenplum DEGreenplum SEIntended for use from desktop applications connecting either toPostgres or Greenplum. Handles distribution policies, able towork with Greenplums’ forward-only cursors, utilizes gpload.Intended for use from servers (IIS, SSAS, SSIS,SSRS, linkedservers, etc.) connecting to Postgres and Greenplum databases.Optimized for extremely large rowsets, supports two phasecommit protocol (DTC enlistment), and provides betterintegration with SSIS.RedshiftIntended for use from any applications connecting to Redshift. Supports cursors and FastLoad via S3. The Redshift Provider can beinstalled side by side with either Postgres or Greenplum OLE DB providers.Note: Server Edition (SE) has all the features of Desktop Edition (DE), and adds more features as shown in the table above.Page 6
2 Installation and Product ActivationTo install the PGNP OLEDB Provider launch the installation module. The Module name may vary depending on theprovider variant/edition:Module nameDescriptionPGNP-Postgres-DE-1.4.3200.exeOLEDB Provider for Postgres, Desktop EditionPGNP-Postgres-SE-1.4.3200.exeOLEDB Provider for Postgres, Server EditionPGNP-Greenplum-DE-1.4.3200.exeOLEDB Provider for Greenplum, Desktop EditionPGNP-Greenplum-SE-1.4.3200.exeOLEDB Provider for Greenplum, Server EditionPGNP-Redshift-SE-1.4.0.3200.exeOLEDB Provider for RedshiftNote: Evaluation module name includes word “trial”, e.g.: PGNP-Postgres-DE-Trial-1.4.3020.exe.The latest releases have .msi extension, both 64-bit and 32-bit, res-SE-1.4.0.3456-x32.msi2.1.1 UI based installationWhen installation module is launched without using command line parameter /SILENT, the UI-based is used.First page of the installation application is shown below. Click Next through the wizard pages.Please read the License Agreement carefully. Click Next.Page 7
Specify the installation folder. By default, the Postgres and Greenplum providers are installed into C:\Program Files(x86)\Intellisoft\PGNP folder, and the Redshift – into C:\Program Files (x86)\Intellisoft\RSNP folder.Enter the Product Key (License Key). The installation module will automatically activate the product on the computer.This step requires an Internet connection. If Internet connection is not available, or you prefer to activate the productlater, select the check box on the bottom of the page (Register the product later using PGNPUpdate utility). Please referto PGNP Update utility guide below, specifically to section 5.4.2 “Working in Activation Mode”.Page 8
Note: If the provider was previously activated on the computer the installation will not require entering the product key. In that case the abovepage is not displayed, and installation proceeds to the next step.Click Next in following dialogs.Page 9
Note. To determine the installed version of the PGNP provider run appwiz.cpl from a command line (or go to Start- Control Panel- Programs andFeatures/Add or Remove Programs). Line starting with “PGNP OLEDB Provider” contains version and build number, e.g. “1.4.0.3200”. Anothermethod is to view PGNP.DLL or PGNP64.DLL Properties in Windows Explorer:The provider version can also be determined programmatically via call to pgnp getlicenceinfo.2.1.2 Unattended/silent installationThe product can be installed in an unattended/silent mode when no UI is shown and no user interaction is needed. Usecommand line parameters /SILENT and /activate key , where key is the license key.Example: PGNP-Postgres-SE-1.4.0.3364.exe /silent /activate A1726A2B156D4DA2829AF34335C7DF23There is also a special word “later” that can be used in place of the key . It allows unattended installation without theproduct activation. The product can be activated later using PGNPUpdate utility.Example: PGNP-Postgres-SE-1.4.0.3364.exe /silent /activate laterPage 10
Note: similarly, product can be uninstalled in an unattended mode using /SILENT parameter, for example: "C:\ProgramFiles (x86)\Intellisoft\PGNP\unins000.exe" /silentNote: if an error occurs during installation, a message box could be shown. This might require user interaction.MSI module can be installed using following command:msiexec.exe /i PGNP-Postgres-SE-1.4.0.3420-x64.msi /quiet PRODUCT KEY A1726A2B156D4DA2829AF34335C7DF232.1.3 Activation on a
Server 2008, Windows Server 2012, Vista, Windows 7, Windows 8; both 32-bit and 64-bit. The PGNP Provider works with the following versions of PostgreSQL database: PostgreSQL 8.0 and later, Greenplum 3.0
particularly in the design of Over Line Electrification (OLE) mast foundations. This paper summarises the traditional and revised approaches to OLE pile foundation design discusses their philosophical differences compares the results of calculations for OLE structural and foundation loads typical of the GWEP “Series 1” designs
ENGLISH 1. Ole: Hello. My name is Ole. What's your name? 2. Maria: Hello Ole. My name is Maria. 3. Ole: Nice to meet you. 4. Maria: Me too. VOCABULARY Norwegian English Class heter to be called Hyggelig å møte deg. Nice to meet you. expression I lige måde. Thanks, you too. phrase jeg I pronoun hei
OLE objects in OpenDocument OpenDocument files can embed OLE objects (at least on Windows). An OLE object is stored in a binary file inside the document. Microsoft OLE2 storage file (not really an open format ) An OLE Package may contain any file or a command line (potential malwa
At Ole Miss, you discover why. Hotty Toddy is cheered by Rebels of all ages. Whether it's "locking the Vaught" on an autumn Saturday, getting loud with Club Red in The Pavilion or going "Swayze Crazy" from the right field seats, you have the opportunity at Ole Miss to take part in a game-day experience that is second to none. Ole Miss .
NATIVE INSTRUMENTS GmbH Schlesische Str. 29-30 D-10997 Berlin Germany www.native-instruments.de NATIVE INSTRUMENTS North America, Inc. 6725 Sunset Boulevard 5th Floor Los Angeles, CA 90028 USA www.native-instruments.com NATIVE INSTRUMENTS K.K. YO Building 3F Jingumae 6-7-15, Shibuya-ku, Tokyo 150-0001 Japan www.native-instruments.co.jp NATIVE .
NATIVE INSTRUMENTS GmbH Schlesische Str. 29-30 D-10997 Berlin Germany www.native-instruments.de NATIVE INSTRUMENTS North America, Inc. 6725 Sunset Boulevard 5th Floor Los Angeles, CA 90028 USA www.native-instruments.com NATIVE INSTRUMENTS K.K. YO Building 3F Jingumae 6-7-15, Shibuya-ku, Tokyo 150-0001 Japan www.native-instruments.co.jp NATIVE .
React-Native Apps JS components render as native ones Learn once, write everywhere 13 Android Android SDKs Native UI JS Runtime React Native 3rd Party Libs NPM Pkgs (e.g., React) Bridge Your App Your App (JS) (Native UI & Modules) iOS iOS SDKs Native UI JS Runtime React Native 3 Party Libs NPM Pkgs (e
inquiry-based instruction supported 5E learning cycle . In the instruction based on 5E learning cycle method, teaching and learning activities and lesson plans were designed to maximize students active involvement in the learning process. The topics included in the lesson plans were about the three units of fifth-grade sciences book; they included: hidden strangles (microbes, viruses, diseases .
