SET SHOWPLAN [ON|OFF] is a session-level command in Sybase ASE, it's not a server-level setting (if it was a server-level setting you'd be able to alter it via sp_configure). Can you run wireshark (or something similar) on the packets being sent from the JDBC client to see if it's setting showplan on as part of the session initialization?
That said - showing the plan should not affect database CPU or memory usage, the plan is generated by the query optimiser anyway. However, showing the plan will increase network utilization.
Sybase ASE will not allow you to directly delete rows from multiple tables using a wild card, but it's pretty simple to create a script to find the table names, and loop through them.
You can find the tables names by querying sysobjects within the database.
To find all the tables with the prefix Table_ you would do:
select name from myDatabase..sysobjects
where type = "U"
and name like "Table\_%" escape "\"
In the above query "U" is the flag for User defined tables. Also because _ is a single character wild card in T-SQL, we have to escape it to find the literal _. T-SQL allows almost any character to act as the escape, but it must be defined after the like clause. Finally % is the multi-character wild card, the T-SQL equivalent to * in *nix and DOS.
It would also be possible to use the above query to build a sql script. We start by telling the server not to print out the number of rows affected by the query using set nocount. This allows us to create a script that will run error-free, without needing additional editing.
set nocount on \\supressess rows affected printout
select "delete from " +name+ " where type = 1" + char(10) + "go"
from myDatabase..sysobjects
where type = "U"
and name like "Table\_%" escape "\"
go
If you execute this from isql and using the -b flag to supress headers, and -o flag to redirect output to a file, it will build a script that can then be executed directly.
isql -Uusername -Ppassword -Sservername -b -omyScriptToDeleteData.sql -iSQLToFindTables.sql
Best Answer
Assumptions:
decryptpermissions to see the actual data (as opposed to database-level encryption which merely encrypts the data out on disk)ASE encrypted data is replicated as varbinary; this means that at no time is the data decrypted and/or re-encrypted; of course, it also means you need a copy of the encryption key in the RDS.
NOTE: If using a MSA or Warm Standby setup you can replicate the encryption key(s) to the RDB.
NOTE: If you have the wrong encryption key in the RDS, that
varbinary(X)data will not be decryptable ("Duh, Mark!" ?).If you're creating explicit repdefs for the table then you'll need to designate the encrypted column's datatype as
varbinary(X), whereXis the value you'll find in the associated (PDB)syscolumns.encrlencolumn.The default function string class is already configured with a function/function-string that supports replication of encrypted data; this basically consists of issuing
set ciphertext onwhen the DSI connects to the RDB, thus allowing thatvarbinary(X)data to be written directly into an encrypted column.Permissions to write the
varbinary(X)data into the RDB table should be the same as for any write operation into the table.As for replication of encrypted data in a heterogenous environment ... I'm about 98% sure this isn't possible if simply because each RDBMS product, if it supports some sort of column-level encryption, uses different encryption methods and keys. [I'd have to peruse the latest SRS 16.x manuals if this has changed with recent versions of the product.]