This is day 4 of my training and today it is going to focus on Bulk Copy, Automatic Recovery, Checking and Fixing Database Consistency

Bulk Copy

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%

Or:

ddlgen -Ulogin -Ppassword -TKC -N%.%.PK% -Ddbname

BCP

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

Automatic Recovery

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

sp_dbrecovery_order

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 checktable

dbcc checkalloc, this one will call these 3

….dbcc tablealloc

….dbcc indexalloc

….dbcc textalloc

dbcc checkcatalog, this one checks the catalogs

dbcc checkstorage

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

tablealloc

checks allocation information for the specified table.

textalloc

checks allocation information in text pages for the specified table.

indexalloc

checks allocation information for the specified index.

checkalloc

runs the same checks as tablealloc, for all pages in a database.

checkcatalog

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.

checktable

checks the integrity of data and index pages in the specified table.

checkdb

runs the same checks as checktable, for all tables in a database.

checkstorage

combines some of the checks of the above commands, and provides additional checks.

reindex

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

dbcc page

dbcc pglinkage

dbcc log

dbcc traceflags

dbcc traceon

dbcc traceoff

dbcc memusage

You can give dbcc permissions to a user (but why would you..as an admin, you should be running this)

grant dbcc to user

and

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