isql command for Sybase IQ does not work

[former_member189724]

Hi all

I have installed the Sybase IQ 16.0 SP8 on a linux SLES environment.

Now itry to create the backups for the database.

I can connect with dbisql without any problems.

The connneciton string is like this:

dbisql -c "uid=dba;pwd=<password>;eng=tss126;dbn=SAPIQDB"

If i try to connect with isql igot always this error message:

CT-LIBRARY error:

        ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.

The connection string that I use is like this:

isql -X -S TSS126 -D SAPIQDB -U DBA -P sql

Here are the information from the SAP Control Center for this DB:

Can someone help me?

Regards Stefan

[SybDBA · Accepted solution]

Hi Stefan,

I think isql feature is not there in Sybase IQ 16.0 SP8.

As there is no isql application inside the ~/IQ-16_0/bin64/

You can use the same feature as isql using the below connection string:

dbisql -c "uid=dba;pwd=<password>;eng=tss126;dbn=SAPIQDB" -nogui

--

Vivek

[tayeb_hadjou · Accepted solution]

In IQ 16 installation, isql utility is located in

%SYBASE%\OCS-15_0\bin in Windows or

$SYBASE/OCS-15_0/bin in Unix

where  %SYBASE% or $SYBASE is the IQ installation home directory.

[c_baker · Accepted solution]

You now must install isql/isql64 separately from IQ by installing the ASE SDK (older releases of IQ included an SDK installation.  Newer releases do not).

dbisql uses a different protocol (cmdseq) than isql (TDS) to communicate with IQ.  The listener port defined for IQ can speak both protocols.

isql requires a directory services layer to resolve the server (-S) parameter passed to the isql command line.  This is typically the 'interfaces' file found in the $SYBASE directory where the SDK (e.g. OCS-15_0) containing isql is installed.  The interfaces file entry must take the form:

iqdemo

master tcp ether <host dnsname or address> 2638

query tcp ether <host dnsname or address> 2638

e.g.

iqdemo

master tcp ether 10.10.10.10 2638

query tcp ether 10.10.10.10 2638

(on Windows the interfaces file is sql.ini stored in the %SYBASE%\ini\ directory and has the form:

[iqdemo]

master=TCP,10.10.10.10,2638

query=TCP,10.10.10.10,2638

)

the servername can be either the IQ database name (dbn) or the IQ logical server name (-n),  (Typically I like to use the dbn as this will ensure connecting to the correct database in a SQLAnywhere environment that might host >1 database in a server).  This also means that the isql -D parameter is not required (it only works with ASE anyway).

Given the above example, the dbisql connection will be:

dbisql -c "uid=<uid>;pwd=<pwd>;dbn=iqdemo;eng=<iqengname>" -x "tcpip(host=10.10.10.10;port=2638)"

--(not all properties might be required, but I find this typically is what can get across the network, even if the IQ engine is not broadcasting)

another way with dbisql is:

dbisql -c "uid=dba;pwd=sql" -host 10.10.10.10 -port 2638

The corresponding isql command (assuming the interfaces has been set up) is:

isql -U dba -P sql -S iqdemo

When connecting with isql vs. dbisl, there are also differences in how the connection is managed - e.g. chained vs. unchained mode, due to the interface used.

For completeness - there are also 2 JDBC drivers than can be used with IQ - the IQ/SQLAnywhere native JDBC/ODBC driver and jConnect (Type 4 wirelevel TDS).

dbisql uses the native JDBC/ODBC driver.  This driver and the IQ/SQLAnywhere ODBC driver support IQ client-side loading.  jConnect does not.

To connect to IQ using the native JDBC/ODBC driver, the URL is of the form

jdbc:sqlanywhere:uid=dba;pwd=sql;eng=mda_analysis;commlinks=tcpip(host#10.10.10.10;port#2641)

using the class sybase.jdbc4.sqlanywhere.IDriver

--(note how the embedded '=' signs are replaced by '#')

For jConnect the URL is:

jdbc:sybase:Tds:10.10.10.10:2638?SERVICENAME=iqdemo&USER=dba&PASSWORD=sql

using the class com.sybase.jdbc4.jdbc.SybDriver

Obviously you can set a properties object to pass parameters for the drivers, but the important thing here is that there are differences when using a TDS-based driver/application (jConnect, isql) when connecting to IQ vs. ASE.  In this case, the ASE connection parameters for an ASE database for jConnect use the following URL:

jdbc:sybase:Tds:10.10.10.10:2638/iqdemo?USER=dba&PASSWORD=sql

--(note the difference in how the database is called.  This is similar to using the -D command in isql)

HTH

Chris

[former_member182090 · Accepted solution]

Just to add to the earlier comments, you should normally use dbisql with IQ. However, using the Ct-library-based 'isql' utility can sometimes be more useful. For example:

- when you need a command-line ASCII interface (i.e. when you're in a remote shell session) and you need to run multi-line SQL commands. dbisql doesn't do this well, but isql does.

- in case you need to debug long SQL script files  that you're executing from the command line. When an error occurs in such a script, it is easier to locate the error with isql (using the -e option), than with dbisql.

- command batches that return multiple result sets are easier handled with isql than with dbisql

To avoid unexpected behaviour when using isql instead of dbisql, you should ensure the session-level settings are set to the Watcom defaults. I keep this stored proc around to execute after I have connectd to IQ or SQL Anywhere with isql:

CREATE OR REPLACE procedure dbo.set_watcom_defaults()

begin

if connection_property('CommProtocol') = 'TDS'

then

    /* This is a TDS connection, set session options to the Watcom defaults.

     * This overrides the TDS defaults set at connection time by sp_tsql_environment()

     */

    set temporary option ansinull = 'ON';

    set temporary option tsql_variables = 'OFF';

    set temporary option ansi_blanks = 'OFF';

    set temporary option chained = 'ON';

    set temporary option quoted_identifier = 'ON';

    set temporary option allow_nulls_by_default = 'ON';

    set temporary option on_tsql_error = 'CONDITIONAL';

    set temporary option isolation_level = '0';

    set temporary option date_format = 'YYYY-MM-DD';

    set temporary option timestamp_format = 'YYYY-MM-DD HH:NN:SS.SSS';

    set temporary option time_format = 'HH:NN:SS.SSS';

    set temporary option date_order = 'YMD';

    set temporary option escape_character = 'ON';

    set temporary option ansi_substring = 'ON';   -- NB: default=ON for SA, OFF for IQ.

end if;

end

[c_baker · Accepted solution]

The SDK appears to be installed with IQ 16 after all.

I stand corrected.

Chris

[former_member189724 · Accepted solution]

Hi Vivek

Thanks for your input.

With the "dbisql ....-nogui" its possible to connect on commandline.

I'm still trying to connect with the command "isql". In addition I have done the following later:

I have installed the ASE SDK 15.7 client. And then I created the interfaces file.

But i have still the same error:

CT-LIBRARY error:

        ct_connect(): directory service layer: internal directory control layer error: Invalid syntax found in interfaces file.

I will create a batch file for running backups. This one i will start over the crontab.

What ist the best way to do this?

Regards Stefan

[former_member182090 · Accepted solution]

As the message says, there is an error in your interfaces file (or SQL.INI file). See Chris Baker's earlier reply for the formatting.

What does your interfaces file look like?

[Gisung · Accepted solution]

Hi,

You can configure connection info using dscp utility like below.

~/IQ-16_0/bin64>dscp

>> open

ok

Session 1 InterfacesDriver>> add SAPIQDB

Service: [ASE]

Transport Type: [tcp]

Transport Address: 10.10.10.10 2638

Transport Type: [tcp]

Transport Address:

Security Mechanism [] :

HA Failoverserver:

Retry Count:

Retry Delay:

Added SAPIQDB

Session 1 InterfacesDriver>> list all

Distinguish name: SAPIQDB

  Server Entry Version: 1

  Server Name: SAPIQDB

  Server Service: Adaptive Server Enterprise

  Server Status: 4 (Unknown)

  Server Address:

    Transport Type: tcp

    Transport Address: 10.10.10.10 2638

[hpita:/gjang]cat interfaces

SAPIQDB

        master tcp ether 10.10.10.10 2638

        query tcp ether 10.10.10.10 2638

===

Gi-Sung Jang

[former_member189724 · Accepted solution]

Hi Gi-Sung Jang

I don't have this program in the folder /IQ-16_0/bin64

Regards Stefan

[former_member189724 · Accepted solution]

Hi Rob

Here is the entry from my interfaces fiel:

SAPIQDB

master tcp ether tss126 2640

query tcp ether tss126 2640

I reread the message from Chris Baker. I have installed the Sybase SDK with all components, but i have still this error.

Regards.

[Gisung · Accepted solution]

Hi

If you installed SDK/OCS on your system, you can find a dscp in below location.

- >> find ./ -name dscp

~/OCS-15_0/bin/dscp

And can you change the interfaces file using "IP address or hostname" not tss126 like below?

[interfaces]

master tcp ether tss126 2640 

query tcp ether tss126 2640

=>

master tcp ether 10.10.10.10 2640

query tcp ether 10.10.10.10 2640

==

Gi-Sung Jang

[former_member189724 · Accepted solution]

Hi Gi-Sung Jang

I have found the file. The file was not in IQ-16_0/bin64. The file was in IQ-16_0/bin64

Now I can connect with isql.

isql -U dba -P >password> -S SAPIQDB

Thanks for your help.

Regards Stefan

[Gisung · Accepted solution]

Hi,

I'm glad to help you.

==

Gi-Sung Jang

[tayeb_hadjou]

Hi Stefan,

Have you configured IQ server enrty in interfaces (Unix) or sql.ini (in Windows) file?

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc30056.1604/doc/html/san1282692606704...

Regards,

Tayeb.

[SybDBA]

Hi Tayeb,

Please explain me the difference between isql & dbisql, as it is given here:

[iqug] Difference between isql &amp;amp; dbisql

but I am unable to understand the stuff they have related to the loading or whatever else.

--

Regards.

Vivek

[tayeb_hadjou]

The utility dbisql is recommended with IQ or Sql Anwyhere database, especially for DBA tasks, Loads, etc. It uses odbc or jdbc, and nativelly uses Watcom Sql language.

dbisql can work in GUI (default) or in interactive (option -nogui).

Isql is a utility based on Open Client libraries and intially designed for SAP Sybase ASE (Adaptive server Enterprise), but compatible with IQ and Sql Anywhere.

An example where DBAs perefer isql instead of dbisql, is execution of Transact sql scripts).

Connection to IQ with open client isql, will implictly set, via login procedure, options for Transact Sql compatibility, like Ansinull, Quoted_identifier, Chained, etc.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00801.1604/doc/html/san1281565042320...