Archive

Posts Tagged ‘Interview Ques’

Backup Server Traceflags

October 13th, 2012 No comments

Backup Server Traceflags

 Backup Server trace flag: 1 : Prints blocksize used during a dump or a load.
 Backup Server trace flag: 2 : Prints i/o optimization parameters used during a dump or a load.
 Backup Server trace flag: 3 : Allow dumping to the /dev/null device.
 Backup Server trace flag: 4 : Prints allocation percentage of the allocation units during dump.
 Backup Server trace flag: 5 : Prints the database pagesize used during a dump or a load.
 Backup Server trace flag: 6 : Sets tracing ON for the external API module.
 Backup Server trace flag: 7 : Disable locking on a file/device.

How Can we Enable:

SYB_BACKUP…qatraceon <traceflag> : Turn On a particuar trace flag
SYB_BACKUP…qatraceon 0 : Display the all enabled traceflags
SYB_BACKUP…qatraceoff <traceflag> : Turn Off a particuar trace flag
SYB_BACKUP…qatraceoff 0 : Turn Off all trace flags

1> SYB_BACKUP…qatraceon 1
2> go
Backup Server: 3.59.1.1: Turning on Backup Server trace flag: 1
(return status = 0)
1> SYB_BACKUP…qatraceon 2
2> go
Backup Server: 3.59.1.1: Turning on Backup Server trace flag: 2
(return status = 0)
1> SYB_BACKUP…qatraceon 3
2> go
Backup Server: 3.59.1.1: Turning on Backup Server trace flag: 3
(return status = 0)
1> SYB_BACKUP…qatraceon 4
2> go
Backup Server: 3.59.1.1: Turning on Backup Server trace flag: 4
(return status = 0)
1> SYB_BACKUP…qatraceon 5
2> go
Backup Server: 3.59.1.1: Turning on Backup Server trace flag: 5
(return status = 0)
1> SYB_BACKUP…qatraceon 6
2> go
Backup Server: 3.59.1.1: Turning on Backup Server trace flag: 6
(return status = 0)
1> SYB_BACKUP…qatraceon 7
2> go
Backup Server: 3.59.1.1: Turning on Backup Server trace flag: 7
(return status = 0)
1> SYB_BACKUP…qatraceon 0
2> go
Backup Server: 3.63.1.1: Backup Server trace flag: 1 is on. This flag does the following: Prints blocksize used during a dump or a load.
Backup Server: 3.63.1.1: Backup Server trace flag: 2 is on. This flag does the following: Prints i/o optimization parameters used during a dump or a load.
Backup Server: 3.63.1.1: Backup Server trace flag: 3 is on. This flag does the following: Allow dumping to the /dev/null device.
Backup Server: 3.63.1.1: Backup Server trace flag: 4 is on. This flag does the following: Prints allocation percentage of the allocation units during dump.
Backup Server: 3.63.1.1: Backup Server trace flag: 5 is on. This flag does the following: Prints the database pagesize used during a dump or a load.
Backup Server: 3.63.1.1: Backup Server trace flag: 6 is on. This flag does the following: Sets tracing ON for the external API module.
Backup Server: 3.63.1.1: Backup Server trace flag: 7 is on. This flag does the following: Disable locking on a file/device.
(return status = 0)

1> dump database model to “/tmp/model.dmp”
2> go
Backup Server session id is: 42. Use this value when executing the ‘sp_volchanged’ system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.173.1.1: The database pagesize is ‘2048’ bytes.
Backup Server: 4.41.1.1: Creating new disk file /tmp/model.dmp.
Backup Server: 6.28.1.1: Dumpfile name ‘model122871499A  ‘ section number 1 mounted on disk file ‘/tmp/model.dmp’
Backup Server: 4.166.1.1: Using dbiosize of 262144 bytes for device /tmp/model.dmp.
Backup Server: 4.165.1.1: Using iocount of 1 for device /tmp/model.dmp.
Backup Server: 4.166.1.2: Using zonesize of 262144 bytes for device /tmp/model.dmp.
Backup Server: 4.166.1.3: Using blocksize of 65536 bytes for device /tmp/model.dmp.
Backup Server: 4.165.1.2: Using numzones of 3 for device /tmp/model.dmp.
Backup Server: 4.155.1.1: Using maximum block size of 65536 bytes for device /tmp/model.dmp.
Backup Server: 4.169.1.1: Dumping entire allocation unit of virtual disk /opt/sybase/new15/ASE/data/master_PROD_ASE_DS.dev, allocation page number 0 having reserved percentage 56% and cumulative allocation percentage 43%.
Backup Server: 4.169.1.1: Dumping entire allocation unit of virtual disk /opt/sybase/new15/ASE/data/master_PROD_ASE_DS.dev, allocation page number 0 having reserved percentage 56% and cumulative allocation percentage 43%.
Backup Server: 4.169.1.1: Dumping only allocated pages of virtual disk /opt/sybase/new15/ASE/data/master_PROD_ASE_DS.dev, allocation page number 256 having reserved percentage 56% and cumulative allocation percentage 22%.
Backup Server: 4.169.1.1: Dumping only allocated pages of virtual disk /opt/sybase/new15/ASE/data/master_PROD_ASE_DS.dev, allocation page number 512 having reserved percentage 56% and cumulative allocation percentage 21%.
Backup Server: 4.169.1.1: Dumping only allocated pages of virtual disk /opt/sybase/new15/ASE/data/master_PROD_ASE_DS.dev, allocation page number 768 having reserved percentage 56% and cumulative allocation percentage 16%.
Backup Server: 4.169.1.1: Dumping only allocated pages of virtual disk /opt/sybase/new15/ASE/data/master_PROD_ASE_DS.dev, allocation page number 1024 having reserved percentage 56% and cumulative allocation percentage 0%.
Backup Server: 4.169.1.1: Dumping only allocated pages of virtual disk /opt/sybase/new15/ASE/data/master_PROD_ASE_DS.dev, allocation page number 1280 having reserved percentage 56% and cumulative allocation percentage 0%.
Backup Server: 4.188.1.1: Database model: 820 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database model: 828 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database model).

1> SYB_BACKUP…qatraceoff 0
2> go
(return status = 0)
1> SYB_BACKUP…qatraceon 0
2> go
(return status = 0)

 

 

Sybase DBA Interview Q&A – You Must Know !

October 9th, 2012 4 comments

Dear All,

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 :

Sybase-DBA-Interview-Q &A (767)

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 !

-Team, sybaseblog.com.

PS : You need to login to download the Q&A Bank.

 

 

ASE15 New Features – II : DBA Perspective

September 15th, 2012 No comments

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/

New features in SAP Sybase ASE 15.7 ESD#2

August 23rd, 2012 No comments

Really very Good and useful new features in Sybase ASE 15.7 ESD#2, Checked Out in Rob’s Blog :

 

A few weeks ago (early August 2012), ASE 15.7 ESD2 was released. Despite being an ESD, this ASE release actually contains a truckload of great new features as well. You should really check these out since some of these are just so useful… Let me just highlight a selected few that I like particularly (I hope to come back to some of these topics in the time ahead).

* larger database size – First, the maximum size of an ASE database has been doubled as of 15.7 ESD2. This means that with a 2KB or 16KB page size, the maximum size of a single ASE database is now 8TB or 64TB, respectively (and you can still have thousands of such databases in a single ASE server).
* async database creation
* split/merge partition
* non-blocking table rebuild
* faster update statistics
* faster index creation
* query performance
* Materialized Views
* security & permission

Source : http://blogs.sybase.com/database/2012/08/why-you-should-check-out-ase-15-7-esd2/#

dbcc checkdb vs dbcc checkstorage

June 25th, 2012 No comments

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.

Performance Issue

June 24th, 2012 No comments

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

http://sybaseblog.com/sybasewiki/index.php?title=Query_-_Which_currently_executing_queries_are_consuming_the_most_CPU_%3F

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.

You can get full details on MDA queries @ http://sybaseblog.com/sybasewiki/index.php?title=Category:MDA_Table_Query

Thanks.

Sybase ASE Indexes

April 9th, 2012 No comments

Index:

Indexes are the most important physical design element in improving database performance:

Indexes help to avoid table scans. A few index pages and data pages can satisfy many queries without requiring reads on hundreds of data pages.

Indexes in ASE:

We can divide ASE indexes in two categories by : i) Physical Order of Data with index key  ii) Uniqueness of the index column

Based on the physical order of data, Adaptive Server provides two general types of indexes that can be created at the table or at the partition level:

Clustered indexes, where the data is physically stored in the order of the keys on the index:

• For all pages-locked tables, rows are stored in key order on pages, and pages are linked in key order.

• For data-only-locked tables, indexes are used to direct the storage of data on rows and pages, but strict key ordering is not maintained.

Non clustered indexes, where the storage order of data in the table is not related to index keys

Based on index column uniqueness, indexes can be unique and non unique.

So following types of indexes present in ASE with permutation and combination with above two properties:

1. Unique Clustered Indexes

2. Non unique Clustered Indexes

3. Unique Non-clustered Indexes

4. Non unique Non-clustered indexes

 

Clustered Index Non Clustered Indexes
Unique Unique Clustered Index Unique Non Clustered Index
Non – Unique Non Unique Clustered Index Non Unique Non Clustered Index

So, all the indexes come under above 4 types:

 

In case of more than one index column, we can add prefix composite in above types.

Means Composite indexes are those indexes, which are created on more than on one column. Above four types of index can be composite as well.

In the case of partitions, we can categories above types as local and global indexes.Local indexes get created at partition level and table level index called as Global indexes.

Global indexes with one index tree cover the whole table, or local indexes with multiple index trees, each of which covers one partition of the table.

Function-based indexes are a type of non clustered index which use one or more expressions as the index key.

 

Implementation of Function String in Sybase Replication Server(SRS)

January 30th, 2012 No comments

These experience shared by  Senior DBAs as name mentioned, Hope this  will help you to understand more about function string from implementation point of view in a Replication environment: 
Craig Oakley , Senior DBA.
—————————-

We used function strings when we wanted to replicate all columns to some servers, and only selected columns to other (web-facing) servers. This was particularly useful before Rep Server allowed multiple RepDefs on the same table. One concern was text columns which were not being replicated to the web-facing server: we had to create a function string to get a text pointer (we used a one-row table and just update all the text columns on top of each other, as the value was not needed on that server): failure to get a text pointer cause the DSI to go down, and we could not specify that as a condition to ignore.

Beyond this, I would imagine function strings could help specify how you want the update to be done, which could be a performance improvement. It would also allow for a different implementation at the replicate than there is at the primary (such as a table at the primary being two joined tables at the replicate).

 

Sukhesh Nair, Senior Sybase DBA
———————————–

We used to have a setup where data was replicated from sybase to oracle as also to a warm standby sybase server. Rep Server function strings helped in filtering data that would need to be passed to Oracle. It helped immensely in streamlining the data flow to targets by manipulating the incoming data through function string. I feel it is one of the most advanced and useful yet very less used capabilities of Sybase Rep Server.
The deterrent could be because of the complexity it would introduce to the replication system. The setup we had worked wonderfully and never gave us any major problems. Without proper monitoring (which needs to be scripted by DBAs) it used to be hard to maintain. Many of the current Rep Server administrators I see do not have adequate knowledge or experience of handling function strings.

Rey Wang , Senior Sybase DBA
————————-

You can map the delete to no op with functional string.

Partha Gogoi Senior DBA
————————-

We use function strings to transform data at the replicate..We have databases being replicated from Toronto and New York to London, Sydney and Singapore and the client ids are transformed at the replicate because, as per business requirements, the client ids are different at each site.. Of course , having a Universal client id would simplify things , but the systems and databases at each site grew independently until replication was set up and it would be a lot of rework to change all the client ids at the replicate sites

Øystein Grinaker Senior DBA
—————————

A Function String could be used to change default behaviour.
Say you delete a row in a table on PDB, but you do not want to delete the row on the RDB. Then make a change in rs_delete. You may make the rs delete just to make a logical delete by updateing a deletemarker for that spesific row.

 

Source : Linkedin.com

http://www.linkedin.com/groups/What-are-possible-usage-Function-3955841.S.65585220?qid=4eb177c2-d7e3-4572-9be4-92d640046c6c&trk=group_most_recent_rich-0-b-ttl&goback=%2Egmr_3955841

 

 

ASE database for SAP ERP

December 20th, 2011 No comments

Hello all,

These are copilataion for Sybase ASE on SAP from the Rob’s Blog :

Read Full Story : http://blogs.sybase.com/database/2011/12/so-what-does-an-ase-database-look-like-in-sap-erp/

  • SAP has released Business Suite on ASE version 15.7.
  • All SAP application data resides in a single ASE database. There is another small database for use by SAP tools.
  • The ASE database uses a 16KB page size.
  • For ERP only (i.e. not counting CRM and the other Business Suite modules), the database contains about 80,000 tables and 170,000 indexes. This is because SAP ERP has many features and functions, all with their own set of tables. SAP customers typically run only a subset of all those functions so in practice a large part of those 80,000 tables will always remain empty.
  • All SAP tables use datarowslocking (there is an interesting historical dimension.
  • All tables names are in uppercase; some table names contain special characters, like the slash character in “/BCV/C_QATTR” (I don’t have a clue what that name means, BTW)
  • Apart from the tables, there are also about 10,000 views. No stored procedures or triggers are used.
  • SAP makes heavy use of dynamic SQL (also known as “prepared statements”).
  • Many tables have a text or image column.
  • All tables are owned by one database user (and that’s not the dbouser).
  • The ASE database is accessed through ODBC.
  • SAP makes frequent use of the built-in ASE Job Scheduler (originally added in ASE 12.5.1).
  • The ASE server uses Unicode with the utf8 character set.

Isolation Level – Summarized.

October 10th, 2011 No comments

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.
(4) SERIALIZABLE.

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 . Transaction T2
then creates data items that satisfy T1’s and commits. If T1 then repeats its read with the
same , it gets a set of data items different from the first read.

—————————————————————————–
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
——————————————————————————