{"id":447,"date":"2026-04-07T10:08:35","date_gmt":"2026-04-07T10:08:35","guid":{"rendered":"https:\/\/fhix.fr\/?page_id=447"},"modified":"2026-04-07T10:09:32","modified_gmt":"2026-04-07T10:09:32","slug":"sql-system","status":"publish","type":"page","link":"https:\/\/fhix.fr\/index.php\/sql-system\/","title":{"rendered":"TIPS AS400 IBMi SQL SYSTEM"},"content":{"rendered":"<ul class=\"wp-block-latest-posts__list wp-block-latest-posts\"><li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2026\/04\/07\/lister-les-dates-et-utilisateurs-de-creation\/\">Lister les dates et utilisateurs de cr\u00e9ation<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p>SELECT OBJLIB as bibliotheque ,<br>OBJNAME as objet , OBJTYPE as type , OBJDEFINER as createur<br>, OBJCREATED as date_creation ,<br>OBJTEXT as description<\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p>FROM TABLE(QSYS2.OBJECT_STATISTICS(&lsquo;AXFILE&rsquo;,&rsquo;*ALL&rsquo;))<br>ORDER<br>BY OBJtype, objname<\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2026\/04\/07\/comparer-2-bibliotheques-pour-chercher-les-fichiers-mis-a-niveau\/\">Comparer 2 biblioth\u00e8ques pour chercher les fichiers mis \u00e0 niveau<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p>Cette requ\u00eate SQL permet de mettre en \u00e9vidence les diff\u00e9rences de niveaux entre 2 biblioth\u00e8ques : <br><code><strong>SELECT<\/strong> <br>  a.SYSTEM_TABLE_NAME as fichier, <br>  a.SYSTEM_TABLE_SCHEMA as bib1,\u00a0a.FORMAT_LEVEL_ID as niv1, <br>  b.SYSTEM_TABLE_SCHEMA as bib2, b.FORMAT_LEVEL_ID\u00a0as niv2, <br>  (a.FORMAT_LEVEL_ID  b.FORMAT_LEVEL_ID) as upgraded<br><strong>FROM<\/strong> sysfiles a , sysfiles b\u00a0\u00a0<br><strong>WHERE<\/strong> <br>  a.system_table_schema = 'MABIB1' and\u00a0 <br>  b.system_table_schema = 'MABIB2' and<br>  a.native_type = 'PHYSICAL' and\u00a0<br>  a.SYSTEM_TABLE_NAME = b.SYSTEM_TABLE_NAME\u00a0<\/code><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:image {\"id\":440,\"sizeSlug\":\"full\",\"linkDestination\":\"none\"} -->\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"540\" height=\"148\" src=\"https:\/\/fhix.fr\/wp-content\/uploads\/2026\/04\/image.png\" alt=\"\" class=\"wp-image-440\" srcset=\"https:\/\/fhix.fr\/wp-content\/uploads\/2026\/04\/image.png 540w, https:\/\/fhix.fr\/wp-content\/uploads\/2026\/04\/image-300x82.png 300w\" sizes=\"auto, (max-width: 540px) 100vw, 540px\" \/><\/figure>\n<!-- \/wp:image -->\n\n<!-- wp:paragraph -->\n<p>Pour n&rsquo;afficher que les fichiers qui ont chang\u00e9 de niveau :<br><code><code><strong>SELECT<\/strong> <br>  a.SYSTEM_TABLE_NAME as fichier, <br>  a.SYSTEM_TABLE_SCHEMA as bib1,\u00a0a.FORMAT_LEVEL_ID as niv1, <br>  b.SYSTEM_TABLE_SCHEMA as bib2, b.FORMAT_LEVEL_ID\u00a0as niv2<br><strong>FROM<\/strong> sysfiles a , sysfiles b\u00a0\u00a0<br><strong>WHERE<\/strong> <br>  a.system_table_schema = 'MABIB1' and\u00a0 <br>  b.system_table_schema = 'MABIB2' and<br>  a.native_type = 'PHYSICAL' and\u00a0<br>  a.SYSTEM_TABLE_NAME = b.SYSTEM_TABLE_NAME and<\/code><\/code><br>  <code><code>\u00a0a.FORMAT_LEVEL_ID  b.FORMAT_LEVEL_ID<\/code><\/code><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:image {\"id\":441,\"sizeSlug\":\"full\",\"linkDestination\":\"none\"} -->\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"457\" height=\"67\" src=\"https:\/\/fhix.fr\/wp-content\/uploads\/2026\/04\/image-1.png\" alt=\"\" class=\"wp-image-441\" srcset=\"https:\/\/fhix.fr\/wp-content\/uploads\/2026\/04\/image-1.png 457w, https:\/\/fhix.fr\/wp-content\/uploads\/2026\/04\/image-1-300x44.png 300w\" sizes=\"auto, (max-width: 457px) 100vw, 457px\" \/><\/figure>\n<!-- \/wp:image --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2026\/03\/29\/lister-les-fonctions-sql\/\">Lister les fonctions SQL<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p>SELECT * FROM QSYS2.SYSFUNCS<br><br>Pour tester la pr\u00e9sence d&rsquo;une fonction (par exemple QCMDEXC) :<br>SELECT *<br>FROM QSYS2.SYSFUNCS<br>WHERE ROUTINE_NAME = &lsquo;QCMDEXC&rsquo;<\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2026\/02\/10\/recherche-de-source-sur-la-machine\/\">Recherche de source sur la machine<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p>Votre source a \u00e9t\u00e9 supprim\u00e9? <br>Comment v\u00e9rifier si il existe une copie de sauvegarde quelque part sur la machine?<\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p><code><strong>SELECT<\/strong> * <strong>FROM<\/strong> syspartitionstat <strong>WHERE<\/strong> system_table_member = 'MONSOURCE'<\/code><\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2025\/12\/10\/taille-dune-bibliotheque\/\">Taille d&rsquo;une biblioth\u00e8que<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p><code><strong>SELECT SUM<\/strong>(objsize) <strong>FROM<\/strong> <strong>TABLE<\/strong>(QSYS2.OBJECT_STATISTICS('MABIB', '*ALL'))<\/code><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p>Additionner 2 biblioth\u00e8ques :<br><code><strong>VALUES <\/strong>(<strong>SELECT SUM<\/strong>(objsize) <strong>FROM<\/strong> <strong>TABLE<\/strong>(QSYS2.OBJECT_STATISTICS('MABIB', '*ALL'))) +<\/code><br>              <code>(<strong>SELECT SUM<\/strong>(objsize) <strong>FROM<\/strong> <strong>TABLE<\/strong>(QSYS2.OBJECT_STATISTICS('MABIB2', '*ALL')))<\/code><\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2025\/10\/08\/lister-les-bibliotheques-les-tables-et-les-colonnes-avec-sql\/\">Lister les biblioth\u00e8ques, les tables et les colonnes avec SQL<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p><strong><mark style=\"background-color:var(--base-3)\" class=\"has-inline-color has-accent-color\">Lister les biblioth\u00e8ques :<\/mark><\/strong><br><code><strong>SELECT<\/strong> * <strong>FROM<\/strong> sysschemas<\/code><br><br>Par exemple, les 10 plus grosses biblioth\u00e8ques :<br><code><strong>SELECT<\/strong> * <strong>FROM<\/strong> sysschemas <br><strong>ORDER BY<\/strong> schema_size <strong>DESC<\/strong><br><strong>LIMIT<\/strong> 10<\/code><br><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p><strong><mark style=\"background-color:var(--base-3)\" class=\"has-inline-color has-accent-color\">Lister les tables : <\/mark><\/strong><br><code><strong>SELECT<\/strong> * <strong>FROM<\/strong> systables<\/code><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p>Par exemple, lister les tables d&rsquo;une biblioth\u00e8que :<br><code><strong>SELECT<\/strong> * <strong>FROM<\/strong> systables <\/code><br><code><strong>WHERE<\/strong> table_schema ='maBib'<\/code><br><br><br><strong><mark style=\"background-color:var(--base-3)\" class=\"has-inline-color has-accent-color\">Lister les colonnes :<\/mark><\/strong><br><code><strong>SELECT<\/strong> * <strong>FROM<\/strong> syscolumns<\/code><br><br>Lister les colonnes d&rsquo;une table :<br><code><strong>SELECT<\/strong> * <strong>FROM<\/strong> syscolumns <br><strong>WHERE<\/strong> table_name='maTable' <strong>AND<\/strong> table_schema='maBib'<\/code><\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2025\/08\/16\/faire-du-menage-dans-lifs\/\">Faire du m\u00e9nage dans l&rsquo;IFS selon la date des fichiers<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p>Par exemple, supprimer tous les fichiers pdf de plus d&rsquo;1 an dans un r\u00e9pertoire : <br><code><strong>SELECT<\/strong> <\/code><br><code><strong>QCMDEXC<\/strong>('rmvlnk objlnk(''' <strong>CONCAT<\/strong><\/code> <code><strong>TRIM<\/strong>(<strong>CAST<\/strong>(path_name <strong>AS<\/strong> <strong>VARCHAR<\/strong>(200))) <strong>CONCAT<\/strong> ''')')<\/code> <code><strong>FROM TABLE<\/strong>(qsys2.ifs_object_statistics( '<em>\/home\/monRepertoire<\/em>'))<\/code> <br><code><strong>WHERE<\/strong> path_name <strong>LIKE<\/strong> '%.<em>pdf<\/em>'<\/code> <code><strong>AND<\/strong> CREATE_TIMESTAMP &lt; CURRENT_TIMESTAMP - 1 YEARS<\/code><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p><strong>Explications :<\/strong><br>&#8211; <strong>ifs_object_statistics<\/strong> permet de lister les fichiers d&rsquo;un r\u00e9pertoire de l&rsquo;IFS <br>exemple : <br><code>  SELECT CAST(path_name AS VARCHAR(100)) <br>  FROM TABLE(qsys2.ifs_object_statistics('\/home\/monRepertoire'))<\/code><br>&#8211; <strong>RMVLNK<\/strong> est la commande IBMi qui permet la suppression d&rsquo;un fichier de l&rsquo;IFS<br>&#8211; <strong>QCMDEXC<\/strong> permet d&rsquo;ex\u00e9cuter une commande IBMi en SQL<\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2025\/08\/09\/lister-les-tables-dune-bibliotheque-avec-la-derniere-date-dutilisation\/\">Lister les tables d&rsquo;une biblioth\u00e8que avec la derni\u00e8re date d&rsquo;utilisation<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p><code><strong>SELECT<\/strong> <strong>SUBSTR<\/strong>(table_name, 1 , 12) , last_used_timestamp <strong>FROM<\/strong> systablestat <strong>WHERE<\/strong> table_schema = 'MaBibliotheque'<\/code><\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2025\/08\/09\/trouver-le-modele-le-n-de-serie-et-le-nom-de-la-machine\/\">Obtenir le type, le mod\u00e8le, le n\u00b0 de s\u00e9rie, le nom de la machine et le n\u00b0 de partition<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p><code><strong>SELECT<\/strong> machine_type, machine_model, serial_number, host_name, partition_id<\/code><br><code><strong>FROM<\/strong> q<code>sys2.system_status_info<\/code><\/code><\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<li><a class=\"wp-block-latest-posts__post-title\" href=\"https:\/\/fhix.fr\/index.php\/2025\/08\/08\/compter-le-nombre-de-spoules\/\">Compter le nombre de spoules par utilisateur<\/a><div class=\"wp-block-latest-posts__post-full-content\"><!-- wp:paragraph -->\n<p><code><strong>SELECT<\/strong> count(*) <strong>FROM TABLE<\/strong>(qsys2.spooled_file_info(USER_NAME =&gt; 'ProfilAS400'))<\/code><\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p>Si besoin, la commande qui permet de supprimer tous les spoules d&rsquo;un d&rsquo;utilisateur : <br>DLTSPLF FILE(*SELECT) SELECT(ProfilAS400)<\/p>\n<!-- \/wp:paragraph -->\n\n<!-- wp:paragraph -->\n<p>Pour avoir le palmar\u00e8s des utilisateurs avec le plus de spoules :<br><code><strong>SELECT<\/strong> job_user , count(*) <strong>AS<\/strong> nombre<br><strong>FROM TABLE<\/strong>(QSYS2.SPOOLED_FILE_INFO(USER_NAME =&gt;'*'))<br><strong>GROUP BY<\/strong> job_user<br><strong>ORDER<\/strong> BY nombre DESC<\/code><\/p>\n<!-- \/wp:paragraph --><\/div><\/li>\n<\/ul>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-447","page","type-page","status-publish"],"_links":{"self":[{"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/pages\/447","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/comments?post=447"}],"version-history":[{"count":2,"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/pages\/447\/revisions"}],"predecessor-version":[{"id":449,"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/pages\/447\/revisions\/449"}],"wp:attachment":[{"href":"https:\/\/fhix.fr\/index.php\/wp-json\/wp\/v2\/media?parent=447"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}