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,