Chiama tramite PC: Comunica ���� Connessione audio ���� Chiama tramite computer

oppure

Chiama via telefono: 02 87103980 - ID Evento: 928 036 288#

Connetti il tuo audi o

La trasmissione inizierà a breve

Il meeting verrà registrato e sarà

disponibile dopo

l’evento per il riascolto

Tutte le linee sono silenziose per

garantire un buon

ascolto

Potete sottomettere i vostri commenti

durante l’evento

usando l’apposita funzione “Chat”

Se avete domande, potete sottoporle in

qualsiasi momento

nel box “Q&A” –le risposte verranno

fornite alla fine

Nicoletta Bernasconi

Product Manager - Power Systems IBM i

IBM Italia

nicoletta_bernasconi@it.ibm.com

Relatori

Roberto De Pedrini

Fondatore di Faq400

roberto.depedrini@faq400.com

Nicoletta Bernasconi – IBM Italianicoletta_bernasconi@it.ibm.com

Radio Mr. i - 12 aprile 2019 ore 12:00

SQL, per lo sviluppatore che non deve chiedere mai!

© 2019 IBM Corporation5

Radio Mr. i 2019 – calendario primo semestre

22 febbraio - Strumenti di accesso e gestione dell'ambiente IBM i http://ibm.biz/Radio_Mri1 22 marzo - Monitoraggio del funzionamento di un sistema IBM i http://ibm.biz/Radio_Mri2 12 aprile - SQL, per lo sviluppatore che non deve chiedere mai! http://ibm.biz/Radio_Mri3 17 maggio - Come accedere in modo ottimizazto a Db2 for i tramite PHP http://ibm.biz/Radio_Mri4 14 giugno - Integrazione tra IBM i e Intelligenza artificiale http://ibm.biz/Radio_Mri5

Chi fosse interessato alle trasmissioni della passata edizione, può scaricare il materiale e le registrazioni dal sito http://ibm.com/it/ibmi

© 2019 IBM Corporation6

DB2 for i e SQL: aree strategiche del sistema operativo

www.ibm.com/developerworks/ibmi/techupdates/db2

Roberto De Pedrini – Faq400roberto.depedrini@faq400.com

SQL, per lo sviluppatore che non deve chiedere mai!

Radio Mr. i - 12 aprile 2019 ore 12:00

SQL – Structured Query Language

Cos’è?

Cosa è:

Un linguaggio standard, indipendente dalla piattaforma per la

definizione e manipolazione dei dati di un DB Relazionale.

Nasce nel 1974 nei laboratori IBM e arriva ufficialmente sul «mercato»

nel 1983 proprio con il DB2…

Giochiamo quindi in casa sul nostro IBM i!

SQLLa «classificazione» delle istruzioni SQL

• DDL = Data Definition Language• Create Table, Alter Table, Create Index, Create

View, Drop…

• DML = Data Manipulation Language• Insert into, update, delete from…

• DQL = Data Query Language• Select campo from tabella, join,

• DCL = Data Control Language• Grant, Revoke …

• TCL = Transaction Control Language• Commit, Rollback

SQL/PLUn vero linguaggio

di programmazione

procedurale!

SQLPerché?

Per la perfomance! Per la sicurezza! Per l’integrità!

Per Log e Audit!

SQLInterfacce SQL

STRSQL Interactive SQL

Per eseguire al volo istruzioni

SQL, DDL e DDM.

Utilissimo F4=Prompt

Limitate possibilità di export

dei risultati

Gestione delle sessioni (script)

non comodissima

Alcune nuove funzioni SQL non

funzionano da STRSQL!

SQLInterfacce SQL

SQL Embedded RPG/Cobol

Utilizzare SQL Embedded

dentro le nostre procedure

RPG (o Cobol) ottimizza,

semplifica, eleva all’ennesima

potenza le possibilità di

interrogazione e

manipolazione dei dati.

SQLInterfacce SQL

RUNSQLSTM

Utilizzabile in un CL/RPG per

eseguire uno script anche di più

comandi.

Limitati i comandi gestibili per

poca gestione dell’output

Membro FAQ400/SRC(SQL001)

CL: ADDLIBLE FAQ400;

INSERT INTO T1 VALUES(’A’, 17);

DELETE FROM MYLOG

WHERE DATALOG<current date – 30 days;

SQLInterfacce SQL

RUNSQL

Utilizzabile nei CL, limitato ad un

solo statement SQL

Esempio

PGM PARM(&LIB)

DCL &LIB TYPE(*CHAR) LEN(10)

DCL &SQLSTMT TYPE(*CHAR) LEN(1000)

CHGVAR VAR(&SQLSTMT) + VALUE(’DELETE FROM

qtemp.worktable1 + WHERE table_schema = ’’ || &LIB ||

’’’)

RUNSQL SQL(&SQLSTMT) COMMIT(*NONE)

NAMING(*SQL)

ENDPGM

SQLInterfacce SQL

ACS / Rdi Esegui Script SQL

Vantaggi:

• Export dei risultati

• Salva e ripristina script

• Inserisci da esempio

Svantaggi:

• Manca F4!

SQLInterfacce SQL

Rdi – Prospettiva Sviluppo

Database

Rational Rdi arriva con una

installazione minimale di IBM

Data Studio, la prospettiva

Sviluppo Database

Benvenuto CTRL-Space!

SQLInterfacce SQL

IBM Data Studio (Free!)

Uno potente strumento per la

modellazione del Database:

- Script

- Replica di

modifiche/creazioni tra

differenti

Schema/Partizioni

- CTRL-Space!

SQLInterfacce SQL

Dbeaver Community Edition

Veloce, multi-database,

interfaccia semplice.

E ctrl-space!

SQLLa terminologia SQL

Terminologia SQL Terminologia tradizionale

(IBMi / AS400)

Schema o Collection Library - Libreria

Table Physical File – File

Row Record

Column Field - Campo

View Logical File - Vista Logica

Index Keyed Logical File –

Isolation Level Commitment control level

Log Journal

Catalog (risultato di DSPFD-DSPFFD)

Global Variable (una sorta di Area Dati)

SQLCreazione DDL dai nostri PF (conversion tool)

Generare DDL da un oggetto File Fisico

esistente:

• «Schemi» di ACS!

• «Schemi» dal Database di Navigator

for i Web o System i Navigator

Oppure

CALL QSYS2.GENERATE_SQL

('EMPLOYEE', 'SAMPLEDB', 'TABLE', 'QDDLSRC',

‘SAMPLEDB',

‘EMPLOYEE',CREATE_OR_REPLACE_OPTION =>

'1', CONSTRAINT_OPTION => '2');

SQLDDL vs DDS (Table vs Phisical File)

SQLDDL vs DDS Perché?

• I/O Nativo (Chain/Setll..) sì (*)

• Migliorata la validazione dei dati (all’insert/update) -> Performance (25:1)

• Gestione dei Vincoli-Constraint (Unique, Primary Key, Foreign Key, Table Check)

• Nomi di tabella, indici, campi, constraint… lunghi e significativi

• Audit Columns (generated always columns)

• Index Performance (64K/8K + Shared Access Paths –Evi)

• Campi Blob e nuovi tipi dato

• Viste – Views (Semplificano – non appesantiscono – cambio tipo dato)

• Create or Replace

(*) Attenzione alle colonne «generated always»

SQLRPG I/O Nativo o SQL Embedded?

• I/O Nativo (chain, update, write…)

• OK per accesso al singolo record

• SQL Embedded

• Prestazioni dell DB2 Optimizer (scelta index

automatica o proposta)

• Join tra tabelle, modifica della logica di

reperimento dei dati

SQL

Trucchi – Barbatrucchi

(e tecniche) con SQL

SQLModernizzare senza rompere tutto! (trick 01)

File fisico CLIENTI0F

A UNIQUE

A R CLIENTIR

A CLCODICE 10A

A CLRAGSOC 35A

A CLINDI 35A

A CLLOCA 35A

A CLNAZ 2A

A K CLCODICE

File logico x Nazione CLIENTI0L

A R CLIENTIR PFILE(CLIENTI0F)

A K CLNAZ

Table CLIENTITAB

Create table CLIENTITAB (

ID Integer Generated always as Identity,

CLCODICE char(10) not null

,CLRAGSOC char(35) not null

,CLINDI char(35) not null

,CLLOCA char(35) not null

,CLNAZ char(2) not null

,Audit_change_user VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER)

,Audit_change_jobname VARCHAR(28) GENERATED ALWAYS AS (QSYS2.JOB_NAME)

,Audit_TS_LastUpdate Timestamp Generated always for each row on update

as row change Timestamp

,constraint CLIENTITAB_CODICE_PK UNIQUE(CLCODICE))

Copio i dati dal file fisico

insert into CLIENTITAB (CLCODICE,CLRAFSOC,CLINDI,CLLOCA,CLNAZ)

select CLCODICE,CLRAFSOC,CLINDI,CLLOCA,CLNAZ from CLIENTI

Ricreo CLIENTI0F come Logical File su CLIENTITAB

A R CLIENTIR PFILE(CLIENTITAB)

A CLCODICE 10A

A CLRAGSOC 35A

A CLINDI 35A

A CLLOCA 35A

A CLNAZ 2A

A K CLCODICE

Gli altri logical file di CLIENTI0F vanno “puntati” su CLIENTITAB sempre

con il dettaglio dei campi come il “vecchio fisico”

No Level Check

Exceptions

SQLGenerated Always Columns

SQLDDL e Dizionario (Field Reference File) – Trick 02

CREATE TABLE clientitab AS

(SELECT id as id_cliente

, RagioneSociale

, Indirizzo

, cast(‘’ as char(35)) as Nome_dello_zio

FROM Dizionario) WITH NO DATA

SQLMQT Materialized Query Table – Trick 03

create table sampledb.mqt_employee as

(select workdept, sum(salary) as DeptSalary

from sampledb.employee

group by workdept)

DATA INITIALLY DEFERRED -- (inizialmente vuota/popolata)

REFRESH DEFERRED -- (non aggiornata automaticamente)

MAINTAINED BY USER;

select * from sampledb.mqt_employee

order by workdept;

refresh table sampledb.mqt_employee;

SQLSystem Catalog Trick 04

select system_table_schema, system_table_name, table_type

from QSYS2.SYSTABLES

where system_table_schema='SAMPLEDB';

select system_table_schema, system_table_name, system_index_name

from QSYS2.SYSINDEXES

where system_table_schema='SAMPLEDB’;

Select table_name, system_table_name, column_name, system_column_name

from QSYS2.SYSCOLUMNS

Where table_schema=‘SAMPLEDB’ and table_name=‘EMPLOYEE’;

SQLUDF SQL o RPG – Trick 05

create or replace function faq400.data8(

data8 dec(8,0))

returns char(10)

language sql

begin

return char(date(timestamp_format(char(data8),'YYYYMMDD')), iso);

end;

select cliente, NrFattura, data8(Datafat) as DataFat from XSTATIS0F

where data8(Datafat) > current date – 12 months

SQLOLAP Function – Quante possibilità – Trick 06

SELECT empno, firstnme, lastname, salary,

RANK() OVER(ORDER BY SALARY DESC) as ClassRank,

DENSE_RANK() OVER(ORDER BY SALARY DESC) as ClassDenseRank,

ROW_NUMBER() OVER(ORDER BY SALARY DESC) as ClassRowNumber

FROM SAMPLEDB.EMPLOYEE

FETCH FIRST 10 ROWS ONLY;

SQLDerived Index – Performance – Trick 07

CREATE INDEX upper_lastname_ix ON table1( UPPER(lastname) )

CREATE INDEX shipdate_year_ix ON table1( YEAR(shipdate) )

SQLOrder by [Column Number] – Trick 08

SELECT EMPNO, SALARY+BONUS as TotalSalary

FROM sampledb/employee

Order by 2 desc;

CREATE VARIABLE FAQ400.campo CHAR(10) DEFAULT 'SALARY‘

SELECT EMPNO,

CASE WHEN FAQ400.campo=‘SALARY’ then SALARY else BONUS end as Campo

FROM sampledb/employee

Order by 2 desc;

SQLOrder by [forzatura] – Trick 09

SELECT Nazione, sum(Fatturato)

from STATISTICO

Where DataFattura between current date – 1 month and current date

Order by

case when Nazione=‘IT’ THEN 0 else 1 end, Nazione;

Esempio 2:

SELECT Nazione, sum(Fatturato)

from STATISTICO

Where DataFattura between current date – 1 month and current date

Order by locate (Nazione, ‘ITDEFR’);

SQLDivision By Zero– Trick 10

With DatiGiornalieri as

(SELECT 0 as RigheOrdini, 1 as RigheDDT

from sysibm.sysdummy1)

SELECT DEC(DOUBLE(RigheOrdini) / DOUBLE(RIgheDDT), 13, 6) AS Rapporto

From DatiGiornalieri;

SQLSQL e file Multimembro– Trick 11

CREATE ALIAS STAT/STATISTICHE2017

FOR STAT/STATISTICHE(2017);

SELECT * FROM STAT/STATISTICHE2017

SQLMerge (Upsert)– Trick 12

merge into listino0f as tgt

using (select * from ListNew0f) as src

on tgt.codice=src.codice

when matched then

update set tgt.prezzo=src.prezzo

when not matched then

insert values (src.codice, src.prezzo);

SQLMa il NULL è uguale al NULL ? – Trick 13

Select * from AnagraficaClienti

where datacreazione IS NOT DISTINCT FROM dataaggiornamento;

SQLRimuovere blanks duplicati – Trick 14

select codice, descrizione,

replace(

replace(

replace(descrizione,' ','<>')

,'><', '')

,'<>', ' ') as DescPulita from qtemp/esempio;

SQLL’ultimo ID – Trick 15

select id into :lastID from final table (

insert into clientitab values (

default,

:codice,

:ragionesociale,

default,

default,

default )

);

SQL e IBM iVuoi saperne di più ?

Evento ONL-i DAYS

Milano 10/11 Giugno 2019

http://www.faq400events.com

SQL e IBM iVuoi saperne di più ?

Nuovo Blog Faq400

http://blog.faq400.com

IBM Client Center Milano, 11 LUGLIO 2017

I nostri contatti:nicoletta_bernasconi@it.ibm.comRoberto.Depedrini@faq400.com

I prossimi appuntamenti di Radio Mr.i

17 maggio - Come accedere in modo ottimizazto a Db2 for i tramite PHP http://ibm.biz/Radio_Mri4 14 giugno - Integrazione tra IBM i e Intelligenza artificiale http://ibm.biz/Radio_Mri5

IBM Client Center Milano, 11 LUGLIO 2017