IMAGE/SQL: Issues and answers concerning SQL tables

Software

Papers & Training

Java

HP Partners

News & Events

Register for more info

This tutorial was presented at the Toronto Interex in August 1995. It is reproduced here for those of you who were not able to attend (some topics have been revised since Interex). The primary goal of this material is to explain the key concepts in SQL that are needed to develop an SQL application which accesses information stored in either TurboIMAGE data sets or SQL tables.

If you would like to download this tutorial to your own web server, please see downloading instructions at the bottom of this page. If you would like to print off a hardcopy of this document, please see printing instructions at the bottom of this page.

Table of contents

• Chapter 1: Overview
  1. What Subjects are Covered in this Presentation?
  2. How are TurboIMAGE/XL, ALLBASE/SQL, and IMAGE/SQL Related?
  3. Why Should I Care about SQL Tables?
  4. What SQL Terminology Should I know?
  5. Where Can I Get Additional Information?
     • IMAGE/SQL and TurboIMAGE Documentation Products
     • ALLBASE/SQL Documentation Products for MPE/iX
     • ALLBASE/SQL Documentation Products for HP-UX

• Chapter 2: SQL Storage
  1. Review Physical Storage for TurboIMAGE/XL
  2. Review Jumbo Data Sets in TurboIMAGE/XL
  3. How are SQL Tables Physically Stored?
  4. How are DBEFileSets Used in ALLBASE/SQL?
  5. Explain the Internals of a DBEFile
  6. How are Rows of SQL Tables Stored in DBEFiles?
  7. How Does the Optimizer Locate Rows in DBEFiles?
  8. Describe a Serial Scan
  9. Describe an Index Scan
  10. Describe a Hash Scan
  11. Describe a TID Scan

• Chapter 3: SQL Security
  1. What are the Key Elements of Security in SQL?
  2. What are the Key Elements of Security in SQL (continued)?
  3. Explain the GRANT Statement
  4. Provide an Example of Security Implemented in SQL
  5. How is SQL Security Implemented by IMAGE/SQL?
  6. How is SQL Security Implemented by IMAGE/SQL (continued)?

• Chapter 4: Trouble-Shooting with SQLMON
  1. How do I Start SQLMON?
  2. How are the SQLMON Screens and Subsystems Organized?
  3. Briefly Describe the Screens in the OVERVIEW Subsystem
  4. Briefly Describe the Screens in the LOCK Subsystem
  5. Briefly Describe the Screens in the STATIC Subsystem

• Chapter 5: SQL Transactions and Locking

  The first three slides explain locking on TurboIMAGE data sets when SQL statements are issued:

  1. TurboIMAGE Locking: MODE 5 user
  2. TurboIMAGE Locking: MODE 1 user WITHOUT an SQL predicate
  3. TurboIMAGE Locking: MODE 1 user WITH an SQL predicate

     The remaining slides explain locking on ALLBASE/SQL tables:

  4. How are Transactions Used?
  5. How Should I Build Transactions?
  6. How Do I Start a Transaction?
  7. How Do I Use Transaction Timeout Limits?
  8. How Do I Terminate a Transaction?
  9. How Does ALLBASE/SQL Resolve Deadlocks?
  10. Savepoints
  11. How Does ALLBASE/SQL Use Locking?
  12. What are the DBECon file parameters Associated With Locking?
  13. What are the DBECon file parameters Associated With Locking (continued)?
  14. How Does Granularity Affect Locking?
  15. How Does Granularity Affect Locking (continued)?
  16. Describe the SHARE (S) and EXCLUSIVE (X) Lock Mode Types
  17. What is Intention Locking?
  18. Describe the INTENT SHARE (IS) Lock Mode Type
  19. Describe the INTENT EXCLUSIVE (IX) Lock Mode Type
  20. Describe the SHARE and INTENT EXCLUSIVE (SIX) Lock Mode Type
  21. Summary of Intention Locking on a PUBLICROW Table
  22. Summary of Intention Locking on a PUBLIC Table
  23. How Do Table Types Affect Locking?
  24. Explain the LOCK TABLE Statement
  25. Explain the LOCK TABLE Statement (continued)
  26. Explain the LOCK TABLE Statement (continued)
  27. Describe Lock Compatibility
  28. Describe Lock Compatibility (continued)
  29. Describe Lock Strengths
  30. How Does the Optimizer Affect Locking?
  31. How are Indexes Locked?
  32. How are Cursors Used?
  33. What ALLBASE/SQL Statements Affect Cursors?
  34. How are Isolation Levels Used?
  35. Describe the Repeatable Read Isolation Level
  36. Describe the Repeatable Read Isolation Level (continued)
  37. Describe the Repeatable Read Isolation Level (continued)
  38. Describe the Cursor Stability Isolation Level
  39. Describe the Cursor Stability Isolation Level (continued)
  40. Describe the Read Committed Isolation Level
  41. Compare RC to the RR and CS Isolation Levels
  42. Describe the Read Uncommitted Isolation Level
  43. Compare RU to the RR, CS and RC Isolation Levels
  44. When Should I Use Each Isolation Level if My Cursor is Updatable?
  45. When Should I Use Each Isolation Level if My Cursor is Not Updatable?
  46. How are the System Catalog Tables Locked?
  47. What is KEEP CURSOR?
  48. How Does a Cursor Opened with KEEP CURSOR Behave?
  49. How Does KEEP CURSOR Affect Locking?
  50. How Does KEEP CURSOR Affect Locking (continued)?

• Summary of Topics Covered.

Downloading instructions

• Download the file sqltables.tar (2.5 Mb) and move it to the proper directory on your web server (an attempt to compress this file failed because of the large number of graphic files).

• On your web server, type the following command:

  tar -xvf sqltables.tar
  

  A directory named sqltables will be created.

• You don't need the file sqltables.tar anymore, so you can remove it from your system.

• Create a URL that reflects the name of your web server and the location of the sqltables directory. The URL will be of the form:

  http://YourWebServer/YourPath/sqltables/main.html
  

• Use your favorite browser and visit your new URL!

Printing instructions

• Chapter 1: Overview
• Chapter 2: SQL Storage
• Chapter 3: SQL Security
• Chapter 4: Trouble-Shooting with SQLMON
• Chapter 5: SQL Transactions and Locking
  • TurboIMAGE Locking (Topics 1-3)
  • ALLBASE Locking (Topics 4-33)
  • ALLBASE Locking (Topics 34-50)

WARNING: Be prepared to wait a while after you have selected one of the above links!