在ASE 1251下如何建多个tempdb

faqbbs

在ASE 1251下如何建多个tempdb及其把它绑定到某个db。。。。。。。急


谢谢

冷月无声

不可以！

可以建高速缓存 在绑定到tempdb上

levinpeng

从Sybase12.5.1开始可以创建多个temp db, 并且添加一个sp_tempdb 存过程, 可以绑定数据库、应用程序等等。
如:
use master
go
create temporary database temp_db_test
on dev_tmp = 100
log on dev_tmp_log = 50
go

跟着可以有sp_temp进行绑定, 其sp_temp的语法及用法如下：
sp_tempdb
Description
sp_tempdb allows users to:


Create the default temporary database group

Bind temporary databases to the default temporary database group

Bind users and applications to the default temporary database group or to specific temporary databases


These bindings are stored in the sysattributes table in master database.

sp_tempdb provides the binding interface for maintaining bindings in sysattributes that are related to the multiple temporary database.

Syntax

sp_tempdb [
    [ { create | drop } , groupname ] |
    [ { add | remove } , tempdbname, groupname ] |
    [ { bind, objtype, objname, bindtype, bindobj [, scope, hardness ] } |
      { unbind, objtype, objname [, scope ] } ] |
    [ unbindall_db, tempdbname ] |
    [ show [, "all" | "gr" | "db" | "login" | "app" [, name ] ] |
    [ who, dbname ]
    [ help ]
]

Parameters
create

creates the default temporary database group.

drop

drops a database group.

groupname

is the default database group. Use "default".

add

adds temporary databases to the default temporary database group.

remove

removes temporary databases from the default temporary database group.

tempdbname

is the name of the temporary database you are adding or removing.

bind

binds logins and applications to temporary databases or the default temporary database group.

unbind

unbinds logins and applications to temporary databases or the default temporary database group.

objtype

is the object type. Valid values are:


login_name (or LG)

application_name (or AP)


Values are not case-sensitive.

objname

is the name of the object you bind or unbind.

bindtype

is the bind type. Valid values are:

group (or GR)

database (or DB)


Values are not case-sensitive.

bindobj

is the name of the object being bound, and is either a group or a database depending on the bindtype.

scope

NULL.

hardness

is hard, soft, or NULL. The default is soft.

When you set the value of hardness to hard, a failure to assign a temporary database according to the binding results in a failure of the login. When you set the value to soft, such a failure results in the assignment of the system tempdb.

unbindall_db

removes all login and application bindings for a given temporary database. It does not remove any database to group memberships. The tempdbname variable is required with this option.

Existing assignments to active sessions are not affected by this operation.

show

displays information stored in the sysattributes table about the existing groups, group members, login and application bindings, and active sessions that are assigned to a given database. The values are:

all or no argument - displays the default temporary database group, all database-to-group memberships, and all login and application bindings.

gr - displays the default temporary database group. sp_tempdb show displays all temporary databases bound to the default temporary database group whether you specify "default" for the name option or not.

db - displays all databases to group memberships. If you provide name, then only the database to group memberships for the database name are printed.

login - displays all login bindings where login is not NULL. If you provide name, then only the bindings for the login name are printed.

app - displays all bindings where the application is not NULL. If you provide name, then the bindings for the application name are printed.


tempdb is always part of the default database group.

who

displays all active sessions assigned to the given temporary database. When using the who parameter, you must use:

dbname - the name of a temporary database. If you provide a nontemporary database name for dbname, sp_tempdb who executes, but does not report any active sessions bound to it.


help

displays usage information. Executing sp_tempdb without specifying a command is the same as executing sp_tempdb "help".

Examples
Example 1

Adds mytempdb1 to the default group:


sp_tempdb add, mytempdb1, "default"

Example 2

Removes mytempdb1 from the default group:


sp_tempdb remove, mytempdb1, "default"

Example 3

Binds login "sa" to the default group:


sp_tempdb bind, lg, sa, GR, "default"

The value for objtype in this example is login_name. You can substitute login_name with lg or LG.

The value for bindtype in this example is group. You can substitute group with gr or GR.

Example 4

Changes the previous binding of login "sa" from the default group to mytempdb1:


sp_tempdb bind, lg, sa, DB, mytempdb1

The value for bindtype in this example is database. You can substitute database with db or DB.

Example 5

Binds isql to mytempdb1:


sp_tempdb bind, ap, isql, DB, mytempdb1

The value for objtype in this example is application_name. You can substitute application_name with ap or AP.

Example 6

Changes the previous binding of isql from mytempdb1 to the default group:


sp_tempdb bind, ap, isql, GR, "default"

Example 7

Removes the bindings of login "sa" and application "isql".


sp_tempdb unbind, lg, sa


sp_tempdb unbind, ap, isql

Example 8

Removes all login and application bindings for the mytempdb1 database:


sp_tempdb unbindall_db, mytempdb1

Example 9

Demonstrates the sp_temp show command. A selection of the different variations is chosen, and abbreviated sample output is displayed.


sp_tempdb show


Temporary Database Groups
-------------------------------
default

Database                        GroupName
------------------------------- ----------------
tempdb                          default
mytempdb                        default
mytempdb1                       default
mytempdb2                       default
mytempdb3                       default

Login   Application  Group    Database    Hardness
------- ------------- -------- ----------- --------
NULL   isql          default  NULL        SOFT
sa      NULL          NULL     mytempdb3   HARD

Example 10

Displays the default temporary database group:


sp_tempdb show, gr


Temporary Database Groups
-------------------------------
default

Example 11

Displays all the temporary database group names that are bound to the default group:


sp_tempdb show, gr, "default"Member Databases
-------------------------------
tempdb
mytempdb
mytempdb1
mytempdb2
mytempdb3

Example 12

Displays all the databases-to-group memberships:


sp_tempdb show, db


Database              Group
--------------------- ----------------
tempdb                default
mytempdb              default
mytempdb1             default
mytempdb2             default
mytempdb3             default

Example 13

Displays all the databases-to-group memberships for the mytempdb1 database.


sp_tempdb show, db, mytempdb1


Database              Group
--------------------- ----------------
mytempdb1             default

Example 14

Displays all the login bindings where login is not NULL:


sp_tempdb show, login


Login   Application   Group   Database    Hardness
------- ------------- ------- ----------- --------

sa      NULL          NULL    mytempdb3   HARD

Example 15

Displays all active sessions that are assigned to the system tempdb:


sp_tempdb who, tempdb


spid   loginame
------ ------------------------------
2      NULL
3      NULL
4      NULL
5      NULL
6      NULL
7      NULL
8      NULL

Example 16

Displays all active sessions that are assigned to the mytempdb3 user-created temporary database:


sp_tempdb who, mytempdb3


spid   loginame
------ ------------------------------
17      sa

Example 17

Displays usage information:


sp_tempdb help


Usage:
sp_tempdb 'help'
sp_tempdb 'create', <groupname>;
sp_tempdb 'drop', <groupname>;
sp_tempdb 'add', <tempdbname>;, <groupname>;
sp_tempdb 'remove', <tempdbname>;, <groupname>;
sp_tempdb 'bind', <objtype>;, <objname>;, <bindtype>;, <bindobj>;, <scope>;,
          <hardness>;
sp_tempdb 'unbind', <objtype>;, <objname>;, <scope>;
sp_tempdb 'unbindall_db', <tempdbname>;
sp_tempdb 'show', <command>;, <name>;
sp_tempdb 'who', <dbname>;

<objtype>; = ['LG' ('login_name') | 'AP' ('application_name')];
<bindtype>; =['GR' ('group') | 'DB' ('database')]
<hardness>; = ['hard' | 'soft']
<command>; = ['all' | 'gr' | 'db' | 'login' | 'app']

Usage
create and drop

When using the sp_tempdb create stored procedure, the groupname variable:


Must be a valid identifier

Cannot already exist


The default group is the system-generated group, of which tempdb is always a member. This default group is present if you:


Upgrade using the Adaptive Server containing this feature, or

Create a new master device.


If the default group is not present, you can create it by using:


sp_tempdb create, "default"

An error message displays if you attempt to create a default group that already exists.


add and remove

To add a temporary database to the default temporary database group, both the temporary database and the group name must already exist. When you use sp_tempdb add to add a tempdbname to a set of databases that are members of the default temporary database group, tempdbname becomes available for round-robin assignment from within that group.

sp_tempdb add fails if tempdbname is not already part of the global list of available temporary databases in Adaptive Server.

User-created temporary databases need not belong to the default temporary database group. The system tempdb is implicitly a member of the default group.

If you try to add a temporary database to the default temporary database group when it is already a part of that group, you get an error message, and no changes take place in sysattributes.


Permissions
By default, only the System Administrator or users with the SA role can execute sp_tempdb.