An Introduction to SQL for System i A beginning overview of SQL in - - PowerPoint PPT Presentation

An Introduction to SQL for System i

A beginning overview of SQL in System i Navigator and Embedded SQL in RPGLE

Quote heard from IBM at a Conference

80% of everything you will need to know three years from now, You don’t know today.

OS Release 7.1, 7.2 and Beyond-Power8

• We saw a presentation by Scott Forstie of IBM that showed a lot of

functionality now built into the OS that used to be user written or vendor provided info. That can now be accessed with SQL in managing your system.

• Tables for DataBase cross-reference
• OS status for PTFs
• Group and User profiles
• Security
• Journaling
• System provided procedures for extreme functionality

Today we will review SQL Use of System i Navigator & in RPGLE

• Use of new terminology
• Schema

RCAC Casting

• Table

CUBE Parameter Markers

• Cursors

Cardinality

• Rows and columns

CLOB, BLOB, DBCLOB

• System Name

BIFs – Aggregate, Scalar, Table

• COALESCE

Alias

• Identity Columns

XML Datatype

• Row Change Timestamp

Partitions

• SQL
• DDL vs DDS functions and functionality
• How to use SQL in Free-form RPG, & CL

SQL Term Native IBM Term

Schema Library (Collection) Table Physical File Row Record Column Field Index Keyed Logical File View Non-keyed Logical Log Journal Isolation Level Commitment Control Level Partition File Member

BE AWARE OF NAMING CONVENTIONS *SYS Uses '/' delimiter

• schema/table

Library list applies *SQL Uses '.' delimiter

• schema.table

Library list does NOT apply

• Must use SET SCHEMA and SET PATH or - Qualify Names
• If not specified, assumes schema of profile name!

Which to use? *SYS for “normal” i related development *SQL if concerned about cross platform usage *SQL if copying examples from web etc.

When using SQL, you have the choice of a naming convention. But it is not just a question of a '.' or a '/', it also effects whether or not the library list is taken into consideration. Use whichever you are comfortable with. If you are going to be using a lot of examples from other platforms (or examples from the web), it is probably best to get used to the SQL naming convention. * Many thanks to Paul Tuohy.

Unqualified Names When an SQL object name is unqualified, these registers are used CURRENT SCHEMA CURRENT PATH *SYS Naming CURRENT SCHEMA defaults to *LIBL

• i.e. CURLIB

CURRENT PATH defaults to *LIBL *SQL Naming CURRENT SCHEMA defaults to profile name CURRENT PATH defaults to

• "QSYS", "QSYS2", "SYSPROC", "SYSIBMADM", “user profile”

Used in a DDL statement CURRENT SCHEMA

• e.g. CREATE PROCECURE MYPROC

SET SCHEMA is a good thing

Unqualified Names (continued) When Referencing unqualified type, variable, function, procedure, and specific names - . Uses CURRENT PATH When Referencing unqualified tables, views . Uses CURRENT SCHEMA NULLS SQL allows for values to be null .i.e. the value is “unknown” Not something we are used to on i .Default in DDS (traditional) is that columns are not null capable .Default in DDL (SQL) is that columns are null capable Null values can present "interesting" results .If there are null capable columns on a table .As a result of outer joins * More thanks to Paul Tuohy.

Data types

The smallest unit of data that can be manipulated in SQL is called a value. How values are interpreted depends on the attributes of their source, which includes the data type, length, precision, scale, and CCSID. The sources of values are:

. Columns . Constants . Expressions . Functions . Special registers . Variables (such as host variables, SQL variables, global variables, parameter markers and parameters of routines)

The DB2 relational database products support both built-in data types and user-defined data types. This section describes the built-in data types. For a description of distinct types, see “User-defined types” on page 91 of DB2 for i SQL Reference 7.2

System i Navigator Create SQL for existing Table

• Open schema
• Select table
• Right click and GENERATE SQL
• Most common errors
• -- SQL150B 10 REUSEDLT(*NO)

in table TABLEA in SCHEMA ignored.

• -- SQL1506 30 Key or attribute

for TABLE in SCHEMA ignored.

• Create data maps for each

schema

• Create sql with prompting and

help

• Check database reorganizations
• Check DB index rebuilds
• Check for suggested indexes

based on usage

• Create indexes
• Extract and build SQL for (almost)

all DB objects in the schema. Reverse Engineering.

Create a SCHEMA Map

• You need to be connected to the appropriate system
• Open My Connections -> System -> DATABASES -> DB Name (may be

system serial number or name)

• Right click on DATABASE Navigator Maps.
• Choose NEW -> Map

Generating SQL with System i Navigator

Open up SYSTEM to DATABASES – Select “Run an SQL Script” at the bottom of the page

The generated CREATE statements might create an SQL object with a different record format level identifier than the original DDS file, even when column attributes such as data type and length exactly match. Such a change means you'll need to tackle the arduous task of recompiling and testing all program objects that reference the database

• bject.

If you want to avoid affecting your existing applications with your move to SQL, you can use IBM's surrogate logical file approach (see "Replacing a DDS Physical File with an SQL Table"). Many customers have seamlessly transitioned to an SQL-defined database with this approach. In addition, third-party tools are available that automate surrogate file creation and the conversion from DDS to

• SQL. http://iprodeveloper.com/database/replacing-dds-physical-file-sql-table

Create a "Surrogate" LF for Original PF The process involves four main steps:

• 1. Replace a DDS-created physical file with an SQL-created table.
• 2. Create SQL indexes to replace existing keyed access paths that are implicitly

created for DDS-created files.

• 3. Create a DDS logical file as a "surrogate" for the physical file replaced in step 1.
• 4. Modify existing logical file DDS to reference the SQL table created in step 1.

Why Embed SQL in Programs?

• Consolidate several programs into one.
• Performance improvements in data retrieval in sets vs reading records in loop
• Very Flexible - Can perform dynamic selections, sum data and sort all in one
• peration.
• Can replace most (but not all) of I/O operations such as

Chain, Read, Write, Update.

What SQL can you put in a program?

• Almost all SQL statements
• Declare Cursor, Open, Close, Fetch
• Create or Replace Table, Select, Insert, Update, Delete
• Commit, Rollback,
• Can include user selections in SQL parameters

When Embedding SQL in RPG Programs

• Syntax and rules to follow for embedded SQL
• Compiling programs is a little different process
• Use and manage SQL Cursors
• Error and Condition Handling / Handlers
• Special Data structure provided for feedback
• Additional information in debugging process
• Performance data in logs
• Ability to use both static and dynamic SQL
• Don’t need DCL-F statement for files

SQL must be coded in the CALCS portion of the program

• SQL is not case sensitive
• No F specs for tables
• Retrieves columns and places them into program variables (lists).
• One-to-one correspondence between SELECT and INTO lists
• SELECT INTO expects only ONE row.
• Multiple rows require use of CURSOR operations and / or Arrays.
• YES - SQL now supports arrays in 7.2

Exec SQL Select Name, Addr, State, ZIP into :Name, :Addr, :St, :ZIP from Employee Where emp# = :Empno;

CRTSQLRPGI OBJ(MYLIB/MYPGM) SRCFILE(UTILITY/QSQLRPGLE) RPGPPOPT(*LVL2) D* SQL COMMUNICATION AREA D SQLCA DS D SQLCODE 10I 0 D SQLSTATE 5A D SQLSTT 5A OVERLAY(SQLSTATE)

C Z-ADD -4 SQLER6 C CALL SQLROUTE C PARM SQLCA C PARM SQL_00018 C SQL_00021 IFEQ '1' C EVAL Field1 = SQL_00023 C EVAL Field2 = SQL_00024 C EVAL Field3 = SQL_00025 C EVAL Field4 = SQL_00026 C EVAL Field5 = SQL_00027 C EVAL Field6 = SQL_00028 C END

//* Exec SQL Close C1; /END-FREE C Z-ADD 5 SQLER6 C SQL_00031 IFEQ 0 C CALL SQLROUTE C PARM SQLCA C PARM SQL_00029 C ELSE C CALL SQLCLSE C PARM SQLCA C PARM SQL_00029 C END /FREE

Program example in SQL RPGLE

// SQL statement to select records EXEC SQL DECLARE C1 CURSOR FOR Tablein; exec sql Open C1; exec sql Insert into Tableout (SELECT * FROM Schema.tablein ORDER BY Column1 ASC, Column4 ASC); // close cursor Exec SQL Close C1; *inlr = '1'; return;

SQL Host Variables

• Host variables are program fields used in SQL statements
• Must be preceded by a colon as in :host-variable-name
• They must be defined somewhere in the program
• Typically in D specs (as external DS) or display files
• Cannot begin with SQ, SQL, RDI, DSN
• Maximum length of host variable names is 64 characters

Language elements

Characters , Tokens, Identifiers Data types Constants Special registers Global variables Functions Methods Conservative binding semantics Expressions Row expression Predicates

Insert, Update, Delete (aka Set at a Time operations)

• Set-at-a-time updates do not require cursor operations
• Statements will update all rows meeting the WHERE criteria
• An UPDATE statement with no WHERE clause will update all rows
• Not necessary to retrieve each record into the program
• It is possible to retrieve and process each row individually if required
• Must use Cursors operations to control

EXEC SQL Update Employee SET Salary = Salary + (BOSS_Salary ) Set Security_Clearance = ‘*ALL’ Where position = ‘Programmer’;

• SQL CASTING
• Casting in SQL is used to change one data type to another. It can also be used to change a data type to the same type

but with a different length, precision, or scale. Casting can be done implicitly, by the database manager, or explicitly, by using the cast function in an SQL statement.

Using Structures in SQL

• Host Structures are groups of variables
• Data structures in RPG
• They can be used in SQL Statements
• Replaces the list of individual host variables
• DS subfields must contain the correct number, type, and sequence for SELECTed columns
• dcl-ds ProductDS Extname(Product)
• Dcl-fields
• END-DS;

Using Structures in SQL

• Host Structures are groups of variables
• Data structures in RPG
• They can be used in SQL Statements
• Replaces the list of individual host variables
• DS subfields must contain the correct number, type, and sequence for SELECTed columns
• dcl-ds ProductDS Extname(Product);
• Dcl-fields;
• END-DS;

Using host structure arrays in ILE RPG applications that use SQL A host structure array is defined as an occurrence data structure or a data structure with the keyword DIM coded. Both types of data structures can be used on the SQL FETCH or INSERT statement when processing multiple rows.

The following list of items must be considered when using a data structure with multiple row blocking support.

• All subfields must be valid host variables.
• All subfields must be contiguous. The first FROM position must be 1 and there cannot be
• verlaps in the TO and FROM positions
• No subfields using the POS or OVERLAY keywords can be included in the data structure
• If the date and time format and separator of date and time subfields within the host structure

are not the same as the DATFMT, DATSEP, TIMFMT, and TIMSEP parameters on the CRTSQLRPGI command (or in the SET OPTION statement), then the host structure array is not usable. For all statements, other than the blocked FETCH and blocked INSERT, if an occurrence data structure is used, the current occurrence is used. For the blocked FETCH and blocked INSERT, the occurrence is set to 1.

The following example uses a host structure array called DEPARTMENT and a blocked FETCH statement to retrieve 10 rows from the DEPARTMENT table. DDEPARTMENT DS OCCURS(10) D DEPTNO 01 03A D DEPTNM 04 32A D MGRNO 33 38A D ADMRD 39 41A DIND_ARRAY DS OCCURS(10) D INDS 5I 0 DIM(4) … C/EXEC SQL C+ DECLARE C1 CURSOR FOR C+ SELECT * C+ FROM CORPDATA.DEPARTMENT C/END-EXEC … C/EXEC SQL C+ FETCH C1 FOR 10 ROWS C+ INTO :DEPARTMENT:IND_ARRAY C/END-EXEC

Blocked FETCH and blocked INSERT are the only SQL statements that allow a data structure with the DIM keyword. A host variable reference with a subscript like MyStructure(index).Mysubfield is not supported by SQL. Example Dfststruct DS DIM(10) QUALIFIED D sub1 4B 0 D sub2 9B 0 D sub3 20I 0 D sub4 9B 0 C/EXEC SQL C+ FETCH C1 FOR 10 ROWS INTO :fststruct C/END-EXEC

Coding SQL statements in ILE RPG applications http://www-01.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/rzajp/rzajprpgi.htm Exec SQL DECLARE cursor1 SCROLL CURSOR FOR SELECT PRID, PRNAME, PPRICE, PSTOCK FROM PRODUCTSP; Exec SQL Open cursor1; Exec SQL Fetch cursor1 INTO :PRID, :PRNAME, :PPRICE , :PSTOCK; Dow %subst(sqlstt:1:2)='00' or %subst(sqlstt:1:2)='01'; RRN +=1; Write PRODSFL; Exec SQL Fetch cursor1 INTO :PRID, :PRNAME, :PPRICE , :PSTOCK; EndDo; Exec SQL Close cursor1; ExFmt PRODCTL

HIRE A DATABASE ADMINISTRATOR !!!