This is day 4 of my training and today it is going to focus on Bulk Copy, Automatic Recovery, Checking and Fixing Database Consistency
Getting the DDL
The lesson started with showing how you can get the DDL for an object, you can use Sybase Central to do this or you can use the ddlgen Utility. Sybase Central is similar to SSMS, you right click on the object and select the Generate DDL option. The ddlgen utility is a Java based command line tool
Syntax looks like this
ddlgen -Ulogin -Ppassword -S[server | host_name : port_number] [-I interfaces_file] [-Tobject_type] [-Nobject_name] [-Ddbname] [-Xextended_object_type] [-Ooutput_file] [-Eerror_file] [-Lprogress_log_file] [-Jclient_charset] -F[ % | SGM | GRP | USR | R | D | UDD | U | V | P | XP | I | RI | KC | TR | PC ]
Here is what object_type (switch T) can be
Object type Description ------------ ------------------ C cache D default DB database DBD database device DPD dump device EC execution class EG engine group EK encrypted keys GRP group I index KC key constraints L login LK logical key P stored procedure R rule RI referential integrity RO role RS remote server SGM segment TR trigger U table UDD user-defined datatype USR user V view WS user-defined Web service WSC Web service consumer XP extended stored procedure
Here are two examples. Both of these generate DDL for the primary and unique keys of all the tables in a database that begin with “PK”:
ddlgen -Ulogin -Ppassword -TKC -Ndbname.%.%.PK%
ddlgen -Ulogin -Ppassword -TKC -N%.%.PK% -Ddbname
This should look familiar if you have used bcp on SQL Server. Bcp has two speeds in Sybase.
Fast bcp, better performance but no recovery, used when a table doesn’t have indexes or triggers (or triggers have been disabled), you also have to have the minimally-logged operations enabled on the database
Slow bcp, slower performance but has better recovery, used when a table has at least one indexes or trigger
You can use bcp to copy data into partitioned tables by executing in parallel with different files in different sessions
You need these options turned on
exec sp_dboption MyDb, "select into/bulkcopy/pllsort", true exec sp_dboption MyDb, "trunc log on chkpt", true
The bcp syntax is more or less the same as in SQL Server so I won’t go into any detail here.
One thing that bcp does not have in Sybase is the queryout, you can however create a view and do it that way
This was mostly about the log, dirty pages, when checkpoints occur, talking about the “trunc log on chkpt” option. Discussed was what happens when recovery kicks in, what happens to committed and uncommitted transaction, how to make recovery less long. Sybase doesn’t have the concept of fast recovery, the database is not available until all the transactions have been rolled forward or rolled back. There is a way to specify which databases you want recovered first after the system databases have been recovered, you do this with the sp_dbrecovery_order proc
Specifies the order in which user databases are recovered and lists the user-defined recovery order of a database or all databases.
sp_dbrecovery_order [database_name [, rec_order [, force [ relax | strict ]]]]
Also discussed were full and incremental backups.
Checking and Fixing Database Consistency
Sybase just like SQL Server has the dbcc (database consistency checker) command
Here are some of them and what they call under the hood
dbcc checkdb, this calls the following dbcc command for every table
dbcc checkalloc, this one will call these 3
dbcc checkcatalog, this one checks the catalogs
dbcc checkstorage stores the output in a database named dbccdb, you have to create the dbccdb database yourself and then run the installdbccdb script that will create the objects you need. After dbccdb is created you need to configure parallelism, configure data cache + large I/O(sp_cacheconfig and sp_poolconfig), you need to create workspaces, you need to run the stored procedure sp_dbbc_updateconfig to set some attributes and finally you need to run sp_dbcc_evaluatedb to make sure it is all configured correctly.
Here is a list of most of them
checks allocation information for the specified table.
checks allocation information in text pages for the specified table.
checks allocation information for the specified index.
runs the same checks as tablealloc, for all pages in a database.
checks for consistency in and between system tables. For example, checkcatalog makes sure that every type in syscolumns has a matching entry in systypes, that every table and view in sysobjects has at least one column in syscolumns, and that the last checkpoint in syslogs is valid. checkcatalog also reports on any segments that have been defined. If no database name is given, checkcatalog checks the current database.
checks the integrity of data and index pages in the specified table.
runs the same checks as checktable, for all tables in a database.
combines some of the checks of the above commands, and provides additional checks.
checks the integrity of indexes on user tables. prints a message when it finds the first index error and then drops/recreates the index.
Just so you know Sybase also has undocumented dbcc command, just like SQL Server
Here is a list of some of those
You can give dbcc permissions to a user (but why would you..as an admin, you should be running this)
grant dbcc to user
revoke dbcc from user
Finally if a database is completely FUBARed then you can use dbcc dbrepair to drop it, you can drop a suspect database with a regular DROP database command