Snippet Sql 2
Come ottenere lista colonne primary_key e constraint di una tabella
ELECT
A.TABLE_SCHEMA "SCHEMA", A.TABLE_NAME, A.CONSTRAINT_TYPE "TYPE",
C.ORDINAL_POSITION "POS", A.CONSTRAINT_NAME, C.COLUMN_NAME
FROM QSYS2.SYSCST A
LEFT OUTER JOIN QSYS2.SYSREFCST B
ON A.CONSTRAINT_SCHEMA = B.CONSTRAINT_SCHEMA
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
LEFT OUTER JOIN QSYS2.SYSKEYCST C
ON A.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA
AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE A.SYSTEM_TABLE_SCHEMA = 'MFXDWHF'
Result:
Casi in cui può essere utile:
--- SCHEMA TABLE_NAME TYPE POS CONSTRAINT_NAME COLUMN_NAME MFXDWHF XMI_FGM_0201_01_ABS_DC PRIMARY KEY 1 Q_MFXDWHF_ZDIY020101_GUUID_00001 INT_SVCTRID_GUUID MFXDWHF XMI_FGM_0201_01_ABS_DC PRIMARY KEY 2 Q_MFXDWHF_ZDIY020101_GUUID_00001 INT_STRD_GUUID_C MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 1 XMI_FGM_0201_01_ABS_DC_CU INT_SVCTRID_GUUID MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 2 XMI_FGM_0201_01_ABS_DC_CU CHAIN_CODE MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 3 XMI_FGM_0201_01_ABS_DC_CU SUBCHAIN_CODE MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 4 XMI_FGM_0201_01_ABS_DC_CU DEST_STORE MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 5 XMI_FGM_0201_01_ABS_DC_CU ORIG_STORE MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 6 XMI_FGM_0201_01_ABS_DC_CU ORIG_CHAIN_CODE MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 7 XMI_FGM_0201_01_ABS_DC_CU ORIG_SUBCHAIN_CODE MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 8 XMI_FGM_0201_01_ABS_DC_CU TRANSACTION_DATE MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 9 XMI_FGM_0201_01_ABS_DC_CU NUMDOC MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 10 XMI_FGM_0201_01_ABS_DC_CU SKU_CODE MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 11 XMI_FGM_0201_01_ABS_DC_CU WIDTH_CODE MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 12 XMI_FGM_0201_01_ABS_DC_CU WH_SIZE MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 13 XMI_FGM_0201_01_ABS_DC_CU ACC_DOC_NUM MFXDWHF XMI_FGM_0201_01_ABS_DC UNIQUE 14 XMI_FGM_0201_01_ABS_DC_CU ACC_DOC_DATE ---
Come ottenere elenco Routines
SELECT
SPECIFIC_SCHEMA,
SPECIFIC_NAME,
ROUTINE_SCHEMA,
ROUTINE_NAME,
ROUTINE_TYPE
FROM QSYS2.SYSROUTINES
WHERE SPECIFIC_SCHEMA='SQLJ'
Result:
--- SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE SQLJ DB2_INSTALL_JAR SQLJ DB2_INSTALL_JAR PROCEDURE SQLJ DB2_REPLACE_JAR SQLJ DB2_REPLACE_JAR PROCEDURE SQLJ DB2_UPDATEJARINFO SQLJ DB2_UPDATEJARINFO PROCEDURE SQLJ INSTALL_JAR SQLJ INSTALL_JAR PROCEDURE SQLJ RECOVERJAR SQLJ RECOVERJAR PROCEDURE SQLJ REFRESH_CLASSES SQLJ REFRESH_CLASSES PROCEDURE SQLJ REMOVE_JAR SQLJ REMOVE_JAR PROCEDURE SQLJ REPLACE_JAR SQLJ REPLACE_JAR PROCEDURE SQLJ SQLJ_PROFILE SQLJ SQLJ_PROFILE PROCEDURE SQLJ UPDATEJARINFO SQLJ UPDATEJARINFO PROCEDURE ---
Come ottenere elenco Trigger
SELECT
TRIGGER_SCHEMA,
TRIGGER_NAME,
EVENT_MANIPULATION,
EVENT_OBJECT_SCHEMA,
EVENT_OBJECT_TABLE
FROM QSYS2.SYSTRIGGER
WHERE TRIGGER_SCHEMA='LAKEVIEW'
Result:
--- TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE LAKEVIEW QSYS_TRIG_LAKEVIEW___LM2000P____000001 DELETE LAKEVIEW LM2000P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM2000P____000002 INSERT LAKEVIEW LM2000P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM2000P____000003 UPDATE LAKEVIEW LM2000P LAKEVIEW QSYS_TRIG_LAKEVIEW___CM0200P____000001 DELETE LAKEVIEW CM0200P LAKEVIEW QSYS_TRIG_LAKEVIEW___CM0200P____000002 INSERT LAKEVIEW CM0200P LAKEVIEW QSYS_TRIG_LAKEVIEW___CM0200P____000003 UPDATE LAKEVIEW CM0200P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3100P____000001 DELETE LAKEVIEW LM3100P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3100P____000002 INSERT LAKEVIEW LM3100P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3100P____000003 UPDATE LAKEVIEW LM3100P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3100P____000004 INSERT LAKEVIEW LM3100P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3100P____000005 UPDATE LAKEVIEW LM3100P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3200P____000001 DELETE LAKEVIEW LM3200P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3200P____000002 INSERT LAKEVIEW LM3200P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3200P____000003 UPDATE LAKEVIEW LM3200P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3300P____000001 DELETE LAKEVIEW LM3300P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3300P____000002 INSERT LAKEVIEW LM3300P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3300P____000003 UPDATE LAKEVIEW LM3300P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3400P____000001 DELETE LAKEVIEW LM3400P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3400P____000002 INSERT LAKEVIEW LM3400P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3400P____000003 UPDATE LAKEVIEW LM3400P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3400P____000004 INSERT LAKEVIEW LM3400P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM0110P____000001 DELETE LAKEVIEW LM0110P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM0110P____000002 INSERT LAKEVIEW LM0110P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM0110P____000003 UPDATE LAKEVIEW LM0110P LAKEVIEW QSYS_TRIG_LAKEVIEW___LM3400P____000005 UPDATE LAKEVIEW LM3400P ---
Come ottenere elenco Journal
SELECT
JOURNAL_NAME,
JOURNAL_LIBRARY,
ASP_NUMBER,
JOURNAL_ASPGRP,
ATTACHED_JOURNAL_RECEIVER_NAME,
ATTACHED_JOURNAL_RECEIVER_LIBRARY
FROM QSYS2.JOURNAL_INFO
WHERE JOURNAL_LIBRARY='QUSRSYS'
Result:
--- JOURNAL_NAME JOURNAL_LIBRARY ASP_NUMBER JOURNAL_ASPGRP ATTACHED_JOURNAL_RECEIVER_NAME ATTACHED_JOURNAL_RECEIVER_LIBRARY QAOSDIAJRN QUSRSYS 1 *SYSBAS QAOSDI1154 QUSRSYS QASOSCFG QUSRSYS 1 *SYSBAS QASOSC1151 QUSRSYS QDSNX QUSRSYS 1 *SYSBAS QDSNX1150 QUSRSYS QLZALOG QUSRSYS 1 *SYSBAS QLZALO1150 QUSRSYS QMAJRN QUSRSYS 1 *SYSBAS QMAJRN1150 QUSRSYS QPMCCCAJRN QUSRSYS 1 *SYSBAS QPMCCA0057 QUSRSYS QSNADS QUSRSYS 1 *SYSBAS QSNADS1152 QUSRSYS QSXJRN QUSRSYS 1 *SYSBAS QSXJRN1366 QUSRSYS QSZRAIR QUSRSYS 1 *SYSBAS QSZRAI1150 QUSRSYS QTOVDBJRN QUSRSYS 1 *SYSBAS QTOVDB0054 QUSRSYS QVPN QUSRSYS 1 *SYSBAS QVPN0145 QUSRSYS QZCAJRN QUSRSYS 1 *SYSBAS QZCAJR1150 QUSRSYS QZMF QUSRSYS 1 *SYSBAS QZMF1151 QUSRSYS ---
Come ottenere Elenco Colonne di una Tabella con il formato
SELECT
SYSTEM_TABLE_NAME,
TABLE_NAME,
ORDINAL_POSITION "Op",
COLUMN_NAME,
SYSTEM_COLUMN_NAME,
CONCAT(DATA_TYPE,CONCAT(CONCAT('(',LENGTH),')')) "Data_Type"
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_NAME ='GN_DWH_LOG'
ORDER BY TABLE_NAME, ORDINAL_POSITION
Result:
--- SYSTEM_TABLE_NAME TABLE_NAME Op COLUMN_NAME SYSTEM_COLUMN_NAME Data_Type GN_DWH_LOG GN_DWH_LOG 1 GL_ID GL_ID BIGINT(8) GN_DWH_LOG GN_DWH_LOG 2 GL_GUUID GL_GUUID VARCHAR(128) GN_DWH_LOG GN_DWH_LOG 3 GL_MSG_TYPE GL_MS00001 VARCHAR(20) GN_DWH_LOG GN_DWH_LOG 4 GL_INT_ID GL_INT_ID VARCHAR(128) GN_DWH_LOG GN_DWH_LOG 5 GL_STEP GL_STEP VARCHAR(128) GN_DWH_LOG GN_DWH_LOG 6 GL_MSG GL_MSG VARCHAR(1024) GN_DWH_LOG GN_DWH_LOG 7 GL_TS GL_TS TIMESTMP(10) ---
