Wait is over Now !!
Please download the Complete ebook for SAP Sybase ASE Q&A Bank Version 1.0 as below:
Wait is over Now !!
Please download the Complete ebook for SAP Sybase ASE Q&A Bank Version 1.0 as below:
By Mich Talebzedah, SearchOpenSource.com
Sybase products are generally perceived within the database administrator (DBA) community as very reliable and easy to maintain, particularly compared to Oracle. Any move from Sybase to other DBMS (database management system) have got to be justified in terms of the current level of dissatisfaction with Sybase and the level of desire to use other. I cannot recall anywhere where this is valid.
Few month back, we posted for Sybase DBA Interview Questions & Answers Series and we received the lot of your valuable suggestions, regarding that.
Today, I would like to thanks for your thoughts , we tried to consider same as per our feasibility
Wait is over Now !! Please download the First Q&A Bank (Limited Edition) with below link :
We are still improving and adding more questions and will be releasing full version by the Month End.
If you are still seeing any correction, improvement, you are most welcome.
Again Thanks, and Happy Learning Sybase !
PS : You need to login to download the Q&A Bank.
Source : Sybase Resources on www & sybase.com
In 2009 , I posted ASE 15 New Features -1 , Now recently ASE has released ASE15.7 ESD-2 and these are new features in recent versions;
Happy Reading and Enjoy !
ASE 15.7 ESD#2 New Features
• Automatic compressed Share Memory dump
• In-Row Large Object Compression
• create database Asynchronously
• Shared Query Plans
• User-Defined Optimization Goal
• Expanded Maximum Database Size
• alter table drop column without datacopy
• Enhancements to dump and load : Dump Configuration, History.
• Hash-Based Update Statistics
• Concurrent dump database and dump transaction Commands
• Fast-Logged Bulk Copy
• Enhancements to show_cached_plan_in_xml
• Merging, Splitting & Moving Partitions
• Non blocking Reorg
• Deferred Table Creation
• Granular Permissions & Predicate Priviliages
ASE 15.7 New Features
• Application Functionality Configuration Group
• ASE Thread-Based Kernel: The ASE kernel us now thread-based instead or process-based
• Data Compression : Use less storage space for the same amount of data, reduce cache memory consumption and improve performance because of lower I/O demands
• New Security Features: End-to-end CIS Kerberos authentication, dual control of encryption keys and unattended startup, secure logins, roles and password management and login profiles
• Abstract Plans in Cached Statements: Abstract plan information can be saved in statement cache
• Shrink Log Space: Allows you to shrink the log space and free storage without re-creating the database using the alter database command to remove unwanted portions of a database log
• Display Currently Set Switches: Allows visibility of all traceflags at the server and session level
• Changes for Large Objects: Includes storing in-row LOB columns for small text, image and unitext datatypes, storing declared SQL statements containing LOBs, indirectly referencing a LOB in T-SQL statements, and allows checking for null values of large objects
• Showing Cached Plans in XML: Allows showplan output in XML for a statement in cache
• Padding a Character Field Using str: Fields can be padded with a specified character or numeric
• Changes to select for update: Allows select for update command to exclusively lock rows for subsequent updates within the same transactio and for updatable cursors
• Creation of non-materialized, non-NULL columns
• Sharing Inline Defaults: Allows sharing inline defaults between different tables in the same db
• Monitoring data is retained to improve query performance
• Dynamic parameters can be analyzed before running a query to avoid inefficient query plans
• Monitor Lock Timeouts
• Enable and disable truncation of trailing zeros from varbinary and binary null data
• Full Recoverable DDL: Use dump transaction to fully recover the operations that earlier versions of Adaptive Server minimally logged
• Transfer Rows from Source to Target Table Using merge.
• View Statistics and Histograms with sp_showoptstats: Allow you to extract and display, in an XML document, statistics and histograms for various types of data objects from system tables
• Changes to Cursors: Changes to how cursors lock, manage trnasactions and are declared
• Nested select Statement Enhancements: Expands the abilities of the asterisk (*)
• Some system procedures can run in sessions that use chained transaction mode
• Expanded Variable-Length Rows: Redefines data-only locked (DOL) columns to use a row offset of upto 32767 bytes. Requires a logical page size of 16K to create wide, variable-length DOL rows.
• Like Pattern Matching: Treat square brackets individually in the like pattern-matching algorithm
• Quoted Identifiers: Use quoted identifiers for tables, views, column names, index names and system procedure parameters
• Allow Unicode Noncharacters: Enable permissive unicode configuration parameter, which is a member of enable functionality group, allows you to ignore Unicode noncharacters
• Reduce Query Processing Latency: Enables multiple client connections to reuse or share dynamic SQL lightweight procedures (LWPs)
• The sybdiag Utility: A new Java-based tool that collects comprehensive ASE configuraiton and environment data for use by Sybase Technical Support
• The optimizer Diagnostic Utility: Adds the sp_opt_querystats system procedure, which allows you to analyze the query plan generated by the optimizer and the factors that influenced its choice of a query plan
ASE 15.5 New Features
• In-memory databases provide improved performance by operating entirely in-memory and not reading/writing transactions to disk.
• Relaxed-durability for disk-resident databases delivers enhanced performance by eliminating committed transactions.
• “dump database” and “load database” functionality is provided for both in-memory and relaxed-durability databases.
• Faster compression for backups is provided by two new compression options (level 100 and 101).
• Backup Server support is now available for IBM’s Tivoli Storage Manager.
• Deferred name resolution allows the creation of stored procedures before the referenced objects are created in the database.
• FIPS 140-2 encryption is now provided for login passwords that are transmitted, stored in memory or stored on disk.
• Incremental Data Transfer allows exporting specific rows, based on either updates since the last transfer or by selected rows for an output file, and does so without blocking ongoing reads and updates.
• The new bigdatetime and bigtime datatypes provide microsecond level precision.
• You can now create and manage user-created tempdb groups, in addition to the default tempdb group.
• The new monTableTransfer table provides historical transfer information for tables.
• The new system table, spt_TableTransfer, stores results from table transfers.
• The sysdevices table has been modified to list the in-memory storage cache under the “name” and “phyname” columns.
• Auditing options have been added to support in-memory and relaxed-durability databases, incremental data transfer, and deferred name resolution.
ASE15 New Features – I : http://sybaseblog.com/2009/12/17/sybase-ase15-new_features/
Checkstorage will detect allocation errors, it is a reasonable substitute for dbcc checkalloc (checkstorage will report a fair number of issues that checkalloc will not, many of them trivial things, and checkalloc may be able to detect a few odd conditions
checkstorage does not.
What checkstorage won’t catch are issues with index tree (keys out of order, index entries that point to missing rows, rows
that are not indexed). So checkstorage is not a good substitute for checkdb.
Source :www & sybooks.
Few days back I have faced performance issue in one of our prod data server. I would like to share here.
User was running batch for pushing 90000 rows in a database and batch was not moved from last 1.5 hrs.
On login in the server I found response of the server was not good, it was taking more time to execute a simple query as usual. In first glance, it was looking like user job is hogging the resources, as user job spid was in syslogshold and not moved from long time.
We do some analysis and finally found the cpu usage for server was 100%. ( I used sp_monitor). I concluded that this high cpu usage is slowing down the server performance.
The next task was finding the query which was taking more cpu time. As server was on 15 version, I ran the below sql querry for mon tables for getting the high cpu usage.
select top 10 s.SPID, s.CpuTime, t.LineNumber, t.SQLText from master..monProcessStatement s, master..monProcessSQLText t where s.SPID = t.SPID order by s.CpuTime DESC
We asked application team to check the reported spid and if possible, please abort the tran. There was select queries which were taking maximum cpu . As they requested us to kill, we aborted/killed from data server.
After few seconds, data server cpu started fluctuating from 50 to 100% and finally it was below 50%.
Application batch of 10K inserts moved very quickly and finally issue resolved.
Basic Difference :
Pre-15.7 kernel (except Windows)
Each engine is a separate process
Retained in 15.7 for risk mitigation
Default kernel for 15.7
Each engine is a thread of a single process
Additional threads for handling I/O, etc.
ASE on Windows has always been thread based
Isolation Level ??
Data concurrency: means that many users can access data at the same time.
Data consistency: means that each user sees a consistent view of the data, including visible changes made by the user’s own transactions and transactions of other users.
Isolation : is a property that defines how/when the changes made by one operation become visible to other concurrent operations. Isolation is one of the ACID property.
Lower isolation levels increase transaction concurrency at the risk of allowing transactions to observe a fuzzy or incorrect database state. These incorrect state you need to manage at application design.
4 Isolation Levels:
The ANSI/ISO SQL-92 specifications define four isolation levels:
(1) READ UNCOMMITTED.
(2) READ COMMITTED.
(3) REPEATABLE READ.
Lower Isolation level —> Higher concurrency, Data consistancy low, Reducing the locking overhead.
Higher Isolation Level —> Lower Concurrency, High Data Consistancy, Possible More Deadlock in multi user enviorment.
Three preventable phenomena
P1 (Dirty Read): Transaction T1 modifies a data item. Another transaction T2 then reads that data item before T1 performs a COMMIT or ROLLBACK. If T1 then performs a ROLLBACK, T2 has read a data item that was never committed and so never really existed.
P2 (Non-repeatable or Fuzzy Read): Transaction T1 reads a data item. Another transaction T2 then modifies or
deletes that data item and commits. If T1 then attempts to reread the data item, it receives a modified value or discovers
that the data item has been deleted.
P3 (Phantom): Transaction T1 reads a set of data items satisfying some
then creates data items that satisfy T1’s
Isolation Level Dirty Read Nonrepeatable Read Phantom Read
Read uncommitted Possible Possible Possible
Read committed Not possible Possible Possible
Repeatable read Not possible Not possible Possible
Serializable Not possible Not possible Not possible
When we execute dbcc sqltext without putting on traceflag 3604 and 3605
where the out put of sqltext goes? In errorlog?
No,For errorlog we have traceflag 3605.
Lets explore the RUN server file again:
-sPROD_ASE_DS1 > /dev/null
-e : denoting the errorlog file where all error message and informational messages resides.
AS we know, When we are running any binary file, the output of that binary displays on the screen.
What about the output of $SYBASE/$YSBASE_ASE/bin/dataserver binary,
generally we redirect it to null device (/dev/null) as above.
Now,I am redirecting the output to file like below as in /tmp/sybaselog.out file.
-sPROD_ASE_DS1 > /tmp/sybaselog.out
Run the dbcc sqltext command , the result would be display in dataserver output file, without any traceflag.
It means when we require any output on user screen and errorlog, need to enable the traceflag 3604 and 3605 respectively,
otherwise it will be display in sybase dataserver binary , out put file ,if we are redirecting it to file.
sybase@localhost ~]$ isql -Usa -SPROD_ASE_DS1 Password: 1> select @@spid 2> go ------ 14 (1 row affected) 1> select name from sysdatabases 2> go name ------------------------------------------------------------ master model sybsecurity sybsystemdb sybsystemprocs tempdb (6 rows affected) 1> dbcc sqltext(14) 2> go DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role. 1> dbcc sqltext(14) 2> go DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role. 1> [sybase@localhost ~]$ tail -f /tmp/sybaselog.out 00:00:00000:00001:2011/09/28 08:51:15.11 server ASE's default unicode sort order is 'binary'. 00:00:00000:00001:2011/09/28 08:51:15.11 server ASE's default sort order is: 00:00:00000:00001:2011/09/28 08:51:15.11 server 'bin_iso_1' (ID = 50) 00:00:00000:00001:2011/09/28 08:51:15.11 server on top of default character set: 00:00:00000:00001:2011/09/28 08:51:15.11 server 'iso_1' (ID = 1). 00:00:00000:00001:2011/09/28 08:51:15.11 server Master device size: 500 megabytes, or 256000 virtual pages. (A virtual page is 2048 bytes.) 00:00:00000:00001:2011/09/28 08:51:15.11 kernel Warning: Cannot set console to nonblocking mode, switching to blocking mode. SQL Text: SELECT fid=right(space(80)+isnull(convert(varchar(80),fid),'NULL'),3), spid=right(space(80)+isnull(convert(varchar(80),spid),'NULL'),4), status=SUBSTRING(convert(varchar(80),status),1,10), loginame=SUBSTRING(convert(varchar(80),loginame),1,8), origname=SUBSTRING(convert(varchar(80),origname),1,8), hostname=SUBSTRING(convert(varchar(80),hostname),1,21), blk_spid=right(space(80)+isnull(convert(varchar(80),blk_spid),'NULL'),8), dbname=SUBSTRING(convert(varchar(80),dbname),1,6), tempdbname=SUBSTRIN SQL Text: select @@spid SQL Text: select name from sysdatabases
Please let me knwo if you have any more thoughts!!
Most Awaited Database ASE 15.7 has been released yday in Vegas Techwave:
Extremely good news for Sybase Users!!!!!!!!
ASE 15.7 key features include:
Management of Large Datasets
Ease of Application Development