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

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.

Source: http://it.tmcnet.com/news/2009/12/16/4534992.htm

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

*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.

-AnVa

Cross Database intgerity constraints and dbid mismatch

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.


Anva

Syssrvroles dropped

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 master..so we can recover it..by loading the dump of master , but still need suggestions....

Transportable Databases

Very Useful feature to migrate the database between two Production Server, for creating a HA  Server or DR Server.

It can migrate the database between different version where in place upgrade is not possible.

Concept :

The current version of ASE 12.5.4 or possibly earlier on ones allow us to use a Sybase utility called transportable databases to move database at device level between servers. In this scenario, databases technically freeze(by quieseing), the underlying device become stagnant and copies of devices can be made.

In the heart of this operation lies a Sybase utility that allow us to create a binary file called as menifest file that has all the details of devices, the databases  and their layout.

Testing :

Suppose we have two devices on file system(as opposed to raw partitions).  These devices were created in Sybase on ASE 12.5.4 on server called SYB_DS1. They were called SYB_DS1_data01.dat and SYB_DS1_log01.dat respectively.

On these two devices I have created two databases transportable1 and transportable2.

Now I logged in SYB_DS1 server and quiesced these two databases and created a menifest file.

1> QUIESCE DATABASE transport HOLD transportable1,transportable2 FOR EXTERNAL DUMP TO  '/var/tmp/menifest_file'  WITH OVERRIDE

2>go

Note : The manifest file is called as /var/tmp/manifest_file.

Now copy above mentioned these two devices to new location with new name for the other server SYB_DS2 on the same host with new name SYB_DS2_data01.dat and SYB_DS2_log01.dat.

Now  Mount these database from two devices on other Sybase Server SYB_DS2. This SYB_DS2 can be ASE 15.0.

Logged in SYB_DS2, First get the information out from menifest file.

1> MOUNT DATABASE ALL FROM '/var/tmp/menifest_file' WITH LISTONLY

2>go

[database]

trasnportable1

transportable2

[device]

'/export/sybase/SYB_DS1/dev/SYB_DS1_data01.dat'='datadev01'

'/export/sybase/SYB_DS1/dev/SYB_DS1_log01.dat'='datalog01'

Now mount the new copied devices with menifest file in SYB_DS2.

1> MOUNT DATABASE ALL FROM '/var/tmp/menifest_file' using

2>'/export/sybase/SYB_DS2/dev/SYB_DS2_data01.dat'='datadev01'

3>'/export/sybase/SYB_DS2/dev/SYB_DS2_log01.dat'='datalog01'

4>go

It will give long output with successful message as

MOUNT DATABASE: Completed reovery of mounted datbase 'transportable1'.

MOUNT DATABASE: Completed reovery of mounted datbase 'transportable2'.

Both the databases would be in offline in SYB_DS2. You need to bring them online by online database comand.

If the SYB_DS2 is ASE15 server, it will upgrade the database.

Happy  Learning!

AnVa

Syncing the Replicated DB with Primary DB in warm standby setup!

Following steps  we can use for syncing the replicated database  from the primary database in warm standby setup.

PDS - Primary Dataserver

PDB - Primary Database

RDS - Replicated Dataserver

RDB - Replicated Database in RDS

Step #1

Put database  PDB in dbo use only

Clean up all logins besides systems, ( terminate all user processes )

Step #2

Quiese the replication server.

isql -Usa -Ppasswd -SRS

1> admin quiesce_check

2> go

Replication Server RS is Quiesced

1>

Step #3

Stop the rep agent thread of Primary Database.

isql -Usa -Ppasswd -SPDS

use PDB

go

sp_stop_rep_agent PDB

go

Step #4

Suspend the connection to Primary database. Login into the Replication Server and issue the following command.

isql -Usa -Ppasswd -SRS

suspend connection to PDS.PDB

go

Step #5

Ignore the secondary transaction point in Primary Database.

isql -Usa -Ppasswd -SPDS

use PDB

go

dbcc settrunc('ltm', 'ignore')

go

Step #6

Truncate the tran log and reset the locator value  to zero. Please only execute if the above step was successful.

isql -Usa -Ppasswd -SPDS

dump tran PDB with truncate_only

go

Issue this command in RSSD database.

rs_zeroltm PDS, PDB

go

Step #7

Dump the primary database.

isql -Usa -Ppasswd -SPDS

dump database PDB to '/dumpfs/PDB.dmp'

go

Step #8

Transfer the dump to RDS and load the RDB.

isql -Usa -Ppasswd -SRDS

load database RDB from '/sybase/RDS/dumpfs/PDB.dmp'

go

Step #9

Valid the secondary tran point  in PDS.

isql -Usa -Ppasswd -SPDS

use PDB

go

dbcc settrunc('ltm', 'valid')

go

Step #10

Start the repagent for PDB in PDS.

isql -Usa -Ppasswd -SPDS

use PDB

go

sp_start_rep_agent PDB

go

Step #11

Resume the connection from the PDS.PDB in Replication Server.

isql -Usa -Ppasswd -SRS

resume connection to PDS.PDB

go

Step #12

Remove DBO use only from db.

Hello world!

Same as all the languages , I am starting this blog with Hello World!

print "Hello world!

First of all the introduction with Sybase and its technologies which are heavily impacting  the information(data) across the globe.

About Sybase: Sybase is largest  enterprise software and service company execusively focused on managing and mobilizing the information . http://www.sybase.com/

About Adaptive Server Enterprise(ASE) : Adaptive Server Enterprise is the mission-critical data management system for taming the information explosion. http://www.sybase.com/products/databasemanagement/adaptiveserverenterprise

About Replication Server: Move and synchronize data across the distributed enterprise with Replication Server.  http://www.sybase.com/products/businesscontinuity/replicationserver

About Sybase IQ: Highly optimized business intelligence, analytics and data warehousing.  http://www.sybase.com/products/datawarehousing/sybaseiq

Besides that Sybase is supporting lot of technologies . To know more about. http://www.sybase.com/products