SQL. Agenda. Where you want to go Today for database access. What is SQL

SQL

Where you want to go Today

for database access

Agenda

„What is SQL

„AS/400 database query tools

„AS/400 SQL/Query products

„SQL Execution „SQL Statements / Examples „Subqueries „Embedded SQL / Examples „Advanced SQL „SQL Performance / Tuning „Future Enhancements to SQL „Conclusion

What is SQL

„SQL - Structured Query Language

–A standardized language for defining and manipulating data in a relational database. –English keyword oriented

–A tool for the application development environment

zQuery

zData definition zData manipulation zData Control

–Set-At-A-Time processing

–SQL is not an end user query product

SQL Openness - Standards

„SQL in DB2/400 has very high standards

compliance

–Entry level of 1992 ANSI, ISO, and FIPS SQL Standard

„X/Open SQL Call Level Interface (CLI)

provides full compliance with level 1 of Microsoft's ODBC support plus many of level 2 functions.

AS/400 database query tools

„Open Query File (OPNQRYF) command

„Query/400

„SQL/400 Query Manager

„SQL/400

–Interactive SQL, Statement Processor

„SQL/400 run time support

–SQL Parser, API's, commands

„X/Open SQL Call Level Interface

„OS/400 Query Management

Open Query File (OPNQRYF)

command

„This is a programming command that can

be used with a high-level language program. It acts as a filter between the program and the database, so that the program receives only those records that meet the criteria specified in the OPNQRYF command. It is not built on the SQL/400 language.

Query/400

„This tool allows easy reporting on database

information. It is not built on the SQL/400 language.

„One feature unique to Query/400 is the

option of merging data into OfficeVision/400 documents.

SQL/400 Query Manager

„This tool allows easy reporting on database

information. It is the interface for using the SQL/400 language and OS/400 Query Management. SQL/400 Query Manager provides three integrated interfaces for the creation and maintenance of queries, report forms, and database tables.

SQL/400

„Programmers use the SQL/400 language to

query and manipulate data in a database. Most SQL functions can be performed either interactively or in application programs

written in a high-level programming language.

„Interactive SQL can also be used by

programmers to test SQL statements.

„The SQL statement processor allows you to

run a series of SQL statements stored in a source file.

SQL/400 run-time support

„SQL run-time parses SQL statements and

runs any SQL statements. This support is part of the Operating System (OS/400) licensed program.

„It allows applications that contain SQL

statements to be run on systems where the SQL Development Kit is not installed.

X/Open SQL Call Level Interface

„Allows any of the ILE languages to access

SQL functions directly through procedure calls to a service program provided by the system. Using this interface, one can perform all the SQL functions without the need for a precompile.

„It is ideally suited for a client-server

environment, in which the target database is not known when the application is built.

„The Query Management Communications

Programming Interface lets users access information on SAA-compliant relational databases and control how this data

appears when formatted into a report. This standard is also found on other SAA

platforms such as PC's and Mainframes.

DB2/400 Database Manager

(Included in OS/400 V3Rx)

„Open Query File (OPNQRYF) command

–Not built on the SQL/400 language

„SQL Parser and run time support

–Allows SQL programs to be ported and run on AS/400's

not containing SQL kit

–Commands available to create a single runnable SQL

statement

„SQL API's

–QSQPRCED - executes SQL packaged statements –QSQCHKS - checks syntax of SQL statements

„X/Open SQL Call Level Interface (V3R2)

–ODBC support

Query/400

„Separate feature not included in OS/400

„Same basic interface as the System/36

DB2/400 Query Manager and SQL

Development Kit

zThe languages supporting embedded SQL statements: zILE C/400

zCOBOL/400, ILE COBOL/400 zFORTRAN/400 zAS/400 PL/I zRPG/400 (III), ILE RPG/400 zSQL/400 Query Manager zQuery Management

AS/400 SQL Execution

zWork with queries, report forms, tables, query

SQL Execution (continued)

„ SQL embedded in HLL programs

–Static SQL

zSQL statement type and structure known at compile

time

–Dynamic SQL

zSQL statement and structure created at run time zResource intensive

„ SQL run-time support

–SQL Parser, SQL API's

–CRTQMQRY, STRQMQRY commands

–Default source file - QQMQRYSRC –New object type - QMQRY

„ X/Open SQL CLI

Database Terminology Table

SYSTEM TERMS SQL TERMS

AS/400 System Name Database Name

Library Collection

Physical File Table

Logical File View

Record Row

Field Name Column Name Data Dictionary Catalog

SQL/400 Statements

–CREATE or DROP

zDatabase or Collection

zTable (define rows and columns)

zView (column projection and ordering) zIndex (row ordering)

zProcedure (External Procedure) zPackage (DRDA Remote Support)

–LABEL/COMMENT ON (table/column text) –ALTER TABLE

SQL/400 Statements (cont.)

„Data Manipulation

–INSERT INTO (rows into tables) –DELETE (rows from tables) –UPDATE (table rows)

–SELECT (rows from tables) –OPEN (opens the cursor)

–DECLARE CURSOR (set pointer) –FETCH (retrieve columns)

SQL/400 Statements (cont.)

„Remote Data Access

–CONNECT

–SET CONNECTION

–RELEASE –DISCONNECT

„Data Access Control

–GRANT or REVOKE zTable or Package „Data Integrity –COMMIT –SET TRANSACTION –ROLLBACK –LOCK TABLE

SQL/400 Statements (cont.)

–DESCRIBE (retrieves info on PREPARE)

–EXECUTE (runs the prepared SQL statement) –EXECUTE IMMEDIATE (prepares and

executes an SQL statement)

SQL/400 Statements (cont.)

„Miscellaneous

–BEGIN/END DECLARE SECTION (not used in RPG)

–CALL (call external procedure)

–DECLARE (Cursor, Procedure, Statement, Variable)

–INCLUDE (insert data set into SQL statements) –SET OPTION (SQL options)

–SET RESULT SETS (data from CA/400) –WHENEVER (exception handling)

SELECT (columns, *, or expressions) FROM (tables or views) WHERE (row selection criteria) GROUP BY (columns)

HAVING (GROUP BY selection criteria) ORDER BY (columns)

SELECT *

FROM empl

Number Name Position Sex Salary

10 AMY 2 F 1200

35 JOE 5 M 1000

30 JON 7 M 1500

20 DON 5 M 1150

25 ANN 8 F

SELECT Statement keywords

„Keywords in WHERE clause:

–Greater than (or =), Less than (or =), Equal –Not greater, Not less, Not equal

–AND, OR, NOT

–Range - inclusive constant range (BETWEEN, NOT BETWEEN)

–Values - list of constant values (IN, NOT IN) –Pattern matching (LIKE, NOT LIKE) with wild

cards

z% = any number of characters z_ = exactly 1 character

SELECT Where Example

„Columns can be reordered

„Selection criteria can be applied

SELECT Name, Number From Empl

Where Position = 5

Name Number

JOE 35

SELECT Where example

SELECT name, position FROM Empl

WHERE position BETWEEN 5 AND 7

Name Position

JOE 5

JON 7

DON

SELECT name, number FROM Empl

WHERE name LIKE 'A%'

Name Number

AMY 10

ANN

SELECT ORDER BY example

SELECT name, number, position FROM Empl

WHERE sex = 'M' AND (salary * 12) > 12000 ORDER BY name _{Name Number Position}

JOE 35 5

JON 30

SELECT name, number, position FROM Empl

ORDER BY 2

Name Number Position

JON 30 7

SELECT GROUP BY example

SELECT position, SUM(salary), COUNT(*) FROM Empl

GROUP BY position ORDER BY position

Position SUM(Salary) Count

2 1200 1

5 2150 2

7 1500 1

8 1500 1

SELECT WHERE Join example

Number Name Position Sex Salary

10 AMY 2 F 1200 35 JOE 5 M 1000 30 JON 7 M 1500 20 DON 5 M 1150 25 ANN 8 F 1500 Position Description 2 Operator 5 Programmer 7 Manager 8 Analyst

SELECT name, EMPL.position, description FROM Empl, Job

WHERE Empl.position = Job.position

Name Position Description

AMY 2 Operator

Joining Tables

„WHERE clause used to specify join

conditions

„Join columns must have compatible

attributes

–number to number, character to character –padding and truncation

„Join conditions (=, ~=, <>, <, ~<, <=, >, ~>,

>=)

„SELECT clause can contain:

–columns from any joined table

„SQL is a SET-AT-A-TIME language

„Give all programmers a 50% salary increase

UPDATE Statement

UPDATE Empl

SET salary = salary + (salary * 0.50) WHERE position = 5

INSERT Statement

One Row at a time:

INSERT INTO Empl

(name, number, position, salary, sex) VALUES ('PAT',11,2,1300,'F')

Multiple Rows at one time:

INSERT INTO Empl

SELECT number, name, position, salary, sex FROM Emplnew

WHERE position = 9

SQL 'Built-in' Functions

„Can be used anywhere expressions are used

„Column Functions

–Applies to an entire column (grouping of records)

„Scalar Functions

–Applies to a single value rather than a set of records

zMathmatical functions zLogical Expressions zString Manipulation zDate/Time functions zData format manipulation zOther DB/2 functions

Column Functions

„AVG

–Average of a numeric column

„SUM

–Adds numeric column

„MAX

–Maximum value of a numeric or character

„MIN

–Minimum value of numeric or character

„VAR

„ Variance between the sum of 2 numbers

„COUNT

–Counts the number of rows

Column Functions examples

SELECT COUNT (*) FROM Empl

SELECT MAX(salary), MIN(salary) FROM Empl

COUNT 6

MIN MAX

Scalar Functions

„Mathmatical Functions (23)

–COS, SIN, COT, TAN, LOG, SQRT...

„Logical Expressions (3)

–LAND, LOR, XOR

„String Manipulation(17)

–LOCATE, TRIM, STRIP, SUBSTR, TRANSLATE...

„Date/Time functions (19)

–DAYOFYEAR, NOW, QUARTER, WEEK...

„Data format manipulation (11)

–DECIMAL, FLOAT, HEX, INT, ZONED...

„Other DB/2 Functions (4)

–NODE, PARTITION, RRN

Scalar Function examples

SELECT number, name, YEAR(CURRENT DATE - birthdate) FROM Empl

WHERE position = '5'

SELECT *

FROM Project

Retrieve the number, name, and age of employees in position 5

Select records in project file with the word 'operation' in the project name

Sub Queries

„A subquery is a SELECT statement embedded in

the WHERE or HAVING clause of another SQL query

–also called inner query or nested query

„Types of Subqueries

–returns single value –returns list of values –Inner and Outer queries

Sub Query Example

„Select maximum salary of employees in position 5

–returns all records having the maximum salary in

position 5

SELECT * FROM empl WHERE position = 5 and salary =

(SELECT MAX(salary) FROM empl

WHERE position = 5)

Embedded SQL

„New source type

–SQLRPG (RPGIII), SQLRPGLE (RPGIV)

„SQL uses a precompiler (option 14 in PDM)

–CRTSQLRPG (RPGIII), CRTSQLRPGI (RPGIV)

User Source File Modified Source File Processed SQL Stmts Program Access Plan SQL

Precompiler LanguageCompiler

Syntax Checking X-ref host variables SQL statements to call Access paths to use

Placing SQL in RPG

„SQL statements executed based on logic in program „SQL statements can only be placed in mainline or

subroutine of calculation specification

–cannot be placed in total time processing

zcan call subroutine containing SQL statements

„/COPY within RPG code

–copy in source with SQL statements

„SQL INCLUDE statement within SQL

RPG syntax for embedded SQL

„/EXEC SQL in pos 7 indicates beginning of SQL

statement

–SQL statement begins in pos 17 - 80 of this or following lines

„/END-EXEC in pos 7 indicates end of SQL

statement

–pos 17 - 80 must be blank

„Continuing the SQL statement

–+ in pos 7, pos 8 must be blank, statement can be in pos 9

- 80

„Upper or lower case allowed in SQL code

„Comment allowed in SQL statement by using an *

in pos 7

Host Variables

„SQL Host Variable is an HLL variable used in an

embedded SQL statement

„Defining Host variables

–all host variables within an SQL statement must be preceded

by a colon (:)

–if the variable exists in more than one table (joined files), the

variable name must be prefixed with the table name (or number) and a period

–host variables must be unique within the program

Host Variable restrictions

„ Any valid RPG/400 variable name can be used for a

host variable except for the following:

–host variable names that begin with the characters 'SQ',

'SQL', 'RDI', 'DSN'. These names are reserved for the database manager.

–indicator field names (*INxx) –tables

–look-ahead fields –named Constants

–RPG reserved date fields (UDATE, UDAY...)

–host variables passed to SQL that have also

been used in an RPG CALL/PARM function

zif field cannot be used in the result field of the PARM, it

cannot be a host variable

Variable restrictions (cont.)

„Additional RPG IV restrictions:

–multiple dimension arrays

–definitions requiring resolution of the field size (*SIZE,

*ELEM)

–date formats *JUL, *MDY, *DMY, *YMD can only

represent dates from 1940 - 2039.

zan error will occur when converting a 4 byte year to

Embedded SQL

Summarized column example

„Returns total of column for selected rows

C/EXEC SQL

C+ SELECT SUM(THRS) INTO :HOURS C+ FROM S#TIME

C+ WHERE CSTCOD = :CSTCOD AND C+ PROJECT = :PROJECT AND C+ TNOCH <> 'NC' AND

C+ TTASK <> 'INT' C/END-EXEC

Embedded SQL

Select single row example

I DS 1 50NBR I 6 8 NAM I P 9 132EARN C* C Z-ADD10 NBR C/EXEC SQL

C+ SELECT number, name, salary C+ INTO :nbr, :nam, :earn C+ FROM Empl

C+ WHERE number = :nbr C/END-EXEC

NBR NAM EARN

„1. Define RPG fields „2. Declare SQL Cursor „3. Open Cursor

„4. Fetch next record

„5. Process record (UPDATE/INSERT, ect.) „6. If last record go to Step 7, else: go to Step 4 „7. Close Cursor

Embedded SQL

SELECT multiple rows

Embedded SQL

SELECT multiple rows example

DRECDS E DS EXTNAME(EMPL) OCCURS(10) : C/EXEC SQL C+ DECLARE C1 FOR C+ SELECT * C+ FROM EMPL C/END-EXEC C/EXEC SQL C+ OPEN C1 C/END-EXEC * top of loop C/EXEC SQL

C+ FETCH C1 FOR 10 ROWS C+ INTO :RECDS

C/END-EXEC

* do something with record(s) and loop : C/EXEC SQL

C+ CLOSE C1 C/END-EXEC

„Updates a posted flag to a transaction file

after all records in batch are processed

Embedded SQL

Update example

C EVAL POSTED = 'Y' C EVAL TODAY = UDATE C*

C/EXEC SQL UPDATE TRANS C+ SET POSTFLG = :POSTED C+ WHERE TRNDATE = :TODAY C/END-EXEC

Embedded SQL

Dynamic SQL statement

C/EXEC SQL

C+ PREPARE STMT4SQL FROM :SELECTSTMT C/END-EXEC

C/EXEC SQL

C+ DECLARE NEXTREC CURSOR FOR STMT4SQL C/END-EXEC

C/EXEC SQL C+ OPEN NEXTREC C/END-EXEC

C DOU EOF = *ON C/EXEC SQL

C+ FETCH NEXTREC INTO :RECDS C/END-EXEC

C IF SQLCOD <> 0 C EVAL EOF = *ON C ELSE C EXSR LOADSF C ENDIF C ENDDO C/EXEC SQL C+ CLOSE NEXTREC C/END-EXEC

Setup data structure RECDS to receive data Put SQL statements into variable SELECTSTMT

Stored Procedures

Calling programs using SQL

PROGRAM: PGMA C/EXEC SQL

C+ DECLARE PGMB PROCEDURE *

* define fields being passed between programs *

C+ ( :action IN char(1), C+ :name OUT char(3), C+ :number IN char(5) )

*

* define location of program being called *

C+ (External Name JUMPSTART/PGMB C+ Language RPGLE

C+ Simple Call) C/END-EXEC :

C/EXEC SQL

C+ CALL PGMB(:function, :name, :nbr) C/END-EXEC

„Program A contains the SQL parameters and

statements

„Program B is a normal RPG program (no SQL

statements needed)

Stored Procedures (cont.)

PROGRAM: PGMB C *ENTRY PLIST

C PARM FUNCTION C PARM EMPL_NAME C PARM EMPL_NBR

Advanced SQL

„SQL Communications Area (SQLCA)

–automatically placed into RPG program by SQL precompiler –contains feedback information

–SQLCODE or SQLSTATE

„SQL error codes (SQLCOD)

–0 = statement executed successfully –+n = executed but with warning

z+100 'No Data Found'

–-n = Error occurred and statement did not run –use SQLCA in HLL or with SQL WHENEVER

„SQL Package

–normally used to store access plan for remote query

Performance: SQL vs. RPG

„SQL/400 beats RPG IV in two tests and very close in

several other tests:

–SQL

zinserting large numbers of records

zretrieving records sequentially by key (selected columns)

–RPG IV

zretrieving records sequentially by key (all columns) zupdating records sequentially

zretrieving a set of records within a major key zretrieving individual records by primary key zretrieving individual records by RRN

SQL Performance tips

„For high-volume sequential access, tune the

block size on the OVRDBF command

„Use a blocked insert statement when adding

large numbers of new records

„Use a multiple-row Fetch statement when

sequentially retrieving 10 or more records at a time

„Limit the number of columns you retrieve or

update

Tuning SQL

„Predictive Query Governor

–system predicts and expedites queries

„Data Management and Query Optimizer

–Data Management algorithms for index usage and row selection –Query Optimizer selects most efficient valid technique for the

type of query being processed

„Start Database Monitor - STRDBMON (V3R6) „Print SQL Information command - PRTSQLINF „Query Debug Messages

The Predictive Query Governor

„ Predictive - guesses how long the query will take before it starts „ It works with all AS/400 Query products, not just SQL based „ Can be used for tuning queries

„ Parameters for system and job

–specify how long of a query can be run

–setup the system to either prevent long queries or warn about long queries.

„ WRKSYSVAL QQRYTIMLMT

–sets the value for everyone

„ CHGQRYA

–overrides the query time limit for a job

Print SQL Information command

(PRTSQLINF)

„Can only be run against a saved access plan

–query must have been executed or 'PREPARE' d

„information about SQL statements embedded in a

program including:

–SQL statements being executed

–access plan (access path to be used/created) –estimated run time

„ CPI4321 - Access path built for file

„ CPI432F - Access path suggestion for file „ CPI4324 - Temporary file built for file „ CPI4322 - Access path built for file

„ CPI4323 - The OS/400 Query access plan has been rebuilt „ CPI4329 - Arrival sequence access was used for file

„ CPI432A - Query optimizer timed out for file

„ CPI432E - Selection fields mapped to different attributes „ CPI4325 - Temporary result file built for query

„ CPI4328 - Access path of file was used by query

Query Optimizer

TOP 10 Debug Message List

Other SQL Topics

„Dynamic SQL

„Cursors

–serial, scrolling

„Using Null values

„Date/Time Arithmatic

„SQL Descriptor Areas (SQLDA)

„Varying-list SELECT statement

„100% compliance with ANSI and ISO SQL

standards

–column-level locking (V4R2) –check constraints (V4R2) –default User (V4R2)

–SQL-only stored procedures –compound SQL

–Case and Cast expressions (V4R2)

„DB2 Multisystem support and SMP support

„New and improved X/Open API's

„Java Database Connectivity (JBDC)

Future enhancements - V4Rx

SQL Conclusion

„English keyword oriented language

–easy to learn

„Greater programmer flexibility

–programming interface into queries allows:

zrun time record selection, file selection,

sorting criteria

zrun time substitution variables

–ad-hoc programming (file conversions) –embedded SQL

SQL/400 Conclusion (cont.)

–Java, ODBC/JDBC driver compatibility

„Many tools available

„Can be optimized and contolled

„SQL/400 open to other platforms

–SAA-compliant SQL statements are portable to other SAA platforms

–SQL compiled objects can be run without SQL/400 license

„Provides table (file) creation function

Bibliography

„Manuals

–SC41-4611 DB2 for OS/400 SQL Programming V3R2 –SC41-4612 DB2 for OS/400 SQL Reference V3R2

–SC41-3806 DB2 for OS/400 SQL Call Level Interface V3R2

„Articles

–Dynamic SQL in RPG;Ed Oleson

–DB2/400 Query Manager: The New AS/400 Query;Skip Marchesani;Sept/97 –Using SQL on the AS/400;Skip Marchesani;Aug/97

Bibliography: Perodicals

–SQL Minimalism; Thomas Stockwell; Midrange Computing; Apr/96

–A quick introduction to SQL with RPG IV; Richard Rubin; News/400; Jan/97 –An Introduction to SQL Subqueries; Ted Holt; Midrange Computing; Feb/96 –OS/400 Tools to Speed Up SQL and OPNQRYF; Suzan Bestgen & Tom

Schreiber; News/400; Nov/96

–SQL/400 vs. RPG IV: Which One's Faster?; Paul Conte; News/400; Sept/96 –SQL/400 vs. DDS: Which Should You Use?; Paul Conte; News/400; Sept/96 –DB2/400: Coming Attractions; Kent Milligan; News/400; Sept/97