Information Systems:SQL
Note: This article needs to be expanded on. Heavily.
SQL (Structured Query Language) is a special purpose programming language designed for managing data held in a relational database management system (according to Wikipedia).
It can be used in place of the traditional coding of positioning, reading, writing, updating, and deleting records. The programmer does not have to decide which logical file to read; SQL will automatically select the best one based on the selections made.
An example of an SQL command is a request to list active items for Roy, alphabetically by description –
select * from xxitemp where ittype = ' ' and itstam = ' ' and itresp = 'ROYF' order by itdesc
Messages generated by the SQL optimiser are (to see these, you must start debug before STRSQL) –
**** Starting optimizer debug message for query . All access paths were considered for file XXITEMP. Access path of file XXITEML02 was used by query. The query access plan has been rebuilt. **** Ending debug message for query .
This record was added/updated to file QSYS2/SYSIXADV, which is a record of the indexes that the optimiser determined would be the best, but that didn’t exist.
TIMES LAST_ADVISED TABLE TABLE KEY_COLUMNS_ADVISED
ADVISED NAME SCHEMA
3 2015-02-12-11.18.48.449058 XXITEMP UWDASWVOLD ITSTAM,ITTYPE,ITRESP,ITDESC
The best index didn’t exist, so SQL built one, based on the closest existing one, which was XXITEML02 (keyed by the full description ITDESC). If this SQL was to be run a lot, particularly on a large file, it would run much faster and use less resources, if we built the logical file shown. SQL would automatically detect and use it.
To see this file, run the query SQL in library SHEILA1.
Display Report
Report width . . . . . : 521
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+..
TIMES_ADV LAST_ADVISED TABLE_NAME TABLE_SCHEMA KEY_COLUMNS_ADVISED
1,529,298 2015-04-30-07.16.41.735584 SROORSPL UP1480BFVA OLLINE, OLORNO
1,006,735 2015-04-30-09.02.56.891188 XXITEMP UWDASWPRDD ITDES1
1,004,018 2015-04-30-09.02.08.912312 IOPHDRP UWDASWPRDD IHSTAT, IHCUST
900,569 2015-04-30-07.49.43.072707 RFORFCLG UP1480BFVA RCSROM, RCUSRFAT, RCHAND, RCDATE
830,097 2015-04-30-09.03.00.058729 WSITEMP WEBPRDD ITSUPP, ITDIN
792,597 2015-04-30-07.16.41.735584 WHOSUD UP1480BFVA SDSROM, SDTYPE, SDSGNO, SDSGLI
756,469 2015-04-30-05.12.40.993270 SROORPPL UP1480BFVA OLSTAT, OLLINE, OLORNO, OLORDS
691,517 2015-04-26-13.32.01.266089 QASZRAIRD QUSRSYS COMPID, FULLPATH
586,470 2015-01-05-15.08.04.027398 WHOLTR UP1480BFVA LTSROM, LTPRDC, LTLZON, LTDATE
524,723 2015-04-30-08.10.27.613593 SROBTR UP1480BFVA INREFX
483,976 2015-04-30-09.02.57.427908 XXITEMP UWDASWPRDD ITSTAT, ITPRDC
425,787 2015-04-30-08.53.22.115359 SROISDPL UP1480BFVA IDTYPP, IDAREA, IDIDAT, IDPAGR
396,622 2015-04-30-07.49.43.072707 RFORFCLG UP1480BFVA RCHAND, RCDATE, RCSROM, RCUSRFAT
396,622 2015-04-30-07.49.43.072707 RFORFCLG UP1480BFVA RCHAND, RCDATE, RCSROM, RCBAID
339,796 2015-04-30-09.02.48.980335 OMSADRP UWDASWPRDD OARECP, OAMSGN
329,019 2015-04-30-07.49.43.072707 RFORFCLG UP1480BFVA RCSROM, RCUSRFAT, RCHAND, RCDATE
263,891 2015-04-30-05.12.41.523028 SROPCR UP1480BFVA PCSTAT, PCXRTY, PCNANO, PCXPRC
255,817 2015-04-30-08.55.16.670031 SROORPHE UP1480BFVA OHORNO
F3=Exit F12=Cancel F19=Left F20=Right F21=Split F22=Width 80
This sorts the file in descending sequence by number of times advised. I usually build the requested logical file when the number of times advised gets over a million. After building new logical files, change this query to omit those requests, by going into ‘Select Records’ and changing the last advised date to after the indexes were built.
Select Records
Type comparisons, press Enter. Specify OR to start each new group.
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...
AND/OR Field Test Value (Field, Number, 'Characters', or ...)
LASTADV GT '2015-05-01-00.00.00.000000'
____ _______________ ____ __________________________________
____ _______________ ____ _________________________________
____ _______________ ____ _________________________________
____ _______________ ____ _________________________________
__________________________________________________________________________________
Field Text Len Dec
TIMESADV NUMBER OF TIMES THIS INDEX HAS BEEN ADVISED 18 0
LASTADV LAST TIME THIS ROW WAS UPDATED 26 Z
TBNAME TABLE OVER WHICH AN INDEX IS ADVISED 258 V
DBNAME SCHEMA CONTAINING THE TABLE 10
KEYSADV COLUMN NAMES FOR THE ADVISED INDEX 16000 V
More...
F3=Exit F5=Report F9=Insert F11=Display names only
F12=Cancel F13=Layout F20=Reorganize F24=More keys
The most effective use of SQL is in InfoNet, in the programs that allow the user to key in a combination of selection and summary options. The program builds a single SQL statement, and the optimizer determines the best index to use. Without SQL, the programmer would have to program for each possible combination of options.
For example, in ASW Hotline Inquiry, it took one minute to search for customer ‘11005’ and handler ‘SHEILAV’. The same thing in InfoNet took less than a second. (On the 525.)