basics of database tuning - computing · pdf filecmpt 354: database i -- embedded sql 2 sql is...
TRANSCRIPT
![Page 1: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/1.jpg)
Embedded SQL
![Page 2: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/2.jpg)
CMPT 354: Database I -- Embedded SQL 2
SQL Is Not for All
• SQL is capable in manipulating relational data• SQL is not good for many other tasks
– Control structures: loops, conditional branches, …– Advanced data structures: trees, arrays, …– Not good for general problem solving
• How to solve a general problem involving relational data?– Use a general programming language (e.g., C++/Java)
in general but use SQL for relational data access– How to embed SQL into a general programming
language?
![Page 3: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/3.jpg)
CMPT 354: Database I -- Embedded SQL 3
Integrating SQL and Programming?
• Typical procedural programming languages– C/C++, Java– Programs = data structures + algorithms– Structured data variables: records, arrays– Procedural semantics: loop, control structures
• SQL– Sets: query results are sets, unordered in
general– No control structure in general– Hiding data structures and algorithms
![Page 4: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/4.jpg)
CMPT 354: Database I -- Embedded SQL 4
Database management
system
Programming Environment
program
files
SQL
database
storage
?
![Page 5: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/5.jpg)
CMPT 354: Database I -- Embedded SQL 5
Database management
system
SQL and Hosting Language
program
files
SQL
database
storage
embedded
![Page 6: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/6.jpg)
CMPT 354: Database I -- Embedded SQL 6
Embedding SQL
Host language+
Embedded SQL
preprocessor
Host language+
Function calls
Host-language compiler
Object-code program
SQL library
Pre-compile
![Page 7: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/7.jpg)
CMPT 354: Database I -- Embedded SQL 7
SQL/Host Language Statement?• How can a preprocessor tell whether a statement is for
SQL or for the host language?– Keywords EXEC SQL in front of the statement
• How can an SQL statement use a variable in the host language?– Insert a new customer whose information is input by a userchar customer_name[20], customer_street[30], customer_city[30],
SQLSTATE[6];… //host language stuffEXEC SQL BEGIN DECLARE SECTION;
char customer_name[20], customer_street[30], customer_city[30];char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;– Declare a host variable in SQL before using it in SQL statements
![Page 8: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/8.jpg)
CMPT 354: Database I -- Embedded SQL 8
Embedded SQL
• SQL can be embedded in many host languages such as C/C++, Java, and COBOL
• EXEC SQL statement is used to identify embedded SQL request to the preprocessor– The basic form follows that of the System R embedding
of SQL into PL/IEXEC SQL <embedded SQL statement>END_EXEC– May vary by language (for example, the Java
embedding uses # SQL { …. }; )
![Page 9: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/9.jpg)
CMPT 354: Database I -- Embedded SQL 9
Using Shared VariablesEXEC SQL BEGIN DECLARE SECTION;
char customer_name[20], customer_street[30], customer_city[30];char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;customer_name=get_customer_name();customer_street=get_customer_street();customer_city=get_customer_city();EXEC SQL INSERT INTO customer(customer_name,
customer_street, customer_city)VALUES (:customer_name, :customer_street, :customer_city);
• The shared variables are prefixed by a colon in SQL statements– They appear without the colon in host language statements
![Page 10: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/10.jpg)
CMPT 354: Database I -- Embedded SQL 10
Status of SQL Statement Execution
• How can we know whether an update succeeds or not?– SQLSTATE connects the host language
program with the SQL execution system• SQLSTATE: an array of five characters
– Each time a function of the SQL library is called, a code is put in SQLSTATE
– ‘00000’: no error condition occurred– ‘02000’: a tuple requested is not found
![Page 11: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/11.jpg)
CMPT 354: Database I -- Embedded SQL 11
Database management
system
Communication in Between
program
files
SQL
database
storage
Data
SQLSTATE
![Page 12: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/12.jpg)
CMPT 354: Database I -- Embedded SQL 12
How to Use SQLSTATE
• An easy way is to treat it as a string• Special considerations for host languages,
e.g., C/C++– A string in C/C++ needs an end-of-string
character ‘\0’– Declare char SQLSTATE[6];– Set SQLSTATE[5]=‘\0’;– Use strcmp in comparison
![Page 13: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/13.jpg)
CMPT 354: Database I -- Embedded SQL 13
Storing Results into Variables
• If an SQL statement returns only one tuple, the attribute values can be stored into variables in the host language
• Use keyword INTO in the SELECT clauseEXEC SQL SELECT SUM(assets)
INTO :total_assetsFROM branchWHERE branch_city = ‘Brooklyn’;
![Page 14: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/14.jpg)
CMPT 354: Database I -- Embedded SQL 14
Handling a Set of Tuples
• Generally, an SQL statement may return a set of tuples
• Cursor – Conceptually, put all tuples returned by a query
into a file– Fetch the tuples from the virtual file– After use, the file should be closed and released
![Page 15: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/15.jpg)
CMPT 354: Database I -- Embedded SQL 15
Example
• From within a host language, find the names and cities of customers with more than the variable amount dollars in some accountEXEC SQL
declare c cursor for select customer_name, customer_cityfrom depositor, customer, accountwhere depositor.customer_name = customer.customer_name
and depositor account_number = account.account_numberand account.balance > :amount
END_EXEC
• Declared, but not materialized yet
![Page 16: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/16.jpg)
CMPT 354: Database I -- Embedded SQL 16
Executing a Cursor
• The open statement causes the query to be evaluated
EXEC SQL open c END_EXEC• The fetch statement causes the values of
one tuple in the query result to be placed on the host language variablesEXEC SQL fetch c into :cn, :cc END_EXEC– Can be called repeatedly to get all tuples
![Page 17: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/17.jpg)
CMPT 354: Database I -- Embedded SQL 17
Executing a Cursor (2)
• SQLSTATE set to ‘02000’ to indicate no more data is available
• The close statement causes the database system to delete the temporary relation that holds the result of the query
EXEC SQL close c END_EXEC• Details vary with language
– The Java embedding defines Java iterators to step through result tuples
![Page 18: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/18.jpg)
CMPT 354: Database I -- Embedded SQL 18
Updates Through Cursors
• To update tuples fetched by cursor, declare the cursor for update
declare c cursor forselect * from accountwhere branch_name = ‘Perryridge’
for update• To update tuple at the current balance of cursor c
update accountset balance = balance + 100where current of c
![Page 19: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/19.jpg)
CMPT 354: Database I -- Embedded SQL 19
Concurrent Changes
• Two users may change tuples in a database concurrently– Process 1: remove account of balance < 1000– Process 2: increase the balance by 10%– Whether an account of balance 950 would be removed
depends on which process touches the tuple first• Keyword INSENSITIVE makes a cursor not
affected by other concurrent updatesEXEC SQL DECLARE accountCursor INSENSITIVE
CURSOR FORSELECT balance FROM account;
– More about concurrency control in CMPT 454
![Page 20: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/20.jpg)
CMPT 354: Database I -- Embedded SQL 20
READ ONY Cursors
• If a cursor is used only for read operations, it can be concurrent with any other processesEXEC SQL DECLARE readAccCursor CURSOR
READ_ONLY FOR SELECT balance FROM account;
• Different database systems may have slightly different syntax
![Page 21: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/21.jpg)
CMPT 354: Database I -- Embedded SQL 21
Scrolling Cursors• Random access versus serial access
– Serial access: fetch the next one, cannot jump to other tuples
– Random access: fetch any tuple in a cursor• Scrolling cursor: a cursor for random access
EXEC SQL DECLARE accCursor SCROLL CURSOR FOR account;
• Options in FECTCH– NEXT or PRIOR– FIRST or LAST– RELATIVE k (RELATIVE -1 = PRIOR)– ABSOLUTE k (ABSOLUTE -1 = LAST)
![Page 22: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/22.jpg)
CMPT 354: Database I -- Embedded SQL 22
Dynamic SQL
• Allow programs to construct and submit SQL queries at run time
char * sqlprog = “update account set balance = balance * 1.05
where account_number = ?”EXEC SQL prepare dynprog from :sqlprog;char account [10] = “A-101”;EXEC SQL execute dynprog using :account;
• “?” is a place holder for a value that is provided when the SQL program is executed
![Page 23: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/23.jpg)
CMPT 354: Database I -- Embedded SQL 23
Summary
• SQL is good for accessing relational data, but not for other general problem solving tasks– Embedding SQL into a host general
programming language is useful in practice• Embedded SQL• Cursors
![Page 24: Basics of Database Tuning - Computing · PDF fileCMPT 354: Database I -- Embedded SQL 2 SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for](https://reader035.vdocuments.us/reader035/viewer/2022062413/5a7823a67f8b9aa3688e7aa0/html5/thumbnails/24.jpg)
CMPT 354: Database I -- Embedded SQL 24
To-Do-List
• Understand how to embed SQL into C#• Understand cursors in SQL Server 2005