Brief: Although every AS/400 has a relational database, OS/400 doesn't facilitate access to Structured Query Language (SQL) without the SQL/400 licensed program. The Execute SQL Statement (EXCSQLSTM) command presented here provides a command interface that allows easy access to the power of SQL.
If there is a need to access data from a relational database, you often see Structured Query Language (SQL) used. SQL was created to work with relational databases and it's one of the few common threads in client/server computing. SQL is a very English-like language. For example, the SQL statement to retrieve the customer name from a customer file might be SELECT CUSNME FROM CUSTFILE. SQL is a nonprocedural language which reduces the number of statements required to accomplish a task.
Although an SQL engine is provided as an integral part of OS/400, it is missing an interactive interface, report-writing functions, and a precompiler for embedding SQL statements in high-level language (HLL) application programs. To use these functions and perform application development, you must purchase the SQL/400 licensed program (the V3R1 version has been renamed DB2/400 Query Manager and SQL Development Kit).
For those AS/400 sites that can't justify the cost of the SQL licensed program, there is an alternative. The Execute SQL Statement (EXCSQLSTM) command presented here gives you access to OS/400's SQL engine in a limited, but powerful way. In the February 1992 issue of MC, a similar command called Run SQL Statement (RUNSQLSTM) was published. The EXCSQLSTM is an improved version of RUNSQLSTM.
What EXCSQLSTM Can Do
Almost any SQL statement can be specified with the EXCSQLSTM command. For example, you can create a table (a physical file) and update, delete, or insert rows (records) within the table. You can also create an index (a logical file) or select and sort records. EXCSQLSTM can be run from a command line or from a program, or it can be submitted to batch using the Submit Job (SBMJOB) command.
A powerful feature of the command is its ability to run SQL statements stored in source members. You can save SQL statements that you use repetitively, and the command can retrieve them at run time. The command also allows you to invoke SEU, so you can easily create, modify, and save SQL statements.
If you select records with the SQL SELECT statement, you can direct the output to the display, to a printer, or to an outfile. If output is directed to an outfile, you can specify a member name and whether you want to replace or add records.
Working with EXCSQLSTM
You can use the EXCSQLSTM command in two ways. You can execute an SQL statement contained in a source member, or you can pass a literal SQL statement through the SQL statement (SQLSTM) parameter. The EXCSQLSTM command prompt is illustrated in 1.
You can use the EXCSQLSTM command in two ways. You can execute an SQL statement contained in a source member, or you can pass a literal SQL statement through the SQL statement (SQLSTM) parameter. The EXCSQLSTM command prompt is illustrated in Figure 1.
By default, the command looks for an SQL statement from a source member because the default value of the SQLSTM parameter is *SOURCE. The command assumes your SQL statements are stored in a source physical file named QQMQRYSRC. This is the standard source file name used by IBM for Query Management query source members.
You can edit the query source member with SEU before the SQL statement executes by specifying a value of *YES in the Edit query source member (EDIT) parameter (see 1).
You can edit the query source member with SEU before the SQL statement executes by specifying a value of *YES in the Edit query source member (EDIT) parameter (see Figure 1).
If you direct the result of an SQL SELECT statement to an outfile, you can specify the qualified file name to be created. The default file name is SQLOUT, and the default library is QTEMP. Ad-ditionally, you can specify the member name to use and whether records are appended to or replaced in the member. The default member is the first member (*FIRST), and the default member option is to replace records (*REPLACE).
If you pass a literal SQL statement and use the command prompter, the statement cannot be more than 512 characters, since the largest input field the command prompter supplies is 512 characters. However if you execute the command without the prompter, you can enter a much larger SQL statement of up to 2,750 characters.
If you don't use the command prompter (F4) when you run the EXCSQLSTM command, you must enclose your SQL statement in single quotes. Any quotes used in the SQL statement therefore must be doubled. For example, the following SQL statement:
SELECT * FROM VENDORFILE WHERE STATE = 'CA' would be specified in the SQLSTM parameter as: 'SELECT * FROM VENDORFILE WHERE STATE = ''CA'''
An even more powerful way to use the EXCSQLSTM command is to embed it in a program. For example, you can use EXCSQLSTM in place of OPNQRYF to create a subset of a file in a particular order.
How it Works
The secret behind EXCSQLSTM is Query Management/400 (QM/400). This OS/400 facility allows you to compile SQL statements contained in a source member (type QMQRY) to a QM/400 query object (*QMQRY). The QM/400 query object can then be run with the Start Query Management Query (STRQMQRY) command.
The SQL statements used to create a QM/400 query object can also contain substitution variables. Values can be passed to these variables through the STRQMQRY command. The article, "Query Management Magic," MC, November 1994 shows how a complete SQL statement can be passed to a QM/400 query through a substitution variable. Pushing this technique a little further allows the EXCSQLSTM command to work.
EXCSQLSTM uses a QM/400 query, GENQRY, made up of 50 substitution variables. 2 illustrates this source member. As you can see, there are 50 variables defined (&S01 through &S50).
EXCSQLSTM uses a QM/400 query, GENQRY, made up of 50 substitution variables. Figure 2 illustrates this source member. As you can see, there are 50 variables defined (&S01 through &S50).
The key to making this technique work is dividing up the SQL statement (specified through the SQLSTM parameter or from a query source member). The SQL statement is divided into pieces that are 55 characters long and then the pieces are passed to the GENQRY object. For example, let's say you enter a literal SQL statement, 150 characters in length for SQLSTM. GENQRY variable &S01 accepts the first 55 characters, variable &S02 accepts the second 55 characters, and variable &S03 accepts the last 40 characters of the statement.
Suggestions and Limitations
If you end up with more than a few source members containing SQL statements, you should consider creating a PDM user-defined option to run the EXCSQLSTM command. Through the user-defined option function of PDM you can automatically pass the library, source file, and source member name to EXCSQLSTM. (For more information about creating PDM user-defined options, see the PDM Users Guide and Reference manual.)
The most significant limitation of the EXCSQLSTM command is its performance. SQL statements do not execute as quickly as they do using the SQL/400 licensed program. Although you can indirectly embed SQL into your programs using the EXCSQLSTM command, performance will not approach the performance of the SQL precompiler. With the SQL/400 licensed program precompiler, SQL function calls actually become part of your HLL program object.
If your SQL statement contains errors, QM/400 sends a general failure escape message QWM2701 ("STRQMQRY command failed.") You can get more detailed information about the reason for the fialure from the job log.
If you specify *YES in the EDIT parameter to indicate that you want to edit an existing QM/400 query source member and you submit the command to batch, the edit request will be ignored.
Installing EXCSQLSTM
If you don't already have a source physical file named QQMQRYSRC, the first thing you have to do is create one. The record length should be 91 bytes instead of the default of 92. You need to change the default record length because QM/400 uses a source file record length of 79 instead of the usual 80 used by most source members. Use the Create Source Physical file command like this.
CRTSRCPF FILE(XXX/QQMQRYRC) + RCDLEN(91)
Next, create the Query Management source member, GENQRY, illustrated in 2 and compile it according to the instructions at the beginning of the source member. Create and compile the EXCSQLSTM command shown in 3. Next, create and compile the CL program SQL003CL shown in 4.
Next, create the Query Management source member, GENQRY, illustrated in Figure 2 and compile it according to the instructions at the beginning of the source member. Create and compile the EXCSQLSTM command shown in Figure 3. Next, create and compile the CL program SQL003CL shown in Figure 4.
A Useful Tool
If you don't have SQL/400, and you want to tap some of the power of SQL or if all you want to do is learn a little about SQL, give the EXCSQLSTM command a try. Either way I think you'll find it a useful tool.
Richard Shaler is a senior technical editor for Midrange Computing.
References
PDM User's Guide and Reference (SC09-1339, CD-ROM QBKA0602).
Query Management/400 Programmer's Guide and Reference (SC41-0090, CD-ROM
QBKA7K01).
SQL/400 Programmer's Guide (SC41-9609, CD-ROM QBKA7F03).
SQL/400 Reference (SC41-9608, CD-ROM QBKA7H03).
The EXCSQLSTM Utility
Figure 1 The EXCSQLSTM Command
UNABLE TO REPRODUCE GRAPHICS
The EXCSQLSTM Utility
Figure 2 The GENQRY Query Management Query Source Member
--==================================================================== -- To compile: -- -- CRTQMQRY QMQRY(XXX/GENQRY) SRCFILE(XXX/QQMQRYSRC) -- --==================================================================== &S01&S02&S03&S04&S05&S06&S07&S08&S09&S10 &S11&S12&S13&S14&S15&S16&S17&S18&S19&S20 &S21&S22&S23&S24&S25&S26&S27&S28&S29&S30 &S31&S32&S33&S34&S35&S36&S37&S38&S39&S40 &S41&S42&S43&S44&S45&S46&S47&S48&S49&S50
The EXCSQLSTM Utility
Figure 3 The EXCSQLSTM Command Source Member
/*==================================================================*/ /* To compile: */ /* */ /* CRTCMD CMD(XXX/EXCSQLSTM) PGM(XXX/SQL003CL) + */ /* SRCFILE(XXX/QCMDSRC) */ /* */ /*==================================================================*/ EXCSQLSTM: CMD PROMPT('Execute SQL Statement') PARM KWD(SQLSTM) TYPE(*CHAR) LEN(2750) + DFT(*SOURCE) EXPR(*YES) PROMPT('SQL + Statement') PARM KWD(OUTPUT) TYPE(*CHAR) LEN(8) RSTD(*YES) + DFT(*) VALUES(* *PRINT *OUTFILE) + PROMPT('Output') PARM KWD(SRCF) TYPE(Q1) PMTCTL(PC1) PROMPT('Query + source file') Q1: QUAL TYPE(*NAME) LEN(10) DFT(QQMQRYSRC) QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) + SPCVAL((*LIBL) (*CURLIB)) PROMPT('Library') PARM KWD(SRCMBR) TYPE(*NAME) LEN(10) PMTCTL(PC1) + PROMPT('Query source member') PARM KWD(EDIT) TYPE(*CHAR) LEN(4) RSTD(*YES) + DFT(*NO) VALUES(*NO *YES) PMTCTL(PC1) + PROMPT('Edit query source member') PC1: PMTCTL CTL(SQLSTM) COND((*EQ *SOURCE)) PARM KWD(OUTFILE) TYPE(Q2) PMTCTL(PC2) + PROMPT('File to receive output') Q2: QUAL TYPE(*NAME) LEN(10) DFT(SQLOUT) QUAL TYPE(*NAME) LEN(10) DFT(QTEMP) + SPCVAL((*LIBL) (*CURLIB)) PROMPT('Library') PARM KWD(OUTMBR) TYPE(E2) PMTCTL(PC2) + PROMPT('Output member options') E2: ELEM TYPE(*NAME) LEN(10) DFT(*FIRST) + SPCVAL((*FIRST)) PROMPT('Member') ELEM TYPE(*CHAR) LEN(8) RSTD(*YES) DFT(*REPLACE) + VALUES(*REPLACE *ADD) PROMPT('Replace or + add records') PC2: PMTCTL CTL(OUTPUT) COND((*EQ *OUTFILE))
The EXCSQLSTM Utility
Figure 4 The SQL003CL Command Processing Program
/*==================================================================*/ /* To compile: */ /* */ /* 1. Insure that a QQMQRYSRC source file exists in your library */ /* list. */ /* */ /* 2. CRTCLPGM PGM(XXX/SQL003CL) SRCFILE(XXX/QCLSRC) */ /* */ /*==================================================================*/ SQL003CL: + PGM PARM(&STM &OUTPUT &SRCF &SRCMBR &EDIT &OUTFILE &OUTMBR) DCLF FILE(QQMQRYSRC) /* Parameter variables */ DCL VAR(&EDIT) TYPE(*CHAR) LEN(4) DCL VAR(&MBROPT) TYPE(*CHAR) LEN(8) DCL VAR(&OUTFILE) TYPE(*CHAR) LEN(20) DCL VAR(&OUTMBR) TYPE(*CHAR) LEN(20) DCL VAR(&OUTPUT) TYPE(*CHAR) LEN(8) DCL VAR(&SRCF) TYPE(*CHAR) LEN(20) DCL VAR(&SRCMBR) TYPE(*CHAR) LEN(10) DCL VAR(&STM) TYPE(*CHAR) LEN(2750) /* Variables used for contrived values */ DCL VAR(&JOBTYPE) TYPE(*CHAR) LEN(1) DCL VAR(&LASTCHAR) TYPE(*CHAR) LEN(10) DCL VAR(&OFFSET) TYPE(*DEC) LEN(5 0) VALUE(0) DCL VAR(&MBRNAM) TYPE(*CHAR) LEN(10) DCL VAR(&MBROPT) TYPE(*CHAR) LEN(8) DCL VAR(&OUTFLIB) TYPE(*CHAR) LEN(10) DCL VAR(&OUTFNAM) TYPE(*CHAR) LEN(10) DCL VAR(&REMAINS) TYPE(*DEC) LEN(5 0) VALUE(0) DCL VAR(&SRCFLIB) TYPE(*CHAR) LEN(10) DCL VAR(&SRCFNAM) TYPE(*CHAR) LEN(10) /* Error processing variables */ DCL VAR(&ERRBYTES) TYPE(*CHAR) LEN(4) VALUE(X'00000000') DCL VAR(&ERROR) TYPE(*LGL) VALUE('0') DCL VAR(&MSGKEY) TYPE(*CHAR) LEN(4) DCL VAR(&MSGTYP) TYPE(*CHAR) LEN(10) VALUE('*DIAG') DCL VAR(&MSGTYPCTR) TYPE(*CHAR) LEN(4) VALUE(X'00000001') DCL VAR(&PGMMSGQ) TYPE(*CHAR) LEN(10) VALUE('*') DCL VAR(&STKCTR) TYPE(*CHAR) LEN(4) VALUE(X'00000001') MONMSG MSGID(CPF0000 QWM0000) EXEC(GOTO CMDLBL(ERRPROC)) /* Validate input */ IF COND(&OUTPUT *EQ '*OUTFILE') THEN(DO) CHGVAR VAR(&OUTFNAM) VALUE(%SST(&OUTFILE 1 10)) CHGVAR VAR(&OUTFLIB) VALUE(%SST(&OUTFILE 11 10)) CHGVAR VAR(&MBRNAM) VALUE(%SST(&OUTMBR 3 12)) CHGVAR VAR(&MBROPT) VALUE(%SST(&OUTMBR 13 8)) IF COND(&OUTFLIB *NE '*CURLIB' *AND &OUTFLIB *NE '*LIBL') + THEN(DO) CHKOBJ OBJ(&OUTFLIB) OBJTYPE(*LIB) ENDDO ENDDO RTVJOBA TYPE(&JOBTYPE) IF (&STM = '*SOURCE') THEN(DO) CHGVAR VAR(&SRCFNAM) VALUE(%SST(&SRCF 1 10)) CHGVAR VAR(&SRCFLIB) VALUE(%SST(&SRCF 11 10)) IF COND(&EDIT = '*YES' *AND &JOBTYPE *NE '0') THEN(STRSEU + SRCFILE(&SRCFLIB/&SRCFNAM) SRCMBR(&SRCMBR) OPTION(2)) OVRDBF FILE(QQMQRYSRC) TOFILE(&SRCFLIB/&SRCFNAM) MBR(&SRCMBR) CHGVAR VAR(&STM) VALUE(' ') READLOOP: + RCVF MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(FORMAT)) IF COND((&LASTCHAR > ' ') *AND (%SST(&SRCDTA 1 1) > ' ')) + THEN(CHGVAR VAR(&STM) VALUE(&STM *TCAT &SRCDTA)) ELSE CMD(CHGVAR VAR(&STM) VALUE(&STM *BCAT &SRCDTA)) CHGVAR VAR(&LASTCHAR) VALUE(%SST(&SRCDTA 79 1)) GOTO CMDLBL(READLOOP) ENDDO FORMAT: + CHGVAR VAR(&OFFSET) VALUE(&OFFSET + 55) CHGVAR VAR(&REMAINS) VALUE(2751 - &OFFSET) IF COND(%SST(&STM &OFFSET 1) *EQ ' ' *AND %SST(&STM &OFFSET 2) + *NE ' ') THEN(CHGVAR VAR(&STM) VALUE(%SST(&STM 1 &OFFSET) + *CAT %SST(&STM &OFFSET &REMAINS))) IF COND(&OFFSET *LT 2695) THEN(GOTO CMDLBL(FORMAT)) IF COND(&OUTPUT = '*OUTFILE') THEN(STRQMQRY QMQRY(GENQRY) + OUTPUT(&OUTPUT) OUTFILE(&OUTFLIB/&OUTFNAM) OUTMBR(&MBRNAM + &MBROPT) SETVAR((S01 %SST(&STM 1 55)) (S02 %SST(&STM 56 55)) + (S03 %SST(&STM 111 55)) (S04 %SST(&STM 166 55)) (S05 + %SST(&STM 221 55)) (S06 %SST(&STM 276 55)) (S07 %SST(&STM 331 + 55)) (S08 %SST(&STM 386 55)) (S09 %SST(&STM 441 55)) (S10 + %SST(&STM 496 55)) (S11 %SST(&STM 551 55)) (S12 %SST(&STM 606 + 55)) (S13 %SST(&STM 661 55)) (S14 %SST(&STM 716 55)) (S15 + %SST(&STM 771 55)) (S16 %SST(&STM 826 55)) (S17 %SST(&STM 881 + 55)) (S18 %SST(&STM 936 55)) (S19 %SST(&STM 991 55)) (S20 + %SST(&STM 1046 55)) (S21 %SST(&STM 1101 55)) (S22 %SST(&STM + 1156 55)) (S23 %SST(&STM 1211 55)) (S24 %SST(&STM 1266 55)) + (S25 %SST(&STM 1321 55)) (S26 %SST(&STM 1376 55)) (S27 + %SST(&STM 1431 55)) (S28 %SST(&STM 1486 55)) (S29 %SST(&STM + 1541 55)) (S30 %SST(&STM 1596 55)) (S31 %SST(&STM 1651 55)) + (S32 %SST(&STM 1706 55)) (S33 %SST(&STM 1761 55)) (S34 + %SST(&STM 1816 55)) (S35 %SST(&STM 1871 55)) (S36 %SST(&STM + 1926 55)) (S37 %SST(&STM 1981 55)) (S38 %SST(&STM 2036 55)) + (S39 %SST(&STM 2091 55)) (S40 %SST(&STM 2146 55)) (S41 + %SST(&STM 2201 55)) (S42 %SST(&STM 2256 55)) (S43 %SST(&STM + 2311 55)) (S44 %SST(&STM 2366 55)) (S45 %SST(&STM 2421 55)) + (S46 %SST(&STM 2476 55)) (S47 %SST(&STM 2531 55)) (S48 + %SST(&STM 2586 55)) (S49 %SST(&STM 2641 55)) (S50 %SST(&STM + 2696 55)))) ELSE STRQMQRY QMQRY(GENQRY) OUTPUT(&OUTPUT) SETVAR((S01 + %SST(&STM 1 55)) (S02 %SST(&STM 56 55)) (S03 %SST(&STM 111 + 55)) (S04 %SST(&STM 166 55)) (S05 %SST(&STM 221 55)) (S06 + %SST(&STM 276 55)) (S07 %SST(&STM 331 55)) (S08 %SST(&STM 386 + 55)) (S09 %SST(&STM 441 55)) (S10 %SST(&STM 496 55)) (S11 + %SST(&STM 551 55)) (S12 %SST(&STM 606 55)) (S13 %SST(&STM 661 + 55)) (S14 %SST(&STM 716 55)) (S15 %SST(&STM 771 55)) (S16 + %SST(&STM 826 55)) (S17 %SST(&STM 881 55)) (S18 %SST(&STM 936 + 55)) (S19 %SST(&STM 991 55)) (S20 %SST(&STM 1046 55)) (S21 + %SST(&STM 1101 55)) (S22 %SST(&STM 1156 55)) (S23 %SST(&STM + 1211 55)) (S24 %SST(&STM 1266 55)) (S25 %SST(&STM 1321 55)) + (S26 %SST(&STM 1376 55)) (S27 %SST(&STM 1431 55)) (S28 + %SST(&STM 1486 55)) (S29 %SST(&STM 1541 55)) (S30 %SST(&STM + 1596 55)) (S31 %SST(&STM 1651 55)) (S32 %SST(&STM 1706 55)) + (S33 %SST(&STM 1761 55)) (S34 %SST(&STM 1816 55)) (S35 + %SST(&STM 1871 55)) (S36 %SST(&STM 1926 55)) (S37 %SST(&STM + 1981 55)) (S38 %SST(&STM 2036 55)) (S39 %SST(&STM 2091 55)) + (S40 %SST(&STM 2146 55)) (S41 %SST(&STM 2201 55)) (S42 + %SST(&STM 2256 55)) (S43 %SST(&STM 2311 55)) (S44 %SST(&STM + 2366 55)) (S45 %SST(&STM 2421 55)) (S46 %SST(&STM 2476 55)) + (S47 %SST(&STM 2531 55)) (S48 %SST(&STM 2586 55)) (S49 + %SST(&STM 2641 55)) (S50 %SST(&STM 2696 55))) GOTO CMDLBL(ENDPGM) ERRPROC: + IF COND(&ERROR) THEN(GOTO CMDLBL(ERRDONE)) ELSE CMD(CHGVAR VAR(&ERROR) VALUE('1')) /* Move all *DIAG messages to previous program queue */ CALL PGM(QMHMOVPM) PARM(&MSGKEY &MSGTYP &MSGTYPCTR &PGMMSGQ + &STKCTR &ERRBYTES) /* Resend last *ESCAPE message */ ERRDONE: + CALL PGM(QMHRSNEM) PARM(&MSGKEY &ERRBYTES) MONMSG MSGID(CPF0000) EXEC(DO) SNDPGMMSG MSGID(CPF3CF2) MSGF(QCPFMSG) MSGDTA('QMHRSNEM') + MSGTYPE(*ESCAPE) MONMSG MSGID(CPF0000) ENDDO ENDPGM: + ENDPGM
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online