- What is the default user ID and password for demo.db and newly created databases?
- How do I recover a DBA password?
- How do I connect to a database?
- How do I determine which version of SQL Anywhere was used to create a database?
- Can I install and run two different versions of SQL Anywhere on the same computer?
- Can I install and run two copies of the same major version of SQL Anywhere on the same computer?
- Is it better to have one database server for each database or should I install multiple databases on a single database server?
- Why is the size of my database increasing or not decreasing as expected?
- How do I create an effective backup and recovery plan?
- How do I report a bug?
- What do I do when an assertion failure occurs?
- How do I improve the performance of my application or database server?
- How do I upgrade my SQL Anywhere software?
- Why is my application running slower after an upgrade?
- Why does my application not work after an upgrade?
- What operating systems are supported by the different versions of SQL Anywhere?
- What are the licensing requirements for a typical SQL Anywhere installation?
- What is the largest database size supported by SQL Anywhere?
- How do I migrate from another database product to SQL Anywhere?
- What competitive advantages does SQL Anywhere offer?
- I need to diagnose performance issues with a specific query. How do I create a graphical plan with statistics?
- Can I use the OUTPUT statement in a stored procedure?
- Where can I find more information?
The default user ID for demo.db and newly created databases is DBA and the default password is sql. You should change the password before deploying the database. For more information about default user IDs and passwords, see DBA authority.
A lost or forgotten DBA password cannot be recovered. If you require assistance, contact Technical Support (http://www.sybase.com/support).
The connection parameters required to connect to a database vary depending on the location of the application relative to the database server. The following connection scenarios assume that there is no communication encryption and there is only one database running on a database server.
In the following examples, you connect to the SQL Anywhere sample database, demo.db. This file is installed in the SQL Anywhere samples directory: samples-dir\demo.db. For information about the default location of samples-dir, see Installation directory structure.
Example 1: The database server is not running, and the database file is installed on the same computer as the application.
You must provide the correct user ID, password, connection and database file parameters for the database file. You may also need to specify the server name. To connect to the database, run a command similar to the following at a command prompt:
dbisql -c "UID=DBA;PWD=sql;ENG=demo;DBF=samples-dir\demo.db"
Example 2: The application and the database server are running on the same computer.
You must provide the correct user ID, password, and database server name. To connect to the database, run a command similar to the following at a command prompt:
dbisql -c "UID=DBA;PWD=sql;ENG=demo"
Example 3: The application and database server are running on the same portion (subnet) of a local area network.
You must provide the correct user ID, password, database server name, and LINKS=tcpip connection parameters for the database file. To connect to the database, run a command similar to the following at a command prompt:
dbisql -c "UID=DBA;PWD=sql;ENG=demo;LINKS=tcpip"
Example 4: The application and database server are running, but are not on the same subnet of a local area network.
You must provide the correct user ID, password, database server name, the LINKS=tcpip connection parameters for the database file, and the TCP/IP host and port protocol options. To connect to the database, run a command similar to the following at a command prompt:
dbisql –c "UID=DBA;PWD=sql;ENG=demo;LINKS=tcpip(host=server_host_name;port=2638)"
To help you determine which connection parameters are required to connect to a database, you can use the Connect Assistant in Interactive SQL, Sybase Central, and the SQL Anywhere Console utility. See SQL Anywhere database connections.
Databases created with SQL Anywhere 9.0.1 or later include the SYSHISTORY system view. The rows in this view that reflect INIT and UPDATE operations give information about when, and with what version, the database was created and upgraded. See SYSHISTORY system view.
To determine the version without starting the database, see DBCreatedVersion function.
Yes, you can install multiple major versions of SQL Anywhere on the same computer. For example, SQL Anywhere 9.0.2, 10.0.1, and 11.0.1 can all be installed and run independently.
However, you must use caution when starting a SQL Anywhere executable that has the same name in multiple versions (for example, dbisql or dbinit) to ensure that you are starting the correct version of the application. You can either specify the full absolute path using environment variables such as SQLANY10, or ensure the version of SQL Anywhere you want is specified first in your path.
For SQL Anywhere 10 and earlier for Windows using the SQL Anywhere installer:
Yes, you can install multiple copies of the same version of SQL Anywhere on the same computer. However, the SQL Anywhere installation program registers some drivers and components in the Windows registry, and there is only one copy of the registry. As a result, the ODBC and OLE DB drivers from the most recent installation are used.
For SQL Anywhere 11 for Windows using the SQL Anywhere installer:
No, you can only install a single copy of SQL Anywhere 11 on a computer.
For SQL Anywhere 11 and earlier for Linux or Unix using the SQL Anywhere installer:
Yes, you can install multiple versions in different locations.
For deployed embedded database applications:
Yes, deployed embedded database applications that include SQL Anywhere can be deployed with other SQL Anywhere installations on the same computer.
On Windows operating systems, the ODBC and OLE DB driver names in the registry should include the name of the application in which they are embedded. For example, the driver name SQL Anywhere 11 should be renamed <Application Name> SQL Anywhere 11.
You should run multiple databases on a single database server because this configuration optimizes the use of computer resources.
Having multiple database servers running on the same computer may result in a competition for resources, and (with dynamic cache resizing) this configuration may result in degraded or unpredictable performance. Degraded or unpredictable performance may be acceptable if you need to stop one database server for maintenance without affecting the others, or if you need to isolate errors to a single database server.
You should verify that you are correctly licensed for the installation option that you choose. For additional information, see Running multiple databases on a single database server.
Database pages are freed when all records on the page have been deleted. When a database page is freed, it becomes available for reuse, but it cannot be removed from the file. Future INSERT and UPDATE statements can use the freed pages.
Whenever modifications such as inserts, updates, or deletes are made to the database, entries are added to the rollback log, which is stored within the system dbspace. If many of these operations are performed before a commit is executed, the rollback log can become very large and may increase the size of the database.
The checkpoint log is stored at the end of the system dbspace. When the database server shuts down, the checkpoint log is truncated and the system dbspace shrinks. Pages that were freed by DELETE or TRUNCATE operations still remain within the database file for future reuse and cannot be removed from the file.
If the size of your database file is increasing, or is not decreasing as expected:
- Execute COMMITs frequently if you are using INSERT, UPDATE, or DELETE statements. Pages allocated for the rollback log are freed for reuse in the system dbspace when a COMMIT is performed.
- Execute CHECKPOINTs occasionally when you are using UPDATE or DELETE statements, or if you are using INSERT statements and large indexes are involved. Pages in the checkpoint log become available for reuse by the checkpoint log after each checkpoint.
- TRUNCATE TABLE can result in page-level deletes. In these cases, copies of the pages do not need to be added to the checkpoint log and individual row-level operations do not need to be added to the rollback log. Pages freed by TRUNCATE TABLE are only reusable after the next checkpoint. TRUNCATE TABLE results in page-level deletes when the following are true:
- There are no foreign keys to, or from, the table being truncated
- TRUNCATE TABLE is not being executed within a trigger
- TRUNCATE TABLE is not being executed in conjunction with an atomic operation
- The checkpoint log pages are written to the end of the system dbspace file. These pages are removed when the database is shut down.
Rebuilding the database can decrease the size of the database because the rebuilt database has fewer free pages.
See the following:
Log in to http://case-express.sybase.com.
Bugs reported using Case-Express are assigned a lower priority than cases opened through Technical Support. For priority issues, open a support case with Technical Support (http://www.sybase.com/support).
See the following:
SQL Anywhere performs best when the database is created with the same major version as the database server. Rebuild the database if you are experiencing performance problems and no longer need to run the database on earlier versions of SQL Anywhere. See Rebuilding databases and Improving database performance.
SQL Anywhere strives to ensure that applications continue to work after an upgrade. However, your application may be affected by behavior changes and the removal of previously supported features.
To determine if behavior changes have been made to your version of SQL Anywhere, or if features have been removed or deprecated, see Changes and Upgrading. Select the section relevant to your version of SQL Anywhere.
For descriptions of the different licensing options for SQL Anywhere 10 and later, including examples that show you how to apply your license, see SQL Anywhere Licensing.
This is dependent on the memory, CPU, and disk drive capacity of the computer on which SQL Anywhere is installed. See SQL Anywhere size and number limitations.
A number of our customers discuss the implementation of large databases in the SQL Anywhere newsgroups. For example, see this newsgroup thread.
To migrate to SQL Anywhere, you must import your data into a SQL Anywhere database. See Migrating databases to SQL Anywhere.
There are differences between products, such as different dialects of SQL. So, your application may need to be modified.
No. The OUTPUT statement can only be executed in Interactive SQL and cannot be used within a stored procedure. Use the UNLOAD statement within your stored procedure to save the result set generated by a SQL statement to a text file. If you use the UNLOAD statement in your stored procedure, information is unloaded from the database server and not from the client computer from which the command was executed. See UNLOAD statement.