-
-
-
-
-
-
4 major categories of SQL statements
- 1) DDL - Data Definition Language
- 2) DML - Data Manipulation Language
- 3) Enbedded SQL Host Language
- 4) SPL - SQL Procedural Language
-
DDL
- - category of SQL statements
- -Data Definition Language
- - CREATE TABLE
- - CREATE VIEW
- - CREATE INDEX
- - ALTER TABLE
-
DML
- - category of SQL statements
- - Data Manipulation Language
- - SELECT
- - INSERT
- - UPDATE
- - DELETE
-
Enbedded SQL Host Language
- - category of SQL statements
- - DECLARE CURSOR
- - FETCH
- - SELECT INTO (get)
-
SPL
- - category of SQL statements
- - SQL Procedural Language
- - to create programs and funtions that can be executed from within SQL environment
-
SQL Development tools
- 1) Interactive SQL
- 2) RUNSQLSTM command
- 3) IBM Data Studio
-
Interactive SQL
- SQL Development tool
- STRSQL - Start SQL Interactive Section
- CREATE TABLE AS400FSP1/HSPPASS (LUSER CHAR(12)
- NOT NULL PRIMARY KEY,
- LPASS CHAR(12) DEFAULT,
- LFNAME CHAR(20) DEFAULT,
- . . .
- RCDFMT HSPPASSR
- - PRIMARY KEY
- - combines "NOT NULL" and "UNIQUE"
- - table can have only 1 PRIMARY KEY
- can contain combination of 2 or more columns
-
RUNSQLSTM command
- SQL Development tool
- provides a scripting facility for executing SQL statements stored in a source file memeber
- Steps:
- 1) F6 to create source memeber type SQLRPGLE
- 2) Enter:
- CREATE TABLE AS400FSP1/HSPPASS (LUSER CHAR(12)
- NOT NULL PRIMARY KEY,
- LPASS CHAR(12) DEFAULT,
- LFNAME CHAR(20) DEFAULT,
- . . .
- RCDFMT HSPPASSR
- 3) Run command - RUNSQLSTM SRCFILE(AS400FSP1/ABOOK) SRCMBR(B060_SQLST)
- 4) Table AS400FSP1/HSPPASS1 is created
-
IBM Data Studio
- SQL Development tool
- can be installed alongside the RDi, so 2 products can share common futures
-
SQL Column Definition
- CHAR Fixed Char
- VARCHAR Var Char
- DECIMAL Packed Numeric
- NUMERIC Zoned Numeric
- SMALLINT 2-byte Int
- INTEGER 4-byte Int
- BIGINT 8-byte Int
- DATE
- TIME
- TIMESTAMP
-
SQL Constrain Clauses
- NOT NULL
- - prevents from containing null (absence of data for a colomn)
- DEAFULT
- - to contain default value
- UNIQUE
- - evety row in the table has a unique value
- PRIMARY KEY
- - combines NOT NULL and UNIQUE
-
Qualified Name
- - includes name of the schema (library)
- - IBM i objects
- - AS400FSP1/PTNLIST
- - SQL naming convention
- - AS400FSP1.PTNLIST
-
Current Schema
- - default library
- - For:
- *SYS - *LIBL
- *SQL - User Profile Name
- - to change Current Chema
- - Set Chema = As400fsp1
- - Set Chema = Default (to use default)
|
|
