How to Access External PostgreSQL Database from SAP On Linux?

[Former Member]

Hi all!

I have Linux-Orale11-ERP6.0. I need connect to an external database PostgreSQL on Linux. I installed the driver ODBC on Linux-Oracle11-ERP6.0 and command isql-v<alias> connect to PostgreSQL:

root@sap etc# isql -v <alias>

----

---

Connected!

sql-statement

help tablename

quit

----

---

SQL>

How do I now configure the Oracle DB Link?

[lbreddemann]

Hello Dmitry,

Markus is of course right - there is no direct way to access PostgreSQL databases from ABAP.

However, since you're using another DBMS from a vendor that loves to integrate everything into one database, there is an option to do that nevertheless.

It's called Oracle heterogeneous services that allows you to create a dblink to a ODBC datasource.

Once in a long forgotten time I wrote a blog on how to do that for a MaxDB database: [Suck that data out ! Connect from Oracle to MaxDB|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/6225] [original link is broken] [original link is broken] [original link is broken];.

Be very clear about that this is a technique that is used +_very_ +rarely. So, maybe I would not try to use the dblink directly in my ABAP code, but get the data into Oracle first (e.g. with a materialized view or a staging table) and use this to access it in ABAP.

Ah, well... all this is of course only possible if your license for Oracle allows this to do. If you bought the Oracle license via SAP bundled to your NetWeaver, then this option is not covered!

Anyway - hope this helps.

Lars

p.s.

You may also want to have a look at UD CONNECT from the BW component or you use a J2EE instance and JDBC access...

[Former Member]

Lars, thank you very much for your reply, I read your blog and I want to do the same.

There is a question. In the file listener.ora is the record:

(PROGRAM = hsodbc)

I have not found such a program. Where do I get?

[markus_doehr2]

You have to install Oracle heterogeneous services using the Oracle universal installer.

Markus

[Former Member]

I installed DG4ODBS.

Configured initH2P.ora, listener.ora and tnsnames.ora.

/oracle/DEV/112_64/hs/admin/initH2P.ora:

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = H2P
HS_FDS_TRACE_LEVEL = OFF
#HS_FDS_SHAREABLE_NAME = /usr/pgsql-9.0/lib/psqlodbc.so
#
# ODBC specific environment variables
#
#set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

/oracle/DEV/112_64/network/admin/listener.ora

################
# Filename......: listener.ora
# Created.......: created by SAP AG, R/3 Rel. >= 6.10
# Name..........:
# Date..........:
# @(#) $Id: //bc/701-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/LISTENER.ORA#1 $
################
ADMIN_RESTRICTIONS_LISTENER = on
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = IPC)
          (KEY = DEV.WORLD)
        )
        (ADDRESS=
          (PROTOCOL = IPC)
          (KEY = DEV)
        )
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = sap-dev)
          (PORT = 1527)
        )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = SUPPORT
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = DEV)
      (ORACLE_HOME = /oracle/DEV/112_64)
    )
#########I inserted###########
    (SID_DESC =
      (SID_NAME = H2P)
      (ORACLE_HOME = /oracle/DEV/112_64)
      (PROGRAM=dg4odbc)
    )
############################
  )

/oracle/DEV/112_64/network/admin/tnsnames.ora

################
# Filename......: tnsnames.ora
# Created.......: created by SAP AG, R/3 Rel. >= 6.10
# Name..........:
# Date..........:
# @(#) $Id: //bc/701-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/TNSNAMES.ORA#1 $
################
DEV.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = sap-dev)
          (PORT = 1527)
        )
    )
    (CONNECT_DATA =
       (SID = DEV)
       (GLOBAL_NAME = DEV.WORLD)
    )
  )
#########I inserted###########
H2P.WORLD  =
  (DESCRIPTION=
     (ADDRESS=
       (PROTOCOL=tcp)
       (HOST=77.120.107.XXX)
       (PORT=5432)
     )
     (CONNECT_DATA=(SID=H2P))
     (HS=OK)
    )

And when I run: tnsping H2P, I get an error:

[root@sap admin]# tnsping H2P

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 29-AUG-2011 15:37:00
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
/usr/sap/DEV/SYS/profile/oracle/sqlnet.ora
TNS-03505: Failed to resolve name

Although:

[root@sap admin]# tnsping DEV

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 29-AUG-2011 16:38:07
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
/usr/sap/DEV/SYS/profile/oracle/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP) (HOST = sap-dev) (PORT = 1527))) (CONNECT_DATA = (SID = DEV) (GLOBAL_NAME = DEV.WORLD)))
OK (10 msec)

what am I doing wrong?

[lbreddemann]

>Ping Utility for Linux: Version 11.2.0.2.0 - Production on 29-AUG-2011 16:38:07
> Copyright (c) 1997, 2010, Oracle.  All rights reserved.
> Used parameter files:
> /usr/sap/DEV/SYS/profile/oracle/sqlnet.ora
> Used TNSNAMES adapter to resolve the alias
> Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP) (HOST = sap-dev) (PORT = 1527))) (CONNECT_DATA = (SID = DEV) (GLOBAL_NAME = DEV.WORLD)))
> OK (10 msec)
> 
> what am I doing wrong?

Hmm... I guess you're using a different tnsnames.ora file here then the one you edited.

Check the TNS_ADMIN and ORACLE_HOME environment variables.

regards,

Lars

[Former Member]

Hello! I was able to configure the db-link!

But now there is another problem. Locally on the server in SQLplus I do SELECT:

select sysdate from "patch_version"@h2p;
.....
SYSDATE
---------------
31-AUG-11
31-AUG-11
31-AUG-11
31-AUG-11
31-AUG-11

126 rows selected.

But on the client computer in SQLplus I get:

SQL> select sysdate from "patch_version"@h2p;
select sysdate from "patch_version"@h2p
                                    *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified

What you need to configure on local computer?

[lbreddemann]

Hmm... you're positively sure that you're connected to the same database?

Have you checked the default domain for your SQLNET setup used by your remote computer?

Usually, once you're logged on to the database server, the connection to the dblink sohould work regardless of the client calling the dblink.

regards,

Lars

[Former Member]

I can not determine where the error occurred. I do everything on the server. If you connect the case, then db link is working:

sap:oradev 5> sqlplus system/pass

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 2 13:32:50 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from "patch_version"@h2p;
.....
--------------------------------------------------------------------------------
status
--------------------------------------------------------------------------------
   version
----------
       140 31-AUG-11
2011.08.31_1
successful
         0

127 rows selected.

If you connect so you get the error:

sap:oradev 2> sqlplus system/pass//172.16.21.31:1521/DEV

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 2 09:32:49 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from "patch_version"@h2p;
select * from "patch_version"@h2p
                              *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified

what and where I need to add that work?

[Former Member]

Hi all!

I was able to configure the dblink. The error was in the port number. Now everything works SQLplus. But the ABAP, get the error:

Runtime Errors         DBIF_DSQL2_SQL_ERROR
Date and Time          16.09.2011 11:37:23

 Short text
     An SQL error occurred when executing Native SQL.

 What happened?
     The error 28500 occurred in the current database connection "DEFAULT".

How to correct the error
    Database error text........: "ORA-28500: connection from ORACLE to a non-Oracle
     system returned this message:#No query has been executed with that
     handle;#Could not send Query(connection dead) {HY000,NativeErr = 1}#ORA-02063:
     preceding 3 lines from H2P"
    Database error code........: 28500
    Triggering SQL statement...: "FETCH NEXT "
the exact same query works in SQLplus, but ABAP does not work. Why is this happening?
    Internal call code.........: "[DBDS/NEW DSQL]"
    Please check the entries in the system log (Transaction SM21).

    If the error occures in a non-modified SAP program, you may be able to
    find an interim solution in an SAP Note.
    If you have access to SAP Notes, carry out a search with the following
    keywords:

    "DBIF_DSQL2_SQL_ERROR" " "
    "Z_TEST_DBLINK" or "Z_TEST_DBLINK"
    "START-OF-SELECTION"

Source Code Extract
Line  SourceCde
...
   58   EXEC SQL PERFORMING LOOP_WRITE.
   59 *    SELECT  "status" INTO :wa FROM "public"."v_test"@h2p
>>>>>     SELECT  "status", "id" INTO :wa FROM "public"."v_test"@h2p
   61   ENDEXEC.
   62  endtry.
   63 *      EXEC SQL.
   64 *        DISCONNECT :dbs
   65 *      ENDEXEC.
   66 *    CATCH cx_sy_native_sql_error INTO exc_ref.
   67 *      error_text = exc_ref->get_text( ).
   68 **      MESSAGE error_text TYPE 'I' INTO G_DUMMY.
   69 *      MESSAGE error_text TYPE 'I'.
   70 **      MESSAGE E001 INTO G_DUMMY.
   71 *ENDTRY

.

the exact same query works in SQLplus, but ABAP does not work. Why is this happening?

[lbreddemann]

Hmm... never seen this one before..

I'd check the workprocess developer trace to get the full error messages.

Also: is there any reason you put the column and table names in lower-case?

regards,

Lars

[Former Member]

tell me please, where you can see the full error mesage?

[lbreddemann]

I thought I already did!?

Work process developer trace -> transaction ST11.

regards,

Lars

[markus_doehr2]

To use an external database from ABAP there are two prerequisites necessary:

- a database client for the target database

- a database interface library (libdb<DB>s.so)

The latter is not existing for Postgres and hence you can't access the database directly from ABAP.

Markus

[Former Member]

hi Markus,

is it possible to create an ud connection to a postgre DB?