IBM i: The SQL Way
Practical IBM i tasks, investigated through SQL.
IBM i native commands remain essential. SQL services give us another way to query the platform, sort the results, isolate outliers, build reports, and turn one-time troubleshooting into reusable automation.
The goal is not to replace native IBM i commands.
The goal is to understand when SQL can help us reach the answer faster and make that knowledge reusable.
Find Active IBM i Jobs Consuming the Most Temporary Storage
Use QSYS2.ACTIVE_JOB_INFO to find and rank active IBM i jobs by temporary-storage usage, then decide where the investigation should continue.
WRKACTJOB SEQ(*TMPSTG)Find IBM i Object Locks and the Jobs Holding Them
Use QSYS2.OBJECT_LOCK_INFO to identify jobs holding or waiting for IBM i object locks, inspect the lock state, and continue the investigation safely.
WRKOBJLCK OBJ(MYLIB/MYFILE) OBJTYPE(*FILE)Find and Analyze IBM i SQL Errors with SQL_ERROR_LOG
Use the SQL Error Logging Facility and QSYS2.SQL_ERROR_LOG to capture, group, and investigate recurring Db2 for i errors with the failing statement, program, job, and call-stack context.
ACS Run SQL Scripts > SQL Error Logging FacilityValidate an IBM i CL Command Without Running It
Use SYSTOOLS.CHECK_COMMAND_SYNTAX to verify the syntax of an IBM i CL command from SQL before storing, scheduling, or conditionally executing it.
QCMDCHK — Check Command Syntax APIQuery IBM i CVEs with SQL
Use SYSTOOLS.CVE_INFO to retrieve current IBM security bulletins for an IBM i release, filter critical and recent CVEs, and build a repeatable vulnerability-review process.
IBM Navigator for i > Security > CVE InformationCheck IBM i Group PTF Currency with a Local XML Feed
Use SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL to compare installed IBM i Group PTF levels with IBM service data from a controlled local XML file.
WRKPTFGRP — Work with PTF GroupsURL-Encode API Parameters with Db2 for i
Use the Db2 for i URL_ENCODE scalar function to safely encode query-string values such as spaces, ampersands, plus signs, and non-ASCII text before calling an HTTP API.
Application URL-encoding libraryConvert an Internal IBM i System Timestamp to an SQL Timestamp
Use FROM_SYSTEM_TIMESTAMP to convert an eight-byte internal IBM i system timestamp into a readable SQL TIMESTAMP value.
Interpret the eight-byte internal system timestamp in application codeConvert an SQL Timestamp to an Internal IBM i System Timestamp
Use TO_SYSTEM_TIMESTAMP to convert an SQL date or timestamp into the eight-byte internal timestamp format used by selected IBM i interfaces.
Build the eight-byte internal timestamp in application codeParse a Qualified IBM i Job Name with SQL
Use SYSTOOLS.JOB_NAME, JOB_USER, JOB_NUMBER, and JOB_NAME_DETAILS to split a qualified IBM i job name into reusable SQL columns.
Parse job-number/job-user/job-name in application codeCreate a Data Journal Reader for an IBM i Table
Use QSYS2.CREATE_DATA_JOURNAL_READER to generate a table function that returns journaled record images as individual columns instead of an unparsed entry-data value.
DSPJRN or QSYS2.DISPLAY_JOURNALMonitor IBM i Geographic Mirroring with SQL
Use QSYS2.GEOGRAPHIC_MIRRORING_INFO to review IASP mirror roles, copy state, synchronization progress, out-of-sync data, tracking space, and replication transport.
PowerHA and geographic mirroring management interfacesFind All IBM i File Overrides in the Current Job
Use SYSTOOLS.OVERRIDE_INFO_ALL to list every database, display, and printer file override active at the current call level of an IBM i job.
DSPOVR FILE(*ALL)View IBM i Service Tools Server Configuration Entries with SQL
Use QSYS2.SERVICE_TOOLS_SERVER_CONFIGURATION_ENTRY_INFO to review stored partition-specific STS LAN configuration entries used during IPL recovery scenarios.
STRSST or DST — Configure Service Tools Server LAN AdapterAdd an IBM i Service Tools Server Configuration Entry with SQL
Use QSYS2.ADD_SERVICE_TOOLS_SERVER_CONFIGURATION_ENTRY to store a partition-specific STS LAN configuration for alternate-hardware IPL and Remote Key Agent recovery scenarios.
STRSST or DST — Configure Service Tools Server LAN AdapterChange an IBM i Service Tools Server Configuration Entry with SQL
Use QSYS2.CHANGE_SERVICE_TOOLS_SERVER_CONFIGURATION_ENTRY to update a stored partition-specific STS LAN recovery configuration without changing the currently active server.
STRSST or DST — Configure Service Tools Server LAN AdapterRemove an IBM i Service Tools Server Configuration Entry with SQL
Use QSYS2.REMOVE_SERVICE_TOOLS_SERVER_CONFIGURATION_ENTRY to delete an obsolete partition-specific STS LAN recovery configuration by UUID.
STRSST or DST — Configure Service Tools Server LAN AdapterInspect an IBM i External Key Manager Configuration with SQL
Use QSYS2.EKM_INFO to read the type, IAM endpoint, service endpoint, and instance ID stored in an IBM i external key manager description file.
DSPEKMD — Display EKM DescriptionRun IBM i CL Commands from SQL and Control Job-Log Output
Use the QSYS2.QCMDEXC scalar function to execute a CL command from SQL, check its return value, and control whether the command text is written to the job log.
QCMDEXC API or CALL QSYS2.QCMDEXCGenerate Excel, CSV, ODS, and Text Files from IBM i SQL
Use SYSTOOLS.GENERATE_SPREADSHEET to export an IBM i table or SQL query to CSV, XLSX, XLS, ODS, or text files in the IFS, including current update and positioning options.
ACS Data Transfer or CLDownloadFind Db2 for i Tables, Indexes, and Views with SQL
Use QSYS2.SYSTABLES, QSYS2.SYSINDEXES, and QSYS2.SYSVIEWS to identify database objects, map long SQL names to IBM i system names, and troubleshoot object-name collisions.
WRKOBJ and DSPFDInspect IBM i Objects with QSYS2.OBJECT_STATISTICS
Use QSYS2.OBJECT_STATISTICS to query native IBM i objects, connect long SQL names to system object names, filter by object type, and troubleshoot objects that are difficult to locate through SQL catalogs alone.
WRKOBJ, DSPOBJD, and DSPFDDiagnose Db2 for i Cross-Reference Problems with QSYS.QADBXREF
Use QSYS.QADBXREF to compare Db2 for i long and system file names, identify suspicious cross-reference entries, and use RCLDBXREF safely when catalog metadata becomes inconsistent.
RCLDBXREF OPTION(*CHECK)