SQL Jumpstart

1
SQLJumpstart

• Mark Holm
• Centerfield Technology

2
Goals

• Get you started with SQL
• Show how SQL works with an existing environment
• Lay the groundwork for learning more

2
3
Notes

• V4R3 and higher syntax used in examples
• Examples show only a small subset of what can be
  done!

3
4
Agenda

• Explain why SQL is important
• Compare SQL with traditional approaches
• Data definition statements
• Selecting data
• Changing data
• Using SQL day to day

4
5
Why SQL?

• Simplifies cross platform development
• SQL generation can be automated
• Allows quick and flexible access to data
• Operates on sets of data -gt powerful
• Official industry standard used by pervasive APIs
  products
• ODBC/JDBC
• Most query tools
• Can perform better than traditional methods!

5
6
Why SQL?

• Business Intelligence
• Query tools
• Data manipulation (some) data cleanup
• Web sites internet applications
• Net.Data
• JDBC
• ERP applications
• SSA eBPCS
• JDE One World
• Mapics XA

6
7
Tools

• Interactive SQL (STRSQL)
• Run SQL statement (RUNSQLSTM)
• Operations Navigator
• Query tools (ODBC)
• And many others..

7
8
Basic statements

• Create Collection CRTLIB
• Create Table CRTPF
• Create View CRTLF (no key)
• Create Index CRTLF (key)
• Insert, Update, Delete add, change, and remove
  records
• Select select records columns to read

8
9
Data definition (DDL)

• Data definition statements create objects
• Collections (libraries)
• Tables (physical files)
• Views (non-keyed logical files)
• Indexes (keyed logical files)

9
10
Create collection

• Create collection Create library
• Example CREATE COLLECTION HR
• Objects created
• Library named HR
• Journal journal receiver
• Catalogs (e.g. SYSTABLES)

10
11
DSPLIB of a newly created COLLECTION
12
Create Table

• Create table Create physical file (CRTPF)
• Objects created, etc.
• Physical file
• File automatically journaled to QSQJRN
• Example Create a table to hold employee
  information

12
13
Create table
CREATE TABLE HR/EMPLOYEE
(FIRST_NAME FOR COLUMN FNAME CHAR (20 )
NOT NULL, LAST_NAME FOR COLUMN LNAME
CHAR (30 ) NOT NULL,
EMPLOYEE_ID FOR COLUMN EMP_ID NUMERIC (6 , 0) NOT
NULL WITH DEFAULT 0, DEPARTMENT FOR COLUMN DEPT
DECIMAL (3 , 0) NOT NULL WITH DEFAULT,
TITLE
CHAR (30 ) NOT NULL
WITH DEFAULT, HIRE_DATE FOR COLUMN HDATE
DATE NOT NULL)
13
14
Tables vs. Physicals

• Tables
• Dirty data cant be inserted
• Marked as a table
• Maximum 1 member
• No maximum size
• Default to reuse deleted rows
• Table is automatically journaled (collection)

• Physical files
• Dirty data can be inserted
• Not an SQL object
• No limit on members
• Default is to cap size
• Default to not reuse deleted records
• File is not automatically journaled

14
15
Prompt for CREATE TABLE in Interactive SQL
16
Create View

• Create View Create logical file (CRTLF)
• Objects created
• Logical file
• No keyed access path
• Example View to format an employees first last
  name and identify employees hired less than 2
  years prior to today

16
17
Create View
CREATE VIEW HR/NEWBIES (EMPLOYEE_NAME,
DEPARTMENT, HIRE_DATE) AS
SELECT concat(concat(strip(last_name),','),st
rip(first_name)),
department, hire_date
FROM HR/EMPLOYEE WHERE (year(current
date)-year(hire_date)) lt 2
17
18
DSPFD of a newly created VIEW
19
View uses

• Subset columns to users or applications
• Create new columns for users or applications
• Subset the rows returned (hide data)
• Join tables together

19
20
Views vs. Logical files

• Logical File
• Is a logical file
• Can be keyed or non-keyed
• Basic data manipulation
• Faster OPEN time
• Logical files can not reference LFs
• Many members

• View
• Looks like logical file
• Never has an access path (index)
• Powerful data manipulation
• Slower OPEN time
• Views can reference views
• One member

20
21
Create Index

• Create Index CRTLF (keyed logical)
• Objects created
• Logical file
• Physical access path (index)
• Created to improve performance -- not for
  function
• Example Create an index over the employee
  identifier

21
22
Create Index
CREATE UNIQUE INDEX HR/EMPIDIX
ON HR/EMPLOYEE (EMPLOYEE_ID ASC)
22
23
Indexes vs. Keyed logical files

• Index
• Looks like logical file
• Powerful data manipulation
• Not usable as file
• One member

• Logical file
• Is a logical file
• Basic data manipulation
• Like a normal file
• Many members

23
24
Deleting objects

• Done with a DROP statement
• DROP TABLE
• DROP VIEW
• DROP INDEX
• By default these statements drop dependent
  objects unlike CL commands (e.g. you cant delete
  a PF via DLTF if there are dependent logical
  files)

24
25
Data Manipulation (DML)

• Insert - add rows to a table
• Update - update column values in a table's rows
• Delete - delete rows in a table
• Select - retrieve rows from one or more tables

25
26
Insert

• Insert add a new row (record)
• Works just like HLL verbs
• Example Add a couple of employees to our table
  in the human resources library

26
27
Insert
INSERT INTO HR/EMPLOYEE
(FIRST_NAME, LAST_NAME, EMPLOYEE_ID, DEPARTMENT,
TITLE, HIRE_DATE)
VALUES ('Joe', 'Jones', 4793, 522, Hotshot
Programmer', '1998-01-16')
INSERT INTO HR/EMPLOYEE
VALUES ('Jane', Smith', 3290, 712, Psychic
Business Planner', '1999-12-01')
27
28
Update

• Update change data in existing row
• Works just like HLL verbs
• Example Give a 20 raise to one our star
  employees

UPDATE HR/EMPLOYEE
SET Salary Salary 1.2
WHERE Employee_id 5228
28
29
Delete

• Delete Remove one or more rows
• Example Fire the human resources department

DELETE FROM HR/EMPLOYEE
WHERE Department 108
29
30
Select

• Select statement is the most complex and powerful
  SQL statement
• If you know how to write good select statements
  you are well on your way to mastering the language

30
31
Select

• SELECT statement overview
• Select column(s)
• From table(s) or view(s)
• Where selection criteria
• Group By grouping columns
• Having selection criteria for groups
• Order By sort order for result rows

31
32
Select - example 1
SELECT FROM HR/EMPLOYEE

• Selects all columns (designated by )
• Selects data from the HR/EMPLOYEE table
• Select all rows

32
33
Select - example 2
SELECT LNAME, FNAME FROM HR/EMPLOYEE WHERE
EMPID 556

• Selects only two columns
• Selects data from the HR/EMPLOYEE table
• Select only rows with EMPID EQ 556

33
34
Select - example 3
SELECT ShirtName, SKU, PRICE.8
AS DISCPRICE FROM PRODLIB/INVENTORY WHERE
SYLE HAWIAN OR COLOR PINK

• Selects two columns and calculates a third called
  DISCPRICE
• Selects data from the INVENTORY table
• Selects parts in a defined range

34
35
Select - example 4
SELECT TERRITORY, SUM(SaleAmt),
AVG(SaleAmt), COUNT() FROM
PRODLIB/SALES WHERE COUNTRY USA GROUP
BY TERRITORY HAVING AVG(SaleAmt) gt
10000.0 ORDER BY 2 DESC

• Rank sales in largest to smallest order
  summarized by territory. Only look at sales in
  the United States that average more than 10000.

35
36
Useful functions
36
37
More useful functions
37
38
Embedded SQL

• Combines the power of SQL with HLLs like RPG,
  COBOL, or C
• All of the benefits of HLLs such as performance,
  complex logic, and control over data
  manipulation.with SQLs capabilities for set at
  a time processing and dynamic selection

38
39
Embedded SQL

• The following statement is an example of what an
  embedded statement looks like
• SELECT fname, lname, address
• FROM employee
• WHERE empid INPUTID
• INPUTID is a host variable

39
40
Embedded SQL

• Embedded SQL programs are pre-compiled and then
  compiled again
• CRTSQLRPGI, CRTSQLCBLI
• Pre-compiler identifies SQL with special tags to
  indicate start and end of SQL statement

40
41
41
42
Embedded SQL

• C/Exec SQL
• C Update Employee
• C Set lname NewLN -- assign new last
  name
• C Where empId EmployId
• C/End-Exec
• --------------------------------------------------
  ---
• Exec SQL
• Update Employee
• Set lname NewLN
• Where empId EmployId
• End-Exec

RPG
COBOL
42
43
Tips

• To start learning SQL use ISQL or Operations
  Navigator
• Create a sample collection and tables
• Add, change, and delete data
• Try different select statements and functions to
  get a feel for the power of SQL
• Read and try examples listed in the book and
  internet resources in this presentation
• Create tables with short and long names

• Put related SQL objects in the same collection
• If selecting a small amount of data, create an
  index over the columns that most uniquely
  identify the data
• Be careful with UPDATE and DELETE statements
  without a WHERE clause
• Use the AS clause to give calculations
  understandable names

43
44
Other resources

• Database Design and Programming for DB2/400 -
  book by Paul Conte
• SQL for Smarties - book by Joe Celko
• SQL Tutorial - www.as400network.com
• AS/400 DB2 web site at http//www.as400.ibm.com/db
  2/db2main.htm
• Publications at http//publib.boulder.ibm.com/pubs
  /html/as400/
• Our web site at http//www.centerfieldtechnology.c
  om

44
45
Summary

• SQL is becoming increasingly important for many
  reasons
• Basic DDL is very similar to DDS
• Power and flexibility come with the DML
  statements -- in particular SELECT
• There are many resources to help take you to the
  next level of understanding

46

• I hear and forget.
• I see and remember.
• I do and I understand.
• Kung Futse
• 551 B.C.