Find out how to transform and extract data and learn the options for saving the output.
As the numbers of applications systems and interfaces continues to grow, many IBM i developers face the challenge of getting their DB2 for i data into the right format to meet the requirements of these new interfaces. The format types range can from XML to comma-separated values (CSV) to fixed-file formats.
SQL is a great language to use in these situations because it has many functions and features to help with data transformations and extracts. A good number of IBM i developers have a firm understanding of the SQL data transformation capabilities, but they are not aware of options that are available to save the output from an SQL statement. This is an important part of the solution because often the developers need to extract a set of business data with a SELECT statement and save the results of that statement into an output file that can be shared with another application system.
The CREATE TABLE Statement
Let's start by reviewing the CREATE TABLE statement as an output option for SELECT statements. Some of you may have already used this option indirectly through the Start SQL Interactive Session (STRSQL) command. From this interface, you can run a SELECT statement and select a service option using the F13 key to have the output from the SELECT inserted into a DB2 table.
This same output processing can be done programmatically using a CREATE TABLE like the statement shown below. The SELECT statement performing the data extract is embedded within the CREATE TABLE statement. When the statement is executed, DB2 first examines the attributes of the columns on the SELECT list to determine the data type and length needed for each column and then creates the output table (myoutput) with the correct attributes. This is a productivity gain for programmers because they no longer have to perform this pre-processing before running the SELECT statement.
CREATE TABLE myoutput AS
(SELECT e.empno, e.firstname ||' '|| e.lastname as "EmpName",
e.phoneno, d.deptname
FROM employee e, department d
WHERE e.workdept = d.deptno AND
(YEAR(CURRENT DATE) - YEAR(hiredate) = 25) ) WITH DATA
After creating the output table, DB2 executes the SELECT statement and inserts the selected data into the output table due to the WITH DATA clause at the very end of the statement. Without this clause, the CREATE TABLE statement would have just created an empty table.
The CPYTOIMPF Command
You may be thinking it's great that the results of my SELECT statement can be saved to an output table, but how does this get my DB2 data into an exchangeable format such as CSV? With the newly created output table, this formatting can easily be done with the Copy To Import File (CPYTOIMPF) command. The CPYTOIMPF command can be utilized to take data from a DB2 object and copy it into a format that can be shared with other systems and applications. The command supports either a delimited format or a fixed format. Shown below is an example CPYTOIMPF command that copies the data from the DB2 table, myoutput, into the specified delimited stream file within IFS. The data from this command is stored in CSV format since the default delimiter for this command is a comma.
CPYTOIMPF FROMFILE(myoutput)
TOSTMF('/home/myfiles/outfile1.txt')
RCDDLM(*CRLF)
DTAFMT(*DLM)
Assuming that a single row was extracted by the SELECT statement, the contents of the delimited stream file would be the following:
"000010", "JENNA HAAS", 0420, COMPUTER SERVICES
The XML Format
If you need to get the data extract into an XML format, the integrated XML publishing functions delivered with the IBM i 7.1 release make this extremely simple as shown below. With the XMLELEMENT function, programmers just supply the name of the XML element they want associated with a particular column (phoneno) or the result of an expression (e.firstname ||' '|| e.lastname). The XMLATTRIBUTES function works in a similar fashion to allow columns or expression values to be embedded as XML attribute values.
CREATE TABLE myxmlout AS
(SELECT XMLSERIALIZE(
XMLELEMENT(NAME "Employee", XMLATTRIBUTES(e.empno as "id"),
XMLELEMENT(NAME "Name", e.firstname ||' '|| e.lastname),
XMLELEMENT (NAME "Extension",e.phoneno),
XMLELEMENT (NAME "DeptName", d.deptname))
AS CHAR(256) ) as "XMLResult"
FROM employee e, department d
WHERE e.workdept = d.deptno AND
(YEAR(CURRENT DATE)-YEAR(hiredate) = 25)) WITH DATA
The CPYTOIMPF command can again be utilized to copy the XML string generated by the SELECT statement into a stream file. The contents of this XML file would look like the following:
<Employee>
<Name>JENNA HAAS</Name>
<Extension>0420</Extension>
<DeptName>COMPUTER SERVICES</DeptName>
</Employee>
Combining SQL Views and CPYTOIMPF
Usage of SQL Views provides a similar SQL output solution while simplifying and streamlining the output processing. An SQL View defines an object that contains a logical representation of the underlying table data but no data itself. Views are commonly used to provide only a subset of the columns or rows, derive new values, or combine sets of rows from multiple tables. The following example SQL View performs all of these tasks.
CREATE VIEW myoutview AS
(SELECT e.empno , e.firstname ||' '|| e.lastname as "EmpName",
e.phoneno, d.deptname
FROM employee e, department d
WHERE e.workdept = d.deptno AND
(YEAR(CURRENT DATE) - YEAR(hiredate) = 25) )
Review this View definition closely, and you will probably notice that the SELECT statement is a duplicate of the SELECT embedded within the CREATE TABLE statement at the beginning of this TechTip. Moving the data extract definition from the CREATE TABLE to the CREATE VIEW statement is why the output option utilizing a View is more efficient. The SQL View approach eliminates the step of saving the output from a SELECT statement into a DB2 table.
Referencing the SQL View on the CPYTOIMPF command removes the step of saving output to a DB2 table, as shown in the code below. With this CPYTOIMPF request, DB2 extracts the data from the underlying tables based on the view definition, and the selected data sets are written directly to the specified output file. The creation of the output file done with the SQL View runs faster and more efficiently because DB2 doesn't have to spend any time or resources populating a DB2 table with the selected data.
CPYTOIMPF FROMFILE(myoutview)
TOSTMF('/home/myfiles/outfile2.txt')
RCDDLM(*CRLF)
DTAFMT(*DLM)
IBM i Navigator Run SQL Scripts
The IBM i Navigator Run SQL Scripts interface delivers another SQL output option to users who want to create a data extract with a minimal amount of coding. This output option requires only a SELECT statement.
The first step with this option is enabling your Run SQL Scripts session to save the results from a SELECT statement. This enablement occurs by selecting the Allow Save Results item on the Options pull-down menu as shown in Figure 1.
Figure 1: Run SQL Scripts offers configuration options. (Click images to enlarge.)
With this Run SQL Scripts option activated, rows from a SELECT statement are easily saved by just executing the statement and then right-clicking on the result set window and selecting the Save Results task. Figure 2 highlights this simple interface.
Figure 2: This is the Save Results interface for the result set window.
After selecting this task, the dialog box in Figure 3 appears. From this interface, the user specifies the output file name along with the format of the output data set. This example again uses the CSV format. The location of the file could be a folder local to the PC, a directory within the IBM i IFS, or any server that's accessible with a Windows Mapped Network Drive.
Figure 3: The Save Results Dialog Box offers format options.
With just a few clicks of the mouse, the data is saved into a shareable data format. Obviously, the Run SQL Scripts interface is not a good option if the process needs to be executed programmatically.
So Many Choices…
Hopefully, this article has given you an understanding of how to use SQL to transform and extract data as well as what the options are for saving the output from a SELECT statement.
as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7,
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