E118 User Created Tempdbs in Adaptive Server Enterprise

1
E118User Created Tempdbs in Adaptive Server
Enterprise

• Raj Rathee
• Senior Software Engineer
• Enterprise Software Div., Sybase
• raj.rathee_at_sybase.com

2
User Tempdbs in ASE
Whats yours is yours, whats mine is mine. But
neither you, nor I, will get to keep it forever.
These are mere transient souls in the ASE
universe. Thus spoke the wise one.
3
Before User Created Tempdbs

• tempdb is the only temporary database
• Cant be dropped
• create table t1(a int, b int) creates a
  table in tempdb with system generated name
• select a, b from testtable order by a would
  create a work table in tempdb to do sort
• All tables, work tables go to tempdb

4
The Problem

• Catalog Contention in tempdb One solution is row
  level locked system catalogs
• but also other desirable features
• Temporary database on fast access device
• Dropping of a temporary database
• Consolidation of servers -- ASPs

5
The Solution

• User created temporary databases
• Similar in purpose and function to system tempdb
  tables, work tables (shareable temp tables too )
• Logins and/or Applications get assigned to a
  temporary database at login time. Remain assigned
  to the database for session duration.
• Temporary objects get created in assigned
  database

6
The Temporary Database World
Temporary DB Groups and Members
Temporary
Databases
tempdb
default
tempdb
tdb1
tdb6
tdb1
tdb2
group1
tdb3
group2
tdb4
tdb3
tdb4
tdb5
group3
tdb6
tdb4
tdb6
In Memory Representation
7
Groups And Bindings
Default Group (contains TEMPDB)
SA
Sales App
SalesTempDB
Sales Manager
Mktg Group
Mktg Manager
MarketingApp
8
The New Feature Interface

• The New Feature Interface

9
Create Temporary Database

• create temporary database . used to create a
  temporary database
• Like normal user database, but flag to indicate
  temporary nature
• Select into/bulkcopy is set
• Trunc log on chkpt is set
• guest user is added
• create table permission granted to public

10
Tempdb Groups

• Group is a set of zero or more temporary
  databases
• default group always exists with system tempdb
  as member.
• Other groups are user created
• Round robin policy used to pick candidate db from
  within a group

11
Sp_tempdb Binding Interface

• Stored procedure sp_tempdb manages temporary
  databases
• Create or drop a group
• Add/remove a temporary db to/from a group
• Bind/unbind login and/or application to/from a
  group or temporary db.

12
Sp_tempdb

• sp_tempdb "create" ltgroupnamegt
• sp_tempdb "drop" ltgroupnamegt

• default group can not be dropped

13
Sp_tempdb
sp_tempdb "add", lttempdb namegt,
ltgroupnamegt sp_tempdb "remove", lttempdb namegt,
ltgroupnamegt

• lttempdb namegt must be a temp db.

14
Sp_tempdb
sp_tempdb "bind", ltobj_typegt, ltobj_namegt,
ltbind_typegt, ltbind_objgt, ltscopegt,
lthardnessgt sp_tempdb "unbind", ltobj_typegt ,
ltobj_namegt, ltscopegt

• ltobj_typegt is LG or AP (or full name
  equivalents)
• ltbind_typegt is GR or DB (or full name
  equival..)
• system tempdb cant be explicitly bound to
• ltscopegt can be NULL
• lthardnessgt is hard or soft. Default is soft.

15
Sp_tempdb

• sp_tempdb "show" ,"all" "gr" "db" "login"
  
  "app" ,ltnamegt
• sp_tempdb who, lttempdb namegt

• show displays groups and bindings information
• who is used to report active sessions assigned
  to a given temporary database

16
Tempdb To Session Assignment

• At login time session assigned to a temporary db.
• Assignment in effect for session duration
• Bindings stored in SYSATTRIBUTES
• Soft bindings never fail. In error cases system
  tempdb will be assigned. Failures in case of hard
  bindings will result in failure to login.
• Algorithm to determine temporary db assignment
  is

17
Tempdb To Session Assignment

• if binding of type "LG" or AP exists for login
  id and application, use that binding, else,
• if binding of type "LG" exists for login, then
  use that binding, else,
• if binding of type "AP" exists for application
  name then use that binding, else
• assign session to a temporary database within the
  default group.

18
New Globals

• _at__at_tempdbid stores a sessions assigned temporary
  database.
• If a session is assigned to mytempdb then select
  db_name(_at__at_tempdbid) will return mytempdb.

19
New Built In

• tempdb_id(spid) returns the temporary database
  that spid is assigned to.
• select spid
• from master..sysprocesses
• where tempdb_id(spid) db_id(mytempdb)
• returns all spids bound to mytempdb.
• select tempdb_id() is eq to select _at__at_tempdbid

20
Restart And Recovery

• Similar to that of system tempdb
• Recreated afresh every time with model
• Recovery order same as for user created dbs
• Use sp_dbrecovery_order to change recovery order
• Early birds get assigned to a default database if
  specified one not recovered (unless a hard
  binding exists in which case login fails)

21
Dropping a tempdb

• Use drop database as usual
• Cant drop if bindings existremove bindings
• Cant drop if there are active sessions assigned
  to the temporary db being dropped - wait for
  sessions to terminate

22
Model DB and Tempdbs

• Model database copied to temporary dbs on
  recovery
• Model can not be larger than the smallest
  temporary database

23
Misc.

• Multi Database Transactions can not start from a
  temporary database unless only temporary
  databases involvedwouldnt be able to recover
  otherwise
• Resource Governor limits continue to hold
• Can not load into a temporary database
• Large scale deallocation not logged
• Cant be replicated
• Not mounted during failover

24
The Truth

• Only allow default group
• Only allow the sa login to be bound for now.
  Binding can not be hard.
• Login, App pair bindings not supported. First
  login, then application, then from default group