Sybase ASE 15.5 利用自带scripts脚本创建演示数据库pubs2

脚本路径在$SYBASE/$SYBASE_ASE路径下，数据库默认在pubs在default设备上，初始大小4～28不等，如脚本31到38行所示：

  1: /* %Z% generic/sproc/%M% %I% %G% */
  2: 
  3: /*
  4: ** raiserror Messages for installpubs2 [Total 3]
  5: **
  6: ** 19527, "The '%1!' database cannot be created. Terminating the installation."
  7: ** 19528, "The '%1!' database does not exist. Terminating the installation."
  8: ** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
  9: */
 10: 
 11: /* 
 12: ** 4/12/98 added 'on default = 3' to CREATE DATABASE 
 13: **         moved CREATE INDEX statements after INSERTs
 14: */ 
 15: 
 16: /* 7/17/98 added use master at start, use pubs2 at end */
 17: /* installpubs2 1.0 4/5/91 */
 18: /* has primary and foreign keys, plus text and image tables*/
 19: use master
 20: go
 21: set nocount on
 22: 
 23: if exists (select * from master.dbo.sysdatabases
 24:     where name = "pubs2")
 25: begin
 26:   drop database pubs2
 27: end
 28: go
 29: print 'Creating the "pubs2" database'
 30: 
 31: if (@@maxpagesize = 1024 * 2)
 32:   create database pubs2 on default =  4
 33: else if (@@maxpagesize = 1024 * 4)
 34:   create database pubs2 on default =  7
 35: else if (@@maxpagesize = 1024 * 8)
 36:   create database pubs2 on default = 14
 37: else if (@@maxpagesize = 1024 * 16)
 38:   create database pubs2 on default = 28
 39: else
 40:   create database pubs2 on default
 41: go
 42: 
 43: if not exists (select * from master.dbo.sysdatabases 
 44:     where name = "pubs2") 
 45: begin 
 46:   /*
 47:   ** 19527, "The '%1!' database cannot be created. Terminating the installation."
 48:   */
 49:   raiserror 19527, "pubs2"
 50:   select syb_quit()
 51: end
 52: go
 53: 
 54: set dateformat mdy
 55: go
 56: sp_dboption pubs2, "trunc log on chkpt", true
 57: go
 58: use pubs2
 59: go
 60: if (db_name() != "pubs2")
 61: begin
 62:   /*
 63:   ** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
 64:   */
 65:   raiserror 19529, "pubs2"
 66:   select syb_quit()
 67: end
 68: go
 69: checkpoint
 70: go
 71: if exists (select * from master.dbo.sysdatabases
 72:     where name = "pubs2")
 73: begin
 74:   execute sp_addtype id, "varchar(11)", "not null"
 75:   execute sp_addtype tid, "varchar(6)", "not null"
 76: end
 77: else
 78: begin
 79:   /*
 80:   ** 19528, "The '%1!' database does not exist. Terminating the installation."
 81:   */
 82:   raiserror 19528, "pubs2"
 83:   select syb_quit()
 84: end
 85: go
 86: if exists (select * from master.dbo.sysdatabases
 87:     where name = "pubs2")
 88: begin
 89:   create table authors
 

在实际生产中我们一般不会把生产库放在master设备上，于是乎先建立专门用户数据段的设备和日志段的设备：

用shell>isql -U sa -P <password> -S <服务器名>  登录后执行如下命令

 

use master
go
disk init  name  = 'pubs2data',
physname  = '/db2bodb1/sybasedata/pubs2data.dat',
size  = '1024M', dsync = true, skip_alloc = true
go 
use master
go
disk init  name  = 'pubs2log',
physname  = '/db2bodb1/sybaselog/pubs2log.dat',
size  = '512M', dsync = true, skip_alloc = true
go 

创建了两个设备，传建完成后，库默认创建在default设备上，修改下创建在自己自定义的数据设备和日志设备上：

  1: /* %Z% generic/sproc/%M% %I% %G% */
  2: 
  3: /*
  4: ** raiserror Messages for installpubs2 [Total 3]
  5: **
  6: ** 19527, "The '%1!' database cannot be created. Terminating the installation."
  7: ** 19528, "The '%1!' database does not exist. Terminating the installation."
  8: ** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
  9: */
 10: 
 11: /* 
 12: ** 4/12/98 added 'on default = 3' to CREATE DATABASE 
 13: **         moved CREATE INDEX statements after INSERTs
 14: */ 
 15: 
 16: /* 7/17/98 added use master at start, use pubs2 at end */
 17: /* installpubs2 1.0 4/5/91 */
 18: /* has primary and foreign keys, plus text and image tables*/
 19: use master
 20: go
 21: set nocount on
 22: 
 23: if exists (select * from master.dbo.sysdatabases
 24: where name = "pubs2")
 25: begin
 26: drop database pubs2
 27: end
 28: go
 29: print 'Creating the "pubs2" database'
 30: 
 31: if (@@maxpagesize = 1024 * 2)
 32: create database pubs2 on pubs2data = 1024 log on pubs2log=512
 33: else if (@@maxpagesize = 1024 * 4)
 34: create database pubs2 on pubs2data = 1024 log on pubs2log=512
 35: else if (@@maxpagesize = 1024 * 8)
 36: create database pubs2 on pubs2data = 1024 log on pubs2log=512
 37: else if (@@maxpagesize = 1024 * 16)
 38: create database pubs2 on pubs2data = 1024 log on pubs2log=512
 39: else
 40: create database pubs2 on pubs2data = 1024 log on pubs2log=512
 41: go
 42: 
 43: if not exists (select * from master.dbo.sysdatabases 
 44: where name = "pubs2") 
 45: begin 
 46: /*
 47: ** 19527, "The '%1!' database cannot be created. Terminating the installation."
 48: */
 49: raiserror 19527, "pubs2"
 50: select syb_quit()
 51: end
 52: go
 53: 
 54: set dateformat mdy
 55: go
 56: sp_dboption pubs2, "trunc log on chkpt", true
 57: go
 58: use pubs2
 59: go
 60: if (db_name() != "pubs2")
 61: begin
 62: /*
 63: ** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
 64: */
 65: raiserror 19529, "pubs2"
 66: select syb_quit()
 67: end
 68: go
 69: checkpoint
 70: go
 71: if exists (select * from master.dbo.sysdatabases
 72: where name = "pubs2")
 73: begin
 74: execute sp_addtype id, "varchar(11)", "not null"
 75: execute sp_addtype tid, "varchar(6)", "not null"
 76: end
 77: else
 78: begin
 79: /*
 80: ** 19528, "The '%1!' database does not exist. Terminating the installation."
 81: */
 82: raiserror 19528, "pubs2"
 83: select syb_quit()
 84: end
 85: go
 86: if exists (select * from master.dbo.sysdatabases
 87: where name = "pubs2")
 88: begin
 89: create table authors

利用修改后的脚本传建数据库

shell> cd $SYBASE/$SYBASE_ASE/scripts

shell>isql -U sa -P <password> -S <服务器名> -i installpubs2，创建成功，如日志。

-bash-3.00$ isql -U sa -P ××××× -S ×××××××  -i installpubs2
Creating the "pubs2" database
CREATE DATABASE: allocating 65536 logical pages (1024.0 megabytes) on disk
'pubs2data' (65536 logical pages requested).
CREATE DATABASE: allocating 32768 logical pages (512.0 megabytes) on disk
'pubs2log' (32768 logical pages requested).
Processed 39 allocation unit(s) out of 384 units (allocation page 68096). 10%
completed.
Processed 77 allocation unit(s) out of 384 units (allocation page 13568). 20%
completed.
Processed 116 allocation unit(s) out of 384 units (allocation page 20224). 30%
completed.
Processed 154 allocation unit(s) out of 384 units (allocation page 26624). 40%
completed.
Processed 192 allocation unit(s) out of 384 units (allocation page 81152). 50%
completed.
Processed 231 allocation unit(s) out of 384 units (allocation page 84480). 60%
completed.
Processed 269 allocation unit(s) out of 384 units (allocation page 46336). 70%
completed.
Processed 308 allocation unit(s) out of 384 units (allocation page 52992). 80%
completed.
Processed 346 allocation unit(s) out of 384 units (allocation page 59392). 90%
completed.
Processed 384 allocation unit(s) out of 384 units (allocation page 98048). 100%
completed.
00:00:00000:00023:2015/11/23 11:05:25.94 server  Timestamp for database 'pubs2' is (0x0000, 0x00001366).
Database 'pubs2' is now online.
Database option 'trunc log on chkpt' turned ON for database 'pubs2'.
Running CHECKPOINT on database 'pubs2' for option 'trunc log on chkpt' to take
effect.
(return status = 0)
Type added.
(return status = 0)
Type added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
Default bound to column.
(return status = 0)
Default bound to column.
(return status = 0)
Default bound to column.
(return status = 0)
Rule bound to table column.
(return status = 0)
Rule bound to table column.
(return status = 0)
Rule bound to table column.
(return status = 0)
(return status = 0)
-bash-3.00$ 

还可以用下面的脚本插入，带图像的数据：

-bash-3.00$ isql -U sa -P ×××× -S ×××× -i installpix2 
Database option 'select into/bulkcopy/pllsort' turned ON for database 'pubs2'.
Running CHECKPOINT on database 'pubs2' for option 'select into/bulkcopy/pllsort'
to take effect.
(return status = 0)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
Return parameters:
 txts               
 ------------------ 
 0x0000000000002094 
(1 row affected)
(1 row affected)
Return parameters:
 txts               
 ------------------ 
 0x00000000000020a6 
(1 row affected)
(1 row affected)
Return parameters:
 txts               
 ------------------ 
 0x00000000000020b7 
(1 row affected)
(1 row affected)
Return parameters:
 txts               
 ------------------ 
 0x00000000000020cb 
(1 row affected)
(1 row affected)
Return parameters:
 txts               
 ------------------ 
 0x00000000000020dc 
(1 row affected)
(1 row affected)
Return parameters:
 txts               
 ------------------ 
 0x00000000000020f3 
Database option 'select into/bulkcopy/pllsort' turned OFF for database 'pubs2'.
Running CHECKPOINT on database 'pubs2' for option 'select into/bulkcopy/pllsort'
to take effect.
(return status = 0)
 
The script installpix is complete.
No further action on your part is required.
-bash-3.00$ 

over！

参考文章：Sybase官网初始pubs2设备