1 / 106

DM237 Assorted DBA tricks

DM237 Assorted DBA tricks. Rob Verschoor Sypron B.V. The Netherlands rob@sypron.nl. About myself… Consultant, specialised in Sybase ASE & RepServer Worked with Sybase since 1989 Located in The Netherlands (Europe) rob@sypron.nl http://www.euronet.nl/~syp_rob. Assorted DBA tricks.

darice
Download Presentation

DM237 Assorted DBA tricks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DM237Assorted DBA tricks • Rob Verschoor • Sypron B.V. • The Netherlands • rob@sypron.nl

  2. About myself… Consultant, specialised in Sybase ASE & RepServer Worked with Sybase since 1989 Located in The Netherlands (Europe) rob@sypron.nl http://www.euronet.nl/~syp_rob Assorted DBA tricks

  3. Topics: Tricks with identity columns Useful DBCC commands RepServer administration made easier Tricks with auditing Useful Java classes Assorted DBA tricks

  4. A word of warning… This presentation mentions covers undocumented or unsupported features of ASE While they will usually work fine , use entirely at you own risk ! (and don’t call Sybase TechSupport when it goes wrong...) Watch for this sign... Assorted DBA tricks

  5. A word of warning… This presentation covers some undocumented or unsupported features of ASE While these will usually work fine , use entirely at you own risk ! (and don’t call Sybase TechSupport when it goes wrong...) Watch for this sign... Assorted DBA tricks

  6. Advantages of identity columns: very useful ; can be used for many purposes fastest way of generating sequential numbers identity counter stored in OAM page little I/O needed for generating new value Disadvantages: risk of identity gaps identity gaps are difficult to repair Tricks with identity columns(Recap)

  7. Repairing identity gaps: use "bcp -E" (may take long) to correct existing data existing identity columns cannot be updated use dbcc object_atts to reset next identity value convoluted; difficult to use; not supported Tricks with identity columns(Recap)

  8. Avoiding identity gaps: In ASE 12, use "identity_gap" feature sets maximum identity gap size for a specific table not set by default Alternatively, put the identity column in a separate table (all versions) Tricks with identity columns(Recap)

  9. Putting the identity column in separate table: create a table with just a single identity column generate a new key value through a “dummy” insert new key value is in @@identity global variable 1> create table my_tab (id_col numeric identity) 2> go 1> insert my_tab values () -- “dummy” insert 2> select @new_key = @@identity 3> go For details, seehttp://www.euronet.nl/~syp_rob/idgaps.html Tricks with identity columns(Recap)

  10. Putting the identity column in separate table (cont’d) Can guarantee that identity gaps will never occur Should identity gaps occur, they can be easily corrected Handy for generating key values that require special processing (check digits, RI constraints) Hardly any performance cost Tricks with identity columns(Recap)

  11. Identity columns cannot be updated you may want to do this to correct existing data if you try, will get this server error message: 1> update my_table 2> set id_col = 90 3> where id_col = 100 4> go Msg 7744, Level 16, State 1: Server 'SYBASE', Line 1: Illegal attempt to update identity field 'id_col'. Tricks with identity columnsUpdating identity columns

  12. Identity columns cannot be updated there is nothing like "set identity update” ; what to do ? there is a solution, though undocumented & unsupported Tricks with identity columnsUpdating identity columns

  13. Identity columns cannot be updated there is nothing like "set identity update” ; what to do ? there is a solution, though undocumented & unsupported Tricks with identity columnsUpdating identity columns • an identity column is indicated by 2 bits in the system tables: • bit 64 of sysobjects.sysstat2 (for the table) • bit 128 of syscolumns.status (for the column) • when these bits are set, this indicates an identity column

  14. Identity column indicated by: bit 64 of sysobjects.sysstat2 bit 128 of syscolumns.status When resetting these bits to 0 manually: the server doesn't recognise it as an identity column anymore you can then update the column values in the existing rows When setting the bits back to 1, identity property continues ! Tricks with identity columnsUpdating identity columns

  15. Remove the identity property: 1> sp_configure "allow updates", 1 2> go 1> update syscolumns 2> set status = status - 128 3> where id = object_id("my_tab") 4> and name = "id_col" 5> and status & 128 = 128 6> go (actually you don’t need to do sysobjects.sysstat2 as well) Tricks with identity columnsUpdating identity columns

  16. Remove the identity property (cont’d): Now update the identity values: 1> update my_table 2> set id_col = 90 3> where id_col = 100 4> go (1 row affected)  update works now ! Tricks with identity columnsUpdating identity columns

  17. Restore the identity property: 1> update syscolumns 2> set status = status + 128 3> where id = object_id("my_tab") 4> and name = "id_col" 5> and status & 128 = 0 6> go don’t forget to disable “allow updates” use a transaction ! Tricks with identity columnsUpdating identity columns

  18. What could you use this for ? Correcting existing data (for example, fixing identity gaps) “Removing” an identity column without recreating the table example: implement id-column-in-single table architecture column becomes a normal numeric column; may add a default Tricks with identity columnsUpdating identity columns

  19. What could you use this for ? (cont’d) “Enlarging” an existing identity column remove bits from both system tables shutdown/restart server issue “alter table” to add new identity column Before: id_col is identity column; “c” is some normal column create table my_tab (id_col numeric(5) identity, c char(9)) insert my_tab (c) values (“blahblah”) Tricks with identity columnsUpdating identity columns

  20. After removing the original identity column: “id_col” is now a normal numeric column “new_id_col” is the new identity column alter table my_tab add new_id_col numeric(20) identity insert my_tab (id_col, c) values (0, “blahblah”) Can set a default on “id_col”, to retain existing syntax: insert my_tab (c) values (“blahblah”) Tricks with identity columnsUpdating identity columns

  21. Not documented or supported  use on your own risk ! However: this already works perfectly since System 10 must ensure no users are accessing the table best shutdown & restart the server after this sort of update In ASE 12, just use “alter table … drop …” (fully supported) Tricks with identity columnsUpdating identity columns

  22. All these tricks wouldn’t be necessary when there would be easy and supported ways to : update existing identity column values reset the identity seed downwards Vote on ISUG enhancement request e01_015 if you think this is important Tricks with identity columnsUpdating identity columns

  23. Questions …. ? Tricks with identity columnsUpdating identity columns

  24. A classic SQL problem: identifying missing data example: a row is inserted into a table every hour you want to find out whether a row is missing somewhere Tricks with identity columnsFinding “missing” data • The classic SQL solution: • self join with group by/having clauses An easier & faster solution: use an identity column

  25. Tricks with identity columnsFinding “missing” data • Example: table “my_tab”: • time_val • ------------------- • Apr 16 2000 1:05PM • Apr 16 2000 2:10PM • Apr 16 2000 3:04PM • Apr 16 2000 5:09PM <== 4 PM row is missing ! • Apr 16 2000 6:03PM

  26. Tricks with identity columnsFinding “missing” data • time_val n • ------------------- ---- • Apr 16 2000 1:05PM 1 • Apr 16 2000 2:10PM 2 • Apr 16 2000 3:04PM 3 • Apr 16 2000 5:09PM 4 • Apr 16 2000 6:03PM 5 • Step 1: add a sequence number to the data • select time_val, • n=identity(4) • into #t • from my_table • order by time_val • Step 2: find the lowest value • select @min_time = min(time_val)from #t (= 1:05 PM)

  27. For row N, add N-1 hours to the lowest time (= 1:05 PM) Tricks with identity columnsFinding “missing” data • Step 3: determine deviation from regular “period” for each row • select deviation = • abs(datediff(mi, dateadd(mi, (n-1)*60, @min_time), • time_val)) • from #t • time_val n • ------------------- ---- • Apr 16 2000 1:05PM 1 • Apr 16 2000 2:10PM 2 • Apr 16 2000 3:04PM 3 • Apr 16 2000 5:09PM 4 • Apr 16 2000 6:03PM 5 • ------ • 1:05PM • 2:05PM • 3:05PM • 4:05PM • 5:05PM

  28. Tricks with identity columnsFinding “missing” data • select deviation = • abs(datediff(mi, dateadd(mi, (n-1)*60, @min_time), • time_val)) • from #t Calculate the difference with the actual “time_val” • time_val n deviation • ------------------- ---- --------- • Apr 16 2000 1:05PM 1 0 • Apr 16 2000 2:10PM 2 5 • Apr 16 2000 3:04PM 3 1 • Apr 16 2000 5:09PM 4 64 • Apr 16 2000 6:03PM 5 58 • ------ • 1:05PM • 2:05PM • 3:05PM • 4:05PM • 5:05PM

  29. Tricks with identity columnsFinding “missing” data • select deviation = • abs(datediff(mi, dateadd(mi, (n-1)*60, @min_time), • time_val)) • from #t Data is missing here • time_val n deviation • ------------------- ---- --------- • Apr 16 2000 1:05PM 1 0 • Apr 16 2000 2:10PM 2 5 • Apr 16 2000 3:04PM 3 1 • Apr 16 2000 5:09PM 4 64 • Apr 16 2000 6:03PM 5 58

  30. Tricks with identity columnsFinding “missing” data • Complicated ? • You haven’t seen the classic self-join-with-group-by-and-having yet !

  31. Tricks with identity columnsFinding “missing” data • The solution to this type of problem lies in the relationship between subsequent rows => this is what “missing” means • The simplest and fastest way of establishing such a relationship is by using an identity column (and a temporary table) • In the classic self-join solution, the self-join is used to establish such a relationship as well, but in a more complicated way

  32. Questions …. ? Tricks with identity columnsFinding “missing” data

  33. sub • --- • 1 • 1 • 2 • 1 • 2 • 3 Tricks with identity columnsNumbering blocks of rows • Suppose you want to number blocks of rows: • customer order_nr • -------- -------- • Brown 145678 • Johnson 101234 • Johnson 356789 • Smith 123456 • Smith 234567 • Smith 345678

  34. Tricks with identity columnsNumbering blocks of rows • The classic solution: • use a loop-based algorithm • Better & faster: • avoid loops • use identity columns instead

  35. Tricks with identity columnsNumbering blocks of rows • Step 1: add a sequence number to the data • select customer, order_nr, n=identity(4) • into t1 from t • order by customer, order_nr • customer order_nr n • -------- -------- --- • Brown 145678 1 • Johnson 101234 2 • Johnson 356789 3 • Smith 123456 4 • Smith 234567 5 • Smith 345678 6 Note: within a customer, the sequence number “n” and the order_nr both increase

  36. Tricks with identity columnsNumbering blocks of rows • Step 2: determine the lowest “n” for each customer • select customer, low_n = min(n) • into t2 • from t1 • group by customer • customer low_n • -------- ----- • Brown 1 • Johnson 2 • Smith 4

  37. Tricks with identity columnsNumbering blocks of rows • Step 3: determine the final sub-range numbers • select t1.customer, • t1.order_nr, • sub = t1.n – t2.low_n + 1 • from t1, t2 • where t1.customer = t2.customer

  38. From tables t1 and t2, the final result can be derived customer order_nr sub -------- -------- --- Brown 145678 1 Johnson 101234 1 Johnson 356789 2 Smith 123456 1 Smith 234567 2 Smith 345678 3 • customer low_n • -------- ----- • Brown 1 • Johnson 2 • Smith 4 t2: Tricks with identity columnsNumbering blocks of rows t1: • customer order_nr n • -------- -------- --- • Brown 145678 1 • Johnson 101234 2 • Johnson 356789 3 • Smith 123456 4 • Smith 234567 5 • Smith 345678 6

  39. Tricks with identity columnsNumbering blocks of rows t1: • customer order_nr n • -------- -------- --- • Smith 123456 4 • Smith 234567 5 • Smith 345678 6 customer order_nr sub -------- -------- --- Smith 123456 1 Smith 234567 2 Smith 345678 3 • customer low_n • -------- ----- • Smith 4 t2: Step 3: select t1.customer, t1.order_nr, sub = t1.n – t2.low_n + 1 from t1, t2 where t1.customer = t2.customer

  40. Tricks with identity columnsNumbering blocks of rows • Result: • No loops => easier to program • Using “select into” to avoid updates • Faster than a loop

  41. Questions …. ? Tricks with identity columnsNumbering blocks of rows

  42. “dbcc resource” is a relatively obscure dbcc command lists pages full of rubbish ..but also two useful pieces of information: the pathname of the server errorlog the pathname of the interface file being used Useful DBCC commandsdbcc resource

  43. 1> dbcc traceon (3604) 2> go 1> dbcc resource 2> go pathname of interfaces file pathname of server errorlog Useful DBCC commandsdbcc resource • (...) • rprotstamp=1 rckptalarmid=537046744 rdsinitkpid=1376277 • rsaspid=0 • rfirstlock=0x0 rbufhmask=0x0 rinterfpath=/home/sybase • rbinaryversion=11920 rerrfile=/home/sybase/install/PROD.log • rdiagstream=0x0 • rdiagbuffer=0x20d16000 rnetworks=0x200135f8 rnnetworks=9 • (...)

  44. In Ase 12.0, errorlog location can also be found as follows: 1> select @@errorlog 2> go ----------------------------- /home/sybase/install/PROD.log Note: undocumented (though hardly seems risky…) Useful DBCC commandsdbcc resource

  45. “dbcc logprint” writes one or more strings to the server errorlog exists since version 11.0 (and probably earlier) 1> dbcc logprint ("blahblah", "blueblue") 2> go In ASE errorlog: (...)15:40:29.43 server background task error -1: blahblah (...)15:40:29.44 server background task error -1: blueblue Ignore this message Useful DBCC commandsdbcc logprint

  46. “dbcc printolog” writes one string to the server errorlog does not include this funny message new in ASE 12.0 1> dbcc printolog ("blahblah") 2> go In ASE errorlog: (...) 15:43:53.50 server blahblah Useful DBCC commandsdbcc printolog

  47. Why would you want to do this ? recording certain types of application errors for DBA purposes: a cron job checks the errorlog every night; sends email to the DBA you want only the “new” errors since the previous check when the check completes; write a custom message to the errorlog the cron job will only look at the part of the errorlog after the last occurrence of this message Useful DBCC commandsdbcc logprint/printolog

  48. dbcc sqltext ( spid ) a “tidy” version of dbcc pss dumps a session’s networks buffers shows recently submitted SQL text; may already have completed ! limited to about last 200 characters available from ASE 11.9.x 1> dbcc traceon (3604) 2> go 1> dbcc sqltext (12) 2> go SQL Text: select amount from order_tab where order_nr = 123 Useful DBCC commandsdbcc sqltext

  49. dbcc traceflags displays currently enabled traceflags in server 1> dbcc traceflags Active traceflags: 302, 3604 When using option “2”, also displays which sessions have any flags set: 1> dbcc traceflags (2) Active traceflags: 302, 3604 Processes with P2_DEBUG flag on: 9, 12 Actually, this is pretty useless... Useful DBCC commandsdbcc traceflags

  50. dbcc istraceon ( traceflag ) Tests whether specified traceflag is enabled Result is placed in @@error if traceflag enabled  @@error will be 0 if traceflag not enabled  @@error will be <> 0 Can use this in SQL code to test for traceflags and take some action New in ASE 12.0 Useful DBCC commandsdbcc istraceon

More Related