存档
关于BCP工具的使用，介绍三个方面的东西
1.命令及参数；
2.出现的问题及解决方法；
3.分析bcp出来的文件结构。
----------------------------------------------------------------------------------------------------------
第一：BCP命令参数
bcp （version 11.0.x）
function：
Copies a database table to or from an operating system file in a userspecified format.
Syntax：
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n] [-c] [-t field_terminator] [-r row_terminator]
[-U username] [-P password] [-I sqlini_file]
[-A packet_size] [-J client_charset]
[-T text_or_image_size] [-E] [-N] [-X]
[-y sybase_dir]
Parameters：
database_name – is optional if the table being copied is in your default database. Otherwise, specify a database name.
owner – is optional if you or the Database Owner own the table being copied. If you do not specify an owner, bcp first looks for a table of that name owned by you. Then it looks for one owned by the Database Owner. If another user owns the table, you must specify the owner’s name or the command fails.
table_name – is the name of the database table to copy. The table name cannot be a Transact-SQL reserved word.
in | out – is the direction of the copy.
in
indicates a copy from a file into the database table;
out
indicates a copy to a file from the database table.
datafile – is the full path name of an operating system file. The path name can be from 1–255 characters in length.
-m max_errors – is the maximum number of nonfatal errors permitted before bcp aborts the copy. bcp discards each row that it cannot insert (due to a data conversion error, or an attempt to insert a null value into a column that does not allow them), counting eachrejected row as one error. If you do not include this option, bcp uses a default value of 10.
-e errfile – is the full path name of an error file where bcp stores any rows that it was unable to transfer from the file to the database.Error messages from the bcp program appear on your terminal. bcp creates an error file only when you specify this option. If you specify this option, and bcp does not encounter any nonfatal errors, it does not create the error file.
-F firstrow – is the number of the first row to copy (default is the first row).
-L lastrow – is the number of the last row to copy (default is the last row).
-n – performs the copy operation using native (operating system) formats. This option does not prompt for each field. Files in native data format are not human-readable.
-c – performs the copy operation with char datatype as the default.Use this format if you are sharing data between platforms. This option does not prompt for each field; it uses char as the default storage type, no prefixes, \t (tab) as the default field terminator, and \n (newline) as the default row terminator.
-t field_terminator – specifies the default field terminator.
-r row_terminator – specifies the default row terminator.
Note：
When specifying terminators from the command line with the -t or -r option, escape characters that have special significance to the Windows NT Command Prompt shell (see example 1 on page 1-11). Either place a backslash in front of the special character or enclose it in quotes. This is not necessary when bcp prompts you (interactive mode).
-U username – specifies a SQL Server login name. If you do not specify username, bcp uses value of the USERNAME environment variable (the current user’s operating system login name, not the Sybase user name and login).
-P password – specifies a SQL Server password. If you do not specify -P password, bcp prompts for a password. If your password is NULL, place the -P flag at the end of the command line by itself.
-I sqlini_file – specifies the name and location of the interfaces file (sql.ini) to search when connecting to SQL Server. If you do not specify -I, bcp looks for a file named sql.ini in the ini subdirectory of your Sybase release directory.
-S server – specifies the name of the SQL Server to connect to. If you specify -S with no argument, bcp uses the server specified by the DSQUERY environment variable.
-a display_charset – runs bcp from a terminal where the character set differs from that of the machine on which bcp is running. (See the System Administration Guide for more information about changing character sets.) -a in conjunction with -J specifies the character set translation file (.xlt file) required for the conversion. Use –a without -J only if the client character set is the same as the default character set.
-q datafile_charset – runs bcp to copy character data to or from a file system that uses a character set different from the client character set. -q in conjunction with -J specifies the character set translation file (.xlt file) required for the conversion. In Japanese language environments, the -q flag translates Hankaku Katakana (half-width characters) into Zenkaku Katakana (full-width characters). Use with the argument “zenkaku” and with the -J flag to indicate the client’s Japanese character set (sjis or eucjis). The zenkaku.xlt file was designed totranslate only from terminal display to SQL Server, not from SQL Server to the terminal.
Note：
The ascii_7 character set is compatible with all character sets. If either the SQL Server’s or client’s character set is set to ascii_7, any 7-bit ASCII character is allowed to pass between client and server unaltered. Other characters produce conversion errors. Character set conversion issues are covered more thoroughly in the System Administration Guide.
-v – displays the version number of bcp and a copyright message and returns to the operating system.
-A packet_size – specifies the network packet size to use for this bcp session. For example: bcp -A 2048 sets the packet size to 2048 bytes for this bcp session. size must be between the values of the default network packet size and max network packet size configuration parameters, one-third the size of the additional network memory configuration parameter, and a multiple of 512. To improve the performance of large bulk copy operations, use network packet sizes that are larger than the default.
-J client_charset – specifies the character set to use on the client. bcp uses a filter to convert input between client_charset and the SQL Server character set. -J client_charset requests that SQL Server convert to and from client_charset, the character set used on the client. -J with no argument sets character set conversion to NULL. No conversion takes place. Use this parameter if the client and server use the same character set.The default may not necessarily be the character set that the client is using. See the System Administration Guide for more information about character sets and the associated flags.
-T text_or_image_size – specifies in bytes the maximum length of text or image data that SQL Server sends. The default is 32K. If a text or image field is larger than the value of -T or the default, bcp does not send the overflow.
-E – explicitly specifies the value of a table’s IDENTITY column. By default, when you bulk copy data into a table with an IDENTITY column, the host file must contain a placeholder for the IDENTITY column (a value of 0 is recommended). The server assigns the row a unique, sequential IDENTITY column value, as bcp inserts each row into the table. If the number of inserted rows exceeds the maximum possible IDENTITY column value, SQL Server returns an error message. To use an explicit IDENTITY column value from the host file for each row, specify the -E flag when copying data into a table. The -E option has no effect on bulk copying out.
-y sybase_dir – specifies a Sybase directory other than the default Sybase release directory.
第二：出现的问题及解决方法
SQL SERVER DBCC命令解释
------------------------------------------
--1 dbcc trraceon DBCC TRACEOFF
--对于数据库死锁，通常可以通过TRACE FLAG 1204、1205、1206，检查ERRORLOG里面的输出，和分析SQLTRACE的执行上下文判断死锁问题的来由。
--TRACEON函数的第三个参数设置为-1，表示不单单针对当前connection，
--而是针对所有包括未来建立的connection。这样，才够完全，否则只是监视当前已经建立的数据库连接了。
--执行下面的话可以把死锁记录到Errorlog中：
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go
--说明：
--260：打印关于扩展存储过程动态链接库的版本信息
--444：停止auto-parameterization
--1200：输出锁信息
--1204：传回参与死锁的SQL SERVER相关程序之运行数据
--1211：停止lock escalation（锁升级）
--1252：显示动态选择锁的相关信息
--2528：通过 DBCC CHECKDB、DBCC CHECKFILEGROUP 和 DBCC CHECKTABLE 禁用对象的并行检查。
--默认情况下，并行度由查询处理器自动确定。最大并行度的配置方式与并行查询相同。
--有关更多信息，请参见 max degree of parallelism 选项。
-- 通常情况下，应将并行 DBCC 保留为启用状态。执行 DBCC CHECKDB 时，
-- 查询处理器重新评估和自动调整并行度，并检查每个表或一批表。
--有时，检查可能在服务器处于实际空闲状态时进行。如果管理员知道在检查结束前负荷将加大，
--可能希望手工减小或禁用并行度。
-- 但是，禁用并行检查会导致数据库的总体性能降低。降低并行度将增加必须扫描的事务日志量。
--这反过来增加了对 tempdb 空间的需求，并导致 dbcc 完成检查所需的时间非线性增加。
--如果运行 DBCC 时启用了 TABLOCK 功能并关闭了并行度，则表可能被锁定更长时间。
--3205：默认情况下，如果磁带驱动器支持硬件压缩，则 DUMP 或 BACKUP 语句会使用该功能。
-- 利用此跟踪标记，可以禁用磁带驱动程序的硬件压缩。
-- 本项在要与不支持压缩的其它站点或磁带驱动器交换磁带时有用。
--3604：将trace结果输出到前端
--3605：要求DBCC的输出放到SQL server ERROR LOG
--8602：停止索引提示功能
--8722：停止join group等最优化提示功能
--8755：停止锁提示功能
--8780：停止最优化超时配置，强制做完整的最优化动作
------------------------------------------------------
--2 DBCC page
dbcc traceon(3604)
dbcc page(northwind,1,100,1)
/*查询northwind 的数据的第101个页面的信息*/
/*DBCC Page ({dbid|dbname},filenum,pagenum[,printopt])
?
具体参数描述如下：
dbid: 包含页面的数据库ID
dbname:包含页面的数据库的名称
filenum:包含页面的文件编号
pagenum:文件内的页面
printopt:可选的输出选项；选用其中一个值：
0:默认值，输出缓冲区的标题和页面标题
1:输出缓冲区的标题、页面标题(分别输出每一行)，以及行偏移量表
2:输出缓冲区的标题、页面标题(整体输出页面)，以及行偏移量表
3:输出缓冲区的标题、页面标题(分别输出每一行)，以及行偏移量表；每一行后跟分别列出的它的列值
*/
------------------------------------------------------
--3 DBCC checkalloc
DBCC checkalloc（northwind)
/*检查指定数据库的系统表内和表间的一致性
checkalloc是检查指定数据库，看其所有正确分配的页和尚未分配的页的情况。
若未指定数据库名，则checkalloc检查当前数据库。checkalloc会返回已分配的和使用的空间数量。
checkalloc的缺省模式为nofix，要使用fix选项，必须把数据库置于单用户模式。
*/
-----------------------------------------------------
--4 DBCC checkcatalog
DBCC checkcatalog(northwind)
/*
检查批定数据库的系统表内和系统表间的一致性
*/
-----------------------------------------------------
--5 DBCC checkconstraints
DBCC checkconstraints(products)
/*
检查指定表上的指定约束或所有约束的完整性
DBCC CHECKCONSTRAINTS
[('table_name'|'constraint_name'
)]
[WITH {ALL_ERRORMSGS|ALL_CONSTRAINTS}]
DBCC CHECKCONSTRAINTS在某个数据库中，检测某些特定的约束或者全部约束的一致性。
DBCC CHECKCONSTRAINTS总是在当前数据库的上下文环境中执行。
注意，DBCC CHECKCONSTRAINTS并不进行磁盘或者文件级别的一致性检测;
它只是确保外键定义的一致性，同时检测约束——仅仅是确认数据有效。
如果你希望检测磁盘上表和索引的一致性，
你应该执行DBCC CHECKDB或者在所有的表上执行DBCC CHECKALLOC和 DBCC CHECKTABLE的组合。
*/
------------------------------------------------------
--6 DBCC checkdb
DBCC checkdb
/*
检查数据库中的所有对象的分配和结构完整性
checkdb [('database_name'[, NOINDEX | REPAIR])]
[WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY]
[, ESTIMATEONLY][, TABLOCK]]
*/
-----------------------------------------------------
--7 DBCC cleantable
DBCC cleantable
/*
回收alter table drop column语句 删除可变长度列或text列后的存储空间
cleantable ('database_name'|database_id, 'table_name'|table_id, [batch_size])
*/
-----------------------------------------------------
--8 DBCC dbreindex
DBCC dbreindex
/*
重建指定数据库的一个或多个索引
dbreindex ('table_name' [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]
*/
-----------------------------------------------------
--9 DBCC indexdefrag
DBCC indexdefrag
/*
对表或视图上的索引和非聚集索引进行碎片整理
indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid | indname})
*/
将Sybase临时数据库tempdb从master设备上移走的两种方法：
缺省情况下，tempdb数据库是放置在master 设备上，容量为2M，而临时数据库是活动最为平凡的数据库常常被用来排序、创建临时表、重格式化等操作，所以tempdb的优化应该受到特别的关注。本篇文章目的在于使你掌握临时数据库的优化策略以及临时表的优化使用。本文中，你将以调整临时库的位置开始，有步骤的完成临时数据库的优化，并在此过程中了解到优化临时数据库和临时表的一些方法和规则。
实验环境的要求：你应具有系统管理员的权限，系统中有auths和article表。
步骤：
第一步：调整临时库的位置
tempdb数据库缺省放在master设备上，将临时数据库发在分离的设备上是更可取的。
1） 初始化一个用来存放临时数据库的设备
disk init
name="tempdb_dev",
physname="d:\sybase\example\tempdb.dat",
vdevno=13,
size=15360
(注意：如果将tempdb数据库放在多个设备上，需初始化多个数据库设备)
2)将临时数据库扩展到该一个设备上
alter database tempdb on tempdb_dev=30
3)打开tempdb数据库，从段上删除master设备
sp_dropsegment "default",tempdb,master
sp_dropsegment logsegment,tempdb,master
4)发出如下命令，检查default段中是否不再包含master设备
select dbid,name,segmap from sysusages,sysdevices
where sysdevices.low<=syusages.size+vstart
and sysdevices.high>=sysusages.size+vstart-1
and dbid=2
and(status=2 or status=3)
说明：若将临时数据库放在多个磁盘设备上，可以更好的利用并行查询特性来提高查询性能。
第二步：将临时数据库与高速缓冲进行绑定。
由于临时表的创建、使用，临时数据库会频繁地使用数据缓存，所以应为临时数据库创建高速缓存，从而可以使其常驻内存并有助于分散I/O：
1、创建命名高速缓存
sp_cacheconfig “tempdb_cache”,”10m”,”mixed”
2、重新启动server
3、捆绑临时数据库到tempdb_cache高速缓存
sp_bindcache “tempdb_cache”, tempdb
4、若有大的I/O，配置内存池
第三步：优化临时表
大多数临时表的使用是简单的，很少需要优化。但需要对临时表进行复杂的访问则
应通过使用多个过程或批处理来把表的创建和索引分开。以下两种技术可以改善临时表的优化
slash; 在临时表上创建索引
1） 临时表必须存在
2） 统计页必须存在（即不能在空表上创建索引）
slash; 把对临时表的复杂的使用分散到多个批处理或过程中，以便为优化器提供信息
下面的这个过程需要进行优化：
create proc base_proc
as
select * into #huge_result from auths
select * from article, #huge_result where article.author_code=
#huge_result.author_code and sex=”0”
使用两个过程可以得到更好的性能
1）create proc base_proc
as
select *
into #huge_result
from auths
exec select_proc
2) create proc select_proc
as
select * from article,#huge_result
where article.author_code=#huge_result.author_code and sex=”0”
说明：在同一个存储过程或批处理中，创建并使用一个表时，查询优化器无法决定这个表的大小。
结论：通过本实验我们知道，临时数据库经过优化可以极大的提高系统性能。实际工作中，必须考虑具体应用的情况，需长时间经验的积累。
=======================================================================
第二种方法转自echoaix的博客，个人感觉方法可行。
tempdb如何真正使用自己的设备
大家知道tempdb缺省是在master设备上占用2兆，第一大小太小，第二占用master设备。方法都是先建一个新的设备，然后把tempdb扩到这个设备上，再从段上删除master设备
sp_dropsegment "default",tempdb,master
sp_dropsegment logsegment,tempdb,master
可是这样tempdb是扩大了，可是占用的master上的2兆好象并不能真正删掉（至少我没有成功）。
我的方法是：1建一个在独立设备上的数据库如testdb，查看 sysdatabases表找到对应dbid假如为9。
2删除sysdatabases和sysusages中tempdb的信息即dbid为2的记录。
3将sysdatabases中dbid为9的记录的dbid改为2，name改为tempdb，将sysusages中dbid为9的记录的dbid改为2。
4记住sp_dboption tempdb,"select into/bulkcopy" ,true，把新的tempdb的selectinto/bulkcopy属性打开
5重启
今天下午一直在研究master设备的内部数据结构，尝试通过编写程序向master设备文件后面追加数据来达到增加master设备大小的目的。
在sybase中初始化一块设备就是向操作系统中的文件系统请求一块空间，并且sybase对这块空间进行了一定的分配初始化。
要是能够手动向master设备文件尾部追加初始化的分配信息数据就可以物理上增加master设备的大小；然后，在 sysdatabases，sysdevices，sysusages表中修改相应的信息。
手动扩展master设备大小的示例：
原master设备大小为50MB，逻辑页号:0 - 25599(逻辑页面大小为2K)。
将master设备增加50MB空间，使得master设备空间变为100MB。
新的master设备逻辑页号: 0 - 51199。
1、修改master设备的high为51199,
update sysdevices set high=51200-1 where name='master'
go
2、给master设备文件尾部增加50MB空间
dd if=/dev/zero of=./master.dat bs=2048 seek=25600 count=25600
3、重启Sybase数据库服务器
4、验证master设备空间大小变为100MB
————————————————————————————————————
——— 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
——— 转载务必注明原始出处 : http://www.dbainfo.net
——— 关键字： ASE master 空间 扩展 手动
————————————————————————————————————
sybase的syslogs（id=8)表中仅有两个字段:xactid:事务ID和op：更新操作的次数（sybase中文文档中的翻译）！
我觉得op应该理解为日志操作的类型。每个OP代表什么含义呢？常用的日志类型有：更新、插入、删除，对应的OP分别为：9，4，5。
下面列出OP值及其对象的操作类型。
Values：meanings
0: Transaction begin 4: Data page insert 5: Data page delete 6: Indirect insert (from deferred insert/update) 7: Index page insert 8: Index page delete 9: Direct replace/update 10: No Op (skipped in recovery) 11: Logged in first phase of deferred insert/update 12: Logged in first phase of deferred delete/update 13: Page allocation 14: Next available object id for a creation 15: Generated by 4801 (?) 16: Page split 17: Checkpoint 18: Transaction save 19: Logged when any "update" begins 20: Sort page deallocations 21: Page deallocation 22: Delete all extents on allocation page 23: Allocate extent - mark all pages used 24: Allocation for new page of a split 25: Direct sysindexes change 26: Compensation log record 27: Sort record 28: SODEALLOC record 30: Transaction end 31: Sort timestamp record 32: Text insert - direct 33: Text insert - deferred 34: Text delete - deferred 35: Indirect insert (log record) 36: Text delete - log record 37: OAM page allocation 38: OAM page deallocation 39: OAM entry insertion 40: OAM entry deletion 41: OAM allocation during create/sort 42: No Op cmd record 43: Record for stored proc begin exec 44: Record for stored proc complete 45: Dump database sync 46: Truncation point save 47: Load database sync 48: Load transaction sync 49: Before/after image for replication 50: Reserved for Replication Server 51: Move OAM entries between OAM pages 52: Record attribute data on OAM 53: Deleted a column from a row 54: Deallocate a run of log pages 55: Data slice control page 56: Deferred insert of an index row 58: Table/procedure schema versioning (replication support) 59: Column/parameter schema versioning (replication support) 60: Prepare to commit 61: Start of a top action 62: End of a top action 63: Insert log record for data row of data-only locked table 64: Counterpart of XREC_INSIND for DOL table 65: Update log record for DOL table 66: Delete log record for data row of data-only locked table 67: Garbage collection log record for DOL table 68: Deferred update log record for DOL table 69: Page alloc without links to neighbors 70: Counterpart of XREC_DNOOP for DOL tables 71: Btree leaf/nonleaf insert 72: Btree leaf/nonleaf delete 73: Btree leaf/nonleaf split 74: Btree garbage collect leaf 75: System catalog rows for the source table of create index 76: End of sort log record 77: New log record from dropextents 82: dbtable structure change (X-DB commit dbcc pfts etc.)
ASE12.5.0.3新功能
一、性能监测系统表 一组新的系统性能监测系统表，利用这些系统表中的信息对分析ASE服务器的性能非常方便。这些性能监测系统表也是DB-XRAY的工作基础。 MDA表，安装配置文档中都有。这个很有用的。 二、多个临时库 能够让你在系统临 数据库tempdb外创建用户临时数据库。使用多个临时数据库的主要目的是减轻对系统临时数据库中系统表和日志表的争用。 三、外部文件系统 能够让你通过SQL语言访问外部文件系统，通过创建代理表将数据表映射到外部文件系统的文件目录或文件。 访问文件目录 访问文件 这个要licenses 四、Java和XML在DB中的应用 Adaptive Server内部提供了一个Java运行环境，这样就可在ASE中运行java代码。数据库服务器ASE就提供了一个新的方法来管理和存储数据及应用逻辑。 在ASE中可用下面三种方法使用java功能： 将java类用作SQL数据类型 在数据库中调用java方法 在数据库中存储和查询XML 五、新增强的功能和新函数 disk resize sampling : update statistics 可变页大小 large column size migrate utility ALS（asynchronous log service） 服务器动态参数配置 函数 year(date_expression) ：表达式中年的部分 month(date_expression) ：表达式中月份的部分 day(date_expression) ：表达式中天的部分 str_replace(string_expression1, string_expression2, string_expression3) ：使用string_expression3值替换 string_expression1中的string_expression2 字符串。 left(character_expression, integer_expression) ：返回字符串的左边指定字符个数的字符串。 len(string_expression) ：返回字符串的字符(character)数，注意不是字节数，而且不包含空隔。 square(numeric_expression) ：返回平方数 newid([optionflag]) ：产生一个32位或36位(包括破折号)的可读随机字符串。 六、SQL debugger ASE12.5提供一个命令行方式的debug工具sqldbgr，使用它可对存贮过程和触发器进行debug跟踪。
ASE 12.5.1新功能
我们知道根据生产数据库的业务数据情况来安排备份的频率，比如：每月、每周、每日，有的甚至每小时备份一次。要备份sybase数据库，需要自己手动写命令。这样，下一次可能会覆盖上一次的备份文件。通过脚本在下一次备份时产生不同的备份文件名称就不会覆盖上一个备份文件了。这样，能够自动保留数据库的多个数据库备份文件，更有力的保障了数据库的安全。
将下面的脚本复制到记事本中，另存为bat格式。添加到windows的计划任务中，
============================================================================================
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | echo off cls :: 默认保留近7日来的数据库备份，可以把7改成自己想连续的天数 :: 其中d:\sybase\data为数据库备份地址，可以自己再次指定 :: master为默认备份的数据库名称，可以修改成其它用户数据库名称 set databak_path=d:\sybase\data set databasename=master echo dt=date()-7>%temp%\OldDate.vbs echo s=right(year(dt),4) ^& "-" ^& right("0" ^& month(dt),2) ^& "-" ^& right("0" ^& day(dt),2)>>%temp%\OldDate.vbs echo wscript.echo s>>%temp%\OldDate.vbs for /f %%f in ('cscript /nologo %temp%\OldDate.vbs') do @set DelFlag=%%f ::删除七日前的数据库备份文件 if exist "%databak_path%\%databasename%_%DelFlag%.dump" del "%databak_path%\%databasename%_%DelFlag%.dump" del %temp%\OldDate.vbs if exist %temp%\dump_db.txt (del %temp%\dump_db.txt) echo dump database %databasename% to "%databak_path%\%databasename%_%date:~0,10%.dump" >>%temp%\dump_db.txt echo go >>%temp%\dump_db.txt isql -Usa -P -Syour_servername <"%temp%\dump_db.txt" >>%databak_path%\dump_log.txt del "%temp%\dump_db.txt" |
————————————————————————————————————
——— 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
——— 转载务必注明原始出处 : http://www.dbainfo.net
——— 关键字： ASE 数据库 备份 自动 连续备份 删除旧备份
————————————————————————————————————
从多个渠道收集来的sybase trace flag
With dbcc traceflags you can do many different things, like change the behaviour of the server, see why a certain query plan is choosen or fine-tune the optimizer.
This page shows you how to use them and provides a list of known traceflags.
You can set a traceflag in two ways
1.At boottime
Usually a server is booted through the runserver file. In this file (default location is $SYBASE/$SYBASE_ASE/install) you can add a traceflag by specifying a -T flag and one or more comma separated values.
2.At run time
When you are logged in to ASE you can set a traceflag with the dbcc traceon command. Example:
dbcc traceon(3604)
You can specify multiple values, comma separated.
To turn a traceflag off, use traceoff
dbcc traceoff(3604)
Which traceflags are active
To see which traceflags are active for your own session use the dbcc traceflags command
dbcc traceflags
To see the traceflags of other sessions as well
dbcc traceflags(2)
List of known traceflags
Below you will find a list of currently known traceflags. But beware of the following:
WARNING
? Most traceflags are undocumented and not supported by Sybase, use at your own risk. Certain traceflags can severely harm your databases.
? Some traceflags are obsolete and are replaced by configuration parameters.
? Certain traceflags should be used when you start the server, others are meant for a specific session, when known this is indicated in the list.
? Experiment first on a test server, use only on production systems when either the traceflag is documented by Sybase or when you have consulted Sybase Technical Support.
List of dbcc traceflags
-----------------------------------------------------------------------------------
100 Display a parse tree for each command
108 Allow dynamic and host variables in create view statements
116 Print the text of the abstract query plan. Supported by Sybase, see documentation
200 Display messages about the before image of the query-tree
201 Display messages about the after image of the query tree
208 Show types of locks taken
217 Display a warning message for using the T-SQL extension of queries with grouped aggregates and columns in the select list which are not in the GROUP BY clause.
241 Compress all query-trees when ASE is started
243 Do not expand select * to column names when creating a compiled object
244 When set, the maximum length of the returned value of str_replace() is 16384, otherwise it is 255
260 Reduce TDS (Tabular Data Stream) overhead in stored procedures. Turn off done-in-behaviour packets. See also Send doneinproc tokens.
291 Changes the hierarchy and casting of datatypes to pre-11.5.1 behaviour. There was an issue is some very rare cases where a wrong result could occur, but that's been cleared up in 11.9.2 and above.
292 Never send doneinproc tokens. See also Send doneinproc tokens.
298 Display an error message when a query uses a correlated variable as an inner or outer member of an outer join.
299 Do not recompile a stored procedure that inherits a temp table from a parent procedure.
302 Print trace information on index selection, supported by Sybase, see documentation
303 Display optimizer OR strategy
304 Revert special "or" optimizer strategy to the strategy used in pre-System 11 (this traceflag resolved several bug issues in System 11, most of these bugs are fixed in ASE 11.0.3.2)
310 Show the plan as choosen by the optimizer, as well as I/O costs. Supported by Sybase see documentation
311 Display optimizers expected I/O cost
317 Show all considered plans. Supported by Sybase, see documentation
319 Display optimizer reformatting strategy