SQL database lecture notes

what is database sql programming and lecture notes on relational database design and sql, and also sql answers and database programming using sql pdf free download
Dr.LeonBurns Profile Pic
Dr.LeonBurns,New Zealand,Researcher
Published Date:21-07-2017
Your Website URL(Optional)
Comment
IBM i Version 7.2 Database SQL programming IBMSQL programming ® ® The DB2 for IBM i database pr ovides a wide range of support for uctur Stred Query Language (SQL). The examples of SQL statements shown in this topic collection e ar based on the sample tables and assume that the following statements e artrue: v Each SQL example is shown on several lines, with each clause of the statement on a separate line. v SQL keywords are highlighted. v Table names pr ovided in the sample tables use the schema CORPDA TA. Table names that e arnot found in the Sample ables T should use schemas you eate. cr v The SQL naming convention is used. v The APOST and APOSTSQL precompiler options ar e assumed although they e arnot the default options in COBOL. Character string literals within SQL and host language statements e ardelimited by single-quotation marks ('). v A sort sequence of HEX is used, unless otherwise noted. Whenever the examples vary om fr these assumptions, it is stated. Because this topic collection is for the application ogrammer pr , most of the examples e arshown as if they were written in an application ogram. pr However, many examples can be slightly changed and un r interactively by using interactive SQL. The syntax of an statement, SQL when using interactive SQL, differs slightly fr om the format of the same statement when it is embedded ogram. in a pr Note: By using the code examples, you ee agrto the terms of the “Code license and disclaimer information” on page 374. Related concepts: Embedded SQL programming Related reference: “DB2 for i sample tables” on page 346 These sample tables e arreferred to and used in the SQL programming and the SQL reference topic collections. DB2 for i5/OS SQL reference What's new for IBM i 7.2 Read about new or significantly changed information for the prSQL ogramming topic collection. TRUNCATE statement The TRUNCATE statement can be used to delete ows all rfrom a table. For mor e information, see “Removing rows from a table using the TRUNCA TE statement” on page 120. System name for tables, views, and indexes When creating a table, view , or index, the system name for the object can be specified on eatethe cr statement. For mor e information, see “Creating a table” on page 17 and “Creating and using views” on page 49. © Copyright IBM Corp. 1998, 2013 1 Connect by Hierarchical queries can be defined using the CONNECT syntax. BY For mor e information, see “Using recursive queries” on page 84. Insert from a remote table You can insert into a local table with data etrieved r from a non-local table. For mor e information, see “Inserting data fr om a emote r database” on page 11.1 CREATE TABLE referencing a remote table You can cr eate a local table with the definition and data etrieved r from a non-local table. For mor e information, see “Creating a table with emote r server data” on page 24. Defaults for procedure parameters and using parameter names in CALL You can define parameters for SQL and external pr ocedures to have default values. Parameters with default values can be omitted when calling the ocedur pr e. The CALL statement can specify parameter names for any guments. ar For mor e information, see “Defining a pr ocedure with default parameters” on page 157. Defaults for function parameters and using parameter names for function invocation You can define default parameters for SQL and external functions. For mor e information, see “Defining UDFs with default parameters” on page 207. You can invoke a function by specifying parameter names for arguments. For mor e information, see “Invoking UDFs with named guments” ar on page 218. Array support for SQL functions You can use arrays in SQL scalar functions. For mor e information, see “Array support in SQL procedures and functions” on page 233. Dynamic compound statement SQL routine logic can be used in a compound statement that is dynamically un. For r mor e information, see “Dynamic compound statement” on page 191. Multiple event triggers A trigger can be defined for mor e than one event. For mor e information, see “Multiple event SQL triggers” on page 224. Obfuscation The content of an SQL procedure, function, or trigger can be obfuscated. For e mor information, see “Obfuscating an SQL routine or SQL trigger” on page 237. SQL routine step debug An SQL routine can be debugged by stepping ectly dir through the SQL debug view. For mor e information, see “Debugging an SQL routine” on page 235. 2 IBM i:SQL programming SQL and external routine management Procedures and functions e artied to system objects that can be administer ed with CL commands. For more information, see “Managing SQL and external outine r objects” on page 238. TRANSFER OWNERSHIP The TRANSFER OWNERSHIP statement assigns a new owner for a database object. For e mor information, see “Security for SQL objects” on page 136. Column masks and row permissions Column masks and ow r permissions can be used to estrict r data fr om being seen by certain users. For more information, see “Column masks and ow r permissions” on page 137. RUNSQLSTM OPTION parameter The RUNSQLSTM command does not need to generate a listing. For e information, mor see “Using the SQL statement processor” on page 304. RUNSQL This new CL command runs a single SQL statement. For mor e information, see “Using the RUNSQL CL command” on page 308. SQL XML Programming All of the information about using XML with SQL has moved to SQL XML Programming. What's new as of October 2016 SQL procedures, functions, and triggers can use the INCLUDE statement to e shar common code. For more information, see “Using the INCLUDE statement” on page 228. What's new as of April 2015 The OR REPLACE option has been added to the CREA TE TABLE statement. For mor e information, see “Using CREATE OR REPLACE ABLE” T on page 47. What's new as of October 2014 Pipelined table functions A pipelined SQL table function is a mor e flexible version of a table function. For e mor information, see “Example: SQL table UDFs” on page 195. SQL variable debug for SQL routines Debugging of SQL routines allows you to display values of SQL variables. For mor e information, see “Debugging an SQL routine” on page 235. Listing option available for RUNSQL command A listing can be equested r for the RUNSQL command. For mor e information, see “Using the RUNSQL CL command” on page 308. SQL programming 3 How to see what's new or changed To help you see wher e technical changes have been made, the information center uses: v The image to mark wher e new or changed information begins. v The image to mark wher e new or changed information ends. In PDF files, you might see evision r bars () in the left gin marof new and changed information. To find other information about what's new or changed elease, this r see the Memo to users . PDF file for SQL programming You can view and print a PDF file of this information. To view or download the PDF version of this document, SQL select programming. Saving PDF files To save a PDF on your workstation for viewing or printing: 1. Right-click the PDF link in your owser br . 2. Click the option that saves the PDF locally . 3. Navigate to the dir ectory in which you want to save the . PDF 4. Click Save. Downloading Adobe Reader You need Adobe Reader installed on your system to view or print these YPDFs. ou can download a eefr copy from the Adobe Web site (http://get.adobe.com/reader/) . Introduction to DB2 for i Structured Query Language Structured Query Language (SQL) is a standar dized language for defining and manipulating data in a relational database. These topics describe the IBM i implementation of the using SQL the DB2 for i database and the IBM DB2 Query Manager and Development SQL Kit for i licensed ogram. pr SQL manages information based on the elational r model of data. SQL statements can be embedded in high-level languages, dynamically pr epared and un, r or un r interactively. For information about embedded SQL, see Embedded SQL programming. SQL consists of statements and clauses that describe what you want to do with the data in a database and under what conditions you want to do it. SQL can access data in a emote r relational database, using the IBM Distributed Relational Database ™ ® Architecture (DRDA ). Related concepts: Distributed database pr ogramming Related reference: “Distributed relational database function and SQL” on page 310 A distributed relational databaseconsists of a set of SQL objects that ar e spread across interconnected computer systems. 4 IBM i:SQL programmingSQL concepts DB2 for i SQL consists of several main parts, such as SQL runtime support, pr ecompilers, and interactive SQL. v SQL runtime support SQL run time parses SQL statements and uns r any SQL statements. This support is part of the IBM i licensed program, which allows applications that contain SQL statements to be un r on systems wher e the IBM DB2 Query Manager and SQL Development Kit for i licensed ogram pr is not installed. v SQL precompilers SQL precompilers support pr ecompiling embedded SQL statements in host languages. The following languages are supported: – ILE C – ILE C++ – ILE COBOL – COBOL – PL/I – RPG III (part of RPG) – ILE RPG The SQL host language pr ecompilers prepare an application pr ogram that contains SQL statements. The host language compilers then compile the ecompiled pr host sour ce programs. For mor e information about pr ecompiling, see Preparing and unning r a pr ogram with SQL statements in the Embedded SQL programming information. The pr ecompiler support is part of the IBM DB2 Query Manager and SQL Development Kit for i licensed ogram. pr v SQL interactive interface The SQL interactive interface allows you to eate cr and un r SQL statements. For mor e information about interactive SQL, see “Using interactive SQL” on page 293. Interactive SQL is part of the IBM DB2 Query Manager and SQL Development Kit for i licensed ogram. pr v Run SQL Scripts ® The Run SQL Scripts window in System Navigator i allows you to eate, cr edit, un, r and tr oubleshoot scripts of SQL statements. v Run SQL Statements (RUNSQLSTM) CL command The RUNSQLSTM command can be used un to ra series of SQL statements that ar e stored in a sour ce file or a sour ce stream file. For mor e information about the RUNSQLSTM command, “Using see the SQL statement processor” on page 304. v Run SQL (RUNSQL) CL command The RUNSQL command can be used to un r a single SQL statements. For mor e information about the RUNSQL command, see “Using the RUNSQL CL command” on page 308. v DB2 Query Manager DB2 Query Manager pr ovides a pr ompt-driven interactive interface that allows you to eate cr data, add data, maintain data, and un r reports on the databases. Query Manager is part of the IBM DB2 Query Manager and SQL Development Kit for i licensed ogram. pr For mor e information, see Query Manager Use . v SQL REXX interface The SQL REXX interface allows you to un r SQL statements in a REXX ocedur pr e. For mor e information about using SQL statements in REXX pr ocedures, see Coding SQL statements in REXX applications in the Embedded SQL programming information. v SQL call level interface The DB2 for i database supports the SQL call level interface. This allows users of any of the ILE languages to access SQL functions directly through bound calls to a service ogram pr that is pr ovided SQL programming 5by the system. Using the SQL call level interface, you can perform all the functions SQL without the need to pr ecompile. This is a standar d set of pr ocedure calls to pr epare SQL statements, run SQL statements, fetch ows r of data, and even perform advanced functions, such as accessing the catalogs and binding pr ogram variables to output columns. For a complete description of all the available functions and their syntax, SQL see call level interface in the Database section of the IBM i Information Center . v Process Extended Dynamic SQL (QSQPRCED) API This application pr ogramming interface (API) pr ovides an extended dynamic SQL capability. You can prepare SQL statements into an SQL package and un r them by using this API. Statements that ear prepared into a package by this API persist until the package or statement is explicitly opped. dr For more information about the QSQPRCED API, see Process Extended Dynamic SQL (QSQPRCED) API. For general information about APIs, see Application programming interfaces. v Syntax Check SQL Statement (QSQCHKS) API This API syntax checks SQL statements. For mor e information about the QSQCHKS API, see Syntax Check SQL Statement (QSQCHKS) API. For general information about APIs, see Application programming interfaces. v DB2 Multisystem This feature of the operating system allows your data to be distributed oss acr multiple systems. It is also allows the definition of partitioned tables. For e mor information, see DB2 Multisystem. v DB2 Symmetric Multipr ocessing This feature of the operating system ovides pr the query optimizer with additional methods for retrieving data that include parallel ocessing. pr Symmetric multipr ocessing (SMP) is a form of parallelism achieved on a single system wher e multiple pr ocessors (CPU and I/O ocessors) pr that share memory and disk esour r ce work simultaneously towar d achieving a single end esult. r This parallel processing means that the database manager can have e mor than one (or all) of the system processors working on a single query simultaneously . For mor e information, see Controlling parallel processing for queries in the Database performance and query optimization topic collection. SQL relational database and system terminology In the elational r model of data, all data is ceived per as existing in tables. DB2 for i objects e created ar and maintained as system objects. The following table shows the elationship r between system terms and SQL relational database terms. Table 1. Relationship of system terms to SQL terms System terms SQL terms Library. Groups related objects and allows you to findSchema. Consists of a library , a journal, a journal the objects by name. receiver, an SQL catalog, and optionally a data dictionary. A schema groups related objects and allows you to find the objects by name. Physical file. A set of ecor r ds. Table. A set of columns and ows. r Record. A set of fields. Row. The horizontal part of a table containing a serial set of columns. Field. One or mor e characters of elated r information of Column. The vertical part of a table of one data type. one data type. Logical file. A subset of fields and ecor r ds of one or View. A subset of columns and ows r of one or mor e more physical files. tables. SQL package. An object type that is used un to rSQL Package. An object type that is used un to rSQL statements. statements. User Profile Authorization name or Authorization ID. Related concepts: Distributed database pr ogramming 6 IBM i:SQL programmingSQL and system naming conventions You can use either the system (SYS) or the (SQL) SQL naming convention in DB2 for ogramming. i pr The naming convention used fects af the method for qualifying file and table names and the terms used on the interactive SQL displays. The naming convention used is selected by a parameter on the SQL commands or by using the SET OPTION statement. System naming (SYS) In the system naming convention, tables and other objects SQL in an SQL statement are qualified by schema name in the form: schema/table or schema.table SQL naming (SQL) In the SQL naming convention, tables and other SQL objects in an SQL statement are qualified by schema name in the form: schema.table Related reference: Qualification of unqualified object names Types of SQL statements There are several basic types of SQL statements. They ar e listed her e according to their functions. v SQL schema statements, also known as data definition language (DDL) statements v SQL data and data change statements, also known as data manipulation language (DML) statements v Dynamic SQL statements v Embedded SQL host language statements SQL programming 7SQL schema statements SQL data statements ALTER FUNCTION ALLOCATE CURSOR ALTER MASK ASSOCIATE LOCATORS ALTER PERMISSION CLOSE ALTER PROCEDURE DECLARE CURSOR ALTER SEQUENCE DELETE ALTER TABLE FETCH ALTER TRIGGER FREE LOCATOR COMMENT ON HOLD LOCATOR CREATE ALIAS INSERT CREATE FUNCTION LOCK TABLE CREATE INDEX OPEN CREATE MASK REFRESH TABLE CREATE PERMISSION SELECT INTO CREATE PROCEDURE SET variable CREATE SCHEMA UPDATE CREATE SEQUENCE VALUES INTO CREATE TABLE CREATE TRIGGER CREATE TYPE CREATE VARIABLE CREATE VIEW DROP GRANT LABEL ON RENAME REVOKE TRANSFER OWNERSHIP SQL data change statements SQL connection statements DELETE CONNECT INSERT DISCONNECT MERGE RELEASE TRUNCATE SET CONNECTION UPDATE SQL transaction statements SQL session statements COMMIT DECLARE GLOBAL TEMPORARY TABLE RELEASE SAVEPOINT SET CURRENT DECFLOAT ROUNDING MODE ROLLBACK SET CURRENT DEGREE SAVEPOINT SET CURRENT IMPLICIT XMLPARSE OPTION SET TRANSACTION SET ENCRYPTION PASSWORD SET PATH SET SCHEMA SET SESSION AUTHORIZATION 8 IBM i:SQL programmingDynamic SQL statements Embedded SQL host language statements ALLOCATE DESCRIPTOR BEGIN DECLARE SECTION compound (dynamic) DECLARE PROCEDURE DEALLOCATE DESCRIPTOR DECLARE STATEMENT DESCRIBE DECLARE VARIABLE DESCRIBE CURSOR END DECLARE SECTION DESCRIBE INPUT GET DIAGNOSTICS DESCRIBE PROCEDURE INCLUDE DESCRIBE TABLE SET OPTION EXECUTE SET RESULT SETS EXECUTE IMMEDIATE SIGNAL GET DESCRIPTOR WHENEVER PREPARE SET DESCRIPTOR SQL control statements CALL SQL statements can operate on objects that e ar created by SQL as well as externally described physical files and single-format logical files. They do not efer rto the interactive data definition utility (IDDU) dictionary definition for pr ogram-described files. Pr ogram-described files appear as a table with only a single column. Related concepts: “Data definition language” on page 17 Data definition language (DDL) describes the portion of that SQL creates, alters, and deletes database objects. These database objects include schemas, tables, views, sequences, catalogs, indexes, variables, masks, permissions, and aliases. “Data manipulation language” on page 56 Data manipulation language (DML) describes the portion of that SQL manipulates or contr ols data. Related reference: DB2 for i5/OS SQL reference SQL communication area The SQL communication area (SQLCA) is a set of variables that ovides pr an application pr ogram with information about its execution of SQL statements. The SQLCA is updated at the end of the execution of every SQL statement. Related concepts: SQLCA (SQL communication area) Handling SQL error return codes using the SQLCA SQL diagnostics area The SQL diagnostics area maintained by the database manager ovides pr information about the SQL statement that is most ecently r run. Your application pr ogram can access the SQL diagnostics area using the GET DIAGNOSTICS statement. Related concepts: Using the SQL diagnostics area Related reference: GET DIAGNOSTICS statement SQL programming 9SQL objects SQL objects are schemas, journals, catalogs, tables, aliases, views, indexes, constraints, triggers, masks, permissions, sequences, stor ed procedures, user-defined functions, user -defined types, global variables, and SQL packages. SQL creates and maintains these objects as system objects. Schemas A schema provides a logical gr ouping of SQL objects. A schema consists of a library , a journal, a journal receiver, a catalog, and, optionally , a data dictionary . Tables, views, and system objects (such as ograms) pr can be eated, cr moved, or estor r ed into any system library. All system files can be eated cr or moved into an SQL schema if the SQL schema does not contain a data dictionary . If the SQL schema contains a data dictionary then: v Source physical files or nonsour ce physical files with one member can be eated, cr moved, or estor r ed into an SQL schema. v Logical files cannot be placed in an SQL schema because they cannot be described in the data dictionary. You can cr eate and own many schemas. Journals and journal receivers A journal and a journal receiver are used to ecor r d changes to tables and views in the database. Journals and journal eceivers r are used in pr ocessing the SQL COMMIT, ROLLBACK, SA VEPOINT, and RELEASE SAVEPOINT statements. Journals and journal eceivers r can also be used as audit trails or for forward or backwar d recovery. Related concepts: Journal management Commitment control Catalogs An SQL catalog is a collection of tables and views that describe tables, views, indexes, ocedurpres, functions, sequences, triggers, masks, permissions, variables, constraints, ograms, pr packages, and XSR objects. This information is contained in a set oss-r of creference tables in libraries QSYS and QSYS2. In each SQL schema there is a set of views built over the catalog tables that contains information about the objects in the schema. A catalog is automatically eated cr when you eate cr a schema. You cannot dr op or explicitly change the catalog. Related reference: Catalog Tables, rows, and columns A table is a two-dimensional arrangement of data that consists rows of and columns. The row is the horizontal part containing one or e mor columns. The column is the vertical part containing one or mor e rows of data of one data type. All data for a column must be of the same Atype. table in SQL is a keyed or non-keyed physical file. A materialized query tableis a table that is used to contain materialized data that is derived om one fr or more source tables specified by a select-statement. A partitioned tableis a table whose data is contained in one or e local mor partitions (members). 10 IBM i:SQL programmingRelated concepts: DB2 Multisystem Related reference: Data types “Creating and altering a materialized query table” on page 22 A materialized query tableis a table whose definition is based on esult the rof a query , and whose data is in the form of pr ecomputed results that ar e taken fr om the table or tables on which the materialized query table definition is based. Aliases An alias is an alternate name for a table or .view You can use an alias to efer r to a table or view in those cases e wher an existing table or view can be referred to. Additionally, aliases can efer r to a specific member of a table. An alias can also ee-part be a thr name with an RDB name that efers r to a emote r system. Related reference: Aliases Views A view appears like a table to an application ogram. pr However, a view contains no data and only logically represents one or mor e tables over which it is eated. cr A view can contain all the columns and ows r of the given tables or a subset of them. The columns can be arranged differently in a view than they e ar in the tables om fr which they e artaken. A view in SQL is a special form of a nonkeyed logical file. Related reference: Views Indexes An SQL index is a subset of the data in the columns of a table e that logically ar arranged in either ascending or descending or der. Each index defines a set of columns or essions expr as keys. These keys e arused for or dering, grouping, and joining. The index is used by the system for faster etrieval. data r DB2 for i supports two types of indexes: binary radix ee indexes tr and encoded vector indexes (EVIs). Creating an index is optional. You can cr eate any number of indexes. You can cr eate or dr op an index at any time. The index is automatically maintained by the system. However , because the indexes ear maintained by the system, a ge larnumber of indexes can adversely fect af the performance of the applications that change the table. Related concepts: Creating an index strategy Related reference: CREATE INDEX Constraints A constraint is a ule r enforced by the database manager to limit the values that can be inserted, deleted, or updated in a table. DB2 for i supports the following constraints: v Unique constraints SQL programming 11A unique constraintis the ule r that the values of the key e valid ar only if they e arunique. You can create a unique constraint using the CREA TE TABLE or ALTER TABLE statement. Although the CREATE INDEX statement can eate cr a unique index that also guarantees uniqueness, such an index is not a constraint. Unique constraints ar e enforced during the execution of INSER T and UPDA TE statements. A PRIMARY KEY constraint is a form of the UNIQUE constraint. The ference dif is that a PRIMAR Y KEY cannot contain any nullable columns. v Referential constraints A referential constraintis the ule r that the values of the eign for key ar e valid only if one of the following conditions is met: – They appear as values of a ent parkey. – Some component of the for eign key is null. Referential constraints ar e enforced during the execution of INSER T, UPDATE, and DELETE statements. v Check constraints A check constraintis the ule r that limits the values allowed in a column oup or gr of columns. You can create a check constraint using the CREA TE TABLE or ALTER TABLE statement. Check constraints ear enforced during the execution of INSER T and UPDA TE statements. o T satisfy the constraint, each owr of data inserted or updated in the table must make the specified condition either TRUE or unknown (because of a null value). Related reference: “Constraints” on page 147 The DB2 for i database supports unique, eferential, r and check constraints. Triggers A trigger is a set of actions that uns r automatically whenever a specified event occurs to a specified table or view. An event can be an insert, an update, a delete, ead or a operation. r A trigger can un r either befor e or after the event. DB2 for i supports SQL insert, update, and delete triggers and external triggers. Related tasks: Triggering automatic events in your database Stored procedures A stored procedure is a pr ogram that can be called with the SQL CALL statement. DB2 for i supports external ocedur pr es and SQL procedures. An external pr ocedure can be any system program, service pr ogram, or REXX pr ocedure. It cannot be a System/36 ogram pr or pr ocedure. An SQL procedure is defined entir ely in SQL and can contain SQL statements, including SQL control statements. Related concepts: “Stored procedures” on page 151 A procedure (often called a stor ed procedure) is a pr ogram that can be called to perform operations. A procedure can include both host language statements and statements. SQL Procedures in SQL provide the same benefits as pr ocedures in a host language. Sequences A sequence is a data ea ar object that pr ovides a quick and easy way of generating unique numbers. You can use a sequence to eplace r an identity column or a user -generated numeric column. A sequence has uses similar to these alternatives. Related reference: 12 IBM i:SQL programming“Creating and using sequences” on page 26 Sequences are similar to identity columns in that they both generate unique values. However , sequences are objects that e arindependent of any tables. You can use sequences to generate values quickly and easily. Global variables A global variableis a named variable that can be eated, cr accessed, and modified using SQL. A global variable can pr ovide a unique value for a session. The variable can be used as part of any expression in places such as a query , a cr eate view, or an insert statement. User-defined functions A user-defined functionis a pr ogram that can be called like any built-in functions. DB2 for i supports external functions, SQL functions, and sour ced functions. An external function can be any system ILE pr ogram or service pr ogram. An SQL function is defined entir ely in SQL and can contain SQL statements, including SQL control statements. A sourced function is built over any built-in or any existing user-defined function. You can cr eate a scalar function or a table function as either an SQL function or an external function. Related concepts: “Using user-defined functions” on page 192 In writing SQL applications, you can implement some actions or operations as a -defined user function (UDF) or as a subr outine in your application. Although it might appear easier to implement new operations as subr outines, you might want to consider the advantages of using a UDF instead. User-defined types A user-defined typeis a data type that you can define independently of the data types e that provided ar by the database management system. Distinct data types map to built-in types. Array data types e ardefined using a built-in type as the element type and a maximum dinality car value. Related concepts: “User-defined distinct types” on page 252 A user-defined distinct type (UDT) is a mechanism to extend DB2 capabilities beyond the built-in data types that ar e available. XSR objects An XSR objectis one or mor e XML schema documents that have been egister r ed in the XML schema repository with the same name. You can use an XSR object during validation of an document XML or during annotated XML schema decomposition. SQL packages An SQL package is an object that contains the contr ol structure produced when the SQL statements in an application program are bound to a emote r relational database management system (DBMS). The DBMS uses the contr ol structure to pr ocess SQL statements encountered while unning r the application program. SQL packages are created when a elational r database name (RDB parameter) is specified on eate a CrSQL (CRTSQLxxx) command and a ogram pr object is eated. cr Packages can also be eated cr with the Cr eate SQL Package (CRTSQLPKG) command. SQL programming 13Note: The xxx in this command efers r to the host language indicators: CI for ILE C, CPPI for ILE C++, ® CBL for COBOL, CBLI for ILE COBOL, PLI for PL/I, RPG for RPG/400 , and RPGI for ILE RPG. SQL packages can also be eated cr with the Pr ocess Extended Dynamic SQL (QSQPRCED) API. The SQL packages mentioned within this topic collection efer r exclusively to distributed ogram pr SQL packages. The QSQPRCED API uses SQL packages to pr ovide extended dynamic SQL support. Related reference: “Distributed relational database function and SQL” on page 310 A distributed relational databaseconsists of a set of SQL objects that ar e spread across interconnected computer systems. Process Extended Dynamic SQL (QSQPRCED) API Application program objects Several objects ar e created when a DB2 for i application ogram pr is being pr ecompiled. DB2 for i supports both non-ILE and ILE ecompilers. pr Application programs can be either distributed or nondistributed. With the DB2 for i database, you might need to manage the following objects: v The original sour ce v Optionally, the module object for ILE ograms pr v The program or service pr ogram v The SQL package for distributed pr ograms With a nondistributed non-ILE DB2 for ogram, i pr you must manage only the original ce sour and the resulting program. The following figur e shows the objects involved and the steps that happen during the precompile and compile pr ocesses for a nondistributed non-ILE DB2 for ogram. i pr The user sour ce file precompiles the sour ce to a temporary sour ce file member . This member is then compiled into ogram. a pr With a nondistributed ILE DB2 for ogram, i pr you might need to manage the original ce, sour the modules, and the esulting r program or service pr ogram. The following figur e shows the objects involved and the steps that happen during the ecompile pr and compile pr ocesses for a nondistributed ILE DB2 for i program when OBJTYPE(PGM) is specified on the ecompile pr command. The user sour ce file precompiles the sour ce to a temporary sour ce file member . This member is then compiled into a module that binds to a ogram. pr 14 IBM i:SQL programmingWith a distributed non-ILE DB2 for ogram, i pr you must manage the original sour ce, the esulting r program, and the esulting r package. The following figur e shows the objects and the steps that occur during the pr ecompile and compile pr ocesses for a distributed non-ILE DB2 for ogram. i pr The user source file pr ecompiles the sour ce to a temporary sour ce file member . This member is then compiled into a program. After the pr ogram is cr eated, an SQL package is cr eated to hold the ogram. pr With a distributed ILE DB2 for ogram, i pr you must manage the original sour ce, module objects, the resulting program or service pr ogram, and the esulting r packages. An SQL package can be eated cr for each distributed module in a distributed ILE ogram pr or service pr ogram. The following figur e shows the objects and the steps that occur during the ecompile pr and compile pr ocesses for a distributed ILE DB2 for i pr ogram. The user sour ce file pr ecompiles the sour ce to a temporary sour ce file member . This member is then compiled into a module that binds to ogram. a prAfter the pr ogram is cr eated, an SQL package is cr eated to hold the ogram. pr Note: The access plans associated with the DB2 for i distributed ogram pr object ar e not cr eated until the program is un r locally. Related tasks: Preparing and unning r a pr ogram with SQL statements User source file A source file member or a sour ce stream file contains the application language and SQL statements. You can create and maintain the sour ce file member by using the sour ce entry utility (SEU), a part of the IBM ® Rational Development Studio for i licensed ogram. pr Output source file member By default, the pr ecompile process creates a temporary sour ce file QSQL Txxxxx in the QTEMP library. However, you can specify the output sour ce file as a permanent file on the ecompile pr command. SQL programming 15If the pr ecompile process uses the QTEMP library, the system automatically deletes the file when the job is completed. A member with the same name as the ogram pr name is added to the output ce sour file. This member contains the following items: v Calls to the SQL runtime support, which have eplaced r embedded SQL statements v Parsed and syntax-checked SQL statements By default, the pr ecompiler calls the host language compiler . Related tasks: Preparing and unning r a pr ogram with SQL statements Program A program is an object that is eated cr as a esult r of the compilation ocess pr for non-ILE compilations or as a result of the bind ocess pr for ILE compilations. An access planis a set of internal uctur str es and information that tells SQL how to un r an embedded SQL statement most ef fectively. It is eated cr only when the ogram pr has been successfully eated. cr Access plans are not cr eated during pr ogram creation for SQL statements if the statements efer r to an object, such as a table or view , that cannot be found or to which you e not ar authorized. The access plans for such statements e ar created when the pr ogram is un. r If, at that time, the table or view still cannot be found or you e still ar not authorized, a negative SQLCODE eturned. is r Access plans are stored and maintained in the ogram pr object for non-distributed SQL programs and in the SQL package for distributed SQL programs. SQL package An SQL package contains the access plans for a distributed pr SQL ogram. An SQL package is an object that is eated cr when: v You successfully cr eate a distributed SQL program by specifying the elational r database (RDB) parameter on the CREA TE SQL (CRTSQLxxx) commands. v You run the Cr eate SQL Package (CRTSQLPKG) command. When a distributed SQL program is cr eated, the name of the SQL package and an internal consistency token are saved in the ogram. pr They ar e used at un r time to find the SQL package and to verify that the SQL package is corr ect for this pr ogram. Because the name of the SQL package is critical for unning r distributed SQL programs, an SQL package cannot be: v Moved v Renamed v Duplicated v Restored to a dif ferent library Module ® A module is an Integrated Language Envir onment (ILE) object that you eate cr by compiling sour ce code using the Cr eate Module (CR TxxxMOD) command (or any of the eate Cr Bound Pr ogram (CRTBNDxxx) commands, where xxx is C, CBL, CPP , or RPG). You can un r a module only if you use the eate CrProgram (CRTPGM) command to bind it into a program. You typically bind several modules together , but you can bind a module by itself. Modules contain information about the SQL statements; however, the SQL access plans ar e not cr eated until the modules are bound into either a ogram pr or service pr ogram. Related reference: Create Program (CRTPGM) command 16 IBM i:SQL programmingService program A service program is an Integrated Language Envir onment (ILE) object that ovides pr a means of packaging externally supported callable outines r (functions or pr ocedures) into a separate object. Bound programs and other service ograms pr can access these outines r by esolving r their imports to the exports provided by a service ogram. pr The connections to these services e ar made when the calling programs are created. This impr oves call performance to these outines r without including the code in the calling program. Data definition language Data definition language (DDL) describes the portion of that SQL creates, alters, and deletes database objects. These database objects include schemas, tables, views, sequences, catalogs, indexes, variables, masks, permissions, and aliases. Related concepts: “Types of SQL statements” on page 7 There are several basic types of SQL statements. They ar e listed her e according to their functions. Related tasks: Getting started with SQL Creating a schema A schema provides a logical gr ouping of SQL objects. To create a schema, use the CREA TE SCHEMA statement. A schema consists of a library , a journal, a journal eceiver r , a catalog, and optionally , a data dictionary . Tables, views, and system objects (such as ograms) pr can be eated, cr moved, or estor r ed into any system libraries. All system files can be eated cr or moved into an SQL schema if the SQL schema does not contain a data dictionary . If the SQL schema contains a data dictionary then: v Source physical files or nonsour ce physical files with one member can be eated, cr moved, or estor r ed into an SQL schema. v Logical files cannot be placed in an SQL schema because they cannot be described in the data dictionary. You can cr eate and own many schemas. You can cr eate a schema using the CREA TE SCHEMA statement. For example, eate cr a schema called DBTEMP: CREATE SCHEMA DBTEMP Related reference: CREATE SCHEMA Creating a table A table can be visualized as a two-dimensional arrangement of data that consists ows of and r columns. To create a table, use the CREA TE TABLE statement. The row is the horizontal part containing one or e mor columns. The column is the vertical part containing one or mor e rows of data of one data type. All data for a column must be of the same Atype. table in SQL is a keyed or non-keyed physical file. You can cr eate a table using the CREA TE TABLE statement. ou Y provide a name for the table. If the table name is not a valid system object name, you can use the optional FOR SYSTEM NAME clause to specify a system name. SQL programming 17The definition includes the names and attributes of its columns. The definition can include other attributes of the table, such as the primary . key Example: Given that you have administrative authority , create a table named 'INVENT ORY' with the following columns: v Part number: Integer between 1 and 9999, and must not be null v Description: Character of length 0 to 24 v Quantity on hand: Integer between 0 and 100000 The primary key is AR PTNO. CREATE TABLE INVENTORY (PARTNO SMALLINT NOT NULL, DESCR VARCHAR(24 ), QONHAND INT, PRIMARY KEY(PARTNO)) Related concepts: Data types Adding and removing constraints Constraints can be added to a new table or to an existing o table. add Ta unique or primary , key a referential constraint, or a check constraint, use the CREA TE TABLE or the ALTER TABLE statement. oT remove a constraint, use the ALTER TABLE statement. For example, add a primary key to an existing table using ALTER the TABLE statement: ALTER TABLE CORPDATA.DEPARTMENT ADD PRIMARY KEY (DEPTNO) To make this key a unique , key replace the keywor d PRIMARY with UNIQUE. You can emove r a constraint using the same ALTER TABLE statement: ALTER TABLE CORPDATA.DEPARTMENT DROP PRIMARY KEY (DEPTNO) Referential integrity and tables Referential integrityis the condition of a set of tables in a database in which eferences all r from one table to another ar e valid. Consider the following example: v CORPDATA.EMPLOYEE serves as a master list of employees. v CORPDATA.DEPARTMENT acts as a master list of all valid department numbers. v CORPDATA.EMP_ACT provides a master list of activities performed for ojects. pr Other tables efer r to the same entities described in these tables. When a table contains data for which there is a master list, that data should actually appear in the master list, efer or ence the is r not valid. The table that contains the master list is par the ent table, and the table that efers r to it is dependent a table. When the efer r ences from the dependent table to the ent partable ar e valid, the condition of the set of tables is called referential integrity. Stated another way , referential integrity is the state of a database in which all values of eign all keys for are valid. Each value of the eign for key must also exist in the ent parkey or be null. This definition of referential integrity equir r es an understanding of the following terms: v A unique keyis a column or set of columns in a table that uniquely identify ow. Although a r a table can have several unique keys, no two ows r in a table can have the same unique key value. 18 IBM i:SQL programmingv A primary keyis a unique key that does not allow nulls. A table cannot have mor e than one primary key. v A parent keyis either a unique key or a primary key that eferis enced r in a efer r ential constraint. v A foreign keyis a column or set of columns whose values must match those ent of a key par . If any column value used to build the eign for key is null, the ule r does not apply . v A parent tableis a table that contains the ent parkey. v A dependent tableis the table that contains the eign for key. v A descendent tableis a table that is a dependent table or a descendent of a dependent table. Enforcement of efer r ential integrity pr events the violation of the ule r that states that every non-null foreign key must have a matching ent parkey. SQL supports the efer r ential integrity concept with the CREA TE TABLE and ALTER TABLE statements. Related reference: “DB2 for i sample tables” on page 346 These sample tables e arreferred to and used in the SQL programming and the SQL reference topic collections. CREATE TABLE ALTER TABLE Adding and removing referential constraints : You can use the CREA TE TABLE statement or the ALTER TABLE statement to add a efer rential constraint. To remove a efer r ential constraint, use the ALTER TABLE statement. Constraints are rules that ensur e that efer r ences from one table, a dependent table, to data in another table, the par ent table, ar e valid. You use efer r ential constraints to ensur e referential integrity. With a efer r ential constraint, non-null values of the eign for key ar e valid only if they also appear as values of a par ent key. When you define a efer rential constraint, you specify: v A primary or unique key v A foreign key v Delete and update ules r that specify the action taken with espect r to dependent ows r when the par ent row is deleted or updated. Optionally, you can specify a name for the constraint. If a name is not specified, one is automatically generated. After a efer r ential constraint is defined, the system enfor ces the constraint on every INSER T, DELETE, and UPDATE operation performed thr ough SQL or any other interface, including System i Navigator , CL commands, utilities, or high-level language statements. Related reference: CREATE TABLE ALTER TABLE Example: Adding referential constraints : You define a efer r ential constraint that every department number in the sample employee table must appear in the department table. The eferrential constraint ensur es that every employee belongs to an existing department. SQL programming 19

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.