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
[cc lang=”php”]
SELECT trim(substr(ORDINAL_POSITION,1,10)),
trim(substr(SCHEMA_NAME,1,10)),
“TYPE”
TEXT_DESCRIPTION FROM qsys2.library_List_info
[/cc]
Extracted data
[cc lang=”php”]
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
[/cc]
Retrieve all reply list entries
[cc lang=”php”]
select * from qsys2.reply_list_info
[/cc]
Extracted data
[cc lang=”php”]
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
[/cc]
Retrieve list of Journals
[cc lang=”php”]
select * from qsys2.journal_info
[/cc]
Extracted data
[cc lang=”php”]
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
[/cc]
PTF Levels
[cc lang=”php”]
select * from systools.Group_PTF_CURRENCY
[/cc]
Extracted data
[cc lang=”php”]
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
[/cc]
List of Job Descriptions
[cc lang=”php”]
SELECT JOB_DESCRIPTION_LIBRARY, JOB_DESCRIPTION,
JOB_QUEUE_LIBRARY, JOB_QUEUE, JOB_QUEUE_PRIORITY
FROM QSYS2.JOB_DESCRIPTION_INFO
[/cc]
Extracted data
[cc lang=”php”]
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
[/cc]
Active jobs using most temporary storage
[cc lang=”php”]
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
[/cc]
Extracted data
[cc lang=”php”]
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
[/cc]
Job Queue information
[cc lang=”php”]
SELECT * FROM QSYS2.JOB_QUEUE_INFO
WHERE ACTIVE_JOBS IS NOT NULL
ORDER BY NUMBER_OF_JOBS DESC
[/cc]
Extracted data
[cc lang=”php”]
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
[/cc]