-- Université IBM i du 7 novembre 2023 -- Session S19 – Apprivoisez l'IFS avec les services SQL -- Philippe Bourgeois - IBM France -- 1. Modification des propriétés de la connexion JDBC pour indiquer NAMING=*SYS -- 2. Exécution des commandes CL suivantes : CL:ADDLIBLE LIB(SYSTOOLS); CL:CHGCURLIB CURLIB(PB); SELECT * FROM library_list_info; -- Liste de tous les services SELECT COUNT(*) FROM services_info; -- 326 au 07/11/2023 SELECT * FROM services_info ORDER BY service_category, service_name; -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Liste de tous les services IBM i SELECT COUNT(*) FROM services_info WHERE service_category NOT LIKE 'DATABASE%'; -- 278 au 07/11/2023 SELECT * FROM services_info WHERE service_category NOT LIKE 'DATABASE%' ORDER BY service_category, service_name; -- Catégorie IFS SELECT COUNT(*) FROM services_info WHERE service_category = 'IFS'; -- 8 au 30/10/2023 --> Il en manque 5 SELECT * FROM services_info WHERE service_category = 'IFS' ORDER BY service_name; -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Services IFS -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- 1. OBJECT_STATISTICS - Liste et attributs des objets d'un répertoire de l'IFS SELECT * FROM TABLE (qsys2.ifs_object_statistics(START_PATH_NAME => '/home/BOURGEOIS'));, -- Les objets sous /home - SANS les sous-répertoires - Informations de base SELECT Path_Name, Object_Type, Data_Size, Object_Owner FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/home', SUBTREE_DIRECTORIES => 'NO')); -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Les objets sous /home - AVEC les sous-répertoires - Informations de base SELECT Path_Name, Object_Type, Data_Size, Object_Owner FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/home', SUBTREE_DIRECTORIES => 'YES')) ORDER BY Path_Name; -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Quels sont les fichiers JSON du répertoire /pb auxquels on a accédé après le 1er octobre 2023 ? SELECT Path_Name, Object_Type, Create_Timestamp, Access_Timestamp, Last_Used_Timestamp FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/pb', SUBTREE_DIRECTORIES => 'YES')) WHERE RIGHT(TRIM(Path_Name), 5) = '.json' AND Access_Timestamp >= '2023-10-01'; -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Quels sont les 5 plus gros fichiers sous le répertoire /home ? SELECT Path_Name, CHAR(Data_Size/1000000) AS Data_Size_Mo, Last_Used_Timestamp FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/home', SUBTREE_DIRECTORIES => 'YES', OBJECT_TYPE_LIST => '*STMF' )) ORDER BY Data_Size DESC LIMIT 5; -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Liste des fichiers dans /tmp qui n'ont pas été utilisés depuis au moins 2 ans SELECT Path_Name, Object_Type, Create_Timestamp, Access_Timestamp, Last_Used_Timestamp FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/tmp', SUBTREE_DIRECTORIES => 'YES', OBJECT_TYPE_LIST => '*ALLSTMF')) WHERE Last_Used_Timestamp < Current Timestamp - 2 years; -- Suppression des fichiers dans /tmp qui n'ont pas été utilisés depuis au moins 2 ans SELECT QCMDEXC('RMVLNK OBJLNK(''' CONCAT TRIM(Path_Name) CONCAT ''')') FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/tmp', SUBTREE_DIRECTORIES => 'YES', OBJECT_TYPE_LIST => '*ALLSTMF')) WHERE Last_Used_Timestamp < Current Timestamp - 2 years; -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Quels sont les membres dans la bibliothèque PB qui ont été modifiés après le 1er octobre 2023 ? SELECT PATH_NAME, SUBSTR(Path_Name, LOCATE_IN_STRING(Path_Name, '/', 1, 3) + 1, (LOCATE_IN_STRING(Path_Name, '/', 1, 4) - LOCATE_IN_STRING(Path_Name, '/', 1, 3))-1) AS Fichier, SUBSTR(Path_Name, LOCATE_IN_STRING(Path_Name, '/', -1) + 1) AS Membre, Text_Description, Data_Change_Timestamp, Object_Owner FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/QSYS.LIB/PB.LIB', OBJECT_TYPE_LIST => '*MBR' )) WHERE Data_Change_Timestamp >= '2023-09-01' ORDER BY Data_Change_Timestamp DESC; -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Nombre de fichiers et taille globale par répertoire de l'IFS WITH all_ifs_dirs AS (SELECT Path_Name AS dir_path FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/', SUBTREE_DIRECTORIES => 'YES', OBJECT_TYPE_LIST => '*ALLDIR *NOQSYS'))) SELECT dir_path, COUNT(*) AS stmf_count, SUM(data_size) AS total_size FROM all_ifs_dirs CROSS JOIN TABLE(ifs_object_statistics(START_PATH_NAME => dir_path, SUBTREE_DIRECTORIES => 'NO', OBJECT_TYPE_LIST => '*STMF')) GROUP BY dir_path ORDER BY stmf_count DESC; -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Les objets de mon répertoire sous /home dont je ne suis pas propriétaire SELECT Path_Name, Object_Type, Object_Owner FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/home/bourgeois', SUBTREE_DIRECTORIES => 'YES')) WHERE Object_Owner <> 'BOURGEOIS' AND Path_Name <> '/home/bourgeois'; -- Correction de la problématique SELECT Path_Name, QCMDEXC('CHGOWN OBJ(''' CONCAT Path_Name CONCAT ''') NEWOWN(BOURGEOIS) RVKOLDAUT(*NO)') AS CHGOWN_Result FROM TABLE (ifs_object_statistics(START_PATH_NAME => '/home/bourgeois', SUBTREE_DIRECTORIES => 'YES')) WHERE Object_Owner <> 'BOURGEOIS' AND Path_Name <> '/home/bourgeois'; -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- 2. IFS_OBJECT_PRIVILEGES -- Quels sont les droits sur le fichier /pb/json/films.json ? SELECT * FROM TABLE(ifs_object_privileges('/pb/json/films.json')); SELECT Path_Name, Object_Type, Owner, Authorization_Name, Data_Authority FROM TABLE(ifs_object_privileges('/home/bourgeois/xsr.log')); -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Objets IFS de /home/bourgeois qui ne sont pas en *PUBLIC *EXCLUDE SELECT Path_Name, 1 AS CHGAUT_Result, os.Object_Type, Owner, Data_Authority FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/home/bourgeois', SUBTREE_DIRECTORIES => 'YES')) os, LATERAL (SELECT Object_Type, Owner, Data_Authority FROM TABLE(ifs_object_privileges(PATH_NAME => os.path_name)) op WHERE op.authorization_name = '*PUBLIC' AND data_authority <> '*EXCLUDE'); -- Objets IFS de /home/bourgeois qui ne sont pas en *PUBLIC *EXCLUDE -- Correction de la problématique SELECT Path_Name, QCMDEXC('CHGAUT OBJ(''' CONCAT TRIM(os.Path_Name) CONCAT ''') USER(*PUBLIC) DTAAUT(*EXCLUDE) OBJAUT(*NONE)') AS CHGAUT_Result FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/home/bourgeois', SUBTREE_DIRECTORIES => 'YES')) os, LATERAL (SELECT Object_Type, Owner, Data_Authority FROM TABLE(ifs_object_privileges(PATH_NAME => os.path_name)) op WHERE op.authorization_name = '*PUBLIC' AND data_authority <> '*EXCLUDE'); -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- 3. IFS_OBJECT_REFERENCES_INFO -- Combien de travaux ont une référence sur le répertoire /home/bourgeois ? SELECT * FROM TABLE(ifs_object_references_info(PATH_NAME => '/home/bourgeois')); -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- 4. IFS_OBJECT_LOCK_INFO -- Quels sont les travaux qui ont un verrouillage ou une référence sur le répertoire /home/bourgeois ? SELECT * FROM TABLE(ifs_object_lock_info(PATH_NAME => '/home/bourgeois')); -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- 5. IFS_JOB_INFO -- Quels sont les références IFS de mon travail en cours ? SELECT * FROM TABLE(ifs_job_info(JOB_NAME => '*')); -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- 6. SERVER_SHARE_INFO -- Quels sont les partages de fichiers Net Server ? SELECT * FROM server_share_info WHERE SHARE_TYPE = 'FILE'; -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- 7 -- IFS_RENAME VALUES ifs_rename(FROM_OBJECT => '/pb/json/listefilms.json', TO_OBJECT => '/pb/json/listefilms' CONCAT VARCHAR_FORMAT(current date, 'YYYYMMDD') CONCAT '.json', REPLACE => 'NO'); -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Lecture et écriture de fichiers -- Fonctions GET_xxx_FROM_FILE VALUES get_clob_from_file('/pb/json/films.json') WITH CS; VALUES get_clob_from_file('/pb/films.properties') WITH CS; VALUES get_clob_from_file('/pb/csv/films.csv') WITH CS; VALUES get_blob_from_file('/pb/pdf/SQL_Reference_V7R5.pdf') WITH CS; -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Recherche des fichiers JSON dans /pb qui contiennent "1984" SELECT Path_Name, get_clob_from_file(Path_Name) AS "Contenu du fichier IFS" FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/pb', SUBTREE_DIRECTORIES => 'YES', OBJECT_TYPE_LIST => '*ALLSTMF')) WHERE RIGHT(Path_Name, 5) = '.json' AND get_clob_from_file(Path_Name) LIKE '%1984%' ORDER BY Path_Name WITH CS; -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Est-ce que les fichiers listefilms.json qui se trouvent dans 2 répertoires différents ont le même contenu ? VALUES (CASE WHEN get_clob_from_file('/pb/json/listefilms.json') = get_clob_from_file('/pb/iws/listefilms.json') THEN 'Les 2 fichiers ont le même contenu' ELSE 'Les 2 fichiers ont un contenu différent' END) WITH CS; -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Lecture et décomposition d'un fichier JSON dans l'IFS SELECT * FROM JSON_TABLE( get_clob_from_file('/pb/json/films.json'), '$.films' COLUMNS(titre VARCHAR(40) PATH '$.titre', annee CHAR(4) PATH '$.annee', duree CHAR(4) PATH '$.duree')) WITH CS; -- Lecture et décomposition d'un fichier JSON dans l'IFS en passant par une vue CREATE OR REPLACE VIEW filmsv1 AS (SELECT * FROM JSON_TABLE( get_clob_from_file('/pb/json/films.json'), '$.films' COLUMNS(titre VARCHAR(40) PATH '$.titre', annee CHAR(4) PATH '$.annee', duree CHAR(4) PATH '$.duree'))); SELECT * FROM filmsv1 WITH CS; -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Fonction GET_XML_FILE VALUES get_xml_file('/pb/xml/films.xml') WITH CS; VALUES XMLPARSE(DOCUMENT get_xml_file('/pb/xml/films.xml')) WITH CS; SELECT * FROM XMLTABLE('/films/film' PASSING XMLPARSE(DOCUMENT get_xml_file('/pb/xml/films.xml')) COLUMNS titre VARCHAR(40) PATH 'titre', annee CHAR(4) PATH 'annee', duree CHAR(3) PATH 'duree') WITH CS; -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Services IFS_READ_xxx SELECT * FROM TABLE(ifs_read(PATH_NAME => '/pb/films.properties', END_OF_LINE => 'ANY')); SELECT * FROM TABLE(ifs_read(PATH_NAME => '/pb/json/films.json', END_OF_LINE => 'NONE')); SELECT LINE FROM TABLE(ifs_read_utf8(PATH_NAME => '/pb/csv/films.csv', END_OF_LINE => 'ANY')); SELECT * FROM JSON_TABLE( (SELECT LINE FROM TABLE(ifs_read(PATH_NAME => '/pb/json/films.json', END_OF_LINE => 'NONE'))), '$.films' COLUMNS(titre VARCHAR(40) PATH '$.titre', annee CHAR(4) PATH '$.annee', duree CHAR(4) PATH '$.duree')); -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- Recherche des fichiers JSON dans /pb qui contiennent "1984" SELECT Path_Name, (SELECT LINE FROM TABLE(ifs_read(PATH_NAME => Path_Name, END_OF_LINE => 'NONE'))) AS "Contenu du fichier IFS" FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/pb', SUBTREE_DIRECTORIES => 'YES', OBJECT_TYPE_LIST => '*ALLSTMF')) WHERE RIGHT(Path_Name, 5) = '.json' AND (SELECT LINE FROM TABLE(ifs_read(PATH_NAME => Path_Name, END_OF_LINE => 'NONE'))) LIKE '%1984%' ORDER BY Path_Name; -- ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// -- SPLIT SELECT * FROM TABLE(systools.split('A,B,C,D,E,F,G,H', ',')); WITH cte1 (z1) AS (VALUES('A,B,C,D,E'), ('F,G,H')) SELECT ordinal_position, z1, element FROM cte1 CROSS JOIN TABLE(systools.split(z1, ',')); ORDER BY z1, ordinal_position; -- Lecture et décomposition d'un fichier CSV de l'IFS WITH cte1 AS (SELECT line_number, line FROM TABLE(ifs_read_utf8(PATH_NAME => '/pb/csv/films.csv'))), cte2 AS (SELECT line_number, line, ordinal_position, TRIM(B '"' FROM element) AS element FROM cte1 CROSS JOIN TABLE(systools.split(line, ','))) SELECT line_number, MIN(CASE WHEN ordinal_position = 1 THEN element END) AS code_film, MIN(CASE WHEN ordinal_position = 2 THEN element END) AS code_realisateur, MIN(CASE WHEN ordinal_position = 3 THEN element END) AS titre_film, MIN(CASE WHEN ordinal_position = 4 THEN element END) AS genre_film, MIN(CASE WHEN ordinal_position = 5 THEN element END) AS duree_film, MIN(CASE WHEN ordinal_position = 6 THEN element END) AS annee_film FROM cte2 GROUP BY line_number; CREATE OR REPLACE VARIABLE pb.fichier_ifs VARCHAR(1024); CREATE OR REPLACE VIEW pb.filmsv2 AS WITH cte1 AS (SELECT line_number, line FROM TABLE(ifs_read_utf8(PATH_NAME => pb.fichier_ifs))), cte2 AS (SELECT line_number, line, ordinal_position, TRIM(B '"' FROM element) AS element FROM cte1 CROSS JOIN TABLE(systools.split(line, ','))) SELECT line_number, MIN(CASE WHEN ordinal_position = 1 THEN element END) AS code_film, MIN(CASE WHEN ordinal_position = 2 THEN element END) AS code_realisateur, MIN(CASE WHEN ordinal_position = 3 THEN element END) AS titre_film, MIN(CASE WHEN ordinal_position = 4 THEN element END) AS genre_film, MIN(CASE WHEN ordinal_position = 5 THEN element END) AS duree_film, MIN(CASE WHEN ordinal_position = 6 THEN element END) AS annee_film FROM cte2 GROUP BY line_number; SET pb.fichier_ifs = '/pb/csv/films.csv'; SELECT * FROM pb.filmsv2; -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Services IFS_WRITE_xxx CALL ifs_write_utf8(PATH_NAME => '/pb/ifsw1.txt', LINE => 'Premier enregistrement', FILE_CCSID => 1208, OVERWRITE => 'REPLACE'); CALL ifs_write_utf8(PATH_NAME => '/pb/ifsw1.txt', LINE => 'Second enregistrement', OVERWRITE => 'APPEND'); -- Copie d'un fichier IFS d'un répertoire à un autre SET TRANSACTION ISOLATION LEVEL READ COMMITTED; CALL ifs_write_utf8(PATH_NAME => '/pb/txt/ifsw1.txt', LINE => get_clob_from_file('/pb/ifsw1.txt'), OVERWRITE => 'REPLACE'); COMMIT; CALL ifs_write_utf8(PATH_NAME => '/pb/txt/ifsw1.txt', LINE => 'Troisième enregistrement', OVERWRITE => 'APPEND'); -- Création d'un fichier JSON dans l'IFS à partir de données d'une table CALL ifs_write_utf8(PATH_NAME => '/pb/json/films2.json', OVERWRITE => 'REPLACE', LINE => (SELECT JSON_OBJECT('films':JSON_ARRAYAGG(JSON_OBJECT('titre' VALUE RTRIM(titre), 'annee' VALUE annee, 'duree' VALUE duree))) FROM video.films)); -- Création d'un fichier dans l'IFS contenant le nom -- de tous les répertoires principaux de l'IFS BEGIN CALL ifs_write_utf8(PATH_NAME =>'/pb/rep_ifs.txt', LINE => '', OVERWRITE => 'REPLACE', END_OF_LINE => 'NONE'); FOR SELECT path_name AS nom_repertoire FROM TABLE(ifs_object_statistics(START_PATH_NAME => '/', SUBTREE_DIRECTORIES => 'NO', OBJECT_TYPE_LIST => '*ALLDIR')) DO CALL ifs_write_utf8(PATH_NAME => '/pb/rep_ifs.txt', LINE => nom_repertoire); END FOR; END; -- Ajout dans l'IFS du PDF "SQL Reference" qui se trouve sur le site Web Infocenter d'IBM CALL ifs_write_binary(PATH_NAME => '/pb/db2/SQL_Reference_75.pdf', OVERWRITE => 'REPLACE', LINE => HTTP_GET_BLOB('https://www.ibm.com/docs/en/ssw_ibm_i_75/pdf/rbafzpdf.pdf')); CALL ifs_write_binary(PATH_NAME => '/pb/db2/SQL_Reference_75.pdf', OVERWRITE => 'REPLACE', LINE => HTTP_GET_BLOB('https://www.ibm.com/docs/en/ssw_ibm_i_75/pdf/rbafzpdf.pdf', '{"proxy":"9.128.135.5,80", "proxySsl":"false"}')); -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------