02 - DB2 for i (EN)02a - SQL (EN)

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

Last Updated on 17 March 2020 by Roberto De Pedrini

This is the second post (Part.2) of the DB2 and SQL FAQ … you can find part 1 at the following link:

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

iDB2-FAQ-011 – How can I create a CSV from an SQL statement automatically and non-interactively?

Who uses the ACS Access Client Solution and its SQL Script execution function knows that it can export the result of an SQL query as a CSV file (or TXT or XLS etc.) … but if we wanted to do it automatically, perhaps from a scheduled program?

The thing is quite simple because in the SQL script we can also use operating system commands … Let’s take the following example … I query the NETSTAT function from SQL and save the contents in a CSV file for further analysis:

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);  

We could also run this script from a CL program, then schedule it or call it from another ILE program.

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

If you need more control here are three good stored procedure by  Niels Liisberg , good Danish IBM Champion Danese: call the sql_to_csv procedure passing your sql statement and a name of output file:

sql_to_csv.sql Call this procedure with your sql statement and the name of output file you want as CSV

ifs_write.sql Create your IFS file

ifs_append.sql Append data to your IFS file

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

The introduction of regular expressions in the DB2 for i SQL functions opens up a world in string processing … with a single regular-expression you save lines and lines of code …

There is an excellent guide in Italian on Marco Riva’s Regular Expressions at the following link: Regular expressions What are they?

iDB2-FAQ-013 – Regular Expression … practical cases

Let’s see some practical cases of Regular Expression in SQL and SQL Embedded:

In this example we see an RPG program that calls a web service … this returns a “path-file” with the complete path of a file (PDF) generated by the same service. The program retrieves “file name”, “file name without extension .pdf (document number)” from the returned string in the format: “C: inetpub pdfOrdini 1925049.pdf”

   // -------------------------
   // Generate_document
   // -------------------------
   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 filename 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;


   // Extract from PATH complete the file name
   exec sql
      select regexp_replace (: pathfile, '^. * \', '')
             into: filename 
       from SYSIBM.SYSDUMMY1;

   // Extract the document number from the filename.pdf
   exec sql
       select regexp_replace (: filename, '.[A-Za-z0-9] + $', '')
             into: numerodoc 
           from SYSIBM.SYSDUMMY1;

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


   END-PROC;   

iDB2-FAQ-014 – Retrieve the week number from a numerical date YYYYMMDD

I have several tables of my ERP on the dates stored in numerical format in the YYYYMMDD format, type 20191013 for October 13th 2019. How can I extrapolate the week number of the year with SQL?

 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 – Get the Timestamp in ISO 8601 format

From a discussion on Midrange.com I report this excellent UDF to obtain the Timestamp in ISO 8601 format proposed by 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 (
                     HOMES
                         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;     

And here I try to see the difference compared to the normal DB2 for i timestamp:

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

iDB2-FAQ-016 – How to store a CLOB in your IFS

A great thing you can do from 7.2 with RPG is to define special variables, with SQLTYPE(XML_CLOB_FILE) or SQLTYPE(CLOB_FILE), through which you can create file objects in the IBMi IFS by defining the variable’s “attributes”.

In this example (by Birgitta Hauser on Midrange.com) we see a case of CLOB_FILE (watch out for CCSID which must be 1208, UTF8!) which will be “saved” in an IFS directory as indicated by the “_name” attribute of the variable itself:

...
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; 

If you are running in an old OS version (why? think to upgrade as soon as possibile!), you don’t have SQLTYPE(CLOB_FILE) but you can store your CLOB file in IFS as you can see in this great Niel Liisbert project on Github Gist:

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

Or in this, also, great example by Birgitta Hauser:

https://github.com/BirgittaHauser/Write-to-IFS-with-SQL

iDB2-FAQ-017 –SQL Dynamic Compound Statement

SQL Dynamic Compound Statement is not so known by IBM i developers but with these statements you can have important logic in SQL statement without build stored procedures or SQL functions.

Look at this example: we want to change some USER-PROFILES and we can use a SQL Dynamic Compound Statement to read USER from the DB2 for i service QSYS2.USER_INFO and then launch a command CHGUSRPRF:

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;

Other interesting tips about Dynamic Compound Statement here: “IT-Jungle Dynamic Compound Statement in IBM i

iDB2-FAQ-018 – How to deal with Null Values in RPG SQL Embedded

We all know RPG don’t like NULLs, so we try to limit NULL values in our table building table by DDS o DDL with “NOT NULL”:

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
);

But you can stumble upon NULLs when you have LEFT JOIN or RIGHT JOIN SQL statement, with SQLCOD warnings and errors when you do the FETCH operation.

But you can controll it with NULL-IND variables: integer variabile that get -1 when you have a Null value in the relative variable. Look at this example code:

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; 

If you want to know something more about NULLs take a look at this great post on ENSKILL: Embedded SQL and Nulls

iDB2-FAQ-019 – MERGE SQL – Howto update or insert records (UPSERT)

You surely know how to update or insert records in a table … but, maybe, you don’t know how to do the job in a single statement SQL.

MERGE is the answer … let we see this example … I create a table in QTEMP and then ADD or UPDATE records to this table in a single MERGE statement:

        ...
        // 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;           

More info about MERGE SQL statement take a look at these pages:

iDB2-FAQ-020 – Audit Trigger SQL, Who moved my cheese?

I just wrote a new post about SQL Audit trigger … how to and how create the trigger in a production (in use) table

SQL Audit Trigger: Who moved my cheese?

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

--- Roberto De Pedrini Faq400.com
About author

Founder of Faq400 Srl, IBM Champion, creator of Faq400.com and blog.faq400.com web sites. RPG developer since I was wearing shorts, strong IBM i supporter, I have always tried to share my knowledge with others through forums, events and courses. Now, with my company Faq400 Srl, I help companies to make the most of this great platform IBM i.

Leave a Reply

Your email address will not be published. Required fields are marked *