SQL prêt-à-porter

call CREATE_SQL_SAMPLE('DB2SAMPLE');

set current schema = 'DB2SAMPLE';

-- DB2 for i SERVICE INFO

select SERVICE_CATEGORY "Categoria", SERVICE_NAME "Nome servizio", SERVICE_SCHEMA_NAME "Libreria", SYSTEM_OBJECT_NAME "Nome sistema", SQL_OBJECT_TYPE "Tipo", EARLIEST_POSSIBLE_RELEASE concat ' PTF DB2 liv. ' concat INITIAL_DB2_GROUP_LEVEL "Prerequisito",

  replace(substr(EXAMPLE, 1, position(x'0D', EXAMPLE) -1), '-- Description: ', '') "Descrizione breve"

  --, EXAMPLE "Descrizione completa"

  from SERV_INFO

  order by SERVICE_CATEGORY, SERVICE_NAME;

with NbrTab (NBR) as

 (select 1 NBR from sysibm/sysdummy1

 union all

 select NBR + 1

  from NbrTab

  where NBR + 1 <= 10) -- modificare per estendere il limite superiore della serie

 select NBR from NbrTab;

select T1.EMPNO + 1 as "Codice libero"

  from EMPLOYEE as T1

    exception join EMPLOYEE as  T2

      on int(T1.EMPNO) + 1 = T2.EMPNO

  fetch first 1 rows only;

select generate_unique()

  from sysibm/sysdummy1;

select timestamp(generate_unique())

  from sysibm/sysdummy1;

call qcmdexc('CHGCURLIB $$LIBFS2  ', 20); 

-- da 7.1 non è più necessario il parametro con la lunghezza del comando

call qcmdexc('DSPLIBL *PRINT'); 

-- Retrieve the activity information for all objects within the DB2SAMPLE schema, using a maximum of 10 objects per each activity

call Health_Activity(1, 0, 'DB2SAMPLE', '%', 10, null, null, null);

-- Retrieve the overview for the entire database.

call Health_Database_Overview(1, '%', null, null, null);

call Health_Size_Limits(1, 0, 'DB2SAMPLE', 'E%', 2, null, null, null);

select table_schema, table_name, table_partition, relative_record_number, lock_state, lock_status, lock_scope, job_name,

  (select JOB_STATUS

     from TABLE(ACTIVE_JOB_INFO('NO', '', substr(job_name, locate('/', job_name, 8)+1), '')) AJ where AJ.JOB_NAME = job_name) 

   as JOB_STATUS,

  (select FUNCTION

     from TABLE(ACTIVE_JOB_INFO('NO', '', substr(job_name, locate('/', job_name, 8)+1), '')) AJ where AJ.JOB_NAME = job_name) 

   as FUNCTION

  from RECORD_LOCK_INFO 

  where table_schema = 'DB2SAMPLE' and table_name = 'EMPLOYEE';

    -- statistiche degli oggetti di tipo *FILE della libreria DB2SAMPLE

select *

  from table(OBJECT_STATISTICS('DB2SAMPLE', 'FILE')) as T;

select *

  from SYSCATALOGS;

select TABLE_NAME "File", SYSTEM_TABLE_NAME "Nome sistema", TABLE_TYPE "Tipo", TABLE_TEXT "Descrizione"

  from SYSTABLES

  where TABLE_TYPE not in('L', 'V', 'A') and TABLE_SCHEMA = 'DB2SAMPLE'

  order by TABLE_NAME;

select TABLE_NAME "File", SYSTEM_TABLE_NAME "Nome sistema", TABLE_TYPE "Tipo", TABLE_TEXT "Descrizione"

  from SYSTABLES

  where TABLE_TYPE in('L', 'V') and TABLE_SCHEMA = 'DB2SAMPLE'

  order by TABLE_NAME;

select INDEX_NAME "Indice", SYSTEM_INDEX_NAME "Nome sistema", TABLE_SCHEMA "Lib.file dip.", TABLE_NAME "File dip.", SYSTEM_TABLE_NAME "File dip. (nome sistema)", INDEX_TEXT "Descrizione", IS_UNIQUE "Chiave un."

    order by INDEX_NAME;

select TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE

  from VIEWS

  where TABLE_SCHEMA = 'DB2SAMPLE'

  order by TABLE_NAME;

select COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,

  NUMERIC_PRECISION, NUMERIC_SCALE

  from COLUMNS

  where TABLE_SCHEMA = 'DB2SAMPLE' and TABLE_NAME = 'EMPLOYEE'

  order by ORDINAL_POSITION;

select *

  from table(QSQSYSCOL2('DB2SAMPLE', 'EMPLOYEE')) as DEFFILE

  order by ORDINAL_POSITION;

select C.SYSTEM_TABLE_NAME, SYSTEM_COLUMN_NAME, trim(COLUMN_TEXT) as COLUMN_TEXT, COLUMN_HEADING, DATA_TYPE,

  CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE

  from SYSCOLUMNS c

    inner join SYSTABLES T on (C.SYSTEM_TABLE_SCHEMA, C.SYSTEM_TABLE_NAME) = (T.SYSTEM_TABLE_SCHEMA, T.SYSTEM_TABLE_NAME) and TABLE_TYPE = 'T'

  where C.SYSTEM_TABLE_SCHEMA = 'DB2SAMPLE' 

    and (ucase(COLUMN_HEADING) like '%JOB%')

  order by C.SYSTEM_TABLE_NAME, SYSTEM_COLUMN_NAME;

select CST.TABLE_NAME, CST.CONSTRAINT_NAME, CST.CONSTRAINT_TYPE, CST.CONSTRAINT_KEYS as

  "Key number", CST.CONSTRAINT_STATE as "State", CST.ENABLED, CST.CHECK_PENDING,

  CST.CONSTRAINT_TEXT, COL.COLUMN_NAME, REF.UNIQUE_CONSTRAINT_NAME as "Parent", REF.UPDATE_RULE,

  REF.DELETE_RULE

  from SYSCST as CST left

  join SYSREFCST as REF on CST.CONSTRAINT_SCHEMA = REF.CONSTRAINT_SCHEMA and

    CST.CONSTRAINT_NAME = REF.CONSTRAINT_NAME left

  join SYSCSTCOL as COL on CST.CONSTRAINT_SCHEMA = COL.CONSTRAINT_SCHEMA and

    CST.CONSTRAINT_NAME = COL.CONSTRAINT_NAME left

  join SYSKEYCST as key on COL.CONSTRAINT_SCHEMA = KEY.CONSTRAINT_SCHEMA and

    COL.CONSTRAINT_NAME = KEY.CONSTRAINT_NAME and COL.COLUMN_NAME = KEY.COLUMN_NAME

  where CST.CONSTRAINT_SCHEMA = 'DB2SAMPLE'

  order by CST.TABLE_NAME, CST.CONSTRAINT_TYPE, CST.CONSTRAINT_NAME, KEY.ORDINAL_POSITION;

select TRIGGER_NAME as "Nome trigger", EVENT_OBJECT_SCHEMA "Libreria", EVENT_OBJECT_TABLE as "File", ACTION_ORDER as "Sequenza", EVENT_MANIPULATION as "Evento", ACTION_TIMING as "Time",

-- TCP/IP info client (vista)

select * from TCPIP_INFO;

-- TCP/IP info client (procedura)

select * from table(TCPIP_INFO()) as IP;

select CONNECTION_TYPE, INTERNET_ADDRESS, NETWORK_ADDRESS, SUBNET_MASK, LINE_DESCRIPTION, INTERFACE_STATUS, AUTOSTART

  from NETSTAT_INTERFACE_INFO

  where INTERFACE_STATUS = 'ACTIVE'

  order by INTERNET_ADDRESS;

select CONNECTION_TYPE, LOCAL_ADDRESS, LOCAL_PORT, LOCAL_PORT_NAME, TCP_STATE, NETWORK_CONNECTION_TYPE, IDLE_TIME, BIND_USER, NUMBER_OF_ASSOCIATED_JOBS

  from NETSTAT_INFO

  where LOCAL_ADDRESS = '0.0.0.0'

  order by LOCAL_PORT;

select CONNECTION_TYPE, REMOTE_ADDRESS, REMOTE_PORT, LOCAL_ADDRESS, LOCAL_PORT, LOCAL_PORT_NAME, TCP_STATE, IDLE_TIME, BIND_USER, BYTES_SENT_REMOTELY, BYTES_RECEIVED_LOCALLY, NUMBER_OF_ASSOCIATED_JOBS

  from NETSTAT_INFO

  where LOCAL_ADDRESS not in('0.0.0.0', '127.0.0.1')

  order by LOCAL_PORT, REMOTE_ADDRESS;

select CONNECTION_TYPE, REMOTE_ADDRESS, REMOTE_PORT, LOCAL_ADDRESS, LOCAL_PORT, LOCAL_PORT_NAME, TCP_STATE, IDLE_TIME, BIND_USER, BYTES_SENT_REMOTELY, BYTES_RECEIVED_LOCALLY, NUMBER_OF_ASSOCIATED_JOBS

  from NETSTAT_INFO

  where REMOTE_ADDRESS = (select CLIENT_IP_ADDRESS from TCPIP_INFO)

  order by LOCAL_PORT, REMOTE_ADDRESS;

select CONNECTION_TYPE, REMOTE_ADDRESS, REMOTE_PORT, LOCAL_ADDRESS, LOCAL_PORT, AUTHORIZATION_NAME as USER, JOB_NAME as JOB

  from NETSTAT_JOB_INFO

  where REMOTE_ADDRESS = (select CLIENT_IP_ADDRESS from TCPIP_INFO)

    and JOB_NAME is not null

  order by LOCAL_PORT, JOB_NAME;

select CONNECTION_TYPE, ROUTE_DESTINATION, SUBNET_MASK, NEXT_HOP, ROUTE_STATUS as ROUTE_AVAILABLE, LOCAL_BINDING_INTERFACE, LOCAL_BINDING_NETWORK_ADDRESS, LOCAL_BINDING_SUBNET_MASK, LOCAL_BINDING_LINE_DESCRIPTION, LOCAL_BINDING_VIRTUAL_LAN_ID

  from NETSTAT_ROUTE_INFO

  where ROUTE_STATUS = 'YES'

  order by ROUTE_DESTINATION;

-- info ambiente

  from SYSTEM_VALUE_INFO;

select SYSTEM_POOL_ID "ID lotto", POOL_NAME "Lotto", DESCRIPTION "Descrizione",

    CURRENT_SIZE "Dim. (Mb)", RESERVED_SIZE "Dim.riserv. (Mb)",

    PAGING_OPTION "Opz.paging", 

    MAXIMUM_ACTIVE_THREADS "Lav.att.max",

    CURRENT_THREADS "Lav.att.corrente",

    CURRENT_INELIGIBLE_THREADS "Lav.inel.",

    ELAPSED_DATABASE_FAULTS "DB err", ELAPSED_DATABASE_PAGES "DB pag.",

    ELAPSED_NON_DATABASE_FAULTS "Non DB err", ELAPSED_NON_DATABASE_PAGES "Non DB pag.",

    ELAPSED_ACTIVE_TO_WAIT "Act.->Att.", ELAPSED_WAIT_TO_INELIGIBLE "Wait inel.",

    ELAPSED_ACTIVE_TO_INELIGIBLE "Act.inel."

  from MEMORY_POOL_INFO;

select ASP_NUMBER, DISK_TYPE, DISK_MODEL, UNIT_NUMBER, dec(UNIT_STORAGE_CAPACITY/1073741824,0, 13, 2) as UNIT_STORAGE_CAPACITY_GB, dec(UNIT_SPACE_AVAILABLE/1073741824,0, 13, 2) as UNIT_SPACE_AVAILABLE_GB, PERCENT_USED, LOGICAL_MIRRORED_PAIR_STATUS, MIRRORED_UNIT_STATUS

  from SYSDISKSTAT;

select *

  from LICENSE_INFO

  order by PRODUCT_ID, FEATURE_ID;

select AUTHORIZATION_NAME "Utente", TEXT_DESCRIPTION "Descrizione", PREVIOUS_SIGNON "Ult.collegamento", SIGN_ON_ATTEMPTS_NOT_VALID "Coll.non validi", STATUS "Stato", PASSWORD_CHANGE_DATE "Data/Ora mod.pwd", DAYS_UNTIL_PASSWORD_EXPIRES "gg a scadenza password", SET_PASSWORD_TO_EXPIRE "Pwd a scad.", USER_CLASS_NAME "Classe", SPECIAL_AUTHORITIES "Aut.spec.", LIMIT_CAPABILITIES "Capac.lim.", GROUP_PROFILE_NAME "Profilo di gruppo", OWNER "Propr.", trim(INITIAL_MENU_LIBRARY_NAME) concat '/' concat trim(INITIAL_MENU_NAME) "Menu", trim(INITIAL_PROGRAM_LIBRARY_NAME) concat '/' concat trim(INITIAL_PROGRAM_NAME) "Menu", trim(JOB_DESCRIPTION_LIBRARY_NAME) concat '/' concat trim(JOB_DESCRIPTION_NAME) "Desc.lavoro", trim(OUTPUT_QUEUE_LIBRARY_NAME) concat '/' concat trim(OUTPUT_QUEUE_NAME) "Coda emissione", PRINT_DEVICE "Stampante", HOME_DIRECTORY "Indirizzario corrente"

  from USER_INFO

  order by AUTHORIZATION_NAME;

select *

  from table(get_job_info('*')) as Job;

select MESSAGE_ID, MESSAGE_TYPE, SEVERITY, MESSAGE_TIMESTAMP, MESSAGE_FILE, trim(MESSAGE_TEXT) as MESSAGE_TEXT, trim(MESSAGE_SECOND_LEVEL_TEXT) as MESSAGE_SECOND_LEVEL_TEXT

  from table(JOBLOG_INFO('*')) as JOBLOG

  order by MESSAGE_TIMESTAMP desc;

select 'LL' as "Gruppo", ORDINAL_POSITION as "Pos.", SYSTEM_SCHEMA_NAME "Lib.", TYPE as "Tipo", TEXT_DESCRIPTION as "Descrizione" from LIBRARY_LIST_INFO

 union all

select 'CS', 0, CURRENT SCHEMA as "CurLib", 'CURRENT', ' ' from SYSDUMMY1

 union all

select 'CP', 0, CURRENT PATH as "CurLib", 'PATH', ' ' from SYSDUMMY1

order by 1, 2;

select SUBSYSTEM "Sottosistema", JOB_NAME "Lavoro", AUTHORIZATION_NAME "Utente", JOB_TYPE "Tipo lavoro", FUNCTION_TYPE "Tipo funzione", FUNCTION "Funzione", CPU_TIME "Tempo ut.CPU", TOTAL_DISK_IO_COUNT "Tot. I/O"

  from TABLE(ACTIVE_JOB_INFO('NO', -- reset statistics

                             '',  -- subsystem filter

                             '',  -- job name filter (* current, *ALL tutti, *CURRENT tutti i lavori con nome uguale al corrente, *SBS all active subsystems, *SYS all active system jobs)

                             '' -- current user filter (comma delimited list)

                             )) ACT_JOBS

  where JOB_STATUS = 'MSGW'                           

  order by SUBSYSTEM, JOB_NAME;

select SCHEDULED_JOB_NAME, STATUS, SCHEDULED_TIME, SCHEDULED_DATE_VALUE, SCHEDULED_DATE, SCHEDULED_DAYS, FREQUENCY, RELATIVE_DAYS_OF_MONTH, NEXT_SUBMISSION_DATE, DESCRIPTION, COMMAND_STRING, USER_PROFILE_FOR_SUBMITTED_JOB, JOB_DESCRIPTION_LIBRARY_NAME concat '/' concat JOB_DESCRIPTION_NAME as JOB_DESCRIPTION, LAST_SUCCESSFUL_SUBMISSION_JOB

  from SCHEDULED_JOB_INFO

  order by SCHEDULED_JOB_NAME;

select *

  from REPLY_LIST_INFO;

select MEMBER_LOCK_TYPE, LOCK_STATE, LOCK_STATUS, LOCK_SCOPE, JOB_NAME

  from table(OBJECT_LOCK_INFO('DB2SAMPLE', 'EMPLOYEE', '*FILE', 0)) as OBJECT_LOCK;

select SYSTEM_OBJECT_NAME "Oggetto", MEMBER_LOCK_TYPE, LOCK_STATE, LOCK_STATUS, LOCK_SCOPE, JOB_NAME

  from OBJECT_LOCK_INFO

  where OBJECT_SCHEMA = 'DB2SAMPLE' and SQL_OBJECT_TYPE = 'TABLE';

select *

  from OUTPUT_QUEUE_ENTRIES

  where USER_NAME = 'QSYSOPR';

select PTF_GROUP_NAME, PTF_GROUP_DESCRIPTION, PTF_GROUP_LEVEL, PTF_GROUP_STATUS

  from GROUP_PTF_INO

  order by PTF_GROUP_NAME, PTF_GROUP_LEVEL desc;

select max(PTF_GROUP_LEVEL) as "Most recently install CUM"

select *

  from SYSTOOLS/GROUP_PTF_CURRENCY

  order by PTF_GROUP_CURRENCY desc, PTF_GROUP_ID;

select PTF_GROUP_NAME "Gruppo", trim(PTF_GROUP_DESCRIPTION) "Descrizione", PTF_PRODUCT_ID "Prodotto", trim(PTF_PRODUCT_DESCRIPTION) "Descrizione prodotto", PTF_IDENTIFIER "PTF ID", 

    PTF_STATUS "Stato inst.", PTF_LOADED_STATUS "Stato", varchar_format(PTF_STATUS_TIMESTAMP, 'DD/MM/IYYY') "Data stato", 

    PTF_ACTION_PENDING "In attesa", PTF_ACTION_REQUIRED "Az.richiesta", PTF_IPL_ACTION "Az.IPL", PTF_IPL_REQUIRED "Rich.IPL", 

    APAR_NAME "APAR", PTF_INCLUDED_IN_GROUP_DATE "Incluso in gruppo", PTF_CUM_PACKAGE "Incluso in cumulativo", PTF_IS_RELEASED "Rilasciata", PTF_SUPERCEDED_BY_PTF "Superata da", 

    varchar_format(PTF_CREATION_TIMESTAMP, 'DD/MM/IYYY') "Data creaz."

  from SYSTOOLS/GROUP_PTF_DETAILS

  where PTF_STATUS <> 'PTF MISSING'

  order by PTF_GROUP_NAME, PTF_IDENTIFIER;

select PTF_GROUP_NAME "Gruppo", trim(PTF_GROUP_DESCRIPTION) "Descrizione", PTF_PRODUCT_ID "Prodotto", trim(PTF_PRODUCT_DESCRIPTION) "Descrizione prodotto", PTF_IDENTIFIER "PTF ID", 

    PTF_STATUS "Stato inst.", PTF_LOADED_STATUS "Stato", varchar_format(PTF_STATUS_TIMESTAMP, 'DD/MM/IYYY') "Data stato", 

    PTF_ACTION_PENDING "In attesa", PTF_ACTION_REQUIRED "Az.richiesta", PTF_IPL_ACTION "Az.IPL", PTF_IPL_REQUIRED "Rich.IPL", 

    APAR_NAME "APAR", PTF_INCLUDED_IN_GROUP_DATE "Incluso in gruppo", PTF_CUM_PACKAGE "Incluso in cumulativo", PTF_IS_RELEASED "Rilasciata", PTF_SUPERCEDED_BY_PTF "Superata da", 

    varchar_format(PTF_CREATION_TIMESTAMP, 'DD/MM/IYYY') "Data creaz."

  from SYSTOOLS/GROUP_PTF_DETAILS

  where PTF_STATUS <> 'PTF MISSING' and (PTF_ACTION_PENDING <> 'NO' or PTF_ACTION_REQUIRED not in('NONE', 'IPL') or PTF_IPL_ACTION <> 'NONE')

  order by PTF_GROUP_NAME, PTF_IDENTIFIER;

select EMPNO, SALARY, repeat('*', int(SALARY/1000)) as Grafico

with

 temp1 (EMPNO, SALARY) as

  (select EMPNO, SALARY

     from EMPLOYEE

     where SALARY <> 0)

 select EMPNO, SALARY,

     from temp1, temp2

     order by SALARY desc;