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
Get the top (by size) 100 spooled files on IBMi
SELECT * FROM QSYS2.OUTPUT_QUEUE_ENTRIES
ORDER BY SIZE DESC
FETCH FIRST 100 ROWS ONLY
Pingback:The new Code400 for RPG Developers