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

SELECT NOW() AS NONE,
         NOW(0) AS ZERO,
         NOW(1) AS ONE,
         NOW(6) AS SIX,
         NOW(12) AS TWELEVE
  FROM SYSIBM.SYSDUMMY1;

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 *
From QSYS.QADBFDEP
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

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

CSV and SQL, regular expressions, dates and timestamps sql, CLOB and IFS, SQL Dynamic Compound Statement, How to deal with Read more

IBM i System Management: FAQ & Howto (Part 4) IT

This is the third part of the IBM i system management FAQ & Howto collection: you can see the other Read more

IBM i and OpenSource: Faq & Howto (Part 1) IT

First steps with Node.js and Python in IBM i environment; recall of programs from QSH and management of messages from Read more

IBM i system management: FAQ and Howto (Part 2 – EN)

A second collection of FAQs and Tips on managing the IBM i system: - Control of the JOB QSZASOINIT - 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 *