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

SQL and DB2 for i – FAQ & Howtos – (Part 4 EN)

Last Updated on 25 June 2020 by Roberto De Pedrini

Here is the fourth part of this “FAQ & Howtos” for SQL and DB2 for i … the previous 3 “episodes” of these Faqs can be found at the bottom of this post in the “Corretal Articles” … but let’s get to the new FAQs:

iDB2-FAQ-031 – QYS2.IFS_OBJECT_PRIVILEGES … DB2 for i Service … to check permissions of IFS objects

If you have the luck and attention to be updated with the operating system and you have installed the TR8 for 7.3 or the TR2 for 7.4 you have an excellent DB2 for services for checking the permissions of IFS files and directories; “IFS_OBJECT_PRIVILEGES”

select * from table (
QSYS2.IFS_OBJECT_PRIVILEGES (path_name => '/ home / faq400 / *');

Tim Rowe and Scott Forstie talk about it in this Commo webinar: Manage your IFS with IBM i services

iDB2-FAQ-032 – Create CSV with SQL (with “Leading Zeros” for decimal numbers from -1 to +1)

Taking a cue from a request on the Midrange.com mailing lists where it was asked how to obtain a CSV keeping the “leading zeros” from decimal numbers (from -1 to +1) as in the case of the value 0.12 to be brought to the CSV as “0.12” and not “.12” like the normal CPYTOIMPF command does.

I found Niels Liesberg’s excellent SQL procedures on Gist Github … with a few more lines to Niels’ SQL_TO_CSV procedure we can keep the Leading Zeros in the CSV created!

Here are the few simple steps:

To the excellent Niels procedure apply this simple modification

            get descriptor 'modified' value colNo
                colValue = DATE;
            - Start FAQ400 Modifications
            - If is a number from -1 to +1 add a leading 0 (example .12 -> 0.12)
            if colType in (2, 3) then
               if to_number (colValue)> 0 and to_number (colValue) <1 then
                  set colValue = '0' concat colValue;
               elseif to_number (colValue)> - 1 and to_number (colValue) <0 then
                  set colValue = '-0' concat substr (colValue, 2);
                  end if;
            end if;
            - End FAQ400 Modifications
            if colType in (1, 12) then - char or varchar

Now let’s try to create a CSV from a simple table with numeric, decimal, negative data etc.

create table faq400.testtable 
 (alpha01 char (10),
  num01 decimal (15, 2),
  num02 decimal (15, 4));
  insert into faq400.testtable
  (alpha01, num01, num02)
  values ('A', 0.12, 0.1234),
        ('B', 1.12, 1.1234),
        ('C', 0.00, 100),
        ('D', -0.10, -0.1234);
call QUSRSYS.sql_to_csv (
   sql_statement => 'SELECT * FROM faq400.testtable',
   output_file => '/tmp/testtable.csv'

And this is the result (considering that in Italy we use the decimal point):

iDB2-FAQ-033 – Read ENVVAR Environment variables from SQL?

From our RPG programs we can read the values of the ENVVAR environment variables with the appropriate system APIs … or use this excellent SQL procedure by Niels Liesberg:

ENVVAR.SQL – https://gist.github.com/NielsLiisberg/093b9bc18b603abac5a297ec239aa690

iDB2-FAQ-034 – Monitor the IFS / tmp directory

Temporary files often accumulate in the IFS / tmp directory … with a few lines of SQL proposed by Scott Forstie in one of his Github Gists, we manage to keep it under control:

Bringing order to the IFS: https://gist.github.com/forstie/eb619063f46284365901dc88033bc6ce

iDB2-FAQ-035 – Different type of Timestamps

Different type of Timestamp are listed in this Simon Hutchinson’s post on RPGPGM.COM – SQL NOW and playing with timestamps

         NOW(0) AS ZERO,
         NOW(1) AS ONE,
         NOW(6) AS SIX,
         NOW(12) AS TWELEVE

But if you are at TR2 for 7.4 or TR8 for 7.3 you can use the new RPG %BIF %TIMESTAMP(*UNIQUE), for a Timestamp with 32-byte unique timestamp

Take a look here: IBM i 7.4 – TR2 Enhancements

iDB2-FAQ-036 – QSYS.QADBFDEP SQL Alternative to DSPDBR

If you are looking for Database Relations you can use DSPDBR command or query QSYS.QADBFDEP table:

Select *
Where dbffil = 'MYTABLE' and dbflib='MYLIB';

More info in this post on RPGPGM.COM: Getting database relationships using SQL

iDB2-FAQ-038 – Searching for reserved chars in char/varchar fields in a table

https://blog.faq400.com/en/db2-for-i/sql-db2-for-i-howto-2/#iDB2FAQ001_How_can_I_use_LIKE_SQL_Operator_when_I_looking_for_percent_or_underscoreSometimes we need to search some “reserved” chars in our tables, like apostrophe, percentage, underscore and so on. We just wrote a post here in this blog: “iDB2-FAQ-001: How can I use LIKE SQL Operator when I looking for percent or underscore?”

Simon Hutchinson, in his great blog RPGPGM.COM just wrote a new post about it: “Searching for reserved characters in SQL

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

%d bloggers like this: