SQL and DB2 for i – FAQ & Howtos – (Parte 5 IT)

Last Updated on 20 Aprile 2021 by Roberto De Pedrini

Eccoci alla quarta parte di questo “FAQ & Howtos” per SQL e DB2 for i … le precedenti 4 “puntate” di queste Faq le potete trovare in fondo a questo post negli “Articoli Corretali” … ma veniamo alle nuove FAQ:

iDB2-FAQ-038 – Come leggere un Membo Sorgente con SQL (Birgitta Hauser’s UDTF)

Se dobbiamo leggere un membro sorgente con SQL dobbiamo creare un ALIAS per accedere a quel determinato membro di un source-file … con questa UDTF User Defined Table Function di Birgitta Hauser, leggere un membro sorgente è un gioco da ragazzi:

How to read source files with SQL

iDB2-FAQ-039 – DDM per gestire File e Tabelle di un altro Sistema IBM i (o comunque server compatibile DRDA)

Con le DDM possiamo leggere un file di database remoto residente, ad esempio, su un altro IBM i visibile in rete: riporto qui i passi da eseguire indicati sul post Embedded SQL to a DDM File di Midrange.com:

Creare una entry nella WRKRDBDIRE per “puntare” al sistema remoto

Start with WRKRDBDIRE and get the remote system set up:

Display Relational Database Entry Detail

Relational database  . . . . . . :   S102867C
   Relational database alias  . . :   DEV
Remote location:
   Remote location  . . . . . . . :   S102867C
     Type . . . . . . . . . . . . :   *IP
   Port number or service name  . :   *DRDA
   Remote authentication method:
     Preferred method . . . . . . :   *USRENCPWD
     Allow lower authentication . :   *ALWLOWER
   Secure connection  . . . . . . :   *NONE
   Encryption algorithm . . . . . :   *AES
Text . . . . . . . . . . . . . . :
Relational database type . . . . :   *REMOTE

Quindi creare un DDM file con l’apposito comando …

CRTDDMF FILE(PDOW/DDMF) RMTFILE(PDOF/SOURCE) RMTLOCNAME(*RDB) RDB(S102867C
)   


                         Display Details of DDM File

Local file:
File . . . . . . . . . . . . . . . . . . . . . . :   DDMF
   Library  . . . . . . . . . . . . . . . . . . . :     PDOW

Remote file  . . . . . . . . . . . . . . . . . . :   PDOW/SOURCE

Remote location:
   Name or address  . . . . . . . . . . . . . . . :   *RDB
     Relational Data Base . . . . . . . . . . . . :   DEV

Access method:
   Remote file attribute  . . . . . . . . . . . . :   *RMTFILE
   Local access method  . . . . . . . . . . . . . :

Share open data path . . . . . . . . . . . . . . :   *NO
Protected conversation . . . . . . . . . . . . . :   *NO
Check record format level ID . . . . . . . . . . :   *RMTFILE
Text . . . . . . . . . . . . . . . . . . . . . . :

Quindi creare una Server Authorization Entry

CHGSVRAUTE USRPRF(PDOW) SERVER(DEV) USRID(PDOW)PASSWORD(yourpswd)

Quindi interrogare il remote DB

select * from pdow/ddmf

iDB2-FAQ-039 – Salvare le password in una tabella DB2?

Se vogliamo creare un servizio di autenticazione e gestire le password degli utenti è sempre preferibile archiviarle in modo criptato, in un post di Midrange.com (How to validate passwords without storing them anywhere) abbiamo trovato qualche suggerimento interessante:

Creiamo la nostra tabella, prestando attenzione al campo password_encryption … che è un varchar “for bit data”

CREATE OR REPLACE TABLE faq400.Security_table 
(
    Security_id varchar(100) ALLOCATE(10) not null constraint Security_table_primary_key PRIMARY KEY,
    Security_name varchar(100) ALLOCATE(25) not null,
    Password_Encryption varchar(256) FOR BIT DATA
    );

Invece di memorizzare le password degli utenti … usiamo la password “ricevuta” come password “di cript” di una parola chiave nota … in questo caso “VALID”,

insert into faq400.Security_table (
    Security_id, Security_name, Password_Encryption)
    Values('ROB', 'Rob Berendt', ENCRYPT_TDES(varchar('VALID'), 'Budweiser#01'));  
    

Per curiosità possiamo anche cercare di vedere cosa è stato salvato in tabella… ma sono dati criptati!

-- Let me check if I can see the password
select * from faq400.Security_table;

-- Let me try another way
select security_id, Security_name, cast(Password_Encryption as Varchar(30)) as Pwd
from faq400.Security_table;

Per controllare se la password utente è corretta … possiamo usare questi due SQL Statement:

-- Ok, now we can check passed password
Select DECRYPT_CHAR(Password_Encryption, 'Budweiser#01')
from faq400.Security_table
where Security_id = 'ROB';

-- or
Select CASE WHEN   DECRYPT_CHAR(Password_Encryption, 'Budweiser#01') =
'VALID'
  THEN 'VALID' ELSE 'INVALID' END
  from faq400.Security_table; 
   

iDB2-FAQ-040 Trigger di una tabella … nella stessa libreria (schema) dei dati o in quella dei programmi?

Se ne è discusso sul Midrange.com (Where to put a trigger program), un programma di trigger va messo nella stessa libreria dei dati dove c’è la tabella (o physical file) o in quella dei programmi?

La risposta è… nella stessa libreria dei dati … come tutti gli oggetti di tipo database, views, indexes ecc … per tutti i vantaggi in casi di save-restore della libreria (al restore non serve neppure ricollegare il trigger alla tabella!) … anche IBM infatti consiglia di usare la stessa libreria: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/dbp/rbaforzahftrg.htm

iDB2-FAQ-041 Usare QSYS2.SYSPARTITIONSTAT per controllare dimensioni e numero di righe dei Membri Sorgenti

Se vogliamo controllare la dimensione (o il numero di righe) dei nostri sorgenti … o anche semplicemente per verificare l’occupazione dei file sorgenti nelle nostre librerie, possiamo interrogare la QSYS2.SYSPARTITIONSTAT per ottenere un lista ordina, ad esempio, per dimensione discendente:

SELECT SYSTEM_TABLE_SCHEMA,
       SYSTEM_TABLE_NAME,
       SYSTEM_TABLE_MEMBER,
       PARTITION_TEXT,
       DATA_SIZE, 
       number_rows,
       create_timestamp,
       last_change_timestamp
       
    FROM qsys2.SYSPARTITIONSTAT a
    WHERE TABLE_SCHEMA = 'FAQ400'
          AND TABLE_NAME = 'QRPGLESRC'
          order by data_size desc;

iDB2-FAQ-042 Un uso particolare delle OLAP functions SQL per ordinare – numerare un elenco

E’ una richiesta particolare apparsa sulla mailing list di Midrange.com: vorrei raggruppare un elenco di ordini-clienti creando almeno un dossier per ogni cliente o gruppo di ordini per un massimo di 5 ordini, esempio:

How can I get from this :
Client A  Order x
Client A  Order z
Client A  Order a
Client B  Order g
Client B  Order h
Client B  Order i
Client B  Order l
Client B  Order p
Client B  Order m

To this:
Client    Order   Dossier Seq
Client A  Order x  1       1
Client A  Order z  1       2
Client A  Order a  1       3
Client B  Order g  2       1
Client B  Order h  2       2
Client B  Order i  2       3
Client B  Order l  2       4
Client B  Order p  2       5
Client B  Order m  3       1

Usando le funzioni OLAP DENSE_RANK e ROW_NUMBER possiamo ottenere l’elenco con i Dossier e le Sequenze interne ad ogni dossier:

with numbered
 as (select customer, ordernr, row_number() OVER (partition by customer order by ordernr)
as rowNbr
     , dense_rank() OVER (order by customer) grpNbr
     from qtemp.orders)
select customer, ordernr,
  dense_rank() OVER (order by grpNbr,  ceiling(rownbr / 5.00)) as Dossier,
  row_number() OVER (partition by grpNbr,  ceiling(rownbr / 5.00) order by grpNbr,  ceiling(rownbr / 5.00)) as DossierSeq
from numbered;   

iDB2-FAQ-043 Uso di Index SQL come se fossero dei Logical Files

Siamo abituati ad utilizzare i Logical Files con l’I/O nativo di RPG (chain, setll, read ecc) avendo a disposizione tutti i campi del Phisical File. Se cerchiamo di fare lo stesso con un Index SQL ed utilizzarlo in RPG noteremo che, a differenza dei logical file, non vengono “riportati” tutti i campi della tabella ma solo quelli della chiave.

Possiamo crea un Index con le colonne che ci interessano o con tutte le colonne della tabella origine… indicando ADD col1… oppure ADD ALL COLUMNS. Per utilizzarlo con l’I/O Nativo ricordiamoci di aggiungere anche la specifica RCDFMT

CREATE INDEX IIML01 ON IIM (IPROD)
WHERE IID = 'IM'
RCDFMT IPI100IM
ADD IDESC, IADJ, IRCT, IISS

oppure

CREATE INDEX IIML01 ON IIM (IPROD)
WHERE IID = 'IM'
RCDFMT IPI100IM
ADD ALL COLUMNS

iDB2-FAQ-044 Date numeriche YYMD – Trasformazione in campo data – Prestazioni

E’ tipico di diversi ERP Italiani e non solo, avere le date nel formato numerico YYMD (o, peggio, YMD di lunghezza 6) salvate nelle proprie tabelle e demandare a codice RPG o Cobol il controllo, la validazione, l’estrazione e il raffronto dei dati per periodo. I vantaggi di avere, invece, un campo data effettivo nelle nostra tabelle, li conosciamo tutti … dalla data possiamo estrarre tutte le componenti (mese, anno, trimestre, ultimi x giorni ecc.) o confrontare periodi di anni differenti, in modo semplice e tutto con SQL. Anche solo la presentazione di un dataset di estrazione, direttamente in SQL o estratto in Excel o in formato report di testo … il campo data risulta sempre più semplice da gestire.

Senza pensare ad un vero e proprio processo di modernizzazione del database… che dovrebbe tener conto anche di diversi altri fattori, possiamo semplicemente costruirci un tabella CALENDAR da usare in Join nelle nostre estrazioni… ottenendo report più leggibili e nettamente più prestanti.

Nel corso “E-Learning: SQL e DB2 for i – Corso completo” della nostra Academy, ho inserito (e sto ancora inserendo) una vera e propria sessione di Tip & Tricks: la lezione che parla appunto di questa tecnica l’abbiamo lasciata in “anteprima gratuita … vale la pena dare almeno una occhiata e valutare l’intero corso in E-Learning!

--- Roberto De Pedrini Faq400.com