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:
- 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, 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
--- Roberto De Pedrini Faq400.com
1 Comment