DB2 for i & SQL – FAQ & Howto (Part. 1) (IT)

An english version of this post here!

La seconda parte di queste FAQ & Hotwos qui: DB2 for i & SQL – FAQ & Howto (Part.2) (IT)

Introduzione

Questo articolo non vuole essere un manuale del DB2 for i ma semplicemente una raccolta di domande e risposte, curiosità, trucchi e anche molte cose note ai più su SQL e sul DB2 for i in generale: credo che ognuno di noi che lavora con IBM i troverà qualcosa che non conosce o semplicemente qualcosa che non ha mai approfondito, da SQL alla gestione propria del DB2 for i … un grande motore di databese parte del sistema operativo IBM i.

La nostra intenzione è di tenere aggiornato questo post per farlo diventare un vero e proprio punto di riferimento dove andare a cercare qualche breve risposta: sono bene accetti suggerimenti di curiosità, trucchi ecc… utilizzate pure i commenti del Blog e facciamola diventare insieme una vera e propria Guida a SQL e DB2 for i.

Tabelle di esempio

Tutti gli esempi qui riportati si basano su un Database di esempio disponibile in qualsiasi partizione di IBM i, “incorporato” nel Database… e recuperabile in una classica libreria (che da un punto di vista SQL si chiama SCHEMA!) richiamando una “Stored Procedure” da ambiente SQL-Script (SQL Script di Access Client Solution ACS, quello di Rational Rdi o anche semplicemente facendo STRSQL). 

(STRSQL o finestra SQL Script)

-- Creiamo una libreria (schema) SAMPLEDB richiamando questa Stored Procedure!

CALL QSYS.CREATE_SQL_SAMPLE(‘SAMPLEDB’)

-- Facciamo un piccolo test

SELECT * FROM sampledb.employee;

iDB2-FAQ-001: Come faccio a cercare % Percentuale o _ Underscore con SQL LIKE?

Tutti conosciamo l’uso di LIKE per la selezione dei record di una tabella ma forse non tutti ne conoscono le sue sfumature. Facciamo qualche esempio sulla tabella SAMPLEDB.EMPLOYEE

• Uso standard di LIKE: tutti i dipendenti che iniziano con “JOHN”
  • Select * from sampledb.employee where lastname like ‘JOHN%’;
• Tutti i dipendenti che finiscono con “SON”:
  • Select * from sampledb.employee where lastname like ‘%SON’;
• Tutti i dipendenti con nome ROBERTO o ROBERTA
  • Select * from sampledb.employee where upper(trim(firstnme)) LIKE ‘ROBERT_’;
• I caratteri % (percentuale) e _ (underscore) hanno un significato ben preciso per predicato Like … se volessimo cercare tutti i record con un simbolo percentuale o underscore in una colonna dobbiamo quindi ricorrere ad una carattere di escape : vediamo in questo caso una ricerca di tutti i records con un uderscore dentro il cognome: definiamo quindi un carattere di ecape nei parametri aggiuntivi della funzione LIKE… quindi caso per caso usiamo e definiamo il carattere di escape:
  • Select * from sampledb.employee where lastname like ‘%!_%’ escape ‘!’;

iDB2-FAQ-002: Esiste qualcosa con %SCANRPL (RPG) in SQL? (REPLACE)

La funzione REPLACE di SQL è un po’ come la BIF %SCANRPL in RPG: permette di sostituire parte di una stringa occupandosi di allargare o stringere gli spazi, se necessario.

Per chi ancora ha delle partizioni IBM i con vecchie versioni di sistema operativo, prima della 7.1 ad esempio (ma faccio presente che sono passati almeno 15 anni … cosa aspettate?), potrebbe essere anche utile in SQL embedded in RPG mancando appunto la BIF %SCANRPL in quelle versioni:

Esempio 1: sostituiamo la parte di stringa equivalente a :old con :new dentro la stringa :text

dcl-s text varchar(50) inz('My original string');
dcl-s old  varchar(30) inz('original');
dcl-s new  varchar(30) inz('new');
exec sql
   set :text = replace(:text, :old, :new);

dsply text;
  // text--> 'My new string'

Naturalmnete l’utilizzo più normale di REPLACE è direttamente in SQL … come in questo esempio dove ho dovuto sostituire HTTP:// con HTTPS:// in una tabella con dei link al tracking di alcune spedizioni di un vettore:

update riespe0f set rslinkwb= replace(rslinkwb, 'http://', 'https://')
 where rsanndoc=2019 and rslinkwb like 'http://as777.brt.it%'

Se vuoi approfondire REPLACE e %SCANRPL c’è un ottimo post di Simon Hutchinson su RPGPGM.COM: https://www.rpgpgm.com/2014/12/replacing-text-in-string.html

iDB2-FAQ-003: Manipolazione stringhe con SQL (POSSTR, LOCATE, LOCATE_IN_STRING)

Restando nel tema della manipolazione delle stringhe con SQL abbiamo scritto un articolo apposito per le funzioni POSSTR (posizione dentro una stringa), LOCATE e LOCATE_IN_STRING, sempre per il posizionamento dentro le stringhe ma con una sintassi leggermente differente:

vedi articolo : https://blog.faq400.com/database-db2-for-i/db2-for-i-sql-manipolazione-stringhe/

iDB2-FAQ-004: Come recupre il numero relativo di record in SQL? (RRN)

E’ possibile anche ottenere il numero relativo di record con un’istruzione SQL: 
SELECT RRN(nomefile) 
FROM nomefile 
WHERE campo = ‘valore’ 

iDB2-FAQ-005: DATE and TIME in SQL

Current date e Current Timestamp

select current timestamp as Timestamp_attuale,
        current date as Data_attuale,
        year(current date) as Anno,
        month(current date) as Mese,
        day(current date) as Giorno,
        hour(current timestamp) as Ora,
        minute(current timestamp) as Minuti,
        second(current timestamp) as Secondi,
        microsecond(current timestamp) as Microsecondi
 from sysibm.sysdummy1;

Differenza in giorni tra le date

select days(current date) - days(date('2019-01-01') ) as Giorni_da_inizio_anno
 from sysibm.sysdummy1;

Estrarre informazioni da una data:

select dayname(current date) as DayName,
        dayofweek(current date) as DayOfWeek,
        dayofweek_iso(current date) as DayOfWeekIso,
        dayofyear(current date) as DayOfYear,
        monthname(current date) as MonthName,
       midnight_seconds(current timestamp) as Midnight_seconds
 from sysibm.sysdummy1;

Approfondimenti sulla gestione date e orari con SQL per DB2 for i sono disponibili ai seguenti link:

• IBM Developer: Fun with date – https://developer.ibm.com/articles/fun-with-dates-and-times/

iDB2-FAQ-006: Ho qualche problema con le divisioni e gli arrotondamenti in SQL

Quando abbiamo a che fare con l’operazione “diviso” “/” in SQL dobbiamo prestare attenzione alla precisione decimale per non trovarci dei risultati poco precisi … soprattutto nel caso in cui usiamo nella funzione dei numeri costanti.

Nel caso che vediamo qui sotto, ripreso dal forum it.comp.as400 italiano, ci si lamentava del fatto che nel caso 1 il risultato tornasse senza decimali … per risolvere bastava indicare un la costante con un numero di decimale appropriato:

create or replace table qtemp.b13
  (b13cod char(10),
   b13id1 decimal(17, 3),
   b13id2 decimal(17, 3),
   b13id3 decimal(17, 3),
   b13id4 decimal(17, 3),
   b13nd1 decimal(21, 3),
   b13nd2 decimal(21, 3),
   b13nd3 decimal(21, 3),
   b13nd4 decimal(21, 3));
 insert into qtemp.b13
   values('A001', 10.123 , 20.345, 30.678, 40.900, 50.123, 60.456, 70.789, 80.900),
   ('A002', 10, 20, 30, 40, 50, 60, 70, 80);

-- CASO 1 ... indicando la costante 3650 come intera ... il risultato è un intero
  SELECT B13COD, SUM(B13ID1 + B13ID2 + B13ID3 + B13ID4) * 36500 / SUM(B13ND1 + B13ND2) as B13TIM FROM B13 group by B13COD 

-- CASO 2 .... se voglio 3 decimali
 
 SELECT B13COD, SUM(B13ID1 + B13ID2 + B13ID3 + B13ID4) * 36500.000 / SUM(B13ND1 + B13ND2) as B13TIM FROM B13 group by B13COD 

-- CASO 3 ... se voglio 5 decimali

 SELECT B13COD, SUM(B13ID1 + B13ID2 + B13ID3 + B13ID4) * 36500.00000 / SUM(B13ND1 + B13ND2) as B13TIM FROM B13 group by B13COD

La regola dell’arrotondamento dei decimali nelle divisini non è semplice … per chi ha voglia di studiarla, e poi spiegarla anche a noi, i link è il seguente: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_decimaldivision.html

iDB2-FAQ-007: Generated Always Column, fammi capire!

Le colonne “Generated Always” sono molto utili per tutte quelle funzioni di audit sulle tabelle … ad esempio per registrare il timestamp dell’utlima modifica, dell’ultimo utente ecc.

Abbiamo dedicato un articolo specifico sul nostro Blog (Generated Always Columns – HowTo) su questo argomento e vi invito a leggere lì gli approfondimenti.

iDB2-FAQ-008: Tabelle e campi con nomi lunghi … ma poi come le vedo?

Finalmente con le DDL di SQL possiamo creare delle tabelle con momi lunghi e significativi con colonne anch’esse con nomi lunghi. Bisogna fare un po’ di accortezza perché da alcune parti del sistema operativo vengono ancoa solo visualizzati oggetti con massimo 10 char.

Il nostro consiglio è di utilizzare sempre dei nomi lunghi ma di definire anche un nome corto con un certo senso … vediamo nel seguente esempio la creazione di una tabella con un nome molto lungo e il suo nome breve (definito da “for system name”) … e con una colonna dal nome lungo con la sua versinoe breve (definito da “for column”):

Create table faq400.MyTableWithALongName for system name mytabshort (
 id      Integer     Generated always 
                     as Identity  primary key,
 MyLongColumnName  for column MyShortCol char(100)   not NULL default '',
 LastUpd         Timestamp Generated always for each row on update
                    as row change Timestamp
                    not NULL) ;

In SQL non ci sono problemi: possiamo riferirci alla tabella e alle colonne indipendentemente con il nome lungo o quello corto:

insert into faq400.MyTableWithALongName 
 (MyLongColumnName)
 values('Esempio01');

 insert into faq400.MyTabShort 
 (MyShortCol)
 values('Esempio02');

 select * from faq400.MyTableWithALongName;

In RPG invece dobbiamo utilizzare i nomi corti sia di colonna che di tabella oppure indicare ALIAS e LIKEREC per utilizzare i nomi lunghi di tabella e di colonne nel nostro codice:

  //***********************************************************
  //  F4LONGNAME: How to deal with long tables name and
  //              long columns name:
  //
  //**********************************************************
   ctl-opt option(*srcstmt:*nodebugio);

   DCL-F MyTableWithALongName usage(*update)
         ExtDesc('MYTABSHORT') ExtFile(*ExtDesc)
                       Keyed alias;
   id=1;

   Chain (id)  MyTableWithALongName;

   MyLongColumnName='Modified by RPG';

   update MyTabShort; // Record Name ... max 10 char!

   *inlr=*on;

Referenze:

• Ottimo post di Simon Hutchinson su RPGPGM.COM: “Create SQL tables, views, and indexes with long names“

iDB2-FAQ-009: Come trovo le tabelle con una determinata colonna?

Tabelle, campi, views, index, procedure, funcion, alias e altro ancora vengono tutti registrati nel SYSTEM CATALOG e con SQL possiamo recuperare interessanti informazioni di questi oggetti del nostro DB2 for i:

In questo esempio cerchiamo tutti i campi “id” (sia nome di campo che column heading):

 SELECT TABLE_NAME, SYSTEM_TABLE_NAME, COLUMN_NAME, SYSTEM_COLUMN_NAME, COLUMN_HEADING
       FROM QSYS2.SYSCOLUMNS
       WHERE 
       (upper(COLUMN_HEADING) like '%BIRTH%' 
 or     upper(COLUMN_NAME) like '%BIRTH%' )      
         and    TABLE_SCHEMA = 'YOURLIB'; 

iDB2-FAQ-010: Estrarre la data da un campo formattato hhmmssmmddccyy

Quando ho un formato di data alfanumerico/numerico non in standard SQL e voglio estrarne la parte data nel formato corretto SQL per poter averne tutti i vantaggi dei campi data è possibile utilizzare due tecniche:

• 1 – Utilizzare l’utility UDF iDate, scaricabile da sito Think400.dk con la quale è possiamo estrarre la parte data:

select lbtime,  Date(Timestamp_Format(Digits(lbtime    ),
'HH24MISSMMDDYYYY'))
from ptilb00

• 2 – Utilizzare il Timestamp_format di SQL

select lbtime, Date(Timestamp_Format(Digits(lbtime), 'HH24MISSMMDDYYYY'))
 from ptilb00