Scenario
The hospital receptionist who fetches the patient's information to schedule appointment with a doctor needs to query only the patient basic information and information about the doctor who is attending the patient. To prevent the receptionist from fetching the patient's medical information or disease information the patient's PMD XML document should be protected.
Operation
DB2 pure XML queries can be used inside SQL views to filter or fetch data which can be called by an application. A SQL view is created to query the XML document.
CREATE VIEW - Creates a View in the database. The view can contain a query which fetches data from single or more tables. The view retrieves only the patient's name, address and doctor information from the PMD XML document.
Also the view uses a query that is indexed and though the view is used it uses the index for faster retrieval.
CREATE INDEX
- to create an XML index for the PMD document.
GENERATE KEY USING XMLPATTERN
- used to specify the key within the XML document.
Solution
The receptionist is prevented from taking sensitive patient information by giving her a view which displays only basic patient information and his doctor's name. The data retrivel is also faster.
The method to generate a Snapshot of how index is used is given below.
Run the following steps on the DB2 CLP window to view the explain snapshot:
The below is the snapshot of the index usage though data is fetched using the view.