Sybase: различия между версиями

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску
 
(не показано 15 промежуточных версий 3 участников)
Строка 1: Строка 1:
  +
[[Категория:Sybase]]
 
=Sybase=
  
=Sybase=
   
Строка 6: Строка 7:
 
sp_helpdb db_name
  
sp_helpdb db_name
 
</PRE>
  
</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
  +
   
 
Создать "утройство" (файл или утройство для данных)
  
Создать "утройство" (файл или утройство для данных)
Строка 19: Строка 34:
 
ALTER database cmm_db on cmm_db_data_2 = "5000M"
  
ALTER database cmm_db on cmm_db_data_2 = "5000M"
 
</PRE>
  
</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>
   
 
==Размеры таблиц==
  
==Размеры таблиц==
Строка 73: Строка 109:
   
 
==Дамп и восстановление==
  
==Дамп и восстановление==
−
Для дампа и восстановления используется утилита bcp.
 +
Для дампа и восстановления ТАБЛИЦ используется утилита bcp .
 
<BR> Подробнее почитать тут http://manuals.sybase.com/onlinebooks/group-as/asg1250e/util/@Generic__BookTextView/10086;pt=8630/*
  
<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>
  +
  +
==ddlgen==
  +
Напишу ...
  +
==Ссылки==
  +
* http://blog.sybdoc.com/

Текущая версия на 17:00, 19 августа 2011

Sybase

Поленые ф-и

Информация по базе 

sp_helpdb db_name

Место в базе: 

sp_spaceused

sp_helpdevice

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


Создать "утройство" (файл или утройство для данных) 

disk init
   name = "cmm_db_data_2",
   physname = "/sybase_devices/cmm_db_data_2.dat",
   size = "5000M"

Добавить это устройство к базе. 

 
ALTER database cmm_db  on cmm_db_data_2 = "5000M"

Размеры устройств: 

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

Изменение размера устройства: 

disk resize name='data_device',
size='10G',
skip_alloc=false

Размеры таблиц


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

Дамп и восстановление

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


Для дампа базы целиком используется isql: 

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).

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

Для загрузки, соответвенно 

load database test
from "/path/to/dump/test.dump"

Если ДАМП файлов несколько, то 

load database test
from "/path/to/dump/test.dump"
stripe on "/path/to/dump/test01.dump"
stripe on "/path/to/dump/test02.dump"

Размеры объектов (исправить

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

Еще один способ дампа - кросс-платформенный

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 

$ ls -lR c:/programs/sybase/ddlgen/
c:/programs/sybase/ddlgen/:
    ddlgen.sh
    lib/

c:/programs/sybase/ddlgen/lib:
    DDLGen.jar
    dsparser.jar
    jconn3.jar

rig up the wrapper script: 

$ 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 $*


backup scripts

schema-out.sh 

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

bcp-out.sh 

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

schema-in.sh 

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

bcp-in.sh 

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

ddlgen

Напишу ...

Ссылки

Источник — https://noname.com.ua/mediawiki/index.php?title=Sybase&oldid=5010