29
Fri, Nov
0 New Articles

Using DB2 with Sed, Tail, Pipes, and Redirection from Within Qshell

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

Export physical files directly to the IFS with headers.

 

In my previous articles about SQL on the IBM i, I have used common commands that are available directly from the command line. In this article, I will use the db2 command in Qshell to export files directly to the IFS. And when I create the file, I will also satisfy a common need to include header information with the data.

Running a Query Using DB2 from Qshell

We will first enter Qshell using the STRQSH command. Once in Qshell, we can access the db2 command. We can use the db2 utility in Qshell to execute SQL statements by specifying the query to run in quotes after the db2 command.

 

We will use the MC_PHONE file for our example, which has the following DDS:

 

A          R MCFMT

A            MCACCT         6S 0       COLHDG('ACCOUNT NUMBER')

A            MCAREA         3A         COLHDG('AREA CODE')

A            MCPHONE        7A         COLHDG('PHONE NBR')

A            MCDATE         8S 0       COLHDG('CHANGE DATE')

 

To perform a Select query over all of the data in the table, we will execute the following command on the Qshell command line:

 

 db2 -v 'select * from mylib.mc_phone'

 

This action displays the following results:

 

031710TomSnyder_DB2_fig1

Figure 1: We have selected the data from mylib.mc_phone.

 

The -v option indicates that the output should be verbose and will echo the results to the standard output.

Using Redirection to Export Data Directly to an IFS File

So far, our output is similar to what we've seen with the interactive SQL command using the STRSQL command. But, because we are in Qshell, we can combine the capabilities of one command with another. To get our output into a text file on the IFS, we will use redirection.

 

Redirection means that we will override our standard output to go to a different location than the default. This can be accomplished using the output redirection "greater than" symbol (>).

 

So, to export the data from our previous select query, we will redirect our output to a file named sqlOut.txt on the IFS using the redirection symbol as follows:

 

db2 -v 'select * from mylib.mc_phone' > sqlOut.txt

 

When you execute the command, you will not see the results of the query displayed on the screen because they went into the file instead. You can list and view the files in your home directory in multiple ways. Here are two ways using of doing that, in and out of Qshell:

 

In Qshell:

  • List the files using the ls command.View the files using the cat command:
  • cat sqlOut.txt.

 

Out of Qshell:

  • Execute the WRKLNK command.
  • Place a 5 in front of the file.

 

The problem here is that there is too much information in the output. It even contains the SQL statement that was executed and the words executing and done! We'll remove the -v option so that this information is not exported.

 

db2 'select * from mylib.mc_phone' > sqlOut.txt

 

Removing Lines from the Output Using sed

Removing the verbose option command removed some of the undesirable information but not all of it. You may want the header information in the file, but you most likely will not want the second line to be contained in the data that you are exporting. DB2 does have a -x option, but that would remove all of the header and footer information, and it isn't available for Qshell anyway. So we'll have to do a little extra work to get the information the way we want it.

 

This is where we'll take advantage of another utility available to Qshell called sed (stream editor). sed is a powerful UNIX text-processing utility that has many capabilities. We will use this utility to target specific lines in our output to be deleted.

 

I have downloaded the text file output into a text editor that displays the line numbers to show the actual results that were generated (Figure 2).

 

031710TomSnyder_DB2_fig2

Figure 2: The text file output generated these results.

 

As you can see here, the header separator line that we will be removing is on line 3, not line 2. There is a blank line for line 1. You can also see that the footer is on line 9 and has two blank lines following it.

 

To delete the third line from a file, we will use the sed command. The first parameter of the command will consist of the number 3 to indicate the line number, followed by the lowercase letter d, which will indicate the delete operation. The file name will be passed as the second parameter of the sed command.

 

So, to delete the third line from our sqlOut.txt file, we would execute the following command:

 

sed '3d' sqlOut.txt

 

This will display the results to the screen. But, if you were to display the file using the cat command, you would see that the file is unchanged. This is because the standard output of the sed command was not changed, which is why it was displayed on the screen. To actually change the file, we will redirect the output to the new file sqlOut2.txt.

 

sed '3d' sqlOut.txt > sqlOut2.txt

 

Now, when we display the sqlOut2.txt file, we will see that the third line is removed.

Removing  the Footer Information

Almost there! Just need to remove that footer information. This is where the fun part comes in. The header was easy because we know the line number, but we do not know how many records will be contained in the results, so we need to figure out how to delete the last three lines.

 

Sed uses the  dollar sign ($) to indicate the last line. It would have been easy if we needed to remove only the last line; we'd just use this:

 

sed '$d' sqlOut2.txt

 

But we need to remove the last three lines, and there isn't an easy way of doing that with sed. Using tail is easier.

Using the Tail Utility

Tail is a command that will output the last part of a file. The tail command has the option to start at a particular line from the beginning, and it also has the ability to output the data in reverse order using the -r option.

 

If we use tail with the -r option on our file, we'll see the file displayed in reverse order:

 

031710TomSnyder_DB2_fig3

Figure 3: The -r option allows us to display the file in reverse order 
 

If we were to use tail with the +4 option, we would see the file displayed with everything except the first four records in the original sequential order:


031710TomSnyder_DB2_fig4

Figure 4: The +4 option displays the file with everything except the first four records in the original sequential order.

 

Individually, these capabilities won't give us what we're looking for, so we'll have to combine them using pipes.

Passing the Standard Output Using Pipes

With the pipe symbol (|), the output of one utility can be passed as the input of another utility. So, if we were to first reverse the order of the output using tail –r and then use the pipe to pass that output to another tail +4, we would have the last four lines removed because the data is upside down. Then all we would need to do is reverse the data again to put it back into its original order with the last four lines removed.

 

Here are the steps that will be piped together to remove the last four lines:

  1. Reverse the order of the sqlOut2.txt file.
  2. Force the output to start four lines from the beginning, which is actually the end because it is in reverse order.
  3. Reverse the order back to its original order; minus the last four lines.

 

Here is the command to do that:

 

tail -r sqlOut2.txt|tail +4|tail -r > sqlOut3.txt

 

Putting It All Together

This may look like a lot, but it's only because I have broken down each step into smaller pieces.

 

Now that we have all of the parts that we will need, we can combine them to generate our desired output using pipes. We could put these into a script and execute them sequentially, or we can combine them, which is what we will be doing here.

 

db2 'select * from mylib.mc_phone'|sed '3d'|tail -r|tail +4|tail -r > sqlOutFinal.txt

 

Now that's a mouthful! We have SQL statements and Qshell/UNIX utilities exporting, removing lines, flipping data, reorganizing, and redirecting to produce the final output.

More Information

You can find more information on the Qshell db2 utility on the IBM Web site by clicking here. As for the Qshell commands, the UNIX command documentation on the Web is bountiful.

 

Happy St. Patrick's Day!

 

Thomas Snyder

Thomas Snyder has a diverse spectrum of programming experience encompassing IBM technologies, open source, Apple, and Microsoft and using these technologies with applications on the server, on the web, or on mobile devices.

Tom has more than 20 years' experience as a software developer in various environments, primarily in RPG, Java, C#, and PHP. He holds certifications in Java from Sun and PHP from Zend. Prior to software development, Tom worked as a hardware engineer at Intel. He is a proud United States Naval Veteran Submariner who served aboard the USS Whale SSN638 submarine.

Tom is the bestselling author of Advanced, Integrated RPG, which covers the latest programming techniques for RPG ILE and Java to use open-source technologies. His latest book, co-written with Vedish Shah, is Extract, Transform, and Load with SQL Server Integration Services.

Originally from and currently residing in Scranton, Pennsylvania, Tom is currently involved in a mobile application startup company, JoltRabbit LLC.


MC Press books written by Thomas Snyder available now on the MC Press Bookstore.

Advanced, Integrated RPG Advanced, Integrated RPG
See how to take advantage of the latest technologies from within existing RPG applications.
List Price $79.95

Now On Sale

Extract, Transform, and Load with SQL Server Integration Services Extract, Transform, and Load with SQL Server Integration Services
Learn how to implement Microsoft’s SQL Server Integration Services for business applications.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

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: