As we run across various uses for SQL we will post them on this page. Please feel free to download the samples, post your own examples & as always post your thoughts.
What is a regular expression
A regular expression is similar to a rule which defines the characters that can appear in an expression. In SQL databases, selecting field values based on regular expressions can be very useful. One example would be to validate credit card numbers or telephone numbers from a given database field. Another would be to check numeric values matching a particular sequence, such as integers containing the digits 99 or 88. SQL server database implementations provide built-in regular expression support.
Retrieve the *current jobs library list
1 2 3 4 |
Extracted data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | SEQUENCE LIBRARY TYPE TEXT_DESCRIPTION 1 QSYSSLIC SYSTEM Command changes for Senior Life 2 QSYS SYSTEM System Library 3 QSYS2 SYSTEM System Library for CPI's 4 QUSRSYS SYSTEM System Library for Users 5 QHLPSYS SYSTEM - 6 QSQL PRODUCT - 7 SRLIFET USER Senior Life Test Library 8 SRLIFE USER Object Library - program/dataarea/dataq/etc.. 9 LEGACYT USER Leagacy Test Library 10 LEGACY USER Legacy Production Library 11 QGPL USER General Purpose Library 12 SLICDTA USER SLIC data library 13 PWL910306D USER Individual G/L Life and Health Data - Release 3.6 14 PWL913306D USER Individual Life and Health Data - Release 3.6 15 PWL913306 USER PWL Individual Life and Health Rel 3.6 16 PWL955301D USER PWL - R3V1 Claims Data Library 17 USS522100 USER ReportPLUS Program Library 18 USS523100 USER IFS MONITER SERVER 19 PWL913307J USER USSI - eSolutions Library |
Retrieve all reply list entries
1 | select * from qsys2.reply_list_info |
Extracted data
1 2 3 4 5 6 7 8 9 10 11 12 13 | SEQUENCE_NUMBER MESSAGE_ID MESSAGE_REPLY COMPARISON_DATA 10 CPA0700 D - 20 RPG0000 D - 30 CBE0000 D - 40 PLI0000 D - 955 CPA5737 C Q1PLIN Q1PCTL |
Retrieve list of Journals
1 | select * from qsys2.journal_info |
Extracted data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | JOURNAL_NAME JOURNAL_LIBRARY ASP_NUMBER JOURNAL_ASPGRP ATTACHED_JOURNAL_REC ATTACHED_JOURNAL_REC MESSAGE_QUEUE MESSAGE_Q EIVER_NAME EIVER_LIBRARY Y QSQJRN ADM 1 *SYSBAS QSQJRN0007 ADM QSYSOPR *LIB TSTJRN CONEB 1 *SYSBAS TSTJRN4440 CONEB QSYSOPR *LIB MTXJRNL DANIEL 1 *SYSBAS MTXJRN0007 DANIEL QSYSOPR QSYS TEMP DANIEL 1 *SYSBAS TEMP0007 DANIEL QSYSOPR QSYS GASJRN GBLAPPSET 1 *SYSBAS GASJRN0001 GBLAPPSET QSYSOPR *LIB LGAJJRN LGAOBJ 1 *SYSBAS LGAJRC0314 LGAOBJ QSYSOPR *LIB LGAJJRN LGAOBJDEV 1 *SYSBAS LGAJRC0318 LGAOBJDEV QSYSOPR *LIB LGAJJRN LGAOBJTST 1 *SYSBAS LGAJRC0315 LGAOBJTST QSYSOPR *LIB LGAPRDJRN LGAPRDOBJ 1 *SYSBAS LGAPRD0114 LGAPRDOBJ QSYSOPR *LIB MEAPRDJRN MEAPRDOBJ 1 *SYSBAS MEAPRD0407 MEAPRDOBJ QSYSOPR *LIB MEASTGJRN MEAPRDOBJ 1 *SYSBAS MEASTG0140 MEAPRDOBJ QSYSOPR *LIB MEAPR2JRN MEAPR2OBJ 1 *SYSBAS MEAPR20135 MEAPR2OBJ QSYSOPR *LIB |
PTF Levels
1 | select * from systools.Group_PTF_CURRENCY |
Extracted data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | PTF_GROUP_CURRENCY PTF_GROUP_ID PTF_GROUP_TITLE PSP INFORMATION NOT AVAILABLE SF99225 IBM OPEN SOURCE SOLUTIONS FOR I PSP INFORMATION NOT AVAILABLE SF99252 CONTENT MANAGER ONDEMAND FOR I - 5770-RD1 7.3 PSP INFORMATION NOT AVAILABLE SF99333 DB2 WEB QUERY FOR I V2.2.0 PSP INFORMATION NOT AVAILABLE SF99433 DB2 WEB QUERY FOR I V2.2.1 PSP INFORMATION NOT AVAILABLE SF99581 WEBSPHERE APP SERVER V8.5 PSP INFORMATION NOT AVAILABLE SF99703 DB2 FOR IBM I PSP INFORMATION NOT AVAILABLE SF99722 IBM HTTP SERVER FOR I PSP INFORMATION NOT AVAILABLE SF99723 PERFORMANCE TOOLS PSP INFORMATION NOT AVAILABLE SF99724 BACKUP RECOVERY SOLUTIONS PSP INFORMATION NOT AVAILABLE SF99725 JAVA PSP INFORMATION NOT AVAILABLE SF99727 TECHNOLOGY REFRESH PSP INFORMATION NOT AVAILABLE SF99728 GROUP SECURITY |
List of Job Descriptions
1 2 3 | SELECT JOB_DESCRIPTION_LIBRARY, JOB_DESCRIPTION, JOB_QUEUE_LIBRARY, JOB_QUEUE, JOB_QUEUE_PRIORITY FROM QSYS2.JOB_DESCRIPTION_INFO |
Extracted data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | JOB_DESCRIPTION_LIBR JOB_DESCRIPTION JOB_QUEUE_LIBRARY JOB_QUEUE JOB_QUEUE_PRIORITY ARY ASNA_DG8 DG8SVCJOBD QGPL QBATCH 5 C3 CURBFILE C3 CURBFILE 5 C3 C3 C3 C3 5 C3 C3_FILE C3 C3_FILE 5 C3 C3STRESS C3 C3STRESS 5 DDORP QLGADEVJD QGPL QBATCH 5 LGAOBJ QLGAJOBD QGPL QBATCH 5 LGAOBJDEV QLGADEVJD QGPL QBATCH 5 LGAOBJTST LGAWSVCTST QGPL QBATCH 5 LGAOBJTST QLGAJOBD QGPL QBATCH 5 LGAPRDOBJ LGAUSRJOBD LGAPRDOBJ LGAJOBQ 5 MEAPRDCNV MEAPROCESS MEAPRDOBJ MEAPRCCTL 5 MEAPRDCNV MEAUSRJOBD MEAPRDOBJ MEAJOBQ 5 MEAPRDOBJ MEAPROCESS MEAPRDOBJ MEAPRCCTL 5 MEAPRDOBJ MEAUSRJOBD MEAPRDOBJ MEAJOBQ 5 MEAPR2CNV MEAPROCESS MEAPRDOBJ MEAPRCCTL 5 MEAPR2CNV MEAUSRJOBD MEAPRDOBJ MEAJOBQ 5 MEAPR2OBJ MEAPROCESS MEAPR2OBJ MEAPRCCTL 5 |
Active jobs using most temporary storage
1 2 3 4 5 6 7 8 9 | SELECT JOB_NAME, AUTHORIZATION_NAME, TEMPORARY_STORAGE, SQL_STATEMENT_TEXT FROM TABLE (QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')) X WHERE JOB_TYPE <> 'SYS' ORDER BY TEMPORARY_STORAGE DESC |
Extracted data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | JOB_NAME AUTHO00001 TEMPO00001 SQL_S00001 699296/AMAPICS/MPXMGRSRV MXAPLUS 2,159 CLOSE CURCSTSYS 699373/QLWISVR/ADMIN2 QLWISVR 589 - 711933/MXAPLUS/JTTSLS MXAPLUS 361 - 699987/MXAPLUS/JTTNMS MXAPLUS 264 - 699375/QWEBADMIN/ADMIN4 QWEBADMIN 176 - 699374/QLWISVR/ADMIN1 QLWISVR 147 - 699305/QDIRSRV/QUSRDIR QDIRSRV 145 - 699377/QLWISVR/ADMIN5 QLWISVR 140 - 699295/AMAPICS/MPXREGSRV MXAPLUS 134 CLOSE CURCSTSYS 711924/MXAPLUS/JTTSLC MXAPLUS 105 - 699156/QSYS/QYPSPFRCOL QSYS 103 - 711949/MXAPLUS/JTTSLA MXAPLUS 101 - 711940/MXAPLUS/JTTSLO MXAPLUS 100 - 699631/MXAPLUS/JTTXAC MXAPLUS 100 - 699976/MXAPLUS/JTTSLI MXAPLUS 87 - 699509/QLWISVR/QINAVMNSRV QLWISVR 81 - 699342/QYPSJSVR/QYPSJSVR QYPSJSVR 80 - 699994/QUSER/QZDASOINIT MXAPLUS 79 INSERT INTO AMCESSQL/LOGTEXT (UID, SEQNO, LOGNAME, LEVEL, 699590/DMCLUSTER/MM DMCLUSTER 73 UPDATE QRECOVERY.QDBRGZ_AMFLIB_TIMFIL_TIMFIL |
Job Queue information
1 2 3 4 5 | SELECT * FROM QSYS2.JOB_QUEUE_INFO WHERE ACTIVE_JOBS IS NOT NULL ORDER BY NUMBER_OF_JOBS DESC |
Extracted data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | JOB_QUEUE_NAME JOB_QUEUE_LIBRARY JOB_QUEUE_STATUS NUMBER_OF_JOBS SUBSYSTEM_NAME SUBSYSTEM_LIBRARY_NA ME DMCLUSTER ICLUSTER RELEASED 0 XDMCLUSTER ICLUSTER AMUJOBQ QGPL RELEASED 0 QBATCH QSYS AXAPSQ QGPL RELEASED 0 QBATCH QSYS AXCTPQ QGPL RELEASED 0 QBATCH QSYS QBATCH QGPL RELEASED 0 QBATCH QSYS QBATCH1 QGPL RELEASED 0 QBATCH QSYS QBATCH2 QGPL RELEASED 0 QBATCH QSYS QBATCH3 QGPL RELEASED 0 QBATCH QSYS QBATCH4 QGPL RELEASED 0 QBATCH QSYS QBATCH5 QGPL RELEASED 0 QBATCH QSYS QBATCH6 QGPL RELEASED 0 QBATCH QSYS QINTER QGPL RELEASED 0 QINTER QSYS QSNADS QGPL RELEASED 0 QSNADS QSYS QSPL QGPL RELEASED 0 QSPL QSYS QS36EVOKE QGPL RELEASED 0 QBATCH QSYS QS36MRT QGPL RELEASED 0 QINTER QSYS QTXTSRCH QGPL RELEASED 0 QBATCH QSYS QZHBHTTP QHTTPSVR RELEASED 0 QHTTPSVR QHTTPSVR |