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@sybase.com

2. User Tempdbs in ASE “What’s yours is yours, what’s 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 • Can’t 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 group1 tdb2 tdb3 group2 tdb4 tdb3 tdb4 group3 tdb5 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" <groupname> sp_tempdb "drop" <groupname> • “default” group can not be dropped

13. Sp_tempdb… sp_tempdb "add", <tempdb name>, <groupname> sp_tempdb "remove", <tempdb name>, <groupname> • <tempdb name> must be a temp db.

14. Sp_tempdb… sp_tempdb "bind", <obj_type>, <obj_name>, <bind_type>, <bind_obj>, <scope>, <hardness> sp_tempdb "unbind", <obj_type> , <obj_name>, <scope> • <obj_type> is “LG” or “AP” (or full name equivalents) • <bind_type> is “GR” or “DB” (or full name equival..) • system tempdb can’t be explicitly bound to • <scope> can be NULL • <hardness> is hard or soft. Default is soft.

15. Sp_tempdb… sp_tempdb "show" [,"all" | "gr" | "db" | "login" | "app"] [,<name>] sp_tempdb “who”, <tempdb name> • “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 • @@tempdbid stores a session’s assigned temporary database. • If a session is assigned to mytempdb then “select db_name(@@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 @@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 • Can’t drop if bindings exist—remove bindings • Can’t 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 involved…wouldn’t be able to recover otherwise • Resource Governor limits continue to hold • Can not load into a temporary database • Large scale deallocation not logged • Can’t 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