{"id":142,"date":"2016-03-19T11:29:51","date_gmt":"2016-03-19T10:29:51","guid":{"rendered":"https:\/\/solidt.eu\/blog\/?p=142"},"modified":"2025-06-27T07:36:51","modified_gmt":"2025-06-27T06:36:51","slug":"iseries-queries-to-explore-the-unknown","status":"publish","type":"post","link":"https:\/\/solidt.eu\/site\/iseries-queries-to-explore-the-unknown\/","title":{"rendered":"AS400 iSeries: Queries to explore the unknown"},"content":{"rendered":"\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Current sql context info\nselect \n\tcurrent_server server,\n\tcurrent_schema schema, \t \n\tuser user, \n\tcurrent_path path, \n\tcurrent_date date, \n\tcurrent_time time, \n\tcurrent_timezone timezone \nfrom sysibm.sysdummy1;\n\n-- Show catalog (collections)\nselect * from QSYS2.SYSCATALOGS;\n\n-- Show schemas (databases)\nselect * from QSYS2.SYSSCHEMAS;\n\n-- Show table info of a schema (tables)\nselect * from qsys2.systables where table_schema = CURRENT_SCHEMA\n\n\n-- Show column info of a table (columns)\nselect COLUMN_NAME, COLUMN_TEXT, DATA_TYPE, LENGTH, IS_NULLABLE\nfrom QSYS2.SYSCOLUMNS sc\nwhere sc.TABLE_SCHEMA = CURRENT_SCHEMA and sc.TABLE_NAME = 'R12C'\nORDER BY COLUMN_NAME\n\n-- Show Primary\/Foreign keys (constraints)\nSELECT * FROM QSYS2.SYSKEYCST WHERE TABLE_SCHEMA = CURRENT_SCHEMA\n\nSELECT\n    *\nFROM\n    QSYS2.SYSCST\nWHERE\n    TABLE_SCHEMA = CURRENT_SCHEMA\n    AND TABLE_NAME = 'MYTABLE'\n\n-- Show Primary\/Foreign keys (constraints) with column info\n\nSELECT\n    *\nFROM\n    QSYS2.SYSCSTCOL\nWHERE\n    TABLE_SCHEMA = CURRENT_SCHEMA\n    AND TABLE_NAME = 'MYTABLE'\n\n\n-- Show view definition of a view\nSELECT VIEW_DEFINITION\n FROM QSYS2.SYSVIEWS\n WHERE TABLE_NAME = 'V_R12C' and TABLE_SCHEMA = CURRENT_SCHEMA\n \n -- Create a view\n CREATE OR REPLACE VIEW V_INCOTERM AS SELECT * FROM QS36F.R12C\n -- Drop view\n -- DROP VIEW QS36F.R12C\n\n-- RUN a native iSeries command\nCALL QSYS2.qcmdexc('CHKPWD   PASSWORD(JOHNJONES)')\n\n-- Get program info\nselect * FROM QSYS2.PROGRAM_INFO WHERE PROGRAM_NAME LIKE 'AMS%' LIMIT 20\nSELECT * FROM QSYS2.SYSPROGRAMSTAT\n\n-- return table with static\/dynamic values\nVALUES 1, 2, 3, 4\nselect * from table(VALUES 1, 2, 3, 4)\n\nVALUES varchar(get_clob_from_file('test.txt'), 1000)\n\n\n\n-- Using (global) variables\n-- Session scope: Global variables have a session scope. \n-- This means that although they are available to all sessions that are active on the database, their value is private for each session.\n\n-- create\nCREATE OR REPLACE VARIABLE QS36F.PARAM_P2 CHAR(435);\n\n-- set variable value\nSET QS36F.PARAM_P2 = 'TEST';\n\n-- show variable value\nVALUES (QS36F.PARAM_P2)\n\n-- list all variables (variable system table)\nSELECT * FROM QSYS2.SYSVARS\n\n\n-- Drop variable\nDROP VARIABLE QS36F.PARAM_P2;\nDROP VARIABLE IF EXISTS QS36F.PARAM_P2;\n\n-- query\/show all variables\nSELECT * FROM QSYS2.SYSVARIABLES\n\n\nSELECT 'DROP VARIABLE ' || v.VARIABLE_SCHEMA || '.' || v.VARIABLE_NAME || ';' FROM QSYS2.SYSVARIABLES v WHERE v.VARIABLE_OWNER = 'ME'\n\n\n-- from: https:\/\/www.rpgpgm.com\/2016\/05\/global-variables-way-to-share-data-in.html\n-- with a system name\nCREATE VARIABLE MYLIB\/GLOBALVAR1 FOR SYSTEM NAME GLBLVAR001 CHAR(30)\nSELECT * FROM (VALUES(MYLIB.GLOBALVAR1)) VARIABLES(VAR1)\n\n\n<\/pre>\n\n\n\n<p>Source: <a href=\"https:\/\/itpscan.ca\/blog\/iSeries\/sql2.php\">https:\/\/itpscan.ca\/blog\/iSeries\/sql2.php<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.ibm.com\/support\/knowledgecenter\/en\/ssw_ibm_i_74\/db2\/rbafzcatalogtbls.htm\">https:\/\/www.ibm.com\/support\/knowledgecenter\/en\/ssw_ibm_i_74\/db2\/rbafzcatalogtbls.htm<\/a><\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SYSCOLUMNSTAT               Information about column statistics with some interesting usage and deletion statistics\nSYSINDEXES                  Information about indexes\nSYSPACKAGE                  Information about packages in the SQL schema. Packages are control-structure database objects \n                            that contain executable forms of SQL statements\nSYSSEQUENCES                Information about sequences. Information about columns that use \"GENERATED AS IDENTITY\" clause\nSYSTABLEDEP                 Information about materialized query table dependencies\nSYSTABLEINDEXSTAT           Information about table index statistics\nSYSTABLESTAT                Information about table statistics\nSYSTYPES                    Information about built-in data types and distinct types\nSYSJARCONTENTS              Information about jars for Java\u2122 routines.\nSYSJAROBJECTS               Information about jars for Java routines.\nSYSPARMS                    Information about routine parameters\n\n* Views and Indexes\nSYSVIEWS                    Information about definition of a view\nSYSVIEWDEP                  Information about view dependencies on tables\nSYSINDEXES                  List of all Indexes\nSYSINDEXSTAT                Statistics of the Indexes\n\n* Triggers\nSYSTRIGCOL                  Information about columns used in a trigger\nSYSTRIGDEP                  Information about objects used in a trigger\nSYSTRIGGERS                 Information about triggers\nSYSTRIGUPD                  Information about columns in the WHEN clause of a trigger\n\n* Constraints\nSYSCST                      Information about all constraints\nSYSCHKCST                   Information about check constraints\nSYSCSTCOL                   Information about the columns referenced in a constraint\nSYSCSTDEP                   Information about constraint dependencies on tables\nSYSREFCST                   Information about referential constraints\nSYSKEYCST                   Information about unique, primary, and foreign keys\nSYSKEYS                     Information about index keys\nSYSFOREIGNKEYS\t\t\t\tInformation about foreign keys\n\n* Procedures and User defined functions\nSYSPROCS                    Information about procedures\nSYSROUTINES                 Information about functions and procedures\nSYSROUTINEDEP               Information about function and procedure dependencies\nSYSFUNCS                    Information about user-defined functions\n\n* Statistics\nSYSPARTITIONINDEXSTAT       Information about partition index statistics\nSYSPARTITIONSTAT            Information about partition statistics<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Source: https:\/\/itpscan.ca\/blog\/iSeries\/sql2.php https:\/\/www.ibm.com\/support\/knowledgecenter\/en\/ssw_ibm_i_74\/db2\/rbafzcatalogtbls.htm<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[8],"tags":[],"class_list":["post-142","post","type-post","status-publish","format-standard","hentry","category-other-scripts"],"_links":{"self":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/142","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/comments?post=142"}],"version-history":[{"count":23,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/142\/revisions"}],"predecessor-version":[{"id":9607,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/142\/revisions\/9607"}],"wp:attachment":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/media?parent=142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/categories?post=142"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/tags?post=142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}