At one time anyone of us programmer types has executed SQL either interactively with the STRSQL command or with RPG using the SQL cursor.
The RUNSQLSTM statement, an OS/400 command, presents a great opportunity to execute many SQL statements all stacked up within a text file. Several parameters can be set up within the SQL text file by creating a simple RPG program to update a line and column(s) of text with a set of parameters. (See RPG program SQLUPD.) This also could be used within a CLP program to be a database trigger by using the ADDPFTRG, another OS/400 command (Add Physical File Trigger).
I created a physical source file called QSQLSRC and stored my SQL text file within there. This member (SQLFILE) will also be updated with my RPG program for passing parameters to the SQL text.
The statements used within the interactive SQL are slightly different than the ones used within a text file. Following is the SQL text that is executed:
CREATE TABLE MANUCAM
(MSSSN INT, DPSQ INT, DPDB4 INT, DPDBE CHAR(10), DPDN CHAR(30),
DPRL CHAR(5), DPTY CHAR(1), DPST CHAR(1),
MSSTT CHAR(5), MSDH CHAR(10));
(The CREATE command builds an actual physical file, and you can define the fields much in the same manner that you would using DDS.)
INSERT INTO MANUCAM (MSSSN, DPSQ, DPDB4, DPDBE, DPDN, DPRL, DPTY,
DPST, MSSTT, MSDH)
(The INSERT command inserts data into the fields in the file that you have created with the CREATE command. The INSERT, SELECT, and ORDER commands are all part of one statement.)
(The SELECT command is similar to the interactive SELECT where you set up your files, fields, joins, and selection criteria.)
SELECT MSSSN, DPSQ, DPDB4, DPDBE, DPDN, DPRL, DPTY, DPST, MSSTT, MSDH
FROM ABPMS,ABDDP <>
WHERE ABPMS.MSEN = ABDDP.DPEN AND <>
ABPMS."MSER" = 'DEF' AND
ABDDP."DPER" = 'DEF' AND
ABPMS."MSXYZ" = 0 AND
(((ABDDP."DPTY" = '2' AND ABDDP."DPST" = '1') OR
(ABDDP."DPTY" = '1' AND ABDDP."DPST" = '1')) AND
(ABDDP.DPRL = 'D' OR ABDDP.DPRL = 'S' OR
ABDDP.DPRL = 'M' OR ABDDP.DPRL = 'F') )
ORDER BY <>
ABPMS."MSSSN" ASC , ABDDP."DPDB4" ASC;
(The DELETE command is also similar to the interactive DELETE command. This statement is deleting records from the file created with the CREATE command.)
DELETE FROM MANUCAM WHERE MSSTT <> 'FLL'
AND SUBSTR(MSDH,7,4) >= '2000' AND SUBSTR(MSDH,1,2) >= '06';
|_Parms updated by program SQLUPD___|
Note: All statements end with a semicolon (;).
The source for the RPG program SQLUPD that updates the source within the SQL text file.
*************** Beginning of data *************************************
0001.00 *&*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
0002.00 *& *
0003.00 *& UPDATE MONTH AND YEAR IN SQL COMMAND FILE *
0004.00 *& *
0005.00 *& OBJECT NAME - SQLUPD *
0006.00 *& *
0007.00 *& CREATION - DECEMBER 2000 DWIGHT BEECH *
0008.00 *& *
0009.00 *&*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
0010.00 FSQLFILE UF E DISK
0011.00 F* SQLFILE IS A MEMBER OF THE SOURCEFILE CREATED IN QSQLSRC
0012.00 E A 80 1
0013.00 E @MO 2 1
0014.00 E @YR 4 1
0015.00 C*
0016.00 C* CMON AND CYEAR WERE PASSED FROM A CLP PROGRAM USING THE
0017.00 C* RTVSYSVAL COMMAND WITH THE PARAMETERS QMONTH AND QYEAR
0018.00 C*
0019.00 C *ENTRY PLIST
0020.00 C PARM CMON 2
0021.00 C PARM CYEAR 2
0022.00 C*
0023.00 C* SET UP YEAR AND MONTH
0024.00 C*
0025.00 C MOVE CMON IMON 20
0026.00 C MOVE CYEAR IYEAR 40
0027.00 C ADD 2000 IYEAR
0028.00 C*
0029.00 C MOVE IMON MON 2
0030.00 C MOVE IYEAR YEAR 4
0031.00 ***
0032.00 *** THE PARAMETERS TO BE UPDATED ARE ON LINE 23, READ THE
0033.00 *** SQL SOURCE FILE TO ARRIVE AT LINE 23
0034.00 ***
0035.00 C 1 DO 23
0036.00 C READ RSQLFILE 50
0037.00 C END
0038.00 C MOVEA*BLANKS A
0039.00 C MOVEA*BLANKS @MO
0040.00 C MOVEA*BLANKS @YR
0041.00 C MOVEASRCDTA A
0042.00 C MOVEAMON @MO
0043.00 C MOVEAYEAR @YR
0044.00 C MOVEL@YR,1 A,30
0045.00 C MOVEL@YR,2 A,31
0046.00 C MOVEL@YR,3 A,32
0047.00 C MOVEL@YR,4 A,33
0048.00 C MOVEL@MO,1 A,61
0049.00 C MOVEL@MO,2 A,62
0050.00 C MOVEAA SRCDTA
0051.00 C UPDATRSQLFILE
0052.00 C MOVE '1' *INLR
****************** End of data ****************************************
The command RUNSQLSTM is as follows:
Run SQL Statements (RUNSQLSTM)
Type choices, press Enter.
Source file . . . . . . . . . . QSQLSRC Name
Library . . . . . . . . . . . ANYLIB Name, *LIBL, *CURLIB
Source member . . . . . . . . . SQLFILE Name
Commitment control . . . . . . . *CHG *CHG, *ALL, *CS, *NONE...
Naming . . . . . . . . . . . . . *SYS *SYS, *SQL
Bottom
F3=Exit F4=Prompt F5=Refresh F10=Additional parameters F12=Cancel
F13=How to use this display F24=More keys
There are additional parameters, but these are the initial ones that will execute your SQL text file. The nice thing about this is you can use an ODBC-connected software program that will allow you to write or map out your SQL that can be imported into an AS/400 text file for further editing. You can also create extensive joins of data files and execute reports on the fly.
