Archive for December, 2009

SYBASE Combines World’s Leading Column-Based IQ Server with Unrivalled Global Messaging Reach

December 31st, 2009 No comments

Hey Guys,

It may be last post of the year 2009, but it is really a great news of 2009,now  two different platforms are combined.


Sybase, Inc. (NYSE: SY), an industry leader in delivering enterprise and mobile software, today announced it is leveraging the newest version of its industry leading column-based analytics server – Sybase IQ 15.1 -and its unrivalled global mobile messaging reach, enabling the company to provide deep analytics functionality on the Sybase 365 mobile services platform that will uniquely address the needs of mobile operators, financial institutions and enterprise customers.

ASE15 New Features – I: DBA Perspective

December 17th, 2009 1 comment

*Source : Sybase Resources on world wide web. Sybooks.

  • MDA Installation Automaticaly.
  • ASE 15.x isql client can now do large network packets.
  • IPv6 platform support : IPv6 is now supported on IBM AIX. 15.0.2 ESD#1.
  • Encrypted columns : meets US Government encryption standards.
  • IN ASE 15.x, there are a number of partition level operations you can do. Reorgs can be done on a partition level.
  • ASE 15.0 and later versions no longer use vdevno. i.e. the disk init syntax doesn’t need to mention the vdevno parameter(Even earlier, it was optional).
  • Before 15.0, after changing a database option we need to use that database and do checkpoint on it. But ASE15.0 doesn’t need this.
  • ASE 15 Cluster Edition, a high-availability version of ASE similar to Oracle’s RAC.
  • Dumping and loading databases with password protection:You can protect your database dump from unauthorized loads using the password parameter of the dump database command. If you include the password parameter when you make a database dump, you must also include this password when you load the database.

  • Fast bcp:  In 15.0.2, fast BCP is also allowed for indexed tables. Rest all things about bcp remain same, like select into/bulkcopy/pllsort’ is enabled; logging only the page allocations in log etc.
  • Disk init syntax:Disk init syntax in 12.5 expects size parameter in K, M, and G only. From 15.0 and onwards, T (Terabyte) can be specified.Also, pre 15.0; the maximum size of a device was 32GB
  • In ASE 15.0.2 IR, you can run sp_configure nondefault, which will list out, the configuration parameters set to non-default values. It is veru usefull to check which config not using the dafult values.
  • Automatic update statistics : Instead of manually running update statistics at a certain time, you can set update statistics to run automatically at the time that best suits your site and avoid running it at times that hamper your system. The best time for you to run update statistics is based on the feedback from the datachange function. datachange also helps to ensure that you do not unnecessarily run update statistics.In ASE 15.0, Update statistics is not necessary after index rebuild. Also sp_recompile is not necessary after index rebuild.
  • Application tracing:Version 15.0.2 of ASE comes with the new feature of ‘application tracing’. Understanding this feature is a must for every DBA, because it provides a simple mechanism to figure out what your client applications are actually doing: apptracing lets you capture the SQL submitted to the ASE server by a specific client connection, and writes it into a file.     set tracefile ‘/tmp/spid54.trace.out’ for 54 # where 54 is spid            set show_sqltext on
  • VLDB Support: ASE 15 allows you to assign two billion logical devices to a single server, with each device up to 4 Tb in size. ASE 15 supports over 32,767 databases, and the maximum size limit for an individual database is 32 terabytes, extending the maximum storage per ASE server to over 1 million terabytes!
  • Functional indexes: When applications need to search tables based on the result of a function, performance can suffer. Functional indexes allow the server to build indexes on a table based on the result of a function. When repeated searches use that function, the results do not need to be computed from scratch.
  • Row-locked system catalogs :Adaptive Server version 15.0 converts most system catalogs to a datarows locking scheme. These system catalogs continue to use allpages locking scheme: Materialized tables such as syslocks and sysprocesses. These tables are generated during run-time and their locking schemes are irrelavent for concurrency.

sysmessages and sysusermessages, which are read-only tables.

Auditing tables in sybsecurity, which are write-once and read many times.

  • Semantic partitions/smart partitioning:  ASE 15 makes large databases easy to manage. And more efficient by allowing you to divide tables into smaller partitions which can be individually managed. You can run maintenance tasks on selected partitions to avoid slowing overall performance, and queries run faster because ASE 15’s smart query optimizer bypasses partitions that don’t contain relevant data.
  • Query Processor: The Adaptive Server version 15.0 query processor is self-tuning, requiring fewer interventions than earlier versions. This version of Adaptive Server has less reliance on worktables for materialization between steps since the engine supports data flow between steps. However, more worktables could be used in cases where Adaptive Server determines that hash and merge operations are effective.
  • Scrollable cursors:With large data sets, filing through a mountain of results data can be difficult. ASE 15’s bi-directional scrollable cursors make it convenient to work with large result sets because your application can easily move backward and forward through a result set, one row at a time. This especially helps with Web applications that need to process large result sets but present the user with subsets of those results.
  • Computed columns: Often applications repeat the same calculation over and over for the same report or query. ASE 15 supports both virtual and materialized columns based on server calculations. Columns can be the computed result of other data in the table, saving that result for future repeated queries.
  • Query processing metrics (qp metrics):Query processing (QP) metrics identify and compare empirical metric values in query execution. When a query is executed, it is associated with a set of defined metrics that are the basis for comparison in QP metrics.
  • Large identifiers:There are new limits for the length of object names or identifiers: 255 bytes for regular identifiers, and 253 bytes for delimited identifiers. The new limit applies to most user-defined identifiers including table name, column name, index name and so on. Due to the expanded limits, some system tables (catalogs) and built-in functions have been expanded.
  • User-defined web services:In addition to the Web methods provided by the Adaptive Server Web Services Engine, Web Services enables you to create Web services and execute SQL commands in Adaptive Server Enterprise using either a Web browser or a SOAP client. These user-defined Web services use existing security and auditing control inherent in Adaptive Server Enterprise.

Will post New Features part 2, once get completed.
If you find any new feature, which is very useful, please comment out in this section.


Cross Database intgerity constraints and dbid mismatch

December 16th, 2009 1 comment

Hi Folks,

I came across the interesting issue on ASE Server.(Still not sure, need to check with sybase tech suport)

Suppose in our prod env, we have cross database referential integrity constraints, these referential integrity constraints stores in sysreferences table by relating the dbids of the two dbs(primary db and referenced db).

When we perform the refresh from one server to another (Suppose PROD – > UAT).These constraints come as it is in refreshed database. In refreshed dataserver, all dbs may not be created in same order as source dataserver, which results in dbid mismatch from source server.

Still our constraints are same as source db with source dbids.

When we run the dbcc checks on refreshed db, it starts flaging for the wrong dbid in sysreferences table.

Interesting ! Isn’t it?

In sybase manual and sybooks, I didn’t get any article related to db id mismatch, for cross db integrity constraints.

Will update u soon, if any.


Categories: ASE, Troubleshooting Tags: , ,

Syssrvroles dropped

December 12th, 2009 3 comments

Hey Guys!
In one case, I dropped out the syssrvroles accidently. Now, no one having any roles, even sa is not having its roles..I hav backup of all system tables and dump of master db. But We dont hav sso_role for any login we can’t do the bcp in , we can’t insert any row in any system table. Even we can’t change the allow update on system table, requires sso_role. Anyway. We have backup of we can recover loading the dump of master , but still need suggestions….