Simple - Srinimf



DB2 Tutorial from SQL - all data object manipulation, creation and use, involve SQL’s.DB2 objects - Database, Tablespaces & Indexspaces - creation & use, and other terminology's associated with databases.DDL - Data Definition LanguageAn introduction to SQLSQL or Structured Query Language isA Powerful language that performs the functions of data manipulation (DML), data definition (DDL) and data control or data authorization (DAL/DCL).A Non procedural language - the capability to act on a set of data and the lack of need to know how to retrieve it. An SQL can perform the functions of more than a procedure.The De Facto Standard query language for RDBMSVery flexibleSQL - Features:-Unlike COBOL or 4GL’s, SQL is coded without data-navigational instructions. The optimal access paths are determined by the DBMS. This is advantageous because the database knows better how it has stored data than the user.What you want and not how to get itSet level processing & multiple row processingSQL - Types (based on the functionality)Data Definition Language (DDL)-Create, Alter and DropData Manipulation Language (DML)-Select, Insert, Update and Delete?Data Control Language (DCL)-Grant and RevokeSQL - Types (Others)?Static or Dynamic SQL?Embedded or Stand-alone SQLThe following are the Operations that can be performed by a SQL on the database tables:SelectUnionJoinTopics dealt with, in DB2 objectsStogroup, Databases, Tablespaces (types, creation and modification)Indexspaces (creation and modification)Some more terms associated with tablespacesDB2 ObjectsThe DB2 Object HierarchyStogroupIt is a collection of direct access volumes, all of the same device typeThe option is defined as a part of tablespace definitionWhen a given space needs to be extended, storage is acquired from the appropriate stogroupDB2 - IBM’s Relational DBMSPrerequisite for this courseThe participant should be exposed to :IBM Mainframe ConceptsCOBOL and File Handling ConceptsVSAMDatabaseA collection of logically related objects - like Tablespaces, Indexspaces, Tables etc.Not a physical kind of object - may occupy more than one disk spaceA STOGROUP & BUFFERPOOL (is buffer area used to maintain recently accessed table and index pages) must be defined for each database.Stogroup and user-defined VSAM are the two storage allocations for a DB2 dataset definition.In a given database, all the spaces need not have the same stogroupThese are, in a sense, the most physical of various storage objects in DB2More than one volume can be defined in a stogroup. DB2 keeps track of which volume was defined first & uses that volume.TablespacesLogical address space on secondary storage to hold one or more tablesA ‘SPACE’ is basically an extendable collection of pages with each page of size 4K or 32K bytes.It is the storage unit for for recovery and reorganizing purposeThree Type of Tablespaces - Simple, Partitioned & SegmentedSimple TablespaceCan contain more than one stored tableDepending on application, storing more than one Table might enable faster retrieval for joins using these tablesUsually only one is preferred. This is because a single page can contain rows from all tables defined in the database.LOAD with replace option deletes all dataSegmented TablespacesCan contain more than one stored table, but in a segmented spaceA ‘Segment’ consists of a logically contiguous set of ‘n’ pages.Segsize parameter decides the allocation size for the tablespaceNo segment is allowed to contain records for more than one tableSequential access to a particular table is more efficientMass Delete is much more efficient than in any other TablespaceReorganizing the tablespace will restore every table to its clustered orderLock Table on table locks only the table, not the entire tablespaceIf a table is dropped, the space for that table can be reclaimed with minimum reorgPartitioned TablespacesPrimarily used for Very large tablesOnly one table in a partitioned TS; 1 to 64 partitions/TSNumpart parameter specifies the no. of partitionsIt is partitioned in accordance with value ranges for single or a combination of columns. Hence these column(s) cannot be updatedIndividual partitions can be independently recovered and reorganizedDifferent partitions can be stored on different storage groups for efficient access.Tablespace parameters to be specified for TS creationLOCKSIZE - indicates the type of locking DB2 performs for the given TSPageTableTablespaceANY - DB2 decides the starting pageData Definition LanguageCREATEThis statement is used to create objectsSyntax : For Creating a TableCREATE TABLE <tabname> (Col Definitions)PRIMARY KEY(Columns) / FOREIGN KEYUNIQUE (Colname) (referential constraint)[LIKE Table name / View name][IN Database Tablespace Name ]Foreign Key references dbname.table on ‘relation condition for delete’Table1 references table2(target) - Table2’s Primary key is the foreign key defined in Table1The Conditions that can be used are CASCADE, RESTRICT & SET NULL (referential constraint for the foreign key definition)Inserting (or updating ) rows in the target is allowed only if there are no rows in the referencing tableALTERThis statement is used for altering all DB2 objectsSyntax : For altering a TableALTER TABLE <Tablename>ADD Column Data-type [ not null with default]Alter allows primary & Foreign key specifications to be changedIt does not support changes to width or data type of a column or dropping a columnDROPThis statement is used for dropping all DB2 objectsSyntax : For dropping a tableDROP TABLE <Tablename>Some general rules for RI & Table ParametersAvoid nulls in columns participating in Arithmetic logic or comparisonsPrimary key cols cannot be nullsLimit referential structures to no more than three levels in a directionUse DB2’s inherent features rather than program coded RI’s.SQL - Selection & ProjectionSelect retrieves a specific number of rows from a table?Projection operation retrieves a specified subset of columns(but all rows) from the tableE.g.. : SELECT CUST_NO, CUST_NAME FROM CUSTOMER;?The WHERE clause defines the Predicates for the SQL operation.The above WHERE clause can have multiple conditions using AND & OR .Other ClausesMany other clauses can be used in conjunction with the WHERE clause to code the required predicate, some are :-Between / Not BetweenIn / Not InLike / Not LikeIS NULL / IS NOT NULLSELECT using a range :Between ClauseE.g. SELECT CUST_NO, CUST_NAME, CUST_ADDR FROM CUSTOMERWHERE CUST_NO BETWEEN 1000 AND 2000;In ClauseE.g. SELECT CUST_NO, CUST_NAME, CUST_ADDR FROM CUSTOMERWHERE CUST_NO IN(1000, 1001,1002);Select clause (contd...)Like ClauseE.g. SELECT CUST_NO, CUST_NAME, CUST_ADDRFROM CUSTOMERWHERE CUST_ID like/not like ‘425%’Note :- ‘_’ for a single char ; ‘%’ for a string of charsEscape ‘\’ - escape char; if precedes ‘_’ or ‘%’ overrides their meaningNULL Clause : To check null the syntax is ‘IS NULL’E.g. SELECT CUST_NO, CUST_NAME, ORDER_NOWHERE ORDER_NO IS NULL;However if there are null values for ORDER_NO, then these are always evaluated as a ‘Not True’ condition in a Query.Order by and Group by clauses:Order by sorts retrieved data in the specified order; uses the WHERE clauseGroup by operator causes the table represented by the FROM clause to be rearranged into groups, such that within one group all rows have the same value for the Group by column (not physically in the database). The Select clause is applied to the grouped data and not to the original table.Here ‘HAVING’ is used to eliminate groups, just like WHERE is used for rows.Order by and Group by clauses (contd...)E.g. SELECT ORDER_NO, SUM(NO_PRODUCTS)FROM ORDERGROUP BY ORDER_NOHAVING AVG(NO_PRODUCTS) < 10ORDER BY ORDER_NO ;FunctionsTypes are two :Column FunctionScalar FunctionColumn FunctionsCompute from a group of rows aggregate value for a specified column(s)AVG, COUNT, MAX, MIN, SUMScalar FunctionsAre applied to a column or expression and operate on a single value.CHAR, DATE, DAY(S), DECIMAL, DIGITS, FLOAT, HEX, HOUR, INTEGER, LENGTH, MICROSECOND, MINUTE, MONTH, SECOND, SUBSTR, TIME, TIMESTAMP, VALUE, VARGRAPHIC, YEARComplex SQL’sOne terms a SQL to be complex when data that is to be retrieved comes from more than one tableSQL provides two ways of coding a complex SQLSubqueries andJoinsSubqueriesNested Select statementsSpecified using the IN(or NOT IN) predicate, equality or non-equality predicate(‘=‘ or ‘<>‘) and comparative operator(<, <=, >, >=)When using the equality, non-equality or comparative operators, the inner query should return only a single valueE.g. SELECT CUST_NO, CUST_NAMEFROM CUSTOMERWHERE ORDER_NO IN (SELECT ORDER_NO FROM ORDER WHERE NO_PRODUCTS <5);E.g. SELECT CUST_NO, CUST_ADDRFROM CUSTOMERWHERE ORDER_NO =(SELECT ORDER_NO FROM ORDERWHERE NO_PRODUCTS = 5);The nested loop statements gives the user the flexibility for querying multiple tablesA specialized form is Correlated Subquery - the nested select statement refers back to the columns in previous select statementsIt works on Top-Bottom-Top fashionNon-correlated Subquery works in Bottom-to-Top fashionCorrelated SubqueryE.g. SELECT A.CUST_NAME A.CUST_ADDRFROM CUSTOMER A WHERE A.ORDER_NO IN(SELECT ORDER_NOFROM CUSTOMER BWHERE A.CUST_ID = B.CUST_ID)ORDER BY A.CUST_ID, A.CUST_NO ;Corelated Subquery using EXISTS clause :E.g. SELECT CUST_NO, CUST_NAMEFROM CUSTOMER AWHERE EXISTS(SELECT * FROM ORDER BWHERE B.ORDER_NO = A.ORDER_NOAND B.ORDER_NO = 5);Multiple levels of SubqueryE.g. SELECT CUST_NO, CUST_NAME, CUST_ADDRFROM CUSTOMERWHERE ORDER_NO IN(SELECT ORDER_NO FROM ORDERWHERE PROD_ID IN(SELECT PROD_IDFROM PRODUCTSWHERE PROD_NAME = ‘NUTS’));JoinsOUTER JOIN : For one or more tables being joined, both matching and non-matching rows are returned. Duplicate columns may be eliminatedThe non-matching columns will have nulls in them.INNER JOIN: Here there is a possibility one or more of the rows from either or both tables being joined will not be included in the table that results from the join operationOther DML Statement’sINSERTE.g..: INSERT INTO Tablename(column1, column2, column3 ,......)VALUES( value1, value2, value3 ,........)If any column is omitted in an INSERT statement and that column is NOT NULL, then INSERT fails; if null it is set to nullDML statements (contd...)If the column is defined as NOT NULL BY DEFAULT, it is set to that default valueOmitting the list of columns is equivalent to specifying all values?SELECT - INSERTE.g. INSERT INTO TEMP (A#, B)SELECT A#, SUM(B)FROM TEMP1 GROUP BY A# ;DML statements (contd...)UPDATEE.g.. UPDATE tablenameSET Columnname(s) = scalar expression WHERE [ condition ]Single or Multiple row updatesUpdate with a SubqueryDML statements (contd...)DELETEE.g. DELETE FROM TablenameWHERE [condition ];Single or multiple row delete or deletion of all rowsStatic SQLHard-coded into an application programcannot be modified during the program’s execution except for changes to the values assigned to the host variablesCursors are used to access set-level data (i.e when a SQL SELECT returns more than 1 row)The general form is EXEC SQL[SQL statements]END-EXEC.Dynamic SQLStatements can change throughout the program’s executionWhen the SQL is bound, the application plan or package that is created does not contain the same information as that for a static SQL programThe access paths cannot be determined before executionWhat is an Index ?‘An index is an ordered set of pointers to rows of a base table’.Or‘An Index is a balanced B-tree structure that orders the values of columns in a table’Why an Index ?‘One can access data directly and more efficiently’Each index is based on the values of data in one or more columns. An index is an object that is separate from the data in the table.When you define an index using the CREATE INDEX statement, DB2 builds this structure and maintains it automatically.Indexes can be used by DB2 to improve performance and ensure uniqueness.In most cases, access to data is faster with an index. A table with a unique index cannot have rows with identical keys.Syntax : For creation of an IndexCREATE INDEX <indexname> ON <tabname>(colname asc/desc)Other DB2 ObjectsVIEWSIt is a logical derivation of a table from other table/tables. A View does not exist in its own right.They provide a certain amount if logical independenceThey allow the same data to be seen by different users in different waysIn DB2 a view that is to accept a update must be derived from a single base tableAliasesMean ‘another name’ for the table.Aliases are used basically for accessing remote tables (in distributed data processing), which add a location prefix to their names.Using aliases creates a shorter name.SynonymAlso means another name for the table, but is private to the user who created it.Syntax:CREATE VIEW <Viewname> (<columns>)AS Subquery (Subquery - SELECT FROM other Table(s))CREATE ALIAS <Aliasname> FOR <Tablename>CREATE SYNONYM <Synonymname> FOR <Tablename>Application programming using DB2Application environments supporting DB2 :IMS(Batch/Online), CICS, TSO(Batch/Online)CAF - Call Attachment FacilityAll DB2 application types can execute concurrentlyHost Language support - COBOL, PL/1, C, Fortran or Assembly langSteps involved in creating a DB2 applicationCoding the applicationusing Embedded SQLusing Host variables (DCLGEN)using SQLCApre-compile the programcompile & link edit the programbindNote : Cursors can also be usedEmbedded SQL statementsIt is like the file I/ONormally the embedded SQL statements contain the host variables coded with the INTO clause of the SELECT statement.They are delimited with EXEC SQL ...... END EXEC.?E.g. EXEC SQLSELECT Empno, Empname INTO :H-empno, :H-empnameFROM EMPLOYEEWHERE empno = 1001END EXEC.Host VariablesThese are variables(or rather area of storage) defined in the host language to use the predicates of a DB2 table. These are referenced in the SQL statement.A means of moving data from and to DB2 tablesDCLGEN produces host variables, the same as the columns of the tableHost variables can be usedIn WHERE Clause of Select, Insert, Update & Delete‘INTO’ Clause of Select & Fetch statementsAs input of ‘SET’ Clause of Update StatementsAs Input for the ‘VALUES’ Clause of Insert statementsAs Literals in Select list of a Select StatementE.g. SELECT Cust_No, Cust_name, Cust_addrINTO :H-CUST-NO, :H-CUST-NAME,:H-CUST-ADDRFROM CUSTOMERWHERE CUST_NO = :H-CUST-NO;DCLGENIssued for a single tablePrepares the structure of the table in a COBOL copybookThe copybook contains a ‘SQL DECLARE TABLE’ statement along with a working storage host variable definition for the tableSQLCAAn SQLCA is a structure or collection of variables that is updated after each SQL statement executes.? An application program that contains executable SQL statements must provide exactly one SQLCA.Structure of the SQLCA (for COBOL)01 SQLCA.05 SQLCAID PIC X(8).05 SQLCABC PIC S9(9) COMP05 SQLCODE PIC S9(9) COMP05 SQLERRM.:05 SQLWARN.10 SQLWARN0 PIC X(1).:10 SQLWARNA PIC X(1).10 SQLSTATE PIC X(5).CursorsUsed when a large number of rows are to be SelectedCan be likened to a pointerCan be used for modifying data using ‘FOR UPDATE OF’ clauseThe four (4) Cursor control statements are –Declare : name assigned for a particular SQL statementOpen : readies the cursor for row retrieval; sometimes builds the result table. However it does not assign values to the host variablesFetch : returns data from the results table one row at a time and assigns the value to specified host variablesClose : releases all resources used by the cursorOPENE.g. - For the Open statementCursors (contd...)FETCHE.g. - For the Fetch statementCursors (contd...)CLOSEE.g. - For the Close statementEXEC SQLCLOSE EMPCUREND EXEC.Cursors (contd...)WHENEVERE.g. - For the Whenever ClauseEXEC SQLWHENEVER NOT FOUNDGo To Close-EMPCUREND EXEC.Cursors (contd...)UPDATEE.g. - For the Update statement using cursorsEXEC SQLUPDATE EMPSet Job = :New-jobWHERE current of EMPCUREND EXEC.Cursors (contd...)DELETEE.g. - For the Delete statement using cursorsEXEC SQLDELETE FROM EMPWHERE current of EMPCUREND EXEC.Application development guidelinesCode modular DB2 programs and make them as small as possibleUse unqualified SQL statements; this enables movement from one environment to another(test to production)Never use ‘Select *’ in an embedded SQL program;Use joins rather than subqueriesUse WHERE clause and filter out dataUse cursors when fetching multiple rows, though they add overheadsUse FOR UPDATE OF clause for UPDATE or DELETE with cursor - this ensures data integrity.Use Inserts minimally ; use LOAD utility instead of INSERT, if the inserts are not application dependentQMF - Query Management FacilityIt is an MVS- and VM- based query toolallows end users to enter SQL queries to produce a variety of reports and graphs as a result of this queryQMF queries can be formulated in several ways : by direct SQL statements, by means of relational prompted query interface or by query-by-example (QBE). QBE is similar to SQL in some ways but more user friendlySPUFI - SQL Processing Using File InputSupports the online execution of SQL statements from a TSO terminalUsed for developers to check SQL statements or view table detailsSPUFI menu contains the input file in which the SQL statements are coded, option for default settings and editing and the output file.PrecompileSearches all the SQL statements and DB2 related INCLUDE members and comments out every SQL statement in the programThe SQL statements are replaced by a CALL to the DB2 runtime interface module, along with parameters.All SQL statements are extracted and put in a Database Request Module (DBRM)Places a timestamp in the modified source and the DBRM so that these are tied. If there is a mismatch in this a runtime error of ‘-818‘, timestamp mismatch occursAll DB2 related INCLUDE statements must be placed between EXEC SQL & END EXEC keywords for the precompiler to recognize themCompile & Link?Modified precompiler COBOL output is compiled?Compiled source is link edited to an executable load module?Appropriate DB2 host language interface module should also be included in the link edit step(i.e DSNELI)BindA type of compiler for SQL statementsIt reads the SQL statements from the DBRM and produces a mechanism to access data (in an efficient manner) as directed by the SQL statements being boundChecks syntax, checks for correctness of table & column definitions against the catalog information & performs authorization validationBind TypesBIND PLAN : accepts as input one or more DBRMs and outputs an application plan containing executable logic representing optimized access paths to DB2 data.BIND PACKAGE : accepts as input a single DBRM and produces a single package containing the optimized access path. The PLAN in this case contains a reference to the physical location of the package(s).What is a Package ?It is a single bound DBRM with optimized access pathsIt also contains a location identifier, a collection identifier and a package identifierA package can have multiple versions, each with its own version identifierAdvantages of PackageReduced bind timeCan specify bind options at the programmer levelVersioningProvides remote data access(in version DB2 V2.3 or higher)What is a Plan ?An application plan contains one or both of the following elements:A list of package namesThe bound form of SQL statements taken from one or more DBRMs.Every DB2 application requires an application plan.Plans are created using the DB2 subcommands BIND PLANFor the following refer handoutList of common SQL return codes and solutionsDB2 UtilitiesDB2 System administrationDB2 UTILITIESCheckCopy/MergecopyRecoverLoadReorgRunstatsExplainCheckChecks the integrity of DB2 data structuresChecks the referential integrity between two tables and also checks DB2 indexes for consistencyCan delete invalid rows and copies them to a exception tableUse CHECK DATA when loading a table without specifying the ‘ENFORCE CONSTRAINTS’ option or after the partial recovery of tablespaces in a referential setCopyUsed to create an imagecopy for the complete tablespace or a partition of the tablespace - full imagecopy or incremental imagecopyEvery successful execution of COPY utility places in the table SYSIBM.SYSCOPY, atleast one row that indicates the status of the imagecopy? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download