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
Relatori
Roberto De Pedrini
Fondatore di Faq400
Nicoletta Bernasconi – IBM [email protected]
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 – [email protected]
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:[email protected]@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