everything you need to know about sql in 4 hours
DESCRIPTION
SQL in 4 hoursTRANSCRIPT
© 2006 Wellesley Information Services. All rights reserved.
Everything You Need to Know About SQL in Four Hours
Skip MarchesaniCustom Systems Corp
Session Sponsor
© 2006 Wellesley Information Services. All rights reserved.
Everything You Need to Know About SQL
In Four Hours Skip Marchesani
Custom Systems CorpSparta, NJ 07871
973-579-1340
Disclaimer:
This presentation may contain examples of code and names of companies or persons. The code is given for presentation purposes and has not been tested by IBM and/or Custom Systems Corp. Therefore IBM and/or Custom Systems Corp does not guarantee the reliability, serviceability, or function of the code and the code is provided "AS IS". IBM AND/OR CUSTOM SYSTEMS CORP EXPRESSLY DISCLAIMS ANY AND ALL WARRANTIES, INCLUDING BUT NOT LIMITED TO, THE WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE AND MERCHANTABILITY. Any names appearing in this presentation are designed to be fictitious and IBM and Custom Systems Corp makes no representations as to the accuracy of the names or data presented in accordance therewith.
Reproduction:
This presentation is the property of Skip Marchesani and Custom Systems Corporation. Permission is granted to make a limited number of copies of this material for non-commercial purposes, providing this page is included with all copies. Express written permission is required for making copies for other purposes.
iSeries, AS/400, OS/400, DB2 UDB, DB2/400 are registered trademarks of the IBM Corporation
Everything You Need to Know About SQL in Four Hours
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 1-2
Copyright Custom Systems Corp 2006
General OverviewCan You Really Learn SQL in Four Hours?
No - You Can'tHowever you can learn the basicsIt is important to understand that SQL is a very powerful programming language. You need to work and play with it, to realize the power of SQL and understand what it can do for you as an application development or database manipulation tool. With a little experience, thought, and creativity you will find you can use SQL for things that at first glance you did not think possible. This presentation is based on the SQL function available in V5R4 of i5 OS.
Copyright Custom Systems Corp 2006
General Overview... Overlap of Session Material
The concepts and theory presented in Everything You Need to Know About SQL in Four Hours are taught at a basic or introductory levelSome of the concepts and theory will be repeated in the following iSeries DevCon elective sessions:
Creating and Managing DB2 UDB Database Objects Using SQL Embedding SQL in a HLL (RPG) Program
Please be aware that there is this overlap of material between this Jumpstart session and the sessions listed above
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 3-4
Copyright Custom Systems Corp 2006
General Overview... Agenda
Part 1Introduction to SQL
Part 2Interactive SQLSQL Statement ProcessoriSeries Navigator SQL Script CenterEmbedding SQL in a HLL (RPG & Cobol) ProgramDynamic SQL
Bonus Material - For Review at Your LeisureQuery ManagerPredictive Query Governor
Copyright Custom Systems Corp 2006
Everything You Need to Know About SQL in Four Hours
Part 1
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 5-6
Copyright Custom Systems Corp 2006
Overview - Part 1What is SQLWhy Learn and Use SQLSQL Implementation in DB2 UDB - aka DB2/400Query Manager OverivewSQL TerminologySELECT StatementColumn and Scalar FunctionsSummarizing DataSubquery and Scalar SubselectDatabase ManagementRow/Record ManipulationUsing SQL on the iSeries & AS/400V5 SQL Support - The DetailFour Types of SQL StatementsInteractive SQL SummaryEmbedded SQL SummarySQL for Database ServingSummary - Part 1V5 SQL Information Sources
Copyright Custom Systems Corp 2006
What is SQL?SQL - Structured Query Language
Invented by IBM in late 1960s early 1970s time frameData language for manipulation of a Relational database English keyword-oriented Excellent tool for application development and database management environment
Query Data definition Data manipulation Data control and management
Powerful language for Set-at-a-Time processing
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 7-8
Copyright Custom Systems Corp 2006
Why SQL?Structured Query Language
IBM Standard for Relational Data Language DB2 Universal Database (aka DB2/400)
Integrated with i5, iSeries and AS/400 architecture and provided as a standard feature with i5 OS and OS/400 Available as extra cost option on all other IBM platforms Available as extra cost option on all other vendors platforms
Industry Standard Relational Database LanguageDefacto language standard used for Database Serving
ODBCJDBC DRDA Others
i5 and iSeries Servers are optimized for Database Serving using SQL and DB2 UDB
Copyright Custom Systems Corp 2006
Why SQL?...Increased Productivity
Very high compliance with ANS/ISO SQL Standard Standardized, uniform database I/O
Across client and server systems Across programming languages
Leading software vendors utilize SQL for database I/O in their application packages Strategic interface for implementing new DB2 UDB function by Rochester Lab
SQL skill is transferable across platforms
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 9-10
Copyright Custom Systems Corp 2006
SQL in DB2 UDB - aka DB2/400i5, iSeries, and AS/400 Implementation
Runs on any model Integrated with architecture Provides interactive interface Can be embedded/compiled in HLL programs
RPG COBOL CJava
Copyright Custom Systems Corp 2006
SQL in DB2 UDB - aka DB2/400...V5 SQL Support
1. DB2 UDB Database Manager Part of i5 OS or OS/400 (operating system)
V5 - 5722-SS1
Parser and run-time support 5722-ST1 license not required to run SQL applications
SQL APIs
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 11-12
Copyright Custom Systems Corp 2006
SQL in DB2 UDB - aka DB2/400...V5 SQL Support
2. DB2 UDB Query Manager and SQL Development Kit Licensed product 5722-ST1Query Manager Interactive SQL interface Precompilers for SQL Other additional features
Copyright Custom Systems Corp 2006
Query ManagerQuery Manager: SQL Based Query
Puts the POWER of SQL into the hands of the end user WITHOUT complex programming! Easy to use tool set that exploits the power of SQL designed for end users and programmers User friendly interface to Query Management CPI Query Creation Interface Report Definition Interface
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 13-14
Copyright Custom Systems Corp 2006
Query Manager...Query Manager: SQL Based Query
Capability to do customized, prompted queries Capability to do data manipulation Table Facility for create, display and maintain SQL table Define Query Manager Profile information Part of DB2 UDB Query Manager & SQL Development Kit
Copyright Custom Systems Corp 2006
Query Manager...Query Manager Uses
With Query Manager USERS can: Create and run queries ( QMQRY object type ) Create and manage reports ( QMFORM object type ) Create, manage, and report on database files Create and manage Query Manager Profiles
With Query Manager PROGRAMMERS can: Do all of the above user functions Easily integrate queries into applications Pass parameter data into queries at RUN TIME
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 15-16
Copyright Custom Systems Corp 2006
Query Manager...Query Manager Uses
With Query Manager any interactive SQL statement can be executed in a CL program Note:
A Query Manager QUERY can do much more than just retrieve data from one or more files. A Query Manager QUERY can be used to execute ANY interactive SQL statement. This means a QM QUERY can be used to manage, control, and manipulate data including but not limited to the update, insertion, and deletion of records in a file assuming the user has the proper authorization thru his Query Manager profile.
Copyright Custom Systems Corp 2006
SQL Terminology Table, Row, and Column
NBR NAM CLS SEX DPT SAL
20 Heikki 2 M 901 6,00010 Ed 5 M 911 7,00050 Marcela 3 F 911 7,500
40 Mike 4 M 977 6,50030 John 5 M 977 3,20060 Frank 2 M 990 6,500
Table - EMP
Column
Row
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 17-18
Copyright Custom Systems Corp 2006
i5 and iSeries Traditional Terminology vs SQL TerminologyDB2 UDB SQL Term i5 & iSeries
DATABASE SYSTEM NAMETABLE FILEROW RECORD
COLUMN FIELDVIEW LOGICAL FILEINDEX KEYED LOGICAL FILE
COLLECTION LIBRARYTABLESPACE Not Applicable
Industry Terms DB2 UDB TermSCHEMA COLLECTION
LOG JOURNALISOLATION LEVEL COMMITMENT CONTROL
LEVEL
SQL Terminology...
Copyright Custom Systems Corp 2006
SQL Tables Used In Examples Employee Table - EMP
NBR NAM CLS SEX DPT SAL20 Heikki 2 M 901 6,00010 Ed 5 M 911 7,00050 Marcela 3 F 911 7,50040 Mike 4 M 977 6,50030 John 5 M 977 3,20060 Frank 2 M 990 6,500
DPT DNM901 Accounts977 Manufact911 Sales990 Spares
Department Table - DEP
Suggestion: As an aid in learning SQL, create these two tables on an iSeries and use Interactive SQL and its prompter, or the iSeries Navigator SQL Script Center, to run the example SQL statements provided in this handout.
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 19-20
Copyright Custom Systems Corp 2006
SELECT StatementSELECT STATEMENT and CLAUSES
SELECT . . . . . . (columns, *, or expressions) FROM . . . . . .(tables or views) WHERE . . . . .(row selection criteria) GROUP BY. . .(columns) HAVING. . . . . (GROUP BY selection criteria) ORDER BY. . .(columns)
Copyright Custom Systems Corp 2006
SELECT Statement...SELECT STATEMENT and CLAUSES
SELECT sex, nam, cls FROM emp
SEX NAM CLSM Heikki 2M Ed 5F Marcela 3M Mike 4M John 5M Frank 2
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 21-22
Copyright Custom Systems Corp 2006
SELECT Statement...
NBR NAM CLS SEX DPT SAL10 Ed 5 M 911 7,00020 Heikki 2 M 901 6,00030 John 5 M 977 3,20040 Mike 4 M 977 6,50050 Marcela 3 F 911 7,50060 Frank 2 M 990 6,500
NBR NAM CLS SEX DPT SAL20 Heikki 2 M 901 6,00010 Ed 5 M 911 7,00050 Marcela 3 F 911 7,50040 Mike 4 M 977 6,50030 John 5 M 977 3,20060 Frank 2 M 990 6,500
ORDER BYNBR Sequence
No ORDER BYArrival Sequence
ORDER BY Clause
SELECT * FROM emp ORDER BY nbr
Copyright Custom Systems Corp 2006
SELECT Statement...ORDER BY Clause
SELECT * FROM emp ORDER BY nbr DESC
NBR NAM CLS SEX DPT SAL60 Frank 2 M 990 6,50050 Marcela 3 F 911 7,50040 Mike 4 M 977 6,50030 John 5 M 977 3,20020 Heikki 2 M 901 6,00010 Ed 5 M 911 7,000
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 23-24
Copyright Custom Systems Corp 2006
SELECT Statement...WHERE Clause
SELECT nbr, nam, sex FROM emp WHERE sex = 'F'
NBR NAM SEX50 Marcela F
Copyright Custom Systems Corp 2006
Creating Derived Fields with the SELECT Statement The SELECT statement can be used to create new virtual fields from the column values in a row using the following operations:
Add Subtract Multiply Divide Concatenate Substring AS
SELECT Statement...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 25-26
Copyright Custom Systems Corp 2006
SELECT Statement...Creating Derived Fields with the SELECT Statement
Example: SUBSTRING, AS, and derived field
SELECT nbr, SUBSTR( nam,1,3 ) AS sname, sal, ( sal * 12 ) FROM emp ORDER BY nbr
NBR SNAME SAL ( SAL * 12 )10 Ed 7,000 84,00020 Hei 6,000 72,00030 Joh 3,200 38,40040 Mik 6,500 78,00050 Mar 7,500 90,00060 Fra 6,500 78,000
Copyright Custom Systems Corp 2006
SELECT Statement...Building Selection Criteria with the WHERE Clause
Row selection is done with the WHERE clause and the following logical operators or KEYWORDS can be used to construct the selection criteria
Greater than, Less than, Equal Greater than or equal, Less than or equal Not greater, Not less, Not equal AND, OR, NOT Range: inclusive constant range (BETWEEN) Values: list of constant values (IN) Wild Card: Constant pattern matching (LIKE)
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 27-28
Copyright Custom Systems Corp 2006
SELECT Statement...Building Selection Criteria with the WHERE Clause
Example: IN and BETWEEN
SELECT nam, cls, dpt FROM emp WHERE dpt IN ( 911, 977 ) AND sal BETWEEN 6500 AND 7500 ORDER BY nam
NAM CLS DPTEd 5 911Marcela 3 911Mike 4 977
Copyright Custom Systems Corp 2006
SELECT Statement...Building Selection Criteria with the WHERE Clause
Example: LIKE, %, and _
SELECT nam, ( sal * 12 ) AS yearsal FROM emp WHERE nam LIKE '%ik%' ORDER BY 2
NAM YEARSALHeike 72,000Mike 78,000
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 29-30
Copyright Custom Systems Corp 2006
SELECT Statement...Building Selection Criteria with the WHERE Clause
Example: LIKE, %, and _
SELECT nam, ( sal * 12 ) AS yearsal FROM emp WHERE nam LIKE '_ik%' ORDER BY yearsal
NAM YEARSALMike 78,000
Copyright Custom Systems Corp 2006
SQL Column and Scalar FunctionsSQL Built-In Functions (95+ for V5R1)
Built-in column functions for grouping: AVG (numeric columns) SUM (numeric columns) MAX (num or char columns) MIN (num or char columns) COUNT (count selected rows)
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 31-32
Copyright Custom Systems Corp 2006
SQL Column and Scalar Functions...SQL Built-In Functions (140+ for V5R4)
Plus all the same built-in scalar functions in OPNQRYF Trigonometric functions ( SIN, ASIN, SINH ) Day, Date, Time operations, durations, calculations Character operators ( LEN, STRIP, RANGE ) String operators ( SUBSTRING, XLATE, XOR ) Upper case operators ( UPPER, UCASE )
NOTE: Mixed upper and lowercase in character fields can be forced to upper case only for selection or comparison purposes by using the UPPER or UCASE scalar functions, or by specifying SRTSEQ(*LANGIDSHR) and LANGID(ENU) on the CHGJOB or STRSQL commands - watch out for performance
Copyright Custom Systems Corp 2006
SQL Column and Scalar Functions...Built-In Functions for Grouping:
Example: COUNT
SELECT COUNT( * ) FROM emp
COUNT(*) 6
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 33-34
Copyright Custom Systems Corp 2006
SQL Column and Scalar Functions...Built-In Functions for Grouping:
Example: MAX and MIN
SELECT MAX ( sal ), MIN ( sal ) FROM emp
MAX(SAL) MIN(SAL) 7,500 3,200
Copyright Custom Systems Corp 2006
Summarizing Data with SQLGROUP BY and HAVING Clause
SELECT dpt, SUM ( sal ), COUNT ( * ) FROM emp GROUP BY dpt ORDER BY dpt
DPT SUM(SAL) COUNT(*)901 6,000 1911 14,500 2977 9700 2990 6500 1
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 35-36
Copyright Custom Systems Corp 2006
Summarizing Data with SQL...GROUP BY and HAVING Clause
SELECT dpt, SUM ( sal ), COUNT ( * ) FROM emp GROUP BY dpt HAVING COUNT ( * ) > 1 ORDER BY dpt
DPT SUM(SAL) COUNT(*)911 14,500 2977 9700 2
Copyright Custom Systems Corp 2006
SubquerySubquery: Query within a Query
PROBLEM: List the employees from departments where the department name starts with 'S'
SELECT nam, dpt FROM emp WHERE dpt IN ( SELECT dpt FROM dep WHERE dnm LIKE 'S%' ) ORDER BY nam
NAM DPTEd 911Frank 990Marcela 911
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 37-38
Copyright Custom Systems Corp 2006
Subquery...Scalar Subselect - Update ALL Rows
PROBLEM: Change each employee's current salary to the amount listed in the new salary file
All rows in EMP will be updated
UPDATE emp aa SET sal = (SELECT sal FROM newsal bb WHERE aa.nbr = bb.nbr)
Copyright Custom Systems Corp 2006
Subquery...Scalar Subselect - Update ALL Rows
PROBLEM: Change each employee's current salary to the amount listed in the new salary file
UPDATE emp aa SET sal = (SELECT sal FROM newsal bb WHERE aa.nbr = bb.nbr)
If no match found in NEWSAL error message issued
Null values are not allowed...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 39-40
Copyright Custom Systems Corp 2006
Subquery...Scalar Subselect - Every Row NOT updated
PROBLEM: Change each employee's current salary to the amount listed in the new salary file
Only rows identified by subselect in outer WHERE clause are updated in EMP
UPDATE emp aa SET sal = (SELECT sal FROM newsal bb WHERE aa.nbr = bb.nbr) WHERE aa.nbr IN (SELECT nbr FROM newsal bb)
Copyright Custom Systems Corp 2006
Subquery...Scalar Subselect Enhancements for V5
V5R1Limited support for Scalar Subselect in SELECT statement
Only simple SELECT statement supported
V5R2Full support for Scalar Subselect in SELECT statementSubselect can now be part of an expression and be included anywhere an expression is allowed
SELECTUPDATEDELETEINSERT INTOCREATE TABLE
Complex SELECT statement supported
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 41-42
Copyright Custom Systems Corp 2006
Subquery...Full support for Scalar Subselect in SELECT statement
Problem: What is the max salary for each department?
SELECT a.dpt, (SELECT MAX(sal) FROM emp b WHERE b.dpt = a.dpt) AS Max_Sal FROM dep a ORDER BY dpt
DPT MAX_SAL901 6000911 7500977 6500990 6500
Copyright Custom Systems Corp 2006
Database ManagementA Database can be Created and Managed with SQL
SQL database objects: COLLECTION (native Library plus additional objects) TABLE (native Physical File) VIEW (native Logical File - unkeyed) INDEX (native Logical File - keyed)
To create SQL database objects CREATE objtype objname CREATE TABLE emp
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 43-44
Copyright Custom Systems Corp 2006
Database Management...A Database can be Created and Managed with SQL
To delete SQL data base objects DROP objtype objname DROP VIEW empnbr
Can Join tables/physical files using the FROM clause on the SELECT statement CATALOG contains database definitions/attributes SQL Authorization maps to native Security
Copyright Custom Systems Corp 2006
Database Management...How to Find System Catalog Files
SQL Licensed product installed? WRKOBJPDM QSYS2 SYS* SYSCOLUMNS
SQL NOT installedWRKOBJPDM QSYS QADB*QADBIFLD
Detailed information about the system catalog and its associated tables and views can be found in Appendix G of:
DB2 UDB for iSeries SQL Reference
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 45-46
Copyright Custom Systems Corp 2006
Database Management...Field names to query: SQL, Query Mgr, Query/400, ...
SYSCOLUMNS (fields at the end of record format)iSeries & AS/400 Native Names
Library - SYS_DNAMEFile - SYS_TNAMEField - SYS_CNAME
SQL NamesLibrary - SYSTEM_TABLE_SCHEMAFile - SYSTEM_TABLE_NAMEField - SYSTEM_COLUMN_NAME
Copyright Custom Systems Corp 2006
Database Management...Field names to query: SQL, Query Mgr, Query/400, ...
QADBIFLD (fields at the beginning of record format)iSeries & AS/400 Naming
Library - DBILIBFile - DBIFILField - DBIFLD
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 47-48
Copyright Custom Systems Corp 2006
Row/Record ManipulationSQL can be Used to Manipulate Single or Multiple Rows in a Table or View
SQL Statements UPDATE INSERT INTO (add a record) DELETE
Handles single or multiple records SQL is a SET-AT-A-TIME language Excellent for processing multiple records that match a specific selection criterion
Copyright Custom Systems Corp 2006
Row/Record Manipulation...Updating Records with SQL
Example: Single Record Update
UPDATE emp SET sal = sal + 1000 WHERE nbr = 30
NBR NAM CLS SEX DPT SAL30 John 5 M 977 4,200
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 49-50
Copyright Custom Systems Corp 2006
Row/Record Manipulation...Updating Records with SQL
Example: Multiple Record Update
UPDATE emp SET sal = sal + 1500
SQL is a SET-AT-A-TIME language
Copyright Custom Systems Corp 2006
Row/Record Manipulation...Inserting/Adding Records with SQL
INSERT Statement
INSERT INTO emp ( nam, nbr, sex, cls ) VALUES ( 'amy', 15, 'F', 2 )
Columns and values one for one correspondence
All columns not required
Specify column names in any order
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 51-52
Copyright Custom Systems Corp 2006
Row/Record Manipulation...An example of the POWER of SQL: Multiple record insert
CREATE TABLE workfile ( dpt DEC ( 3 ), totsal DEC ( 6 ), dnm CHAR ( 10 ) )
INSERT INTO workfile (dpt, totsal, dnm) SELECT a.dpt, sum(sal), dnm FROM emp a, dep b WHERE a.dpt = b.dpt GROUP BY a.dpt, dnm
Dept Total Salary
Dept Name
901 6,000 Accounts911 14,500 Sales977 9,700 Manufact990 6,500 Spares
Copyright Custom Systems Corp 2006
An example of the POWER of SQL: Multiple record insert Compare the work effort for these two SQL statements to the work effort required to do the same with DDS and CL commands, or other interfaces
Which is easier/faster ?? By how much ??
Row/Record Manipulation...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 53-54
Copyright Custom Systems Corp 2006
Row/Record Manipulation...Previous Example Using V5R2 Scalar Subselect
CREATE TABLE workfile AS (SELECT a.dpt, SUM(sal) AS totsal, (SELECT dnm FROM dep b WHERE a.dpt = b.dpt) AS dnm FROM emp a GROUP BY a.dpt, 3) WITH DATA
Dept Total Salary
Dept Name
901 6,000 Accounts911 14,500 Sales977 9,700 Manufact990 6,500 Spares
Copyright Custom Systems Corp 2006
Row/Record Manipulation...CREATE TABLE Syntax Using V5R2 Scalar Subselect
Each derived column defined in the scalar subselect within the CREATE TABLE statement must be given a name using the AS operatorCREATE TABLE statement must be ended with one of the following two clauses
WITH DATA Table is populated with rows and columns that match table definition and selection criterion
WITH NO DATA Table is created as empty table with no rows
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 55-56
Copyright Custom Systems Corp 2006
Row/Record Manipulation...Deleting Records with SQL
Example: Single record delete DELETE FROM emp WHERE nbr = 60
Example: Multiple record delete DELETE FROM emp WHERE dpt = 977
Example: ALL record delete DELETE FROM emp
AKA as the oops or gonzo delete!
Copyright Custom Systems Corp 2006
Using SQL on the iSeries & AS/400Different Ways to Execute SQL Statements
Interactive SQL Embedded or compiled in application programs Query Manager SQL Statement Processor Operations NavigatorDynamic SQL SQL Procedure LanguageExtended Dynamic SQL ODBC, JDBCJSQL or SQLJX/Open SQL Call Level Interface Query Management (different from Query Manager)
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 57-58
Copyright Custom Systems Corp 2006
V5 SQL Support - The Detail Part 1: DB2 UDB Database Manager
Included with OS/400 (operating system) V5: 5722-SS1
SQL parser and run time support SQL license (5722-ST1) not required to run SQL applications
SQL APIs QSQPRCED - Provides extended dynamic SQL capability QSQCHKS - Provides syntax checking for SQL statements
X/Open SQL Call Level Interface V5R1 - SQL Statement Processor
RUNSQLSTM Command
Copyright Custom Systems Corp 2006
V5 SQL Support - The DetailPart 2: DB2 UDB Query Manager & SQL Development Kit
Program number 5722-ST1Query Manager Interactive SQL SQL precompilers V4 - SQL Statement Processor
Packaged with OS/400 and Database Manager in V5R1
SQL REXX interface
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 59-60
Copyright Custom Systems Corp 2006
Four Types of SQL StatementsData definitionData manipulationDynamicMiscellaneous
Copyright Custom Systems Corp 2006
Data Definition StatementsALTER TABLECOMMENT ONCREATE ALIASCREATE COLLECTIONCREATE DISTINCT TYPECREATE FUNCTIONCREATE INDEXCREATE PROCEDURECREATE SCHEMACREATE TABLECREATE VIEWDROP ALIASDROP COLLECTIONDROP DISTINCT TYPEDROP FUNCTIONDROP INDEXDROP PACKAGEDROP PROCEDUREDROP SCHEMADROP TABLEDROP VIEW
GRANT DISTINCT TYPEGRANT FUNCTIONGRANT PACKAGEGRANT PROCEDUREGRANT TABLELABEL ONRENAMEREVOKE DISTINCT TYPEREVOKE FUNCTIONREVOKE PACKAGEREVOKE PROCEDUREREVOKE TABLE
CRTSQLPKG is the native command used to create an SQL package. There is no equivalent SQL statement that provides the same function.
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 61-62
Copyright Custom Systems Corp 2006
CLOSECOMMITDECLARE CURSORDELETEFETCHINSERTLOCK TABLEOPENROLLBACKSELECTSELECT INTOSET variableUPDATEVALUES INTO
Data Manipulation Statements
Copyright Custom Systems Corp 2006
Miscellaneous and Dynamic SQL StatementsMiscellaneous Statements BEGIN DECLARE SECTIONCALLCONNECTDECLARE PROCEDUREDECLARE STATEMENTDECLARE VARIABLEDESCRIBE TABLEDISCONNECTEND DECLARE SECTIONFREE LOCATORINCLUDERELEASESET CONNECTIONSET OPTIONSET PATHSET RESULT SETSSET TRANSACTIONWHENEVER
Dynamic SQL Statements DESCRIBE EXECUTE EXECUTE IMMEDIATE PREPARE
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 63-64
Copyright Custom Systems Corp 2006
Interactive SQLExcellent Tool for Application Development Environment
Designed as a tool for: Programmers Data Base Administrators
Interactive functions Quickly maintain data base Test SQL code Perform SQL requests Store SQL statements for future use
Can be used to prototype database design
Copyright Custom Systems Corp 2006
Interactive SQL...Excellent Tool for Application Development Environment
Modeled after Command Entry Display Multi-level SQL statement prompter
STRSQL command to initiate Recovers after abnormal termination Main components
Statement entry List support Session services Help
NOT Intended as an End User Query Tool!! No report formatting No end user resource controls/throttles
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 65-66
Copyright Custom Systems Corp 2006
SQL in Application ProgramsLanguages Supported
RPG COBOL C JavaAS/400 PL/I FORTRAN/400
Copyright Custom Systems Corp 2006
SQL in Application Programs...
UserSource
File
ModifiedSource
File
ProcessedSQL
Statements
Program
Access Plan
SQLPrecompiler
LanguageCompiler
Syntax checkX-ref host variablesSQL statements to callsComment SQL statements
SQL uses a Precompiler
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 67-68
Copyright Custom Systems Corp 2006
Selecting a Single Row
I DS I 1 50EMPNBR I 6 30 NAM I 31 32 DEPT C* C/EXEC SQL C+ SELECT nbr, nam, dpt C+ INTO :empnbr, :nam, :dept C+ FROM emp C+ WHERE nbr = :empnbr C/END-EXEC
RPG and other HLLs more efficient for doing single record random retrievals SQL more efficient for multiple record sets or groups
SELECT A Single Row From A Table - RPG/400
Copyright Custom Systems Corp 2006
Selecting a Single Row...SELECT A Single Row From A Table - Cobol
WORKING-STORAGE SECTION. 77 EMPNBR PIC S9(5) COMP-3. 77 DEPT PIC S9(3) COMP-3. 77 NAM PIC X(25). . . PROCEDURE DIVISION. EXEC SQL SELECT nbr, nam, dpt INTO :empnbr, :nam, :dept FROM emp WHERE nbr = :empnbr END-EXEC.
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 69-70
Copyright Custom Systems Corp 2006
Select Multiple RowsSELECT & Process Multiple Rows/Records from a Table
1. Declare SQL cursor 2. Open cursor 3. Fetch next record 4. Process record (UPDATE/INSERT/etc) 5. If last record: go to Step 6,
else: go to Step 3
6. Close cursor
Copyright Custom Systems Corp 2006
Select Multiple Rows...Defining and Using an SQL Cursor
PROBLEM:
Update SALARY for all records in a specified department
C* C/EXEC SQL C+ C+ DECLARE empcsr CURSOR FOR C+ SELECT nbr, nam, sal C+ FROM emp C+ WHERE dpt = :dept C+ FOR UPDATE OF sal C+ C/END-EXEC C* C EXFMT PROMPT C* Example continued on next page
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 71-72
Copyright Custom Systems Corp 2006
Select Multiple Rows...SQL Cursor Example - Continued C* C/EXEC SQL C+ C+ OPEN empcsr C+ C/END-EXEC C* C SQLCOD DOUNE 0 C* C/EXEC SQL C+ C+ FETCH NEXT FROM empcsr C+ INTO :number, :name, :salary C+ C/END-EXEC C*
Example continued on next page
Copyright Custom Systems Corp 2006
Select Multiple Rows...SQL Cursor Example - Continued
C* C SQLCOD IFEQ 0 C* C/EXEC SQL C+ C+ UPDATE emp C+ SET sal = sal + :raise C+ WHERE CURRENT OF empcsr C+ C/END-EXEC C* C EXCEPT C END C END C* C/EXEC SQL C+ C+ CLOSE empcsr C+ C/END-EXEC
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 73-74
Copyright Custom Systems Corp 2006
SQL for Database Servingi5/iSeries is the Premier Database Server
Web based computingNetwork centric computingClient server computingAny environment where two or more computers are connected to a network structure and one or more of these computers requests information or data from a primary or host computer on the same network structure
Copyright Custom Systems Corp 2006
SQL for Database Serving...SQL is the Defacto Standard for Database Serving
Tools generate industry standard SQLApplication developmentEnd user
SQL hidden from developer and/or end user Need to understand SQL to
Debug Optimize Tune
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 75-76
Copyright Custom Systems Corp 2006
i5 & iSeries SQL Implementation SQL Uses Native Facilities
Data base management Data base query Journal management Commitment control Security
SQL can access natively created, externally described files AND
HLL I/O operands can access SQL created tables
THE BEST OF BOTH WORLDS!
Copyright Custom Systems Corp 2006
SQL vs High Level LanguagesSQL's Strength is Set-At-A-Time Processing SQL very well suited for:
Set-at-a-time or multiple record processing Database serving with complex record selection criteria Report programs with user selection Subfile programs with complex record selection
Application design considerations
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 77-78
Copyright Custom Systems Corp 2006
Why SQL?Benefits of Using SQL
Practical, effective solution for applications using complex data selection criteria Very flexible, functional tool for application development
Two interactive interfaces Interactive SQL - 5250 green screen basediSeries Navigator SQL Script Center - PC Windows based
Imbedded in a HLL programSeveral other i5, iSeries, and AS/400 interfaces available
Includes Data Manipulation Portability to other architectures Data retrieval language for database serving Key to sharing data between mixed vendor platforms
Copyright Custom Systems Corp 2006
Summary - Part 1What is SQLWhy Learn and Use SQLSQL Implementation in DB2 UDB - aka DB2/400Query Manager OverivewSQL TerminologySELECT StatementColumn and Scalar FunctionsSummarizing DataSubquery and Scalar SubselectDatabase ManagementRow/Record ManipulationUsing SQL on the i5, iSeries, & AS/400V5 SQL Support - The DetailFour Types of SQL StatementsInteractive SQL SummaryEmbedded SQL SummarySQL for Database Serving
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 1 Page 79-80
Copyright Custom Systems Corp 2006
Everything You Need to Know About SQL in Four Hours
Part 2
Copyright Custom Systems Corp 2006
Overview - Part 2Executing SQL on the i5, iSeries & AS/400V5 SQL Support - The DetailInteractive SQL SQL Statement Processor iSeries Navigator SQL Script CenterEmbedding SQL in a HLL ProgramBasic SQL StatementsSQL PrecompilerRPG and Cobol InterfaceCursor OperationsError Detection and HandlingDynamic SQLEditing and Compiling Embedded SQLPerformance TipsSummary - Part 2V5 SQL Information Sources
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 1-2
Copyright Custom Systems Corp 2006
Executing SQL on the i5, iSeries, & AS/400Different Ways to Execute SQL Statements
Interactive SQL Embedded or compiled in application programs Query Manager SQL Statement Processor Operations NavigatorDynamic SQL SQL Procedure LanguageExtended Dynamic SQL ODBC, JDBCJSQL or SQLJX/Open SQL Call Level Interface Query Management (different from Query Manager)
Copyright Custom Systems Corp 2006
V5 SQL Support - The Detail Part 1: DB2 UDB Database Manager
Included with i5 OS or OS/400 (operating system) V5: 5722-SS1
SQL parser and run time support SQL license (5722-ST1) not required to run SQL applications
SQL APIs QSQPRCED - Provides extended dynamic SQL capability QSQCHKS - Provides syntax checking for SQL statements
X/Open SQL Call Level Interface V5R1 - SQL Statement Processor
RUNSQLSTM Command
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 3-4
Copyright Custom Systems Corp 2006
V5 SQL Support - The DetailPart 2: DB2 UDB Query Manager & SQL Development Kit
Program number 5722-ST1Query Manager Interactive SQL SQL precompilers V4 - SQL Statement Processor
Packaged with OS/400 and Database Manager in V5R1
SQL REXX interface
Copyright Custom Systems Corp 2006
Interactive SQLExcellent Application Development Tool!
Designed as a tool for: Programmers Data Base Administrators
Interactive functions Quickly create test dataTest SQL code Perform SQL requests Store SQL statements for future use Quickly maintain data base
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 5-6
Copyright Custom Systems Corp 2006
Interactive SQL...Excellent Application Development Tool!
Can be used to quickly prototype applications STRSQL command to initiate NOT Intended as an End User Query Tool!!
No report formatting No end user resource controls/throttles
Copyright Custom Systems Corp 2006
Interactive SQL...STRSQL Command Parameters Start SQL Interactive Session (STRSQL) Type choices, press Enter. Commitment control . . . . . *NONE *NONE, *CHG, *CS, *ALL Naming convention . . . . . *SYS *SYS, *SQL Statement processing . . . . *RUN *RUN, *VLD, *SYN Library option . . . . . . . *LIBL Name, *LIBL, *USRLIBL... List type . . . . . . . . . *ALL *ALL, *SQL Data refresh . . . . . . . . *ALWAYS *ALWAYS, *FORWARD Allow copy data . . . . . . *YES *YES, *OPTIMIZE, *NO Date format . . . . . . . . *JOB *JOB, *USA, *ISO, *EUR... Date separator character . . *JOB *JOB, /, ., ,, -, ' '... Time format . . . . . . . . *HMS *HMS, *USA, *ISO, *EUR... Time separator character . . *JOB *JOB, :, ., ,, ' ',*BLANK Decimal point . . . . . . . *SYSVAL *SYSVAL, *PERIOD, *COMMA Sort sequence . . . . . . . *JOB Name, *HEX, *JOB... Library . . . . . . . . . _______ Name, *LIBL, *CURLIB Language identifier . . . . *JOB *JOB, *JOBRUN... Bottom F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use dsply F24=More keys
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 7-8
Copyright Custom Systems Corp 2006
Interactive SQL...SQL Statement Entry Function
Enter SQL Statements Type SQL statement, press Enter. Current connection is to relational database SPL335. ===> __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ Bottom F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line F12=Cancel F13=Services F24=More keys (C) COPYRIGHT IBM CORP. 1982, 1994.
Copyright Custom Systems Corp 2006
Interactive SQL...Function Keys for Interactive SQL
F4 Prompt F6 Insert
Inserts a blank line after line where the cursor is located.
F9 Retrieve Retrieves a complete, previously typed SQL statement, and places it on the line with the arrow. You identify the statement you want moved by placing the cursor on any part of the statement. If the cursor is not on any part of a statement, but within the statement entry field, the last statement run is retrieved.
F10 Copy line Copies all of the line the cursor is on to a new line immediately below the line being copied.
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 9-10
Copyright Custom Systems Corp 2006
Function Keys for Interactive SQL F12 Cancel
Displays the Exit Interactive SQL display.
F13 Services Displays the Interactive SQL Session Services menu.
F14 Delete line Deletes the entire line where the cursor is located.
F15 Split line Causes everything to the right of the cursor (including the character where the cursor is located) to be left-justified on a new line immediately below the line where the cursor is located.
Interactive SQL...
Copyright Custom Systems Corp 2006
Interactive SQL...Function Keys for Interactive SQL
F16 Collections/Libraries Displays a list of available collections or libraries, or lets you change the name.
F17 Tables/Files Displays a list of available tables (physical files) and views (logical files), based on previously selected collections (libraries).
F18 Columns/Fields Displays a list of available columns (fields), based on previously selected tables and views (files).
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 11-12
Copyright Custom Systems Corp 2006
Interactive SQL...F4 Prompt - Prompting SQL statements from a blank line Select SQL Statement Select one of the following: 1. ALTER TABLE 15. DROP TABLE 2. CALL 16. DROP VIEW 3. COMMENT ON 17. GRANT PACKAGE 4. COMMIT 18. GRANT TABLE 5. CONNECT 19. INSERT 6. CREATE COLLECTION 20. LABEL ON 7. CREATE INDEX 21. LOCK TABLE 8. CREATE TABLE 22. RELEASE 9. CREATE VIEW 23. REVOKE PACKAGE 10. DELETE 24. REVOKE TABLE 11. DISCONNECT 25. ROLLBACK 12. DROP COLLECTION ====>> 26. SELECT 13. DROP INDEX 27. SET CONNECTION 14. DROP PACKAGE 28. SET TRANSACTION 29. UPDATE Selection _26 <<==== F3=Exit F12=Cancel
Copyright Custom Systems Corp 2006
F4 Prompt - Prompting a partial SQL Statement
Enter SQL Statements Type SQL statement, press Enter. Current connection is to relational database SPL335. ===> SELECT____________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ __________________________________________________________ Bottom F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line F12=Cancel F13=Services F24=More keys (C) COPYRIGHT IBM CORP. 1982, 1994.
Interactive SQL...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 13-14
Copyright Custom Systems Corp 2006
Prompting for SQL SELECT statement Specify SELECT Statement Type information for SELECT statement. Press F4 for a list. FROM files . . . . . . . __________________________________ SELECT fields . . . . . __________________________________ WHERE conditions . . . . __________________________________ GROUP BY fields . . . . __________________________________ HAVING conditions . . . __________________________________ ORDER BY fields . . . . __________________________________ FOR UPDATE OF fields . . __________________________________ Bottom Type choices, press Enter. Number of records to optimize . . . . . . __________ DISTINCT records in result file . . . . . N Y=Yes, N=No FOR FETCH ONLY . . . . . . . . . . . . . . N Y=Yes, N=No UNION with another SELECT . . . . . . . . N Y=Yes, N=No F3=Exit F4=Prompt F5=Refresh F6=Insert line F9=Subquery F10=Copy F12=Cancel F14=Delete F15=Split line F24=More keys
Interactive SQL...
Copyright Custom Systems Corp 2006
Interactive SQL...F13 Services Option 1 - Change Session Attributes
Direct SQL output to a display Change Session Attributes Type choices, press Enter. Statement processing . . *RUN *RUN, *VLD, *SYN SELECT output . . . . . 1 1=Display, 2=Printer 3=File Commitment control . . . *NONE *NONE, *CHG, *CS, *ALL Date format . . . . . . *MDY *JOB, *USA, *ISO, .... *MDY, *DMY, *YMD, .... Date separator . . . . . '/' *JOB, '/', '.', ',', .. ' ', *BLANK Time format . . . . . . *HMS *HMS, *USA, *ISO *EUR, *JIS Time separator . . . . . ':' *JOB, ':', '.', ',' ' ', *BLANK Data refresh . . . . . . *ALWAYS *ALWAYS, *FORWARD More... F3=Exit F4=Prompt F5=Refresh F12=Cancel
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 15-16
Copyright Custom Systems Corp 2006
Interactive SQL...F13 Services Option 1 - Change Session Attributes
Direct SQL output to a Display - continued Change Session Attributes Type choices, press Enter. Allow copy data . . . . *YES *YES, *OPTIMIZE, *NO Naming convention . . . *SYS *SYS, *SQL List of libraries . . . *LIBL Name, *LIBL, *USRLIBL *ALLUSR, *ALL, *CURLIB List type . . . . . . . *ALL *ALL, *SQL Decimal point . . . . . *PERIOD *SYSVAL, *PERIOD, .... Sort sequence . . . . . *HEX Name, *JOB, *JOBRUN *LANGIDUNQ, *LANGIDSHR *HEX Language identifier . . ENU Name, *JOB, *JOBRUN F4 for Prompt Bottom F3=Exit F4=Prompt F5=Refresh F12=Cancel
Copyright Custom Systems Corp 2006
F13 Services Option 1 - Change Session Attributes
Direct SQL output to a File Change Session Attribute Type choices, press Enter. Statement processing . . *RUN *RUN, *VLD, *SYN SELECT output . . . . . 3 1=Display, 2=Printer 3=File Output file: File . . . . . . . . . SQLDATA Name Library . . . . . . SKIP Name Member . . . . . . . . *FILE Name, *FILE, *FIRST Option . . . . . . . . 1 1=Create file 2=Replace file 3=Create member 4=Replace member 5=Add to member Authority . . . . . . *LIBCRTAUT *LIBCRTAUT, *CHANGE, *EXCLUDE, *USE authorization list Text . . . . . . . . . __________________________________ More... F3=Exit F4=Prompt F5=Refresh F12=Cancel
Interactive SQL...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 17-18
Copyright Custom Systems Corp 2006
Interactive SQL...F13 Services: Option 4 - Save Session in a Source File
Change Source File Type choices, press Enter. File . . . . . . QSQLSRC Name Library . . . SKIP Name Member . . . . . *FILE Name, *FILE, *FIRST Option . . . . . 1 1=Create new file 2=Replace file 3=Create new member 4=Replace member 5=Add to member For a new file: Authority . . *LIBCRTAUT *LIBCRTAUT, *CHANGE, *ALL *EXCLUDE, *USE authorization list name Text . . . . . __________________________________________ F3=Exit F5=Refresh F12=Cancel
Copyright Custom Systems Corp 2006
SQL Statement ProcessorSQL Statement Processor - RUNSQLSTM Command
Allows SQL statements, stored as an SQL script in a member in a source file, to be executed from that source member
Same statements can be run repeatedly Statements can be dynamically changed and run No compilation of SQL source required
Used to simplify the setup of an SQL database environment
Part of OS/400 (Database Manager) in V5R1
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 19-20
Copyright Custom Systems Corp 2006
SQL Statement Processor...SQL Statement Processor - RUNSQLSTM Command...
Easy way to do file maintenance on groups of related records
Insert/Add Update Delete
Output listing and resulting messages for the SQL statements executed sent to a print file QSYSPRT is default
Copyright Custom Systems Corp 2006
SQL Statement Processor...RUNSQLSTM Command Parameters
Run SQL Statements (RUNSQLSTM) Type choices, press Enter. Source file . . . . . . . . > QSQLSRC Name Library . . . . . . . . *LIBL Name, *LIBL, *CURLIB Source member . . . . . . > CUSTMSTR Name Commitment control . . . . . *NONE *NONE, *CHG, *CS, *ALL Naming . . . . . . . . . . . *SYS *SYS, *SQL
More... F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use dsply F24=More keys
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 21-22
Copyright Custom Systems Corp 2006
SQL Statement Processor...RUNSQLSTM Command Parameters...
Additional Parameters Severity level . . . . . . . 10 0-40 Date format . . . . . . . . *JOB *JOB, *USA, *ISO, *EUR... Date separator character . . *JOB *JOB, /, ., ,, -, ' '... Time format . . . . . . . . *HMS *HMS, *USA, *ISO, *EUR... Time separator character . . *JOB *JOB, :, ., ,, ' ',*BLANK IBM SQL Flagging . . . . . . *NOFLAG *NOFLAG, *FLAG ANS flagging . . . . . . . . *NONE *NONE, *ANS Decimal point . . . . . . . *SYSVAL *SYSVAL, *PERIOD, *COMMA Sort sequence . . . . . . . *JOB Name, *HEX, *JOB... Library . . . . . . . . . _______ Name, *LIBL, *CURLIB Language id . . . . . . . . *JOB *JOB, *JOBRUN... Print file . . . . . . . . . QSYSPRT Name Library . . . . . . . . . *LIBL Name, *LIBL, *CURLIB Bottom F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use dsply F24=More keys
Copyright Custom Systems Corp 2006
SQL Statement Processor...Frequently Used SQL Statements that are Allowed to be Used with RUNSQLSTM Data Definition Statements Data Manipulation Statements ---------------------------- ---------------------------- ALTER TABLE COMMIT COMMENT ON DELETE CREATE ALIAS INSERT CREATE COLLECTION LOCK TABLE CREATE INDEX ROLLBACK CREATE PROCEDURE UPDATE CREATE SCHEMA CREATE TABLE CREATE VIEW DROP COLLECTION DROP INDEX Miscellaneous Statements DROP PACKAGE ---------------------------- DROP SCHEMA CALL DROP TABLE SET TRANSACTION DROP VIEW GRANT PACKAGE GRANT PROCEDURE GRANT TABLE LABEL ON RENAME NOTE: SELECT statement NOT allowed REVOKE PACKAGE REVOKE PROCEDURE REVOKE TABLE
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 23-24
Copyright Custom Systems Corp 2006
SQL Statement Processor...SQL Statements in the source file
Only SQL statements & comments allowed in the SQL script that is stored in the source file SQL statements
No EXEC SQL or END-EXEC required Must end with a semicolon ( ; )
Comments Must begin with a double hyphen ( -- ) Limited to a single line
Copyright Custom Systems Corp 2006
SQL Script FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 *************** Beginning of data ************************************0001.00 -- 0002.00 -- Create CUSMST file, change SQL name to CUSTOMER_MASTER, 0003.00 -- change system name to CUSTMSTR, add file level text, 0004.00 -- add column headings for each field, add field level 0005.00 -- text for each field, and create keyed logical 0006.00 -- file called CSTNAMES over CUSTMSTR, create uniquely keyed 0007.00 -- logical called CUSTTYPE over CUSTMSTR. 0008.00 -- 0009.00 -- 0010.00 -- Step 1: Create CUSMST Table 0011.00 -- 0012.00 CREATE TABLE CUSMST(CUSTOMER_NUMBER FOR COLUMN CUSNBR DEC 0013.00 ( 5, 0) NOT NULL WITH DEFAULT CONSTRAINT NBR_CUSTOMER CHECK 0014.00 (CUSTOMER_NUMBER BETWEEN 10001 AND 89999 ), CNAME CHAR ( 30) NOT 0015.00 NULL WITH DEFAULT, CUSTOMER_TYPE FOR COLUMN CTYPE CHAR ( 1) NOT 0016.00 NULL WITH DEFAULT CONSTRAINT TYPE_CUSTOMER CHECK (CTYPE IN ('R', 0017.00 'S', 'W', ' ') ), YEAR_TO_DATE_SALES FOR COLUMN YTDSLS DEC ( 9, 2) 0018.00 NOT NULL WITH DEFAULT, LAST_ACTIVITY_DATE FOR COLUMN LADATE DATE 0019.00 NOT NULL WITH DEFAULT, CONSTRAINT CUST_NBR_PRIKEY PRIMARY KEY (CUSNBR));0020.00 -- 0021.00 -- 0022.00 -- Step 2: Change SQL Name for CUSMST Table to CUSTOMER_MASTER 0023.00 -- 0024.00 RENAME TABLE CUSMST TO CUSTOMER_MASTER; 0025.00 -- 0026.00 -- Step 3: Change System name for CUSTOMER_MASTER Table to CUSTMSTR0027.00 -- 0028.00 RENAME TABLE CUSTOMER_MASTER TO SYSTEM NAME CUSTMSTR; 0029.00 -- 0030.00 -- Step 4: Add File Level text to CUSTMSTR using LABEL ON statement0031.00 -- 0032.00 LABEL ON TABLE CUSTMSTR IS 'SQL Customer Master File for SQL test';
SQL Statement Processor...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 25-26
Copyright Custom Systems Corp 2006
SQL Script... FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 *************** Beginning of data ************************************0033.00 -- 0034.00 -- Step 5: Add column headings to CUSTMSTR using the LABEL ON 0035.00 -- statement 0036.00 -- 0037.00 LABEL ON COLUMN CUSTMSTR (CUSTOMER_NUMBER IS 0038.00 'Customer Number', CNAME IS 'Customer Name', 0039.00 CUSTOMER_TYPE IS 'Cust Type', YEAR_TO_DATE_SALES IS 0040.00 'YTD Sales', LAST_ACTIVITY_DATE IS 'Last Activity'); 0041.00 -- 0042.00 -- Step 6: Add column text to CUSTMSTR using the LABEL ON statement 0043.00 -- 0044.00 LABEL ON COLUMN CUSTMSTR (CUSTOMER_NUMBER TEXT IS 0045.00 'Customer Number', CNAME TEXT IS 'Customer Name', CUSTOMER_TYPE 0046.00 TEXT IS 'Customer Type', YEAR_TO_DATE_SALES TEXT IS 'YTD Sales', 0047.00 LAST_ACTIVITY_DATE TEXT IS 'Last Activity Date'); 0048.00 -- 0049.00 -- Step 7: Create a keyed logical file called CSTNAMES 0050.00 -- over CUSTMSTR with CNAME as the key using the 0051.00 -- CREATE INDEX statement 0052.00 --0053.00 CREATE INDEX CSTNAMES ON CUSTMSTR (CNAME); 0054.00 -- 0055.00 -- Step 8: Create a uniquely keyed logical file called CUSTTYPE 0056.00 -- over CUSTMSTR with key FIELDS CTYPE, CUSNBR using the 0057.00 -- CREATE INDEX statement 0058.00 -- 0059.00 CREATE UNIQUE INDEX CUSTTYPE ON CUSTMSTR (CTYPE, CUSNBR); 0060.00 -- 0061.00 -- End of SQL Statements 0062.00 --
SQL Statement Processor...
Copyright Custom Systems Corp 2006
Executing the SQL Script
RUNSQLSTM SRCFILE(QSQLSRC) SRCMBR(CUSTMSTR)
COMMIT(*NONE)
SQL Statement Processor...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 27-28
Copyright Custom Systems Corp 2006
iSeries Navigator SQL Script Center
Database - Run SQL Scripts
Copyright Custom Systems Corp 2006
iSeries Navigator SQL Script Center...
SQL Script Center
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 29-30
Copyright Custom Systems Corp 2006
iSeries Navigator SQL Script Center...
SQL Script Center - Create CUSTMSTR Script
Copyright Custom Systems Corp 2006
Operations Navigator
Database - Run SQL Scripts
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 31-32
Copyright Custom Systems Corp 2006
Operations Navigator...
Run SQL Scripts - Requires Client Access Express
Copyright Custom Systems Corp 2006
Operations Navigator...
Run SQL Scripts - Requires Client Access Express
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 33-34
Copyright Custom Systems Corp 2006
Embedding SQL In a HLL Program High Level Languages Supported
RPG COBOL C JavaAS/400 PL/I FORTRAN/400
Copyright Custom Systems Corp 2006
V5 SQL SupportPart 2: DB2 UDB Query Manager & SQL Development Kit
Program number 5722-ST1Query Manager Interactive SQL SQL precompilers
Required for embedding SQL in HLL programsMore later
SQL REXX interface
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 35-36
Copyright Custom Systems Corp 2006
V5 SQL Support... Part 1: DB2 UDB Database Manager
Included with OS/400 (operating system) V5: 5722-SS1
SQL parser and run time support SQL license (5722-ST1) not required to run SQL applications Support for compiled programs using embedded SQL
SQL APIs QSQPRCED - Provides extended dynamic SQL capability QSQCHKS - Provides syntax checking for SQL statements
X/Open SQL Call Level Interface V5R1 - SQL Statement Processor
RUNSQLSTM Command
Copyright Custom Systems Corp 2006
Basic SQL StatementsData Manipulation statements (DML)
SELECT - retrieves data; one row or multipleUPDATE - updates one row or multipleDELETE - deletes one row or multipleINSERT - adds one row or multiple
Data Definition statements (DDL)CREATE and DROP
Collection (library)Table (physical file)View (logical file)Index (logical file)
Embedding SQL In a HLL Program...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 37-38
Copyright Custom Systems Corp 2006
SQL Statements for Program ControlProvides Functions for Complex Processing
DECLARE CURSOR - builds temp result tableOPEN and CLOSE - open/close result tableFETCH - row retrievalCOMMIT and ROLLBACK - journaling functionsGRANT and REVOKE - security functions
Embedding SQL In a HLL Program...
Copyright Custom Systems Corp 2006
Why embed SQL in programs?Perform dynamic selection functions
ala OPNQRYF, except more flexible
Perform set-at-a-time functions under program controlEven to replace HLL I/O operations
e.g., READ, WRITE, UPDATE, CHAIN
What can be embedded?Basic SQL DDL and DML statements
e.g., SELECT, UPDATE, INSERT, CREATE TABLE, etc.
Program control statementse.g., DECLARE CURSOR, OPEN, CLOSE, FETCH, COMMIT, ROLLBACK
Embedding SQL In a HLL Program...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 39-40
Copyright Custom Systems Corp 2006
SQL Precompiler
UserSource
File
ModifiedSource
File
ProcessedSQL
Statements
Program
Access Plan
SQLPrecompiler
LanguageCompiler
Syntax checkX-ref host variablesSQL statements to callsComment SQL statements
HLLs with Embedded SQL Use a Precompiler
Copyright Custom Systems Corp 2006
RPG Interface - SourceSELECT A Single Row From A Table - RPG
I DS I 1 50 EMPNBR I 6 30 NAM I 31 32 DEPT C* C/EXEC SQL C+ SELECT nbr, nam, dpt C+ INTO :empnbr, :nam, :dept C+ FROM emp C+ WHERE nbr = :empnbr C/END-EXEC
V5R4 supports Embedded SQL in Free Form RPG
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 41-42
Copyright Custom Systems Corp 2006
Rules for Embedding SQL in RPGAll SQL statements must be coded on a C spec SQL statements begin with /EXEC SQL in positions 7-15
with the slash in position 7and end with /END-EXEC in positions 7-15You can enter SQL statements on same line as /EXEC SQL
However, /END-EXEC must be on a separate line Between beginning and ending delimiters, all SQL statements must have + in position 7Use Upper Case for C, /EXEC, and /END-EXEC in RPG IV SQL statements CANNOT
go past position 80be included via a /COPY statement
RPG Interface - Source...
Copyright Custom Systems Corp 2006
Cobol Interface - SourceSELECT A Single Row From A Table - Cobol
WORKING-STORAGE SECTION. 77 EMPNBR PIC S9(5) COMP-3. 77 DEPT PIC S9(3) COMP-3. 77 NAM PIC X(25). . . PROCEDURE DIVISION. EXEC SQL SELECT nbr, nam, dpt INTO :empnbr, :nam, :dept FROM emp WHERE nbr = :empnbr END-EXEC.
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 43-44
Copyright Custom Systems Corp 2006
RPG and Cobol InterfaceUsing Structures in SQL
Host structures are groups of variablesData structures in RPGGroup items in COBOL
Structures can be used in SQL statementsReplaces list of variables
I DS I 1 50 EMPNBR I 6 30 NAM I 31 32 DEPT C* C/EXEC SQL C+ SELECT nbr, nam, dpt C+ INTO :empnbr, :nam, :dept C+ FROM emp C+ WHERE nbr = :empnbr C/END-EXEC
Copyright Custom Systems Corp 2006
RPG and Cobol Interface...SELECT... INTO Expects Only a Single Row/Record
Retrieve column/field values into program variables One-to-one correspondence between SELECT list and INTO list
C/EXEC SQL C+ SELECT nbr, nam, dpt C+ INTO :empnbr, :nam, :dept C+ FROM emp C+ WHERE nbr = :empnbr C/END-EXEC
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 45-46
Copyright Custom Systems Corp 2006
RPG and Cobol Interface...Multiple Rows/Records Can Be Processed
Single SQL statement can process multiple rows/records
Processed rows/records NOT returned to program
C/EXEC SQL C+ UPDATE emp C+ SET dpt = :newdept C+ WHERE dpt = :olddept C/END-EXEC
SQL cursor operations used to return multiple rows/records to program
Copyright Custom Systems Corp 2006
Cursor OperationsSELECT & Process Multiple Rows/Records from a Table
1. Declare SQL cursor 2. Open cursor 3. Fetch next record 4. Process record (UPDATE/INSERT/etc) 5. If last record: go to Step 6,
else: go to Step 3
6. Close cursor
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 47-48
Copyright Custom Systems Corp 2006
Defining and Using an SQL Cursor
+ PROBLEM:
Update SALARY for all records in a specified department
C* C/EXEC SQL C+ C+ DECLARE empcsr CURSOR FOR C+ SELECT nbr, nam, sal C+ FROM emp C+ WHERE dpt = :dept C+ FOR UPDATE OF sal C+ C/END-EXEC C* C EXFMT PROMPT C* Example continued on next page
Cursor Operations...
Copyright Custom Systems Corp 2006
SQL Cursor Example - Continued C* C/EXEC SQL C+ C+ OPEN empcsr C+ C/END-EXEC C* C SQLCOD DOUNE 0 C* C/EXEC SQL C+ C+ FETCH NEXT FROM empcsr C+ INTO :number, :name, :salary C+ C/END-EXEC C*
Example continued on next page
Cursor Operations...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 49-50
Copyright Custom Systems Corp 2006
Cursor Operations...SQL Cursor Example - Continued
C* C SQLCOD IFEQ 0 C* C/EXEC SQL C+ C+ UPDATE emp C+ SET sal = sal + :raise C+ WHERE CURRENT OF empcsr C+ C/END-EXEC C END C END C* C/EXEC SQL C+ C+ CLOSE empcsr C+ C/END-EXEC
Copyright Custom Systems Corp 2006
DECLARE CURSOR StatementSimilar in function to HLL file declarations
F-specs or FD'sNo processing actually takes place - just definition
Host variables may be included in the statementCreated using an embedded SELECT command
most SELECT clauses may be used - ORDER BY, GROUP BY, etc
Must be declared before being referenced
DECLARE CURSOR...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 51-52
Copyright Custom Systems Corp 2006
DECLARE CURSOR StatementBy default, all columns may be updated or deleted
FOR UPDATE OF - lists the columns that are to be updatedcolumns listed in an ORDER BY clause may not be listed in FOR UPDATE OF clause also
FOR READ ONLY - specifies no updating/deleting allowed
Considerations:FOR READ ONLY - may improve performance; better documentationFOR UPDATE OF - security; may improve performance
DECLARE CURSOR...
Copyright Custom Systems Corp 2006
DECLARE CURSOR...DECLARE CURSOR Statement
With Hold clause useful with Commitment ControlBy default, cursors are closed when Commit/Rollback commands executeWith Hold - keeps cursor openWith Hold also an optional clause on the Commit/Rollback commands
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 53-54
Copyright Custom Systems Corp 2006
DECLARE CURSOR...DECLARE CURSOR statement prompting
Columns . . . : 1 71 Edit SKIP/QRPGSRC SEU==> _____________________________________________ AASQLTST FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+. *************** Beginning of data ********************** 0011.00 C/EXEC SQL 0012.00 ==>> C+ <<=== Prompt from 0013.00 ==>> C+ DECLARE empcsr CURSOR FOR <<=== any of 0014.00 ==>> C+ <<=== these lines 0015.00 C/END-EXEC ****************** End of data ************************* F3=Exit F4=Prompt F5=Refresh F9=Retrieve F10=Cursor F16=Repeat find F17=Repeat change F24=More keys
Any SQL statement can be prompted from within a source program
Copyright Custom Systems Corp 2006
DECLARE CURSORDECLARE CURSOR statement prompting - continued
Specify DECLARE CURSOR Statement Type choices, press Enter. Cursor name . . . . . . . . . . empcsr_____________________ Cursor type . . . . . . . . . . 1 1=Not scrollable 2=SCROLL 3=DYNAMIC SCROLL Hold . . . . . . . . . . . . . N Y=Yes, N=No Cursor definition . . . . . . . 1 1=SELECT statement 2=Statement name F3=Exit F5=Refresh F12=Cancel F21=Display statement
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 55-56
Copyright Custom Systems Corp 2006
DECLARE CURSOR statement prompting - continued
Specify SELECT Statement Type information for SELECT statement. Press F4 for a list. FROM files . . . . . . . emp_______________________________ SELECT fields . . . . . nbr,_nam,_sal_____________________ WHERE conditions . . . . dpt_=_:dept_______________________ GROUP BY fields . . . . __________________________________ HAVING conditions . . . __________________________________ ORDER BY fields . . . . __________________________________ FOR UPDATE OF fields . . sal_______________________________ Bottom Type choices, press Enter. Number of records to optimize . . . . . . __________ DISTINCT records in result file . . . . . N Y=Yes, N=No FOR FETCH ONLY . . . . . . . . . . . . . . N Y=Yes, N=No UNION with another SELECT . . . . . . . . N Y=Yes, N=No F3=Exit F4=Prompt F5=Refresh F6=Insert line F9=Subquery F10=Copy F12=Cancel F14=Delete F15=Split line F24=More keys
DECLARE CURSOR...
Copyright Custom Systems Corp 2006
DECLARE CURSOR statement prompting - continued
Columns . . . : 1 71 Edit SKIP/QRPGSRC SEU==> _____________________________________________ AASQLTST FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+. *************** Beginning of data ********************** 0011.00 C/EXEC SQL 0012.00 C+ 0013.00 C+ DECLARE empcsr CURSOR FOR 0013.01 C+ SELECT nbr, nam, sal 0013.02 C+ FROM emp 0013.03 C+ WHERE dpt = :dept 0013.04 C+ FOR UPDATE OF sal 0014.00 C+ 0015.00 C/END-EXEC ****************** End of data ************************* F3=Exit F4=Prompt F5=Refresh F9=Retrieve F10=Cursor F16=Repeat find F17=Repeat change F24=More keys
DECLARE CURSOR...
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 57-58
Copyright Custom Systems Corp 2006
Executes SELECT Statement For a Declared CursorBuilds the access path if necessarySuccessful Open places the file cursor before the first row of the result tableCursor must be closed before it can be opened
C*C/EXEC SQL C+ C+ OPEN empcsr C+ C/END-EXEC C*
OPEN Statement
Copyright Custom Systems Corp 2006
Two FunctionsPosition the cursor for the next operation
C*C/EXEC SQL C+ C+ FETCH NEXT FROM empcsr C+ C/END-EXEC
Bring rows/records into the programC* C/EXEC SQL C+ C+ FETCH NEXT FROM empcsr C+ INTO :number, :name, :salary C+ C/END-EXEC
FETCH Statement
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 59-60
Copyright Custom Systems Corp 2006
FETCH Statement...Alternatives to NEXT Processing
Cursor must be defined as a Scrollable C/EXEC SQL C+ C+ DECLARE empcsr SCROLL CURSOR FOR C+ SELECT nbr, nam, sal C+ FROM emp C+ ORDER BY empid C+ C/END-EXEC C* C/EXEC SQL C+ C+ FETCH PRIOR FROM empcsr C+ INTO :number, :name, :salary C+ C/END-EXEC
Copyright Custom Systems Corp 2006
FETCH Statement...Alternatives to NEXT Processing
Keyword Positions CursorNext On the next row after the current rowPrior On the row before the current rowFirst On the first rowLast On the last row Before Before the first row - must not use INTOAfter After the last row - must not use INTOCurrent On the current row (no change in position)
Relative n n < -1 Positions to nth row before currentn = -1 Same as Prior keywordn = 0 Same as Current keywordn = 1 Same as Next keywordn > 1 Positions to nth row after current
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 61-62
Copyright Custom Systems Corp 2006
FETCH statement prompting
Columns . . . : 1 71 Edit SKIP/QRPGSRC SEU==> AASQLTST FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+. 0023.00 C/EXEC SQL 0024.00 ==>> C+ <<=== Prompt from 0025.00 ==>> C+ FETCH <<=== any of 0026.00 ==>> C+ <<=== these lines 0027.00 C/END-EXEC ****************** End of data ************************* F3=Exit F4=Prompt F5=Refresh F9=Retrieve F10=Cursor F16=Repeat find F17=Repeat change F24=More keys
FETCH Statement...
Copyright Custom Systems Corp 2006
FETCH Statement...FETCH statement prompting - continued
Specify FETCH Statement Type choices, press Enter. Cursor name . . . . . empcsr___________________________ Positioning . . . . . 1 1=NEXT, 2=PRIOR, 3=FIRST 4=LAST, 5=BEFORE, 6=AFTER 7=CURRENT, 8=RELATIVE Fetch type . . . . . . 1 1=Single record 2=Blocked 3=Position only F3=Exit F5=Refresh F12=Cancel F21=Display statement
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 63-64
Copyright Custom Systems Corp 2006
Embedded SQL...BLOCKED FETCH statement prompting
Specify FETCH Statement Type choices, press Enter. Cursor name . . . . . empcsr___________________________ Positioning . . . . . 1 1=NEXT, 2=PRIOR, 3=FIRST 4=LAST, 5=BEFORE, 6=AFTER 7=CURRENT, 8=RELATIVE Fetch type . . . . . . 2 1=Single record 2=Blocked 3=Position only F3=Exit F5=Refresh F12=Cancel F21=Display statement
Copyright Custom Systems Corp 2006
Embedded SQL...BLOCKED FETCH statement prompting
Specify FETCH Statement Type choices, press Enter. FOR records . . . . . . . . 10____ 1-32767 -OR- FOR host variable . . . . . _______________________________ ______________________________ Type choices, press Enter. INTO . . . . . . . . . . . . 1 1=Host structure array 2=Record storage area Host structure array . . . . :array_________________________ ______________________________ F3=Exit F5=Refresh F12=Cancel F21=Display statement
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 65-66
Copyright Custom Systems Corp 2006
FETCH Statement...FETCH statement prompting - continued
Columns . . . : 1 71 Edit SKIP/QRPGSRC SEU==> AASQLTST FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+. 0023.00 C/EXEC SQL 0024.00 C+ 0025.00 C+ FETCH NEXT FROM empcsr 0025.01 C+ INTO :number, :name, :salary 0026.00 C+ 0027.00 C/END-EXEC ****************** End of data ************************* F3=Exit F4=Prompt F5=Refresh F9=Retrieve F10=Cursor F16=Repeat find F17=Repeat change F24=More keys
Copyright Custom Systems Corp 2006
Updating and Deleting DataPositioned Update and Delete Statements
Updates or deletes the current row of an updatable cursorCan only be done after successful Fetch operationAdds a "Where Current of" clause to the Update and Delete statements
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 67-68
Copyright Custom Systems Corp 2006
Updating and Deleting Data...Positioned Update and Delete Statements
C/EXEC SQL C+ DECLARE empcsr CURSOR FOR C+ SELECT nbr, nam, sal C+ FROM emp C+ ORDER BY empid C+ FOR UPDATE OF sal C/END-EXEC C* C/EXEC SQL C+ FETCH NEXT FROM empcsr C+ INTO :number, :name, :salary C/END-EXEC C* C/EXEC SQL C+ UPDATE emp C+ SET sal = sal + :raise C+ WHERE CURRENT OF empcsr C/END-EXEC
Copyright Custom Systems Corp 2006
Closes the cursorCursor must be opened in order to be closed DB2/400 closes cursors for other reasons also:
job endactivation group endsprogram endsmodules endscommit or rollback without a 'with hold' clauseerror handling......
Rule of thumbclose the cursor as soon as you're done with it
CLOSE Statement
C* C/EXEC SQL C+ C+ CLOSE empcsr C+ C/END-EXEC
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 69-70
Copyright Custom Systems Corp 2006
Error Detection and HandlingSQLCODE in SQL Communications Area
Status always returned in SQLCODEboth successful and unsuccessful statements
Programmer must check return codes within programSQL Communications Area (SQLCA)
contains feedback informationmust be included in all SQL programsRPG includes SQLCA automaticallyother languages must have specific include: EXEC SQL INCLUDE SQLCA END-EXEC
Copyright Custom Systems Corp 2006
SQLCAID Char(8) Structure identifying literal: "SQLCA"SQLCABC Integer Length of SQLCASQLCode Integer Return codeSQLErrML SmallInt Length of SQLErrMCSQLErrMC Char(70) Message Replacement textSQLErrP Char(8) Product ID literal: "QSQ" for DB2/400SQLErrD Array of Integers SQLErrD(1) - treated as Char(4); last 4 characters
of CPF or other escape message SQLErrD(2) - treated as Char(4); last 4 characters of CPF or other diagnostic message SQLErrD(3) - for Fetch, Insert, Update or Delete, number of rows retrieved or updated SQLErrD(4) - for Prepare, relative number indicating resources required for executionSQLErrD(5) - for multiple-row Fetch, contains 100 if last available row is fetched; for Delete, number of rows affected by referential constraints; for Connect or Set Connection, contains t-1 if unconnected, 0 if local and 1 if connection is remoteSQLErrD(6) - when SQLCode is 0, contains SQL completion message id
Error Detection and HandlingSQL Communications Area (SQLCA)
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 71-72
Copyright Custom Systems Corp 2006
Error Detection and Handling...SQL Communications Area (SQLCA) - continued
SQLWarn Char(11) Set of 11 warning indicators; each is blank, W, or NSQLWarn0 Char(1) Blank if all other SQLWARNx warning indicators are blank
W if any warning indicator contains W or NSQLWarn1 Char(1) W if a string column was truncated when assigned to host
variableSQLWarn2 Char(1) W if null values were eliminated from a functionSQLWarn3 Char(1) W if number of columns is larger than number of host
variablesSQLWarn4 Char(1) W if prepared Update or Delete statement doesn't include a
Where clauseSQLWarn5 Char(1) ReservedSQLWarn6 Char(1) W if date arithmetic results in end-of-month adjustmentSQLWarn7 Char(1) ReservedSQLWarn8 Char(1) W if result of character conversion contains the substitution
characterSQLWarn9 Char(1) ReservedSQLWarnA Char(1) ReservedSQLState Char(5) Return code; "00000' if no error or warning
Copyright Custom Systems Corp 2006
SQLCODE Error HandlingSQLCODE Values
SQLCODE (SQLCOD) contains return code = 0 Successful statement execution > 0 Successful, with warning condition < 0 Unsuccessful - statement failed
SQLCODE value indicates exact error or conditione.g.. 100 = Row not found (or end of file)e.g.. -552 = Not authorized to object
SQLCODE values have corresponding messagese.g.. SQL0100 = Row not founde.g.. SQL0552 = Not authorized to &1.
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 73-74
Copyright Custom Systems Corp 2006
C/EXEC SQLC+ SELECT name INTO :namC+ WHERE emp = 100C/END-EXECC SQLCOD IFLT 0 C EXSR ERRC ENDIFC SQLCOD IFGT 0C EXSR NFC ENDIF
EXEC SQLSELECT name INTO :lastnameWHERE emp = 100
END-EXEC.IF SQLCODE < 0 PERFORM ERROR-ROUTINE.IF SQLCODE > 0 PERFORM NOT-FOUND-ROUTINE.
COBOL
RPG
Error Checking Within a HLL Program
Copyright Custom Systems Corp 2006
WHENEVER Error Handling
C* C/EXEC SQL C+ C+ WHENEVER SQLERROR GO TO error C+ C/END-EXEC
WHENEVER statement checks SQLCACan branch to a location based on conditionThree conditions:
SQLWARNING (SQLCODE > 0 except 100) OR (SQLWARN0 = 'W')
SQLERROR (SQLCODE < 0)NOT FOUND (SQLCODE = 100)
Two possible actionsCONTINUEGO TO label
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 75-76
Copyright Custom Systems Corp 2006
C/EXEC SQL C+ DECLARE empcsr CURSOR FOR C+ SELECT nbr, nam, sal C+ FROM emp C+ WHERE dpt = :dept C+ FOR UPDATE OF sal C/END-EXEC C* C EXFMT PROMPT C* C/EXEC SQL C+ OPEN empcsr C/END-EXEC C* C SQLCOD DOUNE 0
Better Error Handling Technique
Directly following each SQL statement - code an 'error catcher'Easier to determine source of the errorMore discrete method of determining action to be taken
Copyright Custom Systems Corp 2006
C* C/EXEC SQL C+ C+ FETCH NEXT FROM empcsr C+ INTO :number, :name, :salary C+ C/END-EXEC C* C SQLCOD IFEQ 0 C* C/EXEC SQL C+ UPDATE emp C+ SET sal = sal + :raise C+ WHERE CURRENT OF empcsr C/END-EXEC C END C END C/EXEC SQL C+ CLOSE empcsr C/END-EXEC
Better Error Handling Technique...Directly following each SQL statement - continued
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 77-78
Copyright Custom Systems Corp 2006
Dynamic SQLWhat is Dynamic SQL?
Dynamic SQL is a different way to use SQLSQL statements are not pre-defined in program
Dynamically created on the fly as part of program logic
SQL pre-compiler cannot fully process dynamically created SQL statementsPREPARE statement used in program logic to compile dynamically created SQL statements
Can be used with SQL EXECUTE statement
Copyright Custom Systems Corp 2006
Dynamic SQL...Example 1
PROBLEM: Dynamically select records from the Employee Master File - Any fields, records, or sequence
C* C/EXEC SQL C+ C+ PREPARE search FROM :sqlstm C+ C/END-EXEC C* C/EXEC SQL C+ C+ DECLARE empcsr CURSOR FOR search C+ C/END-EXEC C*
C/EXEC SQL C+ C+ OPEN empcsr C+ C/END-EXEC
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 79-80
Copyright Custom Systems Corp 2006
Dynamic SQL...Example 2
User prompted to enter the delete condition (InpCond variable)
C* C Eval SQLStmtStr = 'Delete From Customer C Where ' C Eval SQLStmt = SQLStmtStr + InpCond C/EXEC SQL C+ C+ PREPARE DynSQLStmt C+ FROM :SQLStmt C+ C/END-EXEC C* C If (SQLCod = 0) And (SQLWn0 = *Blank) C/EXEC SQL C+ C+ EXECUTE DynSQLStmt C+ C/END-EXEC C*
Copyright Custom Systems Corp 2006
Dynamic SQL...Where to use Dynamic SQL
Report programs with user run time selectionFilesFieldsRecord selection criteriaSortingSQL built in functions
Whenever the exact syntax of an SQL statement cannot be determined beforehand
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 81-82
Copyright Custom Systems Corp 2006
Dynamic SQL...Where to use Dynamic SQL
Offers a high degree of application flexibilityCan create/build SQL statement
Based on parameters received fromInteractive user interfaceList selection techniquesApplication control file
Use any programming language
Copyright Custom Systems Corp 2006
Dynamic SQL...Performance considerations
Dynamic SQL can be resource intensiveRemember that a dynamic SQL statement has to be parsed (interpreted) and executed within the application program it is part of
Negative impact on performance
Use it ... Don't abuse it!
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 83-84
Copyright Custom Systems Corp 2006
Editing & Compiling Embedded SQLEmbedded SQL Tips
Use SQL source member typese.g., SQLRPG, SQLRPGLE, SQLCBL, SQLCBLLEPrompting won't work without SQL member type
You can prompt SQL statements in SEUYou MUST key both EXEC SQL and END-EXEC statements first
Then you can prompt (F4) for statements in betweenSame prompter as interactive SQL
Compile commandsPre-ILE compilers
CRTSQLRPG, CRTSQLCBLILE compilers
CRTSQLRPGI, CRTSQLCBLICreates either *PGM, *SRVPGM or *MODULE depending on parameter value specified for "Compile type" or OBJTYPE
Copyright Custom Systems Corp 2006
Editing & Compiling Embedded SQL...Embedded SQL Tips
Test statements in Interactive SQL before embedding themWhen exiting Interactive SQL session
You can save session statements to a source memberCopy from this member into your program source
Default for SQL is to use Commitment ControlRequires journaling
Program execution fails if updated files are not journaledTo request no commitment control
COMMIT(*NONE) on compile
SQL precompile step happens before RPG/COBOL compileTherefore, if SQL syntax or semantic error occurs, no "typical" compile source listing availableCan be very difficult to work through problems at this stage
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 85-86
Copyright Custom Systems Corp 2006
Performance TipsDesigning and Writing Efficient SQL Queries
SQL uses two basic ways to retrieve data Dataspace scan or arrival sequence Generally used when MORE than 20% of records will be selected Index based or keyed access Generally used when LESS than 20% of records will be selected
If SQL can use an index, performance can improve significantly!
Copyright Custom Systems Corp 2006
Performance Tips...Designing and Writing Efficient SQL Queries
Create indexes for fields frequently referenced in WHERE clause GROUP BY clause ORDER BY clause
Create indexes for fields that are frequently used to join files
Performance Analysis Tools STRDBMON Predictive Query Governor TRCJOB Visual Explain (V4R5)
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 87-88
Copyright Custom Systems Corp 2006
Performance Tips...Multiple Row FETCH
Based on host structure arrayRPG - Multiple Occurence Data StructureCOBOL - Occurs clause on declaration of the group item
Clause on FETCHFOR n ROWSn = number of rows to be returned
Specifies the number of rows to be retrieved to fill the array structure
Copyright Custom Systems Corp 2006
Performance Tips...Multiple Row FETCH - continued
D EMP DS Occurs(10)D NBR 5 0D NAME 25D JOB 1C*C Z-ADD 5 JOBC/EXEC SQLC+ FETCH Next C+ FROM CustomerCursor C+ FOR 10 ROWSC+ INTO Emp C/END-EXECC Eval ErrCond = SQLErrD(5) C*
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 89-90
Copyright Custom Systems Corp 2006
Performance Tips...Multiple Row FETCH - continued
First row fetched- placed into first element of host structureFetching stops when n rows are returned - or no more recordsProgram variable may be used to specify number of records to be fetched (NOTE: RPG IV - %Elem built in function)Be Careful - Fetching is always forward from position set by positioning keyword
FETCH RELATIVE -3 .... FOR 3 ROWSis not the same as
FETCH PRIOR .... FOR 3 ROWSResults:
SQLCA updates SQLErrCode(3) to reflect # rows retrievedIf no rows returned and no other exceptions, SQLCode is 100If row(s) returned contain last available row, SQLErrCode(5) set to 100
Copyright Custom Systems Corp 2006
Performance Tips...Blocked INSERT
Multiple elements from a host structure are inserted into a tableProgram variable may be used to set the number of records to be insertedExecutes as if n INSERT statements had been issued
but with improved performance
D EMP DS Occurs(10)D NBR 5 0D NAME 25D JOB 1C*C/ EXEC SQLC+ INSERT INTO CustomerC+ 10 ROWSC+ VALUES :EmpC/ END-EXEC
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 91-92
Copyright Custom Systems Corp 2006
Performance Tips...Invest time to review and become familiar with
Performance and OptmizationClick on Database in iSeries Information Center
You need to understand SQL performance before making extensive use of it!
Small changes in statements, indexes can significantly impact performance
SQL is another programming language and you have to learn the tricks
Copyright Custom Systems Corp 2006
Summary - Part 2Executing SQL on the iSeries & AS/400V5 SQL Support - The DetailInteractive SQLSQL Statement ProcessoriSeries Navigator SQL Script Center Embedding SQL in a HLL Program Basic SQL StatementsSQL PrecompilerRPG and Cobol InterfaceCursor OperationsError Detection and HandlingDynamic SQLEditing and Compiling Embedded SQLPerformance Tips
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 93-94
Copyright Custom Systems Corp 2006
V5 SQL Information SourcesiSeries Information Center Publications - Web or CD
SQL ReferenceSQL Programming ConceptsSQL Programming with Host LanguagesQuery Manager UseSQL Messages and Codes
To access Info Center on the Web http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsphttp://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp
In left scroll barClick on iSeries Information Center ...Click on DatabaseClick on Printable PDFsUse right scroll bar to scroll down to above SQL publication
DB2 UDB for iSeries on the Webhttp://www.ibm.com/servers/eserver/iseries/db2/
iSeries DevCon 2006
SQL Jumpstart Everything You Need to Know About SQL In Four Hours - Part 2 Page 95-96
Notes: ______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
Wellesley Information Services, 990 Washington Street, Suite 308, Dedham, MA 02026
Copyright © 2006 Wellesley Information Services. All rights reserved.