Archive

Archive for the ‘Sybase ASE/REP Interview Questions’ Category

Sybase vs. Oracle: 10 reasons to use Sybase on Linux

December 15th, 2012 No comments

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.

  1. The latest Sybase flagship product, ASE 15, has filled much of the perceived functionality gap between ASE and other databases.
  2. Linux is an ideal and cost-effective platform for development teams and many companies. With the availability of heterogeneous dump and load of Sybase databases across different operating systems, Sybase — by virtue of its modularity and ease of use — is an ideal DBMS for Linux. This needs to be contrasted with Oracle which is, pound for pound, a far heavier beast and resource-hungry.
  3. Sybase has a well-established and skilled workforce, offering infrastructure and development teams who are fully familiar with database architectures and Sybase products.
  4. Applications developed using Sybase have been running for a while and providing adequate service. There is absolutely no guarantee that migrating these applications to another DBMS will result in the same level of service. I know of no case where a migration from Sybase to Oracle or otherwise has resulted in a noticeable performance gain.
  5. The exit barriers from Sybase and the entry barriers to others are high. For a medium-to-large application, it will take an average of 10 years for investment for ROI. A simple cost/benefit analysis will verify this statement.
  6. Check our Sybase ASE 15’s total cost of ownership (TCO) compared to Oracle. Based on my clients’ experience, one requires 2.5 Oracle DBAs to provide the same level of service as a single Sybase DBA.
  7. Sybase is fairly modular and has a simple syntax. Contrast this with Oracle where, in most cases, you require a third-party product to allow the DBA to reduce his/her workload. Perhaps that may be a reason why TOAD, a non-Oracle product, is the most popular GUI interface for Oracle!
  8. Since Sybase is a very secure database. In fact, it is a favourite with the U.S. Department of Defense and Central Intelligence Agency. Sybase is quickly bringing the security features — such as extensive Kerberos support, programmable authentication and data encryption — to the market.
  9. SAP’s release of its lightway product for Sybase on Linux and IBM’s steps towards selling their line of P5 Linux servers with ASE, while they have DB2 and Informix themselves, are pretty strong statements about the future of Sybase.
  10. The future of Sybase is secure, largely because it is well entrenched in its core marketplace, the financial services market. More than half of Wall Street runs on Sybase. The majority of complex trading systems and banks use sophisticated replication technology to provide publisher-subscriber or peer-to-peer replication. At this juncture, none of the competitors can provide the same degree of functionality that Sybase Replication Server provides.

Source :http://searchenterpriselinux.techtarget.com/tip/Sybase-vs-Oracle-10-reasons-to-use-Sybase-on-Linux

SAP D&T Academy Video: How to install sybsyntax db in ASE server

November 22nd, 2012 2 comments

Please follow the following video for installing the sybsyntax db in ASE server.

 

 

SAP D&T Academy Video:How to create login in Sybase ASE

November 22nd, 2012 No comments

Please follow the following video for creating login in Sybase ASE

 

SAP D&T Academy Video :How to set database options in sybase ASE

November 22nd, 2012 No comments

Please follow the following video for setting sybase database options.

 

To Get all index size in a Database :sp__getallindexsize

October 17th, 2012 No comments

You can get all index size in a Database by using sp__getallindexsize as below :

I used the posted method to calculate index size : Calculating Table & Index Usage in ASE

Down load the stored proc here: sp_getallindexsize.sql (49)

1> use pubs3
2> go
1> sp__getallindexsize
2> go
table_name      index_name            size  reserved unused
————— ——————— —– ——– ——
authors         aunmind               2 KB  32 KB    30 KB
roysched        titleidind            2 KB  32 KB    30 KB
titleauthor     auidind               2 KB  32 KB    30 KB
salesdetail     titleidind            2 KB  32 KB    30 KB
titleauthor     titleidind            2 KB  32 KB    30 KB
salesdetail     salesdetailind        2 KB  32 KB    30 KB
titles          titleind              2 KB  30 KB    28 KB
blurbs          tblurbs               14 KB 16 KB    2 KB
sales           sales_8320029642      2 KB  16 KB    14 KB
stores          stores_7840027932     2 KB  16 KB    14 KB
titles          titles_6720023942     2 KB  16 KB    14 KB
authors         authors_5760020522    2 KB  16 KB    14 KB
publishers      publishers_6240022232 2 KB  16 KB    14 KB
store_employees store_empl_8960031922 2 KB  16 KB    14 KB

(14 rows affected)
(return status = 0)

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/

Top 3 Benefits of SAP Business All-in-One on SAP Sybase ASE

September 14th, 2012 No comments
  • Closer and simpler business relationship with SAP. One vendor for both the database and application layer streamlines the processes for customers and partners.
  • Lower costs. SAP Sybase ASE has been shown to have lower maintenance costs over competitive database platforms, which lowers the TCO for the entire landscape.
  • Platform for innovation. Using an SAP database platform provides the foundation for future technology innovation opportunities coming with SAP HANA.

Read Full Story @ http://sapinsider.wispubs.com/Article/Database-Distress-No-More/6610

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/#