Sybase

2011-06-21T14:10:04Z

Jktu:

=Sybase=

==Поленые ф-и==
Информация по базе
<PRE>
sp_helpdb db_name
</PRE>
Место в базе:
<PRE>
sp_spaceused
</PRE>

<PRE>
sp_helpdevice
</PRE>
See also
Commands
create database, disk init, drop database, load database
System procedures
sp_addsegment, sp_dropsegment, sp_helpdb, sp_helpsegment, sp_logdevice, sp_renamedb, sp_spaceused

Создать "утройство" (файл или утройство для данных)
<PRE>
disk init
name = "cmm_db_data_2",
physname = "/sybase_devices/cmm_db_data_2.dat",
size = "5000M"
</PRE>

Добавить это устройство к базе.
<PRE>
ALTER database cmm_db on cmm_db_data_2 = "5000M"
</PRE>
Размеры устройств:
<PRE>
select
t1.name,
t1.phyname,
t1.status,
t2.lstart,
t2.size,
t2.unreservedpgs
from
master..sysdevices t1
inner join
master..sysusages t2 on t1.vdevno = t2.vdevno
--where
--dbid =db_id('audit_db')
order by 1,2,4
</PRE>
Изменение размера устройства:
<PRE>
disk resize name='data_device',
size='10G',
skip_alloc=false
</PRE>

==Размеры таблиц==
<PRE>

CREATE PROCEDURE dbo.CalcFreeSpace
AS
BEGIN
DECLARE @CurrentTableName NVARCHAR(100)

CREATE TABLE #TableList
(TableName NVARCHAR(100)
, RowsCount INT
, KBReserved VARCHAR(15)
, KBData VARCHAR(15)
, KBIndex VARCHAR(15)
, KBUnused VARCHAR(15)
)

-- Run a cursor through all of the tables
DECLARE result_cursor CURSOR FOR
SELECT name FROM dbo.sysobjects WHERE type = 'U'

OPEN result_cursor
FETCH NEXT FROM result_cursor INTO @CurrentTableName
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_spaceused @CurrentTableName
FETCH NEXT FROM result_cursor INTO @CurrentTableName
END
--end loop

--clean up
CLOSE result_cursor
DEALLOCATE result_cursor

--UPDATE #TableList
-- SET KBReserved = REPLACE(KBReserved, ' KB', '')
-- , KBData = REPLACE(KBData, ' KB', '')
-- , KBIndex = REPLACE(KBIndex, ' KB', '')
-- , KBUnused = REPLACE(KBUnused, ' KB', '')

/* Adaptive Server has expanded all '*' elements in the following statement */
SELECT
#TableList.TableName, #TableList.RowsCount, #TableList.KBReserved, #TableList.KBData, #TableList.KBIndex, #TableList.KBUnused
FROM
#TableList
ORDER BY TableName
DROP TABLE #TableList
END
</PRE>

==Дамп и восстановление==
Для дампа и восстановления ТАБЛИЦ используется утилита bcp .
<BR> Подробнее почитать тут http://manuals.sybase.com/onlinebooks/group-as/asg1250e/util/@Generic__BookTextView/10086;pt=8630/*

<BR>
Для дампа базы целиком используется isql:
<PRE>
isql
1> dump database cmm_db
2> to "./cmm_db.dump"
3> go
Backup Server session id is: 70. Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /DBAPP1/sybase/./cmm_db.dump.
Backup Server: 6.28.1.1: Dumpfile name 'cmm_db111080E7CC ' section number 1
mounted on disk file '/DBAPP1/sybase/./cmm_db.dump'
Backup Server: 4.188.1.1: Database cmm_db: 105464 kilobytes (1%) DUMPED.
...
Backup Server: 4.188.1.1: Database cmm_db: 8601706 kilobytes (73%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database cmm_db: 8663424 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database cmm_db).
</PRE>

При этом дамп будет сохранен на сервере БД а не на том хосте где запускался isql. Обратить внимание на права файла, ина компрессию (если с местом проблема.)

Для загрузки, соответвенно
<PRE>
load database test
from "/path/to/dump/test.dump"
</PRE>
Если ДАМП файлов несколько, то
<PRE>
load database test
from "/path/to/dump/test.dump"
stripe on "/path/to/dump/test01.dump"
stripe on "/path/to/dump/test02.dump"
</PRE>

==Размеры объектов (исправить==
<PRE>
select o.name name,
sum(convert(int, rowcnt(i.doampg))) rownum,
sum(convert(int, reserved_pgs(i.id, i.doampg) +
reserved_pgs(i.id, i.ioampg)) *
(d.low / 1024)) reserved_kb,
sum(convert(int, data_pgs(i.id, i.doampg)) * (d.low / 1024)) data_kb,
sum(convert(int, data_pgs(i.id, i.ioampg)) * (d.low / 1024)) index_kb,
sum(convert(int, reserved_pgs(i.id, i.doampg) +
reserved_pgs(i.id, i.ioampg) -
data_pgs(i.id, i.doampg) - data_pgs(i.id, i.ioampg))
* (d.low / 1024)) unused_kb
from sysobjects o, sysindexes i, master.dbo.spt_values d
where i.id = o.id
and d.number = 1 and d.type = "E"
group by o.name
order by reserved_kb desc
</PRE>

==Еще один способ дама - кросс-платформенный==
currently i have a privilege to work with sybase 12.5. perhaps i am spoiled with the ease of mysqldump db_name [tables] or echo .dump [tables] | sqlite3, but i expect any modern database to have a scriptable way to dump schema for selected tables in create statements, as well as data in insert statements that simply could be piped back when needed.

while dumping data is easy enough using bcp, scriptable schema extracts are a bit trickier (especially if you want it to be cross-platform).

but we are lucky – it took sybase only 20+ years to introduce a command-line utility written in java called ddlgen in version 15 of its flagship enterprise product (in my case it worked against 12.5 as well).
ddlgen

<PRE>
$ ls -lR c:/programs/sybase/ddlgen/
c:/programs/sybase/ddlgen/:
ddlgen.sh
lib/
</PRE>
<PRE>
c:/programs/sybase/ddlgen/lib:
DDLGen.jar
dsparser.jar
jconn3.jar
</PRE>
rig up the wrapper script:
<PRE>
$ cat c:/programs/sybase/ddlgen/ddlgen.sh
JAVA_HOME=c:/programs/java/jdk/jdk1.6.0_03
LIB_DIR=`dirname $0`/lib
CLASSPATH=$LIB_DIR/jconn3.jar:$LIB_DIR/dsparser.jar:$LIB_DIR/DDLGen.jar

$JAVA_HOME/bin/java \
-mx500m \
-classpath `cygpath --mixed --path $CLASSPATH` \
com.sybase.ddlgen.DDLGenerator $*
</PRE>

backup scripts

schema-out.sh
<PRE>
source env.sh

[ ! -d $OUT_DIR ] && mkdir -p $OUT_DIR

for table in $TABLES; do
out_file=`cygpath --mixed --absolute $OUT_DIR/${table}-schema.txt`
printf "dumping $table schema to $out_file... "
$DDLGEN -U $USERNAME -P $PASSWORD -S $SERVER:$PORT -D $DATABASE -TU -N$table -O $out_file
printf "done\n"
done
</PRE>
bcp-out.sh

<PRE>
source env.sh

[ ! -d $OUT_DIR ] && mkdir -p $OUT_DIR

LOG=`dirname $0`/bcp-out.log
cat /dev/null > $LOG

for table in $TABLES; do
out_file=`cygpath --mixed --absolute $OUT_DIR/${table}-bcp.txt`
printf "dumping $table to $out_file... "
bcp $DATABASE.dbo.$table out $out_file -c -t, -S $SERVER -U $USERNAME -P $PASSWORD >> $LOG
printf "done\n"
done
</PRE>

schema-in.sh
<PRE>
source env.sh

LOG=`dirname $0`/schema-in.log
cat /dev/null > $LOG

for table in $TABLES; do
in_file=`cygpath --mixed --absolute $OUT_DIR/${table}-schema.txt`
[ ! -f $in_file ] && echo "$in_file does not exist for $table, skipping" && continue
printf "loading $table schema from $in_file... "
isql -S$SERVER -U$USERNAME -P$PASSWORD < $in_file >> $LOG
printf "done\n"
done
</PRE>
bcp-in.sh
<PRE>
source env.sh

[ ! -d $OUT_DIR ] && mkdir -p $OUT_DIR

LOG=`dirname $0`/bcp-in.log
cat /dev/null > $LOG

for table in $TABLES; do
in_file=`cygpath --mixed --absolute $OUT_DIR/${table}-bcp.txt`
[ ! -f $in_file ] && echo "$in_file does not exist for $table, skipping" && continue
printf "loading $table from $in_file... "
bcp $DATABASE.dbo.$table in $in_file -c -t, -S $SERVER -U $USERNAME -P $PASSWORD >> $LOG
printf "done\n"
done
</PRE>

==Ссылки==
* http://blog.sybdoc.com/

noname.com.ua - Вклад [ru]

Sybase