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:
- Create the IFS_APPEND procedure – https://gist.github.com/NielsLiisberg/9ca47191bae9f301c0f00310201aaab6
- Create the IFS_WRITE procedure – https://gist.github.com/NielsLiisberg/3a5ea6d03687310f877ec65a7748e196
- Create the SQL_TO_CSV procedure – https://gist.github.com/NielsLiisberg/3bc04d45c39a816c4b52760d6f861c8b
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“
--- Roberto De Pedrini Faq400.com