1. How do I tell which tables have identities in Sybase?
You can tell if a table has identities one of two ways:
1. sp_help [tablename]: there is a field included in the sp_help output describing a table called "Identity." It is set to 1 for identity fields, 0 otherwise.
2. Within a database, execute this query:
1. select object_name(id) "table",name "column", prec "precision"
2. from syscolumns
3. where convert(bit, (status & 0x80)) = 1
4. go
this will list all the tables and the field within the table that serves as an identity, and the size of the identity field.
2. How do I Configure the burn factor in Sybase?
The number of identity values that gets "burned" upon a crash or a shutdown can by found by logging into the server and typing:
1.sp_configure "identity burning set factor"
2. go
the Default value set upon install is 5000. The number "5000" in this case is read as ".05% of all the potential identity values you can have in this particular case will be burned upon an unexpected shutdown." The actual number depends on the size of the identity field as you specified it when you created your table.
To set the burn factor, type:
1. sp_configure "identity burning set factor", [new value]
2. go
This is a static change; the server must be rebooted before it takes effect
3. How do I configure Identities in Sybase?
You can either create your table initially with the identity column:
1. create table ident_test
2. (text_field varchar(10),
3. ident_field numeric(5,0) identity)
4. go
Or alter an existing table and add an identity column:
1. alter table existing_table
2. add new_identity_field numeric(7,0) identity
3. go
When you alter a table and add an identity column, the System locks the table while systematically incrementing and adding unique values to each row. IF YOU DON'T SPECIFY a precision, Sybase defaults the size to 18! Thats 1,000,000,000,000,000,000-1 possible values and some major major problems if you ever crash your ASE and burn a default number of values... (10^18 with the default burn factor will burn 5^14 or 500,000,000,000,000 values...yikes).
4. How can I execute dynamic SQL with ASE in Sybase?
Adaptive Server Enterprise: System 12
ASE 12 supports dynamic SQL, allowing the following:
declare @sqlstring varchar(255)
select @sqlstring = "select count(*) from master..sysobjects"
exec (@sqlstring)
go
Adaptive Server Enterprise: 11.5 and 11.9
* Firstly define your local server to be a remote server using
sp_addserver LOCALSRV,sql_server[,INTERFACENAME]
go
* Enable CIS
sp_configure "enable cis",1
go
* Finally, use sp_remotesql, sending the sql to the server defined in point 1.
declare @sqlstring varchar(255)
select @sqlstring = "select count(*) from master..sysobjects"
sp_remotesql LOCALSRV,@sqlstring
go
5. Which version of Open Client works with which ASE in Sybase?
The TDS protocol that *is* Open Client is built so that either the client or server will fallback to a common dialect. I suppose that it is theoretically possible that both would fallback for some reason, but it seems unlikely. I was recently working with a client that was using Open/Client 4.2 to speak to a version 11.5 ASE using Powerbuilder 3 and 4! Amazing, it all worked! The main problem that you will encounter is not lack of communication but lack of features. The facility to bcp out of views was added to the 11.1.1 release. You will still be able to connect to servers with old copies of Open/Client, you just won't have all of the features.
There is also another fairly neat feature of the later releases of Open/Client, it has a very good compatibility mode for working with old applications. The client that was running Open/Client 4.2 with Powerbuilder 3 is now connecting to the database using version 11.1.1. Which is not bad when you remember that Powerbuilder 3 only talked 4.2 DBLib!
6. Which should I use, RepAgent or LTM in Sybase?
There are pros and cons to both, however, I think that it should be stated up front that RepAgents are the latest offering and I believe that Sybase would expect you you to use that. Certainly the documentation for LTMs is a little buried implying that they do not consider it to be as current as LTMs.
LTM Cons:
* Older technology. Not sure if it is being actively supported.
* Not integrated within ASE, so there is a (small) performance penalty.
* Separate processes, so need additional monitoring in production environments.
LTM Pros:
* Possible to restart LTM without having to restart ASE.
RepAgent Cons
* If it crashes it is possible that you will have to restart ASE in order to restart RepAgent.
RepAgent Pros
* Latest, and presumably greatest, offering.
* Tightly integrated with ASE so good performance.
* Less to manage, no extra entries in the interfaces file.
7. How can I improve throughput in Sybase?
Check the Obvious
First, ensure that you are only replicating those parts of the system that need to be replicated. Some of this is obvious. Don't replicate any table that does not need to be replicated. Check that you are only replicating the columns you need. Replication is very sophisticated and will allow you to replicate both a subset of the columns as well as a subset of the rows.
Replicate Minimum Columns
Once the replication is set up and synchronised, it is only necessary to replicate those parts of the primary system that actually change. You are only replicating those rows and columns that need to be replicated, but you only need to replicate the actual changes. Check that each replication definition is defined using the clause:
create replication definition rep_def_name
with primary
8. What is the difference between an LTM and a RepAgent in Sybase?
Log Transfer Managers (LTMs) and RepAgents are the processes that transfer data between ASE and the Replication Server.
LTMs were delivered with the first releases of Replication Server. Each LTM is a separate process at the operating system level that runs along side ASE and Replication Server. As with ASE and Replication Server, a RUN_ and configuration file is required for each LTM. One LTM is required for each database being replicated.
Along with ASE 11.5 a new concept was introduced, that of RepAgent. I am not sure if you needed to use RepServer 11.5 as well, or whether the RepAgents could talk to earlier versions of Replication Server. Each RepAgent is, in effect, a slot-in replacement for an LTM. However, instead of running as separate operating system process, it runs as a thread within ASE. Pretty much all of the requirements for replication using an LTM apply to the RepAgents. One per database being replicated, etc. but now you do not need to have separate configuration files.
9. What is the Difference Between Replication Server and SQL Remote in Sybase?
Both SQL Remote and Replication Server perform replication. SQL Remote was originally part of the Adaptive Server Anywhere tool kit and is intended for intermittent replication. (The classic example is that of a salesman connecting on a daily basis to upload sales and download new prices and inventory.) Replication Server is intended for near real-time replication scenarios.
10. What is Replication Server in Sybase?
Replication Server moves transactions (insert, updates and deletes) at the table level from a source dataserver to one or more destination dataservers. The dataserver could be ASE or other major DBMS flavour (including DB2, Informix, Oracle). The source and destinations need not be of the same type.
What can it do ?
* Move data from one source to another.
* Move only a subset of data from source to destination. So, you can ‘subscribe' to a subset of data, or a subset of the columns, in the source table, e.g. select * from clients where state = ‘NY'
* Manipulation/transformation of data when moving from source to destination. E.g. it can map data from a data-type in DB2 to an equivalent in Sybase.*
* Provide a warm-standby system. Can be incorporated with Open Switch to provide a fairly seamless fail-over environment.
* Merge data from several source databases into one destination database (could be for a warehouse type environment for example).
* Move data through a complicated network down to branch offices, say, only sending the relevant data to each branch.
