Simplify your SQL script processing.
Best practice dictates that an SQL database creation script would have the following pair of statements duplicated for each SQL object:
DROP TABLE table1;
CREATE TABLE table1 (col1 INT, col2 CHAR(10));
The pairing of the DROP and CREATE statements is done so that the script can remove old versions of the database objects and avoid failing with an “Object Already Exists” error. The problem with this approach is the DROP statement will fail with an “Object Not Found” error the first time you try to create this database on a system or partition.
Extra processing needs to be added to your scripts to ensure that DROPs of non-existent objects don’t cause your database creation script to fail. Unfortunately, this extra processing has to be done outside of the SQL script, giving developers another thing to manage. The following is a list of the external controls that are commonly employed:
- Use a wrapper CL program with the appropriate MONMSG command.
- Specify a value of 20 for the Severity Level parameter on the RUNSQLSTM CL command.
- Use the Options menu (Figure 1) on the IBM i Access Client Solutions (ACS) Run SQL Scripts
Figure 1: Ignore “Object Not Found” DROP error on Run SQL Scripts Options menu.
Even though these extra processing options allowed your SQL script to run, the job log would still be littered with all the “File Not Found” error messages from DROP statements that were ignored or swallowed.
All these headaches and challenges go away with the new IF EXISTS clause for the SQL DROP statement. This new clause is part of the IBM i Technology Refresh that was announced on October 6, 2020.
The IF EXISTS clause allows your SQL database creation script to add additional processing to the DROP statement. In the following updated SQL script, the DROP statement will now run successfully whether or not TABLE1 exists in the target schema.
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (col1 INT, col2 CHAR(10));
No extra processing is needed with commands or parameters; you’re able to control the situation by adding a simple clause to your SQL script. In addition, the IF EXISTS clause eliminates the error message noise from your job log as shown in Figure 2. The results of the DROP statement will always be successful due to the IF EXISTS clause. Db2 attempts to drop the specified table, and if the table is not found, Db2 just ends processing for the DROP statement.
Figure 2: This is a successful result from the new IF EXISTS clause.
The IF EXISTS clause is not limited to just the SQL DROP TABLE statement; it’s available to use with any SQL DROP statement (DROP INDEX, DROP PROCEDURE, etc.).
The new IF EXISTS clause for the SQL DROP statement will be available for the IBM i 7.3 and 7.4 releases on November 13, 2020 by applying the latest Db2 PTF Group. More details on these PTF Groups can be found here. Happy SQL Scripting!
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