View this document as PDF
Contents
1. parallel queries set statistics io on Use set statistics io on to obtain a representation of the parallel access nodes See Chapter 6 Ensuring Stability and Performance in the Migration Guide for command syntax and usage details New Features Bulletin New and changed commands functions and stored procedures Example set statistics io on go select a cl max a c2 from r10 a r10 b where a c2 b c2 group by a cl go Table Worktable2 scan count 1 logical reads regular 5 apf 0 total physical reads regular 0 apf 0 total 0 apf I0s used 0 Table r10 scan count 1 logical reads regular 10 apf 0 total 10 reads regular 0 apf 0 total 0 apf IOs used 0 Table r10 scan count 1 logical reads regular 10 apf 0 total 10 reads regular 0 apf 0 total 0 apf IOs used 0 Total writes for this command 0 set statistics plancost Description Syntax Usage Example Adaptive Server Enterprise 15 0 2 ESD 4 and later supports set statistics plancost on for parallel queries set statistics plancost on Use set statistics plancost on to obtain a representation of the parallel access nodes in the lava operator tree See Chapter 6 Ensuring Stability and Performance in the Migration Guide for command syntax and usage details Note For an easy to read output format use isql w 132 The w 132 flag indicates a column width of 132 For more information on isq options please see Cha2. the system clock value while get_internal_date returns the value of the server s internal clock At startup Adaptive Server initializes its internal clock with the current value of the operating system clock and increments it based on regular updates from the operating system Adaptive Server periodically synchronizes the internal clock with the operating system clock The two typically differ by a maximum of one minute Adaptive Server uses the internal clock value to maintain the date of object creation timestamps for transaction log records and so on To retrieve such values use get_internal_date rather than getdate Permissions Any user can execute this function See also getdate 8 New Features Bulletin UsingSYBASE_JRE_RTDS Using SYBASE_JRE_RTDS If you have not defined SYBASE_JRE_RTDS or if it does not exist you cannot enable Real Time Data Services RTDS Use SYBASE_JRE to enable RTDS auditinit utility for Windows Adaptive Server Enterprise 15 0 2 ESD 4 includes the auditinit utility for Windows Although we do not describe the auditinit utility in the Windows Configuration Guide it behaves similar to the UNIX version of the auditinit utility See the Configuration Guide for Unix for information on syntax and usage New audit event for LDAP state changes Adaptive Server 15 0 2 ESD 4 includes the new LDPUA State Change audit event with a value of 123 The LDAPUA state change audit record includes thi
3. New Features Bulletin Adaptive Server Enterprise 15 0 2 ESD 4 Document ID DC00803 01 1502 01 Last revised April 9 2008 Topic Page Downgrading from version15 0 2 ESD 4 Changed monitoring tables New and changed commands functions and stored procedures set statistics io set statistics plancost update statistics sp_errorlog get_internal_date Using SYBASE_JRE_RTDS auditinit utility for Windows WO OO OO CLD NM BW NI NI NI New audit event for LDAP state changes a New value for sp_modifylogin options Downgrading from version15 0 2 ESD 4 Before downgrading an Adaptive Server version 15 0 2 ESD 4 set either of the following options e Disable the Replication Agent Set the optimization goal to allrows_mix or allrows_dss If you downgrade the server with both options set incorrectly you will be unable to start your downgraded server Workaround Set the parameters correctly in your configuration file and restart the server Copyright 1987 2008 by Sybase Inc All rights reserved Sybase trademarks can be viewed at the Sybase trademarks page at http www sybase com detail id 1011207 Sybase and the marks listed are trademarks of Sybase Inc indicates registration in the United States of America Java and all Java based marks are trademarks or registered trademarks of Sun Microsystems Inc in the U S and other countries Unicode and the Unicode L
4. ivity value to such SARGs update statistics table name column_name using out_of range on off default e on Enables out of range histogram adjustment for column_name e off Disables out of range histogram adjustment for column_name e default Affects the out of range histogram adjustment depending on the value of trace flag 15355 e Disables out of range histogram adjustment when Traceflag 15355 is on e Enables out of range histogram adjustment when Traceflag 15355 is off Note With Adaptive Server Enterprise version 15 0 2 ESD 4 and later histogram adjustment for out of range SARGS is enabled server wide by default You can turn it off using Traceflag 15355 Example 1 When an out_of_range SARG is detected for a column the optimizer adjusts the column s histogram and assigns an appropriate selectivity value to the out of range clause update statistics TOFO FUOP ORD OrdDt using out_of range on Example 2 In this example if trace flag 15355 is turned on the column s histogram is not adjusted for out of range SARGs update statistics TOFO FUOP ORD OrdDt using Adaptive Server Enterprise 15 0 2 ESD 4 5 New and changed commands functions and stored procedures Usage Permissions See also sp_errorlog Description Syntax Parameters Example out_of range default Note optdiag has been enhanced to display the out_of_range histogram adjustment value and to read this val
5. ogo are registered trademarks of Unicode Inc All other company and product names mentioned may be trademarks of the respec tive companies with which they are associated Changed monitoring tables See Chapter 5 Controlling Optimization in Performance and Tuning Series Query Processing and Abstract Plans for details on optimization goals Changed monitoring tables Adaptive Server 15 0 2 ESD 4 includes the following changes to the Monitoring and Diagnostic Access MDA tables MDA table name New column name Datatype Description monProcessProcedures StmtNumber int The currently executing statement monProcessNetlO networkEngineNumber smallint The network engine for a process monLocks DBName varchar 30 The database name for a DBID monCachedStatement DBName varchar 30 The database name for a DBID monProcessStatement DBName varchar 30 The database name for a DBID monSysPlanText DBName varchar 30 The database name for a DBID monSysStatement DBName varchar 30 The database name for a DBID New and changed commands functions and stored procedures set statistics io Description Syntax Usage This section describes new and changed stored procedures and built in functions for Adaptive Server 15 0 2 ESD 4 For complete information about changed procedures and functions see the Adaptive Server Reference Manual Adaptive Server Enterprise 15 0 2 ESD 4 and later supports set statistics io for
6. pter 2 Using the ISQL Utility in the Utility Guide for the Adaptive Server Enterprise set statistics plancost on go select a cl max a c2 from r10 a r10 b where a c2 b c2 group by a cl go Adaptive Server Enterprise 15 0 2 ESD 4 3 New and changed commands functions and stored procedures Emit VA 6 r 100 er 100 cpu 0 HashVectAgg Max VA 5 r 100 er 100 1 5 el 5 p 0 ep 0 bufct 16 Exchange P 2 C 1 VA 4 r 100 er 100 1 0 el 1 p 0 ep 0 ExchangeEmit VA 3 r 100 er 100 1 0 el 1 p 0 ep 0 HashJoin Inner Join VA 2 r 100 er 100 1 10 el 5 p 0 ep 0 bufct 16 TableScan TableScan r10 a r10 b VA 0 VA 1 r 100 er 100 1 100 e1 100 p 0 ep 100 r 100 er 100 1 100 e1 100 p 0 ep 100 New Features Bulletin New and changed commands functions and stored procedures update statistics Description Syntax Parameters Example The update statistics command has been extended to specify an out of range histogram adjustment at the column level Column statistics for rapidly growing tables may become out of date when an update statistics command completes This results in out of range SARGs search clauses that select a greater range of values than described by the column s histogram Out of range SARGS have a selectivity of 0 The out of range histogram adjustment feature adjusts a column s histogram and assigns an appropriate select
7. rver error log to use a new location without changing the location of the Job Scheduler log use New Features Bulletin New and changed commands functions and stored procedures Usage Auditing Permissions sp_errorlog change log SSYBASE SSYBASE ASE install new log jslog false Adaptive Server error log location is changed to SYBASE SYBASE_ASE install new log However the location of the Job Scheduler Agent log is not changed Example 2 This example changes the errorlog location to SYBASE SYBASE_ASE install new log If the Job Scheduler Agent is running the agent log location is also changed to SYBASE SYBASE_ASE install new log If the Job Scheduler Agent is not running Adaptive Server does not change the agent log location You see a message that the agent log location is unchanged sp_errorlog change log SSYBASE SSYBASE ASE install new log jslog true Example 3 This example changes the Adaptive Server errorlog to SYBASE SYBASE_ASE install new log If the Job Scheduler Agent is running the agent log is also changed to SYBASE SYBASE_ASE install new log If the Job Scheduler Agent is not running Adaptive Server does not change the path of the Job Scheduler Agent log You do not see a message that the agent log location is unchanged sp_errorlog change log SSYBASE SSYBASE ASE install new log sp_errorlog returns 0 if the switch to the new location is
8. s information in the extrainfo column e Keyword or Option e Primary URL state for primary URL state change e Secondary URL state for secondary URL state change e Previous state e NOT SET e READY e ACTIVE e FAILED e SUSPENDED e RESET e Current state e NOT SET Adaptive Server Enterprise 15 0 2 ESD 4 9 New value for sp_modifylogin options e READY e ACTIVE e FAILED e SUSPENDED e RESET e Other information e Manual If the state change is caused by a manual command e Automatic If the state change is automatic Example Primary URL state READY NOT SET Manual New value for sp_modifylogin options 10 The sp_modifylogin options passwd expiration min passwd length and max failed_logins supports the clear value which clears the previous setting of the attribute for the specific user Example Set the password expiration value to 30 sp modifylogin John passwd expiration 30 go User John s password will expire in 30 days even though the system default for passwd expiration is 90 days To clear the passwd expiration value for user John sp _modifylogin John passwd expiration clear go Adaptive Server displays a message that the login specific attribute has been removed The attribute is now set to the system default Note If you use the clear option for a user attribute that does not have a previously set value Adaptive Server displays a message that there i
9. s no login specific attribute set However the user continues to use the system default for that attribute New Features Bulletin
10. successful A non zero return value implies an error Note To pick up the new location of the errorlog when the server is restarted update the e argument in the runserver file A new auditing option errorlog audits changes to the error log administration When the errorlog audit option is enabled any change to the errorlog generates an audit record with event 127 To view the event from sybsecurity issue select from sybsecurity sysaudits01 where event 127 To enable the errorlog audit option enter sp audit errorlog all all on sp_errorlog requires sa permission Adaptive Server Enterprise 15 0 2 ESD 4 7 New and changed commands functions and stored procedures See also See Chapter 8 Logging Error Messages and Events in the Configuration Guide for information on the runserver file get_internal_date Description Returns the current date and time from the internal clock maintained by the Adaptive Server Syntax get_internal_ date Example Example 1 The system clock is synchronized with the Adaptive Server internal clock Current system date January 20 2007 5 04AM select get internal date Jan 20 2007 5 04AM Example 2 The system clock is not synchronized with the Adaptive Server internal clock Current system date August 27 2007 1 08AM select get_internal date Aug 27 2007 1 07AM Usage get_internal_date may return a different value than getdate getdate returns
11. ue from optdiag output files The default value is used for reading optdiag files that do not have out_of_range histogram adjustment information for example from a version earlier than 15 0 2 ESD 4 Error 16015 is raised if you attempt to use out_of_range options for update statistics alongside other options such as consumers or sampling Error 16016 is raised if you specify out_of_range options for a column that currently has no column level statistics update statistics permission defaults to the table owner and is not transferable The command can also be executed by the Database Owner who can impersonate the table owner by running the setuser command For optdiag syntax and usage see Chapter 6 Statistics Tables and Displaying Statistics with optdiag in Performance and Tuning Series Monitoring and Analyzing Dynamically changes the path of the error log sp_errorlog change log new path jslog true jslog false sp_errorlog help change log new_path new path of the error log Maximum length of new_path is 255 characters jslog true the default option If the Job Scheduler is running change log attempts to change the Job Scheduler Agent log to the directory where the new Adaptive Server error log will reside Both logs will indicate error messages if any jslog false do not change the location of the Job Scheduler Agent log Example 1 To change the Adaptive Se
Download Pdf Manuals
Related Search