What's Where: Using the Voyager Class Diagrams and Data Dictionary With Access Queries

1. What's Where: Using the Voyager Class Diagrams and Data Dictionary With Access Queries ELSUG October 9, 2008 Cathy SalikaCARLI Consortium of Academic and Research Libraries in Illinois

2. Voyager is a relational database A relational database is a bunch of tables. A table looks like a spreadsheet.

3. Voyager has about 400 tables Some are things you probably expect PATRON, ITEM, CIRC_TRANSACTION Some are less obvious INV_LINE_ITEM_NOTES Some are used to look up common codes LOCATION, FINE_FEE_TYPE

4. The _ID Fields Many tables have a sequentially assigned number in a column with “_ID” in its name.

5. The _ID Fields Sometimes the relationship is made directly.

6. The _ID Fields Sometimes there’s an intermediary.

7. Starting to Write a Query • First you have to figure out • what data you need • what tables the data are in • how to correctly relate those tables • This is easier if you know about Voyager because you use it regularly.

8. Starting to Write a Query • A good query • is built a few tables at a time • is tested at each step of the way • has no extraneous tables

9. Starting to Write a Query There are two tools to show you what’s in the tables and how the tables relate. Voyager Class Diagrams available on SupportWeb Voyager Data Dictionary in the Voyager Technical User’s Guide But I recommend that you use the CARLI version of these documents.

10. CARLI’s Class Diagrams

11. CARLI’s Data Dictionary

12. CARLI’s Data DictionaryLists the columns in alphabetical order (except that the _ID fields come first)

13. CARLI’s Data DictionaryServes as an index to the class diagrams

14. CARLI’s Data DictionaryTells you which fields are in UNICODE so you can format them properly

15. CARLI’s Data DictionaryIncludes comments on what’s in the fields

16. CARLI’s Data DictionaryProvides lots of tips about where to find things.

17. CARLI’s Data DictionaryGives warnings about tables to avoid

18. CARLI’s Data DictionaryProvides help with tricky links

19. Where to Get Them • CARLI’s Data Dictionary and the additional Class Diagrams • Are in SupportWeb, in the KnowledgeBase. • Search for “CARLI” and you’ll find it. • Will soon be in the EL Commons • Are on these flash drives

20. Views Both versions of the Data Dictionary include views. Their names end with _VW Inside Voyager, a view is actually a query, but you can use them as if they were tables. Some views are helpful, some are inefficient, some are WRONG. So let’s see which are which.

21. Good Views Call Nos. DEWEYCLASS_VWLCCLASS_VWNLMCLASS_VWSUDOCCLASS_VWUDCCLASS_VW A call number is included in a view if the CALL_NO_TYPE in MFHD_MASTER says it should be there.

22. Good Views Fixed Field Data for BibsMARCBOOK_VWMARCCOMPUTER_VWMARCMAP_VWMARCMUSIC_VWMARCSERIAL_VWMARCVISUAL_VW

23. Inefficient Views CIRCCHARGES_VW CIRCRENEW_VW FUNDLEDGER_VWISSUES_VWITEM_VWSERIALS_VW • Why I don’t like them • Some use a dozen tables or more, so queries that use them are slow. • You have to know too much to understand their quirks. • (CIRCCHARGES_VW drops charge transactions if the item has been deleted.) • It’s too hard to make your statistics consistent.

24. If you want to know what the views doand you have access to SQLPLUS on your serverand you don’t mind reading a bit of SQL…here’s how. SQL> set heading off SQL> set long 4500 SQL> column view_name fold_a newl SQL> column text newl newl newl newl SQL> select view_name, text from all_views where owner=‘XXXDB’ order by view_name;

25. Bad Views AUTHBLOB_VWBIBBLOB_VWMFHDBLOB_VW They don’t work correctly for long records. When to use them: NEVER

26. And now, a commercial… If you’re new to query writing in Voyager, consider attending 2 other sessions: “Scary Queries Laid to Rest” by Jean Vik at 8:30am “Idiomatic Access Reports” at 9:30am

27. Questions?Thank you!