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  Create data maps for each schema • Select table  Create sql with prompting and • Right click and GENERATE SQL help  Check database reorganizations • Most common errors  Check DB index rebuilds • -- SQL150B 10 REUSEDLT(*NO)  Check for suggested indexes in table TABLEA in SCHEMA based on usage ignored.  Create indexes • -- SQL1506 30 Key or attribute  Extract and build SQL for (almost) all DB objects in the schema. for TABLE in SCHEMA ignored. 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 object.

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 operation. • 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;