SQL excels at moving, retrieving, and changing data on an AS/400. It’s an interpreted query language, so the DB2/400 query optimizer decides how to implement your query and which access methods to use to access the target data. In this article, I show you a technique for finding out how the AS/400 is implementing your query and give some suggestions for interpretation of the optimizer data. You can use some of these techniques with Query/400, Open Query File (OPNQRYF), and third-party query products because they all use the same optimizer.
SQL statements may be executed statically or dynamically. Static SQL refers to statements that are compiled by the query optimizer; the execution plan is stored in a package file. The next time the statement is run, the SQL interpreter retrieves the stored execution plan and runs the query, thus saving the time required for compilation and optimization of the SQL query. Static SQL is seen in applications that use the SQL precompiler and in client/server applications that make use of parameter markers instead of static comparison values.
Debugging a program that uses static SQL is easy because the AS/400 gives you a command called Print SQL Information (PRTSQLINF) that can be used to examine how the AS/400 is implementing the query. The real joy is in finding optimization information for packages that use dynamic SQL.
What Is Dynamic SQL?
A lot of packages, particularly ODBC-based reporting or ad hoc query applications like Seagate Software’s Crystal Reports, use dynamic SQL. Dynamic SQL means that optimization decisions and access plans are not generated until execution of the query. Dynamic SQL is not a bad thing, but finding out how dynamic SQL queries are implemented can be a bear.
When you issue a dynamic SQL statement from a client application, AS/400 program, or interactive SQL session, the AS/400 compiles the query and then uses the query optimizer to determine how to retrieve or process the SQL request. The optimizer considers things like the number of records in each file and available indexes. The optimizer tries to determine the best method for data retrieval and creates an access path to process your request. The optimizer tries to choose the access path that is least costly in terms of processing, temporary storage, and disk access.
Finding the Job
Sometimes, the best-laid plans of optimizers and men go awry. In these cases, it is helpful to find out just what the optimizer is trying to do. By using the Start Debug (STRDBG) command, you can cause optimizer messages and index suggestions to be written to the AS/400 job log. By reading these messages, you can read the optimizer “plan” and sometimes assist it in making correct decisions.
If you are using SQL from an ODBC client application, such as Crystal Reports, finding the job and starting debugging is complicated. The AS/400 executes all ODBC jobs in the QSERVER subsystem as either QZDASOINIT (Windows 95/98/NT) or QZDAINIT (16-bit platforms) prestart jobs.
Another complication is that all ODBC jobs run under the user profile QUSER. Therefore, if you have 50 clients connecting via ODBC from a 32-bit Windows platform, the Work with Active Jobs (WRKACTJOB) command will display 50 QZDASOINIT jobs for user QUSER in the QSERVER subsystem. The only way to know which user is really connected to a job is to display the job log. At the top of the job log, you should see the message “Servicing User Profile X” (in which X is the user logged in to the system). You must hunt through the jobs until you find the user profile you are targeting.
To help us with this task, we have a stored procedure called RETJOBI that returns the job number. Every program in my shop calls this procedure and then posts the job number on every screen in the application. In this way, my operators can tell me their job numbers when they are having a problem, and I can use the Work with Job (WRKJOB) command to view their jobs.
Source code for RETJOBI, with the appropriate compilation and implementation instructions, is in Figure 1. Figure 2 is Visual Basic (VB) code that uses Microsoft’s ActiveX Data Objects (ADO) to connect to the AS/400. ADO must be checked as a reference in your VB project for the code to work.
Starting Debugging on a Client/Server Job
After you find the job number, things get easier. From a terminal session, invoke the Start Service Job (STRSRVJOB) command for the appropriate prestart job. For example, if the job is a Windows 95 client with the number 100100, the command line would be STRSRVJOB JOB(100100/QUSER/QZDASOINIT).
Next, type STRDBG UPDPROD(*YES) to put the job into debug mode. You may also need to issue the Change Job (CHGJOB) command to ensure that all messages are being written to the job log. The command line is CHGJOB JOB(100100/QUSER/QZDASOINIT) LOG(4 00 *SECLVL).
Optimizer messages are now being written to the job log. To look at the log, use the Display Job Log (DSPJOBLOG) command. For this example, the command line would be DSPJOBLOG JOB(100100/QUSER/QZDASOINIT).
Interpreting the Information
Now that you have debugging started, execute a few SQL statements and display the job log. You will find a wealth of information about how the optimizer is implementing your queries. If you position the cursor on a message and press the F1 (or Help) key, you will see detailed message text that can give you a clue as to how the optimizer is implementing your queries.
Below are some messages to look out for and information about how they may be able to help you understand and manipulate query performance:
• CPI432C, “All access paths were considered for file &1” (&1 will be replaced with the file name in the displayed message). This message tells which access paths were considered in optimization of your query and which access path was chosen for processing (if any). Changing the ORDER BY clause of a SELECT statement can affect which access paths are chosen for query implementation.
• CPI4321, “Access path built for file &1.” This indicates that the optimizer chose to create an access path to implement your query. This may not indicate bad performance. Pressing F1 on this message can lead to why the AS/400 chose to build an access path rather than use an existing index and may provide a suggestion for a new index.
• CPI432F, “Access path suggestion for file &1.” This is my favorite message because the AS/400 suggests an index that may help your query run faster. Pressing F1 on this message will reveal the key fields suggested for the new index. Try building the suggested index and rerunning the query. Refresh your job log and see if the new access path is used.
• CPI4327, “File &1 processed in join position 1.” This indicates which file in a join query will be processed first. To best see why this is important, imagine a database of orders, order details, and part descriptions, and you are writing a query to list parts shipped in last week’s orders by customer. The query will join orders to order details, which have part ID codes that are used to join description information from the parts master table. If the parts master is being processed in join position 1, this could indicate a problem in your query design. Most likely, you would want orders in join position 1 to reduce the number of order records before joining details and descriptions. To solve these problems, try moving elements in the Where clause and changing the order of tables listed in the From clause.
• CPI432E, “Selection fields mapped to different attributes.” This message occurs when you are joining tables on fields that have different data types or when you are supplying Where clause criteria that is incompatible with the data type of a column in the table.
• CPI4323, “The query access plan has been rebuilt.” Always press F1 and see why this is occurring. If a query is being reexecuted by an application, you want the access path to be reused.
To end debugging, issue the End Debug (ENDDBG) and End Service Job (ENDSRVJOB) commands.
It’s an Art, Not a Science
In the end, there is no magic SQL optimization bullet. You have to examine what the optimizer is doing and play with the query and physical file indexes until you get the optimizer to do what you want it to. Use job log messages to achieve the client/server performance you desire.
/*==================================================================*/
/* To compile: */
/* */
/* CRTBNDCL PGM(XXX/RETJOBI) SRCFILE(XXX/QCLSRC) */
/* DFTACTGRP(*NO) ACTGRP(*CALLER) */
/* */
/* To create the stored procedure, use the following command from */
/* an interactive SQL/400 session: */
/* */
/* CREATE PROCEDURE QSYS/RETJOBI (INOUT :job CHAR(6)) EXTERNAL */
/* NAME XXX/RETJOBI SIMPLE CALL */
/* */
/* Just be sure to change XXX to the appropriate library name */
/* */
/*==================================================================*/
PGM PARM(&NBR)
DCL VAR(&NBR) TYPE(*CHAR) LEN(6)
RTVJOBA NBR(&NBR)
ENDPGM
Figure 1: The RETJOBI stored procedure retrieves a job’s number.
Dim Con1 As New Connection
Dim Cmd1 As New Command
Dim Prm1 As Parameter
'Activate the connection with the AS/400
Con1.Open "AS400", "HOWARD", "SECRET"
'Associate the connection with the command object
Cmd1.ActiveConnection = Con1
'set the command into commandtext property
'make sure to replace XXX with the name of the library
'referenced in the create procedure statement
Cmd1.CommandText = "CALL XXX.RETJOBI(?)"
'Create a parameter object to hold job number
Set Prm1 = Cmd1.CreateParameter("job", adChar, adParamInputOutput, 6, "")
'append parameter object into command object
Cmd1.Parameters.Append Prm1
'execute the command
Cmd1.Execute
'show user the job number
MsgBox "Your job number is: " & Cmd1.Parameters("job").Value
'Release the command and the connection
Set Cmd1 = Nothing
Set Con1 = Nothing
Figure 2: This Visual Basic code connects to the AS/400 and executes the RETJOBI procedure, which retrieves the job number.
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