12
Wed, Jun
4 New Articles

OS/400 CL Power from SQL

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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
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 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

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA 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:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA 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.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra 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: