master
go 374 lines 12.2 KB
Raw
1 // SPDX-License-Identifier: GPL-3.0-or-later
2
3 //go:build cgo
4
5 package as400
6
7 import "fmt"
8
9 const (
10 // VERIFIED: Comprehensive system status queries - works on IBM i 7.4+
11 // The reset variant matches legacy behaviour but clears global statistics.
12 querySystemStatusReset = `SELECT * FROM TABLE(QSYS2.SYSTEM_STATUS(RESET_STATISTICS=>'YES',DETAILED_INFO=>'ALL')) X`
13 querySystemStatusNoReset = `SELECT * FROM TABLE(QSYS2.SYSTEM_STATUS(RESET_STATISTICS=>'NO',DETAILED_INFO=>'ALL')) X`
14
15 // CPU collection using SYSTEM_STATUS with specific columns for hybrid method
16 // TOTAL_CPU_TIME: Monotonic counter in nanoseconds (requires *JOBCTL authority)
17 // ELAPSED_CPU_USED: Average CPU percentage since last statistics reset
18 // ELAPSED_TIME: Seconds since last statistics reset
19 querySystemActivityReset = `SELECT
20 TOTAL_CPU_TIME,
21 ELAPSED_TIME,
22 ELAPSED_CPU_USED
23 FROM TABLE(QSYS2.SYSTEM_STATUS('YES','ALL'))`
24 querySystemActivityNoReset = `SELECT
25 TOTAL_CPU_TIME,
26 ELAPSED_TIME,
27 ELAPSED_CPU_USED
28 FROM TABLE(QSYS2.SYSTEM_STATUS('NO','ALL'))`
29
30 // VERIFIED: Memory pool monitoring using MEMORY_POOL() function
31 // The reset variant matches legacy behaviour; NO leaves system statistics intact.
32 queryMemoryPoolsReset = `
33 SELECT
34 POOL_NAME,
35 CURRENT_SIZE,
36 DEFINED_SIZE,
37 RESERVED_SIZE,
38 CURRENT_THREADS,
39 MAXIMUM_ACTIVE_THREADS
40 FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X
41 WHERE POOL_NAME IN ('*MACHINE', '*BASE', '*INTERACT', '*SPOOL')
42 `
43 queryMemoryPoolsNoReset = `
44 SELECT
45 POOL_NAME,
46 CURRENT_SIZE,
47 DEFINED_SIZE,
48 RESERVED_SIZE,
49 CURRENT_THREADS,
50 MAXIMUM_ACTIVE_THREADS
51 FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'NO')) X
52 WHERE POOL_NAME IN ('*MACHINE', '*BASE', '*INTERACT', '*SPOOL')
53 `
54
55 // VERIFIED: Disk status using SYSDISKSTAT table - works on both systems
56 queryDiskStatus = `
57 SELECT
58 AVG(ELAPSED_PERCENT_BUSY) as AVG_DISK_BUSY
59 FROM QSYS2.SYSDISKSTAT
60 `
61
62 // VERIFIED: Disk instance details using SYSDISKSTAT table - only basic columns
63 // Works on both IBM i 7.4 and pub400.com
64 queryDiskInstances = `
65 SELECT
66 UNIT_NUMBER,
67 UNIT_TYPE,
68 TOTAL_READ_REQUESTS as READ_REQUESTS,
69 TOTAL_WRITE_REQUESTS as WRITE_REQUESTS,
70 ELAPSED_PERCENT_BUSY as PERCENT_BUSY
71 FROM QSYS2.SYSDISKSTAT
72 `
73
74 // VERIFIED: Job queue length using JOB_INFO() function
75 // Works on both IBM i 7.4 and pub400.com
76 queryJobInfo = `
77 SELECT
78 COUNT(*) as JOB_QUEUE_LENGTH
79 FROM TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*JOBQ')) X
80 `
81
82 // VERIFIED: Disk count using SYSDISKSTAT table
83 // Works on both IBM i 7.4 and pub400.com
84 queryCountDisks = `
85 SELECT COUNT(DISTINCT UNIT_NUMBER) as COUNT FROM QSYS2.SYSDISKSTAT
86 `
87
88 // Individual queries for columns that exist in SYSTEM_STATUS() - for backward compatibility
89 queryConfiguredCPUs = `SELECT CONFIGURED_CPUS FROM TABLE(QSYS2.SYSTEM_STATUS()) X`
90 queryAverageCPU = `SELECT AVERAGE_CPU_UTILIZATION FROM TABLE(QSYS2.SYSTEM_STATUS()) X`
91 querySystemASP = `SELECT SYSTEM_ASP_USED FROM TABLE(QSYS2.SYSTEM_STATUS()) X`
92 queryActiveJobs = `SELECT TOTAL_JOBS_IN_SYSTEM FROM TABLE(QSYS2.SYSTEM_STATUS()) X`
93
94 // System information queries for labels - only use verified columns
95 querySerialNumber = `SELECT SERIAL_NUMBER FROM TABLE(QSYS2.SYSTEM_STATUS()) X`
96 querySystemName = `SELECT HOST_NAME FROM QSYS2.SYSTEM_STATUS_INFO`
97 querySystemModel = `SELECT MACHINE_MODEL FROM TABLE(QSYS2.SYSTEM_STATUS()) X`
98
99 // Query for IBM i version - use SYSIBMADM.ENV_SYS_INFO which works across IBM i versions
100 queryIBMiVersion = `SELECT OS_NAME, OS_VERSION, OS_RELEASE FROM SYSIBMADM.ENV_SYS_INFO`
101
102 // Alternative query using data area (fallback if SYSIBMADM.ENV_SYS_INFO doesn't exist)
103 queryIBMiVersionDataArea = `SELECT SUBSTRING(DATA_AREA_VALUE, 1, 8) AS VERSION FROM QSYS2.DATA_AREA_INFO WHERE DATA_AREA_LIBRARY = 'QUSRSYS' AND DATA_AREA_NAME = 'QSS1MRI'`
104
105 // Query for Technology Refresh level - shows TR level (e.g., TR1, TR2, TR3)
106 queryTechnologyRefresh = `
107 SELECT MAX(PTF_GROUP_LEVEL) AS TR_LEVEL
108 FROM QSYS2.GROUP_PTF_INFO
109 WHERE PTF_GROUP_DESCRIPTION = 'TECHNOLOGY REFRESH'
110 AND PTF_GROUP_STATUS = 'INSTALLED'
111 `
112
113 // VERIFIED: Network connections from NETSTAT_INFO
114 // Works on both IBM i 7.4 and pub400.com
115 queryNetworkConnections = `
116 SELECT
117 COUNT(CASE WHEN TCP_STATE = 'ESTABLISHED' AND REMOTE_ADDRESS != '::1' AND REMOTE_ADDRESS != '127.0.0.1' THEN 1 END) as REMOTE_CONNECTIONS,
118 COUNT(*) as TOTAL_CONNECTIONS,
119 COUNT(CASE WHEN TCP_STATE = 'LISTEN' THEN 1 END) as LISTEN_CONNECTIONS,
120 COUNT(CASE WHEN TCP_STATE = 'CLOSE-WAIT' THEN 1 END) as CLOSEWAIT_CONNECTIONS
121 FROM QSYS2.NETSTAT_INFO
122 `
123
124 // Network interface information from NETSTAT_INTERFACE_INFO
125 // Provides interface configuration and status (requires IBM i 7.2 TR3+)
126 queryNetworkInterfaces = `
127 SELECT
128 COALESCE(LINE_DESCRIPTION, 'UNKNOWN') as LINE_DESCRIPTION,
129 COALESCE(INTERFACE_LINE_TYPE, 'UNKNOWN') as INTERFACE_LINE_TYPE,
130 COALESCE(INTERFACE_STATUS, 'UNKNOWN') as INTERFACE_STATUS,
131 COALESCE(CONNECTION_TYPE, 'UNKNOWN') as CONNECTION_TYPE,
132 COALESCE(INTERNET_ADDRESS, '') as INTERNET_ADDRESS,
133 COALESCE(NETWORK_ADDRESS, '') as NETWORK_ADDRESS,
134 COALESCE(MAXIMUM_TRANSMISSION_UNIT, 0) as MAXIMUM_TRANSMISSION_UNIT
135 FROM QSYS2.NETSTAT_INTERFACE_INFO
136 WHERE LINE_DESCRIPTION != '*LOOPBACK'
137 ORDER BY LINE_DESCRIPTION
138 `
139
140 // Count network interfaces for cardinality check
141 queryCountNetworkInterfaces = `
142 SELECT COUNT(*) as COUNT
143 FROM QSYS2.NETSTAT_INTERFACE_INFO
144 WHERE LINE_DESCRIPTION != '*LOOPBACK'
145 `
146
147 // HTTP server monitoring
148 queryHTTPServerInfo = `
149 SELECT
150 SERVER_NAME,
151 HTTP_FUNCTION,
152 SERVER_NORMAL_CONNECTIONS,
153 SERVER_SSL_CONNECTIONS,
154 SERVER_ACTIVE_THREADS,
155 SERVER_IDLE_THREADS,
156 SERVER_TOTAL_REQUESTS,
157 SERVER_TOTAL_RESPONSES,
158 SERVER_TOTAL_REQUESTS_REJECTED,
159 REQUESTS,
160 RESPONSES,
161 NONCACHE_RESPONSES,
162 BYTES_RECEIVED,
163 BYTES_SENT,
164 NONCACHE_PROCESSING_TIME,
165 CACHE_PROCESSING_TIME
166 FROM QSYS2.HTTP_SERVER_INFO
167 `
168
169 queryCountHTTPServers = `
170 SELECT COUNT(*) as COUNT
171 FROM QSYS2.HTTP_SERVER_INFO
172 `
173
174 // VERIFIED: Temporary storage monitoring
175 // Works on both IBM i 7.4 and pub400.com
176 queryTempStorageTotal = `
177 SELECT
178 SUM(BUCKET_CURRENT_SIZE) as CURRENT_SIZE,
179 SUM(BUCKET_PEAK_SIZE) as PEAK_SIZE
180 FROM QSYS2.SYSTMPSTG
181 WHERE GLOBAL_BUCKET_NAME IS NULL
182 `
183
184 queryTempStorageNamed = `
185 SELECT
186 REPLACE(UPPER(REPLACE(GLOBAL_BUCKET_NAME, '*','')), ' ', '_') as NAME,
187 BUCKET_CURRENT_SIZE as CURRENT_SIZE,
188 BUCKET_PEAK_SIZE as PEAK_SIZE
189 FROM QSYS2.SYSTMPSTG
190 WHERE GLOBAL_BUCKET_NAME IS NOT NULL
191 `
192
193 // VERIFIED: Subsystem monitoring
194 // Works on both IBM i 7.4 and pub400.com
195 // Note: HELD_JOB_COUNT and STORAGE_USED_KB columns don't exist, removed per AS400.md verification
196 querySubsystems = `
197 SELECT
198 SUBSYSTEM_DESCRIPTION_LIBRARY || '/' || SUBSYSTEM_DESCRIPTION as SUBSYSTEM_NAME,
199 CURRENT_ACTIVE_JOBS,
200 MAXIMUM_ACTIVE_JOBS
201 FROM QSYS2.SUBSYSTEM_INFO
202 WHERE STATUS = 'ACTIVE'
203 ORDER BY CURRENT_ACTIVE_JOBS DESC
204 `
205
206 queryCountSubsystems = `
207 SELECT COUNT(*) as COUNT
208 FROM QSYS2.SUBSYSTEM_INFO
209 WHERE STATUS = 'ACTIVE'
210 `
211
212 // Aggregated queue totals (expensive queries, run on batch path)
213 queryMessageQueueTotals = `
214 SELECT
215 COUNT(*) AS MESSAGE_COUNT,
216 COUNT(DISTINCT COALESCE(MESSAGE_QUEUE_LIBRARY, '') || '/' || COALESCE(MESSAGE_QUEUE_NAME, '')) AS QUEUE_COUNT
217 FROM QSYS2.MESSAGE_QUEUE_INFO
218 `
219
220 queryJobQueueTotals = `
221 SELECT
222 COUNT(*) AS QUEUE_COUNT,
223 COALESCE(SUM(NUMBER_OF_JOBS), 0) AS JOB_COUNT
224 FROM QSYS2.JOB_QUEUE_INFO
225 WHERE JOB_QUEUE_STATUS = 'RELEASED'
226 `
227
228 queryOutputQueueTotals = `
229 SELECT
230 COUNT(*) AS QUEUE_COUNT,
231 COALESCE(SUM(NUMBER_OF_FILES), 0) AS FILE_COUNT
232 FROM QSYS2.OUTPUT_QUEUE_INFO
233 `
234
235 // Enhanced disk query with all metrics
236 queryDiskInstancesEnhanced = `
237 SELECT
238 UNIT_NUMBER,
239 UNIT_TYPE,
240 PERCENT_USED,
241 UNIT_SPACE_AVAILABLE_GB,
242 UNIT_STORAGE_CAPACITY,
243 TOTAL_READ_REQUESTS,
244 TOTAL_WRITE_REQUESTS,
245 TOTAL_BLOCKS_READ,
246 TOTAL_BLOCKS_WRITTEN,
247 ELAPSED_PERCENT_BUSY,
248 COALESCE(SSD_LIFE_REMAINING, -1) as SSD_LIFE_REMAINING,
249 COALESCE(SSD_POWER_ON_DAYS, -1) as SSD_POWER_ON_DAYS,
250 COALESCE(HARDWARE_STATUS, 'UNKNOWN') as HARDWARE_STATUS,
251 COALESCE(DISK_MODEL, 'UNKNOWN') as DISK_MODEL,
252 COALESCE(SERIAL_NUMBER, 'UNKNOWN') as SERIAL_NUMBER
253 FROM QSYS2.SYSDISKSTAT
254 `
255
256 // Remove all queries that reference non-existent tables/columns:
257 // - MESSAGE_QUEUE_INFO (doesn't exist)
258 // - SUBSYSTEM_INFO (columns don't exist)
259 // - JOB_QUEUE_INFO (columns don't exist)
260 // - IFS_OBJECT_STATISTICS (doesn't exist)
261 // - NETSTAT_INFO (may not exist)
262 // - SYSTMPSTG (may not exist)
263 // - SYSFUNCS (ROUTINE_TYPE column doesn't exist)
264 // - All CPU utilization breakdown columns (don't exist)
265 // - All advanced disk columns (don't exist)
266
267 // Plan cache analysis
268 callAnalyzePlanCache = `CALL QSYS2.ANALYZE_PLAN_CACHE('03', '', '', BX'', '%')`
269 queryPlanCacheSummary = `
270 SELECT
271 HEADING,
272 VALUE
273 FROM QTEMP.QDBOP00003
274 WHERE VALUE IS NOT NULL
275 `
276 )
277
278 func buildMessageQueueQuery(target queueTarget, useTableFunction bool) string {
279 if useTableFunction {
280 return fmt.Sprintf(`
281 SELECT
282 '%[1]s' AS MESSAGE_QUEUE_LIBRARY,
283 '%[2]s' AS MESSAGE_QUEUE_NAME,
284 COUNT(*) AS MESSAGE_COUNT,
285 SUM(CASE WHEN MESSAGE_TYPE = 'INFORMATIONAL' THEN 1 ELSE 0 END) AS INFORMATIONAL_MESSAGES,
286 SUM(CASE WHEN MESSAGE_TYPE = 'INQUIRY' THEN 1 ELSE 0 END) AS INQUIRY_MESSAGES,
287 SUM(CASE WHEN MESSAGE_TYPE = 'DIAGNOSTIC' THEN 1 ELSE 0 END) AS DIAGNOSTIC_MESSAGES,
288 SUM(CASE WHEN MESSAGE_TYPE = 'ESCAPE' THEN 1 ELSE 0 END) AS ESCAPE_MESSAGES,
289 SUM(CASE WHEN MESSAGE_TYPE = 'NOTIFY' THEN 1 ELSE 0 END) AS NOTIFY_MESSAGES,
290 SUM(CASE WHEN MESSAGE_TYPE = 'SENDER COPY' THEN 1 ELSE 0 END) AS SENDER_COPY_MESSAGES,
291 COALESCE(MAX(SEVERITY), 0) AS MAX_SEVERITY
292 FROM TABLE(QSYS2.MESSAGE_QUEUE_INFO(
293 QUEUE_LIBRARY => '%[1]s',
294 QUEUE_NAME => '%[2]s',
295 MESSAGE_FILTER => '*ALL'
296 )) MQ
297 `, target.Library, target.Name)
298 }
299
300 return fmt.Sprintf(`
301 SELECT
302 COUNT(*) AS MESSAGE_COUNT,
303 SUM(CASE WHEN MESSAGE_TYPE = 'INFORMATIONAL' THEN 1 ELSE 0 END) AS INFORMATIONAL_MESSAGES,
304 SUM(CASE WHEN MESSAGE_TYPE = 'INQUIRY' THEN 1 ELSE 0 END) AS INQUIRY_MESSAGES,
305 SUM(CASE WHEN MESSAGE_TYPE = 'DIAGNOSTIC' THEN 1 ELSE 0 END) AS DIAGNOSTIC_MESSAGES,
306 SUM(CASE WHEN MESSAGE_TYPE = 'ESCAPE' THEN 1 ELSE 0 END) AS ESCAPE_MESSAGES,
307 SUM(CASE WHEN MESSAGE_TYPE = 'NOTIFY' THEN 1 ELSE 0 END) AS NOTIFY_MESSAGES,
308 SUM(CASE WHEN MESSAGE_TYPE = 'SENDER COPY' THEN 1 ELSE 0 END) AS SENDER_COPY_MESSAGES,
309 COALESCE(MAX(SEVERITY), 0) AS MAX_SEVERITY
310 FROM QSYS2.MESSAGE_QUEUE_INFO
311 WHERE MESSAGE_QUEUE_LIBRARY = '%[1]s'
312 AND MESSAGE_QUEUE_NAME = '%[2]s'
313 `, target.Library, target.Name)
314 }
315
316 func buildActiveJobQuery(target activeJobTarget) string {
317 return fmt.Sprintf(`
318 SELECT
319 JOB_NAME,
320 JOB_USER,
321 JOB_NUMBER,
322 JOB_STATUS,
323 SUBSYSTEM,
324 JOB_TYPE,
325 ELAPSED_CPU_TIME,
326 ELAPSED_TIME,
327 TEMPORARY_STORAGE,
328 CPU_PERCENTAGE,
329 ELAPSED_INTERACTIVE_TRANSACTIONS,
330 ELAPSED_TOTAL_DISK_IO_COUNT,
331 THREAD_COUNT
332 FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
333 JOB_NAME_FILTER => '%[3]s',
334 SUBSYSTEM_LIST_FILTER => '*ALL',
335 CURRENT_USER_LIST_FILTER => '%[2]s',
336 JOB_STATUS_FILTER => '*ALL',
337 JOB_TYPE_LIST_FILTER => '*ALL',
338 DETAILED_INFO => 'BASIC'
339 )) X
340 WHERE JOB_NUMBER = '%[1]s'
341 `, target.Number, target.User, target.Name)
342 }
343
344 func buildJobQueueQuery(target queueTarget) string {
345 return fmt.Sprintf(`
346 SELECT
347 JOB_QUEUE_STATUS,
348 COALESCE(NUMBER_OF_JOBS, 0) AS NUMBER_OF_JOBS,
349 COALESCE(RELEASED_JOBS, 0) AS RELEASED_JOBS,
350 COALESCE(SCHEDULED_JOBS, 0) AS SCHEDULED_JOBS,
351 COALESCE(HELD_JOBS, 0) AS HELD_JOBS
352 FROM QSYS2.JOB_QUEUE_INFO
353 WHERE JOB_QUEUE_LIBRARY = '%[1]s'
354 AND JOB_QUEUE_NAME = '%[2]s'
355 `, target.Library, target.Name)
356 }
357
358 func buildOutputQueueEntriesQuery(target queueTarget) string {
359 return fmt.Sprintf(`SELECT * FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('%[1]s', '%[2]s', '*NO'))`, target.Library, target.Name)
360 }
361
362 func buildOutputQueueInfoQuery(target queueTarget) string {
363 return fmt.Sprintf(`
364 SELECT
365 OUTPUT_QUEUE_LIBRARY_NAME,
366 OUTPUT_QUEUE_NAME,
367 OUTPUT_QUEUE_STATUS,
368 COALESCE(NUMBER_OF_FILES, 0) AS NUMBER_OF_FILES,
369 COALESCE(NUMBER_OF_WRITERS, 0) AS NUMBER_OF_WRITERS
370 FROM QSYS2.OUTPUT_QUEUE_INFO
371 WHERE OUTPUT_QUEUE_LIBRARY_NAME = '%[1]s'
372 AND OUTPUT_QUEUE_NAME = '%[2]s'
373 `, target.Library, target.Name)
374 }