on 2015 Apr 16 10:23 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
Hi Stefan,
Have you configured IQ server enrty in interfaces (Unix) or sql.ini (in Windows) file?
Regards,
Tayeb.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Tayeb,
Please explain me the difference between isql & dbisql, as it is given here:
[iqug] Difference between isql &amp; dbisql
but I am unable to understand the stuff they have related to the loading or whatever else.
--
Regards.
Vivek
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.
User | Count |
---|---|
61 | |
10 | |
10 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.