-- Current sql context info
select 
	current_server server,
	current_schema schema, 	 
	user user, 
	current_path path, 
	current_date date, 
	current_time time, 
	current_timezone timezone 
from sysibm.sysdummy1;

-- Show catalog (collections)
select * from QSYS2.SYSCATALOGS;

-- Show schemas (databases)
select * from QSYS2.SYSSCHEMAS;

-- Show table info of a schema (tables)
select * from qsys2.systables where table_schema = CURRENT_SCHEMA


-- Show column info of a table (columns)
select COLUMN_NAME, COLUMN_TEXT, DATA_TYPE, LENGTH, IS_NULLABLE
from QSYS2.SYSCOLUMNS sc
where sc.TABLE_SCHEMA = CURRENT_SCHEMA and sc.TABLE_NAME = 'R12C'
ORDER BY COLUMN_NAME

-- Show Primary/Foreign keys (constraints)
SELECT * FROM QSYS2.SYSKEYCST WHERE TABLE_SCHEMA = CURRENT_SCHEMA

SELECT
    *
FROM
    QSYS2.SYSCST
WHERE
    TABLE_SCHEMA = CURRENT_SCHEMA
    AND TABLE_NAME = 'MYTABLE'

-- Show Primary/Foreign keys (constraints) with column info

SELECT
    *
FROM
    QSYS2.SYSCSTCOL
WHERE
    TABLE_SCHEMA = CURRENT_SCHEMA
    AND TABLE_NAME = 'MYTABLE'


-- Show view definition of a view
SELECT VIEW_DEFINITION
 FROM QSYS2.SYSVIEWS
 WHERE TABLE_NAME = 'V_R12C' and TABLE_SCHEMA = CURRENT_SCHEMA
 
 -- Create a view
 CREATE OR REPLACE VIEW V_INCOTERM AS SELECT * FROM QS36F.R12C
 -- Drop view
 -- DROP VIEW QS36F.R12C

-- RUN a native iSeries command
CALL QSYS2.qcmdexc('CHKPWD   PASSWORD(JOHNJONES)')

-- Get program info
select * FROM QSYS2.PROGRAM_INFO WHERE PROGRAM_NAME LIKE 'AMS%' LIMIT 20
SELECT * FROM QSYS2.SYSPROGRAMSTAT

-- return table with static/dynamic values
VALUES 1, 2, 3, 4
select * from table(VALUES 1, 2, 3, 4)

VALUES varchar(get_clob_from_file('test.txt'), 1000)



-- Using (global) variables
-- Session scope: Global variables have a session scope. 
-- This means that although they are available to all sessions that are active on the database, their value is private for each session.

-- create
CREATE OR REPLACE VARIABLE QS36F.PARAM_P2 CHAR(435);

-- set variable value
SET QS36F.PARAM_P2 = 'TEST';

-- show variable value
VALUES (QS36F.PARAM_P2)

-- list all variables (variable system table)
SELECT * FROM QSYS2.SYSVARS


-- Drop variable
DROP VARIABLE QS36F.PARAM_P2;
DROP VARIABLE IF EXISTS QS36F.PARAM_P2;

-- query/show all variables
SELECT * FROM QSYS2.SYSVARIABLES


SELECT 'DROP VARIABLE ' || v.VARIABLE_SCHEMA || '.' || v.VARIABLE_NAME || ';' FROM QSYS2.SYSVARIABLES v WHERE v.VARIABLE_OWNER = 'ME'


-- from: https://www.rpgpgm.com/2016/05/global-variables-way-to-share-data-in.html
-- with a system name
CREATE VARIABLE MYLIB/GLOBALVAR1 FOR SYSTEM NAME GLBLVAR001 CHAR(30)
SELECT * FROM (VALUES(MYLIB.GLOBALVAR1)) VARIABLES(VAR1)

Source: https://itpscan.ca/blog/iSeries/sql2.php

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/db2/rbafzcatalogtbls.htm

SYSCOLUMNSTAT               Information about column statistics with some interesting usage and deletion statistics
SYSINDEXES                  Information about indexes
SYSPACKAGE                  Information about packages in the SQL schema. Packages are control-structure database objects 
                            that contain executable forms of SQL statements
SYSSEQUENCES                Information about sequences. Information about columns that use "GENERATED AS IDENTITY" clause
SYSTABLEDEP                 Information about materialized query table dependencies
SYSTABLEINDEXSTAT           Information about table index statistics
SYSTABLESTAT                Information about table statistics
SYSTYPES                    Information about built-in data types and distinct types
SYSJARCONTENTS              Information about jars for Java™ routines.
SYSJAROBJECTS               Information about jars for Java routines.
SYSPARMS                    Information about routine parameters

* Views and Indexes
SYSVIEWS                    Information about definition of a view
SYSVIEWDEP                  Information about view dependencies on tables
SYSINDEXES                  List of all Indexes
SYSINDEXSTAT                Statistics of the Indexes

* Triggers
SYSTRIGCOL                  Information about columns used in a trigger
SYSTRIGDEP                  Information about objects used in a trigger
SYSTRIGGERS                 Information about triggers
SYSTRIGUPD                  Information about columns in the WHEN clause of a trigger

* Constraints
SYSCST                      Information about all constraints
SYSCHKCST                   Information about check constraints
SYSCSTCOL                   Information about the columns referenced in a constraint
SYSCSTDEP                   Information about constraint dependencies on tables
SYSREFCST                   Information about referential constraints
SYSKEYCST                   Information about unique, primary, and foreign keys
SYSKEYS                     Information about index keys
SYSFOREIGNKEYS				Information about foreign keys

* Procedures and User defined functions
SYSPROCS                    Information about procedures
SYSROUTINES                 Information about functions and procedures
SYSROUTINEDEP               Information about function and procedure dependencies
SYSFUNCS                    Information about user-defined functions

* Statistics
SYSPARTITIONINDEXSTAT       Information about partition index statistics
SYSPARTITIONSTAT            Information about partition statistics

14200cookie-checkAS400 iSeries: Queries to explore the unknownno