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.

Regular Expressions

Retrieve the *current jobs library list

1
2
3
4
SELECT trim(substr(ORDINAL_POSITION,1,10)),
trim(substr(SCHEMA_NAME,1,10)),
"TYPE"
TEXT_DESCRIPTION FROM qsys2.library_List_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
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