Behavior Changes in Oracle TimesTen In-Memory Database Part Number B31694-05 # This file details TimesTen API and # behavioral changes from release to release. # NOTE: this file should not be branched. # Entries for all releases should be in this file. # # This text file can be read by any editor; however, # it is also in Emacs outline mode format. # If loaded within Emacs, then run M-x describe-mode # to see what navigational features are available. # Outline headings are denoted by lines beginning with *'s. # NOTE: to keep this working, please do not remove # the lines at the end beginning with "Local variables". * Release 7.0.4.0.0 ** Daemon The user error log created by the TimesTen daemon is no longer world readable. ** Utilities: *** ttMigrate When restoring a data store, ttMigrate will no longer restore a foreign key name that is system-generated (TTFOREIGN_xxx). As a result, system-generated foreign key names may be different after a data store is saved and restored via ttMigrate. Note that the foreign key itself will be unchanged, only its SQL identifier would be affected (e.g., TTFOREIGN_2 could become TTFOREIGN_5). * Release 7.0.3.3.0 ** ODBC A change in the enforcement of maximum lengths of SQL parameter values has caused a change in the behavior of the ODBC functions SQLDescribeParam, SQLExecute and SQLExecDirect: For SQLDescribeParam, parameters that were previously reported as having type SQL_CHAR, SQL_WCHAR or SQL_BINARY are now described as having type SQL_VARCHAR, SQL_WVARCHAR or SQL_VARBINARY, respectively. This change does not apply to parameters whose values will be stored directly into the database, such as parameters found in the "values" clause of a SQL "insert" statement or the "set" clause of a SQL "update" statement. For SQLExecute and SQLExecDirect, TimesTen may accept parameter values that are larger than their maximum lengths as reported by SQLDescribeParam. For example, given a table t1 with column c1 of type char(10), and the query "select * from t1 where c1 = :a", TimesTen will allow values larger than 10 characters to be bound to the ":a" parameter. This change does not apply to parameters whose values will be stored directly into the database, such as parameters found in the "values" clause of a SQL "insert" statement or the "set" clause of a SQL "update" statement. * Release 7.0.3.0.0 ** SQL *** Cache Connect to Oracle Oracle temporary tables cannot be cached. ** JDBC PreparedStatment.executeBatch returnS an array containing update counts. In previous releases, it returned an array of SUCCESS_NO_INFO. ** Utilities *** ttSchema The ttSchema utility no longer outputs CREATE CACHE GROUP statements with autorefresh state ON. Autorefresh state ON is saved in a SQL comment. * Release 7.0.2.5.0 ** SQL The maximum sum of the total number of tables specified in a query and all temporary aggregates needed to handle the query is 32. A temporary aggregate is needed to handle scalar or aggregate subqueries. A query fails with the message "Statement that needs more than 31 nesting levels has not been implemented" when the sum of tables and temporary aggregates in a query is greater than 32. * Release 7.0.2.4.0 ** Utilities *** ttSchema ttSchema no longer outputs "create cache group" statements with autorefresh state ON. Autorefresh state ON is saved in a SQL comment. * Release 7.0.2.0.0 TimesTen does not operate on NFS-mounted systems. The -allowNetworkFiles daemon option, which existed only in the 7.0.1.0.0 release, has been disabled. * Release 7.0.1.0.0 ** Windows installation TimesTen-dependent dll's have been moved from System32 to TIMESTEN_INSTALL/bin. Please make sure that that directory is in the search PATH environment variable before running TimesTen applications or demos. ** SQL *** LENGTH, SUBSTR, and INSTR Functions The behavior of the LENGTH, SUBSTR, and INSTR functions when applied to Unicode supplementary characters has been changed to match Oracle. Use the new LENGTH4, SUBSTR4, and INSTR4 functions to get the UCS4 results corresponding to the old TimesTen behavior. *** Built-in Procedures ttCacheUidPwdSet cannot be invoked over a client-server connection. *** Cache Connect to Oracle Cache Connect emits an error if the Oracle client version is 10gR2 and the server version is between 10.1.0.1 and 10.1.0.4 or between 9.2.0.1 and  9.2.0.7.  This is to avoid an Oracle client bug. See Metalink Note 342791.1 or Bug 4499298. The Oracle bug is fixed in Oracle Database 10g R2 10.2.0.1.0, Oracle Database 10g R1 10.1.0.5.0 and Oracle9i 9.2.0.8.0. This fix affects both client and server, so the fix need to be applied on both client and server. When destroying a data store with either the ttDestroy utility or the ttDestroyDataStore C Utility API function, if the store contains any incremental autorefresh cache groups in the state ON or PAUSED, an error will be generated. The data store can be destroyed using the "-force" option to ttDestroy or using the ttDestroyDataStoreForce function, but this will result in objects such as tables and triggers remaining in the Oracle database. The same error will be given in these circumstances when attempting to connect with OverWrite=1, since that would destroy the existing database. There is no way to specify a "force" for OverWrite=1, so "ttDestroy -force" must be run first. * Release 7.0.0.0.0 ** Types and Type modes TimesTen 7.0 implements a set of new types that match Oracle semantics and behavior. These types are: NUMBER FLOAT BINARY_FLOAT BINARY_DOUBLE CHAR VARCHAR2 NCHAR NVARCHAR2 DATE TIMESTAMP For example, the new TIMESTAMP type can be created with 0 to 9 digits to store fractional seconds. The new DATE type is essentially a TIMESTAMP with zero digits of fractional seconds. The BINARY_FLOAT and BINARY_DOUBLE types are machine native floating point types that can store Inf -Inf and NaN. NUMBER, VARCHAR2 and NVARCHAR2 are identical to the Oracle types of the same name. Users of previous versions of TimesTen may want to continue using the same types that they have used in TimesTen 6.0; or they may wish to start using these new types exclusively; or they may want to use both old and new types. The concept of type mode has been introduced to allow both backwards compatibility for existing users of TimesTen, and to allow Oracle type semantics that new users of TimesTen expect. There is a new data store-creation DSN attribute "TypeMode" that takes two values. TypeMode=0 ---------- This is the default type mode. It is referred to as Oracle type mode. All new users of TimesTen 7.0 experience a data store with type semantics similar to what they expect from an Oracle database. In this mode, to access the old TimesTen types, where the same name exists in both type modes, prefix the type name with TT_ . For example, to create a table column with TimesTen-semantic types CHAR, TIMESTAMP, DATE and VARCHAR, use: CREATE TABLE T1 (a TT_CHAR(15), b TT_TIMESTAMP, c TT_DATE, d TT_VARCHAR(12000)); TypeMode=1 ---------- This is TimesTen type mode, or backwards compatibility mode. In this mode, a data store acts and behaves exactly the same way with respect to types that it did in TimesTen 6.0 and earlier versions. If you are migrating to TimesTen 7.0 from an earlier version you probably want to set this DSN attribute in your data store. To access the new types in TimesTen type mode, where the same name exists in both type modes, prefix the type name with ORA_ . For example, to create a table column with Oracle-semantic types CHAR, TIMESTAMP, DATE, NUMBER and VARCHAR2, use: CREATE TABLE T1 (a ORA_CHAR(15), b ORA_TIMESTAMP(3), c ORA_DATE, d NUMBER(10,3), e VARCHAR2(12000)); ** NOTE: TypeMode is a data store creation attribute. Once it is set for a given data store, it cannot be changed unless that data store is destroyed and re-created. More information can be found in the TimesTen documentation about the TypeMode DSN attribute and the relationship between type names and type modes. *** NCHAR, NVARCHAR, and NVARCHAR2 internal storage format: The storage format used internally for the NCHAR, NVARCHAR, and NVARCHAR2 types has been changed from native-endian to big-endian on all platforms. This change is transparent to users, except when binary interfaces such as SQL_C_BINARY bindings (ODBC) and ResultSet.getBytes() (JDBC) are used to access data of these types. ** Connection Attributes: The following connection attributes or attribute values are deprecated in the 7.0 release and will be removed in a future release. SMPOptLevel=0: In the 7.0 release, use of SMPOptLevel=0 will result in a warning. It will be removed in a future release. Logging=2: In the 7.0 release, use of Logging=2 (diskless logging) will result in a warning. Diskless logging will be removed in a future release. ExclAccess=1: In the 7.0 release, use of ExclAccess=1 will result in a warning. Exclusive connections will be removed in a future release. ThreadSafe=0: In the 7.0 release, use of ThreadSafe=0 will result in a warning. Non-threadsafe connections will be removed in a future release. *** TempSize The minimum value allowed for TempSize has been increased to 6 MB. *** LogFileSize The default value for the LogFileSize attribute has been increased to 64. ** Built-In Procedures: The following built-in procedure names have changed. All the corresponding old built-in names have been deprecated. Calls to them return a warning. They may be removed in a future release. 1. ttRepPolicy replaced by ttRepPolicySet 2. ttCachePolicy replaced by ttCachePolicySet 3. ttXlaDeleteBookmark replaced by ttXlaBookmarkDelete The ttDataStoreStatus built-in procedure now has two additional columns in its result set: connection_name and connid. The following built-in procedures were deprecated in 6.0 and have now been removed. ttcgmonitor (replaced by ttcachemonitor in TT6.0) ttcggensql (replaced by ttcachesqlget in TT6.0) ttoracleagentpolicy (replaced by ttcachepolicy in TT6.0) ttoracleagentstart (replaced by ttcachestart in TT6.0) ttoracleagentstop (replaced by ttcachestop in TT6.0) ** Logging of status information: Previously all logging of errors and warnings, as well as detailed information of use only to TimesTen support, went to the same "daemon log." That was either stored in a file, or sent to the event log (Windows) or syslog (UNIX/Linux). Now the logging is split into a "user log file" which contains only documented errors and warnings and a "support log file" which contains errors, warnings, and informational messages. The TimesTen daemon's "-f" option is now deprecated in favor of "-userlog", and several new options to control logging have been added. The TimesTen daemon now rotates the log files and removes old files. The webserver log is now written to the same files as other daemon log messages. Previously webserver log messages went to a separate file. ** TimesTen version numbers TimesTen version numbers now have five parts, for example, 7.0.0.0.0. The output of the ttVersion utility and the strings returned by SQLGetInfo when passed SQL_DBMS_VER or SQL_DRIVER_VER have changed accordingly.For a description of the usage for these five digits see the Release Notes for TimesTen 7.0.0.0.0 ** Utilities: *** ttIsql The cggensql command is removed in this release. It was deprecated in 6.0. The replacement command is cachesqlget. ** JMS/XLA The enabledTables attribute is no longer supported in a JMX/XLA configuration file. Applications should use built-in procedures for administering XLA bookmarks. There is a new built-in procedure for creating XLA bookmarks in this release -- ttxlabookmarkcreate(). InSee documentation for further details. ** XLA and JMS/XLA XLA subscriptions are no longer global, and are instead maintained on a per-bookmark basis. In past releases when one XLA bookmark subscribed to a database object, all bookmarks would receive updates for that object; similarly, if one bookmark unsubsubscribed to a particular object, all bookmarks would be unsubscribed. This has changed in TimesTen 7.0 so that each bookmark will only receive updates for database objects that each has subscribed to. Note that there is no way to get the old (global) behavior in TimesTen 7.0. Only customers porting an XLA application to TimesTen 7.0 which has multiple bookmarks should be affected by this change in behavior. ** Cache connect to Oracle The WITH LIMIT clause is deprecated from the CREATE CACHE GROUP and ALTER CACHE GROUP statements. The improved log truncation mechanism for autorefresh in release 6.0 tracks the logs more closely to prevent full refresh. It also removes old logs more eagerly. In TimesTen 7.0 the WITH LIMIT setting causes a warning. The setting is recorded but has no effect. The SELECT FOR UPDATE ON ORACLE feature is deprecated. You can use a passthrough SELECT FOR UPDATE statement to achieve the same effect. The DURATION clause in CREATE CACHE GROUP statement is deprecated. Please use the new 7.0 AGING feature. The RESTRICTED CACHE GROUP feature is deprecated. * Release 6.0.4 ** Connection Attributes: *** Connections New connections to the data store made after the number of existing connections has exceeded the value configured using the Connections attribute will now receive a warning. ** Utilities: *** ttIsql The maximum number of columns that can be selected in a query has been raised from 255 to 32767. * Release 6.0.3 ** JDBC: Connection.setAutocommit(boolean autoCommit) forces a Connection.commit(). See http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html#setAutoCommit(boolean). Connection.close() forces a Connection.rollback() if AUTOCOMMIT is off. ** Utilities: *** ttXactAdmin By default, all locks are now listed for each transaction. The "-ml" option can be used to limit the number of locks listed per transaction. The "-row" option has been removed. The "-tbl" option can be used instead to restrict the range of lock information returned. * Release 6.0.2 ** Connection Attributes: *** ConnectionName If the string supplied for the ConnectionName attribute is longer than 30 characters, a warning is generated and the value is truncated to the first 30 characters. Previously, an error would be generated and the connection would fail. In ttIsql, the error was avoided by internally substituting a default ConnectionName value. * Release 6.0.1 ** Justification: Many of the following changes were made due to the name change of "Oracle Connect" to "TimesTen Cache." ** SQL: *** Reserved Keywords When used as undelimited object names, the following keywords will now result in an error: CASE CONNECTION CROSS DEFAULT DESTROY INNER INTERVAL JOIN LEFT RIGHT WHEN ** ODBC: *** SQL_C_ADDR has been deprecated in this release. ** Connection Attributes: *** DurableCommits The default for the DurableCommits attribute has been changed from 1 (durable) to 0 (non-durable). *** SMPOptLevel The default determination for the SMPOptLevel attribute has changed. If left unspecified, the SMPOptLevel will be set to 0 on a uniprocessor machine, and 1 on a multiprocessor machine. This determination is performed at every first connection to the data store. Values used during a previous connection are not retained. If you want to override the default behavior, you must always explicitly specify an SMPOptLevel value. *** TempSize The minimum value allowed for TempSize has been increased to 4 MB. ** Built-In Procedures: The following built-in names have been changed. All the corresponding old built-ins have been deprecated. Calls to them return a warning, They will be removed in the next major release. 1. ttCGGenSQL replaced by ttCacheSqlGet 2. ttCGMonitor replaced by ttCacheMonitor 3. ttCacheSetMemoryThreshold replaced by ttCacheAgingMemoryThresholdSet 4. ttCacheSetAgingInterval replaced by ttCacheAgingIntervalSet 5. ttCachePropagateFlag replaced by ttCachePropagateFlagSet 6. ttOracleAgentStart replaced by ttCacheStart ttCacheStart does not have cache admin uid and pwd as parameters. To set the cache admin uid and pwd, users should call ttCacheUidPwdSet. 7. ttOracleAgentStop replaced by ttCacheStop 8. ttOracleAgentPolicy replaced by ttCachePolicy ttCachePolicy does not have cache uid and pwd as parameters. To set the cache admin uid and pwd, users should call ttCacheUidPwdSet. There are new built-in functions to set the cache admin uid and pwd and get the current cache uid: 1. ttCacheUidGet 2. ttCacheUidPwdSet ttCacheUidPwdSet should be used to set the cache admin uid and pwd instead of setting them during cache agent startup time. ** Utilities: *** ttAdmin The following ttAdmin command line option names have changed: -oraAgentUID replaced by -cacheUid -oraAgentPWD replaced by -cachePwd -oraAgentPolicy replaced by -cachePolicy -oraAgentStart replaced by -cacheStart -oraAgentStop replaced by -cacheStop Both -cachePolicy and -cacheStart cannot be used in conjunction with -cacheUid and -cachePwd. The cache admin uid and pwd should be set using -cacheUidPwdSet -cacheUid -cachePwd command line arguments. The following command line options have been added to ttAdmin: -cacheUidGet to get the current cache admin user id -cacheUidPwdSet to set the cache admin user id and password *** ttBackup When ttBackup is run while a checkpoint or another backup is in progress, it now waits for the checkpoint or backup to complete, rather than returning an error. *** ttBulkCp Default value for -xp option ("commit every") has changed from 0 (commit at end) to 1000 rows. (QA 18830) *** ttIsql Internal command "cgGenSql" replaced by "cacheSqlGet". *** ttStatus The output format of ttStatus has been enhanced to improve legibility and include additional information. This could affect any existing scripts that parse the ttStatus output. The new command line option -nopretty can be used to generate the old-style ttStatus output. *** ttXactAdmin The "-latchstats show" option now prints only the latches that have shown contention. "-latchstats showall" can be used to show all the latches that have been accessed, including those showing no contention. ** System and Replication Tables: *** MONITOR LOCK_DENIALS_COND column renamed to SYS19. SYS10 column renamed to REQUIRED_RECOVERY -- indicates whether the first connection to the data store required recovery to be run. The formula used to compute PERM_IN_USE_SIZE and TEMP_IN_USE_SIZE (and also PERM_IN_USE_HIGH_WATER and TEMP_IN_USE_HIGH_WATER) has been changed to reflect changes made in internal memory management. The following SYSn columns were assigned meanings: SYS1 -- cursor opens SYS2 -- cursor closes SYS12 -- current SMPOptLevel Meanings assigned to SYSn columns are not guaranteed or supported, may change at any time and are not documented. ** Utility API: *** ttBackup() When ttBackup() is called while a checkpoint or another backup is in progress, it now waits for the checkpoint or backup to complete, rather than returning an error. *** ttRepDuplicate() and ttRepDuplicateAcc() The existing ttRepDuplicate() and ttRepDuplicateAcc() utility API functions have been deprecated in favor of the new ttRepDuplicateEx(). ** Cache Connect (formerly Oracle Connect): *** General changes: (a) TimesTen passes SQL statements to Oracle to execute when the syntax is not supported by TimesTen and the passthrough connection attribute is set to 1 or 2. Any new SQL features introduced in this release may cause a previous passthrough SQL statement to be executed in TimesTen now. (b) The error(s) caused by an offending operation may change from release to release. Any application that tries to catch specific error codes may experience behavior changes. *** Default Values: (a) When creating a READONLY cache group or a cache group with autorefresh, the default autorefresh state is PAUSED. *** SQL statements that no longer work: (a) LOAD/REFRESH cache group statements without either the COMMIT EVERY N ROWS statement or the WITH ID statement no longer work. To load a cache group with logging off, you should now do the following: step 1: Connect to the TimesTen DSN with logging=0; step 2: Issue a LOAD CACHE GROUP statement with COMMIT EVERY 0 ROWS" clause. The load statement cannot contain a WHERE clause. *** SQL operations that result in different rows in the table: (a) CREATE READONLY cache groups or SYNCHRONOUS WRITETHROUGH cache groups no longer cause an implicit load on the cache groups. We suggest the following steps to initialize the content of the cache group tables, when logging is enabled. step 1: Start the cache agent. step 2: Create the cache group. if the cache group has autorefresh, use the default autorefresh state, which is PAUSED. step 3: Commit the transaction. step 4: (optional) You may want to create the replication schema so the cache group load will be replicated to other TimesTen data stores. Alternatively, one can create the replication scheme after the cache group load, in which case the load operation will not be replicated. step 5: Commit the transaction. step 6: (optional) Start the replication agent. step 7: Issue the LOAD CACHE GROUP ... COMMIT EVERY N ROWS SQL statement. A reasonable range for N is between 20 and 100. If the cache group has autorefresh in the PAUSED state, this automatically changes the autorefresh state to ON. step 8: Commit the transaction. Alternatively, the no-logging load has the following steps: step 1: Make sure the TimesTen DSN has no outstanding connections. Note: Both replication and cache agents should be shutdown. Otherwise there will be outstanding connections. step 2: Connect to the DSN, using the following connection attribute settings: logging=0;durablecommits=0;locklevel=1; step 3: Issue the SQL statement: LOAD CACHE GROUP COMMIT EVERY 0 ROWS; commit; step 4: Perform a checkpoint. step 5: Disconnect. step 6: Connect with regular logging and start the replication and cache agents. ** SNMP: *** TimesTen MIB In the TimesTen-MIB.txt file, the following prefix and names have been globally replaced: 1. "Ora" with "Cache" 2. "Oracle Cache Agent" with "Cache Agent" 3. "TimesTen Oracle Connect" with "TimesTen Cache" ** TTClasses: 1. TTClasses is now part of the core TimesTen product. In the past, TTClasses was shipped as a separate product. As a result of this change, the TTClasses "configure" script has been removed; instead, configuration of TTClasses Makefiles occurs during TimesTen installation. In addition, the TTClasses documentation can be found in the doc directory and the TTClasses directory structure has been co-mingled with the TimesTen directory structure, as follows: a. TTClasses main directory is TIMESTEN_INSTALL/ttclasses b. TTClasses include directory is TIMESTEN_INSTALL/include/ttclasses c. TTClasses demo directory is TIMESTEN_INSTALL/demo/ttclasses To build TTClasses, go to the TTClasses main directory and type "make" ("nmake" on Windows). Note: Since TTClasses is now a part of the core product, the TTClasses that ships with a particular TimesTen release is only compatible with that release. In the past, a particular TTClasses release was compatible with multiple TimesTen releases. 2. For users of TTClasses TTXla, type-mismatch misuse of TTXlaRowViewer::Get() methods (e.g., using TTXlaRowViewer::Get(i, TIMESTAMP_STRUCT*) on an integer table column) now causes an exception to be thrown. In earlier versions of TTClasses, no exception was thrown in these cases, instead a message was written to the TTClasses log file. Local variables: mode: outline paragraph-separate: "[ ]*$" end: