Here are some gotchas that SQL/400 users might find useful. The first will bite RPG programmers who believe that SQL cursors will remain open when returning to a prior program without setting on LR.
Files accessed using embedded SQL are handled differently than files opened and closed by other methods. To simulate the non-LR return, you must compile the RPGSQL program specifying *ENDJOB for the Close SQL Cursor (CLOSQLCSR) parameter. You then need to include a Close SQL Cursors routine that is executed whenever the program terminates with LR on. You should also assume that your cursor may be open on entry because SQL cursors are not closed in the RPG termination or cancel routines. The Reclaim Resources (RCLRSC) command also ignores SQL cursors.
Another potential gotcha is the LIKE comparison operator. It allows comparison of fields using the % and _ wildcard characters. When using LIKE, be aware that comparison values are not padded with blanks. SQL/400 assumes that all character fields are variable length.
To use LIKE, pad out all character values to their full length or end your comparison string with the % character. This is not necessary for variable length fields or when using any of the other comparison operators that automatically pad with blanks.
The final gotcha concerns partial outer joins. This condition exists when two files are joined and there is not a corresponding record in the secondary file.
A work-around described in the SQL Redbook involves using the UNION keyword and multiple SELECT statements. The work-around will increase the amount of time spent executing a multifile join exponentially depending on the number of files being joined. Often the only practical solution is to incorporate a "does not apply" or "not established" value in your database with a corresponding record in your secondary file. IBM's suggestion to use OPNQRYF-with JDFTVAL(*YES)- can bite back when joining more than two files since JDFTVAL cannot be specified at the file level.
As more AS/400 shops jump on the SQL bandwagon, IBM may be forced to extend SQL/400 to address these inconsistencies. Until then, creative programming is all that's available.
MC Press books written by David Morris available now on the MC Press Bookstore.
 |
||
 |
|
XML for eServer i5 and iSeries In this book, you will learn about Extensible Markup Language (XML), but with an IBM eServer i5/iSeries twist. List Price $64.95
Now On Sale
 |
|
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