TIPS AS400 IBMi SQL SYSTEM

  • Lister les dates et utilisateurs de création

    SELECT OBJLIB as bibliotheque ,
    OBJNAME as objet , OBJTYPE as type , OBJDEFINER as createur
    , OBJCREATED as date_creation ,
    OBJTEXT as description

    FROM TABLE(QSYS2.OBJECT_STATISTICS(‘AXFILE’,’*ALL’))
    ORDER
    BY OBJtype, objname

  • Comparer 2 bibliothèques pour chercher les fichiers mis à niveau

    Cette requête SQL permet de mettre en évidence les différences de niveaux entre 2 bibliothèques :
    SELECT
    a.SYSTEM_TABLE_NAME as fichier,
    a.SYSTEM_TABLE_SCHEMA as bib1, a.FORMAT_LEVEL_ID as niv1,
    b.SYSTEM_TABLE_SCHEMA as bib2, b.FORMAT_LEVEL_ID as niv2,
    (a.FORMAT_LEVEL_ID b.FORMAT_LEVEL_ID) as upgraded
    FROM sysfiles a , sysfiles b  
    WHERE
    a.system_table_schema = 'MABIB1' and 
    b.system_table_schema = 'MABIB2' and
    a.native_type = 'PHYSICAL' and 
    a.SYSTEM_TABLE_NAME = b.SYSTEM_TABLE_NAME 

    Pour n’afficher que les fichiers qui ont changé de niveau :
    SELECT
    a.SYSTEM_TABLE_NAME as fichier,
    a.SYSTEM_TABLE_SCHEMA as bib1, a.FORMAT_LEVEL_ID as niv1,
    b.SYSTEM_TABLE_SCHEMA as bib2, b.FORMAT_LEVEL_ID as niv2
    FROM sysfiles a , sysfiles b  
    WHERE
    a.system_table_schema = 'MABIB1' and 
    b.system_table_schema = 'MABIB2' and
    a.native_type = 'PHYSICAL' and 
    a.SYSTEM_TABLE_NAME = b.SYSTEM_TABLE_NAME and
     a.FORMAT_LEVEL_ID b.FORMAT_LEVEL_ID

  • Lister les fonctions SQL

    SELECT * FROM QSYS2.SYSFUNCS

    Pour tester la présence d’une fonction (par exemple QCMDEXC) :
    SELECT *
    FROM QSYS2.SYSFUNCS
    WHERE ROUTINE_NAME = ‘QCMDEXC’

  • Recherche de source sur la machine

    Votre source a été supprimé?
    Comment vérifier si il existe une copie de sauvegarde quelque part sur la machine?

    SELECT * FROM syspartitionstat WHERE system_table_member = 'MONSOURCE'

  • Taille d’une bibliothèque

    SELECT SUM(objsize) FROM TABLE(QSYS2.OBJECT_STATISTICS('MABIB', '*ALL'))

    Additionner 2 bibliothèques :
    VALUES (SELECT SUM(objsize) FROM TABLE(QSYS2.OBJECT_STATISTICS('MABIB', '*ALL'))) +
    (SELECT SUM(objsize) FROM TABLE(QSYS2.OBJECT_STATISTICS('MABIB2', '*ALL')))

  • Lister les bibliothèques, les tables et les colonnes avec SQL

    Lister les bibliothèques :
    SELECT * FROM sysschemas

    Par exemple, les 10 plus grosses bibliothèques :
    SELECT * FROM sysschemas
    ORDER BY schema_size DESC
    LIMIT 10

    Lister les tables :
    SELECT * FROM systables

    Par exemple, lister les tables d’une bibliothèque :
    SELECT * FROM systables
    WHERE table_schema ='maBib'


    Lister les colonnes :
    SELECT * FROM syscolumns

    Lister les colonnes d’une table :
    SELECT * FROM syscolumns
    WHERE table_name='maTable' AND table_schema='maBib'

  • Faire du ménage dans l’IFS selon la date des fichiers

    Par exemple, supprimer tous les fichiers pdf de plus d’1 an dans un répertoire :
    SELECT
    QCMDEXC('rmvlnk objlnk(''' CONCAT TRIM(CAST(path_name AS VARCHAR(200))) CONCAT ''')') FROM TABLE(qsys2.ifs_object_statistics( '/home/monRepertoire'))
    WHERE path_name LIKE '%.pdf' AND CREATE_TIMESTAMP < CURRENT_TIMESTAMP - 1 YEARS

    Explications :
    – ifs_object_statistics permet de lister les fichiers d’un répertoire de l’IFS
    exemple :
    SELECT CAST(path_name AS VARCHAR(100))
    FROM TABLE(qsys2.ifs_object_statistics('/home/monRepertoire'))
    – RMVLNK est la commande IBMi qui permet la suppression d’un fichier de l’IFS
    – QCMDEXC permet d’exécuter une commande IBMi en SQL

  • Lister les tables d’une bibliothèque avec la dernière date d’utilisation

    SELECT SUBSTR(table_name, 1 , 12) , last_used_timestamp FROM systablestat WHERE table_schema = 'MaBibliotheque'

  • Obtenir le type, le modèle, le n° de série, le nom de la machine et le n° de partition

    SELECT machine_type, machine_model, serial_number, host_name, partition_id
    FROM qsys2.system_status_info

  • Compter le nombre de spoules par utilisateur

    SELECT count(*) FROM TABLE(qsys2.spooled_file_info(USER_NAME => 'ProfilAS400'))

    Si besoin, la commande qui permet de supprimer tous les spoules d’un d’utilisateur :
    DLTSPLF FILE(*SELECT) SELECT(ProfilAS400)

    Pour avoir le palmarès des utilisateurs avec le plus de spoules :
    SELECT job_user , count(*) AS nombre
    FROM TABLE(QSYS2.SPOOLED_FILE_INFO(USER_NAME =>'*'))
    GROUP BY job_user
    ORDER BY nombre DESC