I have a situation where a 12.0.1.3851 database server in a HA configuration is started with an explicit database name (using -n option) but using this database name the connection is refused. Error message indicates "specified database name not found"

Does any administrative tool exists with which I can query all databases in a server without the need to connect to any of the databases of the server?

asked 18 Apr '13, 11:10

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

edited 18 Apr '13, 13:00

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297


Yes, you would need to configure the "utility database", which would allow you to connect to the server, but not connect to a real database. What you can do on the utility database is very limited. You can't call sa_db_list() or even declare variables, but you can call next_database and db_name. Start by executing :

select next_database(NULL), db_name(next_database(NULL))

This will return the database_id and name of the first database loaded. You can then replace the NULL with the database_id returned from the first SQL to get the next loaded database_id and name, and continue to re-execute the statement until next_database returns NULL.

See the docs for more information on the utility database.

This is a little clunky in DBISQL, but it would be pretty trivial to write a simple ESQL app to loop through the databases.

Altenatively, you could manually parse the engine output log, since it also keeps track of whenever a database is started or stopped.

I. 04/18 11:30:33. SQL Anywhere Network Server Version 12.0.1.3853
[snip startup text]
I. 04/18 11:30:33. Now accepting requests
I. 04/18 11:31:11. Starting database "cons" (C:\temp\cons\cons.db) at Thu Apr 18 2013 11:31
I. 04/18 11:31:11. Transaction log: cons.log
I. 04/18 11:31:11. Database "cons" (cons.db) started at Thu Apr 18 2013 11:31
I. 04/18 11:31:31. Starting database "rem1" (C:\temp\rem1\rem1.db) at Thu Apr 18 2013 11:31
I. 04/18 11:31:31. Transaction log: rem1.log
I. 04/18 11:31:31. Database "rem1" (rem1.db) started at Thu Apr 18 2013 11:31
I. 04/18 11:31:32. Database "rem1" (rem1.db) stopped at Thu Apr 18 2013 11:31
I. 04/18 11:32:06. Starting database "rem2" (C:\temp\rem2\rem2.db) at Thu Apr 18 2013 11:32
I. 04/18 11:32:06. Transaction log: rem2.log
I. 04/18 11:32:06. Database "rem2" (rem2.db) started at Thu Apr 18 2013 11:32
I. 04/18 11:32:23. Database "cons" (cons.db) stopped at Thu Apr 18 2013 11:32
I. 04/18 11:32:39. Database server shutdown requested via server console
I. 04/18 11:32:39. Database server stopped at Thu Apr 18 2013 11:32
permanent link

answered 18 Apr '13, 11:58

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

In addition to Reg's answer, you can, in some cases, use the dblocate utility. As long as your server is listening for TCP/IP connections, you can do something like:

dblocate -dv -s myserver

And you would get output like:

SQL Anywhere Server Enumeration Utility Version 12.0.1.3887
Server Name           Address
-----------           -------
myserver              myhost:2638
   Running database: mydatabase
   Running database: myotherdatabase

1 server, 2 databases found

Using -d instead of -dv will give you a single line for each server (see below), but the databases column could be truncated if there are a lot of databases.

SQL Anywhere Server Enumeration Utility Version 12.0.1.3887
Server Name           Address         Database(s)
-----------           -------         -----------
myserver              myhost:2638     myotherdatabase,mydatabase

Also, as Volker said, you can use the -dh switch on the server to hide one or more databases from this list, and you can use the -sb 0 switch on the server to hide the entire server from dblocate. All things considered, Reg's solution of using the utility_db is more accurate, but in certain cases, this could be an alternative.

permanent link

answered 18 Apr '13, 12:51

Graeme%20Perrow's gravatar image

Graeme Perrow
9.6k379124
accept rate: 54%

edited 18 Apr '13, 12:54

You could also use the DBLOCATE tool, say with DBLOCATE -dv (apparently unless the databases were explicitly hidden with dbsrv12 -dh).

permanent link

answered 18 Apr '13, 12:39

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

edited 18 Apr '13, 12:55

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297

Sorry Volker, your answer wasn't there when I started writing mine. And I forgot to add the bit about hiding databases with -dh. +1

(18 Apr '13, 12:52) Graeme Perrow
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×61
×37
×15
×6

question asked: 18 Apr '13, 11:10

question was seen: 3,113 times

last updated: 18 Apr '13, 13:00