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

Last Updated on 22 Marzo 2020 by Roberto De Pedrini

Questo è il secondo post (Part.2) delle FAQ su DB2 e SQL … puoi trovare la parte 1 al seguente link:

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

iDB2-FAQ-011 – Come posso creare un CSV da uno statement SQL in modo automatico e non interattivo?

Chi utilizza ACS Access Client Solution e la sua funzione di esecuzione Script SQL sa che può esportare il risultato di una query SQL come file CSV (o TXT o XLS ecc) … ma se volessimo farlo in modo automatico, magari da un programma schedulato?

La cosa è abbastanza semplice perché nello script SQL possiamo utilizzare anche dei comandi di sistema operativo … Prendiamo il seguente esempio … interrogo la funzione NETSTAT da SQL e salvo il contenuto in un file CSV per analisi successive:

create table qtemp.netstat as
 (SELECT *
     FROM qsys2.netstat_info)
     with data;
 cl: CPYTOIMPF FROMFILE(QTEMP/NETSTAT)
     TOSTMF('/home/faq/netstat.csv')
     STMFCCSID(PCASCII) RCDDLM(CRLF) ADDCOLNAM(*SQL);  

Questo script potremmo anche eseguirlo da un CL program, quindi schedularlo o chiamarlo da un altro programma ILE.

       PGM          
         RUNSQLSTM  SRCFILE(FAQ400/SRC) SRCMBR(XNETSTAT) COMMIT(*NONE) OUTPUT(*PRINT)   
       ENDPGM 

Per un controllo migliore possiamo utilizzare anche queste tre procedure di Niels Liisberg , grande IBM Champion Danese, che creano un CSV da un SQL Dinamico:

sql_to_csv.sql Stored Procedure SQL che riceve una sql statement e un nome di file output e genera l’opportuno csv

ifs_write.sql Crea un file in IFS via SQL e C con il contenuto passato

ifs_append.sql Aggiunge del contenuto ad un file IFS già presente

iDB2-FAQ-012 – Regular Expression con SQL DB2 for i

L’introduzione delle regolar expressions nelle funzioni SQL del DB2 for i apre un mondo nell’elaborazione delle stringhe … con una sola regular-expression si risparmiano righe e righe di codice …

C’è una ottima guida in Italiano sulle Regular Expression di Marco Riva al seguente link: Regular expressions Cosa sono?

iDB2-FAQ-013 – Regular Expression… casi pratici

Vediamo alcuni casi pratici di Regular Expression in SQL e SQL Embedded:

In questo esempio vediamo un programma RPG che chiama un web service … questo torna un “path-file” con il percorso completo di un file (PDF) generato dallo stesso servizio. Il programma recupera “nome file”, “nome file senza estensione .pdf (numero del documento)” dalla stringa tornata nel formato : “C:\inetpub\pdfOrdini\1925049.pdf”

   //-------------------------
   // Genera_documento
   //-------------------------
   dcl-proc doc_genera  export;
   dcl-pi   doc_genera  char(30)  ;
      DocType           char(3) const;
      InternalNumber    char(10) const;
   end-pi;
   dcl-s url varchar(256);
   dcl-s pathfile char(45);
   dcl-s nomefile char(15);
   dcl-s numerodoc char(15);


   url='http://webserverip/api/doc_generate?'
   + 'InternalNumber='+%trim(InternalNumber);



   exec sql
     SELECT Pathfile
     into   :pathfile  
        from
            JSON_TABLE(SYSTOOLS.HTTPGETCLOB(replace(:url,' ','%20'),''),
            '$.Result'  COLUMNS(
             Pathfile VARCHAR(100)  PATH '$'
             ) ) as x ;


   // Estraggo dalla PATH completa il nome del file
   exec sql
      select regexp_replace(:pathfile, '^.*\\', ' ')
             into :nomefile 
       from SYSIBM.SYSDUMMY1;

   // Estratto il numero documento dal nomefile.pdf
   exec sql
       select regexp_replace(:nomefile, '\.[A-Za-z0-9]+$', ' ')
             into :numerodoc 
           from SYSIBM.SYSDUMMY1;

  if nomefile<>'';
     return numerodoc;
  else;
     return 'KO';
  endif;


   END-PROC;   

iDB2-FAQ-014 – Recuperare il numero settimana da una data numerica YYYYMMDD

Ho diverse tabelle del mio ERP on le date memorizzate in formato numerico nel formato YYYYMMDD, tipo 20191013 per il 13 Ottobre 2019. Come posso estrapolare con SQL il numero di settimana dell’anno?

 select ndoc, nfat, dfat, VarChar_Format(Timestamp(Right(Digits(dfat) concat '000000', 14)), 'IYYY/IW')
   from myERPtable
   where tdoc='FAT'
     order by dfat desc, nfat desc;

iDB2-FAQ-015 – Ottenere il Timestamp in formato ISO 8601

Da una discussione su Midrange.com riporto questa ottima UDF per ottenere il Timestamp in formato ISO 8601 proposta da Rob Berendt

CREATE OR REPLACE FUNCTION faq400.ts8601 (
             timestampIn TIMESTAMP
     )
     RETURNS CHAR(25)
     LANGUAGE SQL
     BEGIN
         DECLARE chartimestamp CHAR(19);
         DECLARE timestampout CHAR(25);
         SET chartimestamp = LEFT(CHAR(timestampIn), 19);
         SET timestampout = LEFT(chartimestamp, 10) CONCAT 'T' CONCAT REPLACE(SUBSTR(chartimestamp, 12, 8), ':', '.') CONCAT (
                     CASE
                         WHEN CURRENT TIMEZONE < 0 THEN '-'
                         ELSE '+'
                     END) CONCAT SUBSTR(DIGITS(CURRENT TIMEZONE), 1, 2) CONCAT ':' CONCAT SUBSTR(DIGITS(CURRENT TIMEZONE), 3, 2);
         RETURN timestampout;
     END;     

E qui provo a vederne la differenza rispetto al normale timestamp del DB2 for i:

SELECT faq400.ts8601(CURRENT TIMESTAMP) AS TimeStamp8601,
        CURRENT TIMESTAMP AS TimestampISO,
        CURRENT TIMESTAMP - CURRENT TIMEZONE AS TimestampGMT
     FROM sysibm.sysdummy1;

iDB2-FAQ-016 – Salvare un CLOB nell’IFS

Una cosa fantastica che si può fare dalla 7.2 con RPG è quella di definire delle variabili speciali, con SQLTYPE(XML_CLOB_FILE) o SQLTYPE(CLOB_FILE), tramite le quali è possibile creare degli oggetti file nell’IFS dell’IBMi definendo opportunamente degli “attributi” della variabile.

In questo esempio (di Birgitta Hauser su Midrange.com) vediamo un caso di CLOB_FILE (attenzione al CCSID che deve essere 1208, UTF8!) che verrà “salvato” in una directory dell’IFS come indicato dall’attributo “_name” della variabile stessa:

...
DCL-S  OutFile   SQLType(CLOB_FILE)   CCSID(1208);

Exec sql Declare c1 cursor for json_object('data' value json_arrayagg(json_object('id' value id, 'account' value cust_acct))) from DB_Table offset 10 rows;
Exec sql open c1;

Dow sqlcod = 0;
  Outfile_Name =  '/temp/accounts_{timestamp}.json'
  Outfile_NL = %len(%trimr(Outfile_Name))
  Outfile_FO = SQFCRT; 
  Exec sql fetch c1 for 10 rows into :Outfile;
Enddo;

Exec sql close c1; 

Se non disponibile l’oggetto SQLTYPE(CLOB_FILE) per un problema di OS version o PTF è possibile utilizzare anche la tecnica presentata in questo “progetto Git” di Niels Liisbert disponibile in questo repository Git GIST:

https://gist.github.com/NielsLiisberg/cd2350aee85f5b2e967993faf7ea7595

Oppure in questo esempio sempre di Birgitta Hauser https://github.com/BirgittaHauser/Write-to-IFS-with-SQL

iDB2-FAQ-017 –SQL Dynamic Compound Statement

Le dynamic compound statement non sono molto conosciute e utilizzate dagli sviluppatori IBM i, in genere, ma aprono grandi possibilità permettendo di eseguire della logica senza dover necessariamente creare una Stored Procedure memorizzata.

Prendiamo questo esempio: devo modificare una attributo a tutti gli utenti radio-frequenza … quindi eseguire un CHGUSRPRF su ogni utente che, ad esempio, inizia con RF … lo posso fare con questa Dynamic – Compound – Statement:

begin

    declare cmd varchar(256);

    for vl as c1 cursor for
        select
        *
        FROM  QSYS2.USER_INFO  WHERE AUTHORIZATION_NAME LIKE 'LAB%'
        do

        set cmd = 'CHGUSRPRF USRPRF('|| AUTHORIZATION_NAME|| ') LMTDEVSSN(*YES)'; 
        call qcmdexc(cmd);
    end for;
end;

Altri riferimenti sulle Dynamic Compound Statement si possono trovare in questo ottimo articolo di “IT-Jungle Dynamic Compound Statement in IBM i“

iDB2-FAQ-018 –Gestione dei Null con SQL Embedded

Tutti sappiamo che al nostro caro RPG non piacciono tanto i NULL e, tendenzialmente, cerchiamo di evitare di averli nelle nostre tabelle creando le stesse con le DDS oppure con le DDL ma con la dicitura NOT NULL DEFAULT come nell’esempio che segue:

CREATE OR REPLACE TABLE faq400.pricelist (
ItemId       char(15) not null default '',
CustomerId   char(15)  not NULL default '',
FromDate     date not null default current date,
ToDate       date not null default current date,
Price        decimal(21, 4) not null default 0
);

Ma tutto il nostro sforzo per non cadere nei NULL value viene a cadere appena utilizziamo SQL con LEFT JOIN o RIGHT JOIN: quando la chiave di JOIN non trova valori potrebbe portare dei NULL nelle nostre “host variable/ds” nel risultato della FETCH e tornando insieme anche un SQLCOD di Warning/Errore proprio al momento dell’assegnazione (fetch).

La cosa è comunque controllabile utilizzando delle variabili di tipo INTEGER che fungeranno da NULL-IND (indicatori di valore nullo): praticamente in questi NULL-IND troveremo il valore negativo uno (-1) se il campo relativo è NULL e zero (0) se invece non è NULL. Esempio:

Dcl-c Null -1;

Dcl-s NullInd01 Int(5);
Dcl-s NullInd02 Int(5);


Exec SQL Select a.field1, b.field2
         Into :field1 :NullInd01, 
              :field2 :NullInd02
         From Myfile1 a
         join MyFile2 b on a.key=b.key 
         Where key = :Mykey;

// Check Null
if NullInd01=Null;
  dsply "Field1 is null";
endif; 
if NullInd02=Null;
  dsply "Field2 is null";
endif; 

C’è un ottimo post su ENSKILL che tratta proprio l’uso corretto dei NULL: Embedded SQL and Nulls

iDB2-FAQ-019 – MERGE SQL Per fare Update or Insert (UPSERT)

Per aggiornare o inserire record nelle tabelle con SQL sappiamo tutti fare una operazione di UPDATE (per aggiornare) o INSERT (per inserire) … ma non tutti sanno che possono fare un MERGE che permette di fare l’update se il record già esiste (con una determinata chiave) oppure l’INSERT se lo stesso non esiste.

Vediamo un esempio di MERGE in un RPG SQL Embedded dove vengono inseriti o aggiornati dei valori in una tabella:

        ...
        // Create a table in QTEMP
        create_tableCustH();

        ...
        // Insert or Update temp table
        update_tableCustH(mycod:myval:myweight);


         
 
        //-------------------------------
        // Create a temp table
        //  (or reset it)
        //-------------------------------
        dcl-proc create_tableCustH;
         dcl-pi  create_tableCustH;
         END-PI;

         exec sql
             create or replace table qtemp.custHeadings
              (cod char(20),
               val decimal(21, 4),
               wei decimal(21, 4));

         exec sql
              truncate table  qtemp.custHeadings;

         return ;
        end-proc;

        //-------------------------------
        // update_tableCustH(cod:val:wei)
        //-------------------------------
        dcl-proc update_tableCustH;
         dcl-pi  update_tableCustH;
           cod      char(20)       const;
           val      packed(21:4) const;
           wei      packed(21:4) const;
         END-PI;

         exec sql
            MERGE INTO qtemp.CustHeadings AS A
          USING (VALUES (:cod, :val, :wei)) AS B
                         (cod,  val , wei)
          ON A.cod = b.cod
          WHEN MATCHED THEN
              UPDATE SET a.val = a.val+b.val,
                         a.wei=a.wei+b.wei
          WHEN NOT MATCHED THEN
              INSERT (cod, val, wei)
              VALUES (b.cod, b.val, b.wei)

          ELSE IGNORE ;

         return ;
        end-proc;           

Altre info a questi link:

• MC PressOnline – SQL MERGE Statement Tricks
• IT Jungle – Merge Into the Synchronization Fast Lane with DB2 for i 7.1

iDB2-FAQ-020 – Audit Trigger SQL, per monitorare modifiche su una tabella

Ho fatto un post proprio su questo argomento: creare un trigger per fare l’Audit delle modifiche (inserimenti, cancellazioni e update di determinati campi di una tabella):

SQL Audit Trigger – Chi ha spostato il mio formaggio?

--- Roberto De Pedrini Faq400.com