Creare SQL Views per interpretare QSYS2.DISPLAY_JOURNAL

I sorgenti (statement SQL) di questo post sono disponibili su Github a questo link : https://github.com/Faq400Git/Create_Display_Journal_Table_View

Personalmente sono un grande fan degli IBM i Services, quella serie di Funzioni SQL (UDF e UDTF) e Stored Procedure che, ad ogni Technology Refresh, si arricchisce nel numero e nelle funzionalità.

La QSYS2.DISPLAY_JOURNAL è una UDTF ormai presente dalla 7.1 del sistema operativo, ed è una ottima alternativa al comando DSPJRN per interrogare le registrazione nei Journal Receiver.

Interrogare gli eventi del Journal non è mai stato così semplice: una istruzione SQL è in grado di estrarre da un Journal (o meglio dai Journal Receiver di un Journal) tutti gli eventi riguardanti una o più tabelle sotto controllo.

Finché si tratta di interrogare le informazioni riguardo all’evento sulla tabella (ad esempio tipo evento (insert/delete/update..), il data/ora evento, utente, job ecc) QSYS2.DISPLAY_JOURNAL è veramente fantastica, ma se abbiamo bisogno di interpretare il contenuto del campo ENTRY_DATA (il campo Blob con l’immagine del record) la cosa non è proprio così immediata.

Cercando qua e là so Google ho trovato diversi modi per leggere/interpretare il contenuto di questo campo ENTRY_DATA:

• Il sito ufficiale di IBM ci consiglia di utilizzare la funzione INTEPRET di SQL, mappando i campi che ci interessano nel Blob ENTRY_DATA: IBM Support – How to extract and search for ENTRY_DATA in DISPLAY_JOURNAL table function: ottimo, INTERPRET ci permette di estrarre da quel campo BLOB le nostre informazioni, sia che siano in campi CHAR, VARCHAR, DECIMAL, NUMERIC ecc. Il problema è recuperare gli “offset” e “lunghezze” di ogni campo ed impostare di conseguenza l’istruzione INTERPRET
• Il grande Simon Hutchinson, nel suo posta “Extracting data from journals using SQL” ci spiega invece come farlo da RPG, leggendo i record del DISPLAY_JOURNAL e mappando il campo ENTRY_DATA in una DS con la stessa struttura della nostra tabella.
• Oppure ricorrere alla buona e vecchia API QjoRetrieveJournalEntries: “Retrieve Journal Entries (QjoRetrieveJournalEntries) API“
• Anche questa tecnica proposta da Sam Lennon potrebbe essere una alternativa: “Journal Entries Exposed! JOESD Made Readable!“

Nonostante le mie ricerche su Google non ho mai trovato nessuno che mi permettere di leggere ed interpretare le Journal Entries direttamente con SQL senza impazzire nel mappare campo campo a seconda della tabella di mio interesse.

Ecco allora l’idea: creare una Stored Procedure SQL che legge dal catalogo SYSCOLUMNS nomi, tipi, lunghezze e offset dei campi e crea in automatico una SQL View sulla table function QSYS2.DISPLAY_JOURNAL … insomma qualcosa che faccia per me lo sporco lavoro di mappare tutto il campo ENTRY_DATA a seconda della tabella interessata.

Una volta creata la Stored Procedure quindi sarà sufficiente richiamarla passandogli i seguenti parametri:

• Libreria del Journal
• Nome della tabella (System Name o SQL Name)
• Libreria del Journal
• Nome del Journal
• Libreria dove si vuole creare la View
• Nome della View
• Flag Y/N per eventuale REPLACE della View
• Global Variable (o hsot variable da SQL Embedded) dove tornare lo statement SQL della CREATE VIEW (opzionale)

call FAQ400.CREATE_DISPLAY_JOURNAL_TABLE_VIEW(MYTABLE_LIBRARY => 'FAQ400JOU',
                                              MYTABLE_NAME    => 'MYSAMPLETABLE',
                                              MYJOURNAL_LIBRARY => 'FAQ400JOU',
                                              MYJOURNAL_NAME    => 'QSQJRN',
                                              MYVIEW_LIBRARY    => 'FAQ400JOU',
                                              MYVIEW_NAME       => 'V_MYSAMPLETABLE_AUDIT2',
                                              CREATEANDREPLACE  => 'Y',
                                              MYCMD             => FAQ400.GV_VARCHAR);

E poi interrogare la view per vedere le registrazioni nel Journal della tabella interessata:

select * from FAQ400JOU.V_MYSAMPLETABLE_AUDIT;

Ottenendo qualcosa simile a queste due immagini

Il sorgente della stored procedure CREATE_DISPLAY_JOURNAL_TABLE_VIEW, e qualche esempio di utilizzo, lo puoi trovare nel mio Github a questo link: https://github.com/Faq400Git/Create_Display_Journal_Table_View