Sybase数据库技术，数据库恢复专家

1. The application loaded one of SQL Anywhere's client libraries (JDBC driver, ODBC

driver, DBLIB), which automatically install a signal handler.

2. The application installs its own signal handler function

3. An application fault happens which causes the application's signal handler to call the SA signal handler.

The SA signal handler will return without causingan abort and the application fault would have been re-triggered. The re-trigger of the signal and the return without handling the signal generated 100% CPU utilization. This has been fixed.

Note, existing databases with this problem can be corrected by dropping and recreating the logical indexes sharing a physical index. Whether or not an existing database has an instance of this problem can be determined by checking the physical_index_id and the file_id fields of the system view SYS.SYSIDX. The problem cases exist in a database when two indexes on the same table have the same physical_index_id values but their file_id values differ.

This can be worked around by using dbisql or by correcting the output_nulls Interactive SQL option using the statement:

set option output_nulls = ''

Note that a simple workaround to the problem is to execute:

STOP EXTERNAL ENVIRONMENT CLR

on the connection that is having difficulty executing CLR calls. Unfortunately, stopping the CLR External Environment will have the side effect of dropping all static variables for that particular connection.

- "Interactive SQL is currently configured to quit if a SQL statement fails to execute"

- "You are connected to an database which is not explicitly supported"

- "ODBC tracing is currently enabled"

This has been corrected and these warnings are now suppressed when "-q" is used.

A workaround for this problem is to add a GROUP BY clause.

1. The "Fast launcher" option was enabled

2. The "Connect" window was opened and left open in one DBISQL window

3. A "Connect" window was opened and closed from another DBISQL window

This has been fixed. To workaround the problem, close the "Connect" window and reopen it.

For existing databases with IMMEDIATE text indexes that could be affected by this issue, dropping and recreating the text index is recommended.

With this change a SQL error is now generated only if the http option value is malformed or none of the charsets within the value are supported by SQL Anywhere. In addition, the run-time selection of a suitable response charset has changed to provide more control over the charset selection. Primarily, given that the union of server and client charset lists are empty, a charset is now selected based on the server's AcceptCharset http option value not from the client's Accept-Charset request header. The old behaviour is supported by allowing an asterisk (*) to be specified within the AcceptCharset http option list. An asterisk has the meaning that, should the client/server charset union be empty, try to use the preferred charset specified by the client. If none are found, then select from the server's AcceptCharset http option list. A summary of the processing priority of the various cases follow:

Processing Priority cases:

1 - If a charset can be selected from the union of charsets from the AcceptCharset http option and the Accept-Charset HTTP request header, then it will be used (no change in behaviour).

2 - If the client sends an Accept-Charset HTTP request header, but none of the charsets match the AcceptCharset http option, then use the first and/or highest q-value charset specified within the AcceptCharset http option. (This is a behaviour change).

3 - If the client does not send an Accept-Charset HTTP request header, select the first and/or highest q-value charset specified within the AcceptCharset http option (no change in behaviour).

Caveats:

a. Within the AcceptCharset http option value, the placement of the '+' token (which specifies the use of database charset whenever possible regardless of the q-value assigned to it by the client) is now significant. If '+' is specified anywhere within the http option value then case 1) will be true if the client happens to specify the database charset. If '+' is specified first and/or it has the highest q-value, then cases 2) and 3) above would resolve to using the database charset.

b. Within the AcceptCharset http option value, an asterisk (*) signifies that any client charset (as specified by its Accept-Charset HTTP header) should be considered prior to defaulting to the http option charset list. The best match within the union of client/server charsets ( case 1) ) has priority. In the processing priority cases above, having failed case 1) the client's Accept-Charset is scanned for a suitable charset. If a suitable charset is not found, then a charset is selected according to case 3).

c. SQLCODE -939 error is only generated if the http option value is malformed or none of the specified charsets within its value is supported by SQL Anywhere. The database charset is selected whenever a SQLCODE -939 error is generated.

Note, that in order to fetch such a result set, the application must be using jConnect 6.x.

The original crashed database will need to be recovered with the fixed server.

key from within the Table Editor the primary key was dropped, but could not be re-created. This has been fixed.

WITH HOLD cursors will see a snapshot of all rows committed at the snapshot start time, as well as all modifications done by the current connection since the start of the transaction within which the cursor was open.

Note that the contents of the cursors are unaffected by the current transaction committing.

For example, the following expression was incorrectly described as TIMESTAMP (it should be INT):

select months( current timestamp, NULL )

This incorrect type could have affected more complex expressions composed with one of the affected functions as an argument. This problem has been fixed.

Note, this change could alter the definition of materialized views; views containing the affected constructs should be refreshed.

HRESULT Execute (

IUnknown牋牋 *pUnkOuter,

REFIID牋牋牋牋riid,

DBPARAMS牋牋 *pParams,

DBROWCOUNT牋 *pcRowsAffected,

IUnknown牋牋**ppRowset);

struct DBPARAMS {

void *pData;

DB_UPARAMS cParamSets;

HACCESSOR hAccessor;

};

This is now supported, so it is now possible to execute one INSERT statement and specify several sets of parameters in order to insert several rows into a table.

Note the following OLE DB specification restriction:

Sets of multiple parameters (cParamSets is greater than one) can be specified only if DBPROP_MULTIPLEPARAMSETS is VARIANT_TRUE and the command does not return any rowsets.

This means that multiple parameterized SELECT statements can not be executed that would each return a result set. For the SQL Anywhere provider, DBPROP_MULTIPLEPARAMSETS is VARIANT_TRUE (and always has been).

server.

dbspaces

Java classes

Java jars

external environments

external environment objects

login policies

This has been fixed.

As a work-around, databases can be upgraded using dbupgrad.exe or by executing an ALTER DATABASE UPGRADE PROCEDURE ON statement.

As an interim work-around, the server command line switch "-hW AllowBypassCosted" can be specified to avoid this problem.

Note, that in order to fetch such a result set, the application must be using Open Client 15.

Error: <Notifier(QAnyNotifier_client)>: Request 1604437321 is accepted by invalid respondent 'ias_receiver_lsn'. Please check the message filters on the listener

This error would have happened whenever a UDP notification for client A was sent, resulting in wasteful SIS notifications. This has now been fixed. For 9.0.2, the fix was made only for MobiLink with messaging (QAnywhere) for ASA consolidated databases. In later versions, the fix applies to all MobiLink Notifiers in all supported consolidated databases.

To test the estimated selectivity of the NULL values for a column T.X use: "select first estimate(X, null) from T".

A workaround is to recreate statistics on the column T.X by using: "create statistics T (X)". However, if the column T.X has often only NULL values, and then it is updated to some non-null values, it is recommended to upgrade to a server containing this fix.

-890 "Statement size or complexity exceeds server limits"

Note, to limit the number of concurrent synchronizations for non-persistent HTTP/HTTPS, the -nc option should be set significantly higher than -sm. The greater the difference between -sm and -nc, the more likely (but never guaranteed) the 503 error will be sent to the remote instead of a socket error.

Note, any SOAP services that defined a DATATYPE, GROUP or FORMAT clause, or any web service that defined a METHODS clause, were affected.

Note, it was possible for text indexes that contain long search terms to be affected by this problem when the text index was built or updated. If this occured, the index should be rebuilt with a server containing this fix.

Request from "Dbmlsync Version 10.0.1.3750" for: remote ID: rem1, user name: rem1, version: v1

Request from "Dbmlsync Version 10.0.1.3750" for: remote ID: rem1, user name: rem1, version: v1

Synchronization complete

This problem has been fixed.

Also with this fix, UltraLite embedded SQL cursors are now considered read only if no clause is present. The previous behaviour can be achieved with sqlpp 杕 HISTORICAL.

Example:

ALTER TABLE FOREIGN KEY ( fk1, fk2, ..., fkn) references P (pk1, pk2,..., pkn)

Q:

select * from

F, P

where F.fk1 = P.pk1 and F.fk2 = P.pk2 and ... and F.fkn = P.pkn

returns all rows from the foreign key table F less the rows having at least one NULL for the foreign key columns F.fk1, F.fk2, ..., F.fkn.

This problem has two visible results:

Firstly, diagnostic tracing into a local database will cause the database to grow twice as large as expected due to diagnostic data. It will be necessary to rebuild a database this problem in order to prevent this growth from happening with subsequent builds.

Secondly, JDBC metadata that is supposed to be unique to each connection will end up being visible to all connections. This can cause a variety of problems for applications that depend on querying this metadata. This manifestation of the problem can be avoided by running dbupgrad with a fixed server against a database with this problem.

The presence of this problem in a database can be verified by querying SYSTABLE and noting that all jdbc_* tables have type of BASE.

For more info on intra-query parallelism see:

SQL Anywhere Server - SQL Usage

Query Optimizer

Query optimization and execution

Query execution algorithms

Parallelism during query execution

This change is particularly important when moving to version 11.0.1, from 10.0.1 or 11.0.0, and running the personal server (dbeng11). The 10.0.1 personal server (dbeng10) and 11.0.0 personal server (dbeng11) are restricted to using only one CPU, and only one core if the CPU has multiple cores.

Also, the 10.0.1 optimizer did not consider the number of maximum concurrent threads (i.e. ConcurrentThreads global variable), and may generate parallel plans which will not be executed in parallel by the 10.0.1 personal server, only one parallel branch will process all the rows. This is a bug which was fixed in 11.0.0 GA.

The 11.0.1 personal server can use all the cores available in one CPU, which means the 11.0.1 optimizer will cost and generate access plans using parallel physical operators when multiple cores are available. This difference in behaviour related to the number of cores allowed to be used by the personal server may result in a very different access plan being executed by 11.0.1 compared to the access plan executed by 11.0.0, for the same SQL statement.

For example:

With this fix an HTTP request to the SUB service now succeeds in setting the User-Header within the SUB1 function.

create service sub

type 'raw'

authorization off

secure off

user dba

as call sp_sub();

create procedure sp_sub()

begin

select sub1();

end;

create function sub1()

returns long varchar

begin

call sa_set_http_header('User-Header', 'test');

return 'test';

end;

For more information see:

SQL Anywhere Server - SQL Usage

Query Optimizer

Query optimization and execution

Query processing phases

A workaround is to write the function or procedure using the Watcom-SQL dialect.

As a workaround, the correct library name can be specified in the second argument to sqlany_initialize_interface, the environment variable SQLANY_API_DLL can be set to the correct library name, or the extension can be manually changed in the DEFAULT_LIBRARY_NAME macro in sacapidll.c.

Note: if the server now reports a "License file not found" error after applying this fix, when it started prior to the fix, then the license file was not correctly installed. To correct this, the license file should be moved so that it is in the same directory as the server executable.

A similar problem would have occurred if a maintenance plan was modified after having shown its corresponding event's SQL tab. The event's SQL tab would have displayed stale SQL.

In both cases, pressing F5 to refresh Sybase Central would have show the object's up-to-date SQL. Pressing F5 is now no longer necessary, as Sybase Central has been corrected to keep such objects' SQL up-to-date.

Assertion failed: 201501 - Page for requested record not a table page or record not present on page

This has been fixed.

A workaround is to run the backup without the WAIT BEFORE START clause.

A workaround is to issue a checkpoint before running validation.

The workaround is to add "mldesign.jar" to the list on the "Advanced" page of the QAnywhere 11 plugin properties window.

Note that rebuilding a database to a version 11 format will provide performance improvements, while also preventing this problem.

Note, temporary tables occur when there are no indexes discovered to implement operations such as ORDER BY.

1 - primary key conflicts for table ml_qa_global_props in upload_insert script (Engineering case 463062),

2 - table ml_qa_status_history unlimited growth problem (Engineering case 495514),

3 - UDP notifications to inactive devices problem (Engineering case 544613).

For example, CONTAINS query

'a AND NOT b OR c'

would be evaluated as

'a AND NOT (b OR c)'.

After the fix, the meaning of the query is

'(a AND NOT b) OR c'.

Equivalents to the AND NOT operator are: 'a & NOT b', 'a NOT b', 'a AND -b', 'a & -b', 'a -b'. Note that if hyphen is used, it has to be preceded by a space

and immediately followed by it's argument. There are two other legitimate uses for hyphen in a text query: 'a-b' and 'a - b'. The first case is interpreted as the phrase '"a b"',

whereas the second is interpreted as 'a b' (='a AND b').

Additionally, interpretation of CONTAINS queries was dependent on exact order of operators and their arguments. For example, query

'a AND b OR c'

would be evaluated as

'a AND (b OR c)'

and query

'c OR b AND a'

would be evaluated as

'c OR (b AND a)'.

This has also been fixed by imposing an order of evaluation on all the text query operators as follows:

1) FUZZY, NEAR

2) AND NOT

3) AND

4) OR

With the new rules, two examples above are equivalent

'(a AND b) OR c' = 'c OR (b AND a)'

Full syntax for the CONTAINS queries is described in the documentation:

SQL Anywhere Server - SQL Reference

Using SQL � SQL language elements

Search conditions

CONTAINS search condition

For example, the query:

SELECT DISTINCT X

FROM T

GROUP BY E

ORDER BY E

Would have returned the error: "Could not execute statement. Function or column reference to 'E' must also appear in a GROUP BY." SQLCODE=-149, ODBC 3 State="42000"

For example:

Previously, the server would have read approximately 85 index leaf and table pages for the query below, now the number of pages read is approximately 10.

CREATE TABLE CURRENCY_TABLE

(CURRENCY CHAR(10) NOT NULL,

DOLLAR_EQUIV NUMERIC(5, 2),

PRIMARY KEY (CURRENCY));

INSERT INTO "CURRENCY_TABLE" VALUES ('DOLLAR', 1.00);

INSERT INTO "CURRENCY_TABLE" VALUES ('POUND', 1.91);

INSERT INTO "CURRENCY_TABLE" VALUES ('DM', .45);

INSERT INTO "CURRENCY_TABLE"

select 'DM', NULL from sa_rowgenerator(1,20000);

commit;

create index currency_idx_1 on currency_table( dollar_equiv desc );

call sa_flush_cache();

select count(*) from CURRENCY_TABLE with (index (currency_idx_1 ))

where DOLLAR_EQUIV < 1000 option( force optimization);

1. List of members for a given destination alias

2. "Properties" tab of client store property sheets

3. The "Client Properties" tab of the property sheet for a server message store

4. The "Members" tab in the property sheet for a destination alias

5. The property sheet for a client in a server message store

6. The "Transmission Rules" or "Deletion Rules" tabs in any property sheet.

7. The "Properties" tab of a connector's property sheet.

This has now been fixed.

Note, GA versions of the software will not be able to read a database that has been used by UltraLiteJ as of this change. Earlier databases will automatically be upgraded.

- dropping a table or index

- truncating a table (that could have been truncated quickly, eg. no triggers)

- deleting or replacing long blobs (greater than roughly page size)

- [in general] an operation that resulted in pages being freed without the contents being modified

This was more likely to have been an issue on heavily loaded servers. This problem has been fixed by temporarily allocating the pages at the start of the lazy checkpoint and then re-freeing them at the end.

Q:

select *

from T

where T.X <> (select max(R.Y) from R where R.Z = T.Z+1)

A workaround is to manually remove the MobiLink system setup and then re-run the command.

the remote server or directory access server is deleted. This fix restores the behaviour to what it was in versions 10 and earlier.

I. <1> failed reading command with id:%d and handle:%d

I. <1> Synchronization complete

This has been fixed. Now, the error message "[-10001] Protocol Error: 400" will be displayed and a synchronization error will be reported.

CONTAINS( col1, '"what a weather"')

CONTAINS ( col1, '"what weather"' )

CONTAINS( col1, 'what NEAR[1] weat' )

CONTAINS( col1, 'what NEAR[2] weat' )

This has been fixed, so that only the first and fourth queries will now match the row.

Note, all NGRAM text indexes built before this fix should be rebuilt either by using REFRESH TEXT INDEX ... FORCE BUILD (for MANUAL or AUTO REFRESH text indexes), or by dropping and recreating (for IMMEDIATE text indexes).

For example:

% it's a contraction

The ODBC driver has to parse statements looking for ODBC escape sequences, but did not handle %-style comments. This has been fixed.

TERM LENGTH could fail to find matches.

Examples:

query with non-alphanumeric characters: ' pre-school* ', ' a_variable_n* ', ' " pre-scho* children " '

query over 4-GRAM indexed columns: ' weath* ', ' " beau* weath* " '

query over columns indexed by GENERIC

text index with MINIMUM TERM LENGTH = 3: ' singular no* ending with ', ' " singular no* ending with " '

This has been fixed so that the above examples are now interpreted as the following queries:

' pre-school* ' = '"pre school*"' (GENERIC text index)

' a_variable_n* ' = '"a variable n*"' (GENERIC text index)

' " pre-scho* children " ' = '"pre scho* children"' (GENERIC text index)

(Note: STOPLIST and length restrictions are applied to all resulting terms except for the actual prefix, i.e. if 'pre' was in the STOPLIST, the last query would be interpreted as '"scho* children"')

' weath* ' = '"weat eath"' (4-GRAM text index)

' " beau* weath* " ' = '"beau" & "weat eath"' (4-GRAM text index)

' singular no* ending with ' = 'singular no* ending with' (GENERIC text index with MINIMUM TERM LENGTH = 3)

' " singular no* ending with " ' = '"singular ^ ending with"' (GENERIC text index with MINIMUM TERM LENGTH = 3. The ^ in the query represents the fact that the phrases with a single term between 'singular' and 'ending' will be found, and not those with no intervening terms, or multiple intervening terms)

The following new rules were added to the CONTAINS query grammar with respect to NGRAM text indexes:

1) If a prefix term is shorter than N, all NGRAMs beginning with the prefix will be matched. For example: on a 4-GRAM text index the query 'a*' will match all the rows that contain at least one 4-GRAM beginning with a, including the following:

'apple pie' (contains appl)

'container' (contains aine)

2) If a prefix term has length N or higher, it is converted to a search for the exact substring equal to the prefix. This is due to the fact that information about the beginning and end of a word is not stored in NGRAM index. Searches for 'apple' or 'appl*' on a 4-GRAM text index will both find rows containing words 'apple' and 'pineapple'.

3) If a prefix term appears in a phrase in the CONTAINS query that will use NGRAM text index, the phrase is converted into an AND of two phrases - one from the beginning of the phrase up to and including the prefix term, and the other one beginning at the term following the prefix term and ending at the last term of the phrase. For example, for a 4-GRAM text index, query ' " where ord* an* relati* distance " ' is interpreted as '"wher here ord*" & "an*" & "rela elat lati" & "dist ista stan tanc ance"'

4) If one of the arguments to NEAR is a prefix, NEAR is converted to AND. For example, for a 4-GRAM text index, query ' app* NEAR orang* ' is interpreted as 'app* & "oran rang"'

The following new rules were added to the CONTAINS query grammar with respect to GENERIC text indexes with MINIMUM TERM LENGTH > 1:

1) In a phrase, prefix term shorter than MINIMUM TERM LENGTH will be dropped. For example, on a GENERIC text index with MINIMUM TERM LENGTH = 3, query ' " the g* weather " ' will be interpreted as '"the ^ weather"' and will match both 'the good weather' and 'the bad weather', but not 'the weather is good'.

2) Outside of a phrase, prefix term shorter than MINIMUM TERM LENGTH will match terms that are between MINIMUM and MAXIMUM TERM LENGTH and begin with the specified prefix.

For example, on a GENERIC text index with MINIMUM TERM LENGTH = 3, query ' the g* weather ' will be interpreted as 'the & g* & weather' and will match 'the good weather', 'the weather is good', but not 'the bad weather'.

yet been specified. This has been fixed.

Note, the likelihood of this problem appearing with 11.0.0 servers was even smaller than with 10.0.1 servers.

Also, exports to Oracle previously used the Date data type. Now, for Oracle 9 or later Timestamp is used instead of Date.

Note that databases started later (after the server is up and running) would not have this problem.

With the addition of d2mrelod.jcl, the steps are now:

1) Copy syncd2m_jcl.sql and edit it.

Change {MLTABLESPACE} to your qualified tablespace, eg. MYDB.MYTS

Change {WLMENV} to the name of a WLM associated with your DB2 instance

2) Start DBISQL and connect to DB2 mainframe.

File->Run your copy of syncd2m_jcl.sql.

3) From the %SQLANY%\MobiLink\setup directory, FTP to your mainframe, running the following commands after you connect:

bin

hash

cd xmit

quote site recfm=fb lrecl=80

quote site cyl

put d2mload.xmit

put d2mdbrm.xmit

quit

The two XMIT files on the mainframe are now:

USERID.XMIT.D2MLOAD.XMIT

USERID.XMIT.D2MDBRM.XMIT

where 'USERID' is the username you gave when connecting via FTP.

4) Open a terminal session and run the following commands from the

ISPF Command Shell:

RECEIVE INDATASET('USERID.XMIT.D2MLOAD.XMIT')

RECEIVE INDATASET('USERID.XMIT.D2MDBRM.XMIT')

The output is:

USERID.ML.LOADLIB

USERID.ML.DBRMLIB

5) Copy d2mrelod.jcl and edit it.

Change USERID to your mainframe userid.

Change DSNDB0T to your DB2 DSN.

6) Run your copy of d2mrelod.jcl

7) Copy d2mbdpk.jcl and edit it.

Change USERID to your mainframe userid.

Change DB0T to your DB2 SSID.

Run your copy of d2mbdpk.jcl

The MobiLink schema should now be ready to use.

"Internal warning: <x> dispatch took <y> seconds"

where x is an object name and y is a number. This would have affected the Windows platform only, and not necessarily on all Windows machines. This has now been fixed.

A workaround is turn off the -z option or request level logging.

E. 10/07 09:14:13. Error: java.sql.SQLException: [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Your server command (family id #0, process id #342) encountered a deadlock situation. Please re-run your command.

These errors happen because ASE uses page-level locking by default. This locking level causes deadlocks with many of the SQL statements executed by MobiLink with QAnywhere, especially as many clients are synchronizing concurrently. This problem can be worked around by executing the following SQL statements on the ASE consolidated database, when the database server is not handling other requests:

alter table ml_qa_repository lock datarows

go

alter table ml_qa_delivery lock datarows

go

alter table ml_qa_notifications lock datarows

go

alter table ml_qa_global_props lock datarows

go

alter table ml_qa_status_history lock datarows

go

alter table ml_qa_repository_props lock datarows

go

alter table ml_qa_repository_staging lock datarows

go

alter table ml_qa_status_staging lock datarows

go

create index ml_qa_delivery_cli on ml_qa_delivery ( client )

go

create index ml_qa_delivery_dld on ml_qa_delivery ( client, syncstatus, status )

go

create index ml_qa_mstage_user on ml_qa_repository_staging ( mluser )

go

create index ml_qa_sstage_user on ml_qa_status_staging ( mluser )

go

These SQL statements change the QAnywhere system tables to use row-level locking, and add some indexes for increased performance.

Note that calling ResultSet.getRow() after calling ResultSet.isLast() while positioned on any row other than the last row would have returned the correct row number.

Note that switching the order in which the Bin32 and Bin64 directories appear in the path will work around this problem.

As a work-around, run "regsvr32 -u dbodbcXX.dll" from a command shell which is already elevated.

As a workaround, use the "-c" command line option instead.

select cast('abc' as nchar),cast('abc' as nvarchar)

This problem did not affect calls to SQLColumns(), but it did affect calls to SQLDescribeCol(). This problem has been fixed.

The odbc_distinguish_char_and_varchar option is intended for CHAR columns only. It is provided for backwards compatibility with older versions of the SQL Anywhere ODBC driver. For backwards compatibility, the odbc_distinguish_char_and_varchar option is set 'off' by default. When odbc_distinguish_char_and_varchar option is set 'on', the ODBC driver will describe CHAR columns as SQL_CHAR, rather than SQL_VARCHAR.

The workaround is to use the SASQL_STORE_RESULT option or call the function sasql_store_result().

For example:

SELECT * from table FOR XML AUTO

where table is defined with an NCHAR column, e.g.:

create table (col1 LONG NVARCHAR);

For the error to have occurred, the byte length of the NCHAR value must have been greater than 32767, and the "string_rtruncation" database option must have been set to "on" (which is the default).

This has been fixed.

Note, this problem appeared on the "Data" tab in Sybase Central, and has been fixed as well.

Futhermore, resolving host names from 32-bit applications will fail on 64-bit Ubuntu installations, unless the package lib32nss-mdns is installed. See:

http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=479144

SORTKEY()

COMPARE()

CONVERT( <date, time, or timestamp>, <string expression> )

In order for the failure to occur, further specific characteristics needed to be present in the string parameters to the above functions. This problem has been fixed.

Note that this problem does not exist on Windows platforms.

100902, 100903: Unable to find table definition for table referenced in transaction log

100904: Failed to redo a database operation

For this problem to have occurred, other connections must have been making changes to the database at the time the log is renamed. The problem seemed to occur more frequently if many old log files were present on the primary server. The copy of the transaction log on the mirror server would have been corrupted as a result of this problem. When the log was renamed, there was a small window during which another connection could force a log page to be sent to the mirror before the mirror was notified that the log was renamed. The transaction log lock is now held across this window.

As well, Python scripts can now pass an instance of sqlanydb.Binary in the variable data list. This will cause the value to be sent as binary data.

To further improve the query, an extra connection parameter, ProcOwner (the long form) or POwner (the short form), has been introduced in the iAS Oracle ODBC driver. This parameter can be given as follows:

-c "uid=...;pwd=...;powner=my_procedure_owner;..."

If this connection parameter is given, the query will be limited to the stored procedures owned by the given owner only. Please note, if not all the stored procedures used in the synchronization have a single owner, this extra connection parameter should not be used.

For example, the statement:

select length(repeat('a', 2000000000) || repeat('b', 2000000000))

returns 2147483647 (i.e., 2^31-1) characters, but does not raise a SQL error to indicate the operation failed and truncated the string data. This has been fixed so that SQL Error -1313 MAX_STRING_LENGTH_EXCEEDED will now be generated whenever an operation attempts to construct a string value longer than the server's internal maximum string length.

Transfer-Encoding: chunked

Expires: ... GMT

Content-Type:...

As an artifact of the chunked transfer encoding, a response body consisting of a 0 chunk length was returned. This has been corrected so that a response body and the above headers will not be sent by the server when a 304 'Not Modified' HTTP status has been explicitly set.

... <F0B1S0R0> IIS ReadClient error(10054): Connection was forcibly closed by the remote host.

... <F0B1S0R0> Failed reading from client. Aborting this request.

... <F0B1S1R1> Communication warning! Backend server disconnected before finishing response.

These are expected, as Afaria traffic uses false http content-length. The Relay Server will now suppress these messages for Afaria traffic.

Afaria

dblsn

DBMLSync

MLClient // All pre-11.0.1 ml clients

MLFileTransfer

MLLightPoll

MLMonitor

UltraLite

UltraLiteJ

QAnywhere

QAnywhereUL

These client types, and the full client information (usually including build and version), will be logged by the Relay Server under verbosity level 1 or higher.

In addition to this change, customers should consider configuring dbsupport to ensure that it does not prompt when a failure occurs. For example:

dbsupport -cc autosubmit

or

dbsupport -cc no

A future version of the software may avoid the need to configure dbsupport to prevent prompting in this situation.

1. The column list in a SELECT statement

select C. from customers C

^

2. The WHERE clause in a DELETE statement

delete from customers C where C.

^

3. The SET or WHERE clauses of an UPDATE statement

update Customers C set C.

^

update Customers C set C.City = 'Waterloo' where C.

^

This has been fixed by suppressing adding the owner's name if the inserted text immediately follows a table alias.

A work around is to put msvcr71.dll on the path. It is installed with the JRE.

A workaround is to use an internal unload.

Stored procedure 'ml_qa_upsert_global_prop' not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

(ODBC State = 42000, Native error code = 2812)

Error: Unable to insert into table 'ml_qa_global_props_client' using upload_insert

This problem may have been as a result of the fix made for Engineering case 546131. That fix involved MobiLink creating this stored procedure at startup if it does not exist. In order for MobiLink to be able to create the stored procedure, the database option "ddl in tran" needed to be true. Therefore, there are two possible workarounds for this problem:

Workaround 1: Set "ddl in tran" to true in the database by executing the following SQL statements on the database, where database_name is the name of the database used as the QAnywhere server message store.

use master

go

sp_dboption database_name, "ddl in tran", true

go

Workaround 2: If workaround 1 is not desirable, execute the following SQL statements on the database to make the changes described in case 546131:

drop trigger ml_qa_repository_trigger

go

create trigger ml_qa_repository_trigger on ml_qa_repository for delete

as

delete from ml_qa_repository_props from deleted d, ml_qa_repository_props p

where d.msgid = p.msgid

delete from ml_qa_delivery from deleted d, ml_qa_delivery p

where d.msgid = p.msgid

delete from ml_qa_status_history from deleted d, ml_qa_status_history p

where d.msgid = p.msgid

go

drop procedure ml_set_device_address

go

create procedure ml_set_device_address

@device varchar(255),

@medium varchar(255),

@address varchar(255),

@active char(1),

@ignore_tracking char(1),

@source varchar(255)

as

declare @d varchar( 255 )

if @active = 'y' begin

update ml_device_address

set active = 'n'

where address = @address

and medium = @medium

and active = 'y'

and device_name <> @device

end

select @d=device_name from ml_device_address

where device_name = @device and medium = @medium

if @d is null begin

insert into ml_device_address( device_name, medium, address, active, ignore_tracking, source )

values( @device, @medium, @address, @active, @ignore_tracking, @source )

end

else begin

if @source = 'tracking' begin

update ml_device_address

set address = @address,

active = @active,

ignore_tracking = @ignore_tracking,

source = @source

where device_name = @device and medium = @medium and ignore_tracking = 'n'

end

else begin

update ml_device_address

set address = @address,

active = @active,

ignore_tracking = @ignore_tracking,

source = @source

where device_name = @device and medium = @medium

end

end

go

exec sp_procxmode 'ml_set_device_address', 'anymode'

go

create procedure ml_qa_upsert_global_prop

@client varchar(128),

@name varchar(128),

@modifiers integer,

@value varchar(16384)

as

begin

if exists( select 1 from ml_qa_global_props gp

where gp.client = @client and gp.name = @name )

update ml_qa_global_props

set modifiers = @modifiers, value = @value

where client = @client and name = @name

else

insert into ml_qa_global_props (client,name,modifiers,value)

values( @client, @name, @modifiers, @value )

end

go

exec sp_procxmode 'ml_qa_upsert_global_prop', 'anymode'

go

These SQL statements make the changes to the server message store that are done automatically if database option "ddl in tran" is true.

Note, MobiLink system schema changes can only be made in a major release, so these documented workarounds are provided. Later versions contain these fixes.

[-10076] The MobiLink server was unable to calculate the timestamp precision on the consolidated database using the ml_scripts_modified table. Timestamp precision related warnings will not be generated.

This has been fixed. The following error will now be displayed:

[-10100] The MobiLink system table 'ml_user' is missing or a table column is missing

For example:

- 9.0.2.GA was installed with Mobilink deselected,

- FIPS is installed, again with Mobilink deselected,

- a 9,0,2 EBF is installed accepting all the defaults.

FIPS gets broken because sbgse2.dll does not get installed.

This has been fixed. The installer now checks if FIPS is installed before copying sbgse2.dll.

Note that this problem will not show up if the local temporary table is declared with "on commit preserve rows" or "not transactional".

A workaround is to deploy to file and then modify the SQL file to use the sys.schemas table wherever the sysusers table is currently used, and change the id, uid column references to object_id and schema_id for that table.

For example, the following two queries should be equivalent, but the first incorrectly returned no rows:

select R.row_num || 'z' x, string(R.row_num ,'z') y

from dummy D left outer join rowgenerator R on 1=0

where x = 'z'

select R.row_num || 'z' x, string(R.row_num ,'z') y

from dummy D left outer join rowgenerator R on 1=0

where y = 'z'

Note that this problem was introduced in the changes made for Engineering case 548322.

Note that due to differences in compression schemes, it is strongly recommended that jars containing textual resources be created without compression turned on. For jars containing binary resources (ex. movies or images), using a compressed jar will work fine.

For NCHAR types, the Microsoft ODBC driver selects a SQL_C_CHAR binding. Microsoft acknowledges in its knowledgebase article http://support.microsoft.com/default.aspx/kb/293659 that to do so is an error, but states that it does this to support older legacy applications. The SQL Anywhere ODBC driver selected a SQL_C_WCHAR binding. This caused problems with applications like Microsoft Access, that expected the Microsoft ODBC behaviour. This problem in Access could have been seen when setting up a linked table using the SQL Anywhere ODC driver to a database table containing NCHAR, NVARCHAR, or LONG NVARCHAR column types. The resulting table columns were displayed with "#deleted" values. To conform to the Microsoft ODBC driver behaviour, the SQL Anywhere ODBC driver has been changed. Now,爐he SQL Anywhere ODBC driver selects a SQL_C_CHAR binding for NCHAR, NVARCHAR, and LONG NVARCHAR (including NTEXT) types when the "MS Applications ( Keys In SQLStatistics)" checkbox is selected in the ODBC Administrator configuration dialog for SQL Anywhere datasources. This option can also be specified as the connection parameter "KeysInSQLStatistics=YES" in a SQL Anywhere ODBC driver connection string. This change affects any ODBC function, like SQLBindCol, SQLBindParameter, and SQLGetData, where SQL_C_DEFAULT can be specified.

1) since statics are shared across all connections, there is an issue with the security of data

2) mixing classes loaded by the SA class loader and the system class loader can lead to the VM throwing IllegalAccess and ClassCast exceptions

3) there is now the danger that the system class loader will get used for loading classes that should actually have been loaded by the SA class loader

Because of these potentially serious problems, it has always been STRONGLY recommended that all classes and jars to be used for Java in the database support be explicitly installed within the database. However, for those rare cases where the class really needs to be loaded by the system class loader, the server's classpath argument (-cp) can be used to add directories and jars to the classpath that the server builds for launching the JVM. Unfortunately, as of version 10.0, the server's classpath argument was being ignored when launching the JVM. This problem has now been fixed and the server's classpath argument is now properly appended to the classpath the server builds when launching the VM. Again, the use of the server's classpath argument is STRONGLY discouraged; instead, it is STRONGLY recommended that all classes and jars to be used for Java in the database support be installed explicitly in the database.

A work around in most cases is to omit the 'wait after end' option, as it is often not required.

For example:

ALTER TABLE T RENAME R

go

This format was assumed and if 'go' was not found on a line by itself, nothing would have been executed, but no error would have been generated. Now, the error SQLE_SCRIPT_MISSING_DELIMITER (-1315) is generated if 'go' is not found on a line by itself. Moreover, with this change the file may end with 'go<eof>' (previously, it had to be 'go\n'). Note that even single line script files must end with the 揼o� separator. This includes SQL Passthrough scripts. So the following is a correct example of adding a SQL Passthrough script:

call ml_add_passthrough_script( 憇cript1�, null, null, 慉LTER TABLE T RENAME R\ngo� )

If an error occurs and only part of the file is applied, the database must be shutdown and restarted (and the schema will as it was prior to the ALTER DATABASE statement).

Note, despite the reported gap, no data was actually lost from the logs. The data can be recovered by using dbtran to generate a .SQL script and then applying this to the database.

For example, the following command line was not being handled correctly:

dbisql -c "uid=dba;pwd=sql" CREATE TABLE "123TEST" ( c INT )

For example:

create table T1( a int primary key )

create table T2( a int primary key, c int )

create procedure P1 ( u int ) begin select a from T1 where a = u; end

select dt.* from T1 left outer join T2 on T1.a = T2.a, lateral( P1(T2.c) ) dt

For every row in T1 the left outer join can return at most one row because of the join on primary keys. So the query rewrite removed the left outer join and the table T2 from the query which caused a "Column T2.c not found" error for the procedure argument. This has been fixed.

For example, suppose two publication P1 and P2 are defined that both contain a table T1. Two ML users, U1 and U2 are defined on the remote. U1 is subscribed to P1, and U2 is subscribed to P2. A row is inserted into T1 in an operation I'll call O.

The old behaviour would have been to upload O when the subscription between U1 and P1 was synchronized, then upload it again when the subscription between U2 and P2 was synchronized. The new behaviour is that O will be uploaded for the subscription that is synchronized first (U1-P1 or U2-P2), and not uploaded when the other subscription is synchronized.

CREATE SERVER MyServer CLASS ...

and then created two proxy tables as follows:

CREATE EXISTING TABLE table1 AT 'myserver...'

CREATE EXISTING TABLE table2 AT 'MYserver...'

If the application now queried "table1", the remote data access layer would have correctly established a connection to the remote and returned the requested data. If the application then queried "table2", the data access layer should reuse the connection previously established to the remote, but the server instead would have created a new connection to the remote. This problem has now been fixed.

A workaround is to use a higher value on the -gn command line switch.

Also, the usage text for createcert and viewcert will now display the options in alphabetical order.

Note, normal up channel renewal was not affected by this fix.

A user workaround is to increase web garden size (i.e. adding more worker processes) of the application pool associated with the rs_client.dll web server extension. This user workaround is inefficient though, letting limited resources to be held idle for unnecessary amount of time. With this fix, per process resource caps are no longer a reason for increasing the web garden size.

Note that the Java-based dbisql has always executed the query a total of two times (once for the original query, once for the OUTPUT statement). That behaviour is not addressed by this change.

The problem in dbisqlc has been corrected in most cases by avoiding the close/set-date-format/reopen operations if the requested output file format does not have a mandated date format or if the current date format matches the date format required by the output file format. If the specified output file format requires a specific date format and it is different from the current date format, the query will still be executed two extra times. To avoid executing the query multiple times, set the DATE_FORMAT option for the current connection as listed below before executing the query for the first time:

Output file format DATE_FORMAT setting

DBASEII MM/DD/YY

DBASEIII YYYMMDD

FOXPRO YYYMMDD

WATFILE YYYY/MM/DD

Output file formats not listed above do not have a mandated date format and dbisqlc will not close/reopen the current query to execute the OUTPUT statement.

[1]揇atabase System with Methodology for Generalized Order Optimization擬atthew Young-Lai, Anisoara Nica, 2007 US Patent 7,359,922.

- The ml_pt_script table did not have an explicit ROWID column. This was required for the CLOB column. Some D2M deployments support an implicit ROWID, and some do not, inluding compatibility mode. Fixed by adding an explicit ROWID column.

- The SQL used for JCL-based CREATE PROCEDURE statements included the SQL body of the stored procedure. This worked most of the time, but not under compatibility mode. Now, the external-procedure form of CREATE PROCEDURE is used, which doesn't include the body (which of course isn't necessary because it is in the *.xmit files).

- The SQL used to create the SQL-based D2M stored procedures didn't escape single quotes. This wasn't noticed because D2M treats most unquoted text between single quotes as a string, so it just worked. Single quotes are now escaped in the procedure body, inside the call to DSNTPSMP.

- The D2M ml_add_pt_script stored procedure didn't work under compatibility mode, for several reasons:

1) it had a VARCHAR( 256 ) parameter (the flags for the SQL Passthrough script) that caused conversion problems inside the procedure when run under compatibility mode. This parameter has been changed to VARCHAR( 255 ).

2) it referenced a Unicode string, which isn't supported under compatibility mode. This has been fixed by replacing it with a non-Unicode string.

3) it used a form of the SIGNAL statement that isn't supported under compatibility mode. This has been corrected.

"AutoCommit option has changed to true. All pending statements on this transaction (if any) are committed."

This warning was being generated whether the begin_connection_autocommit script was defined or was absent. This has been fixed. The server will now only turn on auto-commit when the script is defined and will only execute the script if it is defined. If the script is defined, it is still possible to see this warning logged in the MobiLink server console log. This is expected behaviour.

Note, rebuilding the database after applying this fix will remove all of the unneeded bytes.

1. there existed an equality predicate T.A = expression, with a selectivity estimation of selectivity1, and the expression was not a constant

2. there existed a range predicate on T.A with constant bounds (for example, T.A > 111), with a selectivity estimation of selectivity2

3. there existed an index on column T.A, where the column T.A may have been the only column in the index, or the index had a prefix of other columns before T.A, all of which are equated with constants in the query: e.g., the index idx<C1, C2, A> is defined, and the predicates 'T.C1 = 10 and T.C2 = 20 and T.A > 111 and T.A = R.X' are used in the query.

4. The selectivity estimation of the range predicate was much larger then the selectivity estimation of the equality predicate, i.e., selectivity1 << selectivity2

Such a query had an overestimated number of rows, and the execution plan chosen by the optimizer may have been inefficient. This has been fixed.

Note, as workaround the SATMP variable can be set.

- A complete request and response have previously been sent on the connection

- No data has been received on the connection since the most recent response was sent

- The keep-alive timeout has expired

This has been fixed.

Note: For IDENT BlackBerry devices, the default is Direct TCP connections. For non-IDENT BlackBerry devices, the default is BES connections. Using the URL suffix ";deviceside=false" forces Direct TCP connections.

Note that a workaround is to not specify the .dll or .so in the external name clause.

1) the application installed a message handler connections

2) the application shared a connection amongst multiple threads

3) one of the threads executed a statement that returned an asynchronous message

4) at the same time another thread used the same connection and called createStatement(), prepareStatement() or prepareCall(), before the asynchronous message was returned from the server

This problem has now been fixed.

The optimizer was unable to construct a valid access plan [-727] ['WI010']

In order for the problem to have occurred, the statement must have referred to only a single table and used only columns satisfied by an index. Further, snapshot isolation must have been enabled. This has now been fixed.

As a workaround, the statement hint "options(force optimization)" can be included to avoid the error.

extern const OLEDBDECLSPEC DBID DBCOLUMN_IDNAME = {DBCIDGUID, DBKIND_GUID_PROPID, (LPOLESTR)2};

This is what the OLE DB provider would return as the DBID for the "IDNAME" column. This strategy works for many ADO methods that request schema information. However, the following example illustrates a problem with the ADO RecordSet Save() method.

Dim strConnection = "PROVIDER=SAOLEDB;ServerName=demo;DatabaseName=demo;USERID=DBA;PASSWORD=sql"

Dim strSQLStatement = "SELECT ID, Name FROM Products"

Dim strXMLLocation = "c:\\temp\\products.xml"

Dim objADOConnection As New ADODB.Connection

Dim objADORecordSet As New ADODB.Recordset

objADOConnection.Open(strConnection)

objADORecordSet.Open(strSQLStatement, objADOConnection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

If Not (objADORecordSet.EOF And objADORecordSet.BOF) Then

objADORecordSet.MoveFirst()

objADORecordSet.Save(strXMLLocation, ADODB.PersistFormatEnum.adPersistXML)

End If

For reasons unknown, the ADO RecordSet Save() method returns the error "catastrophic failure".

The SQL Anywhere OLE DB provider has been changed to return only the "property ID" part of the DBID. This is equivalent to returning the following structure.

extern const OLEDBDECLSPEC DBID DBCOLUMN_IDNAME = {NULL, DBKIND_PROPID, (LPOLESTR)2};

This permits the ADO RecordSet Save() method to complete successfully.

stream parameters

Note, without the use of AppVerifier it would have been extremely rare for users to encounter these problems.

From the ODBC standand:

"COLUMN_SIZE": For character types, this is the length in characters of the data; The defined or maximum column size in characters of the column or parameter (as contained in the SQL_DESC_LENGTH descriptor field). For example, the column size of a single-byte character column defined as CHAR(10) is 10.

"BUFFER_LENGTH": The defined or the maximum (for variable type) length of the column in bytes. This is the same value as the descriptor field SQL_DESC_OCTET_LENGTH.

"CHAR_OCTET_LENGTH": The maximum length in bytes of a character or binary data type column. For all other data types, this column returns a NULL.

These problems have been fixed.

1. If the foreign keys associated with a primary key are requested, the result set is ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ.

2. If the primary keys associated with a foreign key are requested, the result set is ordered by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.

This has now been corrected.

1. Range expressions of a partial index scan are now always printed as "low expression <[=] column name <[=] high expression [ASC |DESC]". The low and high expressions can be NULL value if the real fence post was set to NULL value; they can be '*' if the real fence post is not set (this is actually an open range partial index scan).

2. Selectivity estimations are now printed with 9 decimal digits.

using a table index, then the result set may have had fewer rows than it should have.

For example, if table T1 had an index on column col, then the following query may have return too few rows:

select * from T1 where col in ( 5, Var1 ) order by a desc;

This has been fixed.

Example:

with recursive Ancestor( child) as

(select row_num from rowgenerator R1 where R1.row_num = 1 )

UNION ALL

(select R2.row_num+100

from Ancestor, rowgenerator R2

where Ancestor.child = R2.row_num

) )

select * from Ancestor

Ancestor(child) has exactly one row after the first query block is computed. Computing the second query block is very efficient if the executed plan is "Ancestor<seq> JNL R2<row_num>". However, because the optimizer did not set up the JNL operator, the inefficient plan "Ancestor<seq> JH* R2<seq>" was executed.

Note, setting the max_client_statements_cached database option to 0 may workaround this issue.

Product: SQL Anywhere 11.0.0.1526 with UltraliteJ feature

OS: Windows XP SP2

BlackBerry Java Development Environment: JDE 4.1.0.185 and MDS 4.1.0.22 20050927

Remote Database for ASA Proxy tables: Oracle 10g

After synchronizing UltraliteJ app tables with Consolidate ASA database, BlackBerry application exits with java.lang.OutOfMemoryError. When confirming BlackBerry memory, there's actually 17M free.

with the error message:

Permission denied: you do not have permission to execute the procedure

"sa_materialized_view_info"

This has been fixed.

Note that the result set of the stored procedure will be empty when the specified values are not appropriate.

DBISQL x.sql [parm1]

is equivalent to

DBISQL READ x.sql [parm1]

Note, for changes to the sa_validate() procedure to take effect, the database will need to be rebuilt with a server containing this fix.

- the query contained a subquery predicate (e.g., EXISTS() , NOT EXISTS()) which could not be flattened in the main query block, which was used in the cost-based optimization by the SA Optimizer

- the subquery predicate was correlated to at least two tables from the main query block

- a correlation expression was equated to a constant in the main query block

For example:

select *

from f, i, fi

where f.fund_id = 1 <==== f.fund_id is a correlation expression for the NOT EXISTS() predicate and it is equated to a constant

and f.fund_id = fi.fund_id

and fi.investor_id=i.investor_id

and i.investor_id not in ( select ib.investor_id <=== i.investor_id is a correlation expression for the NOT EXISTS() predicate

from ba , ib

where f.fund_id = ba.fund_id <==== f.fund_id is a correlation expression for the NOT EXISTS() predicate

and ib.bank_account_id=ba.account_id

)

Syntax: http_buffer_responses = { off | on }

Protocols: HTTP, HTTPS

Default: off

Because of the extra memory overhead required, this feature should only be used to work-around HTTP sync stability issues. In particular, the ActiveSync proxy server for Windows Mobile devices will throw away any data that is not read within 15 seconds after the server has closed its side of the connection. Because MobiLink clients process the download as they receive it from MobiLink, there is a chance they will fail to finish reading an HTTP packet within the allotted 15 seconds causing synchronization to fail with stream error code STREAM_ERROR_READ, when synchronizing using non-persistent HTTP. By specifiying 'http_buffer_responses=On', the client will read each HTTP packet in its entirety into a buffer before processing any of it, thereby beating the 15 second timeout.

For example:

update Employee E

set salary = salary * 1.05

where EXISTS( SELECT 1 FROM Sales S HAVING E.Sales > Avg( S.sales) GROUP by S.dept_no )

The syntax for UPDATE and DELETE statements was been expanded to correct this.

"Cannot access file '<incorrectly-converted-file-name>.db' -- The filename, directory name, or volume label syntax is incorrect."

This issue only occurred when using an OS charset whose label was not used prior to version 10.0.0, such as "GBK". Additionally, for this issue to have occurred, the database must have used a different charset than the OS charset - specifically, the filename must contain characters that require translation to be valid in the database charset. This has been fixed.

- The MobiLink server could have reported the error "The property name 'JMSTimestamp' is reserved and cannot be set." when Sending a message from a QAnywhere application to the JMS connector.

- The ReplyToAddress of a QAnywhere message originating from a Websphere JMS provider could have contained a malformed queue name.

Both of these issues have been corrected.

Connections to HTTPS listener ports were not affected by this problem.

The workaround is to log in using a userid without non-multibyte characters and execute a START JAVA statement. Once the Java VM is successfully started, making Java calls using userids with multibyte characters will work fine.

set option public.login_mode='Standard,Integrated'

was executed, it would have been recorded in the transaction log as

set option public.login_mode='Standard'

This could have affected mirroring environments where the login_mode was set correctly on the primary server, but not on the mirror. This has been fixed.

CREATE function client() RETURNS long binary URL 'http://localhost/image_service/...' TYPE 'HTTP:GET'

Note, this change only extends the semantic meaning of the returned value, declaring the return data type as binary does not change the behaviour at the transport level. Textual data may still be converted to the database character set based on Content-Type HTTP header or SOAP envelope encoding criteria.

Note, both versions of the counters library are registered during a normal product installation so it is unlikely that users would encounter this problem.

A workaround would be to use a higher isolation level (2 or 3).

x >= 2 AND x = 3

with only a column name on one side of the comparison. That column name must also have been the first column in an index. This has now been fixed.

Note, SQL Anywhere 11.0.0 and 11.0.1 ship with JRE 1.6.0 update 4, which does not suffer from this problem.

For example:

select * from sales_order_items soi1, sales_order_items soi2

where soi1.id = soi2.id

AND

soi1.quantity <

(

select avg( quantity ) from sales_order_items soi3

where soi3.prod_id = soi1.prod_id

);

This has now been fixed.

Note, a work around would be to call sa_clean_database manually before creating the index.

-vR show the remote ID in each logging message

-vU show the ML user name in each logging message

When both -vR and -vU are specified, the MobiLink server will add the remote ID and the MobiLink user to each message logged:

yyyy-mm-dd hh:mm:ss. <sync_id> ({remote_id},{user_name})

When started with -vR and without -vU, the prefix will be just the remote_id:

yyyy-mm-dd hh:mm:ss. <sync_id> (remote_id,)

and the MobiLink user name will be empty. When started with -vU and without -vR, the

prefix will be just the user name:

yyyy-mm-dd hh:mm:ss. <sync_id> (,user_name)

and the remote ID will be empty.

The new feature may be useful for MobiLink users who are running the MobiLink server with the command options -on or -os, as the logging messages for a synchronization can span multiple MobiLink server logging files, which makes it is hard to find out the remote ID and MobiLink user name for a given sync ID from such logs. This extra logging information will only apply to the synchronization threads. For the main thread of the MobiLink

server, the logging messages will still contain the following prefix,

yyyy-mm-dd hh:mm:ss. <Main>

because there is no remote ID or MobiLink user name for the main thread. These two command line options will not be affected by the -v+ option, that is, the MobiLink server will not add the remote ID or the ML user name into its logging messages, even if the -v+ option is used. Therefore, the description for the -v+ option

has been changed to - "show all verbose logging specified with lower case letters".

ODBC specificatrion for AUTO_UNIQUE_VALUE :

Whether the data type is autoincrementing:

SQL_TRUE if the data type is autoincrementing.

SQL_FALSE if the data type is not autoincrementing.

NULL is returned if the attribute is not applicable to the data type or the data type is not numeric.

The behaviour has been corrected to follow the ODBC specification.

Note, this problem only affected graphical operation of the program.

[IBM][CLI Driver][DB2/NT] SQL1216N Graphic data and graphic functions are not supported for this database. SQLSTATE=56031

[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "N''" was found following ", ?, ?, ?, ?, ''". Expected tokens may include: "<space>". SQLSTATE=42601

This has been fixed. A workaround is to deploy to a file and edit the consolidated SQL file to change instances of four single quotes to two single quotes.

Comments were also added that explain the DriverManager.registerDriver and DriverManager.getConnection code.

100914: string too long when recording operation in transaction log.

In order for the failure to occur, the inserted string must have had byte-length semantics and it must have contained more characters than the column definition. Further specific characteristics of the database and statement were needed to reproduce the problem. This problem has been fixed.

- number of processors detected (changed format)

- processor limits imposed by Edition, licenses purchased, and -gt option

- max number of processors that will be used

Also, the Edition limit that is imposed during licensing is now also imposed by the server itself.

For some Unix systems, the number of processors used can not be limited. If the number to be used exceeds the per-processor license limitation, an additional message is reported, and is unchanged from previous versions.

A workaround is to disable parallel plans by setting Max_query_tasks=1 as a connection option, or in the query OPTIONS clause.

Assertion failed: 106502 - NULL in not-NULL work table column

In order for the failure to occur, one of the indexed columns in the query must have been declared not-NULL. This has been fixed.

For example:

conn = sqlany_new_connection();

sqlany_connect( conn, <connection_string> );

sqlany_disconnect( conn );

sqlany_connect( conn, <connection_string> );

The last sqlany_connect() call would have returned error -298. This has been fixed. The workaround is to call sqlany_free_connect() and allocate and new handle.

For example, the following generated a "division by zero" error:

select if 1=0 then 1/0 else 42 endif val

This problem occurred because the expression '1/0' was evaluated as part of building the access plan for the query. Constant expression trees were evaluated at open time in a bottom-up fashion so that the value can be used at execution time (avoiding multiple evaluations and supporting optimizations). In some cases, the expression was not actually needed, as in the above example with an IF expression. In these cases, the constant folding process could have generated an error that would not have been returned if the constant literals were replaced with field references.

This has been changed. Errors generated during constant folding are no longer returned at open time. Instead, the constant expression tree is left in its original structure and evaluated at execution time if the value is needed. This execution-time evaluation will generate the related error if the value is actually needed.

Error: [QA] Problem evaluating transmission rules for user ianywhere.server.deleteRules: Problem updating object in repository using key "delete from ml_qa_repository from ml_qa_repository mbr where msgid in (select msgid from ml_qa_messages ml_qa_messages where status > 20 and (syncstatus = 1 or originator in ('ianywhere.connector.beajms','ianywhere.server')))": java.sql.SQLException: [Sybase][ODBC Driver][Adaptive Server Anywhere]Deadlock detected: [Sybase][ODBC Driver][Adaptive Server Anywhere]Dea

Error: dlock detected

This has been fixed. It is possible that some deadlock errors will still occur, but the errors will not prevent delete rules from deleting old messages.

without preparing it. This has been fixed.

Note, when validating a database with a 10.0.1 server between 3920 and 3930 inclusive, it was also possible for errors to be reported, when in fact there were no error. I this case, the 10.0.1 server should be updated to a build number of 3931 or higher, and the validation rerun to see if the errors are still reported.

Note, to fix existing user-defined functions created with DETERMINISTIC, the function will need to be recreated.

Note, once a database contains multiple tables with the same names, all variations of the name will refer to the same (somewhat non-deterministic) instance of the table. The situation can be corrected by dropping and recreating the tables. Any existing data needs to be saved and restored as necessary.

Notes:

- The initial database connection will be retried a given number of times if the first connection fails

- Command line options for connection retries and retry delay:

-cr <n> (n is the number of retries to connect after a failure)

-cd <n> (n is delay, in seconds, between retries)

- The defaults are 3 retries with a 10 second delay between retries

- If all retries fail, the QAnywhere Agent displays an error and waits to be shut down

- If a database connection fails during operation, the QAnywhere Agent will go through termination steps, including finalizing connections, terminating threads and external processes, and then re-starts.

The following are the relevant APIs:

/// <summary>

/// ExceptionListener delegate definition.

/// </summary>

/// <param name="ex">The exception that occurred.</param>

/// <param name="msg">The message that was received, or null if the message

/// could not be constructed.</param>

public delegate void ExceptionListener( QAException ex, QAMessage msg );

/// <summary>

/// Sets an <see cref="ExceptionListener"/> delegate to receive QAExceptions

/// when processing QAnywhere messages asynchronously.

/// </summary>

/// <param name="address">The address of messages.</param>

/// <param name="listener">The exception listener to register.</param>

public void SetExceptionListener( string address, ExceptionListener listener );

In 10.0.x and later, the .NET and Java QAManagerBase class now supports exception listeners.

The .NET and Java QAManagerBase class now supports a property, RECEIVER_INTERVAL, that represents the maximum wait interval (in milliseconds) in the background receiver thread for receiving messages. The default is 60000 milliseconds. As with other QAManager properties, this property must be set before calling an Open() method.

The .NET and Java QAManagerBase class now supports a ReOpen() method that can be called in an ExceptionListener or MessageListener to re-establish connections to the message store database. Following is the API description.

/// <summary>

/// Reopens the QAManagerBase. This re-establishes connections to the message

/// store, without releasing any resources. This method may called in a

/// message or exception listener, and in that case it is not necessary to

/// call Start() again. This method simply executes Close() then Open() if

/// not called in a listener, and in that case Start() must be called to

/// restart receiving of messages.

/// </summary>

/// <exception cref="iAnywhere.QAnywhere.Client.QAException">

/// if there is a problem reopening the manager.

/// </exception>

public void ReOpen();

"Failed to retrieve session[x]"

or

"Session mismatch: session[x].snum=y instead of z"

or

"Session mismatch: session[x].sfp=y instead of z".

when a client unexpectedly disconnected from a Relay Server in the middle of sending a request, or receiving a response. These errors would have happened in a Relay Server farm environment with more than one Relay Servers in the farm and in particular, in the general case when using an Afaria client or a QAnywhere client running a listener. This has now been fixed.

A workaround for this problem is to call GC.Collect() regularly.

For example, the following query:

select manager_id,

sum(emp_id), max(manager_id) over (order by manager_id rows between manager_id preceding and salary following) as max_mgr

from employee

where manager_id > 5

group by manager_id, salary, max_mgr

would have resulted in an error "Expression error near 'employee.manager_id > 5'", rather than the more appropriate "Expression error near 'max(employee.manager_id) over( ... ) ... as max_mgr". The real problem with this query is that the alias 'max_mgr' cannot refer to a window aggregate expression, as the alias is part of the group by list.

This problem has been resolved.

Class SyncParms:

String getPublications()

setPublications( String publication_list )

These methods are used to set the publications that will be used for synchronization (publications control which tables get synchronized). The publications are specified as a comma separated string. Earlier versions of the UltraLite M-Business API used a "publication mask" to identify the publications for synchronization. A mask was an internal number representing a set of publications. The publication mask methods

were removed in version 11.0, and these methods were meant to replace them.

- bogus diagnostics regarding misplaced aggregation

- some illegal usages were not diagnosed

- incorrect results in some cases

This has been corrected.

as well,changing a column's data type to a domain (either in the table editor or in

the column property sheet) now updates the column's nulls setting to allow or prohibit nulls, depending on the chosen domain's nulls setting. If the domain does not explicitly allow or prohibits nulls, then the column's nulls setting is not updated.

to happen on multi-processor machines. This has been fixed.

- bogus diagnostics regarding misplaced aggregation

- some illegal usages were not diagnosed

- incorrect results in some cases

This has been corrected.

hostname/IP address specified on command line

-p

-s

-ss

This has been fixed. If more than one of these options is specified, they are all now applied.

grant select,insert,update,delete on dbo.omni_rset_fetch_table to PUBLIC

statement. This has been fixed.

Note that enclosing the use of the IDataReader in a 'using' block will automatically close it.

For example, the following statement could have caused this behaviour:

select TE1.xidx

from (select * from sys.dummy) as D, TE1

where exists ( select * from TE2 where TE2.x = TE1.xidx and TE2.xidx <= 1 )

For the incorrect behaviour to have occurred, the statement must have had a reference (explicit or implicit) to sys.dummy, where the dummy_col field was not referenced, and there must have been an exists join optimization used, where a generated distinct on rowids was needed. This has now been fixed.

When SQL Passthrough was being used, ie. when there was at least one row in the the ml_passthrough_script table, the MobiLink server could have used twice the number of consolidated database connections than necessary, (ie. up to twice the -w value). When creating these extra connections, the MobiLink server would, during a synchronization, make another consolidated database connection, perform some work, disconnect that connection, then resume the synchronization. In other words, penalizing each synchronization with the time/cost of connecting and disconnecting to the consolidated database. Besides the performance and database connection penalties, these connects/disconnects could also have made the MobiLink server reach OS socket limits faster, potentially causing failures.

Most MobiLink clients (ie. dbmlsync, dbtools sync interface, dbmlsync ActiveX integration component, and all UltraLite runtimes -- except UltraLiteJ, which doesn't support SQL Passthrough) have been updated as part of this fix. This change will cause newer clients to fail if run against an the 11.0.1 GA server. Thus MobiLink clients of version 11.0.1 2156 or later will require a MobiLink server of version 11.0.1 2156 or later.

Failed writing remote id file to '<filename>'

Despite the error, synchronizations would have continued successfully, and the remote id file would have appeared on the disk in good order. This problem has been fixed.

A workaround can be achieved using the following command line switch on the server start line or the "OPTIONS(FORCE OPTIMIZATION)" in the query text.

-hW AllowBypassCosted

Sample schema:

create table uuidtable( pkey int, uuid uniqueidentifier, stringform uniqueidentifierstr );

Sample query:

select * from uuidtable where uuid = ?

The following .NET/OLE DB example shows typical uniqueidentifier parameter binding:

OleDbCommand cmd = new OleDbCommand(txtSQLStatement.Text.Trim(), _conn);

OleDbParameter param1 = new OleDbParameter();

param1.ParameterName = "@p1";

param1.DbType = System.Data.DbType.Guid;

cmd.Parameters.Add(param1);

cmd.Parameters[0].Value = new Guid("41dfe9f9-db91-11d2-8c43-006008d26a6f");

It should be noted that, in a MobiLink server farm deployment, there could be a latency of at most the automatic rules evaluation period to send a push notification to a client as a result of an outbound message to that client. This is due to scalabilty factors in a situation where there is a high volume of outbound messages being processed by the MobiLink server.

Note, a workaround is to stop and restart the database before attempting to drop the procedure.

For example,

INPUT INTO tab( pk, col1 ) VALUES( :hv, 100 - :hv )

This has been fixed so that the final substitution for non-leftmost host-variable references is delayed so that the type of the first reference can be established.

For example,

INPUT INTO tab( pk, col1 ) VALUES( :hv, 100 - :hv )

This has been fixed so that the final substitution for non-leftmost host-variable references is delayed so that the type of the first reference can be established.

Note, this fix requires both an updated ODBC driver and server.

- do not use chunk mode transfer encoding

- explicitly set 'Connection: close' response header

- shutdown the HTTP connection once the error message is sent

Any pending (pipelined) requests following the request encountering the error are terminated. Also, an error response is guaranteed to close the HTTP connection. Interrupting a response (already underway such that the response headers have already been sent) will truncate the output and close the HTTP connection. User error messages that are explicitly caught by the EXCEPTION clause may also CALL SA_SET_HTTP_HEADER('Connection', 'close') prior to issuing the error page to force the HTTP connection to close after the response has been sent.

SET @str2 = if NULL like 'a' then 'TRUE' else 'FALSE' endif;

This has been fixed.

This client is newer than your MobiLink server. You must upgrade your MobiLink server before you can synchronize.

In order to resolve this problem, the MobiLink server must be upgraded as well. As a temporary work around, SQL Passthrough can be disabled by deleting all the rows in the ml_passthrough_script table and restarting the Mobilink server.

SET 'HTTP(VER=1.1)'

TYPE 'HTTP:POST' and length of the values for input parameter equal or exceed 8192 bytes

TYPE 'HTTP:POST' SET 'HTTP(CH=ON)'

This has been fixed.

Note, a workaround is to press ENTER after typing in the alias/correlation name, and then click the "OK" button.

-832 Connection Error: Found server but communication error occurred.

For this to have occurred, the connection must have been via shared memory, the application must have previously connected and disconnected successfully so as to unload the client libraries (ODBC, DBLIB, or DBCAPI), and the time between the last disconnect and the new connect was within 10ms. This has been fixed.

Client is HTTP/1.0:

The server does not support Keep-Alive and Chunk-mode operation for this HTTP version.

By default the server never sets the 'Transfer-Encoding' header and always sets 'Connection: close' header, shutting down the connection once the response has been sent. A SERVICE procedure may set the 'Content-Length' header but setting the 'Connection' header is ignored.

Client is HTTP/1.1:

By default the server responses use chunked-mode transfer encoding and automatically set the 'Transfer-Encoding: chunked' header. If the SERVICE procedure explicitly sets a 'Content-Length' header to some value, then the 'Content-Length' header is sent in place of the 'Transfer-Encoding' header and the response body is not chunk-mode encoded. Note: It is an error for a SERVICE procedure to set both a 'Content-Length' and 'Transfer-Encoding' header.

The server will assume 'Connection: keep-alive' if the client does not send a 'Connection' request header. If a client explicitly sends a 'Connection: close' request header and/or the SERVER procedure explicitly sets 'Connection: close' the server will shutdown the connection once the response has been sent.

Setting Content-Length

In most cases data will need to be buffered in order to calculate the Content-Length. Therefore, it is recommended to use chunk-mode transfer encoding whenever possible. If 'Content-Length' must be set, then care must be taken to ensure that the result-set is not character set translated when the response is composed. It is recommended that the 'CharsetConversion' http option is set to off when returning textual data. Also, setting 'Content-Length' should only be done within a TYPE 'RAW' SERVICE since some services (i.e. 'HTML', 'JSON') add content to the response.

A check has been added to ensure that the actual length of the response matches the value of 'Content-Length' header. If the values do not match then the server will shutdown the connection, once the response has been sent, regardless of whether or not a 'Connection: keep-alive' response header has been sent.

select * from TRange where x1 = 3 and x2 between 2 and 1

A more efficient plan would recognize that the BETWEEN predicate is never true (2 > 1), and uses a prefilter to avoid fetching any rows from TRange. This has been fixed so that the more efficient plan is selected.

Note that exporting data into an existing table did not have this problem.

http://www.openssl.org/news/secadv_20090325.txt. Note that only the third bug listed affected SQL Anywhere, and only on 64-bit Mac.

The usage semantics of HTTP_RESPONSE_HEADER() and NEXT_HTTP_RESPONS_HEADER() are the same as for HTTP_HEADER() and NEXT_HTTP_HEADER() respectively, which are used to inspect the request HTTP headers.

- If a user was viewing a tab with only one metric on it, went to administration and disabled collection of that metric, and went back to the monitoring tab, that metric's graph would still have been displayed, even though the tab itself was hidden.

- If a user disabled collection of a metric that was displayed on a tab with several other metrics, the disabled metric would still have been displayed on the tab along with all the other metrics.

These have both been fixed so that only the collected metrics are now displayed.

Note that this problem affected all MobiLink clients except UltraLiteJ.

identity - Indicates the file that contains the client's identity. An identity consists of the client certificate, the corresponding private key, and, optionally, the certificates of the intermediary certificate authorities. This parameter is equivalent to MobiLink server's 'identity' parameter.

identity_password - An optional parameter that specifies the password used to encrypt the private key found in the identity file. It is only required if the private key in the identity file is encrypted. This parameter is equivalent to MobiLink server's 'identity_password' parameter.

Note that MobiLink clients cannot authenticate directly to the MobiLink server using client-side certificates. They can only be used to authenticate to third-party servers and proxies which have been configured to accept client-side certificate authentication and are sitting between the client and MobiLink server.

Also note, this feature is not supported in UltraLiteJ, and because of ECC compatibility issues between different versions of Certicom's Security Builder libraries, it is also not supported when using ECC TLS. It is only supported for RSA TLS.

create temporary function T_W( x int )

returns timestamp

begin

waitfor delay '00:00:00.500';

return NULL;

end

select coalesce( T_W(0), now() ) time1, coalesce( T_W(1), now() ) time2

In addition to now(), the following expressions also had this behaviour:

getdate()

today()

current date

current time

current timestamp

current utc timestamp

In addition to coalesce() expressions, the same problem could occur with the now() or related function used within these expressions:

ISNULL()

IFNULL()

IF

CASE

ARGN

This has been fixed.

A workaround is to increase the -gn value of the server.

€ U+20AC Euro sign

� U+017D Latin capital letter Z with caron

� U+017E Latin small letter Z with caron

As a workaround, add "KEY 'unused'" to either statement; whatever key is specify will be ignored.

This fix also addressed a problem where warnings were emitted into the SQL file if the ulinit utility was run with 杔.

A workaround is to disable intra-query parallelism by setting the max_query_tasks option to 1.

For example:

Connection 1 deletes row with primary key value 2 but does not commit

Connection 2 inserts row with primary key value 2 - here there was an undetected row locked or download conflict if connection 2 was synchronizing

When connection 1 was rolled back, a database corruption mau have occurred. This has been fixed.

2. Duplicate primary keys during a download were not detected. If the download contained two or more rows with the same primary key, the last row would be kept (this is not neccessarily correct if the download_cursor script does not have an ORDER BY clause). This has been fixed. UltraLiteJ will now report the SQLE_DUPLICATE_ROW_FOUND_IN_DOWNLOAD error when this is detected. Like UltraLite, this error is only reported for duplicate rows in the download_cursor - no detection is done for duplicate rows in the download_delete_cursor, or if the same primary key is in both the download_cursor and download_delete_cursor.

3. The all database synchronization would not have included new tables if the database was synchronized before the table was added (unless the application was restarted). This has been corrected.

SELECT *

FROM proc()

could have been incorrectly described by the server as having a single column with the name "expression". The result set returned by the statement would have had the correct schema. This has been fixed.

Calling the procedure directly (i.e. call proc()) was not affected by this problem, and would be correctly described, so this statement can be used as a workaround. Describing the schema of the result set using a RESULT clause is also a workaround. To fix procedures created with older servers, an ALTER PROCEDURE <proc> RECOMPILE has to be executed for each such procedure.

SELECT [...,]contents[,...] FROM dir_table WHERE [...AND] file_name=[string|variable] [AND...]

now execute much faster. In the above example, dir_table is a proxy table to a directory access server, the WHERE clause contains zero or more AND clauses and no OR clauses, and one of the predicates in the WHERE clause is of the form file_name=[string | variable].

Execution of a TRUNCATE TABLE statement would have failed with an SQLE_UNCOMMITTED_TRANSACTIONS error ("You cannot synchronize or upgrade with uncommitted transactions <table name>"), if there were any uncommitted transactions, even on the current connection. This has been fixed. The TRUNCATE TABLE statement will now only report an error if there are uncommitted inserts and updates on another transaction. With this respect, TRUNCATE now behaves exactly like DELETE.

Synchronizations with the special truncate table download_delete_cursor may have resulted in a fatal error if a second connection did a commit after the truncate was applied, but before the the synchronization completed. This would have occurred only if there were more than a few rows in the table being truncated (248 for 1K page size). This has been fixed.

Also, if a statemented executing at isolation 3 used an index scan for a table marked for update, then the rows of the table could have incorrectly been locked with exclusive (write) locks, instead of intent locks. The consequence of this was that other connections attempting to read the row could have blocked, even if the row was ultimately not modified.

These problems have now been fixed.

Note that the likelihood of encountering the problem with 11.x servers is extremely small.

Note that returning a result set from within an atomic block is not allowed, and an error is issued in this case. This behaviour has not changed.

* No attribute - return a value that is %-decoded and character set translated to the database character set encoding. %-encoded UTF-8 decoding is supported. This should be the default usage for retrieving textual x-www-form-urlencoded data.

* @BINARY should be used to retrieve x-www-form-urlencoded binary data values. The value is %-decoded with no character set translation, UTF-8 %-encoded data is not supported in this mode since %-encoded data are simply decoded into their equivalent byte representation.

* @TRANSPORT will return the raw HTTP transport form of the value, where %-encodings are preserved.

This change does not impact the usage of @BINARY for multi-part/form-data form based uploads. The use of @TRANSPORT in this case is identical to @BINARY. Prior to this change, the work-around was to HTTP_DECODE the value returned by the HTTP_VARIABLE function when it was called with an @BINARY attribute.

INSERT INTO t1(pk,build) VALUES ( {ml r.pk}, {ml r.build})

However, the following script would have failed, because the "ui" in the column list for the insert would have been replaced:

{ml INSERT INTO t1(pk,build) VALUES ( r.pk, r.build )}

This has now been fixed.

"ORA-01461: can bind a LONG value only for insert into a LONG column"

This problem has now been fixed.

select today(*) A1, D2.A2, D3.A3

from dummy D1

left join ( select today(*) A2 from sys.dummy group by A2 ) D2 on 1=1

left join ( select today(*) A3 from sys.dummy group by A3 ) D3 on 1=0

This fault could have lead to the following (non-fatal) assertion failure:

Run time SQL error -- *** ERROR *** Assertion failed: 102501 (10.0.1.####)

Work table: NULL value inserted into not-NULL column

SQLCODE=-300, ODBC 3 State="HY000"

This has been fixed.

/*

* ERROR: Unexpected error encountered while generating event.

* Error for: RHS of #set statement is null. Context will not be modified. table-scripts\download_cursor.vm

* [line 59, column 8]

*/

This problem only happened in the New Synchronization Model wizard, not when custom download subset was enabled in the Mappings editor. The problem has been fixed for new synchronization models.

To work around the problem, in the Mappings editor change the Download Subset (Dnld. Sub.) to None and then back to Custom, then switch back to the Events editor.

For example:

select list(e1 ORDER BY e2, e3 ), list( e4 ORDER BY e2,e3,e5)

from ....

The first ORDER BY clause 'e2, e3' is subsumed by the second ORDER BY clause 'e2,e3,e5'.

For example:

"The server monitor was unable to contact the MobiLink server. The host 'mlstress02' is still available. Error: Timed out trying to read 128 bytes.rWms
"

This has been fixed.

[dbisql]

allowPasswordsInFavorites=false

Allowable values for this directive are "true" and "false". The default is "true". If this directive is added to OEM.INI, and its value is "false", the visible change is in the "Add to Favorites" window: the checkbox called "Save the connection password" will not be visible, and will be assumed to be unselected.

For example:

select V1.N1

from T1 V0

left outer join

( select 'M' as N1

from ( select V4.c as xx

from T2 as V4,( select count(*) as N3 from T3 ) V3

where V4.b = V3.N3 ) V2

) V1

on V0.a = V1.N1

This has now been fixed.

a very complex statement. This has been fixed. The server will now return the SQL error

SQLSTATE_SYNTACTIC_LIMIT.

Also when running an mlsrv.sh file with an argument that contained spaces, such as "dsn=SQL Anywhere 11 Demo", would have failed. The connection string is now quoted within the file to correct this.

A workaround is to manually delete the remote database and transaction log before redeploying.

1) All of the data in the download stream had been applied, but had not yet been committed to the remote database.

2) An SQL Error had been generated by dbmlsync before the download had been committed. Examples of errors that could have occurred include an error occurring in the sp_hook_dbmlsync_download_ri_violation or the sp_hook_dbmlsync_download_end hooks, or an error occurring as dbmlsync had attempted to resolve referential integrity issues.

3) Another hook had been defined in the remote database that would have executed on another connection. For example, the sp_hook_dbmlsync_download_log_ri_violation or the sp_hook_dbmlsync_all_error hooks would have executed on a separate connection.

This problem has now been fixed, and the proper last download timestamp is now sent up to the MobiLink server in the synchronization when this situation occurs.

information for the term could have been lost. This could have caused incorrect results to be returned. This has now been fixed.

Example:

Consider a text index on col1 of table t, built using a text configuration with MAXIMUM TERM LENGTH equal to 7, MINIMUM TERM LENGTH equal to 1:

SELECT t.*

FROM t CONTAINS( col1, '"there is an elephant at the zoo"' );

Before this fix, the query above would be equivalent to:

SELECT t.*

FROM t CONTAINS( col1, '"there is an at the zoo"' );

and would miss documents containing the phrase "there is an elephant at the zoo".

If the "FIPS-approved Strong Encryption" feature is not already installed, proceed as follows after applying the EBF:

- From Add & Remove Programs, select SQL Anywhere 11 and click the Change button.

- Select the Add option and enter the Add-on Registration Key and click Next.

- In the dialog, ensure that the "FIPS-approved Strong Encryption" feature is selected and proceed.

If the "FIPS-approved Strong Encryption" feature is already installed, proceed as follows after applying the EBF:

- From Add & Remove Programs, select SQL Anywhere 11 and click the Change button.

- Select the Modify option and de-select the "FIPS-approved Strong Encryption" feature, to temporarily remove it, and proceed.

- Again from Add & Remove Programs, select SQL Anywhere 11 and click the Change button.

- Select the Modify option and select the "FIPS-approved Strong Encryption" feature and proceed.

Please note that if an application does use Open Client 15 to connect to SQL Anywhere, then it will be necessary to update the version of Open Client 15 once this fix is installed.

SQLGetConnectAttr( hdbc, SQL_ATTR_CONNECTION_DEAD, ... )

after an error occurred, the iAnywhere Solutions ODBC driver for Oracle could told the application that the connection was still alive, even though the connection had actually been killed, or had timed out. If this problem occurred for the MobiLink server main connection, in most cases, the server would have displayed the following messages:

[10009] MobiLink table 'ml_scripts_modified' is damaged

[-10020] Unable to flush scripts

and refused any synchronization requests. This MobiLink server would then have needed to be be restarted in order to carry on any synchronization. This problem is fixed now.

Note that the memory leak does not exist with the 64-bit Unix versions of the SQL Anywhere ODBC Driver Manager, and that the SQL Anywhere ODBC Driver Manager does not exist on Windows platforms. It should also be noted that several SQL Anywhere components on Unix platforms use the SQL Anywhere ODBC Driver Manager implicitly, and are therefore impacted by this problem. In particular, the server will use the SQL Anywhere ODBC Driver Manager for performing Remote Data Access operations; as well, the Mobilink Server uses the SQL Anywhere ODBC Driver Manager to talk to the consolidated database. Also, the iAnywhere JDBC driver uses the SQL Anywhere ODBC Driver Manager to make connections.

As a workaround, add "-a <log file name>" to the server's command line. When recovery completes, the server will shut down. Afterwards, restart the server normally (without the "-a <log file name>" option).

a) cache collection was enabled on the previous run of the database,

b) cache warming was enabled on the current run of the database,

c) the server accessed a large number of pages during the cache collection interval, i.e., the first queries executed against the database referenced a large number of pages (as may be the case in a scan of a large table or set of tables).

This has been fixed. Note, cache collection is on by default, and is controlled by the -cc server command line option. A workaround is to disable cache warming using the -cr- server command line option.

Examples of queries that would failed:

CONTAINS( col, 'apple & banana & tomato & cucumber & ..... & parsley' )

CONTAINS( col, 'dog | cat | camel | .... | chipmunk' )

where ... is a list of terms separated by the & or | operator.

-ca "keyword=value;..."

This new option can be used to let the MobiLink servers connect to another database that is running on a less busy database server, and then the MobiLink servers will use this

second database to maintain their state information for the server farm. This second database can be running on an SA, ASE, Microsoft SQL Server, Oracle, DB2, or MySQL database server. However, it must contain the MobiLink server system objects.

Note, the -ca command line option must be the same for all the MobiLink servers across the same server farm, otherwise, redundant synchronizations may not be detected and data inconsistency can happen.

Internal error: SQLCODE: -108, SQLSTATE: []

Source statement: SET OPTION isolation_level=read_committed

QAnywhere Agent failed to initialize message store

This has been fixed so that the agent now returns error code -103 as expected

For example:

CREATE PROCEDURE _TEST_PROC

(

@IN_VAL1 NUMERIC(7, 0),

@IN_VAL2 NUMERIC(7, 0),

@OUT_VAL NUMERIC(7, 0) OUTPUT

)

AS

BEGIN

SET @OUT_VAL = @IN_VAL1 + @IN_VAL2

END

If the statement "CALL PROCEDURE _TEST_PROC( 100, 200, ? )" was prepared, and the third parameter was bound as SQL_PARAM_INPUT_OUTPUT, the result after execution was 0. It should have been 300. If the parameter was bound as SQL_PARAM_OUTPUT, the result returned was correct.This problem has been fixed.

Note that in the above Transact SQL procedure, OUT_VAL is an INOUT parameter, since Transact SQL parameters are always INPUT and the addition of the OUTPUT clause makes them INOUT.

SELECT [...,]contents[,...] FROM dir_table WHERE [...AND] file_name=[string|variable] [AND...]

These quesries now execute much faster as the "file_name=" predicate evaluation is now pushed down into the directory access server. Unfortunately, if the "file_name=" was specified as a fully qualified reference as in "dir_table.file_name=", then the predicate would not have been pushed down. This problem has now been fixed.

Had Index Persistence been turned off for the database and a row with the same primary key been inserted or downloaded afterwards, there would have been a primary key violation the next time the database was shutdown and re-connected to.

Also, primary key violations could have occurred on databases with Index Persistence tuirned off after error recovery from failed synchronizations.

These problems have now been fixed.

Note, with Transact SQL outer joins, a CONTAINS clause is permitted only in the WHERE clause, and can only reference the preserved side of the join, not the null-supplying side.

Performance may improve when all of the following conditions are met:

- an application has only one connection

- fetching a result set with many thousands of rows

- each row in the result set has small number of columns

- the data size of the fetched columns is small

- prefetch is enabled

- the connection is over TCP/IP (shared memory connections are not likely to see a significant performance improvement)

Note, this problem did not affect the Initialization utility (dbinit), or applications calling the dbtools library.

SQLCODE Error

-193 Primary key for table '%1' is not unique : Primary key value ('%2')

-194 No primary key value for foreign key '%1' in table '%2'

-195 Column '%1' in table '%2' cannot be NULL

-196 Index '%1' for table '%2' would not be unique

- ENCODING 'UTF-8' was specified

- the destination table has both CHAR and NCHAR columns

- the CHAR encoding was not UTF-8

- file data that was destined for a CHAR column contained illegal characters or characters which could not be converted losslessly to the CHAR encoding

the server could have crashed. This problem has been fixed.

behaviour, allowing the driver to be used with such driver managers, as long as the application does not make any wide calls. Wide calls through iODBC, or any other driver manager with similar semantics, remain unsupported.

[JavaAppLauncher Warning] Java application launched from PPC or bad stub. Relaunching in 32-bit, and tagging sub-processes to prefer 32-bit with $JAVA_ARCH=i386.

[JavaAppLauncher Error] This process is [i386] and was re-exec'd from [i386], but for some reason we are trying re-exec to [].

[JavaAppLauncher Error] unable to find a version of Java to launch

This has been fixed.

There are two workarounds:

1. Use the command line launchers instead of the GUI launchers.

2. Run the following from a terminal:

. <SQL Anywhere 11 Installation Directory>/System/bin64/sa_config.sh

for TOOL in DBConsole.app InteractiveSQL.app MobiLinkMonitor.app

SybaseCentral.app ; do chmod +w "$SQLANY11/../$TOOL/Contents/MacOS/

JavaApplicationStub" ; cp /System/Library/Frameworks/JavaVM.framework/

Versions/A/Resources/MacOS/JavaApplicationStub64 "$SQLANY11/../$TOOL/

Contents/MacOS/JavaApplicationStub" ; done

Note, the portion of the script from "for" to "done" should be one single line.

The following bits should not be returned:

SQL_SSF_CONVERT

SQL_SSF_SUBSTRING

SQL_SSF_TRIM_BOTH

SQL_SSF_TRIM_LEADING

SQL_SSF_TRIM_TRAILING

as SQL Anywhere does not support the above SQL 92 functions (e.g., SELECT TRIM(BOTH ' ' FROM ' abc ').

Only the following following bits should be returned.

SQL_SSF_LOWER

SQL_SSF_UPPER

This problem has been fixed.

Also, when the server returned an estimated, rather than an exact row count, Cursor.rowcount was set to a negative number, the absolute value of which was the estimate. Cursor.rowcount will now be set to -1 in this case.

By default the ODBC driver uses the tilde character (~), but some applications do not properly query the ODBC driver to see what escape character is used, and assume that it is the backslash character (\). With this new connection parameter, users can specify the escape character in the connection string to make these applications work properly.

"DSN=My Datasource;UID=xxx;PWD=xxx;ESCAPE=\;ENG=myserver"

The problem can be avoided using "options(force optimization)" on the INSERT statement.

A workaround is to rebuild the database prior to running sa_index_density. Re-creating or reorganizing the offending index may also be a workaround.

For example:

CREATE PROCEDURE foo ( IN a INTEGER, OUT b INTEGER )

BEGIN

SET b = a * 2

END

No information was returned when describing input arguments for the statement "call foo( b=?, a=?)". This has been fixed.

been fixed.

1. The CREATE TABLE ON clause is now supported. Previously, consistently creating tables was not possible, although exporting to existing tables worked.

2. If the table name was quoted with backticks, the output operation would have failed. This has been fixed.

3. The OUTPUT statement would not have worked at all if the USING clause contained "driver=" rather than "dsn=". This has been fixed.

4. Exporting a result set which contains primary key columns with the CREATE TABLE ON clause would have failed, as the Excel ODBC driver does not support the required SQL to designate primary keys. This limitation has now been worked around.

For datatypes that the OUTPUT statement cannot handle, such as BINARY or LONG BINARY, the user must do the conversion to a supported type explicitly. Note that the Interactive SQL utility in version 10 and earlier did allow exporting BINARY columns, although it simply treated bytes as characters when it wrote them to the Excel file. It's unclear whether that was a "good thing" in general. To emulate that behavior in now, use the following:

SELECT ID, Name, Description, Size, Color, Quantity,

UnitPrice, CAST(Photo AS LONG VARCHAR )

FROM Products;

OUTPUT USING 'DSN=MyEXCELDSN' INTO tabProducts CREATE TABLE ON

Also note that the Microsoft Excel ODBC driver treats the file as read-only by default. To write to the file, turn off the "Read only" flag in the DSN or, if not using a DSN, include "ReadOnly=0" as a connection parameter in the OUTPUT statement.

For example:

SELECT * FROM Departments;

OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls);

dbq=c:\test\test.xls;ReadOnly=0'

INTO Departments CREATE TABLE ON

-l "subject=$remote_id;

content=sync cardealer;

action='run dbmlsync.exe -c \"filedsn=c:\my fdsns\CarDealer.dsn\" -ot dbmlsync.log -k -e sa=on';"

This works correctly on the command line, because the tokenizer of the commandline processor in the OS supports escaping double quotes using a backslash, but using the same command in a configuration file would have failed. This problem has been fixed by improving the common configuration file parsing routine to support <slash><slash> and <slash><double quote> as escape sequences for <slash> and <double quote> respectively. The escape mechanism is effective only inside a quoted token.

A workaround to correct this problem is to execute the following commands (requires administrator priviledges):

/opt/samonitor11/bin32/samonitor.sh uninstall

/opt/samonitor11/bin32/samonitor.sh install

import sqlanydb

import decimal

def convert_to_decimal(num):

return decimal.Decimal(num)

sqlanydb.register_converter(sqlanydb.DT_DECIMAL, convert_to_decimal)

Converters may be registered for the following database types:

DT_DATE

DT_TIME

DT_TIMESTAMP

DT_VARCHAR

DT_FIXCHAR

DT_LONGVARCHAR

DT_STRING

DT_DOUBLE

DT_FLOAT

DT_DECIMAL

DT_INT

DT_SMALLINT

DT_BINARY

DT_LONGBINARY

DT_TINYINT

DT_BIGINT

DT_UNSINT

DT_UNSSMALLINT

DT_UNSBIGINT

DT_BIT

DT_LONGNVARCHAR

e.g., ./setup -k <FIPS registration key>

or ./setup -regkey <FIPS registration key>

It is not possible to specify the registration key through the EBF Installer UI itself. Subsequent invocations of the EBF install will then update previously installed FIPS components without needing to specify a registration key.

The problem hass now been fixed.

MESSAGE ... FOR CONNECTION ... IMMEDIATE

did not contain the IMMEDIATE clause in the stored definition of the procedure. This has been fixed. A workaround is to execute the MESSAGE statement using EXECUTE IMMEDIATE.

Note, this new feature is actually available for use with all the graphical administration tools.

[10101] Synchronization request from client '?' was rejected

where ? is the rejected remote ID. A more complete description of this warning code can be found in the documentation.

1. Attempting to export CHAR, LONG VARCHAR, NCHAR, NVARCHAR or LONG NVARCHAR values, would have failed with a message that the type was unsupported. Now, these types are mapped to VARCHAR (the closest type supported by the Excel driver).

Note that the Excel ODBC driver only supports text column widths up to 255. Attempting to export longer values, will still get an error message.

2. The driver portion of the OUTPUT USING string had to be specified as "Driver=" or "driver=". Now, that part of the string is treated without respect to case, so "DRIVER=" is also accepted.

3. REAL, FLOAT, and BIGINT values could not have been exported due to limitations in the Excel ODBC driver. These limitations have been worked around.

4. Numbers that were not MONEY or SMALLMONEY were exported as CURRENCY (i.e. they were displayed with a dollar sign in the Excel spreadsheet). Now they are exported as numbers. MONEY and SMALLMONEY are still exported as CURRENCY.

5. The Export Wizard would consistently have failed when exporting to tables it had to create. The error message was "Syntax error in the CREATE TABLE statement". This has been fixed.

Note that this problem only affected dbxtract, and not dbunload.

A workaround is to specify the temporary file location using one of the SATMP, TMP, TMPDIR or TEMP environment variables.

A manual workaround is to update the timestamp column for each row where it is zero, after the column is added.

For example:

The subquery optimization process supported by the SQL Anywhere Optimizer since version 9.0, is described in the patent [1]. The example below illustrates the five execution strategies considered during the optimization process for a SQL statement using a grouped subquery. This issue is related to a bug introduced in version 10.0.0 when two rowids must be built for each base table to support Snapshot isolation. The bug would have manifest itself if and only if Window Strategy 4 or Window Strategy 5 was chosen in the best access plan.

[1] System and Methodology for Cost-based Subquery Optimization Using a Left Deep Tree Join Enumeration Algorithm", Anisoara Nica, US Patent 20040220923, awarded August 2009

Original query Q0:

SELECT so.id, so.order_date, p.*

FROM sales_order so, sales_order_items soi, product p

WHERE

so.id = soi.id

AND soi.prod_id = p.id

AND p.quantity = (SELECT max(soi1.quantity) FROM sales_order_items soi1 WHERE soi1.prod_id = p.id )

SQL Anywhere Optimizer subquery execution strategy 1, Q1, evaluate subquery as an expression:

SELECT so.id, so.order_date, p.*

FROM sales_order so, sales_order_items soi, product p

WHERE

so.id = soi.id

AND soi.prod_id = p.id

AND p.quantity = (SELECT max(soi1.quantity) FROM sales_order_items soi1 WHERE soi1.prod_id = p.id )

SQL Anywhere Optimizer subquery execution strategy 2, Q2, evaluate subquery as an uncorrelated derived table:

SELECT so.id, so.order_date, p.*

FROM sales_order so, sales_order_items soi, product p,

(SELECT max(soi1.quantity), soi1.prod_id FROM sales_order_items soi1 GROUP BY soi1.prod_id ) as DT2 (max, prod_id)

WHERE

so.id = soi.id

AND soi.prod_id = p.id

AND p.quantity = DT2.max AND p.id = DT2.prod_id

SQL Anywhere Optimizer subquery execution strategy 3, Q3, evaluate subquery as a derived table with Bloom-filter predicates:

SELECT so.id, so.order_date, p.*

FROM sales_order so, sales_order_items soi, product p,

(SELECT max(soi1.quantity), soi1.prod_id FROM sales_order_items soi1 WHERE soi1.prod_id IN Bloom-filter( p.id) GROUP BY soi1.prod_id ) as DT3 (max, prod_id)

WHERE

so.id = soi.id

AND soi.prod_id = p.id

AND p.quantity = DT3.max AND p.id = DT3.prod_id

SQL Anywhere Optimizer subquery execution strategy 4, Q4, evaluate subquery as an uncorrelated derived table with WINDOW functions:

SELECT so.id, so.order_date, p.*

FROM sales_order so, product p, //// <<-- NOTE that "sales_order_items soi" was moved inside to the DT4

(SELECT max(soi1.quantity) OVER (PARTITION BY soi1.prod_id ), soi1.prod_id, soi1.id FROM sales_order_items soi1 ) as DT4 (max, prod_id, id)

WHERE

so.id = DT4.id

AND DT4.prod_id = p.id

AND p.quantity = DT4.max AND p.id = DT4.prod_id

SQL Anywhere Optimizer subquery execution strategy 5, Q5, evaluate subquery as a derived table with WINDOW functions, with Bloom-filter predicates:

SELECT so.id, so.order_date, p.*

FROM sales_order so, product p, //// <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<------------------------------------------NOTE that "sales_order_items soi" was moved inside to the DT5

(SELECT max(soi1.quantity) OVER (PARTITION BY soi1.prod_id ), soi1.prod_id, soi1.id WHERE soi1.prod_id IN Bloom-filter( p.id) FROM sales_order_items soi1 ) as DT5 (max, prod_id, id)

WHERE

so.id = DT5.id

AND DT5.prod_id = p.id

AND p.quantity = DT5.max AND p.id = DT5.prod_id

<main> [-10002] Consolidated database server or ODBC error: ODBC:

[Sybase][ODBC Driver][SQL Anywhere]Primary key for table 'ml_property' is not unique:

Primary key value("'ML','server_info','release_version''")

(ODBC State = 23000, Native error code = -193)

when the consolidated database was running on a SQL Anywhere server. This problem has now been fixed.

SELECT ... INTO LOCAL TEMPORARY TABLE localtemptab FROM extenv_proc()

where extenv_proc() was an external environment procedure that returned a result set, then the SELECT ... INTO statement would have succeeded, but attempting to subsequently access the newly created localtemptab would have resulted in a 'table not found' error. This problem has now been fixed.

WARNING: When choosing to use a database with the new file format described below you give up the ability of being able to use older software that is not aware of the file format change. Therefore, once you create this database you will not be able to start an old server with such a database file. Any attempt to start the server with a build prior to this fix will generate an error "server must be upgraded to start 'database' ( capability 37 missing)". Syntax has been added to dbinit and the create database statement to allow for the creation of databases with this new file format. In order to create a database with this new format, use either:

- dbinit -xw mydb.db // -xw (creates a database with the new file format)

- CREATE DATABASE mydb.db CAPABILITY 'TORNWRITEFIX' ...

Syntax has also been added to dbunload to rebuild databases so that the new database will be created with the new file format. In order to rebuild a database with the old format to one with the new format use:

- dbunload.exe -c 'your connection string to your old db' -xw -ar // this will replace your old database with a new one

- dbunload.exe -c 'your connection string to your old db' -xw -an new.db // this will create new.db in the new file format while maintaining your old database in the old file format

NOTE: make sure to have a backup of your database file before attempting this. Also if your database is strongly encrypted then you'll need to provide the encryption key for both the old database and the new database file using the -ek command line parameter.

In order to upgrade a database with this new file format from version 9 to version 10 or version 11 you'll need to use 10.0.1 3930 or greater or 11.0.1 2278 or greater.

CREATE SYNCHRONIZATION PROFILE ...

ALTER SYNCHRONIZATION PROFILE ...

DROP SYNCHRONIZATION PROFILE ...

SYNCHRONIZE ...

This has been corrected. The workaround is to not use the -eu command line option on SQLPP.

A write failed with error code: (6), The handle is invalid.

Fatal error: disk full when writing to "???"

This problem has been fixed.

A related problem was also fixed where failure to create a temporary file for the utility_db would have cause the server to crash.

1) the MobiLink server connected to one node on an Oracle RAC;

2) another application, A connected to another node on the same Oracle RAC;

3) application A modifies rows in a synchronization table without commit;

4) a MobiLink client, R1 issues a synchronization request that contains a time-based download from this table;

5) the MobiLink server completes the synchronization request successfully;

6) A commits its transaction;

Then the rows modified by application A would not be down loaded to remote R1. This problem has now been fixed.

Note, as a result of this change, the Oracle account used by the MobiLink server must now have permission for the GV_$TRANSACTION Oracle system view instead of V_$TRANSACTION. Only SYS can grant this access. The Oracle syntax for granting this access is:

grant select on SYS.GV_$TRANSACTION to <user-name>

Table "ml_scripts_modified" not found

during each synchronization, when a pooled database connection was reused.This problem has now been fixed.

The workaround is to fix the column default definition so that no error occurs.

reloaded. This has been fixed.

A workaround is to recreate the table under question.

Note: if the desktop environment has a system tray supported by Java 1.6, it is possible to shut down the server monitor cleanly by right clicking on the SQL Anywhere Monitor logo and choosing "Shutdown"

As a workaround, use braces to specify the TCP options, eg. LINKS=tcpip{host=(<ipv6 address>):<port>}".

Note, row level triggers do not have this problem.

1) In order to determine if a stored procedure returns a result set, or if a procedure uses Oracle VARRAYs, the iAnywhere Solutions Oracle ODBC driver needs to describe the stored procedure through Oracle OCI functions. The driver now provides an extra connection parameter, ProcOwner (long form) or POwner (short form), which is used to by tyen driver to describe all the stored procedures invoked by the application. This parameter can be given as followis:

-c "uid=...;pwd=...;procowner=my_procedure_owner;..."

2) The iAnywhere Solutions ODBC driver for Oracle now supports VARRAYs used by packaged procedures.

Databases names must follow the format for a fully qualified, absolute path file name as described in the file URL format as part of IETF RFCs 1738 and 2396. That RFC dictates that a file URL takes the form:

file://<host>/<path>

In the form above, <host> is the fully qualified domain name of the system on which the <path> is accessible, and <path> is a hierarchical directory path of the form:

<root>/<directory>/<directory>/.../<name>.

For BlackBerry devices, the internal flash is accessed using URIs starting with file:///store/ (case sensitive) while the SD media card is accessed using URIs starting with file:///SDCard/ (case sensitive). File paths are case insensitive except for the host portion which is case sensitive. Percent (%) characters have special meaning and relative paths (directories "." and "..") are not allowed. For more information on file name restrictions, please consult the BlackBerry JDE (4.2 or later) API Reference for the javax.microedition.io.file package.

The following example creates a configuration for using the media card. The database is store in the directory "ulj" and is called "test.ulj".

// BlackBerry media card Sample - use file:///store/ulj/test.ulj

// for internal flash

Connection conn;

ConfigFileME config = DatabaseManager.createConfigurationFileME(

"file:///SDCard/ulj/test.ulj"

);

try {

conn = DatabaseManager.connect(config);

} catch(ULjException ex) {

conn = DatabaseManager.createDatabase(config);

// Create the schema here.

}

To maintain support for BlackBerry 4.1 devices, a new distribution directory UltraLite\UltraLiteJ\BlackBerry4.2 has been added which contains files for 4.2 or later devices. BlackBerry 4.1 devices and simulators should continue to use the UltraLite\UltraLiteJ\J2meRIM11files. BlackBerry 4.2 and later devices can use either distribution if they do not require SD card or internal flash storage.

The UltraLiteJ Database Transfer tool has not been updated.

Note, this situation would have been due to file corruption on the disk. Transaction log pages are not stored within the database.

The complete syntax for the new clause in the various statements are:

- CREATE TABLE [IF NOT EXISTS] table-name (...)

- CREATE [UNIQUE] INDEX [IF NOT EXISTS] index-name ...

- CREATE PUBLICATION [IF NOT EXISTS] publication-name ...

A workaround for this problem is to perform an action that will trigger a

synchronization (eg. send another message, call triggerSendReceive, etc.)

Also, it is now possible to consistently export TIME, DATE, and TIMESTAMP values to Excel spreadsheets.

Note, this bug affected both the Interactive SQL utility, and the "Data" tab in Sybase Central.

Where possible, the QAnywhere server should recover gracefully from exceptions thrown by the JDBC driver and continue processing messages.

CREATE TABLE tbl1(a INTEGER, PRIMARY KEY (a))

INSERT INTO tbl1 VALUES (10000000)

Fetching the results using the Interactive SQL utility (dbisql) in command-line mode as follows:

dbisql -ul -c "uid=dba;pwd=sql;dbf=c:\temp\test.udb" select a from tbl1

would have returned the value 1000. The value was truncated in the display, and was only an issue when displaying the results to the command shell. This is now fixed.

processing queries containing large strings. This has been fixed.

To workaround this problem, use the "-q" command line option. That will print the error message in the console and will not prompt the user to press a key to continue.

The server failed to honour the per statement specification for isolation level and used instead other specifications. This problem has been resolved. Now the isolation level specified in the query text is given the highest precedence.

select * from sa_db_properties() where propname = 'HasTornWriteFix';

can be used to determine if the setting is ON or OFF. Turning the setting OFF requires a rebuild of the database using an engine with this fix.

processing queries containing large strings. This has been fixed.

After this change, a CONTAINS query cannot contain more than 300 valid terms (terms that are not in the STOPLIST

and are within permitted term length range).

A workaround is to define each of the callback routines. Example:

a_sync_db sync;

.

.

.

sync.confirmrtn = (MSG_CALLBACK) ConfirmCallback;

For example, if the engine was running in the directory /foo, the statement CREATE ENCRYPTED DATABASE 'new.db' FROM '/bar/source.db' was issued, and the transaction log name stored in source.db was 'source.log,' we would have tried to open /foo/source.log.

If the current working directory had a version of the log file that was incompatible with the source database, the operation would fail. This has been fixed.

It should be noted that this problem does not affect applications using the iAnywhere JDBC driver.

the server's current working directory, and was renamed when the mirror server was unavailable and the primary server was restarted, synchronization would have failed when the mirror server then became available again. This has been fixed.

Relevant third-party articles:

http://perspectives.mvdirona.com/2008/04/17/DisksLiesAndDamnDisks.aspx

http://msdn.microsoft.com/en-us/library/dd979523%28VS.85%29.aspx

http://research.microsoft.com/apps/pubs/default.aspx?id=70554

http://groups.google.com/group/microsoft.public.win32.programmer.kernel/browse_frm/

thread/4590ed3a4133828f/406cfb3a9deae044

and are within permitted term length range).

A sample of this type of query:

select 1 from T

where a = 1 and ( b = 2 or c = 8 ) and ( d = 4 or e = 10 )

and ( a = 1 or e = 7 or c = 9 )

The presence of this issue can be identified by examining a graphical plan with statistics. If the estimated number of rows coming out of a HashGroupBy or a Distinct is small (less than 100), and the actual number of rows is large (many thousands or more), the hash operator may be operating less efficiently than possible. The performance penalty for a query suffering from this problem is unlikely to exceed 100% (i.e twice as slow).

it has not yet been initialized". The script generated by dbunload -n could have failed trying to recompile views. This has been fixed.

As a work around, removing the comments allowed the analysis to proceed.

Note, these issues affected the Connect dialog when connecting to SQL Anywhere, regardless of which program was being used -- DBISQL, Sybase Central, or DBConsole.

For example: 'CREATE TABLE t1( v1 LONG VARCHAR, PRIMARY KEY(v1))'

The use of long datatypes in indexes is not supported by UltraLite, and inserting into the resulting table would have resulted in a crash. This has been corrected, long datatypes are now flagged as invalid when used in an index.

Now, all buttons display normally. However, the layout of the buttons is affected. They are aligned to the left, instead of in the center.

After the fix, buttons on other models of the BlackBerry remain legible.

For example:

select * from R left outer join T where R.X = 10 OR R.Y = T.Y

Both "R.X = 10" and "R.Y = T.Y" are sargable. An index must exist for on R<X> and R<Y>.

Note that using setBlob() requires significantly less memory than using setBinaryStream(), and also, for blob values greater than 256M in size, setBlob() may actually be the only option.

For example:

select * from R, S

where R.X NOT IN ( select T.X from T where T.N = S.I)

where the column T.N is of type numeric and the column S.I is of type integer.

SQLSetConnectAttr( dbc,

SA_SQL_ATTR_TXN_ISOLATION,

(SQLPOINTER)SA_SQL_TXN_READONLY_STATEMENT_SNAPSHOT,

SQL_IS_INTEGER );

The following isolation level options are available.

SQL_TXN_READ_UNCOMMITTED

SQL_TXN_READ_COMMITTED

SQL_TXN_REPEATABLE_READ

SQL_TXN_SERIALIZABLE

SA_SQL_TXN_SNAPSHOT

SA_SQL_TXN_STATEMENT_SNAPSHOT

SA_SQL_TXN_READONLY_STATEMENT_SNAPSHOT

When any of the "snapshot" isolation levels were selected, the ODBC driver would have set the transaction isolation level incorrectly upon connecting to the server. The following is an example of a SET statement that was executed:

SET TEMPORARY OPTION isolation_level = readonly-statement-snapshot;

This would have resulted in a syntax error and the server options would not have been changed. This problem has been fixed. The ODBC driver will now generate the following correct syntax with quotes around the isolation level value.

SET TEMPORARY OPTION isolation_level = 'readonly-statement-snapshot';

A workaround to this problem would be to stop and start the MobiLink server.

A workaround is to disable parallel plans (i.e. set MAX_QUERY_TASKS = 1).

For example:

There exists an index on T <X,Y>: create index T_xy on T (X,Y)

Select * from

T, R where T.X = 1 and T.X = R.X and T.Y > 10

Please note that when the SQL_ATTR_METADATA_ID attribute is set to SQL_TRUE, the string arguments to metadata functions are treated as identifiers, not strings (or patterns like "co%"). Identifiers can be delimited, in which case leading and trailing spaces are removed. Identifiers need not be delimited, in which case trailing spaces are removed.

IAS-RS-AFQ=<affinity_token>

The relay server will respect this affinity control when affinity information was not found in standard cookie nor in proprietary header.

400 Auto started rshost.exe but it exited with return code <?>.

This has been fixed by suppressing the startup error for the failed connections, as long as the connections can attach to the stage manager started by others.

A workaround is to rewrite procedures that cause this behaviour to use the XML generation functions as EXECUTE IMMEDIATEs with a trim. For example, :

CREATE PROCEDURE FOO()

BEGIN

SELECT XMLELEMENT('foo');

END;

could be rewritten as:

CREATE PROCEDURE FOO()

BEGIN

EXECUTE IMMEDIATE trim('SELECT XMLELEMENT(''foo'')');

END;

Note that such rewritten procedures will no longer be able to take advantage of plan caching.

E. 2009-10-22 14:39:32. <1036.440.ShmDebug> Internal Error! Freeing already freed memory!: 00011390

The error message is useful in reporting issue to tech support so that we can identify defects in the higher level logic and eliminate the source of the rare abuse.

[-10002] Consolidated database server or ODBC error: ODBC: [Microsoft][ODBC SQL Server Driver]Restricted data type attribute violation

This has been fixed.

A workaround is to drop indexes on the table(s) being queried recursively, although there may a performance implications to doing this, which could be significant.

A workaround is to recover without in-memory mode, but thist will cause the database on disk to be modified.

Error 2769: Custom Action CAD_writeStrings did not close 3 MSIHANDLEs.

This has been corrected by no longer using MSIHANDLEs in custom actions, but rather using PMSIHANDLE variables, which are automatically freed when they go out of scope.

dbisqlc -c "dbf=my.db;dbs=\"d:\tmp\spacey path\""

This problem has now been corrected.

Note that using quotation marks on the command line to start a database server worked correctly:

dbeng11 my.db -ds "d:\tmp\spacey path"

A related problem was found and fixed in dbisqlc which handled the START DATABASE statement itself, and constructed a connection string containing a "dbs=-ds ..." parameter, rather than passing the START DATABASE statement to the server. Dbisqlc was not putting quotes around a -ds parameter that contained spaces.

1) SQL Remote was running in send-only mode (-s)

2) SQL Remote was running in continuous mode

3) SQL Remote was satisfying a resend request for user "X", and was forced to re-scan the transaction logs

4) While scanning the transaction log, a SYNCHRONIZE SUBSCRIPTION operation was scanned for a user "Y"

5) User "Y" had already been sent the results of the SYNCHRONIZE SUBSCRIPTION operation in a previous run of SQL Remote.

This has been fixed by releasing the locks when the send phase of dbremote reaches the end of the transaction log and determines that the SYNCHRONIZE SUBSCRIPTION operation does not need to be sent to the remote user.

The problem can be worked around by stopping and starting the dbremote process that was running in send-only mode.

Row cannot be located for updating. Some values may have been changed since it was last read.

The OLE DB provider was failing to return DBSTATUS_S_ISNULL for the column value and returned an empty string instead. This caused ADO to generate an UPDATE statement with a WHERE clause expression of the form "column = ?" and a bound value of '' (a zero-length string). This problem has been fixed. ADO will now generate an UPDATE statement with a WHERE clause expression of the form "column IS NULL".

A workaround is to use a server-side cursor.

RS RS

11.0.0.ga-1529 11.0.0.1530 and up

11.0.1.ga and up

12.0 beta and up

RSOE

11.0.0.ga-1529 YES YES

RSOE

11.0.0.1530 and up NO YES

11.0.1.ga and up

12.0 beta and up

Mobilink Monitor, DBConsole) were launched on Mac OS X systems, two copies of

the admin tool icons were being displayed on the Dock. This has been fixed.

A work around is to assign DBNull.Value instead.

A workaround is to deploy to a file, and then run the deployed file.

1) The logging of unilitialized session indexes has been fixed

2) Reword logging of backend socket closing activities to "DoneReceive EOF" instead of "disconnecting" or "socket close"

Support has been added for Connection and Cursor .messages and .errorhandler attributes as outlined in PEP 249. Support has been added for SPARC 64 (and other platforms where sizeof(c_void_p) != sizeof(c_int)).

crash while receiving IPv6 traffic. This has been fixed.

In addition the characters \r(carriage return), \n(line break) and \t(tab) were not replaced by the character references and . As a result the these characters were being treated exactly the same as empty spaces in the unloaded XML file.

These problems have now been fixed.

This has been fixed so that a connection with be attempted in these cases and if the connection attempt is successful, the connection dialog will not be displayed.

1. If a foreign key constraint comes before the definition of the primary table;

2. If the order of the tables in the data part does not follow the order of the tables in the schema part;

These problems have been fixed, but rearranging the schema definition or the data in the XML will workaround these problems..

SELECT num/(num-3) FROM divisionby0

This problem has been fixed. ADO will now set the correct error number (11) and description (Division by zero) from the error code returned by OLE DB.

A work around for the problem is to drop all proxy procedures belonging to a remote server before executing the DROP REMOTE SERVER statement.

[Sybase][ODBC Driver][ASE] Foreign key table qualifier must be name of current database

This has been fixed.

Note, if there exists a text index in a database affected by this problem, it should be dropped and recreated to reclaim the space and avoid possible wrong results for full text queries.

For example, suppose a class Test exists:

public class Test {

public static String auth( InOutInteger status,

String user_name,

String p1,

String p2 )

{

return "insert into j_authparm_T2 (pk, c1, c2, c3) values (?,?,?,?)";

}

public static String auth( InOutInteger status,

String user_name,

String p1,

String p2,

String p3 )

{

return "insert into j_authparm_T2 (pk, c1, c2, c3, c4) values (?,?,?,?,?)";

}

}

Then the auth method can be registered as the script for the authenticate_parameters event:

call ml_add_java_connection_script( 'the version', 'authenticate_parameters', 'Test.auth' )

and invoking dbmlsync with different -ap switches will give different behaviour:

"-ap parm1,parm2" will call the first 'auth' overload

"-ap parm1,parm2,parm3" will call the second 'auth' overload

"-ap parm1" will print the error, "[-10362] No overload matching 'auth(ianywhere.ml.script.MLInOutInteger, java.lang.String, java.lang.String)' was found in class 'Test'", because there are not enough parameters to call any of the overloads

"-ap parm1,parm2,parm3,parm4" will invoke the second 'auth' overload, but the returned SQL will cause error "[-10094] Expecting 3 authentication parameter(s) from client, but received 4 for script insert into j_authparm_T2 (pk, c1, c2, c3, c4) values (?,?,?,?,?)" because the MobiLink server will accept a Java or .NET authentication_parameters script if it has the same number of parameters or fewer, but SQL authentication_parameters scripts must match the exact number of parameters. Some future version of the server may place that restriction on Java and .NET authentication_parameters scripts as well.

1. INSERT () VALUES ();

2. INSERT () select-statement;

3. ALTER TABLE ADD column-definition;

4. ALTER TABLE DROP column-name;

5. ALTER TABLE ALTER column-definition.

This has been corrected. Now all the null values are preserved in these scenarios.

Note, testing has shown that the new correct behaviour can cause a significant performance degradation for server running databases without a transaction log (where every commit becomes a checkpoint). The amount of degradation will depend on the application, and on the hardware in use.

outgoing connection to another server participating in the mirroring system failed. This has now been fixed.

A work around is to increase the heap space in the java starting line by adding the VM option -Xmx256m. This also applies to the batch file that starts uljunload, uljload and uljinfo.

CREATE ENCRYPTED [TABLE] DATABASE newfile FROM oldfile

KEY newkey [ALGORITHM alg] [OLD KEY oldkey]

CREATE DECRYPTED DATABASE newfile FROM oldfile KEY oldkey

Execution of either of these statements in the Interactive SQL utility would have left the values of "oldKey" or "newKey" in plain text in the command history. This has been corrected so that these values are now obfuscated.

Note, using compressed columns will avoid the bad-code path and is a suitable work-around for this problem.

For example (using SQL escape sequences) the string 'Ann\u00E9e' would have downloaded as 'Ann\u00E9e\u0000' the and string 'Enqu\u00EAte qualit\uooE9 deux' would have downloaded as 'Enqu\u00EAte qualit\uooE9 deux\u0000\u0000'.

This has been fixed.

If the "FIPS-approved Strong Encryption" feature is not already installed, proceed as follows after applying the EBF:

- From Add & Remove Programs, select SQL Anywhere 11 and click the Change button.

- Select the Add option and enter the Add-on Registration Key and click Next.

- In the dialog, ensure that the "FIPS-approved Strong Encryption" feature is selected and proceed.

If the "FIPS-approved Strong Encryption" feature is already installed, proceed as follows after applying the EBF:

- From Add & Remove Programs, select SQL Anywhere 11 and click the Change button.

- Select the Modify option and de-select the "FIPS-approved Strong Encryption" feature, to temporarily remove it, and proceed.

- Again from Add & Remove Programs, select SQL Anywhere 11 and click the Change button.

- Select the Modify option and select the "FIPS-approved Strong Encryption" feature and proceed.

Also, when binding null values, dbcapi required a valid type to be specified. This is no longer required.

Note that a workaround is to use Blob.getBytes() directly, instead of using the Blob InputStream.

This has been corrected so that if any parameters passed to the STRING() function are non-NULL, then any NULL parameters are treated as empty strings. This now conforms with the SQL Anywhere server.

Note that in general, applications do not explicitly need to close DatabaseMetaData Statement objects; hence the chances of an application crashing due to this problem are rare. Closing the ResultSet of a DatabaseMetaData call is not uncommon and not affected by this.

option was not specified, a data source would have been created containing "LINKS=ShMem,TCPIP". This has been fixed, the -c option is now required when -w is used.

the SDK15 ODBC driver. According to SDK document, the SDK ODBC Driver "libsybdrvodb.so"

require third party Driver Manager.

With the ODBC DSN, Mobilink Server try to load the ODBC driver when the client sync. request, then Mobilink Server will crash.

Note, the "ULSQLCONNECT" environment variable is now treated the same way.

This change only affects machines running non-Windows operating systems.

A downloaded value for table 'table_name' (column #column_number) was either

too big or invalid for the remote schema type

and then aborted the synchronization, when a client was trying to download data from a table that contained NCHAR, NVARCHAR or LONG NVARCHAR columns, even when NCHAR, NVARCHAR or LONG NVARCHAR data was uploaded in a previous synchronization. This problem has now been fixed.

ORA-24333: zero iteration count

This problem is fixed now.

select * from T where price * (1 - discount) > 500

and the table T was a remote table, then it was possible the query would have returned the wrong result. This was due to fact that the Remote Data Access layer sometimes failed to include the parenthesis when generating queries to be executed on remote servers. This problem has now been fixed.

Note, the Listener utility now tracks the following new Windows 6.x versions:

Windows Server 2008

Windows Vista

Windows 7

Windows Server 2008R2

Future versions will be tracked as "Unrecognized Windows NT ?.?"

Error - EXCEPTION: java.io.FileNotFoundException: C:\Program Files\SQL Anywhere 11\java\SQLAnywhere.jpr (the System cannot find the file specified).

A similar error message would also have appeared for the file Mobilink.jpr

This has been fixed, so that MSMs now work correctly as well.

Note, MSI installs built with the Deployment Wizard did not exhibit this behavior.