S4 HANA連接配接其他資料庫（oracle,sqlserver)

配置步驟很簡單：

1.配置連接配接：dbco

2.測試連結：sm38 執行ADBC_TEST_CONNECTION

可真要配置起來，那可就是兩行淚。

安裝S4系統後直接配置資料庫連接配接，如果你不遇到錯誤，那就真是遇到神了，是以錯誤是必須的，針對oracle和sql server的解法還不一樣，但其實也一樣，那就是安裝驅動。

第一部分：sql server連接配接錯誤的解法：

測試連結時的錯誤如下:

 *** ERROR => DlLoadLib()==DLENOACCESS - dlopen("/usr/sap/S4D/D03/exe/dbmssslib.so") FAIL

"libodbc.so.1: cannot open shared object file: No such file or directory"  [dlux.c

 {root-id=06BE636F524C1ED9ACB34750EB54652F}_{conn-id=00000000000000000000000000000000}_0

 *** ERROR => Couldn't load library '/usr/sap/S4D/D03/exe/dbmssslib.so'

B  *** ERROR => Couldn't load library '/usr/sap/S4D/D03/ex

 [dbcon.c      7091]

B  ***LOG BYG=> could not load library for database connec

B  ***LOG BZY=> unexpected return code 8192 calling DBDS [

B  *** ERROR => Unexpected RC when calling dsql_connect

s4dev:s4dadm 54> cdexe

s4dev:s4dadm 57> ls dbmss

s4dev:s4dadm 58> ls dbms*

s4dev:s4dadm 59> rpm -q glibc

glibc-2.22-61.3.x86_64

s4dev:s4dadm 60> 

步驟一：先到微軟官網下載下傳檔案

https://docs.microsoft.com/zh-cn/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-2017

msodbcsql17-17.0.1.1-1.x86_64.rpm                  12-Mar-2018 19:03             3888300

msodbcsql17-17.1.0.1-1.x86_64.rpm                  03-Apr-2018 23:33             3919661

msodbcsql17-17.2.0.1-1.x86_64.rpm                  11-Jul-2018 05:22             4127205

msodbcsql17-17.3.1.1-1.x86_64.rpm                  26-Feb-2019 04:43              737601

unixODBC-2.3.7-1.suse.x86_64.rpm                   26-Feb-2019 05:33              218160

下載下傳unixODBC-2.3.7-1.suse.x86_64.rpm 和msodbcsql17-17.3.1.1-1.x86_64.rpm 

s4dev:/tmp # rpm -ivh unixunixODBC-2.3.7-1.suse.x86_64.rpm

error: open of unixunixODBC-2.3.7-1.suse.x86_64.rpm failed: No such file or directory

s4dev:/tmp # rpm -ivh unixODBC-2.3.7-1.suse.x86_64.rpm

warning: unixODBC-2.3.7-1.suse.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:unixODBC-2.3.7-1.suse            ################################# [100%]

s4dev:/tmp # rpm -ivh unixunixODBC-2.3.7-1.suse.x86_64.rpm

error: open of unixunixODBC-2.3.7-1.suse.x86_64.rpm failed: No such file or directory

s4dev:/tmp # rpm -ivh msodbcsql17-17.3.1.1-1.x86_64.rpm

warning: msodbcsql17-17.3.1.1-1.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY

Preparing...                          ################################# [100%]

The license terms for this product can be downloaded from

https://aka.ms/odbc17eula and found in

/usr/share/doc/msodbcsql17/LICENSE.txt . By entering 'YES',

you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)

YES

Updating / installing...

   1:msodbcsql17-17.3.1.1-1           ################################# [100%]

步驟二：解決連結問題

安裝驅動後進行dbco 測試，還是有錯誤：

M  *** ERROR => DlLoadLib()==DLENOACCESS - dlopen("/usr/sap/S4D/D03/exe/dbmssslib.so") FAILED

  "libodbc.so.1: cannot open shared object file: No such file or directory"  [dlux.c       550]

M  {root-id=06BE636F524C1ED9ACB3F0548BAEE77A}_{conn-id=00000000000000000000000000000000}_0

B  *** ERROR => Couldn't load library '/usr/sap/S4D/D03/exe/dbmssslib.so'

 [dbcon.c      7091]

B  ***LOG BYG=> could not load library for database connection ORGANIZATIONDB [dbds         984]

B  ***LOG BZY=> unexpected return code 8192 calling DBDS [dbacds       2106]

B  *** ERROR => Unexpected RC when calling dsql_connect

網上可以找到這個錯誤的解法：

5. libodbc.so.1: cannot open shared object file

Environment:

 - SuSE Linux 12

Symptom:

The workprocess trace points to the following error: 

M *** ERROR => DlLoadLib()==DLENOACCESS - dlopen("/usr/sap/SID/DVEBMGS00/exe/dbmssslib.so") FAILED 

"libodbc.so.1: cannot open shared object file: No such file or directory" [dlux.c 521] 

M {root-id=0050568639A41ED6B1EAF03921567B8F}_{conn-id=00000000000000000000000000000000}_0 

B *** ERROR => Couldn't load library '/usr/sap/SID/DVEBMGS00/exe/dbmssslib.so' 

Solution:

Create the symbol links: 

     ln -s /usr/lib64/libodbc.so.2.0.0 /usr/lib64/libodbc.so.1

     ln -s /usr/lib64/libodbcinst.so.2.0.0 /usr/lib64/libodbcinst.so.

安裝這個方案建立連結

s4dev:/usr/sap/S4D/D03/exe # cd /usr/lib64

s4dev:/usr/lib64 # ls libodbc*

libodbc.so    libodbc.so.2.0.0  libodbccr.so.2      libodbcinst.so    libodbcinst.so.2.0.0

libodbc.so.2  libodbccr.so      libodbccr.so.2.0.0  libodbcinst.so.2

s4dev:/usr/lib64 # ln -s /usr/lib64/libodbc.so.2 /usr/lib64/libodbc.so.1

s4dev:/usr/lib64 # ln -s /usr/lib64/libodbcinst.so.2 /usr/lib64/libodbcinst.so.1

完成後dbco測試即可成功。

第二部分：Oracle連接配接錯誤的解法：

微軟以人性化著稱都這樣，是以連接配接oracle那就更不省事。在dbco建立連接配接的時候，你連連接配接字元串都不好寫。

步驟一：下載下傳oracle client。

https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

怕少這少那，一口氣下載下傳并安裝。

oracle-instantclient19.3-sqlplus-19.3.0.0.0-1.x86_64.rpm

oracle-instantclient19.3-basic-19.3.0.0.0-1.x86_64.rpm

oracle-instantclient19.3-devel-19.3.0.0.0-1.x86_64.rpm

oracle-instantclient19.3-tools-19.3.0.0.0-1.x86_64.rpm

安裝好了以後就會有下面這個目錄

s4qas:/usr/lib/oracle/19.3/client64/lib/network/admin 

這個裡面的文本檔案有一段話：

============================================================================

This is the default directory for Oracle Network and Oracle Client

configuration files. You can place files such as tnsnames.ora, sqlnet.ora

and oraaccess.xml in this directory.

NOTE:

If you set an environment variable

TNS_ADMIN to another directory containing

configuration files, they will be used instead of the files in this default

directory.

============================================================================

開始的時候我不知道有這個目錄，所有自己建了client64/network/admin目錄，然後把tnsnames.ora放在這個目錄下。

後來于是又搞了一通環境變量，目的為了指向自己建的目錄。

s4qas:/home/s4qadm#vi .bashrc

export LD_LIBRARY_PATH=/usr/local/lib:/usr/lib/oracle/19.3/client64/lib

export ORACLE_HOME=/usr/lib/oracle/19.3/client64

export TNS_ADMIN=/usr/lib/oracle/19.3/client64/network/admin

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

dbco測試還是錯誤，又把/etc/profile的全局環境變量修改了。

s4qas:/etc # vi profile

s4qas:/etc # echo $TNS_ADMIN

看起來環境變量也是生效的。

可是還是錯誤。

步驟二：解決連結庫問題

錯誤日志如下：

B  Loading DB library '/usr/sap/S4Q/D03/exe/dboraslib.so' ...

M  *** ERROR => DlLoadLib()==DLENOACCESS - dlopen("/usr/sap/S4Q/D03/exe/dboraslib.so")

FAILED

  "libclntsh.so.11.1: cannot open shared object file: No such file or directory"  [dlux.c       550]

M  {root-

id=06E38A3CBB1E1EE9B3F32F46465633D6}_{conn-id=00000000000000000000000000000000}_0

B  *** ERROR => Couldn't load library

'/usr/sap/S4Q/D03/exe/dboraslib.so'

 [dbcon.c      7091]

B  ***LOG BYG=> could not load library for database connection US_WMS_PRD [dbds         984]

B

 ***LOG BZY=> unexpected return code 8192 calling DBDS [dbacds       2106]

B  *** ERROR => Unexpected RC when calling dsql_connect

 [dbacds.c    

2107]

發現/usr/lib/oracle/19.3/client64/lib中有libclntsh.so.11.1 ，将這個檔案拷貝到/usr/sap/S4Q/D03/exe/，錯誤解決。

步驟三：解決tnsname問題

這個時候不出錯了，tnsname也有了，但是dbco測試的時候報：

ORA-12154: TNS: could not resolve the connect identifier specified

把tnsname修改來修改去，又弄出一個問題：

ORA-12505: TNS:listener does not currently know of SID given in connect desc

雖然是錯誤，但是錯誤在變化，說明修改是有效果的。

最後把tnsname修改成：

ORCL.WORLD=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS =

     (PROTOCOL = TCP)

     (HOST = 10.10.10.105)

     (PORT = 1521)

    )

    )

    (CONNECT_DATA =

      (SID = orcl)

      (GLOBAL_NAME = orcl.WORLD)

    )

  )

測試成功。