Error in configuring CIS on ASIQ-Part 2

[former_member232292]

Dear all,

   Thanks for your help in -- -- Now the CIS has been configured successfully and the test is OK--

(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...I don't know why the "create existing table" statement can't find that remote object.

   Please kind help again. Thanks

Regards

Eisen

[tayeb_hadjou · Accepted solution]

Hi Eisen,

In your initial .odbc.ini I see

Database=mpxtest

Check if the remote database in question is actually the simplex database:

forward to r1 ;

select db_name();

sp_iqtable load_test ;

sp_iqmpxinfo ;

If the remote database is multiplex, then it will not work as per proxy table restrictions.

---------

In a multiplex environment, you cannot create a proxy table that refers to the remote table defined within the multiplex.

CREATE EXISTING TABLE Statement - Reference: Statements and Options - SAP Library

----

Regards,

Tayeb.

[former_member232292 · Accepted solution]

Dear Tayeb,

   Thanks. Sorry I forget to say -- I've changed that odbc.ini to connect to that singleplex server, now it's --

[IQ_r1]

Description=SAP IQ 16 singleplex instance -- spxtest

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

ServerName=spxtest

Database=spxtest

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

    So inside the ini, the Database item is the correct "spxtest". And the output --

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

(DBA)> forward to r1 {select db_name()};

db_name()                                                                                   

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

spxtest                                                                                     

(1 rows)

Execution time: 0.535 seconds

(DBA)> forward to r1 {sp_iqtable load_test};

Table_name                                                                                                                       Table_type Table_owner                                                                                                                      Server_type                                                                                                                      location dbspace_id isPartitioned Remarks                                                                                                                          table_constraints                                                                                                                                                                                                                                                PartitionType isRlv

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

load_test                                                                                                                        BASE       DBA                                                                                                                              IQ                                                                                                                               Main          16387 N             (NULL)                                                                                                                           (NULL)                                                                                                                                                                                                                                                           None          F   

(1 rows)

Execution time: 0.724 seconds

(DBA)> forward to r1 {sp_iqmpxinfo};

server_id server_name                                                                                                                      connection_info                                                                                                                                                                                                                                                  db_path                                                                                                                                                                                                                                                          role             status   mpx_mode         inc_state                                        coordinator_failover                                                                                                                  current_version active_versions                                                                                                                                                                                                                                                  private_connection_info                                                                                                                                                                                                                                          mipc_priv_state  mipc_public_state rlvstore

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

(0 rows)

Execution time: 0.549 seconds

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

So we can see -- the target system is a singleplex not one node of this multiplex.  Please have a check. Thanks

Regards

Eisen

[former_member353243 · Accepted solution]

try

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

where the table owner is in upper case,  may be a case sensitivity issue perhaps?

[former_member232292 · Accepted solution]

Dear Danie,

    Thanks a lot... Yeah! it works -- due the case sensitivity-- it should be 'DBA' not 'dba'...

     But I'm quite confused -- both local and remote server are not case sensitive one, why in the "create existing table"  statement -- it's case sensitive?

     Thanks again.

Regards

Eisen

[tayeb_hadjou · Accepted solution]

Hi Eisen,

To see the query executed on remote server when "create existing table ..." is executed on local server, enable tracing

1- On remote server, restart wil

-zr all -zo trace_sql_rem.out

2- On local server

set option CIS_OPTION=7 ;

You will see in trace_sql_rem.out a query executed against system tables like SYSTABLE and SYSUSERPERMS to look for that remote table "load_test" :

"select cast(null as char(128)) as "TABLE_CAT","SYSUSERPERMS"."user_name" as "TABLE_SCHEM",

"SYSTABLE"."table_name" as "TABLE_NAME",if "table_type" = 'VIEW'  .....

......

"

Connect to remote server using dbisql and execute the query above.

You will see the result, likely empty (0 rows).

Test with user 'dba' and 'DBA'.

Double check case sensistivity property:

select db_property('CaseSensitive');

Regards,

Tayeb.

[former_member232292 · Accepted solution]

Dear Tayeb,

    Oh... Got it... Thanks a lot...

    Now I can see -- the output of "select db_property('CaseSensitive')" is 'ON'...

Regards

Eisen