Have you ever dreamt about being able to issue an AS/400 command from your client/server program? Have you ever fantasized about accessing file members using ODBC? Have you ever longed to get optimizer information about your ODBC application's SQL statements? Me neither. But I have needed to do these things. Fortunately, there is a useful trick that allows you to accomplish these feats and more from an ODBC application. The trick is a familiar OS/400 tool-QCMDEXC. In this article, I'll explore just what QCMDEXC means to your ODBC client/server applications. While my examples use Visual Basic (VB) and ODBC, the underlying technique isn't specific to either. (For more information on ODBC, see "ODBC Overview," MC, February 1995.)

As you may know, QCMDEXC is the program that executes CL commands. It resides in the library QSYS. Did you know that you can also call this program as a stored procedure through SQL? Stored procedures, a new feature of V3R1, enable you to run OS/400 programs through SQL statements. (For more information on stored procedures, see "DB2/400 Stored Procedure Implementation," MC, November 1995.) Calling QCMDEXC as a stored procedure through SQL allows you to perform OS/400 functions not possible with SQL alone.

There are two parameters for the QCMDEXC program. The first is a string that contains the OS/400 CL command to be executed. An example of a command string that uses the Start Debug (STR-DBG) command would be STRDBG UPDPROD(*YES). The second parameter is a decimal value (15,5) that contains the length of the command in the first parameter. The fact that it is a decimal parameter has significance in this situation, because, when you call QCMDEXC, you must pad out the second parameter with the correct number of digits and decimal places. For example, using the length of the above command, the second parameter of a call to QCMDEXC would be 0000000020.00000, because there are 20 characters in the command string.

You can run the SQL statement that calls the QCMDEXC stored procedure from any application that supports standard ODBC syntax. Examples of these applications are Microsoft Access, Microsoft Query, Borland Delphi, Powersoft PowerBuilder, and Microsoft Visual Basic. The syntax for the SQL statement is CALL QSYS.QCMDEXC(cmd string, length). So the complete SQL statement for the above example is

 CALL QSYS.QCMDEXC('STRDBG UPDPROD(*YES)', 0000000020.00000) 

Formatting the call to the QCMDEXC command properly every time you want to use it can be tedious. Who wants to count the number of characters in the command and then count the exact number of leading and trailing zeros to place in the length parameter? To ease the use of QCMDEXC as a stored procedure, I have written a couple of small functions in VB. One function takes a CL command and returns a string that is a properly formatted call to QCMDEXC. The other function takes arguments for the Override with Database File (OVRDBF) command and returns a properly formatted call to QCMDEXC with the command.

1 is the FormatQCMDEXC function that formats the QCMDEXC SQL string. To use it, simply pass in the command that you want to execute as the only parameter. You don't have to include the call to QCMDEXC or the length of the command. Just pass in the command that you want to run. The function will format the statement, including the parameter that is the length of the string. It returns a string that is a ready-to-run SQL statement. I used this function throughout the following examples. It takes away the laborious and error-prone process of counting the characters for the length of the command and formatting the length argument with the proper number of zeros. The use of the function is shown in the following examples.

Figure 1 is the FormatQCMDEXC function that formats the QCMDEXC SQL string. To use it, simply pass in the command that you want to execute as the only parameter. You don't have to include the call to QCMDEXC or the length of the command. Just pass in the command that you want to run. The function will format the statement, including the parameter that is the length of the string. It returns a string that is a ready-to-run SQL statement. I used this function throughout the following examples. It takes away the laborious and error-prone process of counting the characters for the length of the command and formatting the length argument with the proper number of zeros. The use of the function is shown in the following examples.

One of the questions that programmers often ask about client/server development on the AS/400 is "How are database file members accessed through ODBC?" The current ODBC specification does not support access to members. Some vendors have adapted their drivers to be able to access them, but each implementation may be different. Further, IBM's Client Access for Windows ODBC driver does not natively have any facilities to access specific file members. Using QCMDEXC, you can overcome this problem for most AS/400 ODBC drivers.

To access file members using most ODBC drivers for the AS/400, you can call QCMDEXC and pass in the OVRDBF command. This command has parameters that allow you to change any references to a particular file and member to point to a different file and member. For example, if you wanted to refer to member MBR9 of file FILE1, you would use this command:

 OVRDBF FILE(FILE1) MBR(MBR9) OVRSCOPE(*JOB) 

The OVRSCOPE parameter specifies how long the override lasts. For ODBC clients, you should set this parameter to *JOB, which will keep the override in effect until the ODBC session terminates. With this override, any references to the file FILE1 will automatically refer to the member MBR9 until the ODBC connection terminates.

2 shows a function I wrote that acts as a front-end for the OVRDBF command. The function has three parameters. The first parameter is the file to override. The second parameter is the destination file for the override. This is an optional parameter. If you leave it blank, the function will use the default value for the parameter on your AS/400 by testing the length of the member name passed to the function. (A length of zero means that no member name was passed.) The third parameter is the destination member for the override. You can also leave this blank if you want to use the default OS/400 value. This function makes use of the FormatQCMDEXC function.

Figure 2 shows a function I wrote that acts as a front-end for the OVRDBF command. The function has three parameters. The first parameter is the file to override. The second parameter is the destination file for the override. This is an optional parameter. If you leave it blank, the function will use the default value for the parameter on your AS/400 by testing the length of the member name passed to the function. (A length of zero means that no member name was passed.) The third parameter is the destination member for the override. You can also leave this blank if you want to use the default OS/400 value. This function makes use of the FormatQCMDEXC function.

3 is an example of VB code that invokes the function to override a database file. In the first override example, any future reference to file FILE1 is being overridden to refer to member MBR9. This is the complete SQL command generated:

Figure 3 is an example of VB code that invokes the function to override a database file. In the first override example, any future reference to file FILE1 is being overridden to refer to member MBR9. This is the complete SQL command generated:

 CALL QSYS.QCMDEXC('OVRDBF FILE(FILE1) MBR(MBR9) OVRSCOPE(*JOB) ', 0000000044.00000) 

The Execute method of the VB database object is being used with the above SQL command and an options parameter value of dbSQLPassThrough, which is 64. This value causes the VB database engine to pass the SQL statement to the ODBC driver as is, without first trying to interpret it. This is necessary because the VB SQL engine does not understand this command, and you want it to execute on the AS/400. (For more information on OS/400 database access with VB, see "ODBC Performance Basics," MC, August 1995.)

QCMDEXC is also useful for gathering SQL optimizer information for client/server application development. You can gather such information by issuing the STRDBG command for the ODBC job. When debug is on for an SQL job, SQL optimizer decision information is written to the job log. An example of the type of information written to the log is shown in 4. The secondary text for the messages sent by the SQL optimizer can be very informative (see 5). Using these messages, you can see which indexes a join used and why. A complete discussion of SQL performance tuning techniques is beyond the scope of this article, but this information can help you optimize your SQL statements for maximum performance.

QCMDEXC is also useful for gathering SQL optimizer information for client/server application development. You can gather such information by issuing the STRDBG command for the ODBC job. When debug is on for an SQL job, SQL optimizer decision information is written to the job log. An example of the type of information written to the log is shown in Figure 4. The secondary text for the messages sent by the SQL optimizer can be very informative (see Figure 5). Using these messages, you can see which indexes a join used and why. A complete discussion of SQL performance tuning techniques is beyond the scope of this article, but this information can help you optimize your SQL statements for maximum performance.

The CL command to start debug is STRDBG UPDPROD(*YES). You set the UPDPROD parameter to *YES so production files can be updated. 6 shows example VB code that performs this operation. This code also uses the FormatQCMDEXC function. The following code shows the resulting SQL statement for this command:

The CL command to start debug is STRDBG UPDPROD(*YES). You set the UPDPROD parameter to *YES so production files can be updated. Figure 6 shows example VB code that performs this operation. This code also uses the FormatQCMDEXC function. The following code shows the resulting SQL statement for this command:

 CALL QSYS.QCMDEXC('STRDBG UPDPROD(*YES)', 0000000020.00000) 

Once this statement is executed, SQL optimizer information is written to the job log. IBM's DB2/400 SQL Programming V3R1 manual has excellent information on optimizing SQL statements. This information, combined with the job log information, can help you maximize the performance of your ODBC applications.

For most ODBC drivers, you can view the job log while the job is still running. Simply issue the Work with User Jobs (WRKUSRJOB) command to view the active jobs of the user profile that you used when you signed on with the ODBC driver. For example, the command for me would look like this:

 WRKUSRJOB USER(SINGLETON) + STATUS(*ACTIVE) 

Be sure not to log off your ODBC application; the ODBC job will end. If you have multiple jobs running on the system and aren't sure which is your ODBC job, you can sometimes find it by looking at each job's open files. Do this by putting a 5 next to an active job. Press Enter to display the Work With Job menu. Select option 14 to view the open files. Do this for each job until you find the open files that match the files used by your application. Once you find the ODBC job, select option 10 from the Work With Job menu. This will display the job log for that job.

Calling QCMDEXC as a stored procedure can allow you to do many things not normally possible with ODBC. There is one significant limitation, however. You can't return data using this method. If you need to retrieve data from the AS/400 to your client/server application, you will have to find another way. Still, you can do many things to help control your interaction with the AS/400 using QCMDEXC.

Wrapping commands in a call to QCMDEXC can be a useful addition to your client/server programming repertoire. This method allows you to perform functions that you can't perform with straight SQL. There are many other possibilities besides what I showed you here. Use your imagination and explore them.

Brian Singleton is an associate technical editor for Midrange Computing. He can be reached by E-mail at This email address is being protected from spambots. You need JavaScript enabled to view it..

REFERENCE

DB2/400 SQL Programming V3R1 (SC41-3611, CD-ROM QBKAQ800).

OS/400 CL Power from SQL

Figure 1: VB Function to Format QCMDEXC Command

 Function FormatQCMDEXC (sCmd As String) As String '-------------------------------------- ' Return a properly formatted call ' to QCMDEXC. ' sCmd is a string that is the command ' to execute '-------------------------------------- Dim sTemp As String ' Local work string sTemp = "CALL QSYS.QCMDEXC('" & sCmd & "' ," sTemp = sTemp & String$(11 - Len(Str$(Len(sCmd))), "0") & Len(sCmd) & ".00000)" FormatQCMDEXC = sTemp End Function 
 
OS/400 CL Power from SQL
 
Figure 2: VB SQL Override Function
 
 Function SQLOverride (sOvrFromFile As String, sOvrToFile As String, sOvrToMbr As String) As String '-------------------------------------------------- ' SQL Override function ' Returns a string that is the complete SQL statement ' to override a file using QCMDEXC. No error checking. ' sOvrFromFile - file to be overridden ' sOvrToFile - file to be overridden to (can be blank) ' sOvrToMbr - member to be overridden to (can be blank) '-------------------------------------------------- Dim sTemp As String ' Work string sTemp = "OVRDBF FILE(" & sOvrFromFile & ")" ' If there is an overriding file, specify it If Len(sOvrToFile) Then sTemp = sTemp & " TOFILE(" & sOvrToFile & ")" End If ' If there is an overriding member, specify it If Len(sOvrToMbr) Then sTemp = sTemp & " MBR(" & sOvrToMbr & ")" End If SQLOverride = FormatQCMDEXC(sTemp & " OVRSCOPE(*JOB) ") End Function 
 
OS/400 CL Power from SQL
 
Figure 3: Calling SQL Override Function
 
 dbSQLPassThrough=64 Set db = OpenDatabase("", False, False, "ODBC;") ' Override member db.Execute SQLOverride("test1", "", "mbr9"), dbSQLPassThrough ' Override a file db.Execute SQLOverride("test2", "file2", ""), dbSQLPassThrough ' Override both db.Execute SQLOverride("test3", "file3", "mbr3"), dbSQLPassThrough ' Override a file and library db.Execute SQLOverride("test4", "qgpl/file4", ""), dbSQLPassThrough 
 
OS/400 CL Power from SQL
 
Figure 4: SQL Optimizer Information in Job Log
 
 Display Job Log System: MCEDIT Job . . : DVSERVER User . . : SINGLETON Number . . . : 002526 Row not found for SQL_CUR1. ODP deleted. Cursor SQL_CUR1 closed. Column qualifier or table ORDHDR undefined. Column qualifier or table ORDHDR undefined. Column qualifier or table CUSTMR undefined. Column qualifier or table CUSTMR undefined. PREPARE of statement STMT0001 completed. All access paths were considered for file CUSTMR. All access paths were considered for file ORDHDR. Access path built for file CUSTMR. Access path built for file ORDHDR. File CUSTMR processed in join position 1. File ORDHDR processed in join position 2. More... Press Enter to continue. F3=Exit F5=Refresh F10=Display detailed messages F12=Cancel F16=Job menu F24=More keys 
 
OS/400 CL Power from SQL
 
Figure 5: Secondary Text for an SQL Optimizer Message
 
 Additional Message Information Message ID . . . . . . : CPI432C Severity . . . . . . . : 00 Message type . . . . . : Information Date sent . . . . . . : 02/22/96 Time sent . . . . . . : 13:56:35 Message . . . . : All access paths were considered for file ORDHDR. Cause . . . . . : The OS/400 Query optimizer considered all access paths built over member ORDHDR of file ORDHDR in library MCSQL. The list below shows the access paths considered. If file ORDHDR in library MCSQL is a logical file then the access paths specified are actually built over member ORDHDR of physical file ORDHDR in library MCSQL. Following each access path name in the list is a reason code which explains why the access path was not used. A reason code of 0 indicates that the access path was used to implement the query. MCSQL/QSYS_ORDHDR_00001 6. The reason codes and their meanings follow: 1 - Access path was not in a valid state. The system invalidated the More... Press Enter to continue. F3=Exit F6=Print F9=Display message details F12=Cancel F21=Select assistance level 
 
OS/400 CL Power from SQL
 
Figure 6: VBDebug Code
 
 DbSQLPassThrough=64 Set db = OpenDatabase("", False, False, "ODBC;") db.Execute FormatQCMDEXC("STRDBG UPDPROD(*YES)"), dbSQLPassThrough 

Brian Singleton is former editor of Midrange Computing. He has worked in the IBM midrange arena for many years, performing every job from backup operator to programmer to systems analyst to technology analyst for major corporations and IBM Business Partners. He also has an extensive background in the PC world. Brian also developed a line of bestselling Midrange Computing training videos, authored the bestselling i5/OS and Microsoft Office Integration Handbook, and has spoken at many popular seminars and conferences.

---

MC Press books written by Brian Singleton available now on the MC Press Bookstore.

		i5/OS and Microsoft Office Integration Handbook Harness the power of Microsoft Office while exploiting the iSeries database. List Price $79.95 Now On Sale

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions