ibm i services for the developer · 2020. 11. 18. · •sql procedure to implement chgdtaara cl...
TRANSCRIPT
![Page 1: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/1.jpg)
© 2020 IBM CorporationWMSUG
IBM i Services for the Developer
—Sue Romano – [email protected]
Db2 for i development
![Page 2: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/2.jpg)
© 2020 IBM CorporationWMSUG
What are IBM i Services
• SQL views, functions, and procedures
• Provide SQL access to system information
• Similar to what CL commands provide
• Similar to what APIs provide
• Use the power of the SQL query engine to access and manipulate this data
![Page 3: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/3.jpg)
© 2020 IBM CorporationWMSUG
Documentation for IBM i Services
• Access directly in Knowledge Center
![Page 4: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/4.jpg)
© 2020 IBM CorporationWMSUG
Documentation for IBM i Services
• Access through the Db2 for i Technology Updates wiki
• http://ibm.biz/Db2foriServices
![Page 5: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/5.jpg)
© 2020 IBM CorporationWMSUG
Program information
5
![Page 6: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/6.jpg)
© 2020 IBM CorporationWMSUG
Examine *PGM and *SRVPGM objects
• DSPPGM and DSPSRVPGM return information for a single object
• Details returned by four views
• PROGRAM_INFO: *BASIC, *SIZE, and *SIGNATURE
• BOUND_MODULE_INFO: *MODULE
• BOUND_SRVPGM_INFO: *SRVPGM
• PROGRAM_EXPORT_IMPORT_INFO: *PROCEXP, *DTAEXP, *ACTGRPEXP, and *ACTGRPIMP
![Page 7: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/7.jpg)
© 2020 IBM CorporationWMSUG
PROGRAM_INFO
• One row for every *PGM and *SRVPGM
• ILE and OPM programs
select activation_group, count(*) as activation_group_countfrom qsys2.program_infowhere program_library = 'APPLIB' and program_type = 'ILE'group by activation_grouporder by 2 desc;
7
![Page 8: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/8.jpg)
© 2020 IBM CorporationWMSUG
BOUND_MODULE_INFO
• One row for each module bound into a program or service program
select optimization_level, count(*) as optimization_level_count
from qsys2.bound_module_infowhere program_library = 'APPLIB'group by optimization_level order by 2 desc;
8
![Page 9: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/9.jpg)
© 2020 IBM CorporationWMSUG
Compare bound module informationwith tst as (select program_name, bound_module,
optimization_level, debug_datafrom qsys2.bound_module_info where program_library = 'TESTLIB'),
prd as (select program_name, bound_module,optimization_level, debug_data
from qsys2.bound_module_infowhere program_library = 'PRODLIB')
select * from tst, prdwhere tst.program_name = prd.program_name
and tst.bound_module = prd.bound_moduleand (tst.optimization_level <> prd.optimization_level or
tst.debug_data <> prd.debug_data);
9
![Page 10: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/10.jpg)
© 2020 IBM CorporationWMSUG
BOUND_SRVPGM_INFO
• One row for every service program bound into a program or service program
select *from qsys2.bound_srvpgm_info
where program_library = 'APPLIB' and program_name like '%RPG%';
10
![Page 11: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/11.jpg)
© 2020 IBM CorporationWMSUG
PROGRAM_EXPORT_IMPORT_INFO
• One row for each data or procedure export and import
select *from qsys2.program_export_import_infowhere program_library = 'QSYS' and
symbol_name = 'Qp0zLprintf';
11
![Page 12: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/12.jpg)
© 2020 IBM CorporationWMSUG
• One row for each *PGM, *SRVPGM, and *MODULE that contains SQL statements
• More complete SQL details than BOUND_MODULE_INFO
SYSPROGRAMSTAT
select * from qsys2.sysprogramstat where program_schema = 'SLR' and
number_external_routines > 0;
![Page 13: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/13.jpg)
© 2020 IBM CorporationWMSUG
• One row for each embedded SQL statement in a *PGM, *SRVPGM, and *MODULE
• Includes statement text and other statement-related details
SYSPROGRAMSTMTSTAT
select * from qsys2.sysprogramstmtstat where program_schema = 'TOYSTORE' and
statement_text like 'UPDATE%' and statement_text like '%EMPLOYEE %';
![Page 14: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/14.jpg)
© 2020 IBM CorporationWMSUG
System interfaces
14
![Page 15: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/15.jpg)
© 2020 IBM CorporationWMSUG
• SQL procedure that executes a CL command
• Use expressions to build the CL command string
QCMDEXC
call qsys2.qcmdexc('CHGJOB CCSID(37)');
call qsys2.qcmdexc('ADDLIBLE ' CONCAT libname);
![Page 16: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/16.jpg)
© 2020 IBM CorporationWMSUG
LPRINTF
• Print to the joblog using SQL
call systools.lprintf('Hello There! Have a great ' concat dayname(current date));
16
![Page 17: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/17.jpg)
© 2020 IBM CorporationWMSUG
LPRINTF – get source for procedure
• Use ACS to extract the procedure source using Insert Generated SQL
17
![Page 18: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/18.jpg)
© 2020 IBM CorporationWMSUG
LPRINTF – get source for procedure
• Select SYSTOOLS procedure LPRINTF
18
![Page 19: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/19.jpg)
© 2020 IBM CorporationWMSUG
LPRINTF – get source for procedure
19
CREATE PROCEDURE SYSTOOLS.LPRINTF (IN PRINT_STRING VARCHAR(1000) CCSID 37 ) SET OPTION BINDOPT = 'BNDSRVPGM(QSYS/QP0ZCPA)'
BEGIN -- Additional setup steps are required before creating this procedure. -- Create a C source member that contains the code for LPRINTF. -- The procedure will embed this with the INCLUDE statement and bind to the appropriate service program using BINDOPT. --CL:CRTSRCPF FILE(QTEMP/QCSRC) RCDLEN(160) MBR(*NONE); --CL:ADDPFM FILE(QTEMP/QCSRC) MBR(LPRINTF); --INSERT INTO QTEMP.QCSRC(SRCSEQ,SRCDTA) VALUES -- (1,'{'), -- (2,'extern int Qp0zLprintf (char *format, ...);'), -- (3,'Qp0zLprintf("%.*s\n",LPRINTF.PRINT_STRING.LEN,LPRINTF.PRINT_STRING.DAT);'), -- (4,'}'); IF PRINT_STRING IS NOT NULL THEN INCLUDE QTEMP / QCSRC ( LPRINTF ) ; END IF ; END ;
![Page 20: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/20.jpg)
© 2020 IBM CorporationWMSUG
Finding messages
20
![Page 21: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/21.jpg)
© 2020 IBM CorporationWMSUG
MESSAGE_FILE_DATA
• Returns one row for each message in a message file
• SQL alternative to the Display Message Description (DSPMSGD) CL command
select * from qsys2.message_file_datawhere message_file_library = 'QSYS' and
message_file = 'QSQLMSG' and(message_text like '%JSON%' ormessage_second_level_text like '%JSON%');
![Page 22: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/22.jpg)
© 2020 IBM CorporationWMSUG
Finding objects
22
![Page 23: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/23.jpg)
© 2020 IBM CorporationWMSUG
OBJECT_STATISTICS
• List details about objects in libraries, like DSPOBJD
• 69 columns of information
o Return all objects in TOYSTORE created at least 2 years ago
23
select *from table(qsys2.object_statistics('TOYSTORE', '*ALL'))where objcreated < current date – 2 years;
![Page 24: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/24.jpg)
© 2020 IBM CorporationWMSUG
OBJECT_STATISTICS
• Return only file names, no extended details, for all files in TOYSTORE
• Return a list of all libraries with no extended details
• Return all the information for a specific file in TOYSTORE
24
select *from table(qsys2.object_statistics('TOYSTORE','*FILE',
'*ALLSIMPLE'));
select * from table(qsys2.object_statistics('*ALLSIMPLE','*LIB'));
select * from table(qsys2.object_statistics('TOYSTORE', '*FILE',
'EMP'));
![Page 25: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/25.jpg)
© 2020 IBM CorporationWMSUG
OBJECT_STATISTICS
• *ALLUSR, *ALLAVL, *ALLUSRAVL, *ALL library options
25
❑*ALL – All libraries❑*ALLAVL – All libraries in all available ASP groups❑*ALLUSRAVL – All user libraries in all available
ASP groups❑*ALLUSR – All user libraries in *SYSBAS and the
current thread's ASP group
select '*ALLUSR' search_style, count(*) lib_countfrom table (qsys2.object_statistics('*ALLUSR', '*LIB')) union
select '*ALLAVL', count(*)from table (qsys2.object_statistics('*ALLAVL', '*LIB')) union
select '*ALLUSRAVL', count(*)from table (qsys2.object_statistics('*ALLUSRAVL', '*LIB')) union
select '*ALL', count(*)from table (qsys2.object_statistics('*ALL', '*LIB'))
order by 2 desc;
![Page 26: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/26.jpg)
© 2020 IBM CorporationWMSUG
OBJECT_STATISTICS
• Which IBM CL commands have had their command parameter defaults changed using CHGCMDDFT
• Identify commands that should have CHGCMDDFT added to QSTRUP
select *from table(qsys2.object_statistics('QSYS', '*CMD'))where APAR_ID = 'CHGDFT';
26
![Page 27: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/27.jpg)
© 2020 IBM CorporationWMSUG
ACS prompting
• Position cursor at location in valid SQL statement
▪ Control+Space
- OR –
▪ F4
![Page 28: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/28.jpg)
© 2020 IBM CorporationWMSUG
• USER_INFO
o List of user profiles (WRKUSRPRF)
• JOB_QUEUE_INFO
o List of job queues (WRKJOBQ)
• SUBSYSTEM_INFO
o List of subsystems (WRKSBS)
• JOB_DESCRIPTION_INFO
o List of job descriptions (WRKJOBD)
Additional object list views
![Page 29: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/29.jpg)
© 2020 IBM CorporationWMSUG
IFS_OBJECT_STATISTICS
• SQL Alternative to RTVDIRINF and WRKLNK
• 87 columns of data returned
• Return object identified by a path name
- OR –
• Return all objects that can be accessed starting with a path name
![Page 30: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/30.jpg)
© 2020 IBM CorporationWMSUG
Search an IFS tree
30
select * from table(qsys2.ifs_object_statistics('/usr')) where path_name like '%.txt'order by path_name;
• Find objects based on a partial name
![Page 31: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/31.jpg)
© 2020 IBM CorporationWMSUG
Integrated File System
31
![Page 32: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/32.jpg)
© 2020 IBM CorporationWMSUG
Read from the IFS
32
• IFS_READ
– character data in job CCSID
• IFS_READ_UTF8
– data converted to UTF-8
• IFS_READ_BINARY
– no conversion
• Three parameters
o Path name – either absolute or relative
o Line length – maximum length for a returned line
o End of line – carriage return and line feed combinations, or none
![Page 33: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/33.jpg)
© 2020 IBM CorporationWMSUG
Read from the IFS
33
select o.path_name, i.*from table (qsys2.ifs_object_statistics(
start_path_name => '/home/SCOTTF',subtree_directories => 'YES',object_type_list => '*ALLSTMF')) o,
table (qsys2.ifs_read(path_name => path_name, end_of_line => 'ANY')) i
where upper(line) like '%IFS%' order by 1,2;
• Search the IFS
![Page 34: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/34.jpg)
© 2020 IBM CorporationWMSUG
Read from the IFS
34
• Search the IFS
![Page 35: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/35.jpg)
© 2020 IBM CorporationWMSUG
Write to the IFS
35
• IFS_WRITE
– data written is in job CCSID
• IFS_WRITE_UTF8
– data written is UTF-8
• IFS_WRITE_BINARY
– no conversion
• Five parameters
o Path name – either absolute or relative
o Line – the data to write
o File CCSID, if creating a new file
o Replace or Append
o End of line – carriage return and line feed combinations, or none
![Page 36: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/36.jpg)
© 2020 IBM CorporationWMSUG
Write to the IFS
36
begincall qsys2.ifs_write(path_name => '/usr/pgms', line => '',
end_of_line => 'NONE', overwrite => 'REPLACE');for select objname, objtype
from table (qsys2.object_statistics('SLR','SRVPGM PGM','*ALLSIMPLE'))
order by objnamedo
call qsys2.ifs_write(path_name => '/usr/pgms', line => cast(objtype as char(7)) concat ' '
concat objname, overwrite => 'APPEND', end_of_line => 'CRLF');
end for;end;
• Write a list of program names to the IFS
![Page 37: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/37.jpg)
© 2020 IBM CorporationWMSUG
Write to the IFS
37
• What did we write?
select * from table (qsys2.ifs_read( 'usr/pgms'));
![Page 38: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/38.jpg)
© 2020 IBM CorporationWMSUG
Data areas
38
![Page 39: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/39.jpg)
© 2020 IBM CorporationWMSUG
DATA_AREA_INFO view
select * from qsys2.data_area_infowhere data_area_library = 'GONZO333';
• Alternative to the Retrieve Data Area (RTVDTAARA) command
![Page 40: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/40.jpg)
© 2020 IBM CorporationWMSUG
DATA_AREA_INFO table function
• QTEMP, *CURLIB, and *LIBL special cases require the UDTF
• Can access *LDA, *PDA, and *GDA data areas
select * from table(qsys2.data_area_info(DATA_AREA_LIBRARY => 'QTEMP',
DATA_AREA_NAME => 'SECRET'));
![Page 41: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/41.jpg)
© 2020 IBM CorporationWMSUG
Change data area
• SQL procedure to implement CHGDTAARA CL command
create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10), data_area_name varchar(10), new_value varchar(2000))
begindeclare cmd_string varchar(2500);set cmd_string = 'CHGDTAARA ' concat data_area_library
concat '/' concat data_area_name concat ' VALUE('''concat new_value concat ''') ';
--call systools.lprintf (cmd_string); -- use for debugcall qsys2.qcmdexc(cmd_string);
end;
![Page 42: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/42.jpg)
© 2020 IBM CorporationWMSUG
Data queues
42
![Page 43: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/43.jpg)
© 2020 IBM CorporationWMSUG
Data Queues – SQL Style
SQL alternatives to Data Queue APIs –• Receive Data Queue Description (QMHQRDQD)• Send Data Queue (QSNDDTAQ)
Data Queue – IBM i Services:
• DATA_QUEUE_INFO – Review the attributes
• DATA_QUEUE_ENTRIES – Look at all the data
• SEND_DATA_QUEUE – Put character data
• SEND_DATA_QUEUE_UTF8 – Put Unicode (JSON) data
• SEND_DATA_QUEUE_BINARY – Put binary data
• RECEIVE_DATA_QUEUE – Extract data
• CLEAR_DATA_QUEUE – Remove data43
• Receive Data Queue (QRCVDTAQ)• Clear Data Queue (QCLRDTAQ)
![Page 44: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/44.jpg)
© 2020 IBM CorporationWMSUG
DATA_QUEUE_INFO view
44
• Details for data queues
o FIFO/LIFO/KEYED
o Key length
o Local or remote data queue
o Number of messages currently on queue
o Maximum messages on queue
![Page 45: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/45.jpg)
© 2020 IBM CorporationWMSUG
Data Queues and SQL
45
cl:CRTDTAQ DTAQ(SCOTTF/SQLCANDOIT) MAXLEN(64000) SENDERID(*YES) SIZE(*MAX2GB) TEXT('DQueue Time');
---- Send character data --call qsys2.send_data_queue(
data_queue_library => 'SCOTTF',data_queue => 'SQLCANDOIT',message_data => 'Ready for the ' concat 'Big time');
• Create a data queue, and send character data to the queue
![Page 46: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/46.jpg)
© 2020 IBM CorporationWMSUG
Data Queues and SQL
46
select * from table(qsys2.receive_data_queue(data_queue_library => 'SCOTTF',data_queue => 'SQLCANDOIT'));
• Retrieve and remove an entry
![Page 47: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/47.jpg)
© 2020 IBM CorporationWMSUG
Data Queues and SQL
47
cl:CRTDTAQ DTAQ(SCOTTF/RMT_SYNC3) TYPE(*DDM) RMTDTAQ(SCOTTF/FROM_SQ740) RMTLOCNAME(*RDB) RDB(SYNC3) TEXT('sq740 to sync3');
---- IBM i to IBM i--call qsys2.send_data_queue(
data_queue_library => 'SCOTTF',data_queue => 'RMT_SYNC3',message_data => 'Sending out an SOS');
• Create a remote data queue, send data to the remote IBM i
![Page 48: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/48.jpg)
© 2020 IBM CorporationWMSUG
Data Queues and RPG
48
DCL-DS Order; Ordno int(10); Customer_number int(10); Order_date timestamp; Ship_date timestamp;
END-DS; DCL-S Order_data char(60) ccsid(65535) based(P_Order); DCL-S P_Order pointer inz(%ADDR(Order));
Exec sql call qsys2.send_data_queue_binary(data_queue_library => 'SCOTTF',data_queue => 'SQLCANDOIT',message_data => :Order_data );
• Send data using an ILE RPG program with Embedded SQL
![Page 49: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/49.jpg)
© 2020 IBM CorporationWMSUG
Data Queues and RPG
49
DCL-DS Order; Ordno int(10); Customer_number int(10); Order_date timestamp; Ship_date timestamp;
END-DS; DCL-S Order_data char(60) ccsid(65535) based(P_Order); DCL-S P_Order pointer inz(%ADDR(Order));
Exec sql select message_data into :Order_datafrom table (qsys2.receive_data_queue_binary (
data_queue_library => 'SCOTTF',data_queue => 'SQLCANDOIT');
• Receive the data into a host variable
![Page 50: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/50.jpg)
© 2020 IBM CorporationWMSUG
DATA_QUEUE_ENTRIES table function
50
• Show all or a subset of a data queue’s content
select * from table (qsys2.data_queue_entries('TESTDQ', 'SLR'));
![Page 51: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/51.jpg)
© 2020 IBM CorporationWMSUG
DATA_QUEUE_ENTRIES table function
51
• Use key selection to subset what is returned
select * from table (qsys2.data_queue_entries('TESTDQ', 'SLR',
selection_type => 'KEY', key_data => ' 1', key_order => 'GT'));
![Page 52: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/52.jpg)
© 2020 IBM CorporationWMSUG
• Clear all messages from a data queue
• Clear messages according to a key value
CLEAR_DATA_QUEUE
52
call qsys2.clear_data_queue(data_queue_library => 'SLR',data_queue => 'DQKEYED',key_data => '0002', key_order => 'EQ');
call qsys2.clear_data_queue(data_queue_library => 'SCOTTF',data_queue => 'SQLCANDOIT');
![Page 53: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/53.jpg)
© 2020 IBM CorporationWMSUG
Spooled output
53
![Page 54: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/54.jpg)
© 2020 IBM CorporationWMSUG
• OUTPUT_QUEUE_INFO
• One row for every output queue
• OUTPUT_QUEUE_ENTRIES & OUTPUT_QUEUE_ENTRIES_BASIC
• One row for every entry in an output queue
• SPOOLED_FILE_DATA
• Returns rows for all the data in a single spooled file
• DELETE_OLD_SPOOLED_FILES• Procedure to remove spooled files
Work with spooled output
![Page 55: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/55.jpg)
© 2020 IBM CorporationWMSUG
OUTPUT_QUEUE_INFO
select * from qsys2.output_queue_infowhere output_queue_library_name = 'QUSRSYS';
![Page 56: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/56.jpg)
© 2020 IBM CorporationWMSUG
OUTPUT_QUEUE_ENTRIES_BASIC
select * from qsys2.output_queue_entries_basic where output_queue_library_name = 'QUSRSYS' and
output_queue_name = 'PRT01' order by size desc limit 5;
![Page 57: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/57.jpg)
© 2020 IBM CorporationWMSUG
SPOOLED_FILE_DATA
select * from table (systools.spooled_file_data('424630/SLROMANO/QPADEV000G', 'RPGDTAQ')) order by ordinal_position;
![Page 58: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/58.jpg)
© 2020 IBM CorporationWMSUG58
DELETE_OLD_SPOOLED_FILES
• Manage spooled files by age• Older than the specified timestamp?
The spooled file is deleted…• Wow, this is easy
-- Preview removal of any spooled file older than 30 days
call systools.delete_old_spooled_files(
delete_older_than => current date - 30 days,
preview => 'YES');
![Page 59: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/59.jpg)
© 2020 IBM CorporationWMSUG59
DELETE_OLD_SPOOLED_FILES
Filter by:• Age• Output queue• User Preview before removing
![Page 60: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/60.jpg)
© 2020 IBM CorporationWMSUG
Debug assistance
60
![Page 61: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/61.jpg)
© 2020 IBM CorporationWMSUG
• Useful tools for difficult debug situations
• Gather information at point of failure to retain state information for later analysis
o JOBLOG_INFO
o STACK_INFO
o LIBRARY_LIST_INFO
o OBJECT_LOCK_INFO
o RECORD_LOCK_INFO
o JOB_LOCK_INFO
o GET_JOB_INFO
o ACTIVE_JOB_INFO
Gathering additional debug information
![Page 62: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/62.jpg)
© 2020 IBM CorporationWMSUG
• Returns all the messages in a joblog for the current job or a specific job
JOBLOG_INFO
select * from table(qsys2.joblog_info('*'));
![Page 63: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/63.jpg)
© 2020 IBM CorporationWMSUG
• Dump the job stack for the current job or a specific job• Either all threads or a specific thread
STACK_INFO
create table coolstuff.stack_dump as
(select * from table (qsys2.stack_info('*'))
where entry_type IN ('ILE', 'OPM')) with data;
![Page 64: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/64.jpg)
© 2020 IBM CorporationWMSUG
• Returns the library list for the current job
LIBRARY_LIST_INFO
select * from qsys2.library_list_info;
![Page 65: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/65.jpg)
© 2020 IBM CorporationWMSUG
• Write the current library list to the joblog.
LIBRARY_LIST_INFO
call systools.lprintf('Library list: ' concat
(select listagg(schema_name, ', ')
within group (order by ordinal_position)
from qsys2.library_list_info));
![Page 66: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/66.jpg)
© 2020 IBM CorporationWMSUG
• Returns information about a single job
• 22 columns of information
GET_JOB_INFO
select * from table (qsys2.get_job_info('399133/QUSER/QSQSRVR'));
![Page 67: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/67.jpg)
© 2020 IBM CorporationWMSUG
• Returns information about active jobs (WRKACTJOB)
• Filtering criteria by job name, subsystem, and user
• 117 columns of information
ACTIVE_JOB_INFO
select * from table(qsys2.active_job_info(job_name_filter=>'QSQSRVR',
detailed_info=>'ALL'));
![Page 68: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/68.jpg)
© 2020 IBM CorporationWMSUG
• Returns one row for every lock on every object
• Use a WHERE clause to limit the result set
OBJECT_LOCK_INFO
select * from qsys2.object_lock_info where object_schema = 'TOYSTORE' and object_name ='SALES';
![Page 69: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/69.jpg)
© 2020 IBM CorporationWMSUG
• Returns one row for every record lock on every file
• Use a WHERE clause to limit the result set
RECORD_LOCK_INFO
select lock_state, rrn, job_name from qsys2.record_lock_info where table_schema = 'TOYSTORE' and table_name = 'SALES'
![Page 70: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/70.jpg)
© 2020 IBM CorporationWMSUG
JOB_LOCK_INFO
• Returns a list of locks held by a job
70
select *
from table(qsys2.job_lock_info('*'))
order by object_type;
![Page 71: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/71.jpg)
© 2020 IBM CorporationWMSUG
• Combine services for more insight
RECORD_LOCK_INFO + GET_JOB_INFO
with lock_conflict_table as (select distinct lock_state, job_namefrom qsys2.record_lock_info where table_schema = 'TOYSTORE' and table_name = 'SALES')
select v_sql_statement_text, v_client_ip_addressfrom lock_conflict_table c,
table(qsys2.get_job_info(c.job_name));
![Page 72: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/72.jpg)
© 2020 IBM CorporationWMSUG
SQL PL – Capture details for lock problems
declare continue handler for sqlstate '57033'begin /* Message SQL0913 object in use */declare msg_schema_name varchar(128);declare msg_table_name varchar(128);declare dot_location integer;declare msg_token varchar(1000); get diagnostics condition 1 msg_token = db2_ordinal_token_1;set dot_location = locate_in_string(msg_token, '.');set msg_schema_name = substr(msg_token, 1, dot_location - 1);set msg_table_name = substr(msg_token, dot_location + 1,
length(msg_token) - dot_location);insert into applib.hard_to_debug_problemsselect * from table (qsys2.joblog_info('*'))
order by ordinal_position desc limit 5;insert into applib.hard_to_debug_lock_problemsselect * from qsys2.object_lock_info
where object_schema = msg_schema_name and object_name = msg_table_name;
end;
![Page 73: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/73.jpg)
© 2020 IBM CorporationWMSUG
dcl-s msg_token char(100); dcl-s msg_schema_name char(128);dcl-s msg_table_name char(128); dcl-s dot_location int(5);if SQLCODE = -913; /* Message SQL0913 object in use */ exec sql get diagnostics condition 1 :msg_token = db2_ordinal_token_1; exec sql set :dot_location = locate_in_string(:msg_token, '.'); exec sql set :msg_schema_name = substr(:msg_token, 1, :dot_location - 1); exec sql set :msg_table_name = substr(:msg_token, :dot_location + 1,
length(:msg_token) - :dot_location); exec sqlinsert into hard_to_debug_problemsselect * from table (qsys2.joblog_info('*'))
order by ordinal_position desc limit 5;exec sqlinsert into hard_to_debug_lock_problemsselect * from qsys2.object_lock_info
where object_schema = :msg_schema_name and object_name = :msg_table_name;
endif;
ILE RPG – Capture details for lock problems
![Page 74: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/74.jpg)
© 2020 IBM CorporationWMSUG
Continue handler to print SQL failure information
declare v_stmt_text clob(50k) ccsid 37;declare continue handler for sqlexceptionbegindeclare local_sqlcode integer;declare local_sqlstate char(5);declare v_message_text varchar(200) ccsid 37;get diagnostics condition 1
local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate, v_message_text = message_text;
call systools.lprintf('APP1 failed with SQLCODE=' concat local_sqlcodeconcat ' SQLSTATE=' concat local_sqlstateconcat ' MESSAGE= ' concat v_message_textconcat ' STMT=' concat v_stmt_text);
end;
74
![Page 75: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/75.jpg)
© 2020 IBM CorporationWMSUG
Additional topics
75
![Page 76: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/76.jpg)
© 2020 IBM CorporationWMSUG
SQL Tutor
76
ibm.biz/Db2foriSQLTutor
• Aggregation of all Gist Examples• Aggregation of all iSee video blogs• Different perspectives to easily
find what you want
![Page 77: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/77.jpg)
Db2 Web Query and IBM i ServicesIBM i Administration Samples folder
– Delivered as part of the Db2 Web Query EZ-Install package
– Sample Reports provided across multiple subject areas
– Learn how you can extend or build new ones
There are other things of interest in EZ-Install
– Query/400 Discovery Tool
– Create Date Dimension Table
– EZ-Report
• Great for using “insert from examples” for FAST creation
of a report over an IBM i Service
Request EZ-Install (or standalone sample administration
reports if you already have Db2 Web Query) by sending an
email to [email protected]
• Include name, email ID, serial number of system
![Page 78: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/78.jpg)
Job Related Samples
![Page 79: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/79.jpg)
PTF Group currency… for multiple systems
PTF Related Reports
79
![Page 80: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/80.jpg)
Storage Related Sample
Temporary storage usage visualization
80
![Page 81: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/81.jpg)
Spool Files
Spool file usage dashboard
81
![Page 82: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/82.jpg)
Create Spreadsheet or PDF from Spoolfile Data
Spool File Information and PDF Generation
82
![Page 83: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/83.jpg)
System Related – Self Refreshing CPU Utilization Dashboard
![Page 84: IBM i Services for the Developer · 2020. 11. 18. · •SQL procedure to implement CHGDTAARA CL command create or replace procedure coolstuff.chgdtaara (data_area_library varchar(10),](https://reader036.vdocuments.us/reader036/viewer/2022071401/60ea752bc1cdbe129d66521e/html5/thumbnails/84.jpg)
© 2020 IBM CorporationWMSUG84