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

Last Updated on 17 Maggio 2020 by Roberto De Pedrini

iDB2-FAQ-021 – Stored Procedure e Linked Server da MS SQL Server a DB2 for i

From Microsoft MS SQL Server we can configure a “Linked Server” to run SQL command on our IBM i from MS.

Se vuoi configurare un Linked Server tra Microsft SQL Server e IBM i … segui questa procedura: https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-in-sql-server-management-studio/

Una volta cresato il linked server puoi usare delle istruzioni SQL come quella che segue (da MS SQL Server):

select
RPMG_ETY_CD,
     ROW_CU_DATA_IN,
ROW_EF_DT,
ROW_XPR_DT,
RPMG_ETY_NM
from 
OPENQUERY 
         (MyLinkedDB2Server,
                'select 
                      RPMG_ETY_CD,
                      ROW_CU_DATA_IN,
                      ROW_EF_DT,
                      ROW_XPR_DT,
                      RPMG_ETY_NM
                 from RERTEBT.V1RERRMM')

O chiamare delle Stored Procedure

exec ('call RERTEBT.GET_DEFINITION (69,'''','''')') AT MyLinkedDB2Server

O utlizzare delle TABLE dai risultati di una stored procedure

SELECT FLT_DFN_ID, FLT_SRC_DFN_NO, FLT_VRSN_NO, FLT_STAT_CD, FLT_TY_CD, FLT_NAME 
      FROM OPENQUERY (MyLinkedDB2Server, 
                         'call RERTEBT.GET_DEFINITION 69,'''','''')')

In this last command you need to set up right proprietors: “proprieties” -> “server options” – “RPG” and “RPG Out” they must be TRUE value

iDB2-FAQ-022 – Un REPLACE di caratteri particolari da una stringa con SQL

Prendo spunto da una domanda sul forum Midrange.com che chiedeva come cambiare dentro una stringa i caratteri apostrofo (single quote ‘) e virgola (comma ,) con degli spazi in un solo passaggio.

Ecco 3 modi differenti per farlo “in un solo passaggio”:

Usando REPLACE

with exampletable as
(select 'My string with quote'' and comma,' as examplestring
from sysibm.sysdummy1)

select REPLACE(REPLACE(REPLACE(examplestring, ',', ' '), '"', ' '), '''', ' ') as CleanString
from exampletable;

Usando TRANSLATE

with exampletable as
(select 'My string with quote'' and comma,' as examplestring
from sysibm.sysdummy1)

select translate(examplestring, '  ', ''',') as CleanString 
from exampletable;

Usando REGEX_REPLACE

with exampletable as
(select 'My string with quote'' and comma,' as examplestring
from sysibm.sysdummy1)

select regexp_replace (examplestring, ',|''' , ' ' ) as CleanString 
from exampletable;

Teniamo presente che REGEX_REPLACE … come tutte le funzioni per Regular Expression … convertono tutto in oggetti UTF-16 DBCLOB … che potrebbero creare problemi di prestazioni!

iDB2-FAQ-023 – Update di una sottotringa con SQL (OVERLAY)

La funzione SUBSTR SQL la conosciamo tutti per fare la funzione sottostringa di un campo in SQL:

select empno, firstnme, lastname, substr(lastname, 6, 10) as stringpart
 from sampledb.employee
 where lastname='MONTEVERDE';
 ---
Result
EMPNO  FIRSTNME    LASTNAME       STRINGPART
200240 ROBERT      MONTEVERDE     VERDE

Ma se volgiamo fare un update di una sottostringa non possiamo usare SUBSTR … come nell’esempio che segue che è ERRATO!

update sampledb.employee
set substr(lastname, 6, 10)='ROSSO'
where lastname='MONTEVERDE';
---
Result
SQL State: 42601
DB2 SQL Error: -104

Il metoto corretto è utilizzare OVERLAY:

update sampledb.employee
set lastname=overlay(lastname, 'ROSSO',  6, 10)
where lastname='MONTEVERDE';

iDB2-FAQ-024 – Gestire errori da funzione SQL TO_NUMBER

LA funzione to_number(myfield) trasforma un campo alfanumerico in un valore numerico … se la stringa è compatibile … altrimenti la funzione va in errore (non ritorna un null). Questo significa che uno statement SQL come quello che segue … non risolve il problema di tornare 0 a fronte di un valore alfanumerico non trasformabile in numero… come nel caso qui sotto … che non ritorna 0 come voluto!

select coalesce(to_number('a'), 0) 
 from sysibm/sysdummy1;

Quindi non possiamo usare coalesce perché non viene tornato un null ma un messaggio di errore … quindi per simulare un null possiamo ricorrere ad una apposita UDF che torna null invece che segnalare l’errore … quindi una TO_NUMBER_evolution … UDF suggerita da Rob Berendt in un post di MidrangeL:

CREATE OR REPLACE FUNCTION rob.mychar_to_float (
            parameter1 char(42)
    )
    RETURNS decfloat
    LANGUAGE SQL
    BEGIN
        DECLARE variable1 decfloat;
        DECLARE invalid_float CONDITION FOR '42820';
        DECLARE EXIT HANDLER FOR invalid_float
        SET variable1 = NULL;
        set variable1 = to_number(parameter1);
        RETURN variable1;
    END;

select mychar, rob.mychar_to_float(mychar), coalesce(rob.mychar_to_float(mychar), 0)
from qtemp.rr;

Maggiorni informazioni su TO_NUMBER li trovi qui: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/db2/rbafzscatonumber.htm

iDB2-FAQ-025 – Recuperare l’ultimo id-identity_column in fase di INSERT (FINAL TABLE vs IDENTITY_VAL_LOCAL)

Se utiliziamo tabelle con un campo IDENTITY potrebbe essere necessario, alle volte, recuperare il valore di questo IDENTITY (generato dal DB) del record appena inserito con una funzione di INSERT INTO.

Supponiamo di avere la nostra tabella creata con delle DDL simile a questo esempio:

CREATE TABLE MYTABLE (
  ID BIGINT  GENERATED ALWAYS AS IDENTITY  NOT NULL,
  MYFIELD1 CHAR(15) NOT NULL DEFAULT '',  
  MYFIELD2 DECIMAL(15, 4) NOT NULL DEFAULT 0
   ) ;

Abbiamo due modi diversi per recuperare questo valore

Metodo 1: FINAL TABLE

Con un SELECT ID from FINAL TABLE (INSERT INTO…) è la cosa più semplice e veloce. Esempio:

SELECT ID FROM FINAL TABLE (
INSERT INTO QTEMP.MYTABLE (MYFIELD1, MYFIELD2)
VALUES('COD001', 123.00) );

Metodo 2: IDENTITY_VAL_LOCAL()

Un altro metodo è quello di utilizzare la funzione IDENTITY_VAL_LOCAL() da eseguire appena dopo lo statement di INSERT… nel seguente esempio tutto SQL creo una global variable SQL … in RPG SQL Embedded potrei usare una normale variabile numerica

create variable faq400.lastid bigint;

INSERT INTO QTEMP.MYTABLE (MYFIELD1, MYFIELD2)
VALUES('COD001', 123.00);

set faq400.lastid=IDENTITY_VAL_LOCAL();

select faq400.lastid from sysibm.sysdummy1;

Proprio su IDENTITY_VAL_LOCAL() c’è un ottimo post, come sempre, di Simon Hutchinson su RPGPGM.COM: View the most recent Identity column value

iDB2-FAQ-026 – UPDATE con JOIN con DB2 for i … non si può … e allora?

Sicuramente ci abbiamo provato, almeno una volta, tutti noi: fare un update di una tabella con una condizione di JOIN: con DB2 for i non si può … con altri RDBMS invece si può fare. Quali alternative?

Se ne è parlato in questi giorni (Maggio 2020) sulla mailing list di Midrange-L, e la domanda era: come posso fare con DB2 for i questa operazione con SQL che con MYSQL riesco? L’istruzione è quella che segue e l’esigenza è quella di aggiornare il campo “Status” della tabella1 quando sono passati il numero di giorni definiti nella tabella2 per ogni singolo id.

UPDATE table1, table2
 SET table1.status='Old'
 WHERE table1.stamp <= current date - table2.alert days
       AND table1.id=table2.id

Opzione 1, suggerita da Birgitta Hauser:

UPDATE table1 a
   SET table.status='Old'
   Where Exists(Select 1
                   From  table2 b
                   Where     a.Id = b.Id
                         and a.Stamp <= Current date - b.Alert Days)

Opzione 2, suggerita Patrick Shindler

UPDATE table1 SET table.status='Old'
WHERE table1.stamp <= DATE(CURRENT_TIMESTAMP) - (
      SELECT table2.alert FROM table2
      ) days 
AND EXISTS
      (
      SELECT 1 FROM table2 b
       WHERE b.id=a.id
      )

Opzione 3, suggerita da Charles Wilt

merge into table1 dst
 using (select id, alert from table2) src
   on dst.id = src.id
 when matched and dst.stamp <= current date - src.alert days
   then update dst.status = 'OLD'
 else ignore;

iDB2-FAQ-027 – LPRINTF – Scrivere messaggi nel JOBLOG con SQL

Ce ne parla il nostro Blogger Massimo Duca in un apposito post qui sul Blog di Faq400:

Scrivere messaggi nel joblog con SQL – By Massimo Duca

iDB2-FAQ-028 – Leggere lo Stack di chiamata di un JOB con QSYS2.STACK_INFO

In alcuni casi dobbiamo leggere lo stack di chiamata di un JOB per capire le condizioni di un certo evento… ad esempio, in un trigger potremmo recuperare il programma che sta eseguendo una determinata operazione. La UDTF QSYS2.STACK_INFO ci permette di farlo. Ce ne parla Massimo Duca in questo Post del nostro blog: Chi ha chiamato il mio programma ? – By Massimo Duca

iDB2-FAQ-029 – Conversioni di date con SQL (o ILE RPG)

Una bella guida alla conversione delle date con SQL e anche con ILE RPG, ce l’ha preparata il nostro Blogger Marco Riva e ce ne parla in questo post:

https://blog.faq400.com/it/programmazione/date-e-ore-conversione-tra-tipi-dati-diversi/Date e ore: conversione tra tipi dati diversi – By Marco Riva

iDB2-FAQ-030 – Esplorare le Data-Area con SQL?

Ce lo spiega il nostro blogger Marco Riva in questo post qui sul Blog: Esplorare le aree dati con SQL By Marco Riva

--- Roberto De Pedrini Faq400.com