Using SQL to retrieve system information

List system values that contain “SEC” in the name.

Take some time and play with the SQL — Become famous Post new samples!!!

SELECT SYSTEM_VALUE_NAME as sysvalue,
ifnull(CURRENT_NUMERIC_VALUE,0) as DecValue,
CURRENT_CHARACTER_VALUE as CharValue
FROM SYSTEM_VALUE_INFO
where
SYSTEM_VALUE_NAME LIKE '%SEC%'

this would produce the listing:

Lets now add to an RPG program:
*note — i know this is not the correct way to retrieve a timestamp — used for example only!!!
[cc lang=”php”]
H dftactgrp( *no ) OPTION(*NODEBUGIO) ACTGRP(‘GETSTAMP’)
*
d GETSTAMP pr
d ReturnValue 20
*
d GETSTAMP pi
d Returnvalue 20
*
d CharacterValue…
d s 30 inz
d NumericValue…
d s 10i 0 inz
d SystemValueName…
d s 10

*inlr = *on;

exec sql
SELECT SYSTEM_VALUE_NAME as sysvalue,
ifnull(CURRENT_NUMERIC_VALUE,0) as DecValue,
CURRENT_CHARACTER_VALUE as CharValue
into :SystemValueName,
:NumericValue,
:CharacterValue
FROM SYSTEM_VALUE_INFO
where
SYSTEM_VALUE_NAME = ‘QDATETIME’;

ReturnValue = %trim(CharacterValue);
[/cc]

Grabbing audit journal entries
The audit journal entries are assigned a SYSLOG_SEVERITY value in the following way:
Severity 2 Critical condition

• SV – System value when QAUDCTL is changed to *NONE Severity 4 Warning condition
• AF – Authority failure
• GR – Generic record, when function usage was checked and failed for a function name with a prefix of QIBM_DB_ Severity 5 Notice: A normal but significant condition
• AD – Auditing changes
• AX – Row and column access control
• CA – Authority changes
• CP – User profile changed, created, or restored
• DS – DST security password reset
• OM – Object move or rename
• OW – Object ownership changed
• PG – Change of an object’s primary group
• PW – Invalid password
• RA – Authority change during restore
• RO – Change of object owner during restore
• RU – Restoring user profile authority
• RZ – Change a primary group during restore
• SO – Server security user information actions Severity 6 Informational message
• CD – Command string audit
• CO – Create object
• DO – Delete object
• GR – Generic record, except for the Severity 4 case where function usage was checked and failed
• GS – Socket description was given to another job
• LD – Link, unlink, or look up directory entry
• OR – Object restore
• PA – Program changed to adopt authority
• RJ – Restoring job description with user profile specified
• RP – Restoring adopted authority program
• SE – Subsystem routing entry changed
• ST – Use of service tools
• SV – System value changed, except for QAUDCTL severity 2 case
• ZC – Object accessed (change)
• ZR – Object accessed (read)

SQL to view transactions from a journal receiver (QAUDJRN)

[cc lang=”php”]
SELECT *
FROM TABLE (QSYS2.DISPLAY_JOURNAL(
‘QSYS’ , ‘QAUDJRN’ ,
JOURNAL_ENTRY_TYPES=>’DO’ ,
STARTING_RECEIVER_NAME=>’*CURCHAIN’
)) AS JDO
where job_User = ‘AMAPICS’
[/cc]

Using SQL/RPGLE to get device IP address
[cc lang=”php”]
H dftactgrp( *no ) OPTION(*NODEBUGIO) ACTGRP(‘GETIP’)
*
d GETIP pr
d ReturnIP 15
*
d GETIP pi
d ReturnIP 15
*
d MyIpAddress s 15 inz

*inlr = *on;

exec sql
select
sysibm.CLIENT_IPADDR
into :MyIpAddress
FROM SYSIBM.SYSDUMMY1;

clear MyIpAddress;
exec sql values(sysibm.CLIENT_IPADDR) into :MyIpAddress;
returnIP = MyIpaddress;

[/cc]

Get IP Address with RPGLE
[cc lang=”php”]

d Format s 8A Inz(‘DEVD0600’)
d ipaddress s 15a
d Rcvar S 5000A Inz
d Varlen S 10i 0 Inz(5000)

d $getipaddress pr extpgm(‘QDCRDEVD’)
d rcvar 5000
d varlen 10i 0
d format 8
d @job 10
d apierror 256

//
// Program Info
//
d SDS
d @JOB 244 253

d APIError ds Qualified
d BytesP 1 4I 0 inz(%size(apiError))
d BytesA 5 8I 0 inz(0)
d Messageid 9 15
d Reserved 16 16
d messagedta 17 256

/free

$getipaddress( rcvar :
varlen :
format :
@job :
Apierror
);
ipaddress = %subst( rcvar:878:15);
dsply ipaddress ‘ ‘;

*inlr = *on;
/end-free
[/cc]

show users by the total size of items they own (descending) with RANK()

[cc lang=”SQL”]
SELECT AUTHORIZATION_NAME, STORAGE_USED,
DENSE_RANK()
over (order by storage_used desc ) as rank
from
QSYS2/USER_STORAGE
[/cc]

SQL Example to list job log entries

[cc lang=”SQL”]
SELECT MESSAGE_TEXT FROM
TABLE(QSYS2.JOBLOG_INFO(‘366139/JFLANARY/QPADEV0008’)) A
WHERE A.MESSAGE_TYPE = ‘REQUEST’
ORDER BY ORDINAL_POSITION DESC
FETCH FIRST 100 ROW ONLY
[/cc]

For Current Job
[cc lang=”SQL”]
SELECT MESSAGE_TEXT FROM
TABLE(QSYS2.JOBLOG_INFO(‘*’)) A
WHERE A.MESSAGE_TYPE = ‘REQUEST’
ORDER BY ORDINAL_POSITION DESC
FETCH FIRST 100 ROW ONLY
[/cc]

Validate email with address
[cc lang=”php”]
exec sql set :myCount = regexp_count( :myEmail, ‘^(?:\w+\.?)*\w+@(?:\w+\.)*\w+$’ );

if myCount = 1;
// email is good
else;
// email is malformed
endif;
[/cc]

EDITED to mention that – I use varchar wherever possible. If you are using fixed length fields you will need to add a %TRIM(:myemail) to remove *blanks

[cc lang=”php”]
dcl-s myEmail varchar(254); // longest length of an email address
dcl-s myCount uns(10); // the count doodah
[/cc]

[wordpress_file_upload multiple=”false” fitmode=”responsive” captcha=”true” captchatype=”RecaptchaV2 (no account)” postlink=”true”]

Follow Link for more examples:
IBM i Technology Updates