Sql by Antonio Antonio

Indice delle schede 1.1 – LIMITI MASSIMI DEL DATABASE/SQL....................................................................2 2.1 – ESECUZIONE DI ISTRUZIONI SQL ..........................................................................3 2.2 – DEBUG / MONITOR ...................................................................................................4 3.1 – DATABASE SQL SU ISERIES ..................................................................................7 3.2 – TIPI DI COLONNE....................................................................................................10 3.3 – ATTRIBUTI VARI .....................................................................................................12 3.4 – CREAZIONE TABELLE REFERENZIATE...............................................................13 3.5 – TIPI DEFINITI DALL’UTENTE .................................................................................14 4.1 – INTEGRITÀ REFERENZIALE ..................................................................................15 4.2 – INTEGRITÀ REFERENZIALE – WEB/PHP .............................................................17 5.1 – FUNZIONI SCALARI ................................................................................................20 5.2 – FUNZIONE SUM E UNION.......................................................................................21 5.3 – UDF (USER DEFINED FUNCTION) .........................................................................22 5.4 – UDF (USER DEFINED FUNCTION) – SQL LANGUAGE ........................................23 5.5 – UDF / API (RETRIEVE USER PROFILE) – SQL LANGUAGE ................................25 5.6 – UDF TABLE .............................................................................................................26 6.1 – VISTE .......................................................................................................................27 R – COSA C’È E COSA MANCA .....................................................................................28 E – MONITOR ERRORI SQL ............................................................................................30 7.1 – SUBQUERY .............................................................................................................33 7.2 – JOIN .........................................................................................................................38 8.1 – SELECT . . . INTO . . . ..............................................................................................43 8.2 – FETCH SCROLL E FOR :N ROWS .........................................................................44 8.3 – INNER/OUTER SELECT (ESTRAZIONE DI RIGHE A PAGINE).............................45 8.4 – SELECT CONDIZIONATA .......................................................................................46 8.5 – COMBINATI(LF) E UNION(SQL) .............................................................................47 9.1 – INSERT.....................................................................................................................54 9.2 – UPDATE ...................................................................................................................55 9.3 – UPDATE STATICO ..................................................................................................56 9.4 – UPDATE DINAMICO ................................................................................................57 9.5 – DELETE - RIMOZIONE DUPLICATI ........................................................................59 9.5 – FETCH E INSERT MULTIPLE .................................................................................60 10.1 – DRDA - ACCESSO A DATABASE REMOTO .......................................................61 10.2 – DRDA2 – TWO PHASE COMMIT ..........................................................................63 10.3 – SQL DINAMICO .....................................................................................................66 10.4 – SQL DINAMICO - APPLICAZIONI .........................................................................71 10.5 – TRIGGERS .............................................................................................................74 10.6 – VARIE FUNZIONALITÀ RECENTI.........................................................................75 10.7 – CIFRATURA DATI..................................................................................................76 10.8 – NUOVE FUNZIONALITÀ A V5R3 ..........................................................................77 10.9 – NUOVE FUNZIONALITÀ A V5R4 ..........................................................................78

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 1

1.1 – Limiti massimi del Database/SQL Per verificare I limiti del Structured Query Language (SQL), consultare Infocenter al link seguente: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmst02.htm#ToC_1490

Questi limiti includono I limiti sui campi numerici, sulle stringhe, sulle date, su database manager….

Per verificare i limiti del database, consultare Infocenter al link seguente: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/dbp/rbafoappmax.htm

Questi limiti includono il numero di bytes in un record, il numero di campi chiave in un file, il numero max. di membri …. Elenco dei limiti principali: Numero di campi in un record Numero di parametri in una funzione Numero di parametri in una procedura Lunghezza record (senza LOBs) Lunghezza record (con LOBs) Ampiezza database Dimensione indice Numero di records Lunghezza totale campi chiave Campi chiave in indice Indici su un file fisico Numero di tabelle in join Numero di tabelle in view Lunghezza campo per insert/update Lunghezza istruzione SQL Numero di elementi in una select Numero di colonne in una GROUP BY Lunghezza totale colonne GROUP BY Numero di colonne in ORDER BY Lunghezza totale colonne in ORDER BY Ampiezza SQLDA Numero di prepared statements Cursori dichiarati a programma Numero di cursori aperti Numero di file in una libreria Numeri di constraints Livello di subselect nidificati Lunghezza commento Numero di records per singola transazione Numero di trigger Numero di invocazioni trigger nidificate Numero di membri in un file fisico

8.000 90 254 32.766 3.758.096.383 1 terabyte 1 terabyte 4.294.967.288 2.000 120 ~ 4.000 256 32 32.766 32.767 ~ 8.000 120 2.000 10.000 10.000 16.777.215 ~ 500.000 ~ 500.000 ~ 500.000 Nessuno 300 32 2.000 ~ 500.000.000 300 200 32.767

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 2

2.1 – Esecuzione di istruzioni SQL XSQL Comando ‘custom’ che utilizza l’Api QSQPRCED Execute SQL (XSQL) Immettere le scelte e premere Invio. SQL statement . . . . . . . . . Log SQL command . . . . . . . . Use local SQL sys values . . . .

__________________________________ .... *YES, *NO, *RMT *YES *YES, *NO *NO_

Qshell Sui sistemi UNIX e LINUX l’interfaccia comandi di default è la SHELL. La default SHELL disponibile su OS/400 si chiama Qshell (Qsh). L’interfaccia STRQSH consente di operare con programmi e files ed è possibile automatizzare delle attivitò ripetitive con gli “shell scripts”. Qsh usa standard files per input (stdin), output (stdout) e errori (stderr).

Esecuzione di istruzioni SQL con QSHELL QSH CMD('db2 "create table cmdsql (cmd char(50))"') QSH CMD('db2 "insert into cmdsql values(''drop table cmdsql'')"')

QSH CMD('db2 "select * from cmdsql"') CMD ---------------------------------------drop table comandi 1 RECORD(S) SELECTED. Creazione di uno script COPY OBJ('/qsys.lib/costagliol.lib/cmdsql.file/cmdsql.mbr') TOOBJ('/qdls/costagli/cmdsql.dat') REPLACE(*YES) Esecuzione di uno script QSH CMD('db2 -f /qdls/costagli/cmdsql.dat')

RUNSQLSTMT Il comando RUNSQLSTM (Esecuzione istruzione SQL) elabora un file origine di istruzioni SQL. http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/cl/runsqlstm.htm

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 3

2.2 – Debug / Monitor Opzioni query reperite nel file QAQQINI nella libreria QUSRSYS. Opzioni principali QAQQINI MESSAGES_DEBUG Specifies whether Query Optimizer debug messages that would normally be issued if the job was in debug are displayed to the joblog. PARALLEL_DEGREE Controls the amount of parallel processing that can be used when running database queries, creating indexes, and maintaining indexes. FORCE_JOIN_ORDER Specifies to the query optimizer that the join of the tables is to occur in the order specified in the query. QUERY_TIME_LIMIT : Specifies the time limit (in seconds) for the query governor to use in determining if the requested query is allowed to run. (Range: 0 - 2147352578) STAR_JOIN : Controls optimizer's usage of a star schema join implementation for join queries. This implementation can only be used when EVIs have been created over the foreign keys of a fact table in a star schema data model. Usage of this parameter does not guarantee the star join implementation will be chosen by the optimizer and it may negatively impact join queries that are associated with normalized relational data models. FORCE_JOIN_ORDER parameter also must be set to *PRIMARY 1 to ensure that the fact table is placed first in the join order - this assumes that the fact table is the first table specified on the join query. UDF_TIME_OUT : Specifies the amount of time, in seconds, that the database will wait for a User Defined Function (UDF) to finish processing. OPTIMIZE_STATISTIC_LIMITATION : Controls how the query optimizer gathers statistics during the optimization process. Calculating statistics from indexes created over large tables can be time-consuming in some cases. This option provides the ability to limit the amount of time spent in statistic gathering tasks. This parameter provides similar capabilities to the QQQOPTIONS data area used prior to V4R4. OPTIMIZATION_GOAL : Specifies the goal that the query optimizer should use when making costing decisions about the requested query. This parameter will be ignored if the query request contains an OPTIMIZE FOR n ROWS clause. REOPTIMIZE_ACCESS_PLAN : For SQL requests with a saved access plan this option controls when the optimizer will reoptimize and possibly rebuild the access plan for an SQL request. Types of SQL that are associated with a saved access plan include an SQL statement embedded in a high-level language program or a dynamic SQL request that is using extended dynamic to store access plans in an SQL package. SQL_SUPPRESS_WARNINGS : Controls the suppression of SQL0030 warnings for SQL statements. This option is only supported on V5R1. OPEN_CURSOR_THRESHOLD : The threshold value to start full close of pseudo-closed cursors associated with Reusable ODPs within a job. OPEN_CURSOR_CLOSE_COUNT : The number of cursors that DB2 will full close within a job when threshold (OPEN_CURSOR_THRESHOLD) is exceeded. APPLY_REMOTE : Specifies for database queries involving distributed files (DB2 Multisystem), whether or not the CHGQRYA query attributes are applied to the jobs on the remote systems associated with this job. ASYNC_JOB_USAGE : Controls whether asynchronous jobs can be used to help improve the performance of database queries. This attribute only applies to queries that involve distributed tables created with the DB2 Multisystem licensed feature.

Debug SQL (DEBUGSQL) Immettere le scelte e premere Invio. Messaggi debug . . . . . . . . . Lavori paralleli . . . . . . . . Ordine join . . . . . . . . . .

____ *DEFAULT *DEFAULT

*YES, *NO *DEFAULT, *MAX, *OPTIMIZE.. *DEFAULT, *NO, *YES

pgm (&debug &parallel &frcjoin) dcl &debug *char 4 dcl &parallel *char 9 dcl &frcjoin *char 8 chgqrya qryoptlib(LIBTEST) xsql ('update LibTEST/qaqqini set qqval = "' !! &debug !< + '" where qqparm = "MESSAGES_DEBUG"') xsql ('update LibTEST/qaqqini set qqval = "' !! &parallel !< + '" where qqparm = "PARALLEL_DEGREE"') xsql ('update LibTEST/qaqqini set qqval = "' !! &frcjoin !< + '" where qqparm = "FORCE_JOIN_ORDER"')

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 4

http://www.as400.ibm.com/developer/bi/documents/dbmonitor.pdf

redp0502.pdf International Technical Support Organization Using AS/400 Database Monitor and Visual Explain To Identify and Tune SQL Queries January 2001 rzajqmst.pdf iSeries

DB2 Universal Database for iSeries Database Performance and Query Optimization http://www-919.ibm.com/developer/bi/tuner/index.html

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 5

Comando PRTSQLINF OBJTYPE(*PGM) DECLARE DPOSIZIONI CURSOR FOR SELECT GDAHCD , GDAECD , GDAMCD , GDAOTX , GDAFTX , GDXFVM , GDLSFL , EDJHFG , EDAHCD FROM CFPLC00F JOIN IVRTS00F ON GDAMCD = J7AMCD JOIN TBRFO00F ON J7QFCE = EDQFCE WHERE GDBJQT <> 0 AND GDLSFL <> ' ' ORDER BY GDAHCD , GDAECD , EDJHFG , EDAHCD SQL4021 SQL4020 SQL402D SQL4027 SQL4002 SQL4007 SQL4006 SQL4010 SQL4007 SQL4006 SQL4008 SQL4014 SQL4015 SQL4011 SQL4007 SQL4006 SQL4009 SQL4014 SQL4015

Ultimo salvataggio del piano di accesso salvato su 01/10/02 a 12:13:27. Il tempo stimato per l'esecuzione della query è di 2 secondi. Attributi query sostituiti dal file opzioni query QAQQINI nella libreria E_ASPGC. Lo schema di accesso e' stato salvato con DB2 UDB Symmetric Multiprocessing installato sul sistema. Utilizzato ordinamento ODP riutilizzabile. Implementazione della query per la posizione di unione 1 della tabella 2. Tutti gli indici considerati per la tabella 2. Accesso scansione tabella per la tabella 2. Implementazione della query per la posizione di unione 2 della tabella 3. Tutti gli indici considerati per la tabella 3. Indice TBRFO04L utilizzato per la tabella 3. 1 coppie di colonne di unione vengono utilizzate per questa posizione di unione. Dalla colonna 2.J7QFCE, alla colonna 3.EDQFCE, operatore di unione MF, predicato di unione 1. Posizionamento riga chiave scansione indice utilizzato sulla tabella 3. Implementazione della query per la posizione di unione 3 della tabella 1. Tutti gli indici considerati per la tabella 1. Indice creato per la tabella 1. 1 coppie di colonne di unione vengono utilizzate per questa posizione di unione. Dalla colonna 2.J7AMCD, alla colonna 1.GDAMCD, operatore di unione MF, predicato di unione 1.

Comando PRTSQLINF OBJTYPE(*SQLPKG) XSQL=> update Libreria/qaqqini set qqval = '*YES' where qqparm = 'MESSAGES_DEBUG'. STATEMENT NAME: STMTNAME update Libreria/qaqqini set qqval = ? where qqparm = ? SQL4021 SQL4020 SQL402D SQL4027 SQL4017 SQL4008 SQL4011

Ultimo salvataggio del piano di accesso salvato su 01/10/02 a 12:09:41. Il tempo stimato per l'esecuzione della query è di 1 secondi. Attributi query sostituiti dal file opzioni query QAQQINI nella libreria E_ASPGC. Lo schema di accesso e' stato salvato con DB2 UDB Symmetric Multiprocessing installato sul sistema. Variabili host implementate come ODP riutilizzabili. Indice QAQQINI utilizzato per la tabella 1. Posizionamento riga chiave scansione indice utilizzato sulla tabella 1.

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 6

3.1 – Database SQL su iSeries Creazione di una Collection (Schema) Create Collection LibrSQL Vengono creati i seguenti oggetti: - libreria LibrSQL - set di SQL views che puntano al Sys Catalog - un giornale QSqJrn e un ricevitore QSqJrn0001 Creazione di una tabella Viene specificato: - la collection (facoltativo) - le colonne e il loro tipo/dimensione (obbligatorio) - il nome della tabella (obbligatorio) - la primary key (facoltativo) - le foreign keys (facoltativo) - le check constraints (facoltativo) Il file creato viene contrassegnato come tabella SQL. Ha al massimo 1 membro (maxmbrs=1) Non ha dimensione massima (size=*nomax) I records cancellati vengono riutilizzati (reusedlt=*yes) E’ giornalizzato con images=*both (se creato in una collection) Il nome del formato record è uguale al nome del file SQL/400 Naming Options 2 alternative: - SQL naming option (collection.nometabella) - System naming option (libreria/nomefile) Definizione delle colonne Vedere la tabella dei tipi. Tipi speciali: - Carattere e binary a lunghezza fissa o variabile, large objects, ecc o Photo VarChar( 20000 ) For Bit Data ... o EmploymentApplication Clob( 1000000 ) ... - datalink (URL->IFS) Keywords opzionali: - Not Null - Default (default-value)

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 7

Convivenza nomi SQL e Tradizionali Create Table MovimentiBancari ... Rename Table MovimentiBancari To System Name MOBAN00F Create Table MOBAN00F ... Rename Table MOBAN00F To MovimentiBancari For System Name MOBAN00F Create Table MOBAN00F ( CdBanca DataMovimento ImportoMovimento

Dec( 7, 0) Not Null, For DtMovim Date Not Null, For ImpMovim Dec(15, 2) With Default Null )

Text e ColHdg Label On Table MOBAN00F Is 'Movimenti Bancari' Label On MOBAN00F ( CdBanca DataMovimento ImportoMovimento

Text Is 'Codice Banca’, Text Is ‘Data del movimento’, Text Is ‘Importo del movimento’ )

Label On MOBAN00F ( CdBanca DataMovimento ImportoMovimento

Is ‘Codice Is ‘Data Is 'Importo

Banca, Movimemto’ Movimento’

Comment On Table MovimentiBancari Is 'Questa tabella contiene . . . ‘ (fino a 2000) Comment On MovimentiBancari ( CdBanca Is ‘Codice banca . . . ‘ DataMovimento Is ‘Data Movimento . . .’ ImportoMovimento Is 'Importo Movimento in EURO . . .’ UDT (User-Defined Types) Create Distinct Type Euro As Dec( 15, 2 ) With Comparisons Aggiunta, rimozione, modifica di colonne Alter Table MOBAN00F Add PercentualeCommissioni for PctCommiss Dec(5,3) Not Null Default 0 Alter Table MOBAN00F Drop PercentualeCommissioni Alter Table MOBAN00F Alter PercentualeCommissioni Set Default 0.01 Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 8

Aggiunta colonna con lunghezza variabile Alter table MOBAN00F Add column COMMENTI VarChar( 500 ) Allocate( 50 ) update moban00f set commenti = '123456789' Funzioni %len e %size in RPGLE Fmoban00f ip e Dlunghezza s C eval C** >> lunghezza = 9 C eval C** >> lunghezza = 502

disk 5 0 lunghezza = %len(commenti) lunghezza = %size(commenti)

Creazione Indici Create Index MOBAN01L On MOBAN00F (CdBanca) Create Unique Index MOBAN01L On MOBAN00F (CdBanca) Create Encoded Vector Index MOBAN01E On MOBAN00F (CdBanca) Creazione di Alias Create Alias MOBAN00F For MovimentiBancari Create Alias StoricoMovimenti For MOBAN00F ( Storico ) Cancellazione di Collections, Tables, Types, Indexes, Aliases Drop Drop Drop Drop Drop

Collection collection-name Table table-name Distinct Type type-name Index index-name Alias alias-name

Drop Collection MOBAN00F Restrict Suggerimenti Memorizzare le DDL in file sorgenti: - QDDLSRC generico - QSqlTblSrc — Tabelle e indici - QSqlProc — Procedure e funzioni

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 9

3.2 – Tipi di colonne SQL Column Data Types Column data type

Description

String Char( length )

Fixed-length character string with a length value from 1 to 32766. If length is omitted, it defaults to 1.

Graphic( length )

Fixed-length graphic string with a length value from 1 to 16383. If length is omitted, it defaults to 1.

VarChar( max-length )

Variable-length character string with a maximum length (maxlength ) from 1 to 32740.

VarGraphic( max-length )

Variable-length graphic string with a maximum length from 1 to 16370.

Long VarChar

Variable-length character string with a maximum length determined by the amount of space available in the row.

Long VarGraphic

Variable-length graphic string with a maximum length determined by the amount of space available in the row.

Blob( max-length )

Binary large object string with a maximum length from 1 to 15728640 bytes. If max-length is omitted, it defaults to 1048576 (1 megabyte). The maximum length can be specified as an integer in the allowable range, as an integer from 1 to 15360 followed by K (increments of 1,024 bytes), or by an integer from 1 to 15 followed by M (increments of 1,048,576 bytes). For example, Blob(2M) is equivalent to Blob(2048K) or Blob(2097152).

Clob( max-length )

Character large object string with a maximum length from 1 to 15728640 bytes. If max-length is omitted, it defaults to 1048576 (1 megabyte). The maximum length can be specified in kilobytes (K) or megabytes (M), as described for the Blob data type.

DbClob( max-length )

Double-byte character large object string with a maximum length from 1 to 7864320 bytes. If max-length is omitted, it defaults to 1048576 (1 megabyte). The maximum length can be specified in kilobytes (K) or megabytes (M), as described for the Blob data type; however, the maximum length is 7680K or 7M.

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 10

SQL Column Data Types Column data type

Description

Numeric Decimal( precision, scale )

Packed-decimal number. The precision value specifies the number of digits and can range from 1 to 31. The scale value specifies the number of digits to the right of the decimal point and can range from 0 to the value specified for precision . You can use Dec(p ) for Dec(p , 0). You can also use Dec by itself for Dec(5, 0); however, always using an explicit precision with Dec provides clearer documentation.

Numeric( precision, scale )

Zoned-decimal number. The precision value specifies the number of digits and can range from 1 to 31. The scale value specifies the number of digits to the right of the decimal point and can range from 0 to the value specified for precision . You can use Numeric(p ) for Numeric(p , 0). You can also use Numeric by itself for Numeric(5, 0); however, always using an explicit precision with Numeric provides clearer documentation.

SmallInt

2-byte, binary integer (5 digits) 32767

Integer

4-byte, binary integer (10 digits) 2.147.483.648

BigInt

8-byte, binary integer (19 digits) 9.223.372.036.854.775.808

Real

Single-precision floating-point number

Double

Double-precision floating-point number

Float( precision )

Floating-point number; precision specifies the number of digits and can range from 1 to 53. The values 1 through 24 specify single-precision, and the values 25 through 53 specify doubleprecision. You can use Float by itself for a double-precision number.

Column data type

Description

Date, Time, and Timestamp Date

Date

Time

Timestamp

Column data type

Description

DataLink DataLink( length )

A datalink that references a nondatabase file. The value length must be from 1 to 32718; if omitted, it defaults to 200. A datalink value is an internal representation of a Universal Resource Locator (URL) and an optional comment.

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 11

3.3 – Attributi vari Nomi riservati create table NomiRiservati (from dec(2), select char(1), insert char(3), distinct char(1)) select distinct, select, from from NomiRiservati Token , was not valid. Valid tokens: ( + * - ? : DAY RRN CASE CAST select (distinct), select, from from NomiRiservati SELECT statement run complete. DISTINCT SELECT FROM c a 0 Identity columns (da V5R2) . . . creazione Tabella

create table ordini (numord dec(7)GENERATED ALWAYS AS IDENTITY, tipo char(1), importo dec(15, 2)) . . . Inserimento ordine

insert into ordini (tipo, importo) values('P', 150) insert into ordini (numord, tipo, importo) values(DFAULT, 'P', 150) . . . Reperimento ultimo numero immesso

Select VALUES IDENTITY_VAL_LOCAL() INTO :ULTIMOORDINE . . . Rinumerazione

Alter table ordini Alter Column Numord RESTART . . . Elimina auto-numerazione

Alter table ordini Alter Column Numord DROP IDENTITY

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 12

3.4 – Creazione tabelle referenziate Creazione di una tabella con campi referenziati al dizionario aziendale d dizionario d xsql

e ds s

200

c**--------------------------------------------------------------c** crea una tabella per la stampa di riepilogo c**--------------------------------------------------------------c eval xsql = 'drop table QTEMP/MOVIMENTI' c/exec sql execute immediate :xsql c/end-exec c eval xsql = 'create table QTEMP/MOVIMENTI + c (Fondo char('+%char(%len(CdFon))+'), + c DesOpera char('+%char(%len(Descriz))+'), + c QtaAcq dec('+%char(%len(Quantita))+', ' + c %char(%decpos(Quantita))+'), + c Titolo char('+%char(%len(Titolo))+'), + c QtaVend dec('+%char(%len(Quantita))+', ' + c %char(%decpos(Quantita))+'))' c/exec sql execute immediate :xsql c/end-exec c if sqlcod < 0 c goto errSQL c endif QTEMP/MOVIMENTI Nome Tipo Lungh Campo Dati Campo Fondo CARATT 2 Desopera CARATT 35 QtaAcq COMPAT 15,3 Titolo CARATT 8 QtaVend Compat 15,3

DIZIONARIO Nome Lungh Campo Campo CdFon 2 Descriz 35 Quantita 15,3 CdTit 8 Quantita 15,3

Creazione di una tabella referenziata da un PF iSeries nativo Create table QTEMP/MOVIMENTI like MOVIM00F Vengono generati solo gli attributi base: TIPO, LUNGHEZZA SI TEXT e COLHDG SI REF, EDTCDE NO CAMPI CHIAVE NO

Creazione di una tabella referenziata da un dizionario aziendale Create table QTEMP/RIEPILOGO As (select CdFon as Fondo ... from dizionario) With (No) Data Creazione A PROGRAMMA di una tabella temporanea C+ DECLARE GLOBAL TEMPORARY TABLE ORDINI C+ (NUMORD INT NOT NULL, C+ DESCR VARCHAR(24), C+ DATAORD DATE) C+ WITH REPLACE C+ ON COMMIT DELETE ROWS Se la tabella già esiste viene cancellata e ricreata con i nuovi attributi Tutti i record vengono cancellati con la COMMIT

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 13

3.5 – Tipi definiti dall’utente Create Distinct Type Euro As Decimal ( 17, 2 ) Create Distinct Type Lire As Decimal ( 17, 0 ) Create Table Ordini (NumOrdine dec(5), ImportoEuro Euro, ImportoLire Lire) Insert into Ordini values(1, 15, 0) Select * from Ordini: NUMORDINE 1

IMPORTOEURO 15,00

IMPORTOLIRE 0

Comparazioni: Select * from Ordini where ImportoLire = 0 Comparison operator = operands not compatible.

Select * from Ordini where ImportoLire = Lire(0) Operazioni: Update Ordini set ImportoLire = ImportoEuro * 1936,27 * use not valid.

Update Ordini set ImportoLire = decimal(ImportoEuro) * 1936,27 1 rows updated in ORDINI

User Function: Create Function Euro2Lire (decimal(17, 2)) Returns decimal (17, 0) Language RPGLE No SQL External Name 'QGPL/EURO2LIRE' Aggiornamento via UDF: Update Ordini set ImportoLire = Euro2Lire(ImportoEuro)

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 14

4.1 – Integrità referenziale LOG SALV LIBRERIE

Libreria Dt.Ult.S.

Libreria Testo

OGGETTI Oggetto Libreria

/* CREAZIONE TABELLA LIBRERIE ------------------------------------*/

create table LIBRERIE (libreria char(10) testo

char(50)

not null primary key, not null with default )

/* CREAZIONE TABELLA LOG SALVATAGGIO -----------------------*/

create table LOGSAVE (libreria char(10)

dtultsave date

not null primary key constraint svl_exist references LIBRERIE (libreria) on delete restrict on update restrict, not null default )

insert into LOGSAVE (libreria) values('PROVALIB') Operation not allowed by referential constraint SVL_EXIST If this is an INSERT or UPDATE statement, the value is not valid for the foreign key because it does not have a matching value in the parent key. Constraint SVL_EXIST for table LOGSAVE requires that any non-null value of the foreign key have a matching value in the parent key. -- insert a row in the parent file that matches the foreign key default values of the dependent rows. Otherwise, you must drop the referential constraint.

insert into LIBRERIE (libreria) values('PROVALIB') insert into LOGSAVE (libreria) values('PROVALIB') select * from logsave LIBRERIA ---------PROVALIB

DTULTSAVE ---------14/11/01

delete from librerie where libreria = 'PROVALIB' Delete prevented by referential constraint SVL_EXIST

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 15

create table LIBRERIE . . . . . . . . on delete cascade on update restrict delete from librerie where libreria = 'PROVALIB' -- Code 3 -- LIBRERIE is not journaled, or you do not have authority to the journal. Files with a constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal.

Crtjrnrcv, Ctrjrn, Strjrnpf LIBRERIE, LOGSAVE delete from librerie where libreria = 'PROVALIB' 1 rows were deleted from LIBRERIE. 1 rows were affected by referential constraints defined on LIBRERIE

/* CREAZIONE TABELLA OGGETTI -----------------------*/

create table OGGETTI (oggetto char(10) libreria char(10)

not null , not null with default , constraint lib_exist foreign key (Libreria) references LIBRERIE (libreria) on delete restrict on update restrict)

insert into OGGETTI (libreria) values('PROVALIB') Statement contains wrong number of values.

insert into OGGETTI values(‘OGGETTO1’, ‘PROVALIB’) 1 rows inserted in OGGETTI.

insert into OGGETTI values('OGGETTO2', 'PROVALIB2') Operation not allowed by referential constraint LIB_EXIST

/* AGGIUNTA COLONNA CON CONSTRAINT SU TABELLA LOG SALVATAGGIO ------------*/

Alter table LOGSAVE Add column IDNASTRO char (10) Constraint NASTROCK Check (IDNASTRO <> ' ') CHECK constraint NASTROCK cannot be added.

Alter table LOGSAVE Add column IDNASTRO char (10) default ‘*’ Constraint NASTROCK Check (IDNASTRO <> ' ') WRKPFCST FILE(LOGSAVE) Gestione Restrizioni File Fisico Immettere le opzioni e premere Invio. 2=Modifica 4=Rimozione 6=Visualizz.record in attesa di controllo

Opz

Restriz. QSYS_LOGSA SVL_EXIST NASTROCK

File LOGSAVE LOGSAVE LOGSAVE

Libreria COSTAGLIOL COSTAGLIOL COSTAGLIOL

Tipo *PRIKEY *REFCST *CHKCST

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Stato EST/ENB EST/ENB

In attesa di controllo NO NO

Pagina 16

4.2 – Integrità referenziale – WEB/PHP create table CMREP6 ( CodiceCdc for CMVYCD char(3), CodiceUtente for CMIWNB dec(5), Descrizione for CMY6TX char(50)); rename table CMREP6 to SHCDC00F; create alias CentroDiCosto for SHCDC00F; create table AVREDO ( CodiceUtente for AVCLCD dec(5), CodiceUfficio for AVCKCD dec(5), Cognome for AVBGTX char(25), CodiceCdC for AVVYCD char(3), rename table AVREDO to SHUTE00F; create alias Utente for SHUTE00F; ALTER TABLE CENTRODICOSTO ADD CONSTRAINT CDC_PK PRIMARY KEY (CODICECDC) ALTER TABLE UTENTE ADD CONSTRAINT UTENTE_PK PRIMARY KEY (CODICEUTENTE) ALTER TABLE UTENTE ADD CONSTRAINT UTENTE_CDC FOREIGN KEY (CODICECDC) REFERENCES CENTRODICOSTO (CODICECDC) ON DELETE RESTRICT ON UPDATE RESTRICT ALTER TABLE CENTRODICOSTO ADD CONSTRAINT CDC_UTENTE FOREIGN KEY (CODICEUTENTE) REFERENCES UTENTE (CODICEUTENTE) ON DELETE RESTRICT ON UPDATE RESTRICT

PHP if ($modo == "aggiorna") { $query = "update Utente set " . "CodiceUfficio = " . $ufficio . " , " . "Cognome = '" . $cognome . "' , " . "CodiceCdC = '" .$cdc . "' , " . "where CodiceUtente = " . $matricola ; } else { $query = "delete from Utente " . "where CodiceUtente = " . $matricola ; } # ESEGUE SQL $result = @odbc_exec($connection, $query); if (!$result) $esito = "SQL" . $r . "-" . odbc_errormsg($connection);

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 17

Richiesta cancellazione di un utente che è responsabile di un centro di costo. Questa operazione causa una violazione dell’integrità referenziale.

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 18

5.1 – Funzioni Funzioni Colonna AVG COUNT MAX MIN STDDEV * SUM VARIANCE or VAR * VAR (VARIANZA) e STTDEV (DEVIAZIONE STANDARD o SCARTO QUADRATICO MEDIO) In ogni serie di misure di una grandezza, i diversi valori ottenuti risultano sempre distribuiti intorno ad un valore medio, che si calcola facendo la media di tutti i dati ottenuti. La differenza tra ogni misura ed il valore medio e' detta deviazione o scostamento semplice. La media dei quadrati degli scostamenti semplici viene detta varianza. La radice quadrata della varianza e' detta deviazione standard o scarto quadratico medio, essa fornisce un'idea di quanto ampio sia l'intervallo dei valori delle misurazioni fatte, per cui: se la deviazione standard e' piccola, le misure sono tutte ben raggruppate intorno al valore medio ed il grado di precisione delle misurazioni eseguite e' alto; se la deviazione standard e' grande, significa che le misure sono molto disperse e la precisione e' minore.

Funzioni Scalari ATANH CHAR CHAR_LENGTH COALESCE CONCAT COS COSH COT CURDATE CURTIME DATE DAY DAYOFMONTH DAYOFWEEK DAYOFYEAR DAYS DECIMAL DEGREES DIGITS DOUBLE EXP FLOAT

FLOOR HASH LEFT LENGTH LN LNOT LOCATE LOG or LOG10 LOR LTRIM MAX MICROSECOND MIN MINUTE MOD MONTH NODENAME NODENUMBER NOW REAL RRN RTRIM

Appunti di programmazione SQL – 1 – ©GC giugno 2006

SECOND SIN SINH SMALLINT SQRT STRIP SUBSTRING or SUBSTR TAN TANH TIME TIMESTAMP TRANSLATE TRIM UCASE or UPPER VALUE VARCHAR VARGRAPHIC WEEK XOR YEAR

Pagina 19

5.1 – Funzioni scalari Case Select Titolo, Case Divisa When 'ITL' Then Importo / 1936.27 When 'EUR' Then Importo Else Importo / Cambio End as ImportoMovimento From MovimentiBancari Selezione con ordinamento condizionato

select Codice, Nome, Citta, Provincia from Clienti order by case Provincia when 'TO' then 0 else 1 end, Provincia, Citta Il primo campo di selezione è una espressione “case”. Per il clienti della provincia di TO il campo di selezione viene impostato a ZERO, per tutti gli altri viene impostato a UNO. Pertanto i clienti di Torino vengono mostrati prima degli altri, ordinati per Provincia + Città.

Cast Select * From MOVIM00F Where Cast (Cliente As Long) > 900000 ... Select Cast (Cliente || ‘ ‘ || Nome As Char(80)) From MOVIM00F Varie Determinazione delle cifre signifitive in un numero

d Numero d NumCifre

S S

11P 0 5P 0

c If Numero > *Zero c/Exec SQL c+ Set :NumCifre = Floor( Log( :Numero )) + 1 c/End-Exec c EndIf c

seton

X = log10 A -> 10x = A Floor(N) = int(N) + 1 Esempio: log10 (12345) = 4,09149.. -> 104,09149.. = 12345 Floor(4,09149) = 5 Conversione carattere -> hex

c/Exec SQL c+ Set :HexFld = Hex( :CharFld ) c/End-Exec Determinazione della settimana dell’anno

select WEEK_ISO(DataSpedizione) from ....

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 20

5.2 – Funzione SUM e UNION create table primanota (numeroreg dec(5, 0), importo dec(10, 2), dareavere char(1)) NUMEROREG 1 1 1 1 2 2 2 2

IMPORTO 1.500,00 1.000,00 2.000,00 4.500,00

DAREAVERE D D D A

1.000,00 1.000,00 2.000,00 4.100,00

D D D A

select numeroreg, sum(importo) as quadratura from primanota group by numeroreg NUMEROREG 1 2

QUADRATURA 9.000,00 8.100,00

select numeroreg, sum(case when dareavere = 'D' then importo else -importo end) as quadratura from primanota group by numeroreg NUMEROREG 1 2

QUADRATURA 0,00 100,00-

select * from primanota where numeroreg in ( select numeroreg from primanota group by numeroreg having sum(case when dareavere = 'D' then importo else -importo end) <> 0) NUMEROREG 2 2 2 2

IMPORTO 1.000,00 1.000,00 2.000,00 4.100,00

DAREAVERE D D D A

select ' ', numeroreg, importo, dareavere from primanota union select 'Totale', numeroreg, sum(importo), dareavere from primanota group by numeroreg, dareavere order by numeroreg, dareavere IMPORTO 4.500,00 4.500,00

DAREAVERE A A

Totale

1 1 1 1

1.000,00 1.500,00 2.000,00 4.500,00

D D D D

Totale

2 2

4.100,00 4.100,00

A A

Totale

2 2 2

1.000,00 2.000,00 4.000,00

D D D

Totale

NUMEROREG 1 1

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 21

5.3 – UDF (User Defined Function) create table ordini (numord dec(7) default null, tipo char(1), importo dec(15, 2)) default 0 insert into ordini (tipo, importo) values('P', 150) insert into ordini (numord, tipo, importo) values(null, 'P', 150) . . . crtdtaara QGPL/&DtaAra *dec 7 CLP pgm (&TipoOrd &NumOrd) dcl &TipoOrd *char 1 dcl &NumOrd *dec 7 dcl &DtaAra *char 10 if (&TipoOrd = 'P') chgvar &DtaAra NUMORDP else chgvar &DtaAra NUMORD rtvdtaara QGPL/&DtaAra &Numord monmsg cpf0000 exec(do) chgvar &Numord 0 return enddo chgvar &Numord (&NumOrd + 1) chgdtaara QGPL/&DtaAra &Numord return endpgm Funzione SQL Create Function GETNORD(char (1)) Returns Dec(7) Language CL No SQL External Name 'QGPL/GETNORD' PDM (F16) Visualizzazione di una opzione definita dall'utente Opzione . . . : Comando . . . :

RU RUNSQLSTM SRCFILE(&L/&F) SRCMBR(&N) COMMIT(*NONE)

Aggiornamento numero ordine update ordini set numord = getnord(tipo) where value(numord, 0) = 0 NUMORD 1 2 93 94 3

TIPO P P D D P

IMPORTO 150,00 200,00 150,00 700,00 100,00

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 22

5.4 – UDF (User Defined Function) – SQL Language Funzione SQL Create Function GETNORD (TipoOrd CHAR(1)) Returns DEC(7) Language SQL Modifies sql data Set option DBGVIEW = *LIST Begin -- Parametri api QCMDEXC Declare Cmd Char(30); Declare CmdL Dec(15, 5); -- Parametri api QWCRDTAA (Retrieve Data Area) Declare Rcvr Char(40); Declare RcvrL Integer; Declare Dtaara Char(20); Declare Start Integer; Declare LenDta Integer; Declare ErrCde Char(20); -- Numero Ordine, Pack/Hex/Char/Return Declare PkdOrd Char(4); Declare HexOrd Char(8); Declare ChrOrd Char(7); Declare NumOrd Dec(7); Declare EXIT HANDLER for SQLEXCEPTION RETURN 0; -- Reperisce il numero d'ordine dalla data area Set Rcvr = ' '; Set RcvrL = 40; If TipoOrd = 'P' Then Set Dtaara = 'NUMORDP *LIBL'; else Set Dtaara = 'NUMORD *LIBL'; End If; Set Start = 1; Set LenDta = 4; Set ErrCde = x'0000000F'; call RtvDtaAra (Rcvr, RcvrL, Dtaara, Start, LenDta, ErrCde); -- Incrementa l'ordine e aggiorna l'area dati If Substr(Errcde, 9, 7) = ' ' Then Set PkdOrd = Substr(Rcvr, 37, 4); -- packed Set HexOrd = Hex(PkdOrd); -- converte in Hex Set NextOrd = Left(HexOrd, 7); -- converte in Char Set NumOrd = Cast(NextOrd as Dec(7)); -- converte in Numero Set NumOrd = NumOrd + 1; -- incrementa Set NextOrd = Char(NumOrd); -- converte in Char Set Cmd = 'CHGDTAARA ' Concat Left(Dtaara, 10) Concat Nextord; Set CmdL = 30; call QSYS.QCMDEXC (Cmd, CmdL); Return NumOrd; Else Return 0; End If; End;

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 23

Creazione SP per API QWCRDTAA CREATE PROCEDURE RtvDtaAra (INOUT RCVRVAR CHAR(40), INOUT RCVRVARLEN INT, INOUT DTAARA CHAR(20), INOUT START INT, INOUT LENDTA int, INOUT ERRORCODE CHAR(20)) LANGUAGE PLI EXTERNAL NAME QWCRDTAA NOT DETERMINISTIC NO SQL PARAMETER STYLE GENERAL

Aggiornamento numero ordine update ordini set numord = GETNORD (tipo) where value(numord, 0) = 0

Inserimento nuovo ordine con attribuzione automatica del numero insert into ordini values(getnord('P'), 'P', 150) -> GETNORD in *LIBL di tipo *N non trovato.

insert into ordini values(GETNORD (char('P')), 'P', 150) 1 righe inserite in ORDINI in COSTAGLIOL.

Funzione SQL Create Function GETNORD ( TipoOrd VARCHAR(1)) Returns dec(7) . . . . . . External Name 'QGPL/GETNORD' SQL0451 - Gli attributi del parametro 1 non sono validi per la procedura o per la funzione GETNORD in *N. -- Per CL: INTEGER, SMALLINT, BIGINT, NUMERIC, VARCHAR, FLOAT, GRAPHIC, VARGRAPHIC, CLOB, BLOB e DBCLOB non sono tipi di dati validi.

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 24

5.5 – UDF / API (Retrieve User Profile) – SQL Language Select ‘QPGMR’, GETUSRPRF(‘QPGMR’) from SYSIBM.SYSDUMMY1 -> “QPGMR Programmatore e utente batch” drop function QGPL/GETUSRPRF; Create Function QGPL/GETUSRPRF ( UsrPrf VARCHAR(10)) Returns char(50) Language Sql Modifies Sql Data Begin -- Parametro di ritorno Declare NomeUtente char(50); -- Parametri api QUSROBJD Declare Rcvr Char(150); Declare RcvrL Integer; Declare FmtName Char(8); Declare ObjName Char(20); Declare ObjType Char(10); Declare ErrCde Char(20); Declare EXIT HANDLER for SQLEXCEPTION RETURN ' '; -- Reperisce il nome utente Set Rcvr = ' '; Set RcvrL = 150; Set FmtName = 'OBJD0200'; Set ObjName = UsrPrf concat 'QSYS'; Set ObjType = '*USRPRF'; Set ErrCde = x'0000000F'; call RtvObjD (Rcvr, RcvrL, FmtName, ObjName, ObjType, ErrCde); -- Incrementa l'ordine e aggiorna l'area dati If Substr(Errcde, 9, 7) = ' ' Then Set NomeUtente = substr(Rcvr, 101, 50); else Set NomeUtente = Substr(Errcde, 9, 7); End If; Return NomeUtente; End; ------------

CREATE PROCEDURE RTVOBJD (INOUT RCVRVAR CHAR(150), IN RCVRVARLEN INT, IN FORMATNAME CHAR(8), IN OBJNAME CHAR(20), IN OBJTYPE CHAR(10), INOUT ERRORCODE CHAR(20)) LANGUAGE PLI EXTERNAL NAME QUSROBJD NOT DETERMINISTIC NO SQL PARAMETER STYLE GENERAL

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 25

5.6 – UDF Table XSQL 'drop table qtemp/ftpsummary' *no XSQL ('create table qtemp/ftpsummary as (+ SELECT + count( case when filename like "%.zip" then 1 else null end) + as [ZIP da spedire], + sum( case when filename like "%.zip" then datasize + else null end) / 1000000 as [SIZE da spedire], + count( case when filename like "%.zipsav" then 1 else null end) + as [ZIP spediti], + sum( case when filename like "%.zipsav" then datasize + else null end) / 1000000 as [SIZE spediti] + FROM TABLE(QGPL/IFSDIR("/pdf/ZZIP")) AS IfsDir + where filename like "%.zip%") with data') RUNQRY *n qtemp/ftpsummary “ZIP da spedire” 54

“SIZE da spedire” 5.254.210

“ZIP spediti” 752

“SIZE spediti” 154.285.400

CREATE FUNCTION QGPL/IFSDIR (IFSFOLDER VARCHAR(128)) RETURNS TABLE (FileName VARCHAR(128), CreateStamp TIMESTAMP, AccessStamp TIMESTAMP, ModStamp TIMESTAMP, Type CHAR(10), DataSize BIGINT, AllocSize BIGINT, Owner CHAR(10)) EXTERNAL LANGUAGE RPGLE EXTERNAL NAME QGPL/IFSDIR DISALLOW PARALLEL RETURNS NULL ON NULL INPUT PARAMETER STYLE DB2SQL

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 26

6.1 – Viste >>-CREATE VIEW--view-name---------------------------------------> '-(----column-name--+-------------------------------------+-+--)-' | .-COLUMN-. | '-FOR--+--------+--system-column-name-' >--AS--+-----------------------------------+--fullselect--------> | .-CASCADED-. | '-WITH--+----------+--CHECK OPTION-' '-LOCAL----'

CREATE VIEW V1 AS SELECT COL1 FROM T1 WHERE COL1 > 10 CREATE VIEW V2 AS SELECT COL1 FROM V1 WITH CHECK OPTION CREATE VIEW V3 AS SELECT COL1 FROM V2 WHERE COL1 < 100

Istruzione SQL INSERT INTO V1 INSERT INTO V2 INSERT INTO V3

INSERT INTO V3

Descrizione del risultato OK ! perché V1 non ha la clausola CHECK OPTION e VALUES(5) non dipende na nessun’altra vista che abbia la clausola CHECK OPTION. ERRORE ! perchè la riga inserita non soddisfa la VALUES(5) condizione di V1 su cui è costruita la vista V2. ERRORE ! perchè V3 dipende da V2 che ha la clausola VALUES(5) CHECK OPTION e la riga inserita non soddisfa V1 (su cui è costruita V2) OK ! anche se non soddisfa la definizione di V3 (V3 non VALUES(200) ha la clausola CHECK OPTION); soddisfa V2 (che ha la clausola CHECK OPTION che punta a V1).

La differenza dei controlli di validazione eseguiti dal sistema in presenza di CASCADED (default) e LOCAL è schematizzata in questa tabella: V1 V2 V3 V4 V5

è è è è è

definita sulla tabella T1 defined su V1 WITH x CHECK OPTION definita su V2 definita su V3 WITH y CHECK OPTION definita su V4

Vista utilizzata in INSERT/UPDATE V1 V2 V3 V4 V5

x = LOCAL y = LOCAL Nessuno V2 V2 V4 V2 V4 V2

x = CASCADED y = CASCADED Nessuno V2 V1 V2 V1 V4 V3 V2 V1 V4 V3 V2 V1

x = LOCAL y = CASCADED Nessuno V2 V2 V4 V3 V2 V1 V4 V3 V2 V1

Appunti di programmazione SQL – 1 – ©GC giugno 2006

x = CASCADED y = LOCAL Nessuno V2 V1 V2 V1 V4 V2 V1 V4 V2 V1

Pagina 27

R – Cosa c’è e cosa manca * Definizioni SQL non disponibili nelle DDS tradizionali: -- Large object (LOB) -- Datalink -- User-defined types (UDTs) -- Nomi tabelle lunghi (fino a 128) -- Nomi campi lunghi (fino a 30) -- Commenti lunghi per tabelle e colonne (fino a 2000) -- Definizione complessa di viste + Expressioni + Aggregazioni + Selezioni complesse -- Viste con subquery -- Viste con Check option -- Encoded vector indexes (EVIs) * Definizioni tradizionali NON disponibili con SQL -- REF e REFFLD -- Files Multi-membro -- Files combinati -- Indici particolari: + su multimembro - dtambrs(*all) + Select/omit + Chiavi su campi derivati + ABSVAL, DIGIT, UNSIGNED, ZONE per campi chiave + ALTSEQ per campi chiave + FIFO, LIFO per chiavi duplicate * Manipolazione del database con funzioni SQL non disponibili in tradizionale -- update e delete ‘in un colpo solo’ -- espressioni e query complesse + espressioni select + operatore Union + Case e Cast -- Condizioni di select complesse + Subqueries -- User-defined functions (UDFs) -- Accesso a LOBs and DataLinks -- Uso delle funzioni XML Extender (dalla Release 5) -- Result set per ODBC and JDBC -- Access plans “dentro” il programma oggetto (OpnQryF ricostruisce l’access plan per ogni query) -- manipolazioni dinamiche (SQL Prepare e Execute) -- Nessuna ricompilazioni per modifica alle tabelle * Manipolazioni tradizionale con funzioni non disponibili con SQL -- Quando cambia la libreria con si ricostruisce l’access plan -- Veloce accesso per RRN -- Accesso ai combinati -- Accesso ai dati avviene sempre con l’indice scelto Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 28

Perché passare a SQL a. IBM fornirà in futuro più migliorie su SQL che sulle DDS b. SQL fornisce delle funzionalità migliori c. SQL è l’unico mezzo per accedere al database da jdbc (java) ed è essenziale per accedere da applicazioni Microsoft (ODBC) d. Le esperienze SQL possono essere riutilizzate anche su altre piattaforme e. E’ facile trovare programmatori che già conoscono l’SQL f. Parte della logica SQL/400 può essere trasferita su altri sistemi g. In molti casi fornisce accessi migliori fra sistemi distribuiti

Perché rimanere sul tradizionale a. b. c. d.

SQL può richiedere una revisione della logica di elaborazione esistente e SQL può richiedere del tempo per l’apprendimento Ci sono casi in cui le performances sono peggiori SQL non dispone di alcune caratteristiche di DDS/HLL

Rischi SQL da non sottovalutare a. b. c. d.

Disordine applicativo e referenziale Salvataggi e ripristini Sicurezza (ODBC) Performances

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 29

E – Monitor errori SQL Il campo SQLCOD (SQL code) è un integer e contiene il codice di ritorno dell’operazione. SQLCOD SQLCOD SQLCOD SQLCOD SQLCOD

= = > = <

0 e SQLWN0 = blank 100 0 e <> 100 0 e SQLWN0 = 'W' 0

-> -> -> -> ->

OK fine file o dati non trovati OK con warning OK con warning Errore

Il campo SQLSTT (SQL state) è di 5 caratteri con la seguente struttura: 1-2 = Classe 3-5 = Sottoclasse Il significato della classe è il seguente: 00 -- Success 01 -- Warning 02 -- No data 03 - ZZ -- Error

Codifica RPGLE per monitor errori SQL con WHENEVER: c/exec sql whenever not found go to [TAG] c/end-exec c/exec sql whenever sqlerror go to [TAG] c/end-exec c/exec sql whenever sqlwarning continue c/end-exec NOT FOUND SQLCODE = +100 oppure SQLSTATE =

'02000'

SQLERROR SQLCODE < 0 SQLWARNING SQLWN0 = 'W') oppure SQLCODE > 0 e <> +100 oppure SQLSTATE classe 01

Codifica RPGLE per monitor errori SQL con SQLSTT: D D D D

SqlStateOK SqlStateNoRow SqlStateDupKey SqlStateWrnPfx

C C C C

Const( Const( Const( Const(

'00000' ) '02000' ) '23505' ) '01' )

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 30

- Istruzioni dove si intende esclusivamente monitorare il “success” il test che segue è sufficiente: C If SqlStt <> SqlStateOK C ExSr SqlError C EndIf - Nelle istruzioni FETCH dove e gli errori: C Select C When C ExSr C When C Eval C When C ExSr C Eval C Other C ExSr C Eval C EndSl

occorre monitorare il fine file, I warnings

SqlStt = SqlStateOK ProcessRow SqlStt = SqlStateNoRow MoreRows = False %Subst( SqlStt : 1 : 2 ) = SqlStateWrnPfx SqlWarning MoreRows = False SqlError MoreRows = False

- Per istruzioni di INSERT per monitorare le chiavi duplicate: C Select C When SqlStt = SqlStateOK C* Skip C When SqlStt = SqlStateDupKey C ExSr SqlDupKey C When %Subst( SqlStt : 1 : 2 ) = SqlStateWrnPfx C ExSr SqlWarning C Other C ExSr SqlError C EndSl

Monitor errori in SPL Declare EXIT HANDLER for SQLEXCEPTION RETURN 0; Declare CONTINUE HANDLER for SQLEXCEPTION Set Numord = -1; Declare Non_Trovato CONDITION FOR SQLSTATE ’02000’; . . . Declare CONTINUE HANDLER FOR Non_Trovato Set Fine_File = 1; . . . Open c1; Fetch c1 Into . . . ; WHILE Fine_File = 0 DO Fetch c1 Into . . .; END WHILE;

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 31

x – Codifica RPGLE per monitor errori SQL con SQLCOD (/copy) * No Errors D SQL_OK C 0 * Correlation without qualification occurred for column D SQL_CRL_NQL C 12 * Number of host variables is incorrect. D SQL_NR_HVAR C 30 * &1 applies to entire table (no where on update) D SQL_ENTIRE_TBL C 88 * End of file or row not found D SQL_EOF C 100 * Relational database &1 not the same as current server &2. D SQL_RLDB_SVR C 114 * Argument &1 of substringing function not valid. D SQL_SST_ARG C 138 * CHECK condition text too long. D SQL_CHECK_LEN C 177 * Query expression text for view &1 in &2 too long. D SQL_EXPR_LEN C 178 * Syntax of date, time, or timestamp value not valid. D SQL_DTTM_STX C 180 * Value in date, time, or timestamp string not valid. D SQL_DTTM_VAL C 181 * Result of date or timestamp expression not valid. D SQL_DTTM_RSLT C 183 * Mixed data not properly formed. D SQL_MIX_DATA C 191 * &1 in &2 type *&3 not found. D SQL_OBJ_ERR C 204 * Not enough SQLVAR entries were provided in the SQLDA. D SQL_DA_VARA C 237 * Not enough SQLVAR entries were provided in the SQLDA. D SQL_DA_VARB C 239 * Conversion error in assignment to host variable &2. D SQL_DTA_CVT C 304 * Too many host variables specified. D SQL_HST_VAR C 326 * Character conversion cannot be performed. D SQL_CHR_CVT C 331 * Characters conversion has resulted in substitution characters. D SQL_CHR_SBT C 335 * Datalink in table &1 in &2 may not be valid due to pending links. D SQL_DTA_LNK C 360 * Alias &1 in &2 created but table or view not found. D SQL_ALIAS_OBJ C 403 * Character in CAST argument not valid. D SQL_CAST_ARG C 420 * Value of parameter &4 in procedure &1 in &2 too long. D SQL_PRM_LEN C 445 * Truncation of data may have occurred for ALTER TABLE in &1 of &2. D SQL_DTA_TRNC C 460 *Not authorized to object &1 in &2 type *&3. D SQL_OBJ_NAUT C 551 * Not authorized to &1. D SQL_NAUT C 552 * Not all requested privileges revoked from object &1 in &2 type &3. D SQL_PRV_NRVK C 569 * Not all requested privileges to object &1 in &2 type &3 granted. D SQL_PRV_NGRT C 570 * Commit level &1 escalated to &2 lock. D SQL_CMT_ESC C 595 * Error occurred during DISCONNECT of relational database &1. D SQL_DSC_ERR C 596 * WHERE NOT NULL clause ignored for index &1 in &2. D SQL_IGN_NNL C 645 * Data conversion or data mapping error. D SQL_DTA_MAP C 802 * Mixed or DBCS CCSID not supported by relational database &1. D SQL_DBCS_NSUP C 863 * Outcome unknown for the unit of work. D SQL_UW_UNK C 990 * Table &1 in &2 created but could not be journaled. D SQL_CRT_NJRN C 7905 * Cursor not open D SQE_NOT_OPEN C -501 * Update prevented by referential constraint &1 in &2. D SQE_CONSTRAINT C -531 * Duplicate key value specified. D SQE_DUPKEY C -803

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 32

7.1 – Subquery Funzione Count I colori preferiti Create Table ColoriPreferiti (Nome char(10) not null, Colore char(10) not null) NOME -------beppe franco alberto gianni emanuela marco sandro monica roberto anna stefania

COLORE -------blu rosso bianco rosso blu giallo beige fucsia verde blu rosso

Persone che hanno scelto un colore scelto da almeno altre 2 persone Select * From ColoriPreferiti OuterRow Where 3 <= ( Select Count( * ) From ColoriPreferiti InnerRow Where OuterRow.Colore = InnerRow.Colore ) order by colore NOME beppe emanuela anna franco gianni stefania

COLORE blu blu blu rosso rosso rosso

Persone che hanno scelto un colore non scelto da nessun altro Select * From ColoriPreferiti OuterRow Where 1 >= ( Select Count( * ) From ColoriPreferiti InnerRow Where OuterRow.Colore = InnerRow.Colore ) order by colore NOME sandro alberto monica marco roberto

COLORE beige bianco fucsia giallo verde

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 33

Titoli non asteriscati di una divisa che ha un solo titolo Select CodTitolo, DesTitolo, CodDivisa, DesDivisa From Titoli OuterRow join Divise on OuterRow.DivTitolo = CodDivisa Where 1 = ( Select Count( * ) From Titoli InnerRow Where OuterRow.DivTitolo = InnerRow.DivTitolo) and DesTitolo not like '%*%' codice titolo 08004711 00905437

descrizione titolo BANK HAPOALIM LTD SNAP LIMIT 10/10

codice divisa ILS RUB

ILS RUB

descrizione divisa SHEKEL ISRAELE RUBLO RUSSO

Funzione Some Create Table MovBancari ( NumMovim Dec( 9, 0 ) Not Null, CdPortaf Dec( 7, 0 ) Not Null, ImpMovim Dec( 15, 2 ) Not Null, DtMovim Date Not Null, DtValuta Date Not Null, Primary key ( NumMovim ) )

NumMovim CdPortaf ImpMovim 234112 234113 234114 234115 234116 234117 234118 234119 234120

61025 11010 21076 21450 11745 11076 11010 21076 21450

1250.00 27852.25 25.50 700.60 1000.00 250.25 89.50 201.00 399.75

DtMovim

DtValuta

2001-05-01 2001-05-01 2001-05-03 2001-05-04 2001-05-04 2001-05-05 2001-05-05 2001-05-05 2001-05-06

2001-05-15 2001-05-04 2001-05-07 2001-05-10 2001-05-08 2001-05-08 2001-05-10 2001-05-09 2001-05-08

Elenco dei movimenti che hanno valuta uguale ad almeno un’altro movimento Select NumMovim, DtValuta From MovBancari OuterMov Where DtValuta = Some ( Select DtValuta From MovBancari InnerMov Where InnerMov.NumMovim <> OuterMov.NumMovim) NUMMOVIM 234.115 234.116 234.117 234.118 234.120

DTVALUTA 10.05.2001 08.05.2001 08.05.2001 10.05.2001 08.05.2001

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 34

Subquery con tabelle derivate (common table expression) Scelta dei programmi più manutenzionati with programmi (programma, tipo, conteggio) as (select objname, objtype, count(*) from ManutPGM group by objname, objtype) select programma, tipo, conteggio from programmi where conteggio > (select avg(distinct conteggio) from programmi) order by conteggio desc PROGRAMMA C01GR ABTI999R REVA001R_E M01GR V42GR FLRIC01C FLIC002R ABAN053R TARF340R GEFO700R REVA052R_E

TIPO RPGLE RPGLE RPGLE RPGLE RPGLE CLP RPGLE RPGLE RPGLE RPGLE RPGLE

CONTEGGIO 51 44 41 38 38 37 35 33 31 31 30

Funzione SUM e aggregazioni per data PF/SPLFLIST A A A A

R RSPLFLIST USERNAME OUTQNAME OPENDATE

10 10 L

xsql ('drop table QTEMP/SPLFAGING') xsql ('create table QTEMP/SPLFAGING as ( + Select USERNAME as Utente, + substr(GETUSRPRF(USERNAME), 1, 20) as Nome, + OUTQNAME as Coda, + decimal(+ sum( Case When OPENDATE = CURRENT_DATE Then 1 + Else null End ), 6, 0) as [1], + decimal(+ sum( Case When CURRENT_DATE - OPENDATE between 1 and 5 Then 1 + Else null End ), 6, 0) as [2-5], + decimal(+ sum( Case When CURRENT_DATE - OPENDATE between 6 and 10 Then 1 + Else null End ), 6, 0) as [6-10], + decimal(+ sum( Case When CURRENT_DATE - OPENDATE between 11 and 20 Then 1 + Else null End ), 6, 0) as [11-20], + decimal(+ sum( Case When CURRENT_DATE - OPENDATE >20 Then 1 + Else null End ), 6, 0) as [>20], + decimal(count(*), 6, 0) as [TOT], + decimal(sum(Pages)/1000, 10, 0) as [Pag_1000] + from SPLFLIST + group by USERNAME, OUTQNAME + order by USERNAME, OUTQNAME) + with data') RUNQRY *N QTEMP/SPLFSUMM Nome QPGMR Programmatore SYSOP Operatore

Coda QPRINT QPRINT

“1” 100 12

“2-5” 75 120

“6-10” 12 0

“11-20” 0 0

Appunti di programmazione SQL – 1 – ©GC giugno 2006

“>20” 254 75

“Pag_1000” 152 15

Pagina 35

Funzione AVG e aggregazioni per ora create table CHIAMATE ( area char(3), tempo time ) select area, tempo from chiamate AREA ---SR GI GC GC GC GC GC SR SR GI EDP SR PC EDP

TEMPO -------02:50:00 01:50:00 04:20:00 01:30:00 00:30:00 00:10:00 00:25:00 00:25:00 00:10:00 00:15:00 00:12:00 00:05:00 00:10:00 00:50:00

Select area, avg( hour(Tempo) *60 + minute(Tempo) ) As TempoMedio from CHIAMATE Group By area Order by TempoMedio desc AREA ----GC GI SR EDP PC

TEMPOMEDIO ---------83 62 52 31 10

Select Gruppo, avg( hour(Tempo) *60 + minute(Tempo) ) As TempoMedio from ( Select Case When Area in ('GI', 'GC') Then 'GI/GC' When Area = 'SR' Then 'SR' When Area = 'EDP' Then 'EDP' Else 'ALTRI' End As Gruppo, tempo from CHIAMATE) as chiamate Group By Gruppo Order by TempoMedio desc GRUPPO -----GI/GC SR EDP ALTRI

TEMPOMEDIO ---------77 52 31 10

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 36

Select Avg( Case When hour(Tempo) *60 + Avg( Case When hour(Tempo) *60 + Avg( Case When hour(Tempo) *60 + Avg( Case When hour(Tempo) *60 + from CHIAMATE "GI/GC" ------77

SR -----52

Area in ('GI', 'GC') Then minute(Tempo) Else null End ) as "GI/GC", Area = 'SR' Then minute(Tempo) Else null End ) as "SR", Area = 'EDP' Then minute(Tempo) Else null End ) as "EDP", Area not in ('GI', 'GC', 'SR', 'EDP') Then minute(Tempo) Else null End ) as "ALTRI"

EDP ----31

ALTRI -----10

with vCHIAMATE (ca1, ca2, ca3, ca4) as (Select Avg( Case When Area in ('GI', 'GC') Then hour(Tempo) *60 + minute(Tempo) Else null End ) as "GI/GC", Avg( Case When Area = 'SR' Then hour(Tempo) *60 + minute(Tempo) Else null End ) as "SR", Avg( Case When Area = 'EDP' Then hour(Tempo) *60 + minute(Tempo) Else null End ) as "EDP", Avg( Case When Area not in ('GI', 'GC', 'SR', 'EDP') Then hour(Tempo) *60 + minute(Tempo) Else null End ) as "ALTRI" from CHIAMATE) select ( cast(ca1/60 as char) !! ':' !! digits(decimal(mod(ca1, 60), 2, 0))) as "GI/GC", ( cast(ca2/60 as char) !! ':' !! digits(decimal(mod(ca2, 60), 2, 0))) as "SR", ( cast(ca3/60 as char) !! ':' !! digits(decimal(mod(ca3, 60), 2, 0))) as "EDP", ( cast(ca4/60 as char) !! ':' !! digits(decimal(mod(ca4, 60), 2, 0))) as "ALTRI" from vCHIAMATE "GI/GC" 1:17

SR 0:52

EDP 0:31

ALTRI 0:10

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 37

7.2 – Join create table titoli (titolo char(7), destitolo char(30), tipotitolo char(1)) create table obbligaz (titolo char(7), scadenza date) create table quotazioni (titolo char(7), dataquotaz date, quotazione dec(15, 5))

Inner Join TITOLO TIPO F.MI A OL.MI A 0352752 O 9852640 O

OBBLIGAZ SCAD.ZA

0352752 31/10/03

select titoli.titolo, destitolo, scadenza from titoli join obbligaz on titoli.titolo = obbligaz.titolo where tipotitolo = 'O' select t.titolo, destitolo, scadenza from titoli t join obbligaz o on t.titolo = o.titolo where tipotitolo = 'O' TITOLO 0352752

DESTITOLO BTP OTT2003 12%

SCADENZA 31/10/03

Left Join select t.titolo, destitolo, scadenza from titoli t left join obbligaz o on t.titolo = o.titolo where tipotitolo = 'O' TITOLO 0352752 9852640

DESTITOLO BTP OTT2003 12% JUMPERS CO.

SCADENZA 31.10.2003 NULL

select t.titolo, destitolo, ifnull(scadenza, '0001-01-01') as Scadenza from titoli t left join obbligaz o on t.titolo = o.titolo where tipotitolo = 'O' TITOLO 0352752 9852640

DESTITOLO BTP OTT2003 12% JUMPERS CO.

SCADENZA 31.10.2003 01.01.0001

select t.titolo, destitolo, value(scadenza, '0001-01-01') as Scadenza from titoli t left join obbligaz o on t.titolo = o.titolo where tipotitolo = 'O'

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 38

Left join - Uso dei campi NULL in RPG D titolo D destitolo D scadenza

s s s

7 30 d

c/exec sql declare cTitoli cursor for c+ select t.titolo, destitolo, scadenza c+ from titoli t left join obbligaz o c+ on t.titolo = o.titolo c+ where tipotitolo = 'O' c/end-exec c/exec sql whenever sqlerror goto eTitoli c/end-exec c/exec sql open cTitoli c/end-exec c/exec sql whenever not found go to eTitoli c/end-exec c

dou

sqlcod <> 0

c/exec sql c+ fetch cTitoli into :titolo, :destitolo, :scadenza c/end-exec c

eTitoli

tag

ID messaggio . . . . . : SQL0305 Messaggio . . . : Indicator variable required. D iscadenza

5i 0

c/exec sql c+ fetch cTitoli into :titolo, :destitolo, :scadenza :iscadenza c/end-exec

Valore TITOLO = '0352752', SCADENZA = '2003-10-31', ISCADENZA = 0 TITOLO = '9852640', SCADENZA = '2003-10-31', ISCADENZA = -1

c c c c

iscadenza = -1

eval endif enddo

scadenza = d'0001-01-01'

TITOLO = '0352752', SCADENZA = '2003-10-31', ISCADENZA = 0 TITOLO = '9852640', SCADENZA = ‘0001-01-01’, ISCADENZA = -1

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 39

Campi NULL In RPG/DDS tradizionale . . . DDS/PF A A A A

R RECORD CODICE RAGSOC COGNOME

6 40 40

ALWNULL ALWNULL

RPGLE CrtBndRRPG

AlwNull(*UsrCtl)

C** scrittura di un record c eval c eval c eval

Codice = ‘054279’ %NullInd( RagSoc ) = *On Cognome = ‘ROSSI’

c** lettura di un record c If

%NullInd( RagSoc )

c c

... endif

DEBUG _QRNU_NULL_RagSoc = ‘1’/’0’

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 40

Exception Join TITOLO TIPO F.MI A OL.MI A 0352752 O 9852640 O

OBBLIGAZ SCAD.ZA

0352752 31/10/03

select t.titolo, destitolo from titoli t exception join obbligaz o on t.titolo = o.titolo where tipotitolo = 'O' TITOLO 9852640

DESTITOLO JUMPERS CO.

Join su più di due files TITOLO

TIPO

OBBLIGAZ SCAD.ZA

TITOLO

DATAQ

QUOTAZ

F.MI

0352752

10/10/2001

100,7500

OL.MI

0352752

12/10/2001

100,9000

0352752

31/10/03

0352752

15/10/2001

101,2000

9852640

30/11/05

9852640 2001-10-10 9852640 2001-10-11

98,0000 98,5000

select t.titolo, destitolo, scadenza, dataquotaz, quotazione from titoli t join obbligaz o on t.titolo = o.titolo join quotazioni q on t.titolo = q.titolo where tipotitolo = 'O' TITOLO 0352752 0352752 0352752 9852640 9852640

DESTITOLO BTP OTT2003 12% BTP OTT2003 12% BTP OTT2003 12% JUMPERS CO. JUMPERS CO.

SCADENZA 31.10.2003 31.10.2003 31.10.2003 30.11.2005 30.11.2005

DATAQUOTAZ 10.10.2001 12.10.2001 15.10.2001 10.10.2001 11.10.2001

QUOTAZIONE 100,75000 100,90000 101,20000 98,00000 98,50000

DESTITOLO BTP OTT2003 12% JUMPERS CO.

SCADENZA 31.10.2003 30.11.2005

DATAQUOTAZ 15.10.2001 11.10.2001

Appunti di programmazione SQL – 1 – ©GC giugno 2006

QUOTAZIONE 101,20000 98,50000

Pagina 41

Analisi dell’attività SQL select t.titolo, destitolo, scadenza, dataquotaz, quotazione from titoli t join obbligaz o on t.titolo = o.titolo join quotazioni q on t.titolo = q.titolo where tipotitolo = 'O' and dataquotaz = (select max(dataquotaz) from quotazioni where t.titolo = titolo) order by dataquotaz Current connection is to relational database SYS01. Opzioni query reperite nel file QAQQINI nella libreria QUSRSYS. L'accesso in sequenza di arrivo è stato usato per il file QUOTAZIONI. E' stata costruita la via di accesso per il file QUOTAZIONI. Questa operazione è durata 0 minuti e 0,0 secondi. La via di accesso costruita contiene 5 voci. Il percorso di accesso è stato costruito utilizzando le attività parallele 0. La via di accesso è stata costruita usando i seguenti campi chiave. DATAQUOTAZ ASCEND. E' stata costruita la via di accesso per il file TITOLI. La via di accesso è stata costruita usando i seguenti campi chiave. TITOLO ASCEND. E' stata costruita la via di accesso per il file OBBLIGAZ. La via di accesso è stata costruita usando i seguenti campi chiave. TITOLO ASCEND. Il file QUOTAZIONI è stato elaborato nella posizione di unione 1. Il file TITOLI è stato elaborato nella posizione di unione 2. Il file OBBLIGAZ è stato elaborato nella posizione di unione 3. ODP created. Blocking used for query.

CHGQRYA QRYOPTLIB(COSTAGLIOL) Opzioni query reperite nel file QAQQINI nella libreria COSTAGLIOL. L'accesso in sequenza di arrivo è stato usato per il file QUOTAZIONI. E' stata costruita la via di accesso per il file OBBLIGAZ. E' stata costruita la via di accesso per il file QUOTAZIONI. File TITOLI elaborato nella posizione di unione 1. Il file OBBLIGAZ è stato elaborato nella posizione di unione 2. Il file QUOTAZIONI è stato elaborato nella posizione di unione 3. Costruito un file temporaneo dei risultati per il query.

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 42

8.1 – Select . . . into . . . Valore del portafoglio titoli ad una data Quantita * Prezzo OBBLIGAZIONI = ( ----------------- / cambio ) + rateo 100 AZIONI

Quantita * Prezzo / cambio

c/exec sql c+ select sum((case when TipoTitolo = 'R' then c+ ((Quantita * Prezzo /100) / Cambio) + Rateo c+ else c+ ((Quantita * Prezzo) / Cambio) c+ end)) c+ into :pTIT c+ from Portaf00f P join Titoli00f T on P.CodTitolo = T.Codtitolo c+ left join Ratei00f R on R.CodTitolo = T.Codtitolo and c+ DataPort = DataRateo c/end-exec

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 43

8.2 – Fetch scroll e For :n Rows Ri-lettura dati query d** definizione variabili referenziate d movimenti extname(Movim00f) d** preparazione estrazione sql d SqlStmt s 500 c** parte comune a tutti i tipi di selezione SqlStmt = 'select * from Movim00f + c eval c where Cliente <> ' + %char(IntCli) + c ' and Area in (select CdArea from + c Area00f where Provincia = ''TO'')' c** parte variabile c if . . . . c eval SqlStmt = %trimR(SqlStmt) + c ' and TotVendite > . . . . c endif c** ordinamento comune a tutti i tipi di selezione c eval SqlStmt = %trimR(SqlStmt) + c ' order by Cliente, Area c** prepara l’istruzione c/exec sql prepare pMovimenti from :SqlStmt c/end-exec c** dichiara il cursore c/exec sql declare cMovimenti scroll cursor for pMovimenti c/end-exec c** apre il file c/exec sql open cMovimenti c/end-exec

DO 2

---------------------------------------------------

c/exec sql c+ fetch first from cMovimenti into :Movimenti c/end-exec if SQLCOD = 0 ElaboraRecord WHILE SQLCOD = 0 c/exec sql fetch next cMovimenti into :Movimenti c/end-exec if SQLCOD = 0 ElaboraRecord Endif END WHILE Endif

ENDDO

-------------------------------------------------

c** chiude il file c/exec sql close cMovimenti c/end-exec

Estrazione primi :n records c/exec sql c+ fetch first :n Rows Only . . . from . . . into c/end-exec

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 44

8.3 – Inner/Outer Select (Estrazione di righe a pagine) Definizioni: n = numero di records per pagina p = pagina richiesta Primo record sulla pagina p = (p-1) * n + 1 Ultimo record sulla pagina p = p * n (fino al massimo numero di record nel file) Se viene richiesta una pagina specifica : SELECT TOP n * FROM (SELECT TOP p*n * FROM Tabella /* .. eventuale filtro sui dati */ ORDER BY CampoSort DESC Per reperire la pagina 6 (con 5 righe per pagina) , ad esempio: SELECT TOP 5 * FROM (SELECT TOP 30 * FROM Clienti) AS T1 ORDER BY Codice DESC Il dataset risultante viene presentato in ordine discendente. Pertanto sarà necessario leggere i dati al contrario o aggiusatre la sequenza con una ulteriore Select: SELECT * FROM (SELECT TOP 5 * FROM (SELECT TOP 30 * FROM Clienti) AS T1 ORDER BY Codice DESC) AS T2 Poichè l’SQL di iSeries non supporta l’istruzione SELECT TOP, per visualizzare pagina 6 della tabella Clienti si iSeries immettere: SELECT * FROM (SELECT * from (SELECT * from Clienti /* inserire il filtro sui dati */ ORDER BY Codice ASC FETCH FIRST 30 ROWS ONLY) AS T1 ORDER BY Codice DESC FETCH FIRST 5 ROWS ONLY) AS T2 ORDER BY Codice Ci sono 2 istruzioni “inner Select”. La più interna, designata come T1, forza la pagina finale dei dati all’inizio dell’elenco. L’altra select, designata come T2 estrae solo le righe che effettivamente devono comparire sulla pagina. Il select più esterna semplicemente riordina i dati nella pagina prima della visualizzazione. In questo esempio la query T1 crea una tabella temporanea con 30 righe, mentre la query T2 legge T1 e crea un’altra tabella temporanea con 5 righe, ma in ordine discendente; Infine l’ultima query crea una terza tabella temporanea per l’ordinamento corretto e visualizza i dati richiesti.

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 45

8.4 – Select condizionata E’ possibile usare una instruzuine CASE in una clausola WHERE. In questo modo è possibile estrarre i dati con vari criteri in modo dinamico, senza usare la Prepare, che in alcuni casi fornisce delle performances più scadenti. Nella istruzione che segue è possibile estrarre i Clienti per esempio sulla base della provincia o in un range di CAP. D iProvincia D iDalCAP D iAlCAP

S S S

1A 5A 5A

C/exec sql C+ C+ declare cCLIENTI cursor for C+ C+ SELECT Codice, Nome, Provincia, CAP C+ FROM Clienti C+ WHERE C+ (Case when :iProvincia <> '' C+ then :iProvincia C+ else Provincia C+ End) = Provincia C+ AND C+ (Case when :iCAP <> '' C+ then substr(CAP,1,5) C+ else :iCAP C+ End) between :iDalCAP and :iAlCAP C/end-exec C/exec sql open cCLIENTI C/end-exec C dow '1' C/exec sql C+ fetch cCLIENTI into :zCLIENTI C/end-exec C if SQLStt <> '00000' C leave C endif C****** . . . . . . . . . C enddo C/exec sql close cCLIENTI C/end-exec Qualora il campo Provincia consenta valori nulli, ad esempio, la parte di istruzione relativa alla selezione della Provincia potrà essere modificata come segue: (Case when :iCusType <> '' then :iCusType else ifnull(CusType,' ') End) = ifnull(CusType,' ') La funzione IFNULLconverte null in blank, e così I record con provincia nulla soddisfano la comparazione. Ovviamente è possibile aggiungere ulteriori gruppi CASE per aumentare i criteri di selezione.

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 46

8.5 – Combinati(LF) e Union(SQL) PF - CONTO A A A A A

R FCONTO NUMERO NOME1 NOME2 TIPO

6 10 10 1

COLHDG('NUM' 'C/C') COLHDG('PRIMO' 'INTESTATARIO') COLHDG('SECONDO' 'INTESTATARIO') VALUES(' ' 'S') COLHDG('T' 'C')

LF - CONTOC -(COMBINATO) A*--------------------------------------------------------------A** CHIAVE SU 1.INTESTATARIO A R FCONTO1 PFILE(CONTO) A NUMERO A NOME1 A NOME2 A TIPO A K NOME1 A K NOME2 A S TIPO COMP(EQ ' ') A*--------------------------------------------------------------A** CHIAVE SU 2.INTESTATARIO A R FCONTO2 PFILE(CONTO) A NUMERO A NOME1 RENAME(NOME2) A NOME2 RENAME(NOME1) A TIPO A K NOME1 A K NOME2 A S TIPO COMP(EQ ' ') A*--------------------------------------------------------A** SOCIETA A R FCONTOS PFILE(CONTO) A NUMERO A NOME1 A NOME2 A TIPO A K NOME1 A K NOME2 A S TIPO COMP(EQ 'S')

Dati di prova NUM C/C

PRIMO INTESTATARIO

SECONDO INTESTATARIO

BEPPE

FRANCO

GIOVANNI

BEPPE

ALBERTO

MARIO

ANTONIO

FIAT

T C

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 47

Elabora tutto il file - RPG fcontoc fqsysprt

if o

e f

k disk usropn infds(dsconto) printer

132

d intestatario

d dsconto d formato

10 500 270

261

c c c c c c c c c c c c c c c c c

open do read if leave endif select when eval when eval when eval endsl except enddo close

eval

oqsysprt o o o

contoc *hival contoc %eof

formato = 'FCONTO1' intestatario = 'Primo' formato = 'FCONTO2' intestatario = 'Secondo' formato = 'FCONTOS' intestatario = 'Società'

contoc *inlr = *on

1p 'Nome' +8 'n.CC' +2 'Intestatario'

nome1 numero intestatario

z +4

Risultato Nome ALBERTO ANTONIO BEPPE BEPPE FIAT FRANCO GIOVANNI MARIO MARIO

n.CC 3 5 1 2 6 1 2 3 5

Intestatario Primo Secondo Primo Secondo Società Secondo Primo Secondo Primo

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 48

Elabora tutto il file - SQLRPG fqsysprt

d Conto d Intestatario

132

printer

e ds s

c/exec sql declare cConto cursor for c+ select nome1, numero, 'Primo ' from Conto where tipo = ' ' c+ union c+ select nome2, numero, 'Secondo' from Conto where tipo = ' ' c+ union c+ select nome1, numero, 'Societa' from Conto where tipo = 'S' c+ order by 1 c/end-exec c/exec sql open cConto c/end-exec c/exec sql whenever not found go to eConto c/end-exec c dou sqlcod <> 0 c/exec sql fetch cConto into :nome1, :numero, :intestatario c/end-exec c except c enddo c eConto tag c/exec sql close cConto c/end-exec c

eval

oqsysprt o o o

*inlr = *on

1p 'Nome' +8 'n.CC' +2 'Intestatario'

nome1 numero intestatario

z +4

Risultato Nome ALBERTO ANTONIO BEPPE BEPPE FIAT FRANCO GIOVANNI MARIO MARIO

n.CC 3 5 1 2 6 1 2 3 5

Intestatario Primo Secondo Primo Secondo Societa Secondo Primo Secondo Primo

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 49

Accesso casuale – RPG h dftactgrp(*no) fcontoc fqsysprt

if o

e f

d ListaCC d contomin d contomax

d d d d d

s s s ds

contomin contomax intestatario dsconto formato

like(nome1) const like(nome1) const like(nome1) like(nome1)

261

c c c c c c c c c

open callp callp callp callp callp callp close eval

oqsysprt o o o oqsysprt o o o

contoc ListaCC('F':'FA9') ListaCC('CA':'CA9') ListaCC('AL':'AL9') ListaCC('E':'E9') ListaCC('Z':'Z9') ListaCC('AS':'AZ') contoc *inlr = *on 1 01 'Nome' +8 'n.CC' +2 'Intestatario'

Dettaglio 1 nome1 numero z intestatario B PI

d ncc

like(nome1) const like(nome1) const

contomin

p ListaCC

10 500 270

Testata

p ListaCC d ListaCC d contomin d contomax

c c c c c c c c c c c c c c c c c c c

k disk usropn infds(dsconto) printer

132

5i 0 setll eval except do read if return endif eval select when eval when eval when eval endsl except enddo

contoc ncc = 0 Testata *hival contoc %eof or nome1 > contomax or ncc > 50

ncc = ncc + 1 formato = 'FCONTO1' intestatario = 'Primo' formato = 'FCONTO2' intestatario = 'Secondo' formato = 'FCONTOS' intestatario = 'Società' Dettaglio

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 50

Accesso casuale – SQLRPG c/Campi selezione h dftactgrp(*no) fqsysprt

d ListaCC d contomin d contomax d d d d

Conto contomin contomax intestatario

132

printer

PR like(nome1) const like(nome1) const e ds s s s

like(nome1) like(nome1) 7

c/exec sql declare cConto cursor for c+ select nome1, numero, 'Primo ' from Conto where tipo = ' ' c+ and nome1 between :contomin and :contomax c+ union c+ select nome2, numero, 'Secondo' from Conto where tipo = ' ' c+ and nome2 between :contomin and :contomax c+ union c+ select nome1, numero, 'Societa' from Conto where tipo = 'S' c+ and nome1 between :contomin and :contomax c+ order by 1 c/end-exec c c c oqsysprt o oqsysprt o

callp . . . eval e

*inlr = *on

Testata . . . Dettaglio . . .

p ListaCC d ListaCC d contomin d contomax d ncc c c

ListaCC('F':'FA9')

1 01 1

B PI like(nome1) const like(nome1) const s eval except

5i 0 ncc = 0 Testata

c/exec sql open cConto c/end-exec c/exec sql whenever not found go to eConto c/end-exec c

dou

sqlcod <> 0 or ncc = 50

c/exec sql fetch cConto into :nome1, :numero, :intestatario c/end-exec c eval c except c enddo c eConto tag c/exec sql close cConto c/end-exec p ListaCC E

ncc = ncc + 1 Dettaglio

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 51

Accesso casuale – SQLRPG c/Parameter markers h dftactgrp(*no) fqsysprt o f d ListaCC d contomin d contomax d d d d

Conto contomin contomax intestatario

d SqlSelect d SqlStmt

132

printer

PR like(nome1) const like(nome1) const e ds s s s s s

like(nome1) like(nome1) 7 720 80

movea

dim(9) ctdata

SqlStmt

SqlSelect

c/exec sql prepare pConto from :SqlSelect c/end-exec c/exec sql declare cConto cursor for pConto c/end-exec c c c oqsysprt e o oqsysprt e o p ListaCC d ListaCC d contomin d contomax d ncc c c

callp ListaCC('F':'FA9') . . . eval *inlr = *on Testata 1 01 . . . Dettaglio 1 . . . B PI like(nome1) const like(nome1) const s eval except

5i 0 ncc = 0 Testata

c/exec sql open cConto using :contomin, :contomax, c+ :contomin, :contomax, c+ :contomin, :contomax c/end-exec c/exec sql whenever not found go to eConto c/end-exec c

dou

sqlcod <> 0 or ncc = 50

c/exec sql fetch cConto into :nome1, :numero, :intestatario c/end-exec c c c c

eval except enddo tag

eConto

ncc = ncc + 1 Dettaglio

c/exec sql close cConto c/end-exec p ListaCC **

select nome1, numero, 'Primo ' from Conto where tipo = ' ' and nome1 between ? and ? union select nome2, numero, 'Secondo' from Conto where tipo = ' ' and nome2 between ? and ? union select nome1, numero, 'Societa' from Conto where tipo = 'S' and nome1 between ? and ? order by 1 optimize for 50 rows

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 52

DEBUG SQL con pochi records 1 Per il file CONTO sono state considerate tutte le vie di accesso QGPLCONTOC 17.

17 - La chiave più a sinistra della via d'accesso non corrispondeva ad alcuno dei campi specificati per i criteri di scelta. Non è stato quindi possibile eseguire il posizionamento riga delle chiavi, per cui il costo per utilizzare questa via d'accesso risulta superiore a quello associato al metodo di accesso scelto. L'accesso in sequenza di arrivo è stato usato per 2 Per il file CONTO sono state considerate tutte le QGPLCONTOC 17. L'accesso in sequenza di arrivo è stato usato per 3 Per il file CONTO sono state considerate tutte le QGPLCONTOC 11.

il file CONTO. vie di accesso il file CONTO. vie di accesso

11 - La via di accesso contiene criteri di scelta/omissione statici che non sono compatibili con la selezione nel query.

L'accesso in sequenza di arrivo è stato usato per il file CONTO. Costruito un file temporaneo dei risultati per il query. Causa . . . . . : E' stato creato un file temporaneo dei risultati per contenere i risultati del query per il codice di errore 5. Questa operazione è durata 0 minuti e 0,0 secondi. Il file temporaneo creato contiene 9 record. I codici di errore e i loro significati sono: 5 - Per il query è stato specificato UNION.

DEBUG SQL con 250.000 records 1 Per il file CONTO sono state considerate tutte le vie di accesso QGPL/CONTOC 0. Percorso di accesso consigliato per il file CONTO. TIPO, NOME1. 2=1 3 Per il file CONTO sono state considerate tutte le vie di accesso QGPL/CONTOC 11. Costruito un file temporaneo dei risultati per il query. Questa operazione è durata 0 minuti e 1,2 secondi. Il file temporaneo creato contiene 20552 record.

create index contox1 on conto(tipo, nome1) create index contox2 on conto(tipo, nome2) La via di La via di La via di Costruito

accesso accesso accesso un file

del file CONTOX1 è stata del file CONTOX2 è stata del file CONTOX1 è stata temporaneo dei risultati

usata dal query. usata dal query. usata dal query. per il query.

TEMPI Stress test su anagrafe di piccole dimensioni RPG = 0:10 SQL = 3:05

Stress test su anagrafe di grosse dimensioni RPG = 0:45 SQL = 0:56

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 53

9.1 – Insert create table Movimenti (NumMov dec(7), importo dec(17, 2), DataMov date) Attenzione alle virgole !!

Tipo 1

insert into Movimenti values(1, 150,25, '2001-12-03')

Tipo 2 insert into movimenti (NumMov) values(2)

NUMMOV 2

IMPORTO -

NULL

DATAMOV -

Tipo 3 insert into movimenti select FQCSNB, FQA3VA, '2001-12-03' from GFMOT00F where FQCSNB between 104600 and 104665

Tipo 4 (not null) create table Movimenti (NumMov dec(7) not null, Importo dec(17, 2) not null, DataMov date not null) insert into movimenti (NumMov) values(2) Statement contains wrong number of values.

Tipo 5 (not null with default) create table Movimenti (NumMov dec(7) not null with default, Importo dec(17, 2) not null with default, DataMov date not null with default) insert into movimenti (NumMov) values(2)

NUMMOV 2

IMPORTO ,00

DEFAULT

DATAMOV 26.11.2001

Tipo 6 (inserimenti multipli) insert into movimenti values (1, 150, '2006-05-03' ), (2, 275, '2006-05-03' )

Appunti di programmazione SQL – 1 – ©GC giugno 2006

Pagina 54

9.2 – Update Arrotondamenti Importo Posdec 995 2 1.000 2 10.000 2 10.004 2 19.362 2 19.363 2

update arrotonda set i5dec = i0dec / 1936,27 , i2decna = i0dec / 1936,27 , i2deca1 = i0dec / 1936,27 + 0,005, i2deca2 = float(i0dec / 1936,27), i2deca3 = round(i0dec/1936,27, 2), i5deca1 = decimal((i0dec / 1936,27 + 0,005), 7, 2), i5deca2 = decimal(float(i0dec / 1936,27), 7, 2), i5deca3 = bigint((i0dec /1936,27 * 10 ** posdec) + 0,5) / 10 ** posdec, i5deca4 = round(i0dec/1936,27, int(posdec))

Risultato I0DEC 10,0

I5DEC 10,5

I2DECNA I2DECA1 I2DECA2 I2DECA3 10,2 10,2 10,2 10,2

I5DECA1 10,5

I5DECA2 10,5

I5DECA3 10,5

I5DECA4 10,5

995

0,51387

0,51

,51000

1.000

0,51645

0,51

0,52

,52000

,51000

,52000

10.000

5,16456

5,16

5,16000

10.004

5,16663

5,16

5,17

5,17000

5,16000

5,17000

19.362

9,99963

9,99

10,00

10,00 10,00000

9,99000 10,00000

10,00000

19.363 10,00015

10,00

10,00 10,00000 10,00000 10,00000

10,00000

Pagina 55

9.3 – Update statico d Movimenti

e ds

extname(Movim00f)

c** ----------------------------------------------------------c** opzioni c** ----------------------------------------------------------c/Exec SQL c+ Set Option CloSQLCsr = *EndMod c/End-Exec c** ----------------------------------------------------------c** dichiara il cursore c** ----------------------------------------------------------c/Exec SQL declare cMovimenti cursor for c+ select * from movim00f c+ order by DataMov, Provincia, Nome for update of TotVendite, TotResi c+ c/End-Exec c** ----------------------------------------------------------c** apre il file c** ----------------------------------------------------------c/exec sql open cMovimenti c/end-exec c** ----------------------------------------------------------c** reperisce e aggiorna i movimenti c** ----------------------------------------------------------c/exec sql whenever not found go to eMovimenti c/end-exec c/exec sql whenever sqlwarning continue c/end-exec c

dou

sqlcod <> 0

c/exec sql fetch cMovimenti into :Movimenti c/end-exec c** aggiorna i movimenti c/exec sql update Movim00f set TotVendite = :ImpVendite, c+ c+ TotResi = :ImpResi c+ where current of cMovimenti c/end-exec c

enddo

c** ----------------------------------------------------------c** termine programma c** ----------------------------------------------------------c eMovimenti tag c/exec sql c+ close cMovimenti c/end-exec

Pagina 56

9.4 – Update dinamico d** file da aggiornare d qfname s

d** campo da aggiornare d fnam s

d** dati da aggiornare d fdata s d** .. valore vecchio d ofdata s d** .. valore nuovo d ndata s

2 2 2

c**--------------------------------------------------------------c** override sul file richiesto c**--------------------------------------------------------------c eval cmd = 'OVRDBF SQLTABLE ' + qfname c eval cmd£ = %len(%trimr(cmd)) c call 'QCMDEXC' c parm cmd c parm cmd£ c**--------------------------------------------------------------c** imposta l'estrazione dei records c**--------------------------------------------------------------c eval SqlStmt = 'select ' + fnam + c ' from SqlTable ' + c ' for update of ' + fnam c/exec sql c+ prepare S1 from :SqlStmt c/end-exec c/exec sql declare C1 DYNAMIC SCROLL cursor for S1 c/end-exec c**--------------------------------------------------------------c** imposta l'aggiornamento records c**--------------------------------------------------------------c c c

eval

SqlStmt = 'update SqlTable ' + 'set ' + fnam + ' = ?' + ' where current of C1'

c/exec sql prepare S2 from :SqlStmt c/end-exec c**--------------------------------------------------------------c** apre il file c**--------------------------------------------------------------c/exec sql whenever sqlerror goto error c/end-exec c** apre il file c/exec sql open C1 using :fdata c/end-exec

Pagina 57

c** legge il file c

*hival

c/exec sql c+ fetch C1 into :fdata c/end-exec c c c

if leave endif

c** converte nel nuovo valore c if c movel c fdata lookup c movel c endif

sqlcod <> 0

fdata <> ofdata fdata ofdata tabold tabnew tabnew ndata

c** aggiorna c/exec sql execute S2 using :ndata c/end-exec c

enddo

c** chiude il file c closeall

tag

c/exec sql close C1 c/end-exec c eval c eval c call c parm c parm ** Definire qui la TabOld/TabNew

cmd = 'DLTOVR SQLTABLE' cmd£ = %len(cmd) 'QCMDEXC' cmd cmd£ di conversione Vecchio

Nuovo

Pagina 58

9.5 – Delete - Rimozione duplicati Creazione Tabella Adesioni create table adesioni (id int, nome char(40), email char(50))

Inserimento Adesioni insert into adesioni values(689, 'Lupo Alberto', 'alupo@tin.it') . . .

Elenco Adesioni select * from adesioni

Rimozioni Adesioni ripetute delete from adesioni where id not in (select min(id) from adesioni group by email)

Elenco Adesioni select * from adesioni

Pagina 59

9.5 – Fetch e Insert multiple Input STPRO00F . SPPORT CODICE PORTAFOGLIO

(6,0)

Output: WTPRO00F .WPORT CODICE PORTAFOGLIO .WBATCH NUMERO BATCH

(6,0) (2,0)

(da 1 a n)

D** Definizione del file dwtpro00f e ds d** Portafogli d Portafogli d Portafoglio d NumBatch c c

*entry

occurs(10000)

like(WPORT ) like(WBATCH) plist parm

c/exec sql declare cPortaf cursor for c+ select SPPORT, mod(SPPORT, :n) + 1 c+ from stpro00f where . . . c/end-exec c/exec sql open cPortaf c/end-exec c/exec sql whenever not found go to ePortaf c/end-exec c/exec sql whenever sqlwarning continue c/end-exec c/exec sql fetch cPortaf for 10000 rows into :Portafogli c/end-exec c dou sqlcod <> 0 c

eval

R = Sqler3

c/exec sql insert into wtpro00f :R rows values(:Portafogli) c/end-exec c enddo c ePortaf tag c/exec sql close cPortaf c/end-exec

Pagina 60

10.1 – DRDA - Accesso a database remoto Accesso a un altro database iSeries WRKRDBDIRE Visualizzazione dettagli database relazionale Database relazionale . . . . Ubicazione remota: Ubicazione remota . . . . Tipo . . . . . . . . . . Descrizione unità . . . . Ubicazione locale . . . . Identificativo rete remota Modo . . . . . . . . . . . Programma di transazione .

c c

. . :

SYS02

. . . . . . .

SYS02 *SNA *LOC *LOC *LOC *NETATR *DRDA

movel(p) movel(p)

. . . . . . .

: : : : : : :

'??? '???

' '

user password

10 10

c** connessione a sistema SYS02 c/exec sql connect to SYS02 user :user using :password c/end-exec c** dichiara il file delle quotazioni c/exec sql declare Quotazioni cursor for c+ select . . . . c/end-exec

Accesso a un database DB2 su altra piattaforma Questa configurazione consente l’accesso al database NRTHWIND sul DB2 UDB Windows server.

ADDRDBDIRE RDB(NRTHWIND) RMTLOCNAME('DB2WIN.COMPANY.COM' *IP) TEXT('NRTHWIND Database su DB2WIN') PORT(50000) RMTAUTMTH(*ENCRYPTED *ALWLOWER) DEV(*LOC) LCLLOCNAME(*LOC) RMTNETID(*LOC) MODE(*NETATR) TNSPGM(*DRDA) Spiegazione dei parametri: RDB – Nome del database relazionale. Questo è il nome che iSeries usa per connettersi al server UDB e deve anche essere il nome specifico del database sul server remoto. RMTLOCNAME – immettere il nome o l’indirizzo IP del DB2 UDB remoto su Windows server. Specificare *IP come tipo connessione. PORT – la porta di default per DB2 UDB for Windows è 50000. Configurazione del DB2 UDB for Windows Server Per consentire a iSeries di accedere ai dati, occorre configurare il UDB sul Windows DB2 server con questi parametri: • •

Un utente e password per accdere al database e agli oggetti del database. Il nome utente deve essere di 10 o meno. Nomi più lunghi sono supposrtati dalla V5R3 (i5/OS). L’istanza del server deve essere configurata per consentire ai clients remoti di connetersi via TCP/IP. Si può verificare il nome della porta dal “DB2 control center”, right clicking sull ‘istanza del DB2 che si vuole usare e selezionare “Setup Communications”. Quindi selezionare TCP/IP e clickkare il bottone delle proprietà.

Pagina 61

Esecuzione della Query sul Windows DB2 da iSeries Alcuni attributi per la sessione SQL devono essere configurati prima di eseguire la query. Se si usa STRSQL le opzioni sono disponibili con F13; nell’SQL embedded usare le apposite istruzioni. In STRSQL, selezionare F13 e cambiare la “naming convention” in *SQL, e la “date format” e “time format” a *ISO. La “naming convention” *SYS non può essere usata per connettersi a Windows DB2 server. Inoltre se il lavoro attuale usa il CCSID di default 65535, cambiarlo il 280 (IT EBCDIC) con CHGJOB CCSID(x) o cambiarlo per il profilo utente. Connettersi al database remoto con l’istruzione CONNECT: Connect To Nrthwind User BEPPE Using 'password'

Quando lo statement è completato siamo connessi al database NRTHWIND su DB2 for Windows servers come utente BEPPE. Non dimenticarsi quando si usa la sintassi *SQL di qualificare la query con : owner name.object name invece della trazionale iSeries library name/object name. Per le richieste non qualificate l’utente corrente viene assunto come owner. Pertanto: Select * From Ordini

di fatto viene tradotta in Select * From BEPPE.Ordini

E’ possibile anche effettuare aggiornamenti. Esempio: Update Set Where With

Nrthwind.Beppe.Ordini PrezzoUnitario=PrezzoUnitario*1.10 PrezzoUnitario<100 NC

Pagina 62

10.2 – DRDA2 – Two Phase Commit d Utente d Password

s s

10 10

c**--------------------------------------------------------------c** reset di tutte le connessioni attive c**--------------------------------------------------------------c/exec sql whenever sqlerror continue c/end-exec c/exec sql disconnect current c/end-exec c**--------------------------------------------------------------c** esegue le connessioni c**--------------------------------------------------------------c/exec sql connect to SYS01 c/end-exec c if sqlcod <> 0 and sqlcod <> -842 c goto Disconnect c endif c c

'Password'

eval dsply

Utente = 'COSTAGLIOL' Password

c/exec sql connect to SYS02 user :Utente using :Password c/end-exec c if sqlcod <> 0 and sqlcod <> -842 c goto Disconnect c endif c**--------------------------------------------------------------c** inserice i dati c**--------------------------------------------------------------c** primo record su SYS01 c/exec sql set connection SYS01 c/end-exec c/exec sql insert into QGPL/filelocale c+ values('PRIMO', 'RECORD') c/end-exec c if sqlcod <> 0 c goto Rollback c endif c** primo record su SYS02 c/exec sql set connection SYS02 c/end-exec c/exec sql insert into QGPL/fileremoto c+ values('PRIMO', 'RECORD') c/end-exec c if sqlcod <> 0 c goto Rollback c endif

Pagina 63

c** secondo record su SYS01 c/exec sql set connection SYS01 c/end-exec c** questa INSERT va in errore c/exec sql insert into QGPL/filelocale c+ values('SECONDO', 'RECORD IN ERRORE') c/end-exec c if sqlcod <> 0 c goto Rollback c endif c**--------------------------------------------------------------c** conferma gli aggiornamenti c**--------------------------------------------------------------c/exec sql commit c/end-exec c goto Disconnect c**--------------------------------------------------------------c** rimuove gli aggiornamenti c**--------------------------------------------------------------c RollBack tag c/exec sql RollBack c/end-exec c**--------------------------------------------------------------c** disconnette c**--------------------------------------------------------------c Disconnect tag c/exec sql release all c/end-exec c/exec sql commit c/end-exec c** disconnette tutto c/exec sql disconnect all c/end-exec c** ripristino della connessione locale c/exec sql connect reset c/end-exec c c

eval return

*inlr = *on

Pagina 64

SYS01 create table filelocale (campo1 char(10), campo2 char(10)) crtjrnrcv jrnlocale crtjrn jrnlocale jrnlocale strjrnpf filelocale jrnlocale SYS02 create table fileremoto (campo1 char(10), campo2 char(10)) crtjrnrcv jrnremoto crtjrn jrnremoto jrnremoto strjrnpf fileremoto jrnremoto SYS01 Create SQL ILE RPG Object (CRTSQLRPGI) Object . . . . . . Library . . . . Source file . . . Library . . . . Source member . . Commitment control

. . . . . .

Relational database

. . . . . .

DRDA2 COSTAGLIOL PFR_READ COSTAGLIOL DRDA2 *ALL

Name Name, Name, Name, Name, *CHG,

*CURLIB QRPGLESRC *LIBL, *CURLIB *OBJ *ALL, *CS, *NONE...

. . . . . . > SYS02

RDB user . . . . . . . . . . . . RDB user password . . . . . . .

> > > > > >

COSTAGLIOL XXXXXXXX

Name, *CURRENT Character value, *NONE, ' '

DEBUG TRACE -----

Collegamento al database relazionale SYS01 terminato. DISCONNECT completed. CONNECT to relational database SYS01 completed. Current connection is to relational database SYS01. DSPLY Password XXXXXXXXXX CONNECT to relational database SYS02 completed. Current connection is to relational database SYS02. SET CONNECTION to relational database SYS01 completed. 1 rows inserted in FILELOCALE in COSTAGLIOL. SET CONNECTION to relational database SYS02 completed. 1 rows inserted in FILEREMOTO in *N. SET CONNECTION to relational database SYS01 completed. Value for column or variable CAMPO2 too long. Rollback completed. RELEASE of all relational databases completed. Collegamento al database relazionale SYS01 terminato. Collegamento al database relazionale SYS02 terminato. Commit completed. DISCONNECT of all connected relational databases completed. CONNECT to relational database SYS01 completed. Current connection is to relational database SYS01.

Pagina 65

10.3 – SQL dinamico h DftActGrp(*No) BndDir('QC2LE') f** stampa fqsysprt o

132

disk

d** richiesta sql d SqlStmt s

d** puntatori alla SQLDA d pSQLDA s d pSQLVAR s

* *

d** dimensioni della SQLDA d nSQLDA s d szSQLDA s

5i 0 10i 0

d** record output e intestazione d Record s 15000 d R132 s 132 d** indice campi d i s 5i 0 d oh s 5i 0 d od s 5i 0 d**--------------------------------------------------------------d** include la SQLDA d**--------------------------------------------------------------d* SQL Descriptor area d SQLDA DS based(pSQLDA) d SQLDAID 1 8A d SQLDABC 9 12B 0 d SQLN 13 14B 0 d SQLD 15 16B 0 d SQL_VAR 80A DIM(200) d SQLVAR d SQLTYPE d SQLLEN d Precis d Scale d SQLRES d SQLDATA d SQLIND d SQLNAMELEN d SQLNAME

d cSQLLEN d pSQLLEN d pPrecis d aPrecis d pScale d aScale

s ds

d Edita d Precis d Scale d SQLDATA

based(pSQLVAR) 1 3 3 4 5 17 33 49 51

2B 0 4B 0 3 4 16A 32* 48* 50B 0 80A 5i 0 inz

1 4 5 8

4b 0 4 8b 0 8

1 1 *

value value value

Pagina 66

c**--------------------------------------------------------------c** acquisisce i parametri con l'istruzione di select c**--------------------------------------------------------------c *entry plist c parm SqlStmt c**--------------------------------------------------------------c** dichiara il cursore c**--------------------------------------------------------------c/exec sql whenever sqlerror goto AnyError c/end-exec c/exec sql declare cSqlTest cursor for pSqlTest c/end-exec c/exec sql c+ prepare pSqlTest from :SqlStmt c/end-exec c**--------------------------------------------------------------c** descrive la SQLDA c**--------------------------------------------------------------c** alloca per 1 elemento c eval nSQLDA = 20 c eval szSQLDA = (nSQLDA * 80) + 16 c alloc szSQLDA pSQLDA c eval SQLN = nSQLDA c/exec sql describe pSqlTest into :SQLDA using LABELS c/end-exec c** alloca per gli elementi effettivi c if SQLN <= SQLD c eval nSQLDA = SQLD c eval szSQLDA = (nSQLDA * 80) + 16 c alloc szSQLDA pSQLDA c eval SQLN = nSQLDA c/exec sql describe pSqlTest into :SQLDA using LABELS c/end-exec c endif c**--------------------------------------------------------------c** reperisce i dati dei campi c**--------------------------------------------------------------c eval pSQLVAR = %addr(SQL_VAR) c eval SQLDATA = %addr(Record) c eval oh = 1 c c** c c** c c c** c c** c c** c c c c** c

for

i = 1 to SQLD

.. tipo Campo select .... Zoned/Packed when SQLTYPE = 484 or SQLTYPE = 488 move Precis aPrecis ...... + 1 per il segno eval cSQLLEN = pPrecis + 1 ...... + 1 per i punti separatori eval cSQLLEN = cSQLLEN + %int(pPrecis / 3,1) ...... + 1 per la virgola if Scale <> x'00' eval cSQLLEN = cSQLLEN + 1 endif ...... intestazione a Dx if cSQLLEN < SQLNAMELEN

Pagina 67

c eval c else c eval c c c endif c** .... Char c other c eval c** ...... intestazione a Sx c eval c endsl

%subst(Record: oh: SQLNAMELEN) = SQLNAME %subst(Record: oh + cSQLLEN - SQLNAMELEN - 1: SQLNAMELEN) = SQLNAME

cSQLLEN = SQLLEN %subst(Record: oh: SQLNAMELEN) = SQLNAME

c if cSQLLEN c eval od = oh c eval oh = oh c else c eval od = oh c eval oh = oh c endif c** dati c eval SQLDATA c** punta all'elemento successivo c eval pSQLVAR c endfor

< SQLNAMELEN + ((SQLNAMELEN - cSQLLEN) / 2) - 1 + SQLNAMELEN + 2 - 1 + cSQLLEN + 2

= %addr(Record) + od = pSQLVAR + 80

c** stampa l'intestazione c eval c except

R132 = %subst(Record:1:132)

Record = *blank

eval

c**--------------------------------------------------------------c** apre il file c**--------------------------------------------------------------c/exec sql c+ open cSqlTest c/end-exec c**--------------------------------------------------------------c** reperisce i dati dal file c**--------------------------------------------------------------c do *hival c/exec sql c+ fetch cSqlTest using descriptor :SQLDA c/end-exec c c c

if leave endif

sqlcod <> 0

c** Editazione dei campi numerici c** ... punta al primo elemento c eval pSQLVAR = %addr(SQL_VAR) c for i = 1 to SQLD c if SQLTYPE = 484 or SQLTYPE = 488 c callp Edita(Precis:Scale:SQLDATA) c endif c** ... punta all'elemento successivo c eval pSQLVAR = pSQLVAR + 80 c endfor

Pagina 68

c** Stampa la riga c c

eval except

enddo

AnyError

R132 = %subst(Record:1:132)

tag

c/exec sql whenever sqlerror continue c/end-exec c/exec sql c+ close cSqlTest c/end-exec c c

eval return

*inlr = *on

c**--------------------------------------------------------------c** Stampa prospetto c**--------------------------------------------------------------oqsysprt e o r132 p**--------------------------------------------------------------p** Editazione p**--------------------------------------------------------------P Edita D Edita D Precis D Scale D SQLDATA

B PI

d Cpybla d Receiver d Source d size

d §apier d §rr1 d §rr2 d §rr3

d d d d d d d d d

s s s s s s s s s

§edtcd §fillc §mask §mask£ §rcvv §rcvv£ §src §srcc §zerob

1 1 *

value value value

extproc('cpybla') * value * value 10i 0 value

1 5 9

4b 0 inz(15) 8b 0 inz(0) 15 1 1 256 4 256 4 25 10 1

based(SQLDATA)

c** Interi/Decimali c move Precis c move Scale c** Prepara la Maschera con l'edit code "J" c call 'QECCVTEC' c parm c parm c parm

aPrecis aScale

§mask §mask£ §rcvv£

Pagina 69

c parm §zerob c parm 'J' §edtcd c parm ' ' §fillc c parm pPrecis c parm pScale c parm §apier c** Edita il Campo c** ... Zoned/Packed c select c when SQLTYPE = 484 c eval §srcc = '*PACKED' c when SQLTYPE = 488 c eval §srcc = '*ZONED' c endsl c call 'QECEDT' c parm §rcvv c parm §rcvv£ c parm §src c parm '*PACKED' §srcc c parm pPrecis c parm §mask c parm §mask£ c parm ' ' §fillc c parm §apier c** Sostituisce il dato nel record con il campo editato c** ...... + 1 per il segno c eval cSQLLEN = pPrecis + 1 c** ...... + 1 per i punti separatori c eval cSQLLEN = cSQLLEN + %int(pPrecis / 3,1) c** ...... + 1 per la virgola c if pScale > 0 c eval cSQLLEN = cSQLLEN + 1 c endif c Callp Cpybla(SQLDATA:%addr(§rcvv):cSQLLEN) p Edita E

Call SQLDA 'select EMITT, NDESO, ICAPS, DIVIS from SOCIE00F where NDESO like "%FINANCE%" and ICAPS > 0' CODICE SOCIETA" 3.035 3.053 3.058 3.069 3.097 3.529 3.761 3.848 4.046 4.305 4.354 5.241 5.269 5.482 5.918

RAG'SOC FINANCE ONE PUBLIC CO LTD TTB FINANCE CAYMAN LTD IBM INTERNATIONAL FINANCE NV LUKINTER FINANCE BV SANWA INTERNATIONAL FINANCE TRUST DAIEI FINANCE MAYNE NICKLESS FINANCE LTD GOLDEN WEST FINANCE INC DHANA SIAM FINANCE SECURITIES NATIONAL FINANCE PUBLIC CO LTD DC FINANCE HOLDINGS LTD INDUSTRIAL FINANCE SANYO SHINPAN FINANCE CO LTD KIATNAKIN FINANCE PLC HOUSING DEVELOPMENT FINANCE

CAPITALE SOCIALE 1.310.700.000,00 10.000.000,00 100.000.000.000,00 10.000.000.000,00 150.000.000.000,00 35.700.000.000,00 349.464.000,00 6.390.000,00 5.279.420.000,00 148.709.900.000,00 251.257.658,00 8.216.240.000,00 1.954.250.000,00 2.537.140.000,00 1.191.140.000,00

CODICE DIVISA THB USD USD USD JPY JPY AUD USD THB THB HKD THB JPY THB INR

Pagina 70

10.4 – SQL dinamico - Applicazioni SQL2XML SQLSTMT

> 'select EMITT as [Codice], NDESO as [RagioneSociale], ICAPS as [CapitaleSociale], DIVIS as [Divisa] from SOCIE00F where NDESO like "%FINANCE%" and ICAPS > 0' TOXML > '/costaweb/Emittenti.xml' ITEMS > 'Emittenti/Emittente' STYLESHEET > 'Emittenti.xsl'

<?xml version="1.0"?> <?xml-stylesheet type="text/xsl" href="Emittenti.xsl"?> <Emittenti> <Emittente> <Codice> FINANCE</Codice> <RagioneSociale>FINANCE</RagioneSociale> <CapitaleSociale> 113.646.875.000,00</CapitaleSociale> <Divisa></Divisa> </Emittente> <Emittente> <Codice> IBM INT</Codice> <RagioneSociale>IBM INTERNATIONAL FINANCE NV</RagioneSociale> <CapitaleSociale> 100.000.000.000,00</CapitaleSociale> <Divisa>USD</Divisa> </Emittente> . . . </Emittenti>

<?xml version="1.0"?> <xsl:template xmlns:xsl="http://www.w3.org/TR/WD-xsl"> <html> <body> <xsl:for-each select="Emittenti/Emittente"> <div> Codice: <xsl:value-of select="Codice" /> Ragione Sociale: <xsl:value-of select="RagioneSociale" /> Capitale Sociale: <xsl:value-of select="CapitaleSociale" /> Divisa: <xsl:value-of select="Divisa" /> <hr /> </div> </xsl:for-each> <A HREF="javascript:void(null)" onclick="javascript:window.close()">chiudi</A> </body> </html> </xsl:template>

Pagina 71

SQL2XLS SQLSTMT > 'select EMITT as [Codice], NDESO as [RagioneSociale], ICAPS as [Cap.Sociale], DIVIS as [Divisa] from SOCIE00F where NDESO like "%FINANCE%" and ICAPS > 0' TOXLS > '/costaweb/Emittenti.xls' HEADERS > *LABELS

SQL dinamico – JAVA ////////////////////////////////////////////////////////////////////////////////// // utilizzo: java JSQL2XLS "select . . . from . . . " ////////////////////////////////////////////////////////////////////////////////// import java.sql.*; import java.io.IOException; import java.io.FileOutputStream; import import import import

org.apache.poi.hssf.usermodel.HSSFCell; org.apache.poi.hssf.usermodel.HSSFRow; org.apache.poi.hssf.usermodel.HSSFSheet; org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class JSQL2XLS { public static void main (String[] parameters) { int rowNum = 0; short colNum = 0; String SQLstatement = parameters[0]; Connection connection = null; try { // Carica il driver Java JDBC Class.forName("com.ibm.db2.jdbc.app.DB2Driver"); //Class.forName("com.ibm.as400.access.AS400JDBCDriver");

// nativo // toolbox

// connette al database connection = DriverManager.getConnection

Pagina 72

("jdbc:db2://localhost" , "user", "password"); //("jdbc:as400://localhost" , "user", "password");

// nativo // toolbox

DatabaseMetaData dmd = connection.getMetaData (); // exegue la query. Statement select = connection.createStatement (); ResultSet rs = select.executeQuery (SQLstatement); // crea la cartella e il foglio Excel HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); // dichiara riferimenti a riga/cella HSSFRow row = null; HSSFCell cell = null; // reperisce informazioni sul resultset ResultSetMetaData rsmd = rs.getMetaData (); int columnCount = rsmd.getColumnCount (); String[] columnLabels = new String[columnCount]; // crea le intestazioni colonna row = sheet.createRow(rowNum++); // crea la riga for (colNum = (short) 0; colNum < columnCount; colNum++) { cell = row.createCell(colNum); // crea la cella String label = rsmd.getColumnLabel(colNum+1); cell.setCellValue(label); } // reperisce i records while (rs.next ()) { row = sheet.createRow(rowNum++); // crea la riga for (colNum = (short) 0; colNum < columnCount; colNum++) { cell = row.createCell(colNum); // crea la cella int colType = rsmd.getColumnType (colNum+1); // reperisce il tipo dati if (colType == 3) { // ..numerico double dvalue = rs.getDouble (colNum+1); if (rs.wasNull ()) { String nvalue = "<null>"; cell.setCellValue(nvalue); } else { cell.setCellValue(dvalue); } } else { // ..carattere String avalue = rs.getString (colNum+1); if (rs.wasNull ()) avalue = "<null>"; cell.setCellValue(avalue); } } } // scrive il foglio .xls FileOutputStream fileOut = new FileOutputStream("excel/jtest.xls"); wb.write(fileOut); fileOut.close(); } catch (Exception e) { System.out.println (); System.out.println ("ERROR: " + e.getMessage()); } finally { // Clean up try { if (connection != null) connection.close (); } catch (SQLException e) { // Ignora } } System.exit (0); } }

Passi di installazione/esecuzione export CLASSPATH=$CLASSPATH:/myJava/:myJava/lib/db2_classes.jar:excel/poi-2.0.jar: javac /myJava/JSQL2XLS.java java JSQL2XLS "select * from MIOFILE" ===> RUNJVA CLASS(JSQL2XLS) PARM('select * from MIOFILE) CLASSPATH('/myJava/lib/db2_classes.jar:excel/poi-2.0.jar')

Pagina 73

10.5 – Triggers Triggers SQL Create Trigger TrgMovBancari1025 After insert on MovimentiBancari Referencing New As n For Each Row Mode DB2Row WHEN (n.CdBanca = 1025) INSERT INTO MovimentiSanPaolo VALUES(n.DataMovimento, n.ImportoMovimento); Create Trigger Libreria.QtzBfrTrg Before Insert On Libreria.Quotazioni Referencing New As NewRow For Each Row Mode DB2Row Begin If NewRow.Quotazione < 0 Then SIGNAL SQLSTATE Value 'US099'; End If; End;

Insert Into Libreria.Quotazioni Values( ‘FIAT’, ‘2001-11-20’, -100,75 ) [SQL0723] SQL trigger QTZBFRTRG in LIBRERIA failed with SQLCODE -438 SQLSTATE US099

Pagina 74

10.6 – Varie funzionalità recenti Debug SET OPTION DBGVIEW = *SOURCE Debug con le istruzioni originali SQL - Disponibile con PTF anche a V5R1

Espressioni in modelli LIKE Select CognomeNome, Indirizzo From Dipendenti Where CognomeNome LIKE ‘%’ CONCAT :NOME CONCAT ’%’

Union in View CREATE VIEW with UNION

DRDA Result Sets SQL Query Engine • • • • •

Query Dispatcher Query Optimizer Data Access Primitives Plan Cache Statistics Manager SQE non supportato se: • > 1 tabella • predicati IN & OR • SMP attivo • Non-Read • Predicati LIKE • UNIONS • Common table expression • LOB

SPL Language •

Nested blocks

Pagina 75

10.7 – CIFRATURA DATI Queste funzioni consento di proteggere i dati da qualunque forma di visualizazzione. E’ necessario installare il prodotto 5722AC3 Cryptographic Access Provider 128-bit for AS/400. Sommario delle funzioni: •

ENCRYPT_RC2 accetta una stringa origine, una password di cifratura, una password reminder (opzionale) e ritorna una stringa cifrata con l’algoritmo RC2 (http://rfc.net/rfc2268.html) Insert Into Dipendenti (Codice, Nome, Password) Values(123, ‘ROSSI GIUSEPPE’, /* Inserisce la password in formato cifrato */ Encrypt_RC2('Segretissima','PwdEncript','PwdRemind'))

Notare che I dati, la password e il suggerimento sono memorizzati nella stringa risultante. LA password può essere lunga da 6 a 127 bytes e il suggerimento fino a 32 bytes. Consultare il manuare di riferimento http://publib.boulder.ibm.com/infocenter/iseries/v5r3/ic2924/info/db2/rbafzmst.pdf per calcolare la lunghezza finale della stringa. •

DECRYPT_CHAR accetta una stringa cifrata e restituisce la stringa in chiaro: Select Decrypt_Char(Password,'PwdEncrypt') From Dipendenti Where Codice = 123 /* Il risultato sarà : ‘Segretissima’ */

•

Sono anche disponibile le seguenti funzioni: Decrypt_Bit, Decrypt_Binary, e Decrypt_DB

•

Se a programma si intende usare una password valida per tutte le operazioni di cifratura è possibile impostarla con Set Encryption Password = 'PwdEncrypt'

E pertanto può essere omessa nelle successive richieste di cifratura o decifratura. Select Decrypt_Char(Password) From Dipendenti Where Codice = 123 /* La password è quella impostata dalla SET ENCRYPTION PASSWORD */

•

GETHINT serve per reperire il suggerimento a ricordarsi la password. Il parametro richiesto è il nome del campo cifrato: Select GetHint(Password) From Dipendenti Where Codice = 123 /* IN questo caso la funzione ritorna ‘PwdReminder’ */

Questa funzione è interessante per consentire all’utente di memorizzarsi I propri dati sensibili impostandosi la password e un campo aggiuntivo per aiutarlo a ricordarsela.

Pagina 76

10.8 – Nuove funzionalità a V5R3 •

DAYNAME accetta una data o un timestamp (o una rappresentazione in formato carattere di una data) e ritorna in formato mixed-case il giorno della settimana in un campo Varchar(100): Select DayName(Current_Date) /* Se la data corrente è il 9 giugno 2006 Viene restituito ‘Domenica' */ From SysIbm.SysDummy1

•

MONTHNAME accetta una data o un timestamp (o una rappresentazione in formato carattere di una data) e ritorna in formato mixed-case il nome del mese in un campo Varchar(100): Select MonthName(Current_Date) /* Se la data corrente è il 9 giugno 2006 Viene restituito ‘Giugno' */ From SysIbm.SysDummy1

•

INSERT è simile alla BIF %Replace dell’ RPGIV e consente di inserire una stringa in una stringa origine a una posizione fissa; in aggiunta è possibile rimuovere dei caratteri prima dell’inserimento; occorrono 4 parametri: SOURCE STRING , START IN SOURCE , LENGTH TO REMOVE, e INSERT-STRING Select Insert('&1',4,2,'My Address') From SysIbm.SysDummy1 /* La stringa risultante è: My Address */

•

REPEAT è utile per riempire una stringa con caratteri diversi da blank (padding) /* Pad Description con binary zeros */ Select Left(RTrim(Descriz)|| Repeat(X'00',30),30) From Articoli

•

RIGHT funziona come la LEFT e ritorna il numero di caratteri richiesti a partire dalla fine della stringa: /* Se NumTel contiene 011/748957 Questa istruzione ritorna gli ultimi 6 numeri: 748957 */ Select Right(NumTel, Length(NumTel)-Posstr(NumTel,' ')) From RubricaTel

•

REPLACE accetta una stringa origine, un pattern di ricercae una stringa di sostituzione e ritorna la stringa modificata: /* Remove la barra dal numero ti telefono, pertanto 011/748957 diventa 011748957 */ Select Int(Replace(NumTel,'/','')) From SysIbm.SysDummy1

Pagina 77

10.9 – Nuove funzionalità a V5R4 •

La sintassi SQL soddifa i “Core requirements” per lo standard SQL 2003. Questi standards possono essere consultati a questo indirizzo http://www.sigmod.org/sigmod/record/issues/0403/E.JimAndrew-standard.pdf

•

Il nome delle colonne è stato aumentato da 30 a 128 caratteri.

•

Il massimo numero di tabelle referenziate in una istruzion e SQL è aumentato da 256 a 1.000. Tuttavia il massimo numero di tabelle referenziate in una vista è 256.

•

La lunghezza massima di una istruzione sql è stata aumentata da 65 KB a 2 MB.

•

L’ampiezza massima per i large objects (BLOBs, CLOBs, DBCLOBs) resta al momento di 2GB.

•

Embedded SQL in formato libero (/FREE) • L’istruzione SQL deve iniziare con EXEC SQL (su una sola linea) • L’istruzione può essere suddivisa su più linee senza caratteri di contizuazione • L’istruzione deve essere terminata da punto e virgola (come le altre /free) Esempio: EXEC SQL Select Nome Into :Nome Where Codice = :Codice;

•

CREATE INDEX consente di specificare dle dimensioni della pagina indice. CREATE INDEX ORDINIX1 ON ORDINI (DATAORD,TIPOORD,NUMEROORD) PAGESIZE(128)

•

I campi esadecimali possono contenere degli spazi fra i caratteri Select x'F2

F5' From SysIBM.SysDummy1

Restituisce: '2345' •

I timestamp possono essere immessi nel formato “yyyy-mm-dd hh:mm:ss.nnnnnn” e i microsecondi possono essere omessi ( il formato precedente era “yyyy-mm-dd-hh.mm.ss.nnnnnn”)

Select TimeStamp('2005-12-25 12:00:00’) As BuonNatale From SysDummy1 •

La clausa WHERE può essere espressa su un gruppo di colonne con un corrispondente gruppo di valori:

Select * From Dipendenti Where (MANAGER, SEDE) = ('M103','TO') •

La stessa regola funziona per i sub-query:

Select * From Dipendenti Where (MANAGER, SEDE) In (Select MANAGER, SEDE From Progetti Where StatoProgetto ='C')

Pagina 78

•

La “fullselect” (istruzioni multiple combinate con una UNION) può esser usata nei sub-query: In questo esempio si vuole ottenere una lista degli impiegati che non hanno ancora immesso una foto o un curriculum nel database:

Select * From Dipendenti Where Codice Not In /* Questa sub-query è una FULLSELECT */ (Select Codice From Fotografie Union Select Codice From Curricula) •

La CREATE TABLE adesso ha la parola chiave RCDFMT che consente di usare la tabella in RPG senza fare la rename:

Create Table Dipendenti (Codice char(5), …) RcdFmt RDipendent •

E’ possibile specificare la Label anche sugli indici (senza quindi essere più obbligati ad utilizzare una CHGOBJD)

Label On Index DIPENDENX1 Is 'Dipendenti x Codice’

•

E’ possibile specificare il grado di processi paralleli direttamente nella procedura SQL SET CURRENT DEGRE. Precedentemente era possibile solo impostando il parametro PARALLEL_DEGREE nel file delle opzioni QAQQINI

Pagina 79