Please see the blog post by Rob sir, which gives us strength to work on Sybase.
http://blogs.sybase.com/database/2011/07/saps-preferred-erp-database-sybase-ase/
Friday 29 July 2011
Sybase will be shining soon...
http://blogs.sybase.com/database/2011/07/saps-preferred-erp-database-sybase-ase/
Wednesday 20 July 2011
How to clear data from cache memory
How to clear data from cache memory
data cache
as of 15.0.3 - dbcc cachedataremove(dbid | dbname, objid | objname, partitionid | partitionname, indid | indexname)
you need sa_role for that as well
pre-15.0.2 ESD6 - try sp_unbindcache_all 'default data cache'
for example to clear "default data cache". For named data caches, you should do this, followed by a sp_bindcache again for each object bound to a named cache
statement cache
set statement_cache off -- session level
<your sql statements here>
or
sp_configure 'statement cache size',0 -- server level
procedure cache
In 12.5.4 ESD5 and 15.0.2 : dbcc proc_cache (free_unused)
pre 12.5.4 ESD5: dbcc proc_cacherm(type, dbname, objname)
where: type is V,P,T,R,D,C,F, or S (must be uppercase) corresponds to View, Proc, Trigger, Rule, Default, Cursor, SQLJ Function, SQL function.
as of 15.0.3 - dbcc cachedataremove(dbid | dbname, objid | objname, partitionid | partitionname, indid | indexname)
you need sa_role for that as well
pre-15.0.2 ESD6 - try sp_unbindcache_all 'default data cache'
for example to clear "default data cache". For named data caches, you should do this, followed by a sp_bindcache again for each object bound to a named cache
statement cache
set statement_cache off -- session level
<your sql statements here>
or
sp_configure 'statement cache size',0 -- server level
procedure cache
In 12.5.4 ESD5 and 15.0.2 : dbcc proc_cache (free_unused)
pre 12.5.4 ESD5: dbcc proc_cacherm(type, dbname, objname)
where: type is V,P,T,R,D,C,F, or S (must be uppercase) corresponds to View, Proc, Trigger, Rule, Default, Cursor, SQLJ Function, SQL function.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Most useful Sybase URLs to understand ASE 15 Performance issues
http://www.sybaseteam.com/useful-dbcc-commands-sybase-technote-database-management-sybase-t-206.html
Getting the process ID for the oldest open transaction.
Getting the process ID for the oldest open transaction
Use the following query to find the spid of the oldest open transaction in a transaction log that has reached its last-chance threshold:
use master
go
select dbid, spid from syslogshold
where dbid = db_id("name_of_database")
For example, to find the oldest running transaction on the pubs2 database:
select dbid, spid from syslogshold
where dbid = db_id ("pubs2")
dbid spid
------ ------
7 1
Monday 18 July 2011
T-SQL Query to get all the tables and lock scheme info.
The following Query gives list of all the User Tables and locking scheme of the Table.
select "Table"=left(name,32), "lock_scheme"= case
when (sysstat2 & 57344) < 8193 then 'APL'
when (sysstat2 & 57344) = 16384 then 'DPL'
when (sysstat2 & 57344) = 32768 then 'DRL'
end
from sysobjects
where type='U'
order by sysstat2
select "Table"=left(name,32), "lock_scheme"= case
when (sysstat2 & 57344) < 8193 then 'APL'
when (sysstat2 & 57344) = 16384 then 'DPL'
when (sysstat2 & 57344) = 32768 then 'DRL'
end
from sysobjects
where type='U'
order by sysstat2
How update operations are performed in ASE?
How update operations are performed in ASE?
Adaptive Server handles updates in different ways, depending on the changes being made to the data and the indexes used to locate the rows. The two major types of updates are deferred updates and direct updates. Adaptive Server performs direct updates whenever possible.Direct updates
Adaptive Server performs direct updates in a single pass:· It locates the affected index and data rows.
· It writes the log records for the changes to the transaction log.
· It makes the changes to the data pages and any affected index pages.
There are three techniques for performing direct updates: · In-place updates
· Cheap direct updates
· Expensive direct updates
Direct updates require less overhead than deferred updates and are generally faster, as they limit the number of log scans, reduce logging, save traversal of index B-trees (reducing lock contention), and save I/O because Adaptive Server does not have to refetch pages to perform modifications based on log records.In-place updates
Adaptive Server performs in-place updates whenever possible.When Adaptive Server performs an in-place update, subsequent rows on the page are not moved; the row IDs remain the same and the pointers in the row offset table are not changed.
For an in-place update, the following requirements must be met:
· The row being changed cannot change its length.
· The column being updated cannot be the key, or part of the key, of a clustered index on an allpages-locked table. Because the rows in a clustered index on an allpages-locked table are stored in key order, a change to the key almost always means that the row location is changed.
· One or more indexes must be unique or must allow duplicates.
· Updates and deletes that involve joins can be performed in direct, deferred_varcol, or deferred_index mode when the table being updated is the outermost table in the join order, or when it is preceded in the join order by tables where only a single row qualifies.
· The affected columns are not used for referential integrity.
· There cannot be a trigger on the column.
· The table cannot be replicated (via Replication Server).
An in-place update is the fastest type of update because it makes a single change to the data page. It changes all affected index entries by deleting the old index rows and inserting the new index row. In-place updates affect only indexes whose keys are changed by the update, since the page and row locations are not changed.Cheap direct updates
If Adaptive Server cannot perform an update in place, it tries to perform a cheap direct update—changing the row and rewriting it at the same offset on the page. Subsequent rows on the page are moved up or down so that the data remains contiguous on the page, but the row IDs remain the same. The pointers in the row offset table change to reflect the new locations.A cheap direct update, must meet these requirements:
· The length of the data in the row is changed, but the row still fits on the same data page, or the row length is not changed, but there is a trigger on the table or the table is replicated.
· The column being updated cannot be the key, or part of the key, of a clustered index. Because Adaptive Server stores the rows of a clustered index in key order, a change to the key almost always means that the row location is changed.
· One or more indexes must be unique or must allow duplicates.
· The affected columns are not used for referential integrity.
Cheap direct updates are almost as fast as in-place updates. They require the same amount of I/O, but slightly more processing. Two changes are made to the data page (the row and the offset table). Any changed index keys are updated by deleting old values and inserting new values. Cheap direct updates affect only indexes whose keys are changed by the update, since the page and row ID are not changed.Expensive direct updates
If the data does not fit on the same page, Adaptive Server performs an expensive direct update, if possible. An expensive direct update deletes the data row, including all index entries, and then inserts the modified row and index entries.Adaptive Server uses a table scan or an index to find the row in its original location and then deletes the row. If the table has a clustered index, Adaptive Server uses the index to determine the new location for the row; otherwise, Adaptive Server inserts the new row at the end of the heap.
An expensive direct update must meet these requirements:
· The length of a data row is changed so that the row no longer fits on the same data page, and the row is moved to a different page, or the update affects key columns for the clustered index.
· The index used to find the row is not changed by the update.
· The affected columns are not used for referential integrity.
An expensive direct update is the slowest type of direct update. The delete is performed on one data page, and the insert is performed on a different data page. All index entries must be updated, since the row location is changed. Deferred updates
Adaptive Server uses deferred updates when direct update conditions are not met. A deferred update is the slowest type of update.In a deferred update, Adaptive Server:
· Locates the affected data rows, writing the log records for deferred delete and insert of the data pages as rows are located.
· Reads the log records for the transaction and performs the deletes on the data pages and any affected index rows.
· Reads the log records a second time, and performs all inserts on the data pages, and inserts any affected index rows.
When deferred updates are required
Deferred updates are always required for:· Updates that use self-joins
· Updates to columns used for self-referential integrity
· Updates to a table referenced in a correlated subquery.
Deferred updates are also required when:· The update moves a row to a new page while the table is being accessed via a table scan or a clustered index.
· Duplicate rows are not allowed in the table, and there is no unique index to prevent them.
· The index used to find the data row is not unique, and the row is moved because the update changes the clustered index key or because the new row does not fit on the page.
Deferred updates incur more overhead than direct updates because they require Adaptive Server to reread the transaction log to make the final changes to the data and indexes. This involves additional traversal of the index trees.For example, if there is a clustered index on title, this query performs a deferred update:
update titles set title = "Portable C Software" where title = "Designing Portable Software"Deferred index inserts
Adaptive Server performs deferred index updates when the update affects the index used to access the table or when the update affects columns in a unique index. In this type of update, Adaptive Server:
· Deletes the index entries in direct mode
· Updates the data page in direct mode, writing the deferred insert records for the index
· Reads the log records for the transaction and inserts the new values in the index in deferred mode
Deferred index insert mode must be used when the update changes the index used to find the row or when the update affects a unique index. A query must update a single, qualifying row only once—deferred index update mode ensures that a row is found only once during the index scan and that the query does not prematurely violate a uniqueness constraint.
The update in below pix changes only the last name, but the index row is moved from one page to the next. To perform the update, Adaptive Server:
1. Reads index page 1133, deletes the index row for "Greene" from that page, and logs a deferred index scan record.
2. Changes "Green" to "Hubbard" on the data page in direct mode and continues the index scan to see if more rows need to be updated.
3. Inserts the new index row for "Hubbard" on page 1127.
Below pix shows the index and data pages prior to the deferred update operation, and the sequence in which the deferred update changes the data and index pages.
Assume a similar update to the titles table:
update titles set title = "Computer Phobic's Manual", advance = advance * 2
where title like "Computer Phob%"
This query shows a potential problem. If a scan of the nonclustered index on the title column found "Computer Phobia Manual," changed the title, and multiplied the advance by 2, and then found the new index row "Computer Phobic's Manual" and multiplied the advance by 2, the advance wold be very skewed against the reality.
A deferred index delete may be faster than an expensive direct update, or it may be substantially slower, depending on the number of log records that need to be scanned and whether the log pages are still in cache.
During deferred update of a data row, there can be a significant time interval between the delete of the index row and the insert of the new index row. During this interval, there is no index row corresponding to the data row. If a process scans the index during this interval at isolation level 0, it will not return the old or new value of the data row.
What will happen when an SQL Statement submitted to ASE?
What will happen when an SQL Statement submitted to ASE?
· SQL statement is parsed by the language processor
· SQL statement is normalized and optimized
· SQL is executed
· Task is put to sleep pending lock acquisition and logical or physical I/O
· Task is put back on runnable queue when I/O returns
· Task commits (writes commit record to transaction log)
· Task is put to sleep pending log write
· Task sends return status to client
Status Values Reported by sp_who | ||
Status | Condition | Effects of kill Command |
recv sleep | waiting on a network read | immediate |
send sleep | waiting on a network send | immediate |
alarm sleep | waiting on an alarm, such as waitfor delay "10:00" | immediate |
lock sleep | waiting on a lock acquisition | immediate |
sleeping | waiting disk I/O, or some other resource. Probably indicates a process that is running, but doing extensive disk I/O | killed when it "wakes up", usually immediate; a few sleeping processes do not wake up, and require a Server reboot to clear |
runnable | in the queue of runnable processes | immediate |
running | actively running on one on the Server engines | immediate |
infected | Server has detected serious error condition; extremely rare | kill command not recommended. Server reboot probably required to clear process |
background | a process, such as a threshold procedure, run by SQL Server rather than by a user process | immediate; use kill with extreme care. Recommend a careful check of sysprocesses before killing a background process |
log suspend | processes suspended by reaching the last-chance threshold on the log | killed when it "wakes up": 1) when space is freed in the log by a dump transaction command or 2) when an SA uses the lct_admin function to wake up "log suspend" processes |
Only a System Administrator can issue the kill command: permission to use it cannot be transferred.
Refreshing a Primary Database of a Replication setup into DEV/QA.
When we load a replicated database into another server, it will not come up/online due to secondary truncation point was set in the source database.
After Load is completed, when you issue online database command, It will be failed due to the replication setup i.e. secondary truncation point of repagent.Error Message: |
dbcc dbrepair(DBName,ltmignore) |
Sunday 17 July 2011
Sybase ASE - How to see all the column names in a database including data type.
There would be times, we need to check all the column names and data types of the columns, a scenario: when planning to migrate data etc.
The following Query gives all the column names in a database with data type. We can easily customize the Query to get a particular data type.
select object_name(id), sc.name,st.name from syscolumns sc, systypes st
where sc.type=st.type
and sc.usertype=st.usertype
and id>29
Saturday 16 July 2011
Sybase Certification...
Preparation
Attaining a certification for Sybase Adaptive Server Enterprise (ASE) should not be taken lightly as this will really help your career and give confidence. All you need to do is spend time on studying the Sybase documentation: you'll find that there are many features you've never used, and you need to fill these gaps in your ASE knowledge.
In any case, you should check out the information about certification-related issues on Sybase's own web pages at http://www.sybase.com/education/profcert/, as this will answer many of your questions.
For preparation, make sure you have the ASE System Admin Guide and the Reference Manual handy especially Whats New in ASE document, as well as the Performance & Tuning Guide for the Professional-level exams. You can download these manuals as PDF documents from the Sybase web site.
The exam codes can be used when booking an exam with Prometric:
- Certified Administrator for ASE 12.5 - Associate Level
In any case, this certification requires one exam (exam code 510-020), irrespective of any certifications you already may have. - Note that this exam is based on the first version of ASE 12.5, i.e. 12.5 GA. It does not contain any questions about new or changed functionality in later 12.5 releases such as 12.5.0.1.
Please check the cited URL for more info:
The exam questions fall into one of the following categories:
- "choose the best answer" : some questions are particularly nasty as all possible replies are valid in one way or another; still, you must choose only one (I recall a particularly ridiculous question like "The client-server architecture in Sybase SQL Server is designed to ..." and then all possible replies make sense). In cases like these, my recommendation is to try to select the reply that seems "best" from a Sybase company viewpoint; this seems to have worked for me.
- "choose N answers" : you must select the specified number of correct replies.
ASE 12.5 Administrators
Recommended Courses/Modules:
- Powering Up With ASE 12.5
- Fast Track to Adaptive Server Enterprise: 12.5
- System & Database Administration: ASE 12.5
ASE 12.5 Administrator Associate Exam Questions Topic wise:
What do I Study ?
1. ASE Overview (4 questions - 7%)
2. ASE 12.5 New Features (3 questions - 5%)
3. Configuration, Upgrade & Installation (2 questions - 4%)
4. Resource Allocation (4 questions - 7%)
5. Database & Logs (7 questions - 13%)
6. Backup & Recovery (6 questions - 11%)
7. Extensible New Limits (4 questions - 7%)
8. Cache Management (4 questions - 7%)
9. Security (5 questions - 9%)
10. Transaction Management (4 questions - 7%)
11. Dynamic Reconfiguration (4 questions - 7%)
12. Parallel Performance Optimization (3 questions - 5%)
13. Locking and Concurrency (3 questions - 5%)
14. Object Resource Management (3 questions - 5%)
· It’s multiple choice exam of 56 Questions out of 37 to be answered correctly in 90mins to hit the target.
When do I know if I passed?
Immediately. After taking the test, your score is immediately printed out and also be sent to Sybase for record keeping.
What if I fail?
The student is allowed to fail any of the two tests two times. If the student fails any of the two tests for the second time, the exam may not be taken again for the period of three months.
Taking is an exam is a stressful process, so try to limit your distractions during your study and test taking time.
You can mail me for the Sybase exam sample Q's dump for better planning and preparation.
Keep the hard-work and all the Best!!!
Subscribe to:
Posts (Atom)