List system values that contain “SEC” in the name.
Take some time and play with the SQL — Become famous Post new samples!!!
1 2 3 4 5 6 | 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!!!
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 | 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); |
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)
1 2 3 4 5 6 7 | SELECT * FROM TABLE (QSYS2.DISPLAY_JOURNAL( 'QSYS' , 'QAUDJRN' , JOURNAL_ENTRY_TYPES=>'DO' , STARTING_RECEIVER_NAME=>'*CURCHAIN' )) AS JDO where job_User = 'AMAPICS' |
Using SQL/RPGLE to get device IP address
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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; |
Get IP Address with RPGLE
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 40 41 | 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 |
show users by the total size of items they own (descending) with RANK()
1 2 3 4 5 | SELECT AUTHORIZATION_NAME, STORAGE_USED, DENSE_RANK() OVER (ORDER BY storage_used DESC ) AS rank FROM QSYS2/USER_STORAGE |
SQL Example to list job log entries
1 2 3 4 5 | 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 |
For Current Job
1 2 3 4 5 | SELECT MESSAGE_TEXT FROM TABLE(QSYS2.JOBLOG_INFO('*')) A WHERE A.MESSAGE_TYPE = 'REQUEST' ORDER BY ORDINAL_POSITION DESC FETCH FIRST 100 ROW ONLY |
Validate email with address
1 2 3 4 5 6 7 | exec sql set :myCount = regexp_count( :myEmail, '^(?:\w+\.?)*\w+@(?:\w+\.)*\w+$' ); if myCount = 1; // email is good else; // email is malformed endif; |
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
1 2 | dcl-s myEmail varchar(254); // longest length of an email address dcl-s myCount uns(10); // the count doodah |
[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