Error in configuring CIS on ASIQ

[former_member232292]

Dear All,

   I'm just trying to configure the IQ connect to another IQ via ODBC. So here are the steps --

1. I create a file name .odbc.ini in $SYBASE

asiq160@bigDataIQ:~> set|grep SYBASE

SYBASE=/asiq160

SYBASE_JRE7=/asiq160/shared/JRE-7_0_7_64BIT

SYBASE_JRE7_32=/asiq160/shared/JRE-7_0_7_32BIT

SYBASE_JRE7_64=/asiq160/shared/JRE-7_0_7_64BIT

SYBASE_OCS=OCS-15_0

asiq160@bigDataIQ:~> cat .odbc.ini

[IQ_r1]

Description=SAP IQ 16 mpxtest_r1

Driver=/asiq160/IQ-16_0/lib64/libodbc.so

EngineName=mpxtest_r1

Database=mpxtest

Commlinks=tcpip(host=10.128.244.161;port=2642)

2. Then login the IQ server to add a remote server --

create server r1 class 'SAODBC' using 'IQ_r1';

create externlogin dba to r1 remote login dba identified by sql;

3. Then the test fails --

(DBA)> forward to r1 {select @@servername};

Could not execute statement.

  Unable to connect to server 'r1': [Sybase][ODBC Driver][SQL

  Anywhere]Connection error: TCPIP requires a server name

  SQLCODE=-656, ODBC 3 State="HY000"

  Line 1, column 1

  forward to r1 {select @@servername}

Press ENTER to continue...

    I think I've already put the servername to the dsn file and it can work fine with dbisql --

asiq160@bigDataIQ:~> dbisql -c "dsn=IQ_r1;uid=dba;pwd=sql" -nogui

(DBA)> select @@servername;

@@servername

--------------------------------------------------------------------------------------------------------------------------------

mpxtest_r1

(1 rows)

Execution time: 0.033 seconds

     So I don't know what's the error in that remote server configuration. Would anyone help on this? Thanks in advance for any idea.

Regards

Eisen

[tayeb_hadjou · Accepted solution]

Hi Eisen,

Use IQODBC server class instead of SAODBC.

and enable remote tracing in both IQ servers:

Set option CIS_OPTION=7;

Traces are sent to srvlog files.

Regards,

Tayeb.

[former_member232292 · Accepted solution]

Dear Tayeb,

    Thank you for your help.  Here's the output --

   I've changed the class to 'IQODBC' but issue still the same. And here's the error messages from srvlog --

I. 08/09 09:16:57. Finished checkpoint of "mpxtest" (mpxtest.db) at Tue Aug 09 2016 09:16

I. 08/09 09:17:21. Connect to 'r1' class 'iqodbc' using 'IQ_r1'

I. 08/09 09:17:21. ODBC error (08001). [Sybase][ODBC Driver][SQL Anywhere]Connection error: TCPIP requires a server name

I. 08/09 09:17:21. ODBC error (08001). [Sybase][ODBC Driver][SQL Anywhere]Connection error: TCPIP requires a server name

I. 08/09 09:17:21. Disconnect from server 'r1'

    Thanks

Regards

Eisen

[tayeb_hadjou · Accepted solution]

The odbc library is libdbodbc16.so not libdbodbc.so in $SYBASE IQ-16_0/lib64

For more details see

KBA 2098055 - Moving data from one SAP IQ server to another SAP IQ server using Insert..Location

[tayeb_hadjou · Accepted solution]

modify

Driver=/asiq160/IQ-16_0/lib64/libodbc.so

to

Driver=/asiq160/IQ-16_0/lib64/libdbodbc16.so

[former_member232292 · Accepted solution]

Dear Tayeb,

     Thanks. But this  libodbc.so in the DSN file is just that libdbodbc16.so -- As in IQ16 -- it's a link to that libdbodbc16.so...

asiq160@bigDataIQ:~/IQ-16_0/lib64> ls -l libodbc.so

lrwxrwxrwx 1 asiq160 sybase 16 Jul  6 08:35 libodbc.so -> ./libdbodbc16.so

   Please have a check. Thanks

Regards

Eisen

[tayeb_hadjou · Accepted solution]

Add these options in dsn (.odbc.in) and try:

DOBROADCAST=NONE

VERIFY=NO

[c_baker · Accepted solution]

Try the following for your CommLinks parameter in your DSN:

Commlinks=tcpip(host#10.128.244.161;port#2642)

The property may be getting confused by multiple '=' signs.

Chris

[tayeb_hadjou · Accepted solution]

Also, be aware that any modification in .odbc.ini will be seen immediately by dbisql BUT not by CIS.

CIS will take it in account ONLY after restarting local IQ server.

So if dbisql works and not CIS, this is probably because they do no see same dsn content.

To debug further, restart remote IQ server with -z option.

[former_member232292 · Accepted solution]

Thanks for all your warm hearted and kind help.

    And the correct answer should be Tayeb's answer -- the ODBC for CIS only works after IQ server restarted ... So after restart the IQ server which ran "create server..." then the issue gone...  Now it's correct --

(DBA)> forward to r1 {select count(*) from spxtest.dba.load_test};

             count()

--------------------

                   0

(1 rows)

   But still some problem in CIS -- when I create a proxy table with --

(DBA)> create existing table pr_spx_load_test at 'r1.spxtest.dba.load_test';

Could not execute statement.

  The remote table 'r1.spxtest.dba.load_test' could not be found

  SQLCODE=-666, ODBC 3 State="HY000"

  Line 1, column 1

  create existing table pr_spx_load_test at 'r1.spxtest.dba.load_test'

Press ENTER to continue...

   But as my previous test -- we can see the CIS can access that load_test on remote server r1...

   Please kind help again.

Regards

Eisen

[Gisung · Accepted solution]

Hi,

I have a couple of questions.

Are there any differences between two IQ such as collation and so on..?

And Is it same when you try to execute through other client tool??

Best Regards,

Gi-Sung Jang

[former_member232292 · Accepted solution]

Dear Gi-sung,

   Thanks. Here are the answers:

1. They are both utf8. The difference is -- the local IQ server is a multiplex and the remote server is a singleplex one.

2. I only have dbisql--no other client tool...

     HTH.

Regards

Eisen

[tayeb_hadjou · Accepted solution]

Hi Eisen,

I suggest post new discussion for the last CIS issue (remote table not found).

Regards,

Tayeb.

[former_member232292 · Accepted solution]

Sure. Will do

[jmtorres]

Hi Eisen,

Have you tried using <hostname> instead of IP address in "Commlinks" section of .odbc.ini?

So instead of using:

Commlinks=tcpip(host=10.128.244.161;port=2642)

use -> Commlinks=tcpip(host=<your_host_name>;port=2642)

assuming that your IP address is mapped correctly to your host name

Regards

Jose

[former_member194571]

Hi Eisen,

The strange thing is that the dbisql connection works but the CIS connection doesn't.

Did you consider to replace EngineName by ServerName? It shouldn't make a difference, but EngineName is deprecated and the error message sounds pretty clear, so it might be worth a try.

Does your IQ server run under the same account and with the same environment that you used for the dbisql session?

Are your two IQ servers and the dbisql session running on the same or different machines?

- Volker

[markmumy]

Remember that dbisql does not use odic directly to connect.  It takes the options/parameters from an ODBC connection and uses them to form it's own connection parameters and connection string.

Likely it is the deprecated EngineName parameter that is causing issues.  Adding VERIFY and DOBROADCAST should help skirt it too.

Mark

[former_member232292]

Dear Volker and Mark,

   Thanks. And I tested on IQ16 ... Both "EngineName" and "ServerName" can work fine. The issue is on the local server must be rebooted after I wrote that .odbc.ini to access it.

   Thanks.

Regards

Eisen