1 / 47

Database, Database Objects and SQL

Database, Database Objects and SQL. Agenda. IDS SQL Architecture Database (DB), DB variants Tables Data types User-Defined Types (UDT) Isolation Levels Constraints Stored Procedures/User-Defined Routines (UDR) Summary Resources. Tables (Cont.). Temporary:

denim
Télécharger la présentation

Database, Database Objects and SQL

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. Database, Database Objects and SQL

  2. Agenda IDS SQL Architecture Database (DB), DB variants Tables Data types User-Defined Types (UDT) Isolation Levels Constraints Stored Procedures/User-Defined Routines (UDR) Summary Resources

  3. Tables (Cont.) Temporary: Not registered in system catalogs. Per-session or connection. Reside in Temporary DBSpace if available. Not logged by default. Automatically cleaned up at end of session or connection. Complete indexing supported. Data statistics supported.

  4. IDS SQL Architecture External Space Client Requests SQL Engine Tables Access Methods Index Type System System catalogs Language Managers API for UDRs C Java SPL

  5. Database (DB), DB variants A database is a logical storage unit that contains tables and indexes. Contains system catalog that tracks information about database objects like: Tables and indexes. SPL routines. integrity constraints. Database names are always lower case. Tip: Command to create a database in dbaccess CREATE DATABASE mydb; Command to connect to and close database in dbaccess DATABASE mydb; CLOSE DATABASE;

  6. Database (DB), DB variants (Cont.) Databases come in three flavors Non-logged: No transactions. No logging. Data consistency not guaranteed. Logged: Transactions are logged. Logging can be buffered or un-buffered. Owner name not used for object resolution. Ansi: Statements always in transaction. Un-buffered logging. Owner name used for object resolution.

  7. Create DB Commands -- Command to create a un-logged database in dbaccess CREATE DATABASE mydb; -- Command to create a logged database in dbaccess CREATE DATABASE mydb WITH LOG; CREATE DATABASE mydb WITH BUFFERED LOG; -- Command to create a ANSI database in dbaccess CREATE DATABASE mydb WITH LOG MODE ANSI;

  8. Tables Permanent Registered in system catalogs. Available across sessions or connections. Can be created in a specific DBSpace or root DBSpace. Two modes: Raw – un-logged. Standard – logged. Temporary Not registered in system catalogs. Per-session or connection. Reside in Temporary DBSpace if available. Not logged by default.

  9. Tables (Cont.) Virtual Tables (outside the scope of this presentation): Registered in system catalogs. Storage is not managed by Informix. Used to access external data as a relational table. Pseudo Tables: Registered in system catalogs. No storage, data generated from in memory data structures. Created internally by Informix to expose administrative data.

  10. Tables (Cont.) -- Command to create a table in a database CREATE TABLE customer ( customer_num SERIAL (101) NOT NULL, fname CHAR(15), lname CHAR(15), company CHAR(20), PRIMARY KEY (customer_num) );

  11. Tables (Cont.) -- Command to create a temporary table in a database CREATE TABLE mytemptab ( stock_no SMALLINT, mfg_code CHAR(5), mfg_name CHAR(20), phone CHAR(18), descript VARCHAR(255) );

  12. Tables (Cont.) -- Command to query pseudo table in sysmaster database -- Returns all table names from all databases in an -- instance. systabnames is a pseudo table. SELECT tabname FROM systabnames;

  13. Table Fragmentation: Why ? ParallelismFragments are accessed in parallel, decreasing scan or insert time. Table scan threads * fragments Fragment EliminationUnneeded fragments are eliminated, decreasing scan or insert time, and reducing disk contention. Table scan threads * X X X X fragments Fragment Elimination & ParallelismBoth goals are achieved. Table scan threads * X X fragments

  14. Table Fragmentation: OLTP OLTP characteristics: • High volume of short transactions. • Each transaction accesses a few rows. • Index access method is used. For this environment: • Fragment the data by round robin or expression. • For large tables that receive a large percentage of transaction activity, fragment the indexes using an expression-based fragmentation strategy.

  15. Table Fragmentation: Data Warehousing DW characteristics: Low volume of long running queries. Queries access most of the rows in each table. Very few indexes are generally required. Preferred access method is sequential scan. Preferred join method is the hash join. For this environment: • Fragment elimination. • Parallel scan the needed fragments.

  16. Table Fragmentation: Expression based dbspaces tab_adbs1 tab_adbs2 tab_adbs3 CREATE TABLE table_a (x INTEGER, y INTEGER, z CHAR (25)) FRAGMENT BY EXPRESSION x <= 10 and x >= 1 in tab_adbs1,x <= 20 and x > 10 in tab_adbs2,x <= 30 and x > 20 in tab_adbs3; Data is split across fragments based on the range given for expression

  17. Table Fragmentation: Expression based dbspaces tab_adbs1 tab_adbs2 tab_adbs3 CREATE TABLE table_a (x INTEGER, y INTEGER, z CHAR (25)) FRAGMENT BY ROUND ROBIN in tab_adbs1, tab_adbs2, tab_adbs3; Data is split across fragments evenly based on round robin strategy

  18. System Catalog Tables System Catalog Tables: Each database has its own system catalog tables/views. These store meta data about the database objects. You can query these tables just like any other tables. Tables with tabid < 100 in “informix”.systables. Reside under the owner name “informix”. Examples: “informix”.systables “informix”.sysindices “informix”.syscolumns -- query to retrieve system catalog tables/views in the current -- database SELECT tabname FROM “informix”.systables WHERE tabid < 100

  19. Views Virtual table based on a specified SELECT statement. Registered in system catalogs. Data is not stored on disk. View query dynamically loaded and evaluated as needed. Different views of underlying tables for different users. Access privilege restrictions can be enforced.

  20. Views (Cont.) -- create a view on customer table in stores_demo database CREATE VIEW custview (firstname, lastname, company, city) AS SELECT fname, lname, company, city FROM customer WHERE city = ‘San Jose'

  21. Indexes Index Characteristics Unique Clustered Composite key Index over multiple columns of a table. Functional index Index over a set of keys derived from the results of a function. -- Create index cidx on fname column of customer table CREATE INDEX cidx ON customer(fname)

  22. Indexes Built-in Index Types BTREE (Default) RTREE Tree structure to index Spatial data. User defined Indexing schemes GIST Generalized Search Tree – to index any content. VII Virtual Index Interface based access method. Index Structure maintained by routines registered with Informix.

  23. SQL Datatypes Informix Datatypes Common SQL Types Extended Datatypes Numeric Complex Datatypes User Defined Datatypes Character Large Objects Distinct Opaque Collection Row Types Time SET Multi-Set List

  24. Built-in Data types Numeric INTEGER SERIAL SERIAL8 SMALLINT BIGINT BIGSERIAL DECIMAL or NUMERIC(p,s) REAL or SMALLFLOAT FLOAT DOUBLE PRECISION MONEY(p,s) Time DATE DATETIME INTERVAL Character CHAR(n) CHARACTER VARYING(n,r) VARCHAR(n,r) LVARCHAR(m) NCHAR(n) NVARCHAR(m,r) Large Objects BLOB CLOB BYTE TEXT Misc BOOLEAN

  25. Auto Generated Data types SERIAL, SERIAL8 and BIGSERIAL are auto generated. Store a sequential integer. Starting value for sequence can be optionally provided. Automatically generated by the server. SERIAL type takes 4 bytes of storage. SERIAL8 and BIGSERIAL take 8 bytes of storage each.

  26. UDT (Cont.) Distinct data type Based on existing built-in or extended data type. Same internal structure as the source type. CREATE DISTINCT TYPE pound AS FLOAT; CREATE DISTINCT TYPE kilos AS FLOAT; CREATE DISTINCT TYPE my_pound AS pound; CREATE TABLE my_weight(name VARCHAR(24), weight pound); CREATE TABLE my_kweight(name VARCHAR(24), weight kilos);

  27. UDT (Cont.) Opaque Types Fully encapsulated. Internal structure is unknown to the database server. Type designer needs to provide routines that describe its characteristics (storage, input, output, arithmetic and relational operators, functions…).

  28. UDT (Cont.) Complex Types Built with combination of other built-in or extended data types. Row data type: Has group of elements of any data type known to the server. Collection data type: SET, LIST and MULTISET types. Has groups of elements of the same data type.

  29. UDT (Cont.) -- create a ROW Type CREATE ROW TYPE prx ( patient VARCHAR(28), pid INT, addr VARCHAR(128) ); -- create a table with SET data type CREATE TABLE employee ( name CHAR(30), address CHAR (40), salary INTEGER, dependents SET(VARCHAR(30) NOT NULL) );

  30. UDT (Cont.) -- create a table with MULTISET data type CREATE TABLE employee ( name CHAR(30), address CHAR (40), salary INTEGER, bonus MULTISET(MONEY NOT NULL) ); -- create a table with LIST data type CREATE TABLE employee ( name CHAR(30), address CHAR (40), salary INTEGER, dependents LIST(VARCHAR(30) NOT NULL) );

  31. Sequences A database object that generates a sequence of whole numbers within a defined range. Unlike SERIAL Auto generated type, sequences are not tied to any table.

  32. SEQUENCES Example CREATE SEQUENCE seq_2 INCREMENT BY 1 START WITH 1 MAXVALUE 30 MINVALUE 0 NOCYCLE CACHE 10 ORDER; CREATE TABLE tab1 (col1 int, col2 int); INSERT INTO tab1 VALUES (0, 0); INSERT INTO tab1 (col1, col2) VALUES ( seq_2.NEXTVAL, seq_2.NEXTVAL ); SELECT * FROM tab1; col1 col2 0 0 1 1

  33. ISOLATION Levels DIRTY READ: No locks and it respects none. COMMITTED READ: Guarantees that every retrieved row is committed in the table at the time that the row is retrieved. COMMITTED READ LAST COMMITTED: Same as COMMITTED READ, but readers avoid locking errors as they work on last committed data.

  34. ISOLATION Levels (Cont.) CURSOR STABILITY: Holds locks on row until next row is fetched or cursor is closed. REPEATABLE READ: Places a shared lock on every row that is selected during the transaction.

  35. Constraints Primary Key Constraint: Defined over a column or group of columns. Enforces uniqueness in column values. NULL values are not allowed in primary key columns. CREATE TABLE customer ( customer_num SERIAL (101) NOT NULL, fname CHAR(15), lname CHAR(15), company CHAR(20), PRIMARY KEY (customer_num) );

  36. Constraints (Cont.) • Referential Constraint • Relationship between tables or same table. • Primary key in one table can be referenced in the same or another table. • ON DELETE CASCADE option to delete referenced table rows on delete.

  37. Constraints (Cont.) -- Referential constraint CREATE TABLE customer ( customer_num SERIAL (101) NOT NULL, fname CHAR(15), lname CHAR(15), company CHAR(20), PRIMARY KEY (customer_num) ); CREATE TABLE orders ( order_num SERIAL(1001), order_date DATE, customer_num INTEGER NOT NULL, ship_instruct CHAR(40), PRIMARY KEY (order_num), FOREIGN KEY (customer_num) REFERENCES customer (customer_num) );

  38. Constraints (Cont.) Check Constraint: Condition or requirement on a data value before data can be assigned to a column during an INSERT or UPDATE. -- Check that acct1 and acct2 are between 0 and 99999 CREATE TABLE my_accounts ( chk_id SERIAL PRIMARY KEY, acct1 MONEY CHECK (acct1 BETWEEN 0 AND 99999), acct2 MONEY CHECK (acct2 BETWEEN 0 AND 99999) );

  39. Stored Procedure/UDR Stored Procedure: Written in Stored Procedure Language (SPL). Compiled and stored natively in server. Extremely powerful constructs to implement business logic. CREATE PROCEDURE raise_prices ( per_cent INT ) UPDATE stock SET unit_price = unit_price + (unit_price * (per_cent/100)); END PROCEDURE; EXECUTE PROCEDURE raise_prices (10);

  40. Stored Procedure/UDR UDR C Language UDRs: Written in C language. Compiled into a shared library and registered with the server. Server dynamically loads and executes code in shared library. Used heavily for UDT and VTI/VII implementations. DataBlade APIs provided to perform advanced tasks . JAVA Language UDRs: Written in Java Language. Compiled into class or jar files and registered with the server. UDRs can contain JDBC code to execute advanced tasks. Server contains embedded JVM to execute the code.

  41. XML, XPATH, XSLT functions Informix server has built-in support for: Publishing relational data as XML. Performing XPATH operations. Transforming generated XML using XSLT. XML Publishing Functions: genxml() and genxmlClob(). genxmlqueryhdr() and genxmlqueryhdrClob(). genxmlelem() and genxmlelemClob().

  42. XML, XPATH, XSLT functions (Cont.) XPath functions for pattern matching and extraction within XML document: extract() and extractClob() extractValue() existsNode() XSLT functions: XSLTransform() XSLTransformAsCLOB() XSLTransformAsBLOB()

  43. XML functions example SELECT genxml(mycustrpt, 'mycustomers') FROM (SELECT c.customer_num, c.customer_name.last, o.order_num FROM customer c, orders o WHERE c.customer_num = o.customer_num and order_date = '05/20/1998‘ ) AS mycustrpt; Output: genxml <mycustomers customer_num="104" expression="Kilmore" order_num="1001"/>

  44. XML functions example (Cont.) SELECT genxmlelem(mycustrpt, 'mycustomers') FROM ( SELECT c.customer_num, c.customer_name.last, o.order_num FROM customer c, orders o WHERE c.customer_num = o.customer_num AND order_date = '05/20/1998‘ ) AS mycustrpt; genxmlelem <mycustomers> <row> <customer_num>104</customer_num> <expression>Kilmore</expression> <order_num>1001</order_num> </row> </mycustomers>

  45. XML functions example (Cont.) SELECT XSLTransform(s.style_doc, x.xml_doc) FROM style_sheets s, xml_docs x WHERE s.style_title = 'ecommerce_ROOT.xsl' AND x.xml_transaction_id = 12345; -- Transforming dynamically generated XML documents SELECT XSLTransform(s.style_doc, genxml ( “transaction”, row(o.trnid, o.custid, o.addr) ) ) FROM style_sheets s, orders o WHERE s.style_title = 'ecommerce_ROOT.xsl' AND o.trnid = 12345;

  46. Summary We have learned the following: Types of databases: Ansi, Logged and non-logged. Database objects: tables, indexes, views, sequences. SQL Datatypes, UDT Isolation levels Constraints Stored Procedures and UDR XML functions

  47. Resources • The Online IDS Information Center • http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp • One-stop shop for IDS product documentation • Supports book marking favorite topics, narrowing the scope to refine searches, printing subsets of topics • IBM Informix DeveloperWorks Technical Articles • http://www.ibm.com/developerworks/db2/products/informix/index.html • Premium technical resource site for DBAs and developers • Features explained with examples/sample code • Contributions from IBM experts as well as customers • IBM DeveloperWorks IDS Blogs • http://www-128.ibm.com/developerworks/blogs/page/roundrep (IDS Replication) • http://www-128.ibm.com/developerworks/blogs/page/gbowerman(IDS Application Development) • http://www-128.ibm.com/developerworks/blogs/page/idsteam(IDS Experts Blog)

More Related